Thursday 15 December 2011

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.

Friday 9 December 2011

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 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.

Thursday 24 November 2011

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 All that's required is a simple login, a couple of clicks and away you go.


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.

Friday 18 November 2011

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:[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.

Wednesday 16 November 2011

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.

Friday 11 November 2011

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.

Monday 31 October 2011

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.

Thursday 13 October 2011

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, 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.

Friday 30 September 2011

..... so how can you access SEC XBRL data?

Well you can't! without a parser. You can view it, as those nice people at the SEC, seeing there might be an issue here, now use a parser themselves to chuck it into a html page and a spreadsheet for you to download. But haven't you always been able to access it like this? - these files contain no tags so aren't they just the same as the html documents companies have been filing for over a decade? They are certainly no more useful for comparative analysis. Isn’t it all about the tags? This is like giving someone a Ferrari but neglecting to leave them the keys.

It should be stated at this point that if you can handle a parser - were talking about a slice of programming here, you can suck out all the goodness and re-purpose the data to fit your requirements, which is obviously what the big boys are doing.

The SEC were not the only ones to see a problem developing. Even the “Father” of XBRL began to wince at the complexity of it all and opined for something simpler, so he invented XBRLS – the “S” standing for simple! Although, as far as I am aware, this hasn’t got any further than a Wikipedia entry.

Not all regulators have taken the “full on” XBRL path. In the UK, the Inland Revenue & Companies House, the UK depository for legally required admissions, have chosen to use iXBRL as the medium of disclosure. iXBRL makes sense. It is just one document which can be read instantly by a browser but has the tags buried in it. It's almost as if Steve Jobs has gotten hold of XBRL and given it the Apple treatment. I will be looking at iXBRL in more detail in a future entry.

Friday 9 September 2011

Inaccessible XBRL

"An intelligent fool can make things bigger and more complex. It takes a touch of genius and a lot of courage to move in the opposite direction" - Einstein

When I first heard about XBRL and it's amazing capabilities from the commentators at the time, I imagined a world of disclosures in which a single document would deliver the graphic brilliance of a pdf annual report combined with the analytical precision of a spreadsheet. I anticipated that the same document could be opened effortlessly in different applications depending on how you wished to view the data.

Fast forward to today....

XBRL reporting, as implemented by the SEC at the behest of XBRL International, requires you to take delivery of no less than seven separate files.

None of these files can be read by any of the tools you find on an average desktop. Not by Excel, by Word, by Explorer or any other web browser or Abobe Reader. Not by using the latest versions. Not even by jigging them around a bit. This may surprise you, seeing Microsoft were part of the original consortium. I can only assume their involvement was nominal. Perhaps it's never advisable to send a junior to a standards committee meeting, even if they plan to spend the entire afternoon discussing data labels.

Friday 12 August 2011

XBRL and Me

Right from the early days of collecting data, I could imagine a time when data would be extracted automatically from annual reports for analysis. Back then I assumed this would be achieved by clever machines using OCR and AI techniques (Some of the software I've built recently has attempted to use similar methods). In those days, we didn’t sell structured data, just standardised financial statements and ratios on bits of paper. The widespread adoption of computers allowed for the migration of this standardised data into structured databases. But often you were putting square pegs into round holes. Our solution was to expand the data set, so whatever was reported could have its own "hole", to create the world’s first as reported database. In practice, it only had 1600 data items (compared to nearly 16,000 tags in the XBRL US-Gaap Taxonomy!) so the description "as reported" was always stretching it a bit, but we were applying that old 80:20 rule and so more often than not a data item was as described. We even had an equivalent of company defined taxonomy extensions, where if a data item didn’t fit, it would be stuffed into a tagged “other” item (ensuring it all still added up) and broken out in a custom labelled table.

To cut a long and rather dull story short, I love the thinking behind XBRL. It’s a great idea. I have though become more than little curious with regards to it's implementation.

Thursday 4 August 2011

Why was XBRL invented?

"Progress isn't made by early risers. It's made by lazy men trying to find easier ways of doing something" - The science fiction writer, Robert Heinlein

Before the advent of XBRL, comparative analysis would require the mass re-keying of data. As a man who once did this for a living, it's a enough to make you want to roll over and go back to sleep! Not only is it laborious, it's error prone, expensive (we did like to get paid!) and time-consuming (leading to long lags between publication of company financial data and it appearing on a screen near you). The Architects of XBRL were trying to solve these problems and also eliminate one further stage, invisible to user, in the production of accounting data - the transfer of the data from the accounting system to the prettified published accounts. Indeed in the science fiction world of XBRL, the numbers in the back office accounting system may not have been keyed in at all but arrive in the form of an XBRL enabled invoice.

So as ever, it's about saving time and money with the added advantage of being able to have greater confidence in data which has been untouched by error prone human hands.

Tuesday 2 August 2011

Why XBRL for Analysis?

The purpose of this blog is to record my thoughts and observations about the usefulness of xbrl published data in the process of preparing company data for investment or credit analysis and in so doing aid those considering the use of xbrl data in their analytical models. Is it the panacea that it's proponents claim or does it fall short? I've started this blog now because as of today 2,106 US companies have filed xbrl data so we are quickly reaching a point where comparative analysis of xbrl data becomes a valid option.