• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Function STOCKHISTORY and its Troubling NA errors

Excel Version
  1. 365
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.

stock spill.xlsx
ABCDEFGH
212/30/2022Start
31/12/2023End
11
12CANADIAN NATURAL RESOURCES LIMITED (XTSE:CNQ)Light Sweet Crude Oil
13DateCloseDateClose
1412/30/2022$ 75.1912/30/2022$ 80.26
151/3/2023$ 71.291/2/2023#N/A
161/4/2023$ 70.471/3/2023$ 76.93
171/5/2023$ 70.621/4/2023$ 72.84
181/6/2023$ 73.291/5/2023$ 73.67
191/9/2023$ 73.881/6/2023$ 73.77
201/10/2023$ 74.371/9/2023$ 74.63
211/11/2023$ 74.761/10/2023$ 75.12
221/12/2023$ 76.091/11/2023$ 77.41
231/12/2023$ 78.39
Sheet2
Cell Formulas
RangeFormula
D13:E22D13=STOCKHISTORY(D12,A2,A3)
G13:H23G13=STOCKHISTORY(G12,A2,A3)
Dynamic array formulas.


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.

stock spill.xlsx
ABCDEFG
212/30/2022Start
31/12/2023End
27
28CANADIAN NATURAL RESOURCES LIMITED (XTSE:CNQ)Light Sweet Crude Oil
29Fri 12/30/2022$ 75.19$ 80.26
30Sat 12/31/2022$ 75.19$ 80.26
31Sun 1/1/2023$ 75.19$ 80.26
32Mon 1/2/2023$ 75.19$ 80.26
33Tue 1/3/2023$ 71.29$ 76.93
34Wed 1/4/2023$ 70.47$ 72.84
35Thu 1/5/2023$ 70.62$ 73.67
36Fri 1/6/2023$ 73.29$ 73.77
37Sat 1/7/2023$ 73.29$ 73.77
38Sun 1/8/2023$ 73.29$ 73.77
39Mon 1/9/2023$ 73.88$ 74.63
40Tue 1/10/2023$ 74.37$ 75.12
41Wed 1/11/2023$ 74.76$ 77.41
42Thu 1/12/2023$ 76.09$ 78.39
Sheet2
Cell Formulas
RangeFormula
A29:A42A29=SEQUENCE(A3-A2+1,,A2)
D29:D42,G29:G42D29=XLOOKUP($A29#,FILTER(INDEX(STOCKHISTORY(D28,$A$2,$A$3,0,0),,1),ISNUMBER(INDEX(STOCKHISTORY(D28,$A$2,$A$3,0,0),,2))),FILTER(INDEX(STOCKHISTORY(D28,$A$2,$A$3,0,0),,2),ISNUMBER(INDEX(STOCKHISTORY(D28,$A$2,$A$3,0,0),,2))),,-1)
Dynamic array formulas.
  • Like
Reactions: manen2312 and Tluv
Author
DRSteele
Views
1,885
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top