STOCKHISTORY Function
Retrieves historical price data for a stock, fund, currency or index.
Category
Information
Available in
Microsoft 365 (subscription) only
Returns
A spilled table of prices
Overview
STOCKHISTORY pulls historical market data from Microsoft's online service straight into your sheet — daily, weekly or monthly — and spills it as a table.
By default you get a Date and a Close column with headers. Ask for Open, High, Low and Volume too by listing property numbers.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| stock | Yes | A ticker in quotes ("MSFT"), an exchange-qualified ticker ("XNAS:MSFT"), or a cell with a Stocks data type. |
| start_date | Yes | The earliest date to fetch. |
| end_date | Optional | The last date. Defaults to start_date (a single day). |
| interval | Optional | 0 daily (default), 1 weekly, 2 monthly. |
| headers | Optional | 0 no headers, 1 headers (default), 2 instrument name plus headers. |
| property0 … property5 | Optional | Which columns, in order: 0 Date, 1 Close, 2 Open, 3 High, 4 Low, 5 Volume. Default is 0, 1. |
Examples
Example 1 — Monthly closing prices for a quarter
Three rows — one per month — plus a header row.
Example 2 — Date, Open, High, Low and Close
List the properties you want, in the order you want the columns.
Leave enough empty cells below and to the right, or the result shows #SPILL!.
Common mistakes
- #BLOCKED! or #CONNECT! STOCKHISTORY needs an internet connection and a Microsoft 365 subscription signed in to Excel.
- Fewer rows than expected. Markets close on weekends and holidays, so daily results skip those dates.
Notes
- Prices are in the instrument's own currency and can be delayed; check Microsoft's data disclaimer before relying on them.
- Because it needs Microsoft's live market data, STOCKHISTORY can't run in our practice Sandbox.
- Related functions: FILTER, TAKE, SORT.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.