STOCKHISTORY function - Microsoft Support (2024)

The STOCKHISTORY function retrieves historical data about a financial instrument and loads it as an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER.

Important:The STOCKHISTORY function requires a Microsoft 365 Personal, Microsoft 365 Family, Microsoft 365 Business Standard, or Microsoft 365 Business Premium subscription.

Technical details

Syntax

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

The STOCKHISTORY function syntax has the following arguments:

Notes

• The STOCKHISTORY function does not stamp a format on the cells that it spills into. If you delete the formula, the cells that it filled have the General format.

• When you enter the property arguments, you type a number for each property 0 through 5, in the order you want to see them. The value you enter for each property corresponds to the property number. For example, to include Date, Open, and Close, enter 0,2,1. These properties are defined as follows:

  Value	Property	Definition
  	Date	If interval is daily or weekly, the first valid trading day in the period. If interval is monthly, the first day of the month, regardless of if it was a trading day.
  1	Close	Closing price on the last trading day in the period
  2	Open	Opening price on the first trading day in the period
  3	High	Highestday’s high in the period
  4	Low	Lowestday’s low in the period
  5	Volume	Number of shares traded during the period

• The STOCKHISTORY function belongs to the

• Date arguments can be a date enclosed in double quotes (e.g. "01-01-2020") or a formula (e.g. TODAY()) or a cell reference to a cell with a date.

• Date returned may be earlier than the date provided. For example, if December 31, 2019 is provided as the start date and interval is monthly, then December 1, 2019 will be returned as that is the start date for the period requested.

• Please note that while some financial instruments may be available as Stocks data types, the historical information will not be available. For example, this is the case for most popular Index Funds including the S&P 500.

  See Also

  Best Excel Functions for Inventory ManagementReal-time stock quotes in Excel - MarketXLSAbout the Stocks financial data sourcesHow To Get Real Time Market Data For US Markets · Market Data

Tips

• If you want to get the highest high over a 3-month period, it is faster to use a monthly interval than a daily or weekly interval. For example, =MAX(STOCKHISTORY("XNAS:MSFT", "1/1/2022", "3/1/2022", 2, 0, 3)) will calculate the maximum value of 3 datapoints (one for each month), data only with no headers, for the highest trading value for each month. If instead the formula used a weekly or daily interval, you would get the same result but there would be many more datapoints used in the calculation which can lead to reduced performance.

• If you want to see a 52-week high or low, it is often faster to use aStocks data type, which has those properties readily available. For example, convert "xnas:msft" to a stock data type in cell A1, and in cell B1 you can write the formula =A1.[52 week high] to get the value. You can also configure your workbook to automatically refresh that value as described here.

• STOCKHISTORY, in showing historical data, generally only updates after a trading day completes. This means that you cannot use STOCKHISTORY to get data for today's trading details until after the market has closed or after the day has completed depending on the market.

• If you use STOCKHISTORY with a function that automatically updates (like TODAY) and if your workbook has automatic calculation enabled, then STOCKHISTORY will automatically refresh the data when you open the workbook. This update will happen in the background, and you can edit your workbook as desired while this update is underway. If yourworkbook uses large number of STOCKHISTORY function calls, this background update will continue as long as needed to update the data. You may close yourworkbook at any time during this process if you wish.