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...

Firefox XBRL Add-on

Continuing my trawl through the free viewers.....

As I mentioned in my previous post, the Firefox XBRL Add-on, although apparently not supported, has a couple of tricks up it's sleeve.

With the Firefox XBRL Add-on you can view any of the taxonomies used in the creation of the XBRL and you can drill down into the notes for a financial statement item by clicking on it to reveal a pop up box that will show any links, providing these have been enforced in the original document. Charles Hoffman calls this "metadata leveraging".

He also discovered that you can do some crazy stuff by dragging & dropping the headings around like you can in an Excel pivot table to totally customise the view. You can do this with any heading with a triangle in the top left hand corner. You can swap them round or drop them onto "[DRAG PAGING COLUMNS HERE]". Not sure how useful it is at this level but it's mildly entertaining. Perhaps more usefully, you can choose to view only specific data items or periods by clicking on the "Item" and "Date" headings (with the triangles in the corner) to reveal lists with check boxes. You can't however save these settings to use with other documents.

And It will turn XBRL into iXBRL and vice versa, although the iXBRL doesn’t always render perfectly but if you wanna turn XBRL into a single document to read offline, it’s fairly adequate.

The Add-on is not based on the Rivet open source code. Don't be put off by the fact that Firefox says it's not compatible with version 5. It will install and work once you relax Mozilla's strict compatibility enforcement in the options. Click on a copy of the instance document on an Edgar filing page and (after a short interlude) the add on will kick in to enable you to see the document. You can also view documents on your hard drive, although it will need a connection to download the taxonomies before it will load.

Of course the fact it is in Firefox maybe a problem. I moved over from Firefox to Chrome last year so it's not where I'd ideally like it.

Seeing XBRL for free

Continuation of previous post - Where do I want to see XBRL?

So what can the free viewers do for us? Starting with the SEC viewer, this fires up as soon as you choose the interactive data option from a list of Edgar filings. You can choose to view any of the financial statements or notes from a side menu or more significantly you can click on a link to dump all the data into Excel, except for the fact that it doesn't, as the data is already there! - more on this later as this creates an interesting possibility. You can only view one company at a time but of course you can always Edgar search for another company in a new tab in your browser. Clicking on the name of a data item brings up a pop up box from which you can expand an option to view a definition or important details such as the name of the tag.

Rivet CrossView Preview as a viewer has a number of advantages over the SEC one. You don’t have to wade through a large list of filings to find the one you want as the index only deals in XBRL. Each filing you choose comes up as a separate tab so it's easy to view more than one company at a time. The last option on the tool bar will mark which data items are from the filer's extended taxonomy i.e. not a standard item and hence which are not very useful when comparing one company with another. This option will even be greyed out if none of the data items have been extended - always a very good sign!

The catchily titled RR Donnelly XBRL Interactive Viewer does all the things the CrossView viewer does but has a worse look and feel. Also rather annoyingly, when you select a company, it shows the bottom of the list of filings so you invariably have to page up to get to the latest ones.

Most significantly, all three viewers let you download the entire document into Excel (minus the really rather useful tags) in an identical format with each section in a separate sheet in the workbook. These spreadsheets are pre-canned so in the case of the SEC viewer this brings us back to the interesting possibility that you can circumnavigate the software and go directly to the spreadsheet! e.g. this link will take you to a spreadsheet of Apple's 2011 10-K. More on this in a following post.

You can also link directly to a specific section in a report using both the SEC viewer or CrossView Preview. Charles Hoffman has more on this.

It is also worth mentioning the Firefox XBRL Add-on which, although apparently not supported, has a couple of tricks up it's sleeve which I will come to in my next post.

Where do I want to see XBRL?

To be frank, if I was an analyst, I would have no interest in viewing XBRL or more specifically the data in which it has been entwined. I don't want to view it, I want to use it, I want to add value to it. I want it in Excel. I still believe that's the best place for it, even if it comes via an intermediary database first. Analysts spent years persuading resistant data vendors to dig the data out of their databases and let it flow into spreadsheets unencumbered by restrictive proprietary software. I don't believe this paradigm is going to change any time soon. Of course there are others who take a different view.

So I'm not going to spend much time talking about the merits of one viewer over another but rather whether they can assist me in getting the data to where I want it. I will note that the only free viewers that are supported (i.e. where there are signs of ongoing development) are all web based and all use the open source code originally made available by Rivet Software at the behest of the SEC.

So don't bother with Rivet Software's Dragon View or XBRL View, both of which are desktop based but are unsupported.

I'm gonna stop here because I prefer to keep my posts short and punchy. There will be a stream of posts coming out over the next few days (well maybe not a stream - more like 2) looking at the viewers.