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

Видео Code Review - Polaris Valuation App канала Polaris Investing