STOCKHISTORY

Pull historical stock prices and trading data directly into Excel with the STOCKHISTORY function.

Financial

Excel 365 only

Google Sheets Not supported

Syntax

=STOCKHISTORY(symbol, start_date, end_date, interval, headers) Returns: Array

Arguments

Argument	Required	Description
symbol	Yes	The stock or security symbol for which you want to retrieve historical data.
start_date	Yes	The start date for the historical data range.
end_date	Yes	The end date for the historical data range.
interval	Yes	The interval at which the data is provided (e.g., daily, weekly, monthly).
headers	Yes	A boolean value (TRUE/FALSE) that indicates whether headers should be included in the output. TRUE includes headers, FALSE excludes headers.

About

STOCKHISTORY retrieves historical stock prices, trading volumes, and other market data for any publicly traded security. Enter a ticker symbol and date range, and Excel pulls opening prices, closing prices, highs, lows, and volume directly from financial data sources. The function returns an array that automatically fills adjacent cells with your results.

Use STOCKHISTORY when you're analyzing stock performance over time, comparing multiple securities, or building investment tracking worksheets. It's faster than manually copying data from financial websites and updates automatically when you reopen your workbook. The function works with daily, weekly, or monthly intervals, and you can choose which data columns to display. For real-time stock quotes, check out the Stocks data type feature in Excel. For currency exchange rates, try WEBSERVICE.

Keep in mind that STOCKHISTORY requires a Microsoft 365 subscription and an internet connection. The data comes from LSEG Data & Analytics and typically updates after each trading day closes, so you won't get live intraday prices.

Examples

Tracking Tesla stock for a quarter

=STOCKHISTORY("TSLA", DATE(2025,1,1), DATE(2025,3,31), 0, 1)

Retrieves daily stock data for Tesla from January 1 to March 31, 2025, including date and closing price columns with headers. The 0 indicates daily intervals, and 1 shows column headers.

Monthly Apple stock performance

=STOCKHISTORY("AAPL", DATE(2024,1,1), DATE(2024,12,31), 2, 1, 0, 1, 2, 3, 4, 5)

Gets monthly data for Apple throughout 2024 with all available columns: date, close, open, high, low, and volume. The 2 sets monthly intervals, perfect for longer-term trend analysis.

Year-to-date Microsoft performance

=STOCKHISTORY("MSFT", DATE(2026,1,1), TODAY(), 1, 1)

Pulls weekly Microsoft stock data from the start of the year until today. Using TODAY() makes the data refresh automatically when you open the workbook. The 1 interval gives you weekly snapshots.

Simple price history without headers

=STOCKHISTORY("NVDA", DATE(2025,10,1), DATE(2025,10,31), 0, 0, 0, 1)

Returns just dates and closing prices for NVIDIA in October 2025, with no header row. The 0 for headers keeps the output clean, and specifying properties 0 and 1 shows only date and close columns.

Watch out for

⚠

Using index funds or ETFs

Many popular index funds like S&P 500 trackers lack historical data in STOCKHISTORY, even though they work with the Stocks data type.

→ Stick to individual company stocks. If you need index data, consider downloading it from financial websites or using constituent stocks instead.

⚠

Interval numbers instead of names

Excel expects numeric codes for intervals (0 for daily, 1 for weekly, 2 for monthly), but it's easy to forget which number means which interval.

→ Use 0 for daily data, 1 for weekly, and 2 for monthly. You can't type "daily" or "weekly" as text like in older examples.

⚠

Spilled array gets deleted

When you delete a STOCKHISTORY formula, Excel leaves behind the formatted cells from the spilled array, creating ghost data that looks real but won't update.

→ Always clear the entire spilled range after deleting the formula. Look for the blue border around the array to see which cells to clear.

⚠

Expecting real-time prices

STOCKHISTORY shows historical data that updates after trading days close, not live prices during market hours.

→ For current prices, use the Stocks data type feature instead. STOCKHISTORY is designed for historical analysis, not real-time trading decisions.

Tips & notes

The interval parameter accepts numbers only: 0 for daily, 1 for weekly, 2 for monthly. The headers parameter also uses numbers: 0 for no headers, 1 for headers, 2 for both ticker identifier and headers. You can customize which data columns appear by adding property parameters (0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume). If you omit the property parameters, Excel defaults to showing date and closing price. Market identifiers like 'XNAS:MSFT' let you specify which exchange to use when ticker symbols overlap across markets.

Common questions

Can I use STOCKHISTORY with cryptocurrencies?

STOCKHISTORY works with publicly traded securities available in LSEG Data & Analytics. Some crypto-related stocks and ETFs may work, but direct cryptocurrency prices typically aren't available through this function.

Why do my dates start earlier than I specified?

When using weekly or monthly intervals, Excel may return dates before your start date. For example, a monthly interval starting December 31 might return December 1 data. This is normal behavior, the function adjusts to the interval boundaries.

Does STOCKHISTORY work in Excel for Mac or web?

Yes, STOCKHISTORY works across all Excel platforms (Windows, Mac, web) as long as you have a qualifying Microsoft 365 subscription and internet connection.

How do I stop the array from spilling into cells I'm using?

You can't control where the array spills, it automatically fills cells based on the data returned. Plan your worksheet layout to leave space for the spilled results, or place the formula in an area with empty adjacent cells.