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:   (Get a free, more useful token at our website)

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

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