Home / Free Excel Spreadsheets and Templates / Yahoo Historical Price Download Spreadsheet

Yahoo Historical Price Download Spreadsheet

Download Historical Prices from Yahoo

This article was original posted on
our partner website XLAutomation

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
https://query1.finance.yahoo.com/v7/finance/download/BHP.AX?period1=1493123204&period2=1495715204&interval=1d&events=history&crumb=WxrLhK9KSAq
(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=%7B0%7D"  
Dim objRequest                  As WinHttp.WinHttpRequest
 
    Set objRequest = New WinHttp.WinHttpRequest
 
    With objRequest
        .Open "GET", strUrl, True
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        .waitForResponse
        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.

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.

 

Click on the “Email me my Free Spreadsheet” button below to get a copy of this sample workbook.

How to use the Free Yahoo Historical Price Download ?

  1. Download the Historical Price Download by clicking the ‘Email me my Free Spreadsheet’ button above.
  2. Open the workbook and then Enable Macro’s these are required to run the routines
  3. Select the period that you want to extract prices for ‘Daily’, ‘Weekly’ or ‘Monthly’
  4. Specify the number periods to extract 365 days for a year of daily prices
  5. Set the Security Codes to be extracted and the End Date for the search
  6. Finally select the button ‘Update Prices’ to extract the Historical Prices

colour logo

XLAutomation is a partner website for the Excel Help Desk.

If you are interested in more financial spreadsheets and automated routines to improve analysis we recommend visiting their website. They offer a custom workbook called Share Trade Tracker that provides Dashboards and other automated feeds of information.

Click to see a quick explainer video or access a Free Trial