How to Use the STOCKHISTORY Function in Microsoft Excel to Track Market Prices

Financial analysts, portfolio managers, and individual investors frequently need to evaluate historical asset performance, backtest investment strategies, or calculate moving averages in Microsoft Excel. Historically, pulling historical market quotes into a spreadsheet required tedious manual steps: downloading historical CSV tables from financial websites, writing complex Power Query web-scraping scripts, or subscribing to expensive third-party market data API connectors.

To automate investment research, Microsoft integrated the native STOCKHISTORY function into Microsoft Excel for Microsoft 365. STOCKHISTORY is a dynamic array function that connects directly to Refinitiv market data feeds, pulling decades of historical stock, exchange-traded fund (ETF), mutual fund, index, and currency pricing directly into your spreadsheet using a single formula.

Understanding the STOCKHISTORY Syntax

The STOCKHISTORY function features an extensive yet logical parameter structure:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], ...)

Parameters and Arguments Explained:

• stock: The ticker symbol of the financial security enclosed in quotes (e.g. "MSFT" or "XOM") or a cell reference containing an Excel Stocks Linked Data Type. You can also specify exchange prefixes (such as "LON:AZN" for the London Stock Exchange).
• start_date: The beginning date of the historical period (e.g. DATE(2025,1,1) or cell reference).
• end_date (Optional): The concluding date of the data query. If omitted, Excel returns only the single day specified by start_date.
• interval (Optional): Granularity of price intervals:
  • 0 = Daily (Default)
  • 1 = Weekly
  • 2 = Monthly
• headers (Optional): Controls whether column headings are printed:
  • 0 = No headers
  • 1 = Show basic headers (Default)
  • 2 = Show instrument identifier and headers
• properties (Optional): Defines which data metrics to return in adjacent columns:
  • 0 = Date
  • 1 = Close (Default)
  • 2 = Open
  • 3 = High
  • 4 = Low
  • 5 = Volume

Basic Usage: Pulling Daily Closing Prices

Suppose you want to extract the daily closing prices for Apple Inc. (AAPL) for the entire month of January 2026.

1. In cell A1, enter the start date: 01/01/2026.
2. In cell B1, enter the end date: 31/01/2026.
3. In cell A3, enter the formula:

   =STOCKHISTORY("AAPL", A1, B1, 0, 1, 0, 1)

4. Press Enter.

Because STOCKHISTORY is a dynamic array formula, it automatically “spills” a structured two-column table starting at cell A3: column A outputs the trading dates, and column B outputs the corresponding adjusted closing prices for every trading session.

Extracting Complete OHLCV Trading Data

For technical analysis and charting candlestick graphs, you need the complete Open, High, Low, Close, and Volume (OHLCV) dataset. You can request all metrics in your desired column order:

=STOCKHISTORY("MSFT", "2026-01-01", "2026-03-31", 0, 1, 0, 2, 3, 4, 1, 5)

Excel outputs a comprehensive six-column historical financial table:

Date	Open	High	Low	Close	Volume
02/01/2026	$425.50	$428.10	$423.80	$427.25	18,450,200
05/01/2026	$428.00	$431.50	$426.90	$430.80	21,120,500

Tracking Historical Currency Exchange Rates (Forex)

STOCKHISTORY is not limited to corporate equities; it also tracks historical foreign exchange currency conversions. Use standard ISO currency pair codes separated by a forward slash or colon:

# Track historical British Pound to US Dollar (GBP/USD) rates
=STOCKHISTORY("GBP/USD", TODAY()-30, TODAY(), 0, 1, 0, 1)

# Track Euro to British Pound (EUR/GBP)
=STOCKHISTORY("EUR/GBP", "2026-01-01", TODAY(), 1, 1, 0, 1)

This allows international finance teams to convert past cross-border invoices into reporting currencies at exact historical exchange rates.

Handling Common STOCKHISTORY Errors

When implementing STOCKHISTORY across financial models, anticipate these potential error triggers:

• #SPILL! Error: Occurs when cells below or to the right of your formula already contain text, numbers, or formatting. Clear any obstruction in the spill range to allow the table to expand.
• #BLOCKED! Error: Indicates that your Microsoft 365 account licence does not have access to live financial data types or your company IT policy restricts external internet data queries.
• #VALUE! Error: Triggers when the ticker symbol is misspelled, the security is not covered by Refinitiv, or start dates exceed available historical archives.
• Weekend and Holiday Gaps: Financial markets do not trade on weekends or bank holidays. STOCKHISTORY automatically omits non-trading days, ensuring data integrity without inserting false zero values.

By leveraging the native STOCKHISTORY function, you can build dynamic financial models, automated portfolio valuation trackers, and interactive investment dashboards directly inside Microsoft Excel with zero manual data entry.