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.

 

[silicon_beaver]

the search condition/cookie,
crumb=OmnmyZ9XOhu

the 11 char in crumb is key.

 

[betaidi2]

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.

Hi

I was able to get yahoo historical quotes to retrieve historical data and download it to my spreadsheet after the date format change. ie today 5/24/2017.

As you pointed out above the

https://query1.finance.yahoo.com/v7...&interval=1d&events=history&crumb=yt6hjEL5Tx8

sends the data to an excel file from the web page.

What I did was
1) use the UNIX date format corrected for time zone

qURL = "https://query1.finance.yahoo.com/v7/finance/download/" & Symbol(i) & "?period1=" & ((StartDate - 25569) * 86400 + 21600) & "&period2=" & ((EndDate - 25569) * 86400 + 21600) & "&interval=" & DataRequest & "&events=history&crumb=yt6hjEL5Tx8 "

I used your crumb and it worked in mine, my crumb was different.
the 21600 corrects for time zone
the 25569 sets the date to 1/1/1970 for a windwos, Mac number is different - see other people post on subject

2) In the listing of the query tables parameters I used
with ...........
.PostText = True
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True

I added .postText=true
without the .postText=true no data was pulled
prior to Yahoo's date change, the postText=True was not needed

The above pulled the data from yahoo and placed it in my spreadsheet

I hope this helps

betaidi2


1

 

[silicon_beaver]

betaidi2,

Great work!

yes, I did repeat your way. and it's working.

Thanks you so much!

 

[silicon_beaver]

Anyone knows how to query new yahoo options data. Above method is not working for new yahoo options data.

https://finance.yahoo.com/quote/SPY/options?p=SPY
https://finance.yahoo.com/quote/SPY/options?p=SPY&date=1496188800

 

[Palaceman3]

Hello,

I do not have so much experience in VBA but excited to enter in stock market intraday activities, can you please give details of the code you use, I don't get to make it works,

Sdate = (Sheets("Stocklist").Cells(d, a) - 25569) * 86400 + 21600
Edate = (Sheets("Stocklist").Cells(d, b) - 25569) * 86400 + 21600

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;https://query1.finance.yahoo.com/v7/finance/download/" & stockcode & "?period1=" & Sdate & _
"&period2=" & Edate & "&interval=1d&events=history&crumb=yt6hjEL5Tx8", Destination:=Range("$A$1"))
.PostText = True
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=True
.SaveData = True
End With

Could you please explain, HistoricalPrices instead of ActiveSheet, DataRequest instead of 1d, and any mistake you found on it, I would really appreciate it.

 

[haimke]

Hi

I was able to get yahoo historical quotes to retrieve historical data and download it to my spreadsheet after the date format change. ie today 5/24/2017.

As you pointed out above the

https://query1.finance.yahoo.com/v7...&interval=1d&events=history&crumb=yt6hjEL5Tx8

sends the data to an excel file from the web page.

What I did was
1) use the UNIX date format corrected for time zone

qURL = "https://query1.finance.yahoo.com/v7/finance/download/" & Symbol(i) & "?period1=" & ((StartDate - 25569) * 86400 + 21600) & "&period2=" & ((EndDate - 25569) * 86400 + 21600) & "&interval=" & DataRequest & "&events=history&crumb=yt6hjEL5Tx8 "

I used your crumb and it worked in mine, my crumb was different.
the 21600 corrects for time zone
the 25569 sets the date to 1/1/1970 for a windwos, Mac number is different - see other people post on subject

2) In the listing of the query tables parameters I used
with ...........
.PostText = True
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True

I added .postText=true
without the .postText=true no data was pulled
prior to Yahoo's date change, the postText=True was not needed

The above pulled the data from yahoo and placed it in my spreadsheet

I hope this helps

betaidi2


1

Hi, I tried this, but I guess I did something wrong...
I am not the best with excel.
just wonder, is there a way to fix the file so all can download it fixed?
I hope this is not too rude by me.
thanks
Haimke

 

[faberem20]

Hi, I tried this, but I guess I did something wrong...
I am not the best with excel.
just wonder, is there a way to fix the file so all can download it fixed?
I hope this is not too rude by me.
thanks
Haimke

Hello,

I tried to edit the code and did not have any success either. Is it possible to post an updated Excel file with the revised code?

 

[betaidi2]

I'm away from computer for 2 weeks. I can post it when I get back, but my code is tied to form controls that wouldn't be present with just code posted. The main points of my previous post was needed to update your existing url in query to address date format change and need to add .postText statement.
Hope this helps.
Betaidi2

 

[fsantin]

Dear all,

Reading everything you guys posted, I could fix my function, as follows:

Function GetStock(stTicker As String, Optional dtDate As Variant)


    ' 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
            GetStock = CVErr(xlErrNum)
        ElseIf dtDate > Date Then
            dtDate = Date
        End If
    End If


    Dim dtPrev As Date
    Dim strURL As String, strCSV As String, strRows() As String, strCols() As String
    Dim dbClose As Double


    dtPrev = dtDate - 7
    stTicker = stTicker & ".SA"


    If dtDate <> Date Then
        ' Compile the request URL with start date and end date
        strURL = "https://query1.finance.yahoo.com/v7/finance/download/" & stTicker & _
                 "?period1=" & (((dtPrev - DateSerial(1970, 1, 1)) * 86400) + 10800) & _
                 "&period2=" & (((dtDate - DateSerial(1970, 1, 1)) * 86400) + 10800) & _
                 "&interval=1d&events=history&crumb=C5TLDavB8eQ"


    Else
        ' Compile the request URL
        strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & stTicker & "&f=l1"
    End If


    ' Debug.Print "strURL: " & strURL
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", strURL, False
    http.Send
    strCSV = http.responseText
    
    ' Debug.Print "strCSV: " & strCSV
    
    If dtDate <> Date Then
        ' The most recent information is in row 2, just below the table headings.
        ' The price close is the 5th entry
        strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
        strCols() = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
        dbClose = Val(strCols(4)) ' 4 means: 5th position, starting at index 0
    Else
        dbClose = Val(strCSV)
    End If


    ' Debug.Print "dbClose: " & dbClose
    
    GetStock = dbClose
    
    Set http = Nothing


End Function

Notes:

1. I'm not the author of this code, it's just the join of several codes found on the internet.
2. "crumb" changes to each user... in my case, i'm looking for stock prices on Bovespa (Brazilian Stock Exchange). Try downloading a sample file in Yahoo Finance site to check for the correct crumb.
3. Period1 and Period2 calculation is different to each user... in my case I have to add 10800 to the calculation.

Best regards,

FS

 

[silicon_beaver]

fsantin,

Thanks for update.
Is this way slow? or do you feel it slow?

I realize IE object method is slower than .QueryTables. method

If possible, I would go .QueryTables. method. Unfortunately, it doesn't always work.

Regards,