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.

Easy access to SEC XBRL spreadsheets via a web app

Thought I ought to act on my last blog post and make things even easier. So I've created a simple web app to speed up downloading XBRL spreadsheets into Excel. Nothing special - just saves you a few clicks and some messing around when all you want to do is analyse a company in Excel. It can be found at www.xbrlxl.com. All that's required is a simple login, a couple of clicks and away you go.

(Update)

This has been updated so you can now download and compare multiple companies using their XBRL tags. See my later post Instant comparisons of XBRL data in Excel. It's still possible to access the SEC financial report spreadsheet by clicking on the filing type (e.g 10-K) in a list of filings produced by a search.

Accessing SEC XBRL spreadsheets directly

As I mentioned in a previous post - Seeing XBRL for free, you can circumnavigate the SEC viewer and go directly to a pre-canned spreadsheet of the entire filing!

Here's the specifics of how to do it:

Use a URL with the following format:

     http://www.sec.gov/Archives/edgar/data/[CIK]/[Accession No]/Financial_Report.xls

where [CIK] and [Accession No] are the unique filer and filing identifiers you need to insert which can be found by doing an Edgar search or in an RSS feed of Edgar filings. The Accession Number must be a fixed length of 18 digits so must include any leading zero's. The opposite is true for the CIK - variable length and no leading zero's.

So if you wanted the 2011 Apple 10-K in Excel you would create the following link. Click on it and it will download.

http://www.sec.gov/Archives/edgar/data/320193/000119312511282113/Financial_Report.xls

Firefox XBRL Add-on

Continuing my trawl through the free viewers.....

As I mentioned in my previous post, the Firefox XBRL Add-on, although apparently not supported, has a couple of tricks up it's sleeve.

With the Firefox XBRL Add-on you can view any of the taxonomies used in the creation of the XBRL and you can drill down into the notes for a financial statement item by clicking on it to reveal a pop up box that will show any links, providing these have been enforced in the original document. Charles Hoffman calls this "metadata leveraging".

He also discovered that you can do some crazy stuff by dragging & dropping the headings around like you can in an Excel pivot table to totally customise the view. You can do this with any heading with a triangle in the top left hand corner. You can swap them round or drop them onto "[DRAG PAGING COLUMNS HERE]". Not sure how useful it is at this level but it's mildly entertaining. Perhaps more usefully, you can choose to view only specific data items or periods by clicking on the "Item" and "Date" headings (with the triangles in the corner) to reveal lists with check boxes. You can't however save these settings to use with other documents.

And It will turn XBRL into iXBRL and vice versa, although the iXBRL doesn’t always render perfectly but if you wanna turn XBRL into a single document to read offline, it’s fairly adequate.

The Add-on is not based on the Rivet open source code. Don't be put off by the fact that Firefox says it's not compatible with version 5. It will install and work once you relax Mozilla's strict compatibility enforcement in the options. Click on a copy of the instance document on an Edgar filing page and (after a short interlude) the add on will kick in to enable you to see the document. You can also view documents on your hard drive, although it will need a connection to download the taxonomies before it will load.

Of course the fact it is in Firefox maybe a problem. I moved over from Firefox to Chrome last year so it's not where I'd ideally like it.

Seeing XBRL for free

Continuation of previous post - Where do I want to see XBRL?

So what can the free viewers do for us? Starting with the SEC viewer, this fires up as soon as you choose the interactive data option from a list of Edgar filings. You can choose to view any of the financial statements or notes from a side menu or more significantly you can click on a link to dump all the data into Excel, except for the fact that it doesn't, as the data is already there! - more on this later as this creates an interesting possibility. You can only view one company at a time but of course you can always Edgar search for another company in a new tab in your browser. Clicking on the name of a data item brings up a pop up box from which you can expand an option to view a definition or important details such as the name of the tag.

Rivet CrossView Preview as a viewer has a number of advantages over the SEC one. You don’t have to wade through a large list of filings to find the one you want as the index only deals in XBRL. Each filing you choose comes up as a separate tab so it's easy to view more than one company at a time. The last option on the tool bar will mark which data items are from the filer's extended taxonomy i.e. not a standard item and hence which are not very useful when comparing one company with another. This option will even be greyed out if none of the data items have been extended - always a very good sign!

The catchily titled RR Donnelly XBRL Interactive Viewer does all the things the CrossView viewer does but has a worse look and feel. Also rather annoyingly, when you select a company, it shows the bottom of the list of filings so you invariably have to page up to get to the latest ones.

Most significantly, all three viewers let you download the entire document into Excel (minus the really rather useful tags) in an identical format with each section in a separate sheet in the workbook. These spreadsheets are pre-canned so in the case of the SEC viewer this brings us back to the interesting possibility that you can circumnavigate the software and go directly to the spreadsheet! e.g. this link will take you to a spreadsheet of Apple's 2011 10-K. More on this in a following post.

You can also link directly to a specific section in a report using both the SEC viewer or CrossView Preview. Charles Hoffman has more on this.

It is also worth mentioning the Firefox XBRL Add-on which, although apparently not supported, has a couple of tricks up it's sleeve which I will come to in my next post.

Where do I want to see XBRL?

To be frank, if I was an analyst, I would have no interest in viewing XBRL or more specifically the data in which it has been entwined. I don't want to view it, I want to use it, I want to add value to it. I want it in Excel. I still believe that's the best place for it, even if it comes via an intermediary database first. Analysts spent years persuading resistant data vendors to dig the data out of their databases and let it flow into spreadsheets unencumbered by restrictive proprietary software. I don't believe this paradigm is going to change any time soon. Of course there are others who take a different view.

So I'm not going to spend much time talking about the merits of one viewer over another but rather whether they can assist me in getting the data to where I want it. I will note that the only free viewers that are supported (i.e. where there are signs of ongoing development) are all web based and all use the open source code originally made available by Rivet Software at the behest of the SEC.

So don't bother with Rivet Software's Dragon View or XBRL View, both of which are desktop based but are unsupported.

I'm gonna stop here because I prefer to keep my posts short and punchy. There will be a stream of posts coming out over the next few days (well maybe not a stream - more like 2) looking at the viewers.

Making XBRL Accessible

Now that the SEC have a heap of expensively produced documents piling up that perhaps people are struggling to analyse, they have turned to the Spanish authorities for inspiration. XBRL Spain announced a competition at the beginning of the summer to create a piece of open source software that would make some sense of this stuff. Not to be outdone, The SEC announced their own competition in July with a closing date of the end of January 2012 (Edit: The competition was actually instigated by xbrl.us, a body set up to administer and promote the use of XBRL in the US). Sadly, the results for the Spanish competition were not “as expected”.

In the meantime all is not lost. Fortunately, many of the people who created this behemoth of a standard, have kindly offered their services to various software & consultancy companies, or even started their own. So you can buy whatever solution you need!

In the coming weeks I will be looking at how these various offerings, free or otherwise might help us to unlock all that XBRL loveliness.