Yahoo Finance API for Stock Quotes Changed

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.


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:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.


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.

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.
 
Upvote 0
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!"
 
Upvote 0
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.
 
Upvote 0
@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...
 
Upvote 0
@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?
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top