Thursday, 12 January 2012

Looking up XBRL in Excel

This post follows on from previous one - Xbrl Comparative Analysis in Excel. And I have now created two easier ways of getting XBRL into Excel using XBRL to XL and XBRL Sheet.

When building a standard model to represent XBRL data what do we need to be thinking about?

Well my thinking is that we need to use formulas which are:

1. Easy to build
2. Replicable
3. Transparent

And the model needs to be:

4. Re-usable for different entities.

This really has very little to do with XBRL but an awful lot to do with building robust, validatory & maintainable models.

1. Easy to build. We don't want to be spending more time than it's worth building a beautiful model. So we need a standard formula (see above). At a minimum, this formula must reference three components: the relevant data item (e.g. us-gaap:SalesRevenueNet); the entity (e.g. Microsoft); the time period or instant (e.g. 30th June 2011).

Given that each XBRL tag has to be unique and that there are nearly 16,000! of them, their names tend to be long and extremely forgettable so the quickest way to reference a data item is going to be from a handy list. Again Excel is very good at lists so I'd recommend building a list of the items you are most likely to use in a separate sheet (see above), each tagged with a memorable short name (a tag tagging a tag - whatever next!). You can download the US-GAAP taxonomies from XBRL US which you can then load up in Arelle to use as a starting point. Visit Charles Hoffman's blog for more accessible versions and to learn how to navigate around these monsters.

You can then lookup the item you wish to use from your list when building a formula (either by manually selecting the cell or using yet another lookup function). Of course, if you are that way inclined, you can always build a VBA function to do this but I try to avoid these when I can, as they can create clarity, mobility & version issues that then means your model stops working when you least want it to!

As I mentioned in my previous post, it's a good idea to put each company in a separate sheet (see above) so each column references a particular sheet. You need to set this reference up in each column. I made this easy to do by naming each sheet with it's ticker so we just need to add this to each column and then use this cell in each formula. We can even make this automatic which I will come onto when (and if!) I get to point 4. I have created a fixed range "A1:E1000" for each sheet in the standard lookup formula. Potentially this might get exceeded so you need to keep an eye on this when you load new companies. We could get cleverer with this and make it self managing but I'm realising that this post is already ridiculously long! so I'm not.

We can take advantage of our knowledge of how we loaded the data from Arelle into separate sheets to gain some control over which period will appear in each column. We know the latest year of data will be column 5 (see above). So when we use a lookup function, we can specify a year relative to this (also see above). In forecasting models, the last reported year is often regarded as the current year or year "0". Although strictly speaking it isn't. That honour really belongs to the 1st year to be forecast, which is the current financial year but which is actually referred to as year "1". Still with me!? I have adopted this nomenclature in the way we specify the relative year so year "-1" is the year previous to the latest reported year. Things get way more complicated when we try to create composite years but that's a post much further down the line.

The long and short of this is that it's very easy to specify which year you want to see in each column of your model - enter a number relative to the latest year in the relative year row.

So you take the standard lookup formula, choose a data item from a list and put it in the 1st (or data item) column then set the ticker and relative year you want for each column. Simple.

2. Replicable. Now if you've put your dollars ($) in the right place (which I think I have!), you only need to create the formula once and you can copy it all over the spreadsheet and it will work. Phew! point 2 was a hell of a lot shorter than point 1.

3. Transparent. In my standard items and in my ratios, I want to know where my data is coming from so when I get a surprising value, that surprise doesn't last long! With this in mind, I would recommend that each standard item consists of one XBRL item and that this sheet is then in effect just an intermediary layer or step to your analytics. This may seem over-elaborate but trust me - this will save you an awful lot of time in the future. If you want "robust, validatory & maintainable", there is a price. We can do an awful lot more on this and I will in the future because this is important and really is my "value-add".

4. Re-usable. At some point I'm probably going to get bored of analysing Microsoft and Apple and it might even start to dawn on me that Apple will not remain a perpetual money making machine for ever so I'm gonna need some new companies, perhaps even a new model. So I bin my spreadsheet.......No wait - you don't need to - the "back end" will remain valid. You may need to add some new items to the standard layer for your new "front end" analytical model to work but as you've seen above, that's easy. It will be even easier if you do what I've done with my third example.

Each seperate sheet (see above) is now called filing (1), filing (2) etc. So now you can wipe the existing XBRL and load the new data into each respective sheet without having to re-name it. I've used the word "filing" rather than "company" or "entity" as you may wish to re-use this same sheet to do some historical analysis going back across multiple periods and hence multiple filings. This poses no problems whatsoever and you can even flick the relative year between "0" & "-1" depending on whether you wish to use restated data or not.

You maybe wondering why I've put the numbers in brackets rather than say "_1". Well as you may have noticed by now, I'm quite lazy and this fools Excel into thinking that these are duplicate sheet names, which means each time you copy the last sheet, it automatically re-names it incrementing the number inside the brackets by 1 so I don't have to! I don't know why anyone would want to use any other analytical tool - it's a slackers paradise!

Now if only we could simplify the process of getting the raw XBRL data into the spreadsheet in the first place......Ummm, guess what, I might have an idea on that. Stay tuned!

The examples used in this post are available for download from my website (follow the "Spreadsheet Examples" link. Enjoy building your models!


  1. XBRL enabled analytical applications for their persistent analysis of company submissions. The incremental capabilities enabled by XBRL are provided in two areas: 1. incrementally more correct, timely and accurate data available for analysis from the company XBRL submissions that was previously available via traditional reporting / parsing processes; and 2. collaborative modeling and analytical capabilities provided by the formula standardization.

  2. Great blog. Just downloaded a few 10-Ks. Now it's time to play.

  3. Great work Jim. Just wanted to mention that if you're interested in analytics for the financial sector you may want to check out Model Factory by Modern Analytics. This solution helps financial organizations and advisers properly guide their clients and make better use of their past data to make strong decisions for the future. Hope you'll check it out and let me know your thoughts.