Exceljet

Quick, clean, and to the point

Get stock price on specific date

Excel formula: Get stock price on specific date
Generic formula 
=STOCKHISTORY(B5,date,,0,0,1)
Summary 

To get the latest close price of a stock with a formula, you can use the STOCKHISTORY function. In the example shown, the formula in cell D5, copied down, is:

=STOCKHISTORY(B5,$F$5,,0,0,1)

The result is the close price for each symbol in the table on July 1, 2021. Note that if there is no close date on the date given, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page.

Explanation 

In this example, the goal is to retrieve the close price on July 1, 2021 for each symbol shown in column B. This can be done with the STOCKHISTORY function, whose main purpose is to retrieve historical information for a financial instrument over time. In many configurations, STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. However, in this case, we want only a single result for each symbol. The formula is tied to the date entered in cell F5, which can be changed at any time.

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

We only need to provide start_date because the end_date will automatically default to the start_date if not provided. This gives us the basic information we want, but we need to remove the date and the header, since we only want the close price. We start by providing a reference to the symbol in B5 and the date in cell F5:

=STOCKHISTORY(B5,$F$5

Notice $F$5 is entered as an absolute reference to prevent this reference from changing as the formula is copied down the table. The final formula looks like this:

=STOCKHISTORY(B5,$F$5,,0,0,1)

In this version, end_date is left blank, interval is set to 0 for daily, headers is set to 0 (no headers), and the final argument is property1, which is given as 1 to retrieve the price. More on STOCKHISTORY properties here.

As the formula is copied down, STOCKHISTORY retrieves the close price for each symbol in column B on July 1, 2021. If the date in cell F5 is changed, STOCKHISTORY retrieves a new set of close prices.

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.