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!

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.