Problem with STOCKHISTORY() and Dynamic Arrays

user_6304284 0
Viewed 26

Let me explain my problem with the STOCKHISTORY() function.

enter image description here

  1. It retrieves the closing prices for a single stock ticker as expected, no problem so far (first "table").

  2. However, I want to use STOCKHISTORY for all the tickers that appear in a table that may grow with time. In this case, STOCKHISTORY fails to retrieve all history prices except for the first line (second "table").

  3. What I am looking for is a single dynamic array formula with STOCKHISTORY that shows the complete price history for all the tickers that will be placed in an excel table (varying length) (third "table").

It seems to me that this a bug of the STOCKHISTORY function, but I may be wrong. Or perhaps, there is a workaround that may solve my problem.

Any suggestions?

Remark: I have added a picture of my excel sheet. Because I use the international settings for Spain, dates are shown in dd/mm/yyyy format and constant horizontal arrays use the "" separator (in the US the separator is the ",").

excel-formula excel
1 Answers

The problem can be resolved by creating arrays out of the Start_date and End_date inputs.

Here an example screenshot:

enter image description here

for the Start_dates array I used this formula:

=EDATE($C$2,SEQUENCE(MONTH($C$3)-MONTH($C$2)+1,1,0,1))

and for the End_dates array this formula:

=EDATE($C$2,SEQUENCE(MONTH($C$3)-MONTH($C$2)+1,1,1,1))

which only differs by a 1 instead of 0 in the penultimate SEQUENCE() index

Edit 1

The formulas for the dates arrays only work for monthly differences within the same year. But I'll leave that small adjustment to you. Just an example here.

user_171587030
Related