Yahoo Historical Price Extract
Yahoo Finance has always been a great source of information for share traders. Some recent changes in the format of the Yahoo website have resulted in errors for many of the extract and data scraping methods that many Excel users have relied upon. This post will describe a new method that can be used within Excel to extract the historical price for any security from the Yahoo Finance website. In a future post this will be extended to extract dividend information for any security.

A quick search of the Yahoo forum reports the advice from "Official 'Hoo Staff" that the Finance API has been discontinued by the Finance team and that they do not intend to reintroduce that functionality.

 


The revised finance website from Yahoo offers the same information but with many more options. The ability to download historical prices has significantly changed but with some new routines you can easily replace your existing historical price extract routines in your own Excel workbook. Here are the steps that we took to create this revised method.

The URL https://au.finance.yahoo.com/quote/BHP.AX/history?p=BHP.AX  provides the historical list of prices to your browser. The default is to display daily prices for the the previous year.

If you hover over the "Download Data" link you see the following URL
(note: the URL above worked when the post was first created, but due to the validity periods for the crumb parameter it may no longer work)



Historical Price URL

The download URL is the made up of the following components that we have replicated below to automate the retrieval of the historical price data into an Excel workbook.

https://query1.finance.yahoo.com/v7/finance/download/BHP.AX?
The new starting query URL string and includes the security code. Only one security at a time for this request

period1=1493123204&period2=1495715204
The start and end date for the data download. These are in a unix date format that is calculated from 1st January, 1970. In this case the date range was 25 May 2016 - 25 May 2017.

interval=1d&events=history
The interval for the extract is daily with "1d" and the historical price is returned via the events parameter

&crumb=WxrLhK9KSAq
Finally the crumb which was the most complicated part of the download URL to replicate. We found in our testing that this could sometimes be re-used and other times needed to be regenerated. It was paired with a cookie that was not contained in the URL request that also needed to be supplied for the download to succeed. 


Key Code Components

We provide a sample Excel workbook at the bottom of this post with all the required code for you to replicate the extract of historical security prices from Yahoo. The key code components to achieve the extract are shown below with some explanation. If you have questions or comments on our approach please let us know in the comments at the end of the post.

Set the Unix Date for Start and End Date
Private Function strGetUnixDate(dteSetDate As Date) As String
'This function will set the Date required in the URL to the Unix date format
    strGetUnixDate = (dteSetDate - DateValue("January 1, 1970")) * 86400
End Function

Get the Yahoo Request to set the Crumb and Cookie values
Sub GetYahooRequest(strCrumb As String, strCookie As String)
'This routine will use a sample request to Yahoo to obtain a valid Cookie and Crumb

Dim strUrl                      As String: strUrl = "https://finance.yahoo.com/lookup?s=rubbish"    
Dim objRequest                  As WinHttp.WinHttpRequest
    
    Set objRequest = New WinHttp.WinHttpRequest
        
    With objRequest
        .Open "GET", strUrl, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        .waitForResponse (10)
        strCrumb = strExtractCrumb(.responseText)
        strCookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With
       
End Sub

The sample URL was found by trial an error to return a valid Crumb and Cookie that could then be used for the later call to the Yahoo Finance page to extract the historical prices. The Crumb value is extracted from the responseText returned using a string match. The Cookie is found in the responseHeader.

Update 25th June, 2017
Following a change to the Yahoo site an adjustment was required to the Open "GET" component of the HTTP Request. In the original version posted the call type for the "GET" was set to True. This made in the call in asynchronous mode. This was changed in the updated version to make the call in synchronous mode.  Which ensures that a call to Send does not return until WinHTTP has completely received the response.

Get Yahoo Finance data using the Crumb and Cookie

'----------------------------------------------------------------------
' Purpose   : Get a Finance Stock History from Yahoo.
' strUrl = "https://query1.finance.yahoo.com/v7/finance/download/" &
'        strSecurityCode &
'        "?period1=" & strStartDateUnix &
'        "&period2=" & strEndDateUnix &
'        "&interval=1d&events=history"
' intRetrys is optional and is the number of times it will try before giving up
'---------------------------------------------------------------------
Private Function strGetYahooFinanceDataRetry(strUrl As String, Optional intRetrys As Integer) As String

    Dim strResult                                   As String
    Dim arrRows()                                   As String
    Dim arrRow()                                    As String
    Dim i                                           As Integer
    Dim blnForceRefresh                             As Boolean: blnForceRefresh = False
    
    'Default retry 5 times if it isn't provided
    If intRetrys <= 0 Then intRetrys = 5
    
    'Loop through a number of times if it fails
    For i = 1 To intRetrys
        strResult = strGetYahooFinanceData(strUrl, blnForceRefresh)
        
        'Test if it worked
        arrRows = Split(strResult, vbLf)
        arrRow = Split(arrRows(0), ",")
        If arrRow(0) = "Date" Then
            Exit For
        Else
            'Reset the crumb and cookie as the don't seem to work
            blnForceRefresh = True
        End If
    Next i
    
    strGetYahooFinanceDataRetry = strResult
        
End Function

This routine uses the previously extracted Crumb and Cookie to extract historical price data for the specified security. See the sample workbook in action below and for those that want to replicate this for themselves download our sample Excel workbook to see the code and working example.


Sample Excel Workbook

See below an image to demo the sample workbook that is provided. Enter the security code and required date into the highlighted cells then click "Update Prices". The historical price will then be returned for each security. The date returned will be the closest possible prior to the supplied date.

Updated Sample Workbook (25th June, 2017)
Changes to the Yahoo site required a change to the extract steps for the Cookie and Crumb. Thanks to Signal Solver for posting their version of our original solution to this issue as it included the correct changes for the extraction of the Cookie and Crumb from the Yahoo site before extracting the required price or dividend data.
- Change to Cookie and Crumb extract routine

Updated Sample Workbook (4th June, 2017)
More suggestions included to the sample workbook to include new features
- Select to extract Price or Dividend historical data
- Set a single end date for all securities
- Output to separate Worksheets or CSV files
- Updated instructions for use

Updated Sample Workbook (2nd June, 2017)
Following feedback and suggestions we have updated the original sample workbook to include new features
- Specify the "Number of Days" to extract a set range of historical data prior to the End Date
- Historical data by Security code is stored in a Result worksheet for the Number of Days


Note: This workbook has been tested on Windows running Office 2013. No testing on MAC has been performed. It may work for a MAC environment but will need to be verified by others.





Click on the "Email me my Free Spreadsheet" button below to get a copy of this sample workbook. 
Another Free Spreadsheet by XLAutomation
Make a donation if you found this spreadsheet helpful

Our Promise: "We will only use your contact details to share the latest news, free spreadsheets and product information from the XLAutomation team. We will never share your contact details with anyone else".

Suggestions for next version of Yahoo Price Extract

As voted by our website visitors the following Yahoo Price Extract suggestions that will be next on our list are ranked below. Have your say and use the voting form further down this page to promote your favourite suggestion to the top. For details on each of the suggestions listed in the rankings refer to the short descriptions in the voting form.


Review current suggestions




Tell us what you think...

Thanks to the trading community who have downloaded our spreadsheet. We would love to know how useful you've found this solution, and if you think we could include anything else to make it even better! Leave a comment and let us know.