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]

This thread might help too: https://www.mrexcel.com/forum/excel...rieve-stock-market-quotations-into-excel.html

I have been using the API successfully for several years but this week it doesn't seem to be updating. I went on the yahoo finance community boards (https://forums.yahoo.net/t5/Yahoo-F...e-API-broken/m-p/282108/highlight/false#M3997) and it would seem they have discontinued it.

Have I got that right?

Later in that thread someone has offered up a solution which directly replaces the yahoo API.

Yahoo finance API has had some flaky days in the past so I'm hoping I've got it wrong and I'll wake up the a fully functioning workbook again. If not, maybe we could get the alternative version working?

By the way, not sure if it's relevant but this week some of my stock charts on google finance and yahoo finance only display historical data for a month upwards. There are no days, weeks or near-live charts at all (solo oil and michelmersh brick holdings are 2 that are affected)

 

[merlin777]

I have been using the API successfully for several years but this week it doesn't seem to be updating. I went on the yahoo finance community boards (https://forums.yahoo.net/t5/Yahoo-F...e-API-broken/m-p/282108/highlight/false#M3997) and it would seem they have discontinued it.

Have I got that right?

Later in that thread someone has offered up a solution which directly replaces the yahoo API.

Yahoo finance API has had some flaky days in the past so I'm hoping I've got it wrong and I'll wake up the a fully functioning workbook again. If not, maybe we could get the alternative version working?

By the way, not sure if it's relevant but this week some of my stock charts on google finance and yahoo finance only display historical data for a month upwards. There are no days, weeks or near-live charts at all (solo oil and michelmersh brick holdings are 2 that are affected)

Well, now I'm really confused. Many stock prices in my workbook have updated today, they can only be getting data from the yahoo API. Neither google finance or yahoo finance are showing 1 day or 5 day charts for some stocks.

What on earth is going on?

 

[Simulationconsultant]

I've posted a new version of the multiple stock quote downloader that works with the new Yahoo finance system. Please let me know if it works for you (I've only tested it on Windows 10/Excel 2010. Get it here

 

[silicon_beaver]

Test new yahoo API
It offers upto six-month data. better than nothing. but six-month span is a new load for data maintenance

 

[merlin777]

Well, I thought I would report back. I did nothing and it came good on its own. Yahoo said they had some glitches to deal with and it seems they did. None of the data I use is historical so I guess the changes haven't affected me.

However, I'm still not an expert with the api and I'm having trouble with some of the codes. I'm using p2 which is 'change in percent' but it often differs greatly from the near-live price percentage on google finance so I tried to change it to k2 which is 'change in percent [real-time] and I just get 'N/A'. Is this the wrong code to use or has it been changed or what?

 

[igold]

Sad to say that the Yahoo Stock Quote API is now dead. The page is returning this message.

It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.

Fcn Yahoo!

 

[DRSteele]

How irritating! A simple way to get a current stock quote into Excel has now been curtailed. I wonder what got them all huffy.

 

[alansidman]

a little bit convoluted but here is an alternative

https://office-watch.com/2016/excel-stock-prices-from-google-finance/

 

[merlin777]

this was posted by an administrator on the yahoo community halp forums:

[FONT=&quot]"The new download issue which began 11/01/17 and returning an error 999 for most users is currently being investigated and we hope to have it resolved soon."

so hopefully reports of its permanent demise are wrong.[/FONT]

 

[igold]

@merlin777

I hope you're right, but the message that they posted on the actual web page seemed pretty unambiguous.