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.