Forum Discussion
International holiday dates confuse MS Stockhistory function
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. |
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
