jonathanwang003
Board Regular
- Joined
- May 9, 2014
- Messages
- 133
Hello world,
Yahoo Finance has changed their API today without any information on how we can update. The new URL uses Unix Timecoding for dates.
Here's a sample URL for the S&P500. If you copy this and paste it into your browser, it will open the CSV with historical quotes.
https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1459468800&period2=1494028800&interval=1d&events=history&crumb=yt6hjEL5Tx8
How I've modified this with VBA is using the Qurl below. I just need 3 variables (Symbol, StartDate, and EndDate) I replaced the old Qurl which stopped working today with this Qurl code but I keep getting an error on the second to last line where it refreshes the BackgroundQuery.
What the code does is constructs the URL, opens it and puts its data into the current workbook on a specific tab and cell.
I've tested the URL above, I've ensured it matches what the code generates, I just don't know why it gives me an error.
Yahoo Finance has changed their API today without any information on how we can update. The new URL uses Unix Timecoding for dates.
Here's a sample URL for the S&P500. If you copy this and paste it into your browser, it will open the CSV with historical quotes.
https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1459468800&period2=1494028800&interval=1d&events=history&crumb=yt6hjEL5Tx8
How I've modified this with VBA is using the Qurl below. I just need 3 variables (Symbol, StartDate, and EndDate) I replaced the old Qurl which stopped working today with this Qurl code but I keep getting an error on the second to last line where it refreshes the BackgroundQuery.
What the code does is constructs the URL, opens it and puts its data into the current workbook on a specific tab and cell.
Code:
Qurl = "https://query1.finance.yahoo.com/v7/finance/download/" & Symbol
Qurl = Qurl & "?period1=" & (StartDate - DateSerial(1970, 1, 1)) * 86400 _
& "&period2=" & (EndDate - DateSerial(1970, 1, 1)) * 86400 & "&interval=1d&events=history&crumb=yt6hjEL5Tx8"
With HistoricalPrices.QueryTables.Add(Connection:="URL;" & Qurl, Destination:=PasteDest)
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
I've tested the URL above, I've ensured it matches what the code generates, I just don't know why it gives me an error.
Last edited: