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.

XBRL from the Arelle Command Line

So continuing on from my previous post, what else is there to say about Arelle?

Through the various windows and tabs, you can see just about every piece of information that's contained in a filing's XBRL files minus the confounded XML. For example the "Presentation" and "Calculation" tabs two along from the "Fact Table" tab basically show you what's in a filing's "_pre.xml" and "_cal.xml" files, demonstrating the relationships between the various data items.

The app is well supported with documentation available with the install and on the web site. The development team seem active, replying to a query I raised within 24 hours.

The command line tools are of particular interest as it means there is the potential to automate the extraction of data to Excel. And as an analyst what you want is an automated "one button" solution. You don't want to be wasting time messing around with the data you wish to analyse.

The command line executable enables you to set arguments to extract the XBRL (or more precisely and more usefully! - the data minus the XBRL) to a "csv" file that can be opened directly in Excel or loaded into (or referenced from) an existing spreadsheet. There are seven options but only three are of any interest. "--csvFacts" enables you to extract the "Fact List" (what's in the Fact List tab). Note this is not the Fact Table (darn!) and is essentially all the data items in the instance document. The "--csvPre" and "--csvCal" options unsurprisingly serve up the "Presentation" and "Calculation" tabs respectively.

Using the command line is best done using a batch file (.bat) which can be scheduled to run automatically by the operating system. An example batch file for use with the "csv" options is provided in the "scripts" directory of the Arelle install. The file is called "exportCsvFromXbrlInstance.bat". You can edit this to create the "csv" file you require. Don't double click on it to edit it as it will run! but open it from a text editor.

As you can see from the batch file, you can customise the columnar output of "--csvFacts" using the "--csvFactCols" argument. Note that contrary to the documentation on the website, you need two dashes in front of the arguments and not one. The "Fact List" delivers a data value for each period on a separate line (as per the instance document) so it requires a great deal of manipulation in Excel to get it looking sensible. It could provide a useful feed into an intermediary database such as Access but my interest here is Excel. So despite the degree of automation afforded by the command line, to get something more immediately useful we need to head back to the "Fact Table" generated by the Arelle GUI.