Data Access - Quick Reference Guide смотреть онлайн

00:00:00 Start
00:00:31 Template used to develop Polaris free applications - SP500 Analyzer, Portfolio Return, Polaris Valuation
00:00:55 Video is a brief review of content in videos in "Data Access, Database, Code Review" playlist
00:02:50 Built-in Excel functions - volatile functions, lookup functions, distinct values, alphabetize
00:05:33 SumProduct should have been called SumOperator
00:06:18 Use Choose function to add comments to formulas
00:06:43 Lookup formulas - Index, Match and XLookup
00:07:28 Counting non-blank cells in a column
00:08:03 First and Last row containing a non-blank
00:08:18 Unary operator turns True/False into -1 or 0
00:08:55 Last occupied row in column that is NOT contain a formula
00:09:34 Replace 0 with blank with a IfBlank equilavent using IfError
00:10:02 Number occurrences in a string
00:10:29 Number of distinct values in a column
00:11:56 Built-in VBA functions - Worksheetfunction, Application, OnError, WorkSheetFuncton.Iferror, Application.Intersect
00:16:00 Last row in column
00:16:10 Application.Evaluate - execute Excel formulas in VBA
00:16:44 Polaris provided general functions
00:17:42 UpdateTime
00:18:27 Random
00:19:14 Array Max, Min, Mean, StdDev
00:19:28 TransposeArray
00:20:08 LastRow_InColumn
00:20:35 DistinctSort, SelectionSort
00:20:53 Indirect_Trigger
00:21:51 IfBlank
00:22:25 ActiveTabName, TabNum, TabName, WorksheetNameExists, WorksheetIndexExists
00:23:40 System Level Functions - IsInternetConnected, PublicIP, ComputerName, UserName
00:24:14 Polaris provided timing functions
00:24:44 GetTickCount - milliseconds
00:25:11 Spin - milliseconds
00:25:24 Sleep - milliseconds
00:25:54 Wait_Loop, Wait - seconds
00:26:51 TimerEvent - time of day
00:27:08 Polaris provided stock related functions
00:27:14 Single Ticker functions - current, historical
00:28:49 Multiple Ticker functions - current, historical
00:30:00 Web Services functions - IEX, EOD, Polaris, Alpha
00:30:11 EOD Multiple Web Service example - returns JSON formatted data
00:31:55 Polaris provided utility functions
00:32:06 Tickers
00:32:59 CSV_SplitOut
00:33:35 JSON_Parser
00:34:18 Full_JSON_Parser - watch "Data Access - VBA External Data" for more information
00:34:47 Polaris provided tracing functions - Debug_Caller, EchoBack
00:35:36 If spreadsheet not set up for tracing include Exit Function as first statment in Debug_Caller function
00:36:03 SQL Statements - (3) Data Definition, (3) Data Manipulation, (1) Select
00:36:26 Data Definition Create, Alter, Drop
00:34:58 Data Manipulation - Insert, Update, Delete
00:37:30 Query - Select, Subquery, Correlated subquery
00:38:34 Other - Views, Stored Procedures
00:39:05 Advanced - Common Table Expression (CTE), Table Value Constructor (TVC)
00:39:53 Wrap-up