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.

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).

Dealing with XBRL data issues part 1 - missing totals

As can be detected from the title, there are numerous issues, many of which in reality are (or can be seen as) positive features of XBRL! I'm gonna deal with each of them one by one by demonstrating the various strategies we use to create comparative data.

I first touched on this in an earlier post and explained what was then our rather clumsy solution to the problem.

Missing totals mean missing standard values when trying to do comparative analysis. They are missing because XBRL preserves the presentation that companies have always used to show their financial performance, namely the succinct and more readable presentation you see in a paper report. Why would you want to repeat a figure just to create a complete set of totals? There's no need, it will only create clutter and make it less readable.

Fortunately we can do something about this and we do in the latest version of XBRL Sheet (the latest version is not yet generally available so email us - jimtruscott@fundamentalx.co.uk - if you would like to start using it). You can find more info on XBRL Sheet in this post and you can watch XBRL Sheet solving the missing totals problem in this video.

Lets have a look at an XBRLSheet download...

Before we used to download one column of tags, now we download two! The 1st column contains the standard tags which is our understanding of the high level tag that the company should be using in a "Standard" rendition of the values. This will usually marry up with the actual tag they used (in column 3) as per line 4 - Cost of Revenue in the example above. But if they have been more specific (which is great as more precise tags gives us a better understanding of their business), then we supply the different high level tag as well (which they haven't used as they would have to duplicate the line, creating a cluttered presentation as discussed above). So line 3 - Revenues is a case in point. Microsoft used the more specific tag "SalesRevenueNet". To make these easy to spot and check if necessary, the different standard tag will appear in a different colour. So we see another one further down. Again we may be interested in quantifying all provisions when a business is restructured rather than just goodwill. These two different tags enable us to do this.

So how do we use this info in our model? Well as we always recommend (see this post - specifically the bit about transparent data), you should connect with this data via an intermediate sheet (see below), to create values that can plug straight into your model.

Now with this extra column, we don't need to create a calculation to catch all the multiple alternatives for revenue (as represented by the Total Revenues line); we just need to use a simple lookup on "revenues" in this new column and the values will appear as shown. The 2nd column above contains the tag that is looked for. We in fact do a double lookup - we look in both tag columns in the Filings sheets to make sure we never miss an item and this also allows us to pick up the specific values if we want as well (e.g. the Revenue - Sales line). The names in the 1st column by the way are our names for the items and demonstrate how having an in-between sheet enables you to customise the data before it hits your model.

Fixing errors in XBRL Instance documents again

You can find a previous example in the post "Fixing errors in XBRL Instance documents" and an introduction to XBRL data errors here.

The next example is a little more subtle. Wrong but not immediately obvious, unless you are trying to model some business sectors, perhaps using our Sector3 product! - find loads more info on our award winning product here.

Boeing in their 2012 10-K slightly changed the tag for one of their top level business segments in one of the sections showing data for their businesses. It was referring to exactly the same segment (the label i.e. the description was the same) but the tag was different. A mistake - someone wasn't paying attention. In fact with reference to the previous example, they created an entirely bogus "context" for this identical segment.

So we got rid of it, replacing all connections to it with the correct context reference (shown below). In fact there was more than one wrong context so they all went the same way.

There was a little more work to do here than previously as a tag comes with a panoply of associated data - labels, definitions & the like, all of which we felt it was prudent to remove. Details were as ever recorded in the "xsd" file as shown below.

The consequence of this error was that data was missing for assets in Boeing's Defense, Space And Security business. Well it isn't anymore in Sector3.

The case against extensions

Thinking again about Daniel Robert's recent post and the virtue of extensions... And shocked to find myself, at the recent International XBRL conference, one of the few to put my hand up in favour of extensions, I thought it might be worth exploring the other extreme - the absence of extensions.

The exclusive use of GAAP tags would give data vendors and wholesale users of data (e.g quants) what they crave - data they can normalize. Of course there are arguments that there is some data that can never be normalized but that won't stop people trying when the goal is to model the world (well maybe an economy or a sector).

So what would be the cost? Would a company's performance suddenly be mis-represented? Would the subtleties of it's economic contribution now become masked?

Isn't that the point of the label (data item description)?

For those who want a more nuanced understanding of a company's business achievements, they merely need to disregard the tags and focus their attention on the labels column. As per Daniel's piece, this applies equally to companies who wish to engage in the art of obfuscation. Isn't this the beauty of XBRL, you have enough data for everyone provided each type is used correctly?

OK so a tag may not fully describe the nature of a data value but the vendors are going to tag it with something anyway. Better that apples are tagged as fruit than risk them being tagged as oranges.

Ultimately this must be advantageous to companies, as rather than the aggregated holes into which their data will fall being decided by a data factory worker on the other side of the world, they get to decide which GAAP tag their precious data will be allocated to.

As James Claus of Moon Capital pointed out at last years US XBRL conference, investors would prefer data which has (if not down right reject data that hasn't) been classified by the companies themselves. And is the reason why Moon capital never use vendor sourced data. Companies need to get precious about their data and XBRL actually allows them to do that.

Umm Extensions....picking 3 companies at random

I had a conversation recently with a well regarded member of the XBRL community who intimated to me that serious tagging errors were a thing of the past. Now what you see below may not be errors, but they are a serious problem when attempting to automate analysis using XBRL tagging.

You can find the spreadsheet containing the data I've highlighted on the xbrlxl.com website here.

The examples shown are literally the first 3 companies I loaded up in the XBRL to XL software (off the top of my head - I had no fore-knowledge that they contained significant problems). I expected I might have to look at a dozen or so to find anything remarkable but these 3 large corporations quickly scotched that idea. I should stress these are the very latest 10-K's, not the XBRL they put together when they were first trying to get the hang of this stuff,

I've basically highlighted problems that would effect the kind of analysis I might do if I was interested in understanding the performance of these behemoths of US Industry.

So with Boeing (BA), we can't calculate an accurate figure of trade receivables because any money leant to customers to finance purchases is shown as an extension (no us-gaap tag in the 1st column) so we can't add it to trade receivables automatically, as we might, to get a true figure because they haven't used a standard item to classify it. The extension obscures it's true nature when we want to standardize this data.

If we wanted to calculate our own estimate of EPS on a diluted basis, again we would be out of luck as any numerator we re-calculated could not be divided by the correct denominator as this again has been classified using an extension. Also note that the earnings figure used by the company to calculate basic EPS has been obscured by an extension.



Moving on to inventories, we can't calculate a true work in progress figure, again because of the use of an extension. A significant number in the case of Boeing.

General Motors (GM) is even more problematic. I have no S,G & A or equity income because of extensions. I can't get a figure even for depreciation as the amount has been obscured as a segment axis (don't ask! - but it basically makes it very difficult to get hold of a depreciation number this way because it's hidden behind a segment extension).

So this just leaves us with 3M (MMM). And they've managed to hide trade payables behind an extension.

There are more errors (sorry data issues) - I've just picked out the obvious ones in a cursory flick through these 3 companies.

And it might be there are no suitable us-gaap extensions to cover the item they are trying to disclose.

The use of extensions in the ways highlighted above could be seen by some as healthy and in this respect you might want to read a post by Daniel Roberts.

The data you see in the spreadsheet has all been picked out of sheets generated using XBRL to XL (xbrlxl.com). As well as providing XBRL data for analysis, this version highlights any potential errors.

..... so how can you access SEC XBRL data?

Well you can't! without a parser. You can view it, as those nice people at the SEC, seeing there might be an issue here, now use a parser themselves to chuck it into a html page and a spreadsheet for you to download. But haven't you always been able to access it like this? - these files contain no tags so aren't they just the same as the html documents companies have been filing for over a decade? They are certainly no more useful for comparative analysis. Isn’t it all about the tags? This is like giving someone a Ferrari but neglecting to leave them the keys.

It should be stated at this point that if you can handle a parser - were talking about a slice of programming here, you can suck out all the goodness and re-purpose the data to fit your requirements, which is obviously what the big boys are doing.

The SEC were not the only ones to see a problem developing. Even the “Father” of XBRL began to wince at the complexity of it all and opined for something simpler, so he invented XBRLS – the “S” standing for simple! Although, as far as I am aware, this hasn’t got any further than a Wikipedia entry.

Not all regulators have taken the “full on” XBRL path. In the UK, the Inland Revenue & Companies House, the UK depository for legally required admissions, have chosen to use iXBRL as the medium of disclosure. iXBRL makes sense. It is just one document which can be read instantly by a browser but has the tags buried in it. It's almost as if Steve Jobs has gotten hold of XBRL and given it the Apple treatment. I will be looking at iXBRL in more detail in a future entry.