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.

 

[sumitgupta]

sorry...still getting an error....let me know if you find the solution.

 

[igold]

@sumitgupta

you are missing /
...ts=history&crumb=yt6hjEL5Tx8"
should be
...ts=history&crumb=/yt6hjEL5Tx8"

Who is this comment directed to?

 

[alansidman]

Look at post 7 for an alternative

 

[igold]

@alansidman,

Do you know for a fact that Yahoo is abandoning this service. Perhaps they are having some glitches at the moment...

 

[alansidman]

@igold

I do not know for a fact, however, it has been over two weeks that the Yahoo API has not worked for me. As such I have moved to something that does work. When and if it returns to normalcy, I shall perhaps return. In the meantime, I am not fretting over how to get it to work.

 

[igold]

@igold

I do not know for a fact, however, it has been over two weeks that the Yahoo API has not worked for me. As such I have moved to something that does work. When and if it returns to normalcy, I shall perhaps return. In the meantime, I am not fretting over how to get it to work.

The Historical API and the Quote API has been working for me for the last two weeks. I do see a problem with the Historical today. For me the Quote API is tied into an extensive portfolio tracker for me, while the Historical was just a sheet I pulled in for research. Since both URL's are entrenched in VBA and with extensive parsing of the Response text, fretting the details is paramount.

I guess it depends what you are attempting to accomplish.

 

[alansidman]

Been doing some searching around the internet today and found this site which has some interesting discussions.

https://www.bogleheads.org/forum/viewtopic.php?t=173057

 

[igold]

That's a pretty good thread. I have been using the Yahoo API for stock quoting since about 2012. The sale of Yahoo to Verizon has me worried.

Excuse the pun, but you never know what those yahoo's will do.

 

[sumitgupta]

Have you tr

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.

Try using this URL instead of finance.yahoo.com site...
https://www.google.com/finance/hist...e=Oct+10,+2008&enddate=Oct+9,+2009&output=csv

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.

 

[silicon_beaver]

yahoo changes to https from http.

I do daily yahoo data query. intraday and EOD.
Presently, intraday data still run and ok to access. but yahoo historical data are discontinued.
Indeed I tried whatever way to query new yahoo historical data. Just no way to make it, similar error as post#1.

Looking for a fix for this https change, either on historical data or yahoo options data. none working now.