Thursday, 17 January 2019

Why Verified and Adjusted XBRL is the Best Choice for Company Analysis

If you read my last post Do Extensions make XBRL Unusable out of the Box?,  you will know that using XBRL without adjusting the tagging could well be calamitous.

So what are your options? Well in the post before that Which Source of Company Financial Data should I use?, I set out the options for finding the best source for comparative analysis. Lets re-iterate them here but I'm also gonna add one further choice.

1. Lifting values straight from the Financial Reports filed by a company
2. Data Vendor - Bloomberg, Thomson Reuters, Capital IQ, FactSet etc
3. XBRL (unadjusted)
4. Verified and Adjusted XBRL (VAXBRL)

The first choice is only an option if you have ridiculous amounts of time. The second if you have ridiculous amounts of cash and you are willing to take the risk the data is completely error free and has been interpreted correctly. Vendor data by its nature has been handled by a third party so really needs to be verified if it is to form the basis of an expensive transaction. This is why it is never used as the primary source for in-depth company analysis in critical departments such as M&A.

My previous post explains the danger of using neat XBRL. Over half the Dow Jones 30 companies had used extensions in a way that could lead to significant errors in your analysis if you just plugged the raw XBRL into your model. Only 20% of companies had no extensions on the face of their Primary Financial Statements. And if the trend over the last five years is anything to go by (which I also examined in my analysis - I was interested to see what that fifth year of data might look like compared to the first), it not going to get any better.


There is a viable alternative. And it is not expensive. And by expensive I mean in terms of time, that most precious of commodities. VAXBRL. Just spend a little time verifying and adjusting the XBRL. This way you'll always know the provenance of the source (direct from the company). And with the right tools, it can become an easy and inherent part of your financial modelling process over which you will have complete control.

What you'll end of with is a set of financials better than any vendors at a fraction of the cost. Now that I think is what the XBRL revolution was supposed to be about.

Of course I wouldn't be telling you this if I didn't have a set of tools up my sleeve that might be just the job. Take a look at our totaliZd product. Download a totaliZd X Sheet at work (ready for adjustments). An adjusted version and video is coming next week.




Monday, 14 January 2019

Does the current use of Extensions make XBRL unusable out of the box?

Yes. You can't just plug it in.

But does that make XBRL useless? No. It just means you'll need to make a few adjustments to the tagging. A small extra step in the process with the right tools. If you don't, if you insert raw XBRL numbers, the ones you can get from the SEC or XBRL.US straight into your models, your analysis will be flawed. I'm not saying it might be flawed - it WILL be flawed.

I say that because I've looked at the following table. Well actually I compiled it. It shows the incidence of extensions for all 25 of the non financial companies in the Dow Jones Index (DJ30) for the latest published XBRL income and balance sheet and was compiled using our X Sheet. As long as you only want to compare Apple against Intel you should be fine or the other lone troika of companies that have no extensions on the face of the primary financial statements. Otherwise you've got a problem.



You can download the full spreadsheet here.

I chose to look at the face of the two principal financial statements for the DJ30 because I wanted to look at the best case scenario. The most comparable values tagged by theoretically the best resourced companies, supported by the top audit firms and therefore capable of delivering pinpoint tagging. The tags I looked at of course represent a fraction of the tagged elements in an annual 10-K, and being the most commonly summarised amounts, the ones in theory least prone to being extended. I excluded the financial companies as they are a bit more peculiar so maybe even more liable to be extended. Like I said I wanted the optimum target result.

Just in case you are not familiar with extensions, an extended tag is a useless tag. That's being a bit harsh but it does make comparative analysis exceedingly difficult, without intervening to pull it back into the US-GAAP taxonomy (the list of standard tags). The idea is that companies can extend this standard taxonomy by adding their own bunch of tags on top, where items don't fit into the list. This undoubtedly legitimately occurs and this is part of the beauty of XBRL, that it allows for this. It wouldn't be XBRL (eXtensible Business Reporting Language) without it. Unfortunately, in reality, it is up to the company to decide when this is so.

