Forum Discussion
International holiday dates confuse MS Stockhistory function
Hi. I am having trouble with a stock list to return prices that are traded on U.S., Canadian & European exchanges. The stockhistory function seems to adjust ONLY for U.S. holidays. E.g. U.S. Thanksgi...
Thank you. I was not clear with my question but your answer triggered another solution I will try. I was trying to using one row as a date row for a list of multi-country stocks. I now realize that that (likely) will not work as the date row will return the dates for that specific country/exchange. So I will have separate the stocks into countries and have separate date rows for each one (a drag but can't think of another way).
Generating separately dates sequence and stock histories you may combine them in one spill imitating fill down. Something like
=LET(
dates, SEQUENCE(TODAY() - start + 1, , start),
history, STOCKHISTORY(ticker, start, TODAY(), 0, 0, 0, 1),
HSTACK(
dates,
XLOOKUP(
dates,
INDEX(history, 0, 1),
INDEX(history, 0, 2),
,
-1
)
)
)Sergei. This tests my limits of XL but gives me lots to work on to beef those up. Many thanks.
John