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