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

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.