If you wish to download historical stock prices into Excel, Excel 365 users now have the option to load data using the STOCKHISTORY Function provided by Microsoft. But how useful is STOCKHISTORY? In the table below, we compare the functionality of STOCKHISTORY against our HistoricalPrices Add-in and the GetHistoricalPrices products, which use Yahoo Finance as the data source. The HistoricalPrices Add-in will give you a form driven insertion of historical prices available across all your worksheets. The GetHistoricalPrices worksheet will provide you with the macros and VBA source code necessary to build your own historical prices insertion of historical data.
Comparing STOCKHISTORY, HistoricalPrices and GetHistoricalPrices
STOCKHISTORY is very convenient, but limited in scope. Commodities and Mutual Funds are not included. Data intervals smaller than a day are not included, nor are adjusted close prices (prices adjusted for dividends). Also concerning is the fact that STOCKHISTORY data is not adjusted for spin-offs, which can lead to apparent step changes in prices, which don't really exist. Examine this data for AT&T, for example:
Comparing AT&T Data STOCKHISTORY (left), Yahoo data (right)
The discrepancy here is due to the AT&T spin-off of Warner Brothers on April 11th 2022, which is not accounted for in STOCKHISTORY. Any kind of backtest against this data could lead to problems. That said, spin-offs are unusual, and STOCKHISTORY does adjust for splits correctly, and splits are a much more common corporate action.
It's clear that STOCKHISTORY currently doesn't quite provide the flexibility of Yahoo! provided data, but it is a welcome addition to Excel world and will be adequate for many uses. However, sophisticated users should consider some of the more flexible tools available before relying on STOCKHISTORY alone. All the tools mentioned can be used together.