Download Historical Price Data into Excel using VBA macros
VBA code and examples to import historical stock prices into Excel
Yahoo! data covers over 75 stock exchanges and many different types of security--stocks, ETFs, mutual funds, indices, commodity futures, currencies and cryptocurrencies. It is much more versatile than the STOCKHISTORY function (see "How useful is STOCKHISTORY?"), and for now, the data is free. Our spreadsheet, GetHistoricalPrices, combines our legacy CSV download product (EmulateURL) and the more recent and extremely versatile (one minute granularity) JSON based download into one convenient spreadsheet which is highly adaptable to your needs and gives you full access to well-commented VBA source code, should you need to do any coding. 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 could still access the data, however Yahoo kindly removed the authentication requirements in April 2022. We have modified the worksheet appropriately. To minimize the impact on existing users, GetHistoricalPrices 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 GetHistoricalPrices in action!
GetHistoricalPrices–Import Price History into Excel
The GetHistoricalPrices spreadsheet
Gives you the capability to 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.4k
All VBA code is accessible, well commented and digitally signed by Algorithm Science.
Requires Windows and Excel 2007 or better.
In 2017 I incorporated the "gethistoricaldata" vba subroutine in my personal portfolio reporting and management tool to retrieve historical stock prices from Yahoo.
I am using your excellent historical stock data downloader
Thank you for the email, Andrew. It's an excellent product which I will continue to use
Seems to work from home ok... thanks for the response. Appreciate it. Love your macro!
Love your historical data spreadsheet. Fantastic.
It's been a fun hobby of mine to be able to download stock price data. Thank you
Thank you again for the excellent macro product that you offer!!
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).