The result is the latest available close price in the current month. If there is no data yet in the current month, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page. The STOCKHISTORY function 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: The result is an array that includes headers, date, and close price:
To get a closing price for today, we can use the TODAY function instead of a fixed date. However, this formula is a bit fragile. If the current date (today) is a holiday or weekend, or if the market is open but not yet closed, STOCKHISTORY will return a #VALUE error. One way to handle this problem is to set the interval argument in STOCKHISTORY to monthly instead of daily: The last argument, 2, sets interval to monthly (2) instead of the default of daily (0). The result is the latest available close price in the current month. To adapt this example to work in the example, as shown above, we also need to get rid of the date and the header, since we only want the close price. We can do that by adjusting arguments like this: Here, the zero after interval (2) sets the headers argument to “no headers”. The final 1 is a property setting that tells STOCKHISTORY to return the close price only. See the table here for more information about properties available to the STOCKHISTORY function. When this formula is copied down the table, the result is the last available close price for each symbol in the current month. Note the formula will fail with a #VALUE error if there is not yet close price data in the current month. See below for a workaround.
Workarounds
The above formula works fine as long as there is at least one close price in the current month. However, if there is no price data yet in the current month, the formula will return a #VALUE error. As a workaround, we can modify the formula to retrieve the last week of close prices for a given symbol, then use the LOOKUP function to get the last value in the list: This works because LOOKUP has some unique behaviors that make it useful for retrieving the last value in a list. Essentially, we are asking LOOKUP to find a value we know can’t exist (TODAY()+1). LOOKUP always operates in approximate match mode, assuming data is sorted. It scans to the end of the values looking for TODAY+1 and when that value isn’t found, it returns the last value in the second column. This is an example of the BigNum concept. If you want to retrieve the date as well (as a reference for the price that comes back) you can use a more involved formula based on the LET function: Rather than rely on approximate matching, this formula explicitly requests the last result. First, the LET function stores results from STOCKHISTORY in a variable called results. Next, the ROWS function counts the rows in results and feeds this number into the INDEX function as row_num. Finally, with results provided as array, and column_number hardcoded as 0, INDEX returns the last row in results. This row contains two values: the date and the close price on that date.
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.
