# Get stock price last n days

=SORT(STOCKHISTORY(symbol,TODAY()-n,TODAY(),,,0,1,2,3,4,5),1,-1)

To get the daily closing stock price for the last n days (i.e. last 7 days, last 14 days, last 30 days, etc.) you can use a formula based on the STOCKHISTORY function. In the example shown, the formula in cell D4 is:

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

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.

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: e*nd_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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.