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. 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: The result is an array that includes headers, date, and close price:
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
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.
