Transparent data - drilling down using XBRL

In my previous posts, I made a big play about the transparency of XBRL and how for example XBRL to XL leverages this. However in reality our application has never fully exploited these possibilities, until now. Yes you could view the “as presented” data used in the standard formula by switching to the relevant filing sheet to see the company's financial statements and notes as laid out in the paper version of the 10-K. But there was no direct link, no instant drill down.

Not only do I think that transparency is essential in a constructing a robust model and critical in ensuring you don't deal on the wrong number, it's also rather thrilling to be magically transported back from a headline figure to it's underlying components, displayed just as the company disclosed them.

The following screen shots don't really do it justice as you kinda need to see it in action so you may want to watch the video instead. The latest XBRL Sheet example, downloadable from here, contains the new "Standard" sheet that enables the top to bottom drill down shown here using the two included macros. The macro code can also be downloaded from there separately as a VBA module (Basically just a text file with the code in).

We have created two simple functions that enable you to rapidly move around the data to gain an understanding of the underlying values, where they came from and to verify their accuracy.

XDrillDown (assigned the short cut key ctrl y in the example sheet) drills down to the next layer of the data. So if you are in the "Model" sheet as in the example above, pressing this short cut will take you the components of the selected ratio. The result of which is shown below.

XDrillAcross (short cut key ctrl l - not 1 but the letter as in "level") drills across the same level to show you any of the other components in this layer of the ratio. So pressing the short cut will take you to the other component of "Goodwill/Total Assets" as shown by my arrow. Continually pressing XDrillAcross will cycle through the components in turn so another press will take you back in this case to Goodwill.

If you use XDrillDown on any of these components, it will take you to the next layer (shown below) and reveal where the "Standard" items found these values in the "as reported" XBRL "Filing" sheets downloaded using XBRL Sheet from xbrlxl.com. Now you can see just where these values came from in the 10-K or 10-Q. And again if you use XDrillAcross, it knows where the next component is, so will take you there (as indicated again by my arrow).

This is made possible by a small change we have made to XBRL Sheet. The Standard sheet (see the example above but one with "C69" circled) now has extra columns in it that represent the links to their as reported values.

How do I get back to the different layers? Well each layer is represented by a tab, so just click on the "layer" you want to look at again and the ratio or data item you previously drilled through will still be highlighted.

Just to complete the picture, if you use XDrillDown on a formula which contains a value on the same sheet (e.g. ROCE below), it will reveal every value on the same sheet using arrows (any values on another sheet will be indicated by a dotted line to a "sheet" symbol and can be cycled using XDrillAcross in the usual way). Press XDrillDown again and the arrows will disappear.

It's worth noting these functions can be used to validate any formula with components so could be used for tick back in any spreadsheet you have created with or without XBRL.

These functions aren't doing anything particularly clever, merely packaged up to use existing functionality built into Excel. I'm not big into wheel re-invention. But hopefully they will enable you to take full advantage of the transparency built into XBRL, as implemented by us in XBRL Sheet.

Instant comparisons of XBRL data in Excel

You want to compare 5 companies using your own unique, highly sophisticated model in Excel. No problem - just visit xbrlxl.com, click the "XBRL to XL" button, select the companies, choose your filings & click the "XBRL to XL" button again. You will then have a spreadsheet on your desktop crammed full of XBRL data ready for Analysis.

(Update: You can now access this data directly from Excel using XBRL Sheet)

Why is it Analyst ready?

1. It comes with the tags so cross-company comparisons are possible.

2. The data is wholly transparent - miraculously the data arrives in Excel just as it appears in the tables of a 10-K or 10-Q document yet through the power of Excel, any XBRL data item can be called up for use in creating standard formulas. Of course this link can be traced back to show in effect where in the original report your normalised data came from. More than that it shows you if any of the data items used by the company are not standard (potentially compromising any comparisons - something that is not visible in the original financial reports).

3. It's "readily" customisable - You choose the years & data items you wish to use. What you can then do with the data is limited only by the near unlimited power of Excel and it's sister applications in Office.

Right that's the sales pitch out of the way! In my previous post I looked at how you could hook up XBRL to a financial model in Excel. I used an Arelle Fact Table from the Arelle GUI as the source but wondered whether this could be made much, much simpler. XBRL to XL is the result of my musings.

XBRL to XL is very easy to use. There are only 2 types of button you need to worry about. The 5 filing buttons and the magic "XBRL to XL" button so 6 in all. So when you have filled in the search fields for your 1st filing, you press the "Filing 1" button. When you want to search for your 2nd filing you press the "Filing 2" button and so on. This is the only thing you really need to remember as if you press the top button ("Filing 1") when you were actually trying to select the 3rd filing, it will replace the details of the 1st filing rather than put them in the 3rd slot.

The more specific you can be with your search criteria, the faster the search, as the filing details of the five filings will be automatically populated in the filing selection box ready for processing. If you know exactly which filings to include, the whole process will take no more than seven clicks (one to select each filing, one for XBRL to XL to do it's thing and one to confirm your download.

The same 5 "Filing" buttons are used if your initial search has not been specific enough and you need to select a company and/or a particular filing. Click on the check box of the particular company/filing you wish to select and then press one of the 5 filing buttons again, depending in which sheet you wish it to appear. You can keep replacing filings in the filing selection box using the filing buttons until you are happy with your choices.

Note these filings can be from 5 different companies or 5 historical filings from the same company or a mixture. There are no restrictions.

Now press the "XBRL to XL" button to get your filings to Excel. XBRL to XL goes and gets the required filings from the SEC and processes the data into Excel. The processing stage for each filing is highly optimised and takes approximately 2 seconds per filing plus the time it takes to retrieve the XBRL instance documents from the SEC.

Once processing has been completed, you can choose to download the spreadsheet or a zipped version. The downloaded spreadsheet can have up to 5 filings of XBRL data stored in separate sheets which will automatically populate the model on the "front" sheet. The current model that is downloaded is very sparse and is there by way of example (for you to improve on!) The mechanics of how this model is connected to the XBRL data is explained in the previous post Looking up XBRL in Excel.

So there you have it - Analyst Ready data in Excel and represents my entry in the XBRL Challenge set by XBRL US. More details can be found on the fact page for my competition entry. You can also watch "XBRL to XL" TV where you will find step by step guides to downloading and using the data.

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!