Code Review - Polaris TMS App

00:00 Start
00:15 Worksheets that do NOT contain VBA code - TrustIt, Dropbox, Sample Queries, About
00:23 All Ticker worksheets VBA code calls common Change and SelectionChange subroutines
00:54 Stock Screen worksheet - ancillary
01:40 Module 6 - Query_Stock_Screen, Query_TickerData, Query_Prices
05:10 Manage worksheet - grey cells use for configuration
06:29 TickerData worksheet
08:32 Volatility worksheet
09:50 ThisWorkBook
10:10 Workbook_Close - checks to see if Portfolio state changed since spreadsheet opened
10:44 Workbook_Open - saves Portfolio state when spreadsheet opened, calls SystemCheck subroutine
11:05 SystemCheck
13:27 Use cases
15:42 Portfolio_Analyzer VBA code consists of 1 Worksheet_Change event and 6 Worksheet_SelectionChange events.
15:49 Worksheet_Change event - displays popup reminder
16:22 Primary Worksheet_SelectionChange event is when user selects A2 (Execute)
16:40 Secondary Worksheet_SelectionChange events
17:44 Max_Return subroutine - calculates max return by varying cumulative price decrease and increase
18:14 Check for user entries on Ticker worksheets
18:42 TickerWorksheets_Reset subroutine
20:00 Black-out fuse turned Off indicating looping in the Max_Return subroutine and no screen updating
20:33 Align looping limits with step value
21:24 Estimate loops, minutes, seconds, end time
21:43 Target maximization cell, typically C1 (Portfolio)
21:52 Display estimations
22:25 Use integer values for looping avoids problems with storing fractional numbers in binary
24:00 Application status bar shows progress
24:21 Display max return
24:25 Black-out fuse turned On indicating screen updating allowed
24:42 A2 (Execute) Worksheet_SelectionChange event - invokes new Portfolio state
25:22 Check Black-out fuse
26:04 Display rotating Billboard message
27:24 Master fuse turned off
27:47 Check if new Portfolio state different from previous state
29:32 Popup displayed if Portfolio states different and user has made entries on the Ticker worksheets
30:57 Restore cell formulas that user may have accidently overwritten
31:05 If needed copy new Tickers from staging area and run query to get latest ticker data
32:24 Tickers returned from the query become the new Portfolio Tickers
33:40 Renaming worksheet tabs to new Tickers is time consuming
34:40 Copy initial investments into Portfolio area
35:04 Make new Portfolio state the current Portfolio state
35:10 Invoke price updates and Ticker worksheet calculations
36:32 Turn calculation on - batch recalculation
36:45 Auto Override
37:27 Master fuse turned on
37:55 Ticker worksheet VBA code - common Change and SelectionChange subroutines
38:05 RandomWalk worksheet VBA code - has additional VBA SelectionChange code
38:51 D2 (Execute) Ticker Worksheet_SelectionChange subroutine - invokes prices update
40:26 Query_Prices subroutine - SP500_Daily query run for SP500, Portfolio, and RandomWalk worksheets. All other Ticker worksheets run PriceHistory query.
40:36 RandomWalk worksheet prices randomly generated
42:40 Commit (Write) current Portfolio state to Ticker worksheets
43:52 If user selected D2 then turn on calculation, screen updating, and call Auto_Override subroutine
46:34 Auto override applies to last order on Ticker worksheets
49:33 Ticker worksheet Selection Change subroutine - other events
50:06 Alternate use of Cumulative Shares, Column U - separate video that discusses
50:29 Popup message to use Columns BU and CB instead of Column P
50:50 Move cursor away from cell if certain rows and columns selected
51:19 Assumed user will NOT select cells in Columns D, E, G-L, AG, AP
51:53 Highlight row on Ticker Worksheet
52:04 User selects cell in an occupied row that DOES contain an Order in specific Columns OR user selects cell in any occupied row in specific Columns
52:41 Check for existing user input in occupied rows beyond the current row selected. User prompted to remove existing input in order to proceed.
53:33 Row 301 indicates no existing user input beyond the current selected row exists
54:10 How it is determined if there is existing user input - use Excel SumProduct function
55:19 Removing (restoring) cells with existing user input - make note of use of formulas in row 301
55:57 If user choses to proceed cursor left in selected cell for user to type in a entry
56:14 If cursor in Column AO rotate between menu items each time cell selected
56:23 If cursor in Column S rotate between menu items each time cell selected
56:30 Ticker Worksheet_Change subroutine - handles graphing requests
57:14 Show how user chooses a comparative Ticker using G2. Using AB2 and AC2 user chooses to hide or show Buy/Sell data points.
59:21 G2 user change event
01:42 AC2 user change event
01:02:03 AC1 user change event
01:04:13 Database objects - 4 Stored Procedures, 5 tables, 2 views
01:04:25 Stored Procedures
01:05:29 Tables
01:06:09 Views
01:06:20 Wrap-up