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?

Building Xbrl sheets from scratch (using our query engine)

Who are we to tell you how to analyse a company? That's why our Xbrl example spreadsheets are merely that - examples. The tech behind it is simple, been around for years (ie. works!) and is easy to deploy. The examples provide a starting point and hint at what's possible. You build on them to create a custom sheet that meets your specific analytical needs or you can start from scratch.

So how do I build a sheet from scratch? Well watch this short video.

And these videos will also give you a flavour of how to customise existing queries as they demonstrate the example sheets in use.

You can add bells and whistles like set them to automatically update when you open a sheet or provide extra layers of automation by incorporating them in a VBA macro to fully harness the power of Excel.

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 technically speaking

In my last post The SEC Structured Data Sets, I talked a little about this new SEC initiative to make XBRL more accessible. This time I'm gonna major on how it works.

At this point you may want to refer to the SEC technical document Annual and Quarterly Financial Statements, the Financial Statement Data Sets page (where the files reside) and if you want to see what the data contained in these files actually looks like, you can download one of our example spreadsheets here. The web queries in this sheet access XBRL Flat, our name for this data set. The sheet itself contains links to videos & info on how it all works. I will talk more about our item for item implementation of this data set in my next post.

On the Financial Statement Data Sets page, you will see there are currently 24 files. After we pass the last business day of this quarter (March 31 2015), they'll be 25. Don't try to open the latest files in Excel - they're too big but you could download one of the early ones to take a peak at the data layout of the files contained in these zips.

As the comprehensive technical document explains, there are 4 files. The one that counts is the num.txt. This has the values. In theory this file by itself has enough in it to do your analysis - values matched with dates and most importantly, tags for each filing. The files are not cumulative so you need to access each one to be sure of finding your filing. This is the point, in other words, where you need to load all these files into a database. If you load it all, its gonna be big (over 10 gig for starters).

The filings are keyed on the Accession Number (adsh) which is what EDGAR uses, so if you want to find the values for a particular company, you need to look up the adsh. This is where the sum.txt file comes in, which contains the company names & CIK's, so you need to load this into another table. Of course you could just find the adsh by going to EDGAR or our website - if you select a filing from xbrlxl.com, the adsh corresponds to what the aNo = in the address bar, but the adsh adds some annoying dashes! (In our implementation, we use a more comprehensive and timely database for these lookups - this is what you see at xbrxl.com).

You could stop there, as for example all the values we download in XBRL Flat come from just these two files. But if you want to see what the company has called these data items and if like us, you are sticklers for as reported data, then pre.txt contains the layout along with the labels. The final file, tag.txt contains important information on the tags but you may consider it not important enough.

So what to watch out for? Duplicate values! - surely impossible but no it's been seen and verified in the original filings. And the fields aren't quite in the order shown in the documentation so use the header records. Also you may want to exclude any records where the coreg field is populated, as more than likely you ain't looking at a value for the entire consolidated entity in these cases (I anticipate this will become more prevalent and relevant when they release values for the notes).

Two small bits of standardisation have occurred in these files that are not explicitly documented.

The financial statement headings do not have standard names and tags in the US-GAAP implementation of XBRL i.e. what's in the original filings (Yes I know - ridiculous!) but they do now in this SEC data set; these names and codes (or shall we call them tags?!) are actually all listed in the technical document.

The only slight problem is that a filing can have two financial statements which bear the same code (e.g. BS). One for the consolidated group and one for the parent. Can you tell the difference? Er No. Of course the parent one should have drastically less items. But of course if I'm gonna read this with a computer, I have to go through the hassle of counting items or something and that of course is not necessarily an exhaustive solution. There is also a code called UN (Unclassifiable Statement) which suggests that the SEC classifications may not themselves be exhaustive! I don't actually know why I'm going on about this as we solve this problem (differently) in our full database.

Secondly, the month end of the dates attached to each value have been standardised. This is good and bad. Good as it makes searching and aggregating easier, unless you were specifically looking for values at Apple's year end (27th Sept 2014) when the values are held as 30th Sept. Bad as those few companies that don't adhere to standard month end periods (last Friday of the month etc) can have say 51 week or 53 week years and you wouldn't know it from the num.txt file. This could lead to say revenues being over or understated by 2% on an annual basis, more so if quarterly. To pick this up, you need to keep an eye on the period date in the sub.txt file (don't use the fye field as its filled in inconsistently - 0927 for Apple but 1130 for adobe).

Probably worth re-iterating that no additional standardisation of data values has occurred in this data set. For more details on what needs to be done, see my Missing Totals post.

Next time I will explain how we have replicated this database and what you can do with it.