And so ineffectual is this type of extended tagging, that when Europe joins the XBRL party next year, ESMA has specified that this type of disconnected extensions will not be allowed. Every value will have a backstop connection to a tag in the IFRS taxonomy and if that does not fully describe the item in question, an extended alias maybe used as its primary tag.

So having said all that, I'm not sure we should be seeing so many extensions on the face of the financial statements. Surely they should pretty much all look like Apple. Exceptions that prove the rule not make a mockery of it. Anyway I'm not gonna dwell on that. It is what it is. A more pertinent question then is whether these extensions really matter and if so what we can do about the data we have in front of us. This is why the table is not just a simple count of extensions but an examination, albeit cursory, of the potential impact on meaningful company analysis.

We've had a lot disconcerting reports on extensions before but I wanted to make this inquiry as real world as possible. So the top level items and my subjective assessment of  the likely impact on KPI's or the ability to strip back figures for future forecasting. So some extensions I have deemed as insignificant, some likely to have a minor impact and others, which set off a big red flashing light as, major. So where a total, which can be calculated by combining its disclosed components using established taxonomy data validation rules, has been extended, this is deemed as insignificant but where restructuring charges have disappeared from the standard taxonomy by virtue of an extension, I have considered this a major problem. I have sometimes paid attention to the size of the amount but really it is not necessarily a reliable marker as for example in some years restructuring charges can be massive and others minuscule.

Anyway you can make your own judgement as I have made the spreadsheet containing the table available so you can use the X Sheet to see all the extensions for yourself by looking at the Filings tabs. Also by looking at the totaliZd tab, you can quickly see the scale of the impact on a comprehensive set of standard values designed for analysis (we call our version the X Financials). The scaling option, a feature of totaliZd, has deliberately been set to billions so you can more easily see the significance of any impact. A value of one could even be significant within or without its group at this level of magnification.


As the screen shot above shows, any amount in the square green boxes, is a problem that needs fixing. How you do this I will probably cover in the next post.

What you see in totaliZd is as bad* as it can be. That is not a comment on the individual companies involved but the limitations of using neat XBRL. This is the point of totaliZd, to provide a starting point, from which you can quickly fix this apparent mess and leverage XBRL to perform the kind of analysis never previously possible before. I'm not dissing XBRL. I'm simply qualifying how you use it.

*Things get off to a very bad start for Exxon and Coca Cola as neither them supply a Revenues tag. This isn't even an extension problem. This is just a "not using enough us-gaap tags" problem. Problems like this automatically get fixed in the X Sheet when using our standiZd option.

This post could really be considered part of a series I started in 2017 - And so has the dream come true? to look at the state of XBRL disclosures and examine the continuing validity of the objections raised in using it.


Thursday, 10 January 2019

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

We 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 starting point 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 discussed 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.

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

Friday, 4 January 2019

...taking control of an Excel Power Web Query is another thing entirely

This post is the follow up to Getting XBRL in Excel is dead easy with Power Query.

Now to gain control of a "legacy" web query, to dynamically add your own variable parameters, say input a ticker into an X Sheet, you had to feed the query into Excel via an iqy file (internet query - this tech was actually introduced by Microsoft way back in the last millennium).

You no longer have to mess around with peculiar files in Get & Transform but in a lot of respects its way more complicated. Because Power Query is about Tables, and its about Tables; did I mention its about Tables? Power Query thinks in Tables, all it outputs is Tables* and what you input better refer to a Table somewhere along the line or there will be no output.

*Well strictly speaking you can specify that it outputs part of a table so you can squeeze a solitary value out of it.

So you need to know about Excel Tables and you probably need to know a little bit of Power Query M (The query language that powers Get & Transform). There is some good news, you don't need to learn all of this if you don't want to, as we've done it for you, and as always, in the name of transparency, we will try to explain along the way what we've done and how its doing it. And maybe throw in the odd video as well.

