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.

 

[fsantin]

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,

Yes, it's a little bit slow, but considering that I only check once a day the prices, it's ok for me. I never used the ".querytables" workaround.

Regards,

 

[Spaceant]

Is it possible to post a workable Excel spreadsheet that can be used to download multiple historical stock quote?

Like the file shown in this link:
Multiple Stock Quote Downloader for Excel

Sa

 

[faberem20]

The below link does not work with Yahoo Finance right now. Is there an update to this file that can get Yahoo Finance bulk quotes?

Thanks in advance!
Erik

Is it possible to post a workable Excel spreadsheet that can be used to download multiple historical stock quote?

Like the file shown in this link:
Multiple Stock Quote Downloader for Excel

Sa

 

[igold]

@faberem

If you see my post #8 that url is a working Yahoo Stock Quote API. I use it multiple times daily (as part of a larger code). I think the limit on symbols is 200.

 

[faberem20]

Thank you for the reply. Is it possible to modify this code so I could download adjusted stock prices over a period of time? For example, I would like to download the past year of daily adjusted share prices for approximately 100 tickers at one time. Do you know how to modify the code to accomplish this?

Thanks in advance!

Erik

@faberem

If you see my post #8 that url is a working Yahoo Stock Quote API. I use it multiple times daily (as part of a larger code). I think the limit on symbols is 200.

 

[igold]

Unfortunately as stated in this thread, the historical quotes have changed. This API is for daily stock quotes and other financial data ie moving averages, 52wk high/low, dividends, etc...

 

[Spaceant]

Hi fsantin,

Can you post the excel file with the GetSstock function as I can't get it right?

Sa

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

 

[sidex]

Yes, please.

Can you post a sample of .xlsm file that works. Can not figure out to get it done.

 

[betaidi2]

Yes, please.

Can you post a sample of .xlsm file that works. Can not figure out to get it done.

Here is a simple sub that will pull data for ge for fixed dates and put data in cell "B3" of a spreadsheet.
Its a simple code that you can build on, its just to demonstrate that the data can be pulled and displayed.
Sorry if I put too much detail, some people said they were not that familiar with VBA.

Copy from Option Explicit -> end sub, paste to a new module then run sub
Note I had to add extra " & " after to https: to avoid MrExcel placing a ... in the center of the https in the reply posting.
You can remove it when you code it, but it will run with it in this one and avoid reply posting adjustment by MrExcel.
Make sure you don't have 2 option Explicits

In my program I pull multiple quotes for the same dates and stack them into 1 table.
When you do this: the spacing between tables is important, if its not right, it will jumble all the data.
Hope this helps.



Option Explicit

Dim qURL As String
Dim MyCrumb As String
Dim DataSh As Worksheet
Dim QryTable As QueryTable

Sub TestURL()

' All you have to do from here is put in variables for the dates etc and pull what you want, then format it to you specifications
' you can pull historical prices dividends splits etc

' to run the sub, copy the code from options explicit to end sub, put it in a blank module and put curser on the next line
' put curser anywhere in this line and choose run sub in VBA editor window

' put your crumb in for MYCrumb
' to find your crumb go to yahoo, put in dates etc that you want and --Right Click on Download Data--
' choose properties
' put curser on address (URL) and scroll to the end of the URL and your crumb is there
' you can use the Address(URL) data line to check the dates, Price, div and stock splits parts of URL etc
' I put an extra " & " in the qURL ine because MrExcel wont post the entire https:// with out putting ... I the middle of it.
' you can remove the extra in you code
' it will run with it though

Set DataSh = ActiveSheet
MyCrumb = "OmnmyZ9XOhu"

qURL = "https:" & "//query1.finance.yahoo.com/v7/finance/download/GE?period1=1497420000&period2=1498370400&interval=1d&events=history&crumb=" & "MyCrumb"
Set QryTable = DataSh.QueryTables.Add(Connection:="URL;" & qURL, Destination:=Range("B3"))
With QryTable
.RowNumbers = True
.PostText = True
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


' unpacks the data and partialy formats it
' note if you want to stack sucessive tables you need to put a space between them or it moves them laterally and jumbles the data

Range("B3").Offset(0, 0).CurrentRegion.TextToColumns Destination:=Range("B3").Offset(0, 0), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
Tab:=True, _
Semicolon:=True, _
Comma:=True, _
Space:=False, _
Other:=False

End Sub

 

[DRSteele]

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