Exceljet

Quick, clean, and to the point

Get stock price last n days

Excel formula: Get stock price last n days
Generic formula 
=SORT(STOCKHISTORY(symbol,TODAY()-n,TODAY(),,,0,1,2,3,4,5),1,-1)
Summary 

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.

The STOCKHISTORY function is only available in Excel 365 
Explanation 

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:

Apple's close price on December 27, 2022

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:

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.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Author 
Dave Bruns

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.