Moving to Tables is mostly a good thing. It encourages good practice in the way you treat and organize your data but it does mean you are entering the realms and rigidity of database structures (sort of Microsoft Access by the back door!). But it does mean (at least while you've got your head stuck in a query) you have to leave behind some of the lovely freedoms of Excel. The quaint idea that a cell could be any old data type depending on what turned up doesn't wash in a Table.

There are three ways of connecting a dynamic variable, each slightly more complicated and they are all demonstrated in this example Excel Workbook:


1. Put your dynamic variable in a Table. A Table! Now with a Table, Power Query can potentially see this variable and so we can edit our original query to reference it.

2. Create a new query to reference our new Table and and edit our original query to reference this new query. Cleaner solution and if we need to transform our variable, we have the space to do so in this new query. Also we now have a dynamic variable query that we can re-use.

3. Even more editing to turn our second query into a Function. This is what we do in the X Sheet.

Whichever option you choose, you are going to come face to face with the Power Query M Language. With the third option, you will also need to enter the Advanced Editor.

Now I'm not gonna run through each stage of the process of creating the above three solutions because I'll be here all day but we have videos to show you exactly how to achieve each result. I will though highlight here the key components in each approach and any other useful observations.

1. Simple Table reference. Watch the video.

Once you have created your new table, you need to replace the static variable in the original web request with this:

Excel.CurrentWorkbook(){[Name="TableName"]}[Content]{0}[ColumnName]

So the key line in your original web query will look something like this depending on what you named your Table and its solitary Column:

Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=0&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker])),

NOTE: Your variable doesn't have to be in an Table. You can just use a Named Range. It works because a Named Range is in fact a pseudo table. What you have to remember is that a Named Range has no column name so it enforces a default column name on it. So your ColumnName in this circumstance is Column1.

2. New query to reference our new variable Table. Best to watch the video.

You can easily make a query out of an existing Table in a sheet via Get & Transform. Tables are very keen to define each Column in a Table as a specific type. We need to change the Changed Type Applied Step to:

Table.TransformColumnTypes(Source,{{"Token", type text}}){0}[Token]

This also insures that we return a value rather than the whole table by adding the "{0}[Token]" 1st cell reference to the end.

The main query can now reference this new query which we called Authenticate in the video:

