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.

 

[stefanheline]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

I use the exact same code snippet that you have and it's worked for a quite a long time. I believe I originally started with a Google query, but switched over to Yahoo.

But as you mentioned it stopped working as of this weekend.

 

[stefanheline]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

Just started playing around with dates and used your offset from 1/1/1970 and calculate that the values generated seemed to be off by 4 hours. I pulled same sample query strings and using what is shown for period1 and period2 and reversed engineered the dates. When I started using 1/1/1970 as the base date, it wasn't working and then calculated that it was off. Try using 12/31/1969 8:00:00 PM instead (or decrement the value by 14400). It may be a time zone issue.

 

[Andy16H]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

I used this functionality almost daily. Pretty disappointed in the change. I created the following workaround. I'm sure it's not the best way but its working for now.

Public Function YahooQuote(strTicker As String, Optional dtDate As Variant, Optional dtValue As Variant)
     'requires reference to winhttp.dll in Microsoft WinHTTP Services, version 5.1
    Dim HttpReq  As New WinHttpRequest
    Dim strCSV As String
    Dim strURL As String
    Dim dtPrevDate As Date
    Dim strRows() As String
    Dim strColumns() As String
    Dim dbDate As Double
    Dim dbOpen As Double
    Dim dbHigh As Double
    Dim dbLow As Double
    Dim dbClose As Double
    Dim dbVolume As Double
    Dim dbAdjClose As Double
 ' Date is optional - if omitted, use today. If value is not a date, throw error.
    
 If IsMissing(dtDate) Then
    dtDate = Date
 Else
    If Not (IsDate(dtDate)) Then
        YahooQuote = CVErr(xlErrNum)
    End If
 End If
 
 dtStartDate = (dtDate - DateSerial(1970, 1, 1)) * 86400
 dtEndDate = ((dtDate + 1) - DateSerial(1970, 1, 1)) * 86400
    strURL = "[URL]https://finance.yahoo.com/quote/[/URL]" & strTicker & "/history?period1=" & dtStartDate & "&period2=" & dtEndDate & "&interval=1d&filter=history&frequency=1d"
    'Debug.Print strURL
    
    On Error Resume Next
     
    With HttpReq
        .Open "GET", strURL, False
        .SetRequestHeader "User-Agent", "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9b5) Gecko/2008032620 Firefox/3.0b5"
        .Send
    End With
     
    strCSV = HttpReq.ResponseText
    HttpReq.WaitForResponse
    
    TotalLength = Len(strCSV)
    StartPoint = InStr(strCSV, """HistoricalPriceStore""")
    EndPoint = InStr(strCSV, """firstTradeDate""")
    strCSV = Mid(strCSV, StartPoint, EndPoint - StartPoint)
    
    TotalLength = Len(strCSV)
    StartPoint = InStr(strCSV, "{" & """prices""" & ":[") + 11
    EndPoint = InStr(strCSV, "]," & """isPending""")
    strCSV = Mid(strCSV, StartPoint, EndPoint - StartPoint)
    strCSV = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(strCSV, "{", ""), "}", "") _
    , "," & """" & "date" & """" & ":", Chr(10)), """" & "open" & """" & ":", ""), """" & "high" & """" & ":", ""), """" & "low" & """" & ":", "") _
    , """" & "close" & """" & ":", ""), """" & "volume" & """" & ":", ""), "," & """" & "amount" & """" & ":", Chr(10)), """" & "type" & """" & ":", "") _
    , """" & "data" & """" & ":", ""), """" & "unadjclose" & """" & ":", ""), """", ""), "date:", "")
    
    
    strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
    strColumns = Split(strRows(0), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
    
    dbDate = (strColumns(0) / 86400) + DateSerial(1970, 1, 1)
    dbOpen = strColumns(1)
    dbHigh = strColumns(2)
    dbLow = strColumns(3)
    dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0
    dbVolume = strColumns(5)
    dbAdjClose = strColumns(6)
    
    'Debug.Print "Date: " & dbDate
    'Debug.Print "Open: " & dbOpen
    'Debug.Print "High: " & dbHigh
    'Debug.Print "Low: " & dbLow
    'Debug.Print "Close: " & dbClose
    'Debug.Print "Volume: " & dbVolume
    'Debug.Print "AdjClose: " & dbAdjClose
    
    If IsMissing(dtValue) Then
        YahooQuote = dbClose
    ElseIf dtValue = "Date" Or dtValue = "date" Then
        YahooQuote = dbDate
    ElseIf dtValue = "Open" Or dtValue = "open" Then
        YahooQuote = dbOpen
    ElseIf dtValue = "High" Or dtValue = "high" Then
        YahooQuote = dbHigh
    ElseIf dtValue = "Low" Or dtValue = "low" Then
        YahooQuote = dbLow
    ElseIf dtValue = "Close" Or dtValue = "close" Then
        YahooQuote = dbClose
    ElseIf dtValue = "AdjClose" Or dtValue = "Adjclose" Or dtValue = "adjclose" Then
        YahooQuote = dbAdjClose
    ElseIf dtValue = "Volume" Or "volume" Then
        YahooQuote = dbVolume
    Else
        YahooQuote = "N/A"
    End If
    
  Set HttpReq = Nothing
     
End Function

 

[stefanheline]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

That's quite a bit of code as a workaround and if I understand it correctly, you are only selecting one row at a time from the table. I usually only need to load one row at a time (if I run it daily), but want to be able to select all days since I last loaded the quotes.

 

[Morzanapy]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

Yahoo has simply discontinued this service without any notice to their thousands users ! Found this link on Invest Excel that does the same thing using Google but I can't even get the closing prices of the SPX500 as the macro doens't recognize Goole symbol for SPX which is .INX ! Google Finance Stock Quotes in Excel

 

[alansidman]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

With Yahoo abandoning its service, I have moved to an Excel add-in available at Jürgen Bäurle that works very nicely. It is free!!

 

[igold]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

I can see from a routine that I have that the Historical API has changed- I have not really looked at that yet.

However on the Stock Quote API is still working. With that being said, about a week ago I did have to make on slight change to URL.

This URL which will bring back Symbol, Name, Last, Previous Close, and 1 yr Target was changed to this:

 http://download.finance.yahoo.com/d/quotes.csv?s=AAPL+AIZ+BDX+BR+CVS&f=snl1pt8

from this:

http://finance.yahoo.com/d/quotes.csv?s=AAPL+AIZ+BDX+BR+CVS&f=snl1pt8

in the exact same format. I did not have to change my parsing at all.

I do not think that Yahoo has abandoned this... yet. With the impending transition on their sale who knows what it will happen in the near future, but as of right now, this is working.

 

[igold]

Re: Please Help: Yahoo Finance API for Stock Quotes Changed

Also looking my Historical routine using a different URL than what was posted above.

Using this URL which is asking for Historical pricing from 3/1/2017 - 3/31/2017 for the symbol UNP, namely: Date, Open, High, Low, Close, Volume, Adj Close

https://ichart.finance.yahoo.com/table.csv?s=UNP&a=2&b=1&c=2017&d=2&e=31&f=2017&g=d&q=q&y=0&z=UNP&x=.csv

As part of the Response text from Yahoo is this excerpt:

Will be right back...


Thank you for your patience.

Our engineers are working quickly to resolve the issue.


So perhaps all is not lost. If anyone wants the complete response, here it is:





****** http-equiv="content-type" content="text/html; charset=UTF-8">
****** charset="utf-8">
<title>Yahoo</title>
****** name="viewport" content="width=device-width,initial-scale=1,minimal-ui">
****** http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<style>
html {
height: 100%;
}
body {
background: #fafafc url(https://s.yimg.com/nn/img/sad-panda-201402200631.png) 50% 50%;
background-size: cover;
height: 100%;
text-align: center;
font: 300 18px "helvetica neue", helvetica, verdana, tahoma, arial, sans-serif;
}
table {
height: 100%;
width: 100%;
table-layout: fixed;
border-collapse: collapse;
border-spacing: 0;
border: none;
}
h1 {
font-size: 42px;
font-weight: 400;
color: #400090;
}
p {
color: #1A1A1A;
}
#message-1 {
font-weight: bold;
margin: 0;
}
#message-2 {
display: inline-block;
*display: inline;
zoom: 1;
max-width: 17em;
_width: 17em;
}
#spanishContent {
display: none;
}
</style>
********>
document.write('

');var beacon = new Image();beacon.src="//bcn.fp.yahoo.com/p?s=1197757129&t="+new Date().getTime()+"&err_url="+encodeURIComponent(document.URL)+"&err=500";
*********>

******>
<!-- status code : 502 -->
<!-- Could Not Connect -->
<!-- host machine: media-router7.prod.media.bf1.yahoo.com -->
<!-- timestamp: 1495303918.000 -->
<!-- url: https://ichart.finance.yahoo.com/table.csv?s=UNP&a=2&b=1&c=2017&d=2&e=31&f=2017&g=d&q=q&y=0&z=UNP&x=.csv-->

******** type="text/javascript"> if (****************.hostname=='att.yahoo.com'){ document.write(' '); }else{ document.write(' '); } *********> Will be right back... Thank you for your patience. Our engineers are working quickly to resolve the issue. Volvemos enseguida� Gracias por tu paciencia. Nuestros ingenieros están trabajando rápidamente para resolver el problema. ******** type="text/javascript"> if (****************.hostname=='espanol.att.yahoo.com'){ document.getElementById('englishContent').style.display = 'none'; document.getElementById('spanishContent').style.display = 'block'; } *********>

<tbody>
</tbody>

 

[sumitgupta]

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