STOCKHISTORY function

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.

Note: This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers.

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:

Argument

Description

stock

Function returns historical price data about the financial instrument corresponding to this value. Enter a ticker symbol in double quotes (e.g., "MSFT") or a reference to a cell containing the Stocks data type. This will pull data from the default exchange for the instrument. You can also refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., "XNAS:MSFT"). Learn more about our data sources.

start_date

The earliest date for which data is retrieved. Note that if interval is not 0 (daily), the first data point may be earlier than the start_date provided - it will be the first date of the period requested.

end_date

Optional. The latest date for which data will be retrieved. Default is TODAY.

interval

Optional. Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. Default is 0.

headers

Optional. Specifies whether to display headings as follows: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. Default is 1 (i.e, show headers). When included, headers are rows of text that are part of the array returned from the function.

property0 - property5

Optional. The columns that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. If any of them is present, only the indicated columns are returned in the order provided. Default is 0,1 (i.e., Date and Close).

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:
     

    Property

    Definition

    Date

    First valid trading day in the period 

    Close

    Closing price on the last trading day in the period 

    Open

    Opening price on the last trading day in the period 

    High

    Highest price of the highest day’s high in the period 

    Low

    Lowest price of the lowest day’s low in the period 

    Volume

    Volume traded during the period

  • The STOCKHISTORY function belongs to the Lookup & Reference family of functions.

  • 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.

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×