Quick Navigation

STOCKHISTORY Function

Excel 365+

Summary

The STOCKHISTORY function retrieves historical stock market data for financial instruments and returns it as a dynamic array. Perfect for financial analysis, trend tracking, and investment research directly within Excel spreadsheets.

Syntax

STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Parameters

Parameter	Type	Required	Description
stock	`Text/Stocks`	Yes	Financial instrument identifier ("MSFT" or XNAS:MSFT)
start_date	`Date`	Yes	Starting date for historical data
end_date	`Date`	No	Ending date (optional, defaults to start_date)
interval	`Number`	No	0=daily, 1=weekly, 2=monthly (default=0)
headers	`Number`	No	0=none, 1=headers, 2=instrument+headers (default=1)
property0	`Number`	No	First property: 0=Date,1=Close,2=Open,3=High,4=Low,5=Volume
property1	`Number`	No	Second property (same options as property0)
property2	`Number`	No	Third property (same options)
property3	`Number`	No	Fourth property (same options)
property4	`Number`	No	Fifth property (same options)
property5	`Number`	No	Sixth property (same options)

Using the STOCKHISTORY Function

STOCKHISTORY transforms Excel into a powerful financial analysis platform by pulling real-time historical market data. Use it to create dynamic stock charts, calculate returns, analyze volatility, or build investment dashboards without leaving your spreadsheet.

Common STOCKHISTORY Examples

Basic Daily Stock History

=STOCKHISTORY("MSFT","1/1/2023","12/31/2023")

Returns daily Date and Close prices for Microsoft from 2023 with headers.

Complete OHLCV Data

=STOCKHISTORY("AAPL",DATE(2023,1,1),TODAY(),0,1,0,2,3,4,1,5)

Full OHLCV data (Date,Open,High,Low,Close,Volume) from Jan 2023 to today.

Monthly Summary No Headers

=STOCKHISTORY("GOOGL",DATE(2022,1,1),DATE(2023,1,1),2,0,0,1,3,4)

Monthly Date,Close,High,Low for Google 2022 without headers.

Specific Exchange Weekly Data

=STOCKHISTORY("XNAS:TSLA","6/1/2023",TODAY(),1)

Weekly Tesla data from Nasdaq starting June 2023.

Frequently Asked Questions

Requires Microsoft 365 Personal, Family, Business Standard, or Business Premium subscription.

STOCKHISTORY updates after trading day completion. Current day data unavailable until market close.

Most index funds lack historical data availability through STOCKHISTORY, even if available as Stocks data type.

Use 4-character MIC code followed by colon and ticker: "XNAS:MSFT" for Microsoft on Nasdaq.

Spilled cells retain General format. No special formatting is applied by the function.

Common Errors and Solutions

#BUSY!

Cause: Data still loading from Microsoft servers

Solution: Wait a few moments or refresh the workbook

#FIELD!

Cause: Invalid property number (must be 0-5)

Solution: Use only 0(Date),1(Close),2(Open),3(High),4(Low),5(Volume)

#VALUE!

Cause: Invalid stock ticker or date format

Solution: Verify ticker symbol and use proper date format ("MM/DD/YYYY", TODAY(), or cell reference)

Subscription required

Cause: Using Microsoft 365 plan without STOCKHISTORY access

Solution: Upgrade to Personal, Family, Business Standard, or Premium subscription

Notes

Requires Microsoft 365 subscription with stock data access
Dynamic array function - spills automatically
Data updates after market close, not real-time intraday
First data point may precede start_date for weekly/monthly intervals
Property order determines column sequence in results
Faster performance using appropriate intervals (monthly for long periods)
Belongs to Lookup & Reference function category
Works best with automatic calculation enabled for TODAY() refreshes

Compatibility

Available in: Excel 365

Not available in: Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365+