Code Review - Polaris Valuation App

00:00:00 Start 00:00:30 Video located in Polaris Valuation App playlist 00:00:45 Worksheets - Manage, TrustIt, Topical, Candidates, Queries, Narratives, PolarisData, About, Dropbox, Valuation 00:02:35 VBA code layout - Module 5, Module 6, Candidates, ThisWorkBook, Valuation. VModules 1 to 4 are not discussed in this video. 00:03:33 Candidates worksheet 00:06:58 Queries worksheet 00:08:41 ThisWorkBook - event subroutines Workbook_Open and Workbook_BeforeClose. Workbook_Open calls SystemCheck function. Workbook_BeforeClose displays message about deleting Narratives. 00:11:38 Module 5 - functions communicate with Polaris SQL Server database 00: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. 00:12:35 Polaris_Data_Accessible attempts to execute a No-Operation (NOOP) stored procedure to see if SQL Server database is accessible 00:12:53 LastestVersion retrieves lastest version of spreadsheet 00:13:03 Message_Board retrieves message to be displayed when spreadsheet first opened. If message is blank then no message displayed. 00:12:24 SpreadsheetOpen logs context information (Excel version, spreadsheet version, etc) in database when spreadsheet opened. 00:13:55 NarrativeLog logs Narrative information in database when Narrative is saved 00:14:39 TickerLog logs Ticker and Price in database when a new valid Ticker entered in cell E2 00:15:39 Billboard retrieves revolving billboard message to display in cell H1 in Valuation worksheet 00:16:04 Lookup Ticker returns Ticker based on the partial company name. Company name can include wildcard *. 00: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. 00:17:13 Global constants and variables - VideoRecord, SpreadsheetVersion, NameofComputer, NameofUser 00: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. 00:21:03 NarrativeFindRow searches for Ticker in Narrative worksheet. If not found returns next available row. 00:21:53 PortfolioFindRow searches for Ticker in Portfolio list on Manage worksheet. If not found returns next available row. 00: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. 00: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. 00:23:56 Valuation worksheet VBA code. Two event subroutines for Worksheet_Change and Worksheet_SelectionChange. 00: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. 00:30:08 Worksheet_SelectionChange event clears or copies data into cells based on cell user clicks on 00:31:28 Quick Ticker Input 00:32:26 Quick Clear of Company Name 00:32:51 Quick Switch between dropdown lists 00:34:20 Quick Clear of Manage dropdown list 00:35:03 Quick Add to Active Portfolio list 00: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. 00: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. 00:38:54 CAPM related data also saved in Narrative and logged in SQL Server database. CAPM data is never restored to be used in Valuation spreadsheet. 00:40:41 Valuation_DCF worksheet VBA code 00:43:36 Polaris SQL Database Objects - Stored Procedures (SP's), Views, and Tables 00:44:06 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 00:48:07 Views and Tables - vw_Valuation_SpreadsheetVersion, Valuation_SpreadsheetVersion, Valuation_SpreadsheetOpenLog, Valuation_SpreadsheetTickerLog, Valuation_SpreadsheetNarativeLog 00:50:22 Wrap-up