Forum Discussion

Denham299's avatar
Denham299
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. 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?

excel
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 03, 2022

    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.

    • Denham299's avatar
      Denham299
      Copper Contributor
      Dec 03, 2022

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

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        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
        )
    )
)

Resources