Yahoo Finance API for Stock Quotes Changed

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 57

Thread: Yahoo Finance API for Stock Quotes Changed

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Yahoo Finance API for Stock Quotes Changed

    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 by jonathanwang003; May 17th, 2017 at 06:35 PM.

  2. #2
    New Member
    Join Date
    May 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  3. #3
    New Member
    Join Date
    May 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    190
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Code:
    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 = "https://finance.yahoo.com/quote/" & 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

  5. #5
    New Member
    Join Date
    May 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  6. #6
    New Member
    Join Date
    May 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  7. #7
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,019
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  8. #8
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    1,846
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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:

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

    Code:
    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

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  9. #9
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    1,846
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Code:
    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">
    Yahoo
    ****** name="viewport" content="width=device-width,initial-scale=1,minimal-ui">
    ****** http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

    ********>
    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";
    *********>

    ******>









    ******** 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';
    }
    *********>








    Last edited by igold; May 20th, 2017 at 03:27 PM.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  10. #10
    New Member
    Join Date
    May 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com