Using an API Call to get an Overview of the XBRL contained in Filing

We have a very simple call that enables you to quickly see which sections of data are available as XBRL in a filing. This is particularly helpful when used in conjunction with items/sections Endpoint. That Endpoint brings back all the XBRL values disclosed in a chosen section along with all the presentational data to display it as it was reported (e.g. show me in the Income Statement for 2019. What you are looking at here when you do that is all the same XBRL used by the SEC to construct their Interactive Data pages for a filing.

Use the Endpoint sections as shown, choose a company in the Co Box and select a Date for the filing. You can also choose further along the row in the Annual? and Quarter Boxes whether you want to see the Annual Filing (e.g. 10-K or 20-F - just select "yr", otherwise "q") or choose which quarter you wish to see the filing for. By default it will bring back the annual filing.

The results, as shown above, give you a list of all the Sections by name and by Tag. Unlike (in theory!), XBRL Values, Sections returned from the SEC data are not tagged, very bizarrely, with a set of standard tags. So we have stepped in to supply our own Standard Section Tags and to make things even easier, especially when summoning a section, we've turned these into a series of short codes. You can see these displayed in the Code column when you a return list of Sections. Each Section is also numbered according to its display order (No). This is useful when summoning a more obscure Section that does not have a Code. Line_Total next to it will give you a handle on whether a Section is likely to contain any useful XBRL (Total lines of XBRL contained in a Section). You can also quickly verify that the data returned for a Section is as it should be by following the "Link" to the same page shown in SEC Interactive Data for a filing. It should be as the data source is the same - the XBRL filed at the SEC.

Getting Tickers and Other Company Details Back with an XBRL API Data Call

 

There are three relevant Endpoints when trying to find a company to download.

1. cos/try (as shown above) enables you to search on a company name. Input at least five letters into the Company Box and it will return a list if matching companies when you refresh the query (Alt F5) as shown below.

2. cos/ will search by Ticker or CIK to return an exact match. In the case of a Ticker, it may return more than one result as it will show all the different companies that have used that Ticker since Companies have filed XBRL. The most common reason why two companies may have used the same Ticker would be where a company has morphed into a different legal entity so two companies, two CIK's. Don't worry when bringing back results we handle this by mapping the same Ticker to both companies to bring back a seamless series of results. Note: CIK's never change whereas Tickers for a company can.

3. cos/industry. You can use a SIC code with this Endpoint to bring back all companies with the same SIC. You could copy then SIC code for a target company into the industry box, select this Endpoint and the query will return all the companies operating in the same industry.

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

Getting XBRL in Excel is dead easy with Power Query...

Constructing a web query in Excel using Microsoft's new wonder query engine to access XBRL is really simple.

Starting with Excel 2010, Microsoft started peddling a new analytical add-in designed to bring data of any shape, size and colour into Excel in one big mashup. An analysts dream.

In Excel 2016 and Office 365, it became part of the fabric and got a new name - Get & Transform; Microsoft's chosen way to source data from within and without Excel. So much so that the way we used to, well still do, run web queries at XBRL XL to pull in XBRL is now described as a "legacy data import".

The web query is dead. Long live the web query! Yep web queries are still a vital part of the mashup and are immediately accessible from the Get & Transform area of the Data Ribbon.

I have to say, having spent the last 6 months, pulling it and tangling with it, its still a bit ropy round the edges. There are bugs and you can do the same things in confusingly different places. It doesn't all quite fit together but it will do and when it does its going to be epic.

So we've started to play with it. And you can see the results in this video.

We designed our XBRL data to be accessible by the most universal route possible. So you could query our data via the most basic of web protocols to download simple tables.

So a web request to access XBRL from the latest 10-K from Microsoft looks like this:

http://xbrlsheet.xbrlxl.com?tkn=0&eid=msft   (Get a free, more useful token at our website)

And a query to give you precise control of what filing you get back:

http://xbrlsheet.xbrlxl.com?tkn=12345&eid=msft&dat=20170630&fil=1&typ=fsn

fil=1 brings back a 10-Q, fil=4, 10-K. typ=fsn brings back the financial statements & notes.

So if you choose Get Data/From Web from the Data Ribbon and insert one of the above web requests (with a token that works rather than the placeholders above - tkn=0 works but its a groundhog - same filing keeps coming back), it will go away and bring back four tables. If you select Table 3 and press load you will instantly have a spreadsheet full of XBRL financials just as the company reported them.

As it comes with the tags, you can do lookups to pick up precisely whatever values you want to include in your models. You can see these at work here in our Power Query version of the X Sheet.

Of course to get a different filing all you have to do is use a different company ticker. Except how do you do that in the Power Query we've just built? Well you could run through the whole rigmarole again and build another Power Query, putting a different ticker in the web request, and as we saw that's relatively painless. But there's got to be a better way, right? A way to take control of the query? Well there is but it is less than straight forward and you'll need a whole new tool set.

In the meantime, you can use one we prepared earlier, The X Sheet . When we come back, we will show you how you do it. Till then we suggest you brush up on your knowledge of Excel Tables!

So now you can see exactly how to take control of an XBRL Power Query in the next post...

Will there ever be less XBRL data items?

If you read my previous post, you will know that the arrival of the SEC US-GAAP Taxonomy unleashed a veritable tsunami of data items on a concerned body of filers and a bewildered bevy of investors.

So with so many data items milling around, you would have thought that someone or something might be sent out to clean it up. Well the good news is that in late 2014, the FASB launched the Xbrl Taxonomy Simplification Initiative.

And now the not so good news...

Source: FASB - 2017 Taxonomy Release Notes

Looks good until you realise that this represents the rate of change. In other words you don't really want the blue line exceeding the red line. But you can actually see the impact of the 2014 initiative as the red line finally jumps above the blue one through 2015/2016 reaching a peak of 681 deprecated items in 2016. I have to say I still find it staggering that there are so many items to choose from given how few items companies actually disclose in their 10-K's and Q's.

So is there any way out of this forest? Well all is not entirely lost because in March 2017 the SEC finally agreed to allow foreign filers to use the IFRS taxonomomy. This is good on two counts. One it means that all foreign filers will for the first time be required to file XBRL which starts to answer the is it only for US Companies question (more on this later!) and two it establishes a precedent that a taxonomy does not have to have 16,000 data items.

The IFRS Taxonomy which is designed to satisfy the filing requirements of every country around the world, not just one large country, has only 4,610 items. It did have a lot less (47% less to be precise back in 2011 when it was originally scheduled to be mandated) but I think they were told to bulk it up a bit if they wanted to get SEC approval (sound of head banging on table). A group was tasked with adding items by custom or practice (and approx. 1,000 Common Practice items have been added since then). During this process no specific consideration is given to how useful an item's structural relationship might be - this is sadly not the philosophy behind either the IFRS or US taxonomy. Well at least 4,500 is a lot better than 16,000.

IFRS based XBRL is likely to be adopted for mandatory filing in Europe in 2020 and one would imagine the rest of the world will fall into line after that (completely answering the US Companies only question). Most of the world now uses International Financial Reporting Standards or has standards based on them.

I should point out that I am a member of the IFRS Taxonomy Consulative Group (ITCG). But this is not why I'm a relative fan of the IFRS taxonomy. It's just got less items in.

What do filers do with all the XBRL data items at their disposal?

This is the latest in a series of ponderings on whether the XBRL dream has come true?

In our XBRL to XL database, we have 20,000 data items that originate from the US-GAAP Taxonomies. That's a heck of alot of data items. Admittedly that includes alot of bumf that is just there for presentation or connecting stuff (abstract items) or axis (column headings) and items that have subsequently been deprecated. But compare that to S&P Compustat's 500 odd or the 1600 data items we use for our standardisation routines.

I guess the FASB were worried about companies using extension tags, although perhaps there are more direct routes of limiting i.e. prohibiting extensions.

Of course the more there are, the greater the risk of mistakes. Choosing the wrong item over another in the same section is not easy to detect. Usual validation routines won't pick it up. And none of this stuff is audited. Ultimately it is up to the company, or whoever they've outsourced their XBRL tagging to, to pick it up.

The kinda advice stressed by Ernst & Young on the release of new taxonomies is typical and not surprising.

"As they move to a new taxonomy, companies should review their practices for selecting XBRL tags and thoroughly search the tags in the new taxonomy".

So what value do we as users get out of all this wonderful detail?

Well lets have a look at disclosure levels for our probably over the top standard data set of 1632 items (lets refer to this set as the standidZd 1600. So a couple of weeks ago I did a chop of our SEC XBRL to XL database (powers XBRL sheet) to check on item usage. I thought it would be interesting to look at usage for our reference set of industrial companies we established when looking at filing histories (seeing our standiZd 1600 was designed explicitly for non financial companies). Keeping everything consistent with all our other data sampling in this series, I only included 10-K's but all the 10-K's they've ever filed in XBRL. I should stress that this is before we've applied our standardisation routines.

So what is this table telling us? That 263 of our standiZd 1600 have NEVER been used by any of the top US industrial companies. And a whopping 65% of what we regard as the most comparable individual data items have been used by 5% or less. So if we were trying to compare any one of these 1000 odd items, we would have at best only 11 other companies (and roughly on average probably only six) to compare them with. Lets hope a couple of them happen to be in the same sector! And we can probably forget about creating any meaningful aggregates for these items.

What happens if we widen it right out to the total population of US-GAAP tags and the total population of companies. Not surprisingly it gets uglier. 10,000 of US-GAAP tags appear in 5% or less of the total number of 10-K filings made to date (approx. 50,000). And roughly only 1,000 items appear in more than 5% of these filings.

This isn't a perfect piece of analysis as some items have come and some items have gone (but that has been happening by and large around the margins of the taxonomy)

So it's probably not too unreasonable to conclude 20,000 data items is drastic overkill for comparative analysis and that our 1600 standiZd data items IS over the top! We will certainly take a close look at those 263 items to see if we need to rename! this set downwards.

So what use does this disparate tagging have? Well it does allow us to easily find the rare occasions where particular tags have been used but because use is so rare, it probably has no real advantage over them just being tagged using extensions. A simple text search, although not as neat would be just as effective, if not more so as it would pick up all (the right or wrong) occasions when an extension has been used instead.

All this prompts the question - will there ever be less data items?

How to screen for the very latest filings in Excel

Now EDGAR is very good at showing you all the latest filings. But I wanted to take it a step further in my analysis of XBRL filings for the 2017 10-K reporting season. I also wanted to do it in Excel. By the way, this is all part of the Has the dream come true? series of posts and videos which you can follow starting from here.

If you have read my previous posts, you will know I have set up a control group of companies so we can see for real in this reporting season what we can and can't do with XBRL. Now I've needed to be able to monitor those filers, just as if they were my portfolio of potential investments. So we've added a watchlist feature to Xbrl sheet. Technologically, it's just a variation on the existing queries you can run in Xbrl sheet. It uses the same query file but you set different parameters to do some very powerful screening of the latest flings at the SEC.

Great thing about Excel data queries is that you can set them up to run whenever you want automatically, so when you open your workbook or indeed at regular intervals, say every hour.

You won't always know which companies your wish to watch so we've covered that by adding the ability to screen by filing date. Say for example, show my every company that filed yesterday (whisper it but you can actually search down to the latest minute so you could set it to show you all those that filed in the last hour for example. We update our database from the SEC in real time so as soon as it's available on EDGAR, it's available in Xbrl sheet). Not only that, you can filter by industry to build a peer group of real time filings. You can specify a particular SIC code or a wider range to pick a bigger industry grouping or search by filer (i.e. find filers in the same industry as your chosen target).

Anyway this video shows you how to do all that. And once you've identified a filing, you bring down all it's XBRL tagged data using the same mechanism in Xbrl sheet.

And there's an example sheet to play with here.

Has the dream come true? - show me how I can see 10 years of XBRL data

This is the latest installment in what seems to be turning out to be a series - catch the start here - has the dream come true?

After my last post, I thought I really ought to show you all this data. Provide the evidence as it were. We've just added a few new bits and pieces to Xbrl Sheet so I also I thought it would be a good way to bring you upto date with what we've being doing over at XBRL to XL.

So it looks like this.

And you can see how I created this in next to no time (and how you can do the same) here.

And if you just can't wait and want to get hold of the data in a hurry for Neflix (or any company), you can download the Xbrl Sheet from here. Just pick up a token from the website and fire away.

Has the dream come true - Is there enough XBRL?

If you missed the introduction, then you can go back to this post.

I think I'm gonna end up tackling the "enough" question alot. Today as we stand at the beginning of the 2017 10-K reporting season, lets for now just narrow it down to the question of history.

Throughout this examination of the validity of XBRL today, I want to keep it as real world as possible, so a lot of my work in the coming weeks is gonna be working with a subset of companies from that most popular of general US indices - the S&P500. So I'm gonna draw from approx. 250* constituents to see if we can get closer to an answer.

*No I haven't just halved the index! I didn't want to work with financial companies as that just makes my life harder and I wanted to just look at companies that were going to lodge 10-K's in the current reporting window. Conveniently that left me with 256.

So before any reporting for 2017 began, we had (Source: XBRL to XL Database):

Which means not surprisingly, given how long XBRL has been a requirement, pretty much the entire population already has a five year history (not 100% as not all constituents were listed when XBRL filing started). That's a good start but even more excitingly, nearly 60% of this index population is suddenly gonna have 10 years of data! As the majority of their data points will now stretch back 10 years for the first time by the end of this reporting cycle. Companies are required to disclose 3 years of income and cash flow data (in each filing) so 8 filings will take you back 10 years for period items (9 years for balance sheet items).

In fact 4 companies (chevron corp, fluor corp, newmont mining corp, united technologies corp) had already filed 10'K's 8 times so this year will have a complete 10 year history.

10 years has always been regarded by analysts as the kinda historical period you can properly get your teeth into. The theory being that it equates roughly with an entire macro-economic cycle - from boom to bust.

And so has the dream come true?

Seeing I thought I had some stuff to say and I hadn't bloggged for far too long, I thought I'd look back at my early posts. When I started back in 2011, 2,000 companies had made their first fledgling filings. Now 11,000 plus companies have made over 170,000 filings (Source: XBRL to XL database). Now that we are swimming in a sea of XBRL, are we living in a world where analysis is easy, accurate and free (well cheap at least!)?

When XBRL started, there was a tremendous euphoria that we were on the cusp of analytical nirvana. The first mandatory filing season quickly burst that bubble. XBRL it appeared (or certainly the FASB/SEC implementation) wasn't all it was cracked up to be. Those problems are well documented but all the same I thought it would be worth listing them here to see what if anything has changed. Has nirvana sailed back into sight or is it still a case of manana?

So why couldn't I use XBRL?

There wasn't enough of it. If I'd only got a few 10-K and Q filings, how could I do any meaningful historical analysis?

There was too much of it! Over 16,000 tags, but I'm only interested in two or three hundred data points at the most!

It was complicated. A lot of the interesting numbers that were being structured for the first time were held in weird structures - multi-dimensioned hypercubes. And what the hell is a tuple?

I couldn't access it. Yes I could go to the SEC website and download it for free but then what? It certainly wasn't playing nice with Excel which was somewhat of a surprise for structured data.

This structured data didn't have any structure. Why could I get a tagged figure for Revenues for Google but not for Microsoft. How does that work? And what was I meant to do with extensions which seemed to allow Filers to bypass completely any semblance of standardisation that might exist.

So apart from too little data, too much data, obscure complicated structures, poor accessibility, evanescent structures, extensions, what else was wrong?

...Oh did I mention the errors? Filings in the early years were plagued with errors. The top three spots on the podium of errors were taken in reverse order by missing required values (like err, earnings per share), negative values (due to yet another layer of complication) and right at the top, invalid axis combinations?? Exactly! (Filers were tying themselves up in tuples trying to dimension this stuff). The most oft cited error - the mis-used extension didn't even get a look in. Pushed way off the rostrum by more basic booby traps.

So given what I've just said, the next question might seem a little rhetorical - could I trust the source? There ought to have been a simple answer to this question. Something along the lines of yes. I was no longer having to deal with a middle man to get my structured data (a vendor), it was being signed off by the company and of course audited. Except it wasn't - the audited accounts were a separate filing, the html 10-K. The xml XBRL was in a seperate file which wasn't audited and for which the Directors had no liability.

I've mentioned the missing data values but I haven't mentioned the missing data. Not everything was being xbrl'd. It only applied to the financial statements and accompanying notes. Not for example the MD & A (Management Discussion and Analysis) into which companies often like to lob a lot of interesting and important values. And yes the 10-K got the treatment but not the preceding earnings announcement.

So apart from too little data, too much data, obscure complicated structures, poor accessibility, evanescent structures, extensions, errors, unaccountability, missing tranches of data, was there anything else?

Well...it was only for US companies and so in an increasingly globalised world in which the conventional orthodoxy of free trade prevailed and your competitor could be on the other side of the world then even if you managed to straighten out your data you might not have any companies to compare all this comparative data with. So is it possible that Donald Trump could be the saviour of XBRL?

Realising that Donald Trump is not a satisfactory answer to my question (or indeed any question for that matter), I will address each of these issues in turn in my next few posts to see whether this dream is still giving analysts nightmares. For now I've run out of blog.

The SEC Structured Data Sets - uh?

This SEC Announcment may have slipped your notice, as it was slid out at the back end of last year when certainly my thoughts were more about parties than databases. The SEC announced that some of that mountain of XBRL data that's been piling up on their computers is now available as a series of flat files. The Data Transparency Coalition certainly thought it was significant.

So what are these Structured Data Sets and what does this all mean for accessing Comparative XBRL data for financial analysis? By bulking it up and stripping out the markup they've made part of the XBRL filing data more accessible but with a number of caveats. Note I said "more accessible" and not "accessible". You can't open these files in Excel - you might think you can because they are tab separated flat files but actually you can't; they are too big. They are designed to be read into a database (from whence they come). So if you quickly just want to get hold of some tagged data for a few companies from the SEC, you're out of luck. Note you can also load the XBRL instance document into Excel as XML but that doesn't make it any more readable.

But it is now much easier to read them into an database. Yes you still need to build an intermediary database. But you don't have to worry about context references and dimensions and all that messy XML tagging. e.g. In an existing XBRL filing I might find 43 values for "Revenues" but which is actually the one I want? In the flat file, num.txt, there will probably be just 3 values - one for each of the primary financial periods.

Of course you don't have to build your own database - because we built one earlier! So if you quickly just want to get hold of some tagged data for a few companies then you can! We thought it would be an interesting exercise in evaluating the worth of this pilot program. We found it relatively easy and we like what we see. We chose to add a little pre-processing to the files, to make the resulting database run more efficiently and coalesce better with our existing ones.

Our copy can be accessed in exactly the same way as all our data - through Excel. A simple web query brings the data into a sheet according to the parameters you supply. It works just like the existing XBRL Sheet. The query is available here and the example XBRL Sheet here. You can also find a video here that shows you how to get started with the example sheet. Probably worth pointing out that because access is simply through the rendering of a customisable web page - the web query, access isn't confined to Excel; many other applications and programming languages can interface with this.

So what are the caveats? It is only data from the Financial Statements themselves that are in these files. Nothing from the Notes to Financial Statements for now and new files only appear once a quarter. And it is just as it was filed - there are no corrections in these data sets.

In the next couple of posts I'll talk more about the technical aspects, our implementation and the current limitations of this initiative.

Transparent data - drilling down using XBRL

In my previous posts, I made a big play about the transparency of XBRL and how for example XBRL to XL leverages this. However in reality our application has never fully exploited these possibilities, until now. Yes you could view the “as presented” data used in the standard formula by switching to the relevant filing sheet to see the company's financial statements and notes as laid out in the paper version of the 10-K. But there was no direct link, no instant drill down.

Not only do I think that transparency is essential in a constructing a robust model and critical in ensuring you don't deal on the wrong number, it's also rather thrilling to be magically transported back from a headline figure to it's underlying components, displayed just as the company disclosed them.

The following screen shots don't really do it justice as you kinda need to see it in action so you may want to watch the video instead. The latest XBRL Sheet example, downloadable from here, contains the new "Standard" sheet that enables the top to bottom drill down shown here using the two included macros. The macro code can also be downloaded from there separately as a VBA module (Basically just a text file with the code in).

We have created two simple functions that enable you to rapidly move around the data to gain an understanding of the underlying values, where they came from and to verify their accuracy.

XDrillDown (assigned the short cut key ctrl y in the example sheet) drills down to the next layer of the data. So if you are in the "Model" sheet as in the example above, pressing this short cut will take you the components of the selected ratio. The result of which is shown below.

XDrillAcross (short cut key ctrl l - not 1 but the letter as in "level") drills across the same level to show you any of the other components in this layer of the ratio. So pressing the short cut will take you to the other component of "Goodwill/Total Assets" as shown by my arrow. Continually pressing XDrillAcross will cycle through the components in turn so another press will take you back in this case to Goodwill.

If you use XDrillDown on any of these components, it will take you to the next layer (shown below) and reveal where the "Standard" items found these values in the "as reported" XBRL "Filing" sheets downloaded using XBRL Sheet from xbrlxl.com. Now you can see just where these values came from in the 10-K or 10-Q. And again if you use XDrillAcross, it knows where the next component is, so will take you there (as indicated again by my arrow).

This is made possible by a small change we have made to XBRL Sheet. The Standard sheet (see the example above but one with "C69" circled) now has extra columns in it that represent the links to their as reported values.

How do I get back to the different layers? Well each layer is represented by a tab, so just click on the "layer" you want to look at again and the ratio or data item you previously drilled through will still be highlighted.

Just to complete the picture, if you use XDrillDown on a formula which contains a value on the same sheet (e.g. ROCE below), it will reveal every value on the same sheet using arrows (any values on another sheet will be indicated by a dotted line to a "sheet" symbol and can be cycled using XDrillAcross in the usual way). Press XDrillDown again and the arrows will disappear.

It's worth noting these functions can be used to validate any formula with components so could be used for tick back in any spreadsheet you have created with or without XBRL.

These functions aren't doing anything particularly clever, merely packaged up to use existing functionality built into Excel. I'm not big into wheel re-invention. But hopefully they will enable you to take full advantage of the transparency built into XBRL, as implemented by us in XBRL Sheet.