Friday 6 January 2012

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.

1 comment:

  1. This post have resolved my problem,thanks very much and hope you writting more good articles.
    comparative data

    ReplyDelete