How does the new Excel Linked Data Type Stocks stack up?

Late in 2018 Microsoft rolled out a new concept - Linked Data Types. The first two types to be taken out of this shiny new box are Stocks and Geography. They are in effect connections to external cloud data sources with the initial connections curated using Microsoft Bing AI.

So are they any good? Well not surprisingly my interest is in the Stocks Data Type.

More info on how it all works and an introduction to what you can do with this Data Type can be found in the following links:

Office 365 Support - Excel data types: Stocks and geography

Strategic Finance - Excel: Stocks Data Type

Office Watch - Is Excel Stocks Data Type Premature?

I've been playing with this data on and off for a few weeks now and, going a bit deeper, I wanted to look at the good and the bad.

My interest was in using it as a front end stock selector for bringing back detailed XBRL financials from one of the APIs out there that enable you to do just that (try out the XBRL US API via our Excel template). These invariably work off a Ticker (or other identifier). But you don't always know the ticker so if the Stocks Data Type could magic up an instant ticker this would be a pretty nifty solution. And you can see it doing exactly that in this video.

So I tried this out on a handy but somewhat out of date list of 1000 bigger companies, essentially by converting this list of names into Linked Stocks (Select All/Click Stocks Data Type on the Data Ribbon)

I had a problem in roughly 300 cases (30%). 10% was due to the fact that some of these companies were no longer listed (merged/acquired etc). So lesson 1, Stocks only works with live companies (well I suppose it is stocks after all!). So I was down to a problem with 20%. For half of these (10% of the whole), it offered me some alternatives via the Data Selector Pane that pops up if no good match is found. What was surprising was it often offered me a choice when I felt it didn't need to e.g. Fox Corp; what was the clear cut choice was on the list and it should have gone straight for it, no quibbles, like it had for the other 80% of live companies.

And I got there in the end with the final 10% using a couple of strategies:

• Feeding it a ticker instead (kinda defeated the point as this was supposed to be the output!)*
• Removing words (taking it out "The", "Company", "Corporation", "LP" was particularly fruitful - funny kind of intelligence as you'd have thought removing noisy words would be the very first thing Bing did)

*Note you can be sure to get the correct quote more often by prefixing the ticker with the exchange followed by a colon or a two digit country code.

I was perplexed why for these 100 companies, it gave me no choices to pick at all. Take Deere & Co - I fed it John Deere and it give me not a single option.

Another thing to note with Bing is that it loves a bit of context. Feed it Ford and it gets stuck - is it Ford Motor Company or Forward Industries (Ticker = Ford) but put together a table of Auto Industry companies and add Ford underneath it and it uses its intelligence to automatically convert it to a new row containing Ford Motor Company.

All of this was done with data from their new supplier - Refinitiv (formerly known as Thomson Reuters before the Image Consultants got to work). They had to rather quickly change from their previous suppliers, Morningstar.due to data issues.

What's good about it is the functionality works really well. I like it. What lets it down at the moment is the AI and sometimes the data. The good thing is that the AI ,by its nature, will get better. When I first started asking Alexa to play songs by the British band James, she would torture me with songs by James Arthur. After admonishing her for several months for her crass stupidity, she finally learnt the error of her ways. I bet in a years time, Bing won't be quite so easily defeated by John Deere.

Missing data

Some surprises here given the quality of the source.

For example the employees figure for Fox was missing but I could easily find it elsewhere. Note you won't always realise the data is missing if you just look at the cards as these only show the values that are actually present.

A lot of data was missing for oil companies which was odd. e.g. Enterprise Products Partners has no industry classification. Peculiar as the industry classification system used by Refinitiv is TRBC (Thomson Reuters Business Classification) i.e. their own so you wouldn't have thought that any large companies would be unclassified.

Usage limitations?

A word of CAUTION. You will notice what looks like a thin little disclaimer appears when you first add a Linked Stock Data Type:

Financial market information is provided as is and not for trading purposes or advice.

Annoying enough that you have to click to remove this desktop hogging message but that's only the half of it. Its not a "stocks may go down as well as up" type of message or not just that old chestnut of how they can never be held responsible if you, heaven forbid, decide to trade on the bits of dodgy data that they never got round to cleaning up, that you yourself should have spotted.

As it says, click on the link and you will learn more about our data sources. And you will learn what not for trading purposes or advice really means. It means you LEGALLY can't use it to do these things, well certainly if you do it for a living or carry out any kind of financial function.

So this is all a bit confusing given that these professions are by far and away the biggest users of Excel. Do they tip toe round this functionality? Ordered not to use it by Legal on pain of death?

Whether this has any legal legs is I imagine debatable, given that you are not informed about this up front and this functionality is now baked into a product that to my knowledge has never had any hidden restrictions over what you could do with it before. I only looked because I'm in the financial information business so went sniffing around for this classic little stitch up.

So in theory, all you can do is look at it. So you have to question the point of it being in Excel at all. In practice I suspect you can get away with doing an awful lot more with it but you would have to question whether you could, say share a workbook with Stock Types in it or data derived thereof. Could this per se constitute advice?