Download historical stock price data into Excel
with the HistoricalPrices Add-in
If you want to download historical stock price data into Excel, there's a lot to like about the Yahoo Finance data. For one thing it covers all major stock exchanges. It includes many different types of security--stocks, ETFs, mutual funds, indices, commodity futures, currencies and cryptocurrencies. Also, there are no subscriptions to pay, at least for now. Our latest add-in HistoricalPrices combines our legacy CSV download product and the more versatile JSON based download into one convenient utility. which is highly adaptable to your needs. Also included is the PriceOnDate user defined function (UDF) which allows you to access the price of any security on any given date.
Watch the video below to see HistoricalPrices in action!
HistoricalPrices–Import Price History into Excel
You won't need to know any VBA to get historical data onto your spreadsheets. It is so easy to use. Just enter the destination of the data and the ticker, what range and interval you need and away you go. You can even specify what columns you need and make many other fine adjustments.
If you prefer to use VBA, of course, we have you covered. The core data-fetch functions from the previous HistoricalPrices releases are still available. See here.
You can set the candlestick period (data interval) to
- 1, 2, 5 or 15 minutes
- 1 hour
- 1 day
- 1 week
- 1 or 3 months
The uncluttered HistoricalPrices ribbon menu has a button to bring up the interactive userform, and several refresh options, including the ability to add a refresh button to any of your worksheets
Or you can have any of the HistoricalPrices data regions refresh automatically, every candle interval.
The easy-to-grasp interactive userform gives you complete control over formatting the data when creating or editing a HistoricalPrices data region. For many fields the defaults will be fine, but you have the ability to set:-
- The destination placement of the data
- The ticker symbol
- The type of data (prices, dividends or splits)
- The time range of the data
- The data interval (candlestick width)
- Ascending or descending date order
- Do you wish to display market time or offset the time somehow?
- Interpolate prices into null (inactive) periods
- Filter out dates before a fixed date, or after another fixed date
- Which fields (Open, AdjClose etc.) do you require and in what order?
- Do you want headers?
- Do you want automatic refreshes in addition to manual?
All these settings can be on the form or referred to the spreadsheet. Just click on the form titles to bring up a help page on how to use the form.
Simple yet versatile, you can use this function when you just need a single price.
- Give it a date and a ticker and this simple function will give you the Adjusted Close price for that day.
- Give it just a ticker symbol and it will give you a recent price (very recent for real-time exchanges)
- You can also specify "High", "Low", "Open", "Close", "Volume" or "AdjClose" as a third parameter.
Ordering is through PayPal, we don't have access to any of your personal or credit card information, except for your email address so that we can send a link to the download. Algorithm Science is authenticated by Thawte and the program itself is code signed with a Thawte/DigiCert certificate to prevent tampering.
The program does not gather any information from your computer. There are links to this website for help, and occasional connections to Yahoo! to import data, that's all.
If you can't load the add-in, or it doesn't work the way we describe, we will refund your money--just email us at firstname.lastname@example.org . If something should break and the data becomes unavailable within a period of 60 days of your purchase, we will either fix the problem or we will refund your money.
"...the add-in is perfectly usable."
"This new Add-in is really awesome.
- I really love the PriceOnDate() feature which is essentially like the Office 365 stock price feature which is not available on my standalone version of excel.
- Drawing Historical Data for a Single Stock Is much more flexible compared to the earlier emulator. "
"Wanted to drop you a note to applaud your new Add-in. It works great. Working on recoding my existing sheets to use the add-in, rather than VBA code. I think when I’m done with conversion it will work great. I will send you a copy of my sheet."
Comparing the HistoricalPrices Add-in to the GoogleFinance() and STOCKHISTORY() functions
You may have heard of the STOCKHISTORY function which Microsoft announced in a Beta program in June 2020, but which will not be available to most users "for several months". This future feature of Excel is the result of many requests from Excel users. Google Sheets already has a function called GoogleFinance which has the capability to bring in stock history, so there is an element of catch-up.
If you are making comparisons, we have put together a chart to help you. HistoricalPrices is not compatible with Google Sheets, but we've included it in the comparison in case you are interested. In general, you will find you can do much more with HistoricalPrices because the Yahoo! data is more expansive than either MSN or Google Finance data. However, its doesn't have to be an either/or decision. STOCKHISTORY will (if it ever sees the light of day) make a useful supplement to HistoricalPrices so if there is a problem with either provider, you may still be able to get data.
Don't want an Add-in?
The older version of HistoricalPrices is still available. It is a macro enabled workbook not an add-in and it has most of the functionality of the add-in, but not all. Click here for details.