Get stock price last n days

In this example, the goal is to retrieve historical stock price information for a given stock, provided as a ticker symbol like "MSFT", "AAPL", "MMM", etc.  over the past n days, where n is a variable that can be changed as desired. In addition, the data should be sorted in reverse chronological order, with the latest information appearing first.

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:

=STOCKHISTORY("AAPL","27-Dec-2021") // close price on Dec 27

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: 

=STOCKHISTORY(I5

Next, we add the start_date and end_date, which are calculated with the TODAY function:

=STOCKHISTORY(I5,TODAY()-I8,TODAY()

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:

STOCKHISTORY(I5,TODAY()-I8,TODAY(),,,

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:

STOCKHISTORY(I5,TODAY()-I8,TODAY(),,,0,1,2,3,4,5)

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:

=SORT(STOCKHISTORY(I5,TODAY()-I8,TODAY(),,,0,1,2,3,4,5),1,-1)

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.