Source = Web.Page(Web.Contents("http://xbrlsheet.xbrlxl.com/?tkn=" & Authenticate & "&eid=" & Excel.CurrentWorkbook(){[Name="Company"]}[Content]{0}[Ticker]

In the video we actually managed to make all the changes without having to tussle with the Power M Query Language in the Advanced Editor.

Best to save/convert the query to a Connection Only query. Otherwise you'll end up with a new unwanted Table in a new sheet every time you exit the Query Editor. You need to find the "Load To..." option to do this.

WARNING: If you are on the default (strictest) Privacy setting, the main query won't run and you will get a message something like this:

Formula.Firewall: Information about a data source is required.

or:

...references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Excel, rather pathetically, can't distinguish between a local (our new variable Table) and an outside source as this juncture. By converting this query to a Function, as we do as the next option, resolves this piece of stupidity. We can of course also set our Privacy level either at the Workbook level or in  the Query Settings in the Query Editor (File>Options and Settings>Query Options>Privacy) to "We don't care" (Always Ignore Privacy Level Settings).

3. Turn our new query into a Function. And check out this video if you want to beef up the power!

This is our favorite option, the power Power Query option but you do have to dip your toe into the Advanced Query editor.

It saves the query from trying to create a new table in Excel every time unless you make it Connection Only and circumvents the mindless security objections as described above. You can also, rather usefully, opt to pass a variable to a Function as we do in the X Sheet. Its like proper programming! These advantages are spelled out in more detail in a post on my other blog - Jot About Excel. And again of course the Function you build is re-usable in other queries.

And in reality the changes are fairly simple. Top and tail your query with these two lines:

let Years = (YearNo as number) =>

in Years

where Years is what we named our new Function and YearNo is the variable we pass it (just use empty brackets if no variable is required). As you can see, if you are familiar with Lambda expressions, its uses a similar syntax.

Friday, 7 December 2018

Getting XBRL in Excel is dead easy with Power Query...

Constructing a web query in Excel using Microsoft's new wonder query engine to access XBRL is really simple.

Starting with Excel 2010, Microsoft started peddling a new analytical add-in designed to bring data of any shape, size and colour into Excel in one big mashup. An analysts dream.

In Excel 2016 and Office 365, it became part of the fabric and got a new name - Get & Transform; Microsoft's chosen way to source data from within and without Excel. So much so that the way we used to, well still do, run web queries at XBRL XL to pull in XBRL is now described as a "legacy data import".

The web query is dead. Long live the web query! Yep web queries are still a vital part of the mashup and are immediately accessible from the Get & Transform area of the Data Ribbon.

I have to say, having spent the last 6 months, pulling it and tangling with it, its still a bit ropy round the edges. There are bugs and you can do the same things in confusingly different places. It doesn't all quite fit together but it will do and when it does its going to be epic.

So we've started to play with it. And you can see the results in this video.

We designed our XBRL data to be accessible by the most universal route possible. So you could query our data via the most basic of web protocols to download simple tables.

So a web request to access XBRL from the latest 10-K from Microsoft looks like this:

http://xbrlsheet.xbrlxl.com?tkn=0&eid=msft   (Get a free, more useful token at our website)

And a query to give you precise control of what filing you get back:

http://xbrlsheet.xbrlxl.com?tkn=12345&eid=msft&dat=20170630&fil=1&typ=fsn

fil=1 brings back a 10-Q, fil=4, 10-K. typ=fsn brings back the financial statements & notes.


So if you choose Get Data/From Web from the Data Ribbon and insert one of the above web requests (with a token that works rather than the placeholders above - tkn=0 works but its a groundhog - same filing keeps coming back), it will go away and bring back four tables. If you select Table 3 and press load you will instantly have a spreadsheet full of XBRL financials just as the company reported them.


As it comes with the tags, you can do lookups to pick up precisely whatever values you want to include in your models. You can see these at work here in our Power Query version of the X Sheet.

Of course to get a different filing all you have to do is use a different company ticker. Except how do you do that in the Power Query we've just built? Well you could run through the whole rigmarole again and build another Power Query, putting a different ticker in the web request, and as we saw that's relatively painless. But there's got to be a better way, right? A way to take control of the query? Well there is but it is less than straight forward and you'll need a whole new tool set.

In the meantime, you can use one we prepared earlier, The X Sheet . When we come back, we will show you how you do it. Till then we suggest you brush up on your knowledge of Excel Tables!

So now you can see exactly how to take control of an XBRL Power Query in the next post...



Friday, 5 May 2017

Will there ever be less XBRL data items?

If you read my previous post, you will know that the arrival of the SEC US-GAAP Taxonomy unleashed a veritable tsunami of data items on a concerned body of filers and a bewildered bevy of investors.

So with so many data items milling around, you would have thought that someone or something might be sent out to clean it up. Well the good news is that in late 2014, the FASB launched the Xbrl Taxonomy Simplification Initiative.

And now the not so good news...


Looks good until you realise that this represents the rate of change. In other words you don't really want the blue line exceeding the red line. But you can actually see the impact of the 2014 initiative as the red line finally jumps above the blue one through 2015/2016 reaching a peak of 681 deprecated items in 2016. I have to say I still find it staggering that there are so many items to choose from given how few items companies actually disclose in their 10-K's and Q's.

So is there any way out of this forest? Well all is not entirely lost because in March 2017 the SEC finally agreed to allow foreign filers to use the IFRS taxonomomy. This is good on two counts. One it means that all foreign filers will for the first time be required to file XBRL which starts to answer the is it only for US Companies question (more on this later!) and two it establishes a precedent that a taxonomy does not have to have 16,000 data items.

The IFRS Taxonomy which is designed to satisfy the filing requirements of every country around the world, not just one large country, has only 4,610 items. It did have a lot less (47% less to be precise back in 2011 when it was originally scheduled to be mandated) but I think they were told to bulk it up a bit if they wanted to get SEC approval (sound of head banging on table). A group was tasked with adding items by custom or practice (and approx. 1,000 Common Practice items have been added since then). During this process no specific consideration is given to how useful an item's structural relationship might be - this is sadly not the philosophy behind either the IFRS or US taxonomy. Well at least 4,500 is a lot better than 16,000.

IFRS based XBRL is likely to be adopted for mandatory filing in Europe in 2020 and one would imagine the rest of the world will fall into line after that (completely answering the US Companies only question). Most of the world now uses International Financial Reporting Standards or has standards based on them.

I should point out that I am a member of the IFRS Taxonomy Consulative Group (ITCG). But this is not why I'm a relative fan of the IFRS taxonomy. It's just got less items in.

Tuesday, 2 May 2017

What do filers do with all the XBRL data items at their disposal?

This is the latest in a series of ponderings on whether the XBRL dream has come true?

In our XBRL to XL database, we have 20,000 data items that originate from the US-GAAP Taxonomies. That's a heck of alot of data items. Admittedly that includes alot of bumf that is just there for presentation or connecting stuff (abstract items) or axis (column headings) and items that have subsequently been deprecated. But compare that to S&P Compustat's 500 odd or the 1600 data items we use for our standardisation routines.

I guess the FASB were worried about companies using extension tags, although perhaps there are more direct routes of limiting i.e. prohibiting extensions.

Of course the more there are, the greater the risk of mistakes. Choosing the wrong item over another in the same section is not easy to detect. Usual validation routines won't pick it up. And none of this stuff is audited. Ultimately it is up to the company, or whoever they've outsourced their XBRL tagging to, to pick it up.

The kinda advice stressed by Ernst & Young on the release of new taxonomies is typical and not surprising.

"As they move to a new taxonomy, companies should review their practices for selecting XBRL tags and thoroughly search the tags in the new taxonomy".

So what value do we as users get out of all this wonderful detail?

Well lets have a look at disclosure levels for our probably over the top standard data set of 1632 items (lets refer to this set as the standidZd 1600. So a couple of weeks ago I did a chop of our SEC XBRL to XL database (powers XBRL sheet) to check on item usage. I thought it would be interesting to look at usage for our reference set of industrial companies we established when looking at filing histories (seeing our standiZd 1600 was designed explicitly for non financial companies). Keeping everything consistent with all our other data sampling in this series, I only included 10-K's but all the 10-K's they've ever filed in XBRL. I should stress that this is before we've applied our standardisation routines.


So what is this table telling us? That 263 of our standiZd 1600 have NEVER been used by any of the top US industrial companies. And a whopping 65% of what we regard as the most comparable individual data items have been used by 5% or less. So if we were trying to compare any one of these 1000 odd items, we would have at best only 11 other companies (and roughly on average probably only six) to compare them with. Lets hope a couple of them happen to be in the same sector! And we can probably forget about creating any meaningful aggregates for these items.

What happens if we widen it right out to the total population of US-GAAP tags and the total population of companies. Not surprisingly it gets uglier. 10,000 of US-GAAP tags appear in 5% or less of the total number of 10-K filings made to date (approx. 50,000). And roughly only 1,000 items appear in more than 5% of these filings.

This isn't a perfect piece of analysis as some items have come and some items have gone (but that has been happening by and large around the margins of the taxonomy)

So it's probably not too unreasonable to conclude 20,000 data items is drastic overkill for comparative analysis and that our 1600 standiZd data items IS over the top! We will certainly take a close look at those 263 items to see if we need to rename! this set downwards.

So what use does this disparate tagging have? Well it does allow us to easily find the rare occasions where particular tags have been used but because use is so rare, it probably has no real advantage over them just being tagged using extensions. A simple text search, although not as neat would be just as effective, if not more so as it would pick up all (the right or wrong) occasions when an extension has been used instead.

All this prompts the question - will there ever be less data items?