Excel STOCKHISTORY Function in 2026: Pull Live Historical Financial Data Into Any Spreadsheet
Published: June 8, 2026
For years, getting historical stock prices into an Excel spreadsheet meant exporting CSV files from financial websites, cleaning them up, and hoping the data was accurate. Not anymore. The STOCKHISTORY function, available in Microsoft 365, pulls real-time historical stock price data directly into your spreadsheet — no API key, no web scraping, no manual imports. In this guide, you will learn exactly how STOCKHISTORY works, how to use it to build everything from simple price lookups to sophisticated multi-stock performance dashboards, and what its limitations are so you can plan around them.
What Is the STOCKHISTORY Function?
STOCKHISTORY is a dynamic array function that returns a table of historical price data for a financial instrument — stocks, ETFs, mutual funds, and currencies. It uses the same data source as Excel's built-in Stocks data type (powered by Refinitiv / LSEG) and spills its results into as many rows and columns as needed.
The basic syntax is:
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], …)
Arguments:
stock: The ticker symbol as a text string, e.g. "MSFT" or a cell reference containing the ticker.
start_date: The first date in the range. Can be a date value, cell reference, or date formula.
end_date (optional): The last date. Defaults to the current date if omitted.
interval (optional): 0 = daily (default), 1 = weekly, 2 = monthly.
headers (optional): 0 = no headers, 1 = date headers (default), 2 = stock code + date headers.
property0, property1, … (optional): Which price fields to return: 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume.
Basic Examples to Get Started
Example 1: Last 30 Days of Microsoft Closing Prices
=STOCKHISTORY("MSFT", TODAY()-30, TODAY(), 0, 1, 0, 1)
This returns a two-column spill range: Date and Close Price for every trading day in the past 30 days. The function automatically skips weekends and market holidays.
Example 2: Full OHLCV Data for a Custom Date Range
=STOCKHISTORY("AAPL", "2026-01-01", "2026-03-31", 0, 1, 0, 1, 2, 3, 4, 5)
Returns all six columns — Date, Close, Open, High, Low, Volume — for Apple stock for Q1 2026.
Example 3: Monthly Prices for the Past Year
=STOCKHISTORY(A2, DATE(2025,6,1), TODAY(), 2, 1, 0, 1)
Where A2 contains a ticker symbol. Returns monthly close prices, useful for building year-on-year performance charts.
Property Reference Table
Here is a quick reference for the property numbers used in STOCKHISTORY:
If you omit all property arguments, STOCKHISTORY defaults to returning just Date (0) and Close (1).
Building a Multi-Stock Comparison Dashboard
One of the most powerful uses of STOCKHISTORY is comparing the performance of multiple stocks over the same time period. Here is a simple structure to build this in Excel:
Set up a header row with your ticker symbols in B1, C1, D1, etc. (e.g., MSFT, AAPL, GOOGL).
In A2 enter your start date. In A3, use =STOCKHISTORY(B$1, $A$2, TODAY(), 2, 0, 0) to get the monthly dates from the first ticker.
In B3, enter: =STOCKHISTORY(B$1, $A$2, TODAY(), 2, 0, 1) for close prices — this returns only prices without the date column (dates are already in column A).
Copy B3 to C3, D3, etc. Each column automatically pulls data for the ticker in its header row.
Add an index column to show percentage return from the start date: =B3/B$3-1. Format as percentage.
Insert a line chart using the index columns to create a clean percentage-return comparison chart.
Currency and Exchange Rate Data
STOCKHISTORY is not limited to equities. You can retrieve historical currency exchange rate data using standard forex pair notation. For example:
=STOCKHISTORY("USD/EUR", DATE(2026,1,1), TODAY(), 2, 1, 0, 1)
This returns monthly USD-to-EUR exchange rates since January 2026. The same function works for GBP/USD, JPY/USD, and most major currency pairs. This is extremely useful for finance teams doing foreign exchange exposure analysis or converting historical revenues to a base currency.
Limitations to Be Aware Of
Microsoft 365 subscription required. STOCKHISTORY is not available in Excel 2019 or older perpetual licences.
Data is delayed. Prices are not real-time. The data is typically delayed by 15-20 minutes for intraday, and historical data may have a 24-hour lag for the most recent trading day.
No intraday granularity. The minimum interval is daily (interval = 0). Hourly or minute-by-minute data is not available.
Spill range conflicts. Because STOCKHISTORY returns a spill range, ensure there are enough empty cells below and to the right of your formula. If another value blocks the spill, Excel returns a #SPILL! error.
Internet connection required. The function calls a live data service. Without an internet connection, or if the Refinitiv/LSEG service is temporarily unavailable, STOCKHISTORY returns a #GETTING_DATA or #VALUE! error.
Limited historical depth. Most tickers support up to 20 years of daily history. Some less-traded instruments may have shorter histories available.
Conclusion: Professional Financial Analysis Without Leaving Excel
STOCKHISTORY removes one of the most tedious tasks in financial analysis: sourcing and cleaning historical price data. With a single formula, you have clean, structured data ready for analysis, charting, and modelling. Combined with Excel's dynamic array engine, it becomes the foundation for multi-stock dashboards, performance attribution models, and currency analysis workbooks that update automatically every time you open them.
Try it now: In any Microsoft 365 Excel workbook, type =STOCKHISTORY("MSFT", TODAY()-90) in an empty cell and press Enter. Watch as 90 days of Microsoft stock history appears automatically. From there, the analysis possibilities are unlimited.
