XBRL Comparative Analysis in Excel using the Arelle Fact Table as a source

It's a bit messy and requires you to tidy the data but because the Arelle GUI exposes the XBRL tags with their respective values (see "Xbrl Tags into Excel using Arelle" post), we can harness the power of Excel to finally do what XBRL was supposed to allow us to do in the first place - compare companies.

Five simple steps required for each company you wish to compare.

Step 1 - Load instance document. Arelle allows you to load this directly from the web if you don't feel the need to download and store it locally.

Choose "Open Web..." from the File menu and copy the URL of the instance document into the input box (shown above). There are two ways to find this - the slow way & the quick way. The slow way requires you to click on the "SEC Search" button which takes you to the EDGAR search page, find the company you are looking for, search for the filing etc etc etc.......You may find it quicker to use the "single click" search option available from my website (see post "Easy access to SEC XBRL spreadsheets via a web app").

From my website, enter the company identifier or name, make your single click to search, then right click on the "X" (shown above) for the filing you require and choose to copy the link. Return to Arelle & paste into the input box. Crikey all this just to open an instance document! and that was the easy bit!

Step 2 - Copy the "Fact Table" to Excel. First remove all (well some) of the unwanted (empty) columns. Choose the remove random empty columns option otherwise known as right clicking on the table and choosing "options > ignore dimensions". For more on this, see previous post "Xbrl Tags into Excel using Arelle".

Step 3 - This step isn't entirely necessary but it kinda feels right (and should speed up lookups - see below). Delete all the note sections containing html formatted and therefore difficult to read text by deleting the rows containing this data.

Step 4 - Delete the unwanted columns that the "random" button failed to delete (i.e. most of them). You may need to expand the width of the columns to see which one is which. Don't use the "autofit column width" option to do this quickly as you will end up with some flipping big columns which are then difficult to re-size back to something sensible! Highlight all the columns and set the width manually to something manageable instead. I deleted 19 columns for the Microsoft 10K in the example spreadsheet (see below) to leave me with the 3 key years of data.

Now you in effect have a lookup table with 2 years of balance sheet (or "instance" data in XBRL parlance) and 3 years of "duration" data such as the income statement.

Step 5 - Except lookups won't work because an exact match is needed for unsorted data (would need to be in alpha-numeric order for it to be sorted) and the tags pasted from Arelle have leading spaces consistent with the presentation hierarchy, so another step is needed. Insert a new column in front of the tags and fill it with the following formula "=trim(B1)" using a relative cell reference to replicate the "tags" column but with the leading spaces removed (as shown above).

Now do it all over again for the next company you wish to compare! putting it in a new sheet.

Because the tags are the same* regardless of which company you are looking at, you can now use Excel's lookup functions to create standard reports for comparative analysis. Order is unimportant for exact lookup functions, which is what enables us to do this regardless of how the company has chosen to present the data.

The beauty of this is that you can load the data "as presented" whilst viewing the data in a standardised format customised to your choice. Standardised and as presented data connected and all in the same file. Now that is powerful and only possible because of XBRL. More on the mechanics of how to create a standard report for multiple companies in my next post. For now have a look at the example spreadsheet (snippets above) which you can download from my website - www.xbrlxl.com (follow the "Spreadsheet Examples" link on the left hand side).

*Unless a company has made excessive and unnecessary use of the dreaded taxonomy extensions.

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.

XBRL Tags into Excel using Arelle

I've finally found a piece of free software that will put the data with the tags into Excel. Using Arelle is not the most elegant of solutions but it will do it.

Arelle is essentially an open source project that has created a XBRL parser and related tools. It's functionality is exposed via a Python API but crucially they have also created a windows (and OS X) app that can be accessed from a GUI or the command line.

The GUI lets you get a number of views on the XBRL that you can't get anywhere else for the price. Of most interest, for an analyst trying to harness the power of XBRL in Excel, is the "Fact Table" tab.

Load up an instance document, which you can do by selecting a file from your hard drive (Arelle will even open up a zipped archive) or the web by supplying a URL such as the path to the document in Edgar*, or you can choose to download the SEC RSS feed which will list the latest filings in a tab marked "RSS". You can then right click on a filing to open the instance document, which is kinda neat. Once it's loaded (this may take some time whilst it downloads the countless schemas or forever if the XBRL.org site is down!), a series of tabs will appear on the top right hand side of the app. You can load more than one instance document at a time in which case the tabs will be replicated, with unfortunately no indication as to which tabs belong to which.

In the "Fact Table" tab you will see a list of statements which can be expanded to reveal the presented items with YES! the XBRL tags next to the data values. You may see an awful lot of unnecessary periods (or dimensions as those XBRL types like to call them) running across the top. Some of these may disappear if you right click anywhere in the table and choose the "ignore dimensions" option. No idea what it's supposed to do but I have seen useless columns vanish.

Now you can right click again to "expand all" rows, select the rows you want in the usual manner and right click again to "copy to clipboard". If you're feeling lazy and enjoy swimming in data, don't bother expanding and selecting rows, just (you've guessed it) right click and "copy to clipboard" the whole table - and it means the whole of it, not just a statement table.

Now for the magic bit - when you get to Excel, the XBRL tags HAVEN'T mysteriously disappeared! Who-hay! Now at last we can seriously consider some comparative analysis! which needless to say I'll be doing in a couple of posts time. Next up I feel I should finish off talking about Arelle and in particular whether the command line creates any useful options.

*You can quickly find the path using XBRL XL. Search for your chosen document and click on the document type (e.g. 10K) which will take you to the Edgar filing page. Right click on the instance document (invariably the first "data file") and copy the address.