fbpx
Your 'Go To' for Custom Excel Solutions
excel-applications-blue2

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.

yahoo-finance-api-broken2

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)

 

Track your share portfolio performance in one place with Share Trade Tracker. This custom built Excel spreadsheet for traders includes a Historical Price Extract function, and automated retrieval of share prices and dividends, including franking credits & franked % for Australian shares. The addition of a filtered dashboard, charts and reports give traders the tools they need to monitor their trading performance.

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 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.

Vote for your preferred suggested improvement to the Yahoo Price Extract workbook

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.