The result is detailed historical pricing for Microsoft Corporation (“MSFT”) over the last 30 days. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The data appears in reverse chronological order because the STOCKHISTORY is nested inside the SORT function, which is configured to sort in descending order. This can be done with the STOCKHISTORY function, whose main purpose is to retrieve historical information for a financial instrument over time.
Simple example
STOCKHISTORY retrieves historical stock price information based on a given symbol and date range. For example, to return the closing price for Apple, Inc. on December 27, 2021, you can use STOCKHISTORY like this: The result is an array that includes headers, date, and close price:
STOCKHISTORY takes a number of function arguments, but in this simple case, we only need to provide symbol and start_date. All other arguments are optional with default values: end_date defaults to start_date, interval defaults to daily, headers are on by default, and Date and Close are the default information retrieved. For a more detailed discussion, see our STOCKHISTORY function page.
STOCKHISTORY configuration
In the more complex example shown above, we need to supply additional information. We start by providing a reference to the symbol in I5: Next, we add the start_date and end_date, which are calculated with the TODAY function: Notice that start_date is the current date minus I8, which is 30 in the example shown. This results in a date 30 days earlier than the date returned by TODAY. This works because Excel dates are just large serial numbers. The end_date is simply the value returned by TODAY – the current date. The next two arguments are interval and headers, and we leave these at their defaults, by using empty commas: Finally, we need to specify the properties we want. The STOCKHISTORY function supports six properties that are specified by number. In this case, we want all properties (Date, Close, Open, High, Low, and Volume), so we list out all 6 numbers in the order that we want them: This completes the configuration for STOCKHISTORY. When entered, the result will be an array that will spill into multiple cells. By default, STOCKHISTORY will return information in chronological order with older dates appearing first. Note that the number of rows returned will vary according to the number in cell I8, and the number of days the market is not open in the date range provided; STOCKHISTORY will simply omit dates on which the market is not open. The output also depends on the time of day. If the market is already closed, STOCKHISTORY will return information for the current date.
Sorting results
In the example shown, we want to sort results in reverse chronological order. To do this we can nest the STOCKHISTORY function inside the SORT function like this: Inside SORT, the array returned by STOCKHISTORY becomes the array argument. Sort_index is provided as 1 since dates are in the first column, and sort_order is given as -1, since we want to sort dates in descending order. The output of this formula is fully dynamic. If the symbol in cell I5 is changed, or the number for n in cell I8 is changed, the formula returns a new set of results.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
