Function STOCKHISTORY and its Troubling NA errors

The relatively new function STOCKHISTORY is terrific in most respects. Of course, it’s not without deficiencies. Principally, it delivers data only for dates that the source possesses, that source being Refinitiv. When a security did not trade for whatever reason or Refinitiv does not have the data, the function reports NA. This NA error is most troublesome when making calculations across dates and securities.

Take for example a portfolio of stocks going across columns and dates and another column spilling on down the rows. If the dates reported by STOCKHISTORY are different for the various securities then they won’t line up! If a stock is halted for a few days, the problem is just worsened. How frustrating.



Note that it is not possible to simply use STOCKHISTORY copied down to all rows of a long list of dates for each security because Excel will quickly report a #CALC! error as there eventually occurs too many calls on the source.

What can be done? Here is a complex formula that accounts for every single calendar day between the relevant dates, even weekends, holidays and no-trade days. As long as the Start date does not cause an error, this formula fills in all the data. The formula reports the Data Type data for the day in the row if available OR it reports the last valid data. If only Microsoft would include a new argument in function STOCKHISTORY that could if desired report the all dates and the last valid data between the Start Date and the End date this mind-bending formula would be unnecessary.