I've found a way to pull in Yahoo daily close data for a particular stock using vba code, but Yahoo seems to allow me to pull only 100 days (100 rows). If anyone has a way to get more of the historical data using vba code, I'd appreciate knowing how to do this.
The code that pulls 100 rows is below...
Code
ActiveWorkbook.Queries.Add Name:="Table 2", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/MSFT/history/""))," & Chr(13) & _
"" & Chr(10) & " Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & _
" #""Changed Type"" = Table.TransformColumnTypes(Data2,{{""Date"", type date}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Close*"", type text}, {""Adj Close**"", type text}, {""Volume"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 2"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 2]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_2"
.Refresh BackgroundQuery:=False
End Sub