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:


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.

Generic formula



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:


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:


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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.