Code Review - Polaris Valuation App

00:00 Start
00:30 Video located in Polaris Valuation App playlist
00:45 Worksheets - Manage, TrustIt, Topical, Candidates, Queries, Narratives, PolarisData, About, Dropbox, Valuation
02:35 VBA code layout - Module 5, Module 6, Candidates, ThisWorkBook, Valuation. VModules 1 to 4 are not discussed in this video.
03:33 Candidates worksheet
06:58 Queries worksheet
08:41 ThisWorkBook - event subroutines Workbook_Open and Workbook_BeforeClose. Workbook_Open calls SystemCheck function. Workbook_BeforeClose displays message about deleting Narratives.
11:38 Module 5 - functions communicate with Polaris SQL Server database
11:55 TickerData retrieves Ticker data and places in PolarisData worksheet. Function called each time new valid Ticker entered in cell E2 in Valuations worksheet.
12:35 Polaris_Data_Accessible attempts to execute a No-Operation (NOOP) stored procedure to see if SQL Server database is accessible
12:53 LastestVersion retrieves lastest version of spreadsheet
13:03 Message_Board retrieves message to be displayed when spreadsheet first opened. If message is blank then no message displayed.
12:24 SpreadsheetOpen logs context information (Excel version, spreadsheet version, etc) in database when spreadsheet opened.
13:55 NarrativeLog logs Narrative information in database when Narrative is saved
14:39 TickerLog logs Ticker and Price in database when a new valid Ticker entered in cell E2
15:39 Billboard retrieves revolving billboard message to display in cell H1 in Valuation worksheet
16:04 Lookup Ticker returns Ticker based on the partial company name. Company name can include wildcard *.
16:40 Module 6 - functions called by ThisWorkBook and Valuation worksheets. Functions make VBA code in worksheets more modular and easier to understand. Functions DO NOT communicate over the internet.
17:13 Global constants and variables - VideoRecord, SpreadsheetVersion, NameofComputer, NameofUser
18:01 SystemCheck first checks to see if in Video Record mode. Next records environment (date, time, etc) data in cells in Manage worksheet. Next checks Excel version. Next checks internet connection. Next logs spreadsheet information in database. Next checks version of spreadsheet. Lastly displays message if message is non-blank.
21:03 NarrativeFindRow searches for Ticker in Narrative worksheet. If not found returns next available row.
21:53 PortfolioFindRow searches for Ticker in Portfolio list on Manage worksheet. If not found returns next available row.
22:49 Restore_Narrative re-populates Narrative related cells if Ticker entered in cell E2 has a previous saved Narrative. If not previous saved Narrative then Narrative related cells set to blank.
23:08 Restore_Formulas restores ALL formulas in the Valuation worksheet after a Ticker is entered in cell E2. This is done in case user accidently overwrites a cell formula.
23:56 Valuation worksheet VBA code. Two event subroutines for Worksheet_Change and Worksheet_SelectionChange.
24:55 E2 change code - retrieves Price, manage dropdown ticker list, check to see if in Video Record mode, log Ticker information, refresh Narrative cells, update Billboard message, restore formulas, restore Narrative cells, retrieve Polaris data, clear cells where user input expected.
30:08 Worksheet_SelectionChange event clears or copies data into cells based on cell user clicks on
31:28 Quick Ticker Input
32:26 Quick Clear of Company Name
32:51 Quick Switch between dropdown lists
34:20 Quick Clear of Manage dropdown list
35:03 Quick Add to Active Portfolio list
36:24 When user clicks on cell AD1 Narration and data associated with Narrative are cleared in Valuation worksheet. Must save to delete from Narrative worksheet.
36:55 When user clicks on cell U1 and Ticker is valid then Narration and data associated with Narrative displayed in column S saved. Saved Narrative data is logged in Polaris SQL Server database.
38:57 CAPM related data also saved in Narrative and logged in SQL Server database. CAPM data is never restored to be used in Valuation spreadsheet.
40:50 Polaris SQL Database Objects - Stored Procedures (SP's), Views, and Tables
41:14 Stored Procedures - sp_Valuation_SpreadsheetOpen, sp_Valuation_Message_Board, sp_Valuation_Billboard, sp_Valuation_SpreadsheetOpen, sp_Valuation_TickerLog, sp_Valuation_NarrativeLog, sp_Valuation_TickerData, dbo.LocalDateTime, dbo.UTCDateTime, dbo.FormatDateTime
45:15 Views and Tables - vw_Valuation_SpreadsheetVersion, Valuation_SpreadsheetVersion, Valuation_SpreadsheetOpenLog, Valuation_SpreadsheetTickerLog, Valuation_SpreadsheetNarativeLog
46:49 Wrap-up