Which source of Company Financial Data should I use for my model?

If we want to create a model of a company's future performance, we need a starting point, a set of inputs which we can examine, adjust and extrapolate our best estimate of how we anticipate the company will perform in the future.

So what are our choices?

1. Lifting values straight from the Financial Reports filed by a company
2. Data Vendor - Bloomberg, Thomson Reuters, Capital IQ, FactSet etc
3. XBRL

I believe XBRL provides the best possible place to start. Why?

Well, as my old Physics teacher was never short of telling us, lets go back to first principles.

Our ideal starting point would be a perfectly accurate picture of how a company is performing right now. We can’t get this however for two reasons:

1. We don’t have real time access to companies accounting systems so we are constrained behind the curve by the reporting calendar.
2. We can only see the published external numbers, the numbers that the company allows us to see, subject of course to any legal disclosure requirements or the opinion of their auditors.

So even this, our best source, is inherently flawed, so we must be ready at all times to make adjustments/correct the figures put in front of us. Despite these caveats, the company still has to be our first port of call because only they have the closest and best view of the current operating performance.

So why would we use a data vendor?

Well their starting point is exactly the same but what they do is prepare the accounts for financial analysis. If you start from the Financial Reports filed by a company, preparing every single company this way is expensive, which is why they will charge you thousands of dollars for the privilege. Criticisms levelled at data vendors in the past have been that they don’t always get the figures right and that its not always clear how and what adjustments have been made.

Also a standardised approach can be problematic as the Corporate Finance Institute note:

“Companies such as Bloomberg, Capital IQ, and Thompson Reuters provide powerful databases of financial data. However, financial statements retrieved from these databases tend to be in a standardized format. Thus, if the company uses an accounting value unique to its business operations, you will not grasp it from data retrieved and it will affect your analysis.”

This is why in critical decision making, when an investment decision or M&A deal could be worth millions of dollars, vendor data would never be used as a starting point for a single entity centric model. Of course this data has value in screening and modelling whole markets or sectors but even here, for the reasons stated, they are potentially flawed inputs.

So what does XBRL bring to the party?

XBRL takes the effort out of lifting the financial values from the report and provides a first pass at standardization. Not normalization mind, but a first step in that process if your goal is vertical analysis against a company’s peers. As I discuss in this article, unfettered XBRL, as filed with the SEC and made available through Edgar or alternatively for a fee via the XBRL.US API, does not, nor indeed intend to, provide a perfect set of standardized values.

By harnessing the XBRL tagging, your models can be automatically derived from genuinely as reported values, the closest view of past operating performance direct from the company and untampered by any third parties. The hard graft of lifting values, monotonous, error prone and time consuming, is removed. But as I underlined above, this is not quite the finishing point. Most of the hard work is done but we must always be ready and prepared to make a few adjustments*. They will undoubtedly be required.

*How you can easily make these adjustments is discussed here and in the following video. If you want to read more about the need for adjustments in XBRL, then check out the next post in this series.

You can also read about totaliZd from Fundamental X here, our complete solution for preparing XBRL derived inputs to financial models in Excel.

Has the dream come true? - show me how I can see 10 years of XBRL data

This is the latest installment in what seems to be turning out to be a series - catch the start here - has the dream come true?

After my last post, I thought I really ought to show you all this data. Provide the evidence as it were. We've just added a few new bits and pieces to Xbrl Sheet so I also I thought it would be a good way to bring you upto date with what we've being doing over at XBRL to XL.

So it looks like this.

And you can see how I created this in next to no time (and how you can do the same) here.

And if you just can't wait and want to get hold of the data in a hurry for Neflix (or any company), you can download the Xbrl Sheet from here. Just pick up a token from the website and fire away.

Has the dream come true - Is there enough XBRL?

If you missed the introduction, then you can go back to this post.

I think I'm gonna end up tackling the "enough" question alot. Today as we stand at the beginning of the 2017 10-K reporting season, lets for now just narrow it down to the question of history.

Throughout this examination of the validity of XBRL today, I want to keep it as real world as possible, so a lot of my work in the coming weeks is gonna be working with a subset of companies from that most popular of general US indices - the S&P500. So I'm gonna draw from approx. 250* constituents to see if we can get closer to an answer.

*No I haven't just halved the index! I didn't want to work with financial companies as that just makes my life harder and I wanted to just look at companies that were going to lodge 10-K's in the current reporting window. Conveniently that left me with 256.

So before any reporting for 2017 began, we had (Source: XBRL to XL Database):

Which means not surprisingly, given how long XBRL has been a requirement, pretty much the entire population already has a five year history (not 100% as not all constituents were listed when XBRL filing started). That's a good start but even more excitingly, nearly 60% of this index population is suddenly gonna have 10 years of data! As the majority of their data points will now stretch back 10 years for the first time by the end of this reporting cycle. Companies are required to disclose 3 years of income and cash flow data (in each filing) so 8 filings will take you back 10 years for period items (9 years for balance sheet items).

In fact 4 companies (chevron corp, fluor corp, newmont mining corp, united technologies corp) had already filed 10'K's 8 times so this year will have a complete 10 year history.

10 years has always been regarded by analysts as the kinda historical period you can properly get your teeth into. The theory being that it equates roughly with an entire macro-economic cycle - from boom to bust.

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!