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 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.
The new starting query URL string and includes the security code. Only one security at a time for this request
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.
The interval for the extract is daily with “1d” and the historical price is returned via the events parameter
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 ?
- Download the Historical Price Download by clicking the ‘Email me my Free Spreadsheet’ button above.
- Open the workbook and then Enable Macro’s these are required to run the routines
- Select the period that you want to extract prices for ‘Daily’, ‘Weekly’ or ‘Monthly’
- Specify the number periods to extract 365 days for a year of daily prices
- Set the Security Codes to be extracted and the End Date for the search
- Finally select the button ‘Update Prices’ to extract the Historical Prices
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.