Wednesday, 17 June 2020

XBRL API Quarterly Data Call

As explained in our Getting Started guide, We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API. All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.

An API call for quarterly data works in much the same way as for annual values. However to bring back Quarterlies, three more boxes come into play. except this time they are black.

 Black Boxes  - these either represent fixed elements of the API path or ones that have a small set of finite values so we have rigged them up as drop downs in Excel.

 Black Box  - Annual? Default value is "yr" but you can select "q" to see quarterly values instead.

 Black Box  - Quarter. Selecting "q" brings this box into play where you can select which quarter 1,2, or 3. You can also specify "123" to get all quarterlies for a year. NOTE: "12" etc will not bring back 1st and 2nd Quarters. Only "123" works for multiple quarters in a year. If this is set to "4", and the next box is set to "ytd", it will bring back the annual figures.

 Black Box  - YTD. Setting this to "ytd" as opposed to blank will bring back cumulative figures for the year - the Year To Date.

Orange Boxes work just like they do for annual amounts. So if you set the date to 2019, it will bring back the quarter you selected (e.g. 2) for the reporting year ending in that year. And if you set Years to 5, you will get a times series of quarter 2's going back 5 years.

Monday, 15 June 2020

Getting started with an XBRL API Data Call

(if you've stumbled across this article and want to know more, email us and I can add you to our beta programme)

We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API, or indeed, the XBRL US version.

All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.

For our API you will need a token which you can get from our website by registering or signing in, which then goes in the Red Box.

To run the query, move to a cell in the grey area where the results are. Right click and choose refresh query or use the keyboard shortcut ALT F5. In Excel the API call is made using Power Query.

Green Box - Tag. Choose a data item by entering an XBRL tag. We have constructed a data set of the most commonly used tags that you are likely to need called the X Financials. These are listed on a sheet at the back of the Workbook. If you want to see all the tags you could possibly ever use! then got to an online XBRL Taxonomy (there are quite a few free ones around). I use the Workiva Wdesk Taxonomy Analyser. You can also just enter "All" in this box and it will bring back data for all the items used the companies you select.

Green Box - "All". If you don't know which tag to select or need to get familiar with the tags in use, you can put "All" in the Green Box as the Tag.. Be aware that if a value lies off the main axis (sorry getting a bit technical here), then these values will not get returned with this call. This only applies to more obscure values that might have been disclosed in a more complex table in the notes. A full explanation of axis and dimensions is explained in another article. Values on these axis will be made available in future. We want to make sure we do this in a meaningful way that doesn't bombard you with incomprehensive incomparable data

Orange Box - Date. Choose a year for the values that you want returned. It will get values from filings with a reporting period ending in that year. So specifying 20191231 will return values from a 10-K with a year end up to and including that date. If you just enter 2019, it will do the same. If you enter 20190630, it will bring back values for filing periods ending between 20190630 and 20180701.

Orange Box - Years. The other (optional) Orange Box, Years, determines how many years of values are bought back. So the Date box specifies the starting year and Years how many periods are returned in the series. If no Years are specified, it will bring back one year of values.

Blue Box - Cos. Enables you to specify which companies you want values returned for. This is where the beauty of modern Excel kicks in. Add a ticker to the bottom of the list and the Excel Table (The Blue Box) automatically expands to include it. Companies have to be specified by ticker, but don't worry if you haven't got the ticker at hand, you can quickly look them up using our API on the Beryl Cos sheet. You can always reduce the number of companies returned by pulling the Table back up to its original size using the triangular (ish) handle at the bottom right corner of the Table.

Also read - A Walk Around the Results Returned by an API Data Call (coming soon).

Friday, 26 April 2019

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?

Friday, 15 March 2019


Now here's a great way to access reported XBRL, as filed with the SEC. XBRL US have been databasing filed XBRL since day one and last year they published an accessible restful API designed to make their extensive database available to a wider community.

Now out of beta and with a small but burgeoning community, the XBRL US API provides an excellent way to get hold of individual pieces of XBRL via relatively straight forward http API requests. Pretty much everything is there, although it can take some time and experimentation to get all the pieces together you want, given the breadth and complexity of the dataset.

But never fear, there are two handy templates to get you up and running from day one, one for Excel (which we at Fundamental X have provided) and one for Google Sheets.

The Excel sheet shows you how you can quickly use Power Query as a front end to SEC filed XBRL. See how to immediately get going in this video.

Things to note:
  • As filed, warts and all. So all the errors and missing data is present (or not!)
  • Some limits to the free version (see pricing below)
  • Authentication token required with each request and needs updating every hour
  • Initial registration and Authentication setup a little tortuous (4 bits of info required)*
*Easier with the Official Google Sheets template as just email and password required via in app login (although new login required each time token expires) and with the Excel template setup is required only once with the option to generate new Authentication tokens automatically.

Pricing (individuals) - see also XBRL US Membership
  1. Free - Registration required - Limits: Max 1000** records returned per request; Non-commercial use*.
  2. Individual Membership - $55 a year - Limits: Max 2000** records returned per request; Non-commercial use*.
  3. Power User Membership - $250 a year - Limits: No record limits; Non-commercial use*.
  4. Sole Practitioner Membership - $500 a year - Limits: No record limits; Commercial use allowed*.
*If you are an investment professional who trades on behalf or advises others then you would need to purchase Sole Practitioner Membership. Different rates apply for corporates. Commercial use also allows you to share the data with your clients, with attribution. Redistribution is not possible.

**The record limit may not necessarily be a problem. It may just require you to make more individual requests and frame them more carefully. There may also be a cap on the number of requests made within certain timescales with Google Sheets.

Wednesday, 23 January 2019

Fixing XBRL to Build a Set of Investment Grade Financials in which you can have Total Confidence

So as promised, here's how you fix the XBRL to get your very own set of investment grade financials. See my last post as to why you need to do this.

This of course is also a sales pitch. We have a set of tools that we think does just that.

I've chosen a very simple single ratio model so you can see exactly what's going on and how easy it is to improve on the XBRL tagging filed by companies. I'm using our fully featured totaliZd product to do this and you can download both a before and after X Sheet to see the impact of these tools.

So lets say I'm a strong believer that research & development drives future growth. How does my target company stack up against its competitors?

Well I don't know, because as a result of extensions (and standard totals missing from as reported filed financials - a common problem), my model in Excel is currently a mess.

Now to give this analysis some credence, I've chosen the first company with a significant extension issue off our previous list of the DJ30 companies and found four of its competitors against which to compare. And I'm getting not a single R&D figure, coming through from the XBRL on the face of the financial statements, for any of them!

This is not uncommon behaviour for a peer group of companies. They watch each others disclosures and follow suit. There is even a button in the leading brands of XBRL creation software to allow you to do this! This is good and bad. Bad that it happens in the first place. Good in that it makes it easier to fix - we can quickly roll out the same change across the whole group.

Now at this point I would recommend you watch the video to see how to do this and how quickly it can be done. I was able to make the change, from model to filing and back again, for 4 companies in under 30 seconds.

totaliZd allows for layers of tagging so we never change the sacred as reported numbers and tags, we just build layers on top until we get exactly what we want, a view of the company that truly reflects what we wish to see. This way we can always get back to the genuine source figures. So we pick up the tag we want from our handy set of X Financials (broad set of the most commonly used set of values and adjustments used in modelling companies) and plonk it in the User Tag column. Job done. And when you have finished, this is what you will end up with.

So continuing our narrative, 3M looks like a poor performer compared to Corning, so based on our (very) simple and crude analysis, we should instead back Corning to generate greater revenues in the future.

Now we have only changed one tag here. And we can leave it at that if that's all we want to look at. But what other changes might we have to make, if we wanted to fully prepare 3M, to create a full set of VAXBRL (Verified & Adjusted XBRL), ready for any analytical challenges we might want to throw at our target company? Well if you look at the X Sheet after adjustments, you will see I had to add only five other tags to turn dubious as reported XBRL into Verified & Adjusted XBRL.

As the name implies, part of the process is Verifying the accuracy and validity of the XBRL and this is what the totaliZd sheet enables you to do. One glance at this sheet tells you everything is tickety boo and good to go. More on this in a later post - suffice to say there is an awful lot of checking going on here (we instigate a triple check procedure that enables you to look at the XBRL from three angles).

totaliZd enables you to adjust the XBRL, not to change it, but improve on it to match your own modelling requirements. User Tagging creates a backstop so no matter what the company churns out as a filing, you can easily fix or improve on it. You can make as many or as few adjustments as you wish and they will all be reflected in the totaliZd validation. You are in control, enabling you to decide what values go into your models. This way you can create a set of Financials which surpass any that you might purchase from the likes of Capital IQ or Bloombergs. This is serious value for money.

So confident are we that it will change the way you analyse companies, we have decided to offer a 60 day free trial of totaliZd (no credit card details or prepayment required). Just email us at to take up this offer.

This post is part of series that started here and is a follow on to my last post - Why Verified and Adjusted XBRL is the Best Choice for Company Analysis

Thursday, 17 January 2019

Why Verified and Adjusted XBRL is the Best Choice for Company Analysis

If you read my last post Do Extensions make XBRL Unusable out of the Box?,  you will know that using XBRL without adjusting the tagging could well be calamitous.

So what are your options? Well in the post before that Which Source of Company Financial Data should I use?, I set out the options for finding the best source for comparative analysis. Lets re-iterate them here but I'm also gonna add one further choice.

1. Lifting values straight from the Financial Reports filed by a company
2. Data Vendor - Bloomberg, Thomson Reuters, Capital IQ, FactSet etc
3. XBRL (unadjusted)
4. Verified and Adjusted XBRL (VAXBRL)

The first choice is only an option if you have ridiculous amounts of time. The second if you have ridiculous amounts of cash and you are willing to take the risk the data is completely error free and has been interpreted correctly. Vendor data by its nature has been handled by a third party so really needs to be verified if it is to form the basis of an expensive transaction. This is why it is never used as the primary source for in-depth company analysis in critical departments such as M&A.

My previous post explains the danger of using neat XBRL. Over half the Dow Jones 30 companies had used extensions in a way that could lead to significant errors in your analysis if you just plugged the raw XBRL into your model. Only 20% of companies had no extensions on the face of their Primary Financial Statements. And if the trend over the last five years is anything to go by (which I also examined in my analysis - I was interested to see what that fifth year of data might look like compared to the first), it not going to get any better.

There is a viable alternative. And it is not expensive. And by expensive I mean in terms of time, that most precious of commodities. VAXBRL. Just spend a little time verifying and adjusting the XBRL. This way you'll always know the provenance of the source (direct from the company). And with the right tools, it can become an easy and inherent part of your financial modelling process over which you will have complete control.

What you'll end of with is a set of financials better than any vendors at a fraction of the cost. Now that I think is what the XBRL revolution was supposed to be about.

Of course I wouldn't be telling you this if I didn't have a set of tools up my sleeve that might be just the job. Take a look at our totaliZd product. Download a totaliZd X Sheet at work (ready for adjustments). Yo can now also see a fully adjusted version of an X Sheet and video which I talk about in my next post.

Monday, 14 January 2019

Does the current use of Extensions make XBRL unusable out of the box?

Yes. You can't just plug it in.

But does that make XBRL useless? No. It just means you'll need to make a few adjustments to the tagging. A small extra step in the process with the right tools. If you don't, if you insert raw XBRL numbers, the ones you can get from the SEC or XBRL.US straight into your models, your analysis will be flawed. I'm not saying it might be flawed - it WILL be flawed.

I say that because I've looked at the following table. Well actually I compiled it. It shows the incidence of extensions for all 25 of the non financial companies in the Dow Jones Index (DJ30) for the latest published XBRL income and balance sheet and was compiled using our X Sheet. As long as you only want to compare Apple against Intel you should be fine or the other lone troika of companies that have no extensions on the face of the primary financial statements. Otherwise you've got a problem.

You can download the full spreadsheet here.

I chose to look at the face of the two principal financial statements for the DJ30 because I wanted to look at the best case scenario. The most comparable values tagged by theoretically the best resourced companies, supported by the top audit firms and therefore capable of delivering pinpoint tagging. The tags I looked at of course represent a fraction of the tagged elements in an annual 10-K, and being the most commonly summarised amounts, the ones in theory least prone to being extended. I excluded the financial companies as they are a bit more peculiar so maybe even more liable to be extended. Like I said I wanted the optimum target result.

Just in case you are not familiar with extensions, an extended tag is a useless tag. That's being a bit harsh but it does make comparative analysis exceedingly difficult, without intervening to pull it back into the US-GAAP taxonomy (the list of standard tags). The idea is that companies can extend this standard taxonomy by adding their own bunch of tags on top, where items don't fit into the list. This undoubtedly legitimately occurs and this is part of the beauty of XBRL, that it allows for this. It wouldn't be XBRL (eXtensible Business Reporting Language) without it. Unfortunately, in reality, it is up to the company to decide when this is so.

And so ineffectual is this type of extended tagging, that when Europe joins the XBRL party next year, ESMA has specified that this type of disconnected extension will not be allowed. Every value will have a backstop connection to a tag in the IFRS taxonomy and if that does not fully describe the item in question, an extended alias maybe used as its primary tag.

So having said all that, I'm not sure we should be seeing so many extensions on the face of the financial statements. Surely they should pretty much all look like Apple. Exceptions that prove the rule not make a mockery of it. Anyway I'm not gonna dwell on that. It is what it is. A more pertinent question then is whether these extensions really matter and if so what we can do about the data we have in front of us. This is why the table is not just a simple count of extensions but an examination, albeit cursory, of the potential impact on meaningful company analysis.

We've had a lot disconcerting reports on extensions before but I wanted to make this inquiry as real world as possible. So the top level items and my subjective assessment of  the likely impact on KPI's or the ability to strip back figures for future forecasting. So some extensions I have deemed as insignificant, some likely to have a minor impact and others, which set off a big red flashing light as, major. So where a total, which can be calculated by combining its disclosed components using established taxonomy data validation rules, has been extended, this is deemed as insignificant but where restructuring charges have disappeared from the standard taxonomy by virtue of an extension, I have considered this a major problem. I have sometimes paid attention to the size of the amount but really it is not necessarily a reliable marker as for example in some years restructuring charges can be massive and others minuscule.

Anyway you can make your own judgement as I have made the spreadsheet containing the table available so you can use the X Sheet to see all the extensions for yourself by looking at the Filings tabs. Also by looking at the totaliZd tab, you can quickly see the scale of the impact on a comprehensive set of standard values designed for analysis (we call our version the X Financials). The scaling option, a feature of totaliZd, has deliberately been set to billions so you can more easily see the significance of any impact. A value of one could even be significant within or without its group at this level of magnification.

As the screen shot above shows, any amount in the square green boxes, is a problem that needs fixing. How you do this I cover in the next post.

What you see in totaliZd is as bad* as it can be. That is not a comment on the individual companies involved but the limitations of using neat XBRL. This is the point of totaliZd, to provide a starting point, from which you can quickly fix this apparent mess and leverage XBRL to perform the kind of analysis never previously possible before. I'm not dissing XBRL. I'm simply qualifying how you use it.

*Things get off to a very bad start for Exxon and Coca Cola as neither them supply a Revenues tag. This isn't even an extension problem. This is just a "not using enough us-gaap tags" problem. Problems like this automatically get fixed in the X Sheet when using our standiZd option.

This post could really be considered part of a series I started in 2017 - And so has the dream come true? to look at the state of XBRL disclosures and examine the continuing validity of the objections raised in using it.