International holiday dates confuse MS Stockhistory function

[Denham299]

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. Thanksgiving is not the same time as Canadian Thanksgiving. So my Canadian prices are not lining with the proper dates as the function skipped over Nov 24th as that was U.S. Thanksgiving. Any help you can provide on how to adjust the Stockhistory function to accommodate multiple closure dates for international in addition to U.S. stock exchanges closure dates please?

[#2]

@Denham299 

You can adjust the ticker symbol with the marktet identifier. Maybe the holidays change accordingly.

stock

Function returns historical price data about the financial instrument corresponding to this value. Enter a ticker symbol in double quotes (e.g., "MSFT") or a reference to a cell containing the Stocks data type. This will pull data from the default exchange for the instrument. You can also refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., "XNAS:MSFT"). Learn more about our data sources.

[#3]

@Detlef Lewin

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

[#4]

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

[#5]

@Sergei Baklan 

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

John