Forum Discussion
leolapa
Sep 05, 2021Brass Contributor
Excel's stock data type can't find several stock exchange indexes (i.e: FTSE, CAC 40, Hang Seng...)
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
Index | Exchange | Country | Codes/tickers attempted |
AEX | Euronext Amsterdam | Netherlands | AEX, XAMS |
CAC 40 | Euronext Paris | France | CAC 40, CAC40, PX1, XPAR |
FTSE MIB | Borsa Italiana | Italy | FTMIB, MIB, FTSE MIB, XMIL |
FTSE 100 | London Stock Exchange | United Kingdom | FTSE, FTSE100, FTSE 100, UKX, XLON |
Hang Seng | HKEX - Hong Kong Exchanges | Hong Kong | HSI, Hang Seng, XHKG |
IBEX 35 | Bolsa de Madrid | Spain | IBEX, IBEX35, IBEX 35, BMEX |
MOEX Russia | MOEX - Moscow Exchange | Russia | MOEX, IMOEX, MISX |
FTSE JSE SA Top 40 | JSE - Johannesburg Stock Exchange | South Africa | JSE, FTSE JSE, FTSEJSE, JTOPI, XJSE |
Kospi | KRX - Korea Exchange | South Korea | KS11, KOSPI, KRX, XKRX |
IPC | Grupo BMV | Mexico | IPC, 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!!!
- mstcpjmCopper Contributor
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.
- mstcpjmCopper ContributorI 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.
- Mike AsplinCopper Contributor
@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
- Charles_WhaleyCopper Contributor
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.
- Charles_WhaleyCopper Contributor
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.
- acfloreaCopper Contributor
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!
- Hrman1966Copper Contributor
- leolapaBrass Contributor
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.
- Hrman1966Copper Contributor
After a couple of years I still can't find the AEX-index (mayor index in Amsterdam, The Netherlands) in Excel-stocks.
- JerryDNYCCopper ContributorI 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!- Almac42Brass Contributorit is quite frankly astounding the lack of proper usable documentation on the available securities/ furtures/ etc. Very frustrating needless to say.
- bjcoughlinCopper ContributorI 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- leolapaBrass Contributor
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.
Refinitiv doesn't provide Exchange Indexes here. List of supported exchanges only means you may find information about papers traded on such Exchanges.
- leolapaBrass Contributor
SergeiBaklan 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".
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?