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

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], …, [property5])

Arguments

ArgumentRequired?What it does
stockYesA ticker in quotes ("MSFT"), an exchange-qualified ticker ("XNAS:MSFT"), or a cell with a Stocks data type.
start_dateYesThe earliest date to fetch.
end_dateOptionalThe last date. Defaults to start_date (a single day).
intervalOptional0 daily (default), 1 weekly, 2 monthly.
headersOptional0 no headers, 1 headers (default), 2 instrument name plus headers.
property0 … property5OptionalWhich 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.

=STOCKHISTORY("MSFT","1/1/2026","3/31/2026",2) Explain

Example 2 — Date, Open, High, Low and Close

List the properties you want, in the order you want the columns.

=STOCKHISTORY("XNAS:MSFT",TODAY()-30,TODAY(),0,1,0,2,3,4,1) Explain

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.
Kyle Stott

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.