Yahoo Historical Price Extract
This free spreadsheet is a solution for share traders who relied on the Yahoo Finance historical price extract feature that was suddenly discontinued. You can use this new method in Excel to extract the historical price for any security from the Yahoo Finance website.
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.
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 developed an Excel workbook that used a "cookie & crumb" approach to mimic a Yahoo website request. This worked for some months and further changes to the website rendered that method invalid and the prices were no longer returned. We have now developed an alternate method that used "scraping" to extract the prices we are interested in. This method relies on a basic WinHTTP call and is likely to remain working into the future. Even if Yahoo make changes to the structure of their price history page the "scraping" code can be updated to reflect any new tags that are required.
We have listed the key code component below that is the basis of this new method.
You can download for FREE the updated Yahoo Historical Price workbook at the bottom of this page and run the price extract for yourself. With this updated version to get access to the VBA code that is used to "scrape" the prices from the Yahoo website we are charging a one-off cost. For details on accessing the VBA code click here.
Get the Yahoo Scrape Request to extract Page data for Yahoo Prices
Private Function strGetYahooScrapeFinanceDataWinHTTP(strURL As String) As String 'This function will return the Yahoo Finance Data that has been requested via the URL Dim strResult As String Dim objRequest As WinHttp.WinHttpRequest 'Any Errors are just by passed as Blank returned values On Error GoTo ERROR_HANDLER strGetYahooScrapeFinanceDataWinHTTP = "" Set objRequest = New WinHttp.WinHttpRequest With objRequest .Open "GET", strURL, False .setRequestHeader "Content-Type", "text/html" .send .waitForResponse (10) strResult = .ResponseText Call WriteResponseDetails("WinHTTP", "History", strURL, .ResponseText) strResult = .ResponseText End With strGetYahooScrapeFinanceDataWinHTTP = strResult PROGRAM_EXIT: Exit Function ERROR_HANDLER: Resume PROGRAM_EXIT End Function
New Features - Historical Price Extract v9 - Released 26th February, 2019
Updates have been made to restore the capability of this spreadsheet to extract prices from Yahoo. It now uses a "scraping" technique to return the prices for a group of security codes.
- Scraping price data from Yahoo website has restored the price extract
- Extract Method for users that can still use the "Cookie & Crumb" approach it is available for selection at the top of the worksheet.
Free 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 "Extract Historical Data". The historical price will then be returned for each security. The date returned will be the closest possible prior to the supplied date.
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 require Windows virtualisation.
Download Your Free Historical Price Extract Spreadsheet
We will use the details entered below to add you to our mailing list to receive the free download file and latest news about free spreadsheets and products from the XLAutomation team. You will have the option to unsubscribe at any time.
IMPORTANT NOTE: If you have previously download Historical Price Extract you will have received an unlocked copy of the workbook with access to the VBA source code. In this updated version we have hidden the VBA source code. For a one-off cost of $49 (AUD) we can provide access to the source code. This small one-off cost will support changes to the Historical Price Extract should they be required in the future. Click here for more details on purchasing a copy of the VBA source code
Suggestions for next version of Yahoo Price Extract
1. Include Splits and Adjust Prices Overtime
Update the extract options to include an option that takes into account "splits" reported by Yahoo and then an adjusted the share price to account for the split overtime. This would include additional columns to the extract to list when a split occurs for a security and then an "adjusted share price" column to reflect how the share price has been affected for a single share over the time of the extract period.
2. Option for Output Directory and File Format
Provide an option to specify or select a directory that will be destination for the output files. As part of that include an option to specify a File Format for the output files.
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 below to promote your favourite suggestion to the top. For details on each of the suggestions listed in the rankings refer to the short descriptions.