How to Use the STOCKHISTORY Function in Microsoft Excel to Retrieve Historical Stock Prices

Financial analysts, portfolio accountants, and individual equity investors historically relied on third-party market data plugins, manual CSV export routines, or complex VBA web-scraping scripts to import historical stock quotes into Microsoft Excel. With the introduction of modern dynamic array functions in Microsoft 365, Excel features native integration with cloud financial data feeds via the STOCKHISTORY function. This formula allows you to pull opening, closing, high, low, and volume figures directly into your spreadsheet across custom date ranges with a single spilling formula.

Mastering the STOCKHISTORY syntax enables automated portfolio tracking, volatility modeling, and historical asset comparisons without external software subscriptions.

Software Requirements and Financial Data Sources

The STOCKHISTORY function is available exclusively to subscribers using Microsoft 365 (Personal, Family, or Enterprise) across Excel for Windows, Excel for Mac, and Excel for the Web. Market quotes, trading volumes, and historical pricing indices are provided directly through Microsoft’s partnership with Refinitiv (an LSEG business). Because data queries are fetched from Microsoft cloud services in real time, an active internet connection is required when recalculating workbooks.

Understanding the Syntax and Arguments of STOCKHISTORY

The function accepts up to eleven parameters to customize the output structure:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties0], [properties1], ...)

The parameters operate as follows:

• stock (Mandatory): The ticker symbol enclosed in quotation marks (e.g. "MSFT" or "LON:AZN") or a direct cell reference containing an active Stock Data Type.
• start_date (Mandatory): The earliest date for historical retrieval, entered as a date serial or via the DATE(year, month, day) function.
• end_date (Optional): The concluding date for the historical series. If omitted, Excel defaults to retrieving data for the start date alone.
• interval (Optional): Determines data aggregation: 0 for Daily (default), 1 for Weekly, and 2 for Monthly.
• headers (Optional): Specifies table labels: 0 for No header, 1 for Basic header (Date and Property), and 2 for Full header including the instrument name and ticker.
• properties (Optional): Numerical indices defining which columns to return:
  • 0: Date
  • 1: Close
  • 2: Open
  • 3: High
  • 4: Low
  • 5: Volume

Basic Example: Fetching Daily Closing Prices

To pull daily closing prices for Microsoft Corporation across the month of January 2026:

1. Select cell A1 on an empty worksheet.
2. Enter the following formula:

=STOCKHISTORY("MSFT", DATE(2026, 1, 1), DATE(2026, 1, 31), 0, 1, 0, 1)

3. Press Enter. Excel connects to the cloud financial feed and spills the results downward across two columns: column A displays trading dates (excluding weekends and exchange holidays), and column B displays adjusted closing prices.

Constructing a Comprehensive OHLCV Trading Table

For technical analysis and candlestick charting, retrieve a full Open, High, Low, Close, and Volume table over a rolling 60-day window using dynamic dates:

1. Select cell A1 and enter the formula:

=STOCKHISTORY("AAPL", TODAY()-60, TODAY(), 0, 2, 0, 2, 3, 4, 1, 5)

2. Notice the sequence of property parameters: 0, 2, 3, 4, 1, 5. This orders your columns logically from left to right as Date, Open, High, Low, Close, and Volume.
3. Setting the header argument to 2 instructs Excel to output a clean title block identifying “Apple Inc. (AAPL)” at the top of your dataset.

Querying International Exchanges and Currency Exchange Rates

The STOCKHISTORY function is not limited to standard US markets (NYSE and NASDAQ). You can query international stock exchanges by prepending the market identifier code (MIC):

• London Stock Exchange (LSE): =STOCKHISTORY("LON:SHEL", DATE(2026,1,1), TODAY())
• Toronto Stock Exchange (TSX): =STOCKHISTORY("TSE:SHOP", DATE(2026,1,1), TODAY())
• Foreign Exchange (FX) Currency Pairs: Retrieve historical currency exchange rates by formatting currency pairs with a forward slash:

=STOCKHISTORY("GBP/USD", DATE(2026, 1, 1), TODAY(), 0, 1, 0, 1)

Troubleshooting Common STOCKHISTORY Errors

If your formula returns an error code, review these standard diagnostic checks:

• #BUSY!: Indicates Excel is waiting for the cloud market endpoint to respond. Allow several seconds for the dynamic array to calculate.
• #VALUE!: Typically indicates an unrecognised ticker symbol, a reversed date range (where start date occurs after end date), or a date falling on a market holiday where no preceding trading data exists.
• #SPILL!: Occurs when existing text, numbers, or merged cells occupy the worksheet cells directly below or to the right of your formula, blocking the array from expanding. Clear adjacent cells to allow data to spill cleanly.