Forum Discussion

Copper Contributor

Dec 02, 2022

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...

excel

SergeiBaklan

Diamond Contributor

Dec 03, 2022

Denham299

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
        )
    )
)

Denham299

Copper Contributor

Dec 03, 2022

SergeiBaklan

Sergei. This tests my limits of XL but gives me lots to work on to beef those up. Many thanks.

John