Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel's stock data type can't find several stock exchange indexes (i.e: FTSE, CAC 40, Hang Seng...)

Brass Contributor

I noticed by searching around that this is a topic that has been asked quite a bit with no clear answers that get to bottom of this issue.

 

I'm well aware the Excel data types feature is still a work in progress, but by now the dev team could've done a better job on this one. The data selector search tool for instance couldn't be any worse - it really SUCKS!!!

 

So, this is a list of mainstream stock exchanges that Microsoft claims they are supported according to the link below, but I've tried every possible code/ticker related to them that I could and no sucess at all to have them linked to Excel data types.

 

About the Stocks financial data sources

 

IndexExchangeCountryCodes/tickers attempted
AEXEuronext AmsterdamNetherlandsAEX, XAMS

CAC 40

Euronext ParisFranceCAC 40, CAC40, PX1, XPAR

FTSE MIB

Borsa ItalianaItalyFTMIB, MIB, FTSE MIB, XMIL
FTSE 100London Stock ExchangeUnited KingdomFTSE, FTSE100, FTSE 100, UKX, XLON
Hang SengHKEX - Hong Kong ExchangesHong KongHSI, Hang Seng, XHKG
IBEX 35Bolsa de MadridSpainIBEX, IBEX35, IBEX 35, BMEX
MOEX RussiaMOEX - Moscow ExchangeRussiaMOEX, IMOEX, MISX
FTSE JSE SA Top 40JSE - Johannesburg Stock ExchangeSouth AfricaJSE, FTSE JSE, FTSEJSE, JTOPI, XJSE
KospiKRX - Korea ExchangeSouth KoreaKS11, KOSPI, KRX, XKRX
IPCGrupo BMVMexicoIPC, MXX, XMEX

 

And come on, we're mostly talking about MAJOR exchanges here, and that are supported by this service according to Microsoft's own support page, so there's no excuse that no one can't find them for crying out loud!!!

16 Replies

@leolapa 

Refinitiv doesn't provide Exchange Indexes here. List of supported exchanges only means you may find information about papers traded on such Exchanges.

@Sergei Baklan I beg to differ...

 

Please take a look at the screenshot of an Excel schedule I put together of global markets indexes I was able to link with Excel stock data types, and the data card of one of them not only does provide the correct points from the last trading day close, but it also clearly indicates on the bottom righthand corner under "Instrument type" as an "INDEX".

 

Excel_IBOV.png

 

I individually checked each one of the 15 indexes' data cards from the above list and they all provide accurate information from Refinitiv.

 

So, back to my prior question: What about those others I'd listed? Why no one can manage to find them if they are from markets covered by the Excel data type feature according to Microsoft's support page about this topic?

I am having the same issue. Have you found any resolution @leolapa?

I am equally shocked that some major exchanges are easily accessible, while others are completely unattainable.

Thanks

Nothing so far...
I also learned as I was developing some more complex tables to fetch historical stock data via STOCKHISTORY function (which also pulls data from the data types functionality) that this data fetching feature is still ways to go in order to get to some robust state. It still does a lot of data lagging, compromises sheet processing performance, etc.
I'm currently giving up that route and fetching financial instruments historical data and other related info using Power Query scripts via Yahoo! Finance links, and that also applies to global stock exchanges indexes.

I totally agree. It's incredible that the page you show above doesn't have a column for the symbol needed for the STOCKHISTORY function. And while the NAMES in the screenshot @leolapa posted can be found using the Stock data type (which would have been MUCH more useable had he included the TEXT instead of just a screenshot), the "Ticker Symbol" for FOUR of the 14 names don't work with STOCKHISTORY!
I don't understand why all the major indexes aren't listed in one page as well as the CORRECT Ticker Symbol needed to use with the STOCKHISTORY function!
it is quite frankly astounding the lack of proper usable documentation on the available securities/ furtures/ etc. Very frustrating needless to say.

@leolapa 

 

After a couple of years I still can't find the AEX-index (mayor index in Amsterdam, The Netherlands) in Excel-stocks.

@leolapa 

 

The Amsterdam index just worked for me with XAMS. I wouldn't have thought to try this option of not for your table so thank you! 

You're welcome @acflorea!

 

This agreement between Microsoft and Refinitiv does not include proprietary data from some stock exchanges, but apparently they keep adding on new exchanges as new data providers are added on over time, which must've happened with XAMS since the date of my original post.

@acflorea 

 

The index AEX-index doesn't work for me. XAMS is unknown.

@leolapa  I would add that Canada's newest equity exchange is not there, yet because it has started small, it is no doubt the fastest-growing equities and ETF exchange in Canada. Originally dubbed the NEO Exchange when it was created in 2014, it has been acquired by CBOE (yes, THE leader in options trading), and is now called CBOE Canada.  This marks CBOE's entry into regular equities trading, an initiative that is intended to grow into a multi-national network of equities markets world-wide.  (To be clear, this is a STOCK market, not an options market.)  CBOE Canada is also the only Canadian exchange that lists Canadian Depositary Receipts (CDRs), which allow Canadians to buy major US securities without currency risk (much like ADRs in the US).  In short,I think that we're all in agreement  that Microsoft needs to escalate it's efforts to fill in the gaps in it's promise to deliver stock prices and other timely information for all equities exchanges, not just the major US ones.

@leolapa The simplest solution to this annoyance is to find an equivalent ETF that tracks the index you're interested in. For example:

 

  • Russell 2000 > iShares Russell 2000 ETF (IWM)

This ETF can be tracked as any other stock.

 

There is another issue with available indexes such as:

 

  • NASDAQ Composite Index (COMP)
  • DOW JONES INDU AVERAGE NDX (DJI)
  • S&P 500 INDEX (INX)

and it is that the date & time of the quote is not fetched (because it's not a tradeable instrument). This issue is also solved by using an equivalent ETF.

As a (2nd best) alternative to the (many?) outstanding flaws in the Microsoft/Refinitive implementation of the Stocks data type and STOCKHISTORY function, you can always use Web Queries in your spreadsheets to access data from any web site that provides stock data in tabular form.  It's kludgy, but once implemented it works better than data downloads and cut&paste.  My dynamically-updated stock exchange indexes spreadsheet gets it's numbers (probably slightly delayed) from Yahoo! Finance.  I just set up a watchlist there with all the indexes I want and put it's URL into a web query. A click on Refresh All gets the latest numbers, just as with the Stock Type/STOCKHISTORY approach.

 

However, that may not solve the other problem of Refinitiv (or Microsoft?) not allowing access to some exchanges and ALL of the listed companies on them.  As an example, I used Canada's NEO Exchange (now called CBOE Canada).  But since my last post, I've noticed that they seem to be working on that.  With the NEOE: prefix I can now get a few of the stocks and CDRs of companies listed there.  E.g. NEOE:ETHC gets me quotes on Ether Capital Corp, and I can get CDR quotes on IBM and Costco with NEOE:IBM and NEOE:COST. So far I haven't be able to access any of the ETFs listed on that exchange or very many other common shares. At least it's a step in the right direction.

 

 

@Charles_Whaley 

I made a custom GPT for Excel inquiries: https://chat.openai.com/g/g-nbUfJ7DV4-excel-wizard. I encourage anyone to try asking for a solution to this issue.

@oes anyone know if thereis a code that the Excel data type works on for

 

FTSE 100

FTSE 250

Any Chinese index

Hang Seng

Taiwan index

Korean index

Indian index

 

Sector infdicies of STOXX 600

 

infuruating i can find individual stocks and in fact most funds, but not indices. Wasted hours trying to fix this

 

Thanks