Yahoo Finance API for Stock Quotes Changed

[jonathanwang003]

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.

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.

 

[merlin777]

Having my doubts now.... seen a lot more worrying comments.

 

[Harold5435]

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.

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.

I heard that Yahoo Finance API has benn discontinued.
MarketXLS would be an alternative. It has regular updates and custumer support unlike Yahoo.
It cost a little but it will save you more time.
I hope it would help you.

 

[igold]

Hi Harold,

Welcome to the MrExcel Forum.

I have had some experience with MarketXLS, but I am sure someone, somewhere, had to have said, "why pay, when you can ride for free!"

 

[merlin777]

Hi Harold,

Welcome to the MrExcel Forum.

I have had some experience with MarketXLS, but I am sure someone, somewhere, had to have said, "why pay, when you can ride for free!"

I've spent the weekend fretting over this and i've found a free solution that doesn't even need vba to get live prices imported every minute.

Essentially, you create a sheet in google docs, build a table of the data you need using the googlefinance() function, publish it to web and then use that URL to 'get data from web' into excel.

Works with 2007. As far as I can tell, once you've published the google sheet to web it seems to keep updating the web page even if the sheet is closed. Even if I'm wrong you just need to leave it open. If you set excel's connection properties to refresh in the background automatically every minute it all seems to work like a dream....

I'm using it for live data but i think it works for historical too.

The only thing I can't get from it is the exchange the shares are on.

....and the best bit? It doesn't use Yapoo.

 

[igold]

@merlin777

Correct me if I am wrong, but even if you change the Excel connection properties to refresh every minute, you are not getting fresh data every minute. Google only refreshes every five minutes, so you are getting the same "stale" information from the Google sheet for 4 out of the 5 Excel refreshes until Google refreshes it's web page once every 5 minutes...

 

[merlin777]

@merlin777

Correct me if I am wrong, but even if you change the Excel connection properties to refresh every minute, you are not getting fresh data every minute. Google only refreshes every five minutes, so you are getting the same "stale" information from the Google sheet for 4 out of the 5 Excel refreshes until Google refreshes it's web page once every 5 minutes...

You may well be right, though I haven't really looked that closely. My totals seem to change with the 1 min refreshes but it could be that individual shares are update at different 5min intervals?

 

[alansidman]

If you are looking for historical data, then this site has a downloadable spreadsheet that uses Google Finance

http://investexcel.net/multiple-stock-quote-downloader-for-excel/

 

[DRSteele]

Well, there is some good news. Microsoft is making moves in regards to Excel and stock quotes. See here: https://excel.uservoice.com/forums/...racking_code=32590eacde58136765272f6e89d97ae7

 

[merlin777]

Well, there is some good news. Microsoft is making moves in regards to Excel and stock quotes. See here: https://excel.uservoice.com/forums/...racking_code=32590eacde58136765272f6e89d97ae7

fantastic. hope it'll work with 2007.

the google sheets approach is working so well for me I might not even use the MS option.

 

[DRSteele]

Now some super news, at least at first blush. See here: https://excel.uservoice.com/forums/...current-stock-prices-and-historical-data-into