Download historical stock prices into Excel
Spreadsheet gives you both CSV and JSON interfaces
Download Historical Stock Prices Into Excel
There's a lot to like about Yahoo Finances' historical prices. Firstly it covers so many (over 75) stock exchanges and so many different types of security--stocks, ETFs, mutual funds, indices, commodity futures, currencies and cryptocurrencies. Secondly, its free, at least for now. Our spreadsheet, HistoricalPrices, combines our legacy CSV download product (EmulateURL) and the more recent and extremely versatile JSON based download (JSON Fetch) into one convenient spreadsheet which is highly adaptable to your needs and gives you full access to well-commented VBA, should you need to do any coding. As a bonus we also include the PriceOnDate user defined function (UDF) which allows you to download the historical stock price data into Excel of any security on a given date.
CSV Historical Prices
The Comma Separated Value interface was the mainstay of Yahoo Finance historical prices for many years giving access to Daily, Weekly and Monthly OHLC data and also Dividends and Split history. Yahoo complicated the ability to download historical prices into Excel as CSV in May 2017 by adding a cookie and crumb validation. As it turns out you can still access the data. To minimize the impact on existing users, HistoricalPrices includes a VBA function which takes the original URL as an argument. With this function--EmulateURL, you can fix your broken worksheets with minimal changes to your existing VBA code.
JSON Historical Prices
Watch the video below to see HistoricalPrices in action!
HistoricalPrices–Import Price History into Excel
The HistoricalPrices spreadsheet
Lets you download historical stock price data into Excel. With examples of how to use the functions, as a .xlsm file. You will need to enable macros to run it. Version 1.0
All VBA code is accessible, well commented and digitally signed by Algorithm Science.
Requires Windows and Excel 2007 or better.
How to emulate the Yahoo ichart historical prices URLs in Excel VBA--The EmulateURL function
In this section we talk about the VBA code so you can fix your existing spreadsheets using the EmulateURL function with minimal changes. The disabled URLs looked like this:
Yahoo disabled URLs of this type around May 2017, by adding a cookie and crumb so that only browsers could access the historical prices. EmulateURL is a VBA function which emulates the output of these URLs by adding a working cookie and crumb. I'd like to thank Scott Lindsay at http://www.xlautomation.com.au as the inspiration for a certain essential elements of the routine, particularly the use of the winHTTP library.
How to use it
EmulateURL is a VBA function which needs only two arguments:
- The ichart URL you wish to emulate (as a string)
- The spreadsheet location where you wish the output to go (as a Range object)
The ichart URL is the exact same URL that you were using up until Yahoo! turned off the service in May 2017 so changes to your code will be minimal. The URL itself has many arguments depending if you want daily prices (&g=d), weekly prices (&g=w), monthly prices (&g=m), dividends (&g=v) or splits (&g=s). Start date is given in the a,b and c parameters, end date is given in the d,e and f parameters. The stock symbol (&s) must be understood by Yahoo!
For example the VBA code:
MyURL = "http://ichart.finance.yahoo.com/table.csv?s=MSFT&a=05&b=06&c=2000&d=05&e=06&f=2017&g=m"
EmulateURL MyURL, Sheet1.Range("A1")
Gives you the following:
If you also want to download historical stock prices into Excel in CSV format, you can add the spreadsheet location:
EmulateURL MyURL, Sheet1.Range("A1"), Sheet1.Range("J1")
Giving you, (in addition to the above):
There's also options to:
- Sort in ascending or descending date order.
- Skip over rows with zeros in the prices, or bad formatting
- Read out the raw data from the underlying queries
Leaving a comment and registering for update notifications
If you use the code and find it useful or faulty, please let me know either way in the comment box below--I'd appreciate it. If you would like to receive notification of updates you can register (no junk mail I promise).