XBRL API Quarterly Data Call

As explained in our Getting Started guide, We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API. All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.

An API call for quarterly data works in much the same way as for annual values. However to bring back Quarterlies, three more boxes come into play. except this time they are black.

 Black Boxes  - these either represent fixed elements of the API path or ones that have a small set of finite values so we have rigged them up as drop downs in Excel.

 Black Box  - Annual? Default value is "yr" but you can select "q" to see quarterly values instead.

 Black Box  - Quarter. Selecting "q" brings this box into play where you can select which quarter 1,2, or 3. You can also specify "123" to get all quarterlies for a year. NOTE: "12" etc will not bring back 1st and 2nd Quarters. Only "123" works for multiple quarters in a year. If this is set to "4", and the next box is set to "ytd", it will bring back the annual figures.

 Black Box  - YTD. Setting this to "ytd" as opposed to blank will bring back cumulative figures for the year - the Year To Date.

Orange Boxes work just like they do for annual amounts. So if you set the date to 2019, it will bring back the quarter you selected (e.g. 2) for the reporting year ending in that year. And if you set Years to 5, you will get a times series of quarter 2's going back 5 years.

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

How to screen for the very latest filings in Excel

Now EDGAR is very good at showing you all the latest filings. But I wanted to take it a step further in my analysis of XBRL filings for the 2017 10-K reporting season. I also wanted to do it in Excel. By the way, this is all part of the Has the dream come true? series of posts and videos which you can follow starting from here.

If you have read my previous posts, you will know I have set up a control group of companies so we can see for real in this reporting season what we can and can't do with XBRL. Now I've needed to be able to monitor those filers, just as if they were my portfolio of potential investments. So we've added a watchlist feature to Xbrl sheet. Technologically, it's just a variation on the existing queries you can run in Xbrl sheet. It uses the same query file but you set different parameters to do some very powerful screening of the latest flings at the SEC.

Great thing about Excel data queries is that you can set them up to run whenever you want automatically, so when you open your workbook or indeed at regular intervals, say every hour.

You won't always know which companies your wish to watch so we've covered that by adding the ability to screen by filing date. Say for example, show my every company that filed yesterday (whisper it but you can actually search down to the latest minute so you could set it to show you all those that filed in the last hour for example. We update our database from the SEC in real time so as soon as it's available on EDGAR, it's available in Xbrl sheet). Not only that, you can filter by industry to build a peer group of real time filings. You can specify a particular SIC code or a wider range to pick a bigger industry grouping or search by filer (i.e. find filers in the same industry as your chosen target).

Anyway this video shows you how to do all that. And once you've identified a filing, you bring down all it's XBRL tagged data using the same mechanism in Xbrl sheet.

And there's an example sheet to play with here.

XBRL Comparative Analysis in Excel using the Arelle Fact Table as a source

It's a bit messy and requires you to tidy the data but because the Arelle GUI exposes the XBRL tags with their respective values (see "Xbrl Tags into Excel using Arelle" post), we can harness the power of Excel to finally do what XBRL was supposed to allow us to do in the first place - compare companies.

Five simple steps required for each company you wish to compare.

Step 1 - Load instance document. Arelle allows you to load this directly from the web if you don't feel the need to download and store it locally.

Choose "Open Web..." from the File menu and copy the URL of the instance document into the input box (shown above). There are two ways to find this - the slow way & the quick way. The slow way requires you to click on the "SEC Search" button which takes you to the EDGAR search page, find the company you are looking for, search for the filing etc etc etc.......You may find it quicker to use the "single click" search option available from my website (see post "Easy access to SEC XBRL spreadsheets via a web app").

From my website, enter the company identifier or name, make your single click to search, then right click on the "X" (shown above) for the filing you require and choose to copy the link. Return to Arelle & paste into the input box. Crikey all this just to open an instance document! and that was the easy bit!

Step 2 - Copy the "Fact Table" to Excel. First remove all (well some) of the unwanted (empty) columns. Choose the remove random empty columns option otherwise known as right clicking on the table and choosing "options > ignore dimensions". For more on this, see previous post "Xbrl Tags into Excel using Arelle".

Step 3 - This step isn't entirely necessary but it kinda feels right (and should speed up lookups - see below). Delete all the note sections containing html formatted and therefore difficult to read text by deleting the rows containing this data.

Step 4 - Delete the unwanted columns that the "random" button failed to delete (i.e. most of them). You may need to expand the width of the columns to see which one is which. Don't use the "autofit column width" option to do this quickly as you will end up with some flipping big columns which are then difficult to re-size back to something sensible! Highlight all the columns and set the width manually to something manageable instead. I deleted 19 columns for the Microsoft 10K in the example spreadsheet (see below) to leave me with the 3 key years of data.

Now you in effect have a lookup table with 2 years of balance sheet (or "instance" data in XBRL parlance) and 3 years of "duration" data such as the income statement.

Step 5 - Except lookups won't work because an exact match is needed for unsorted data (would need to be in alpha-numeric order for it to be sorted) and the tags pasted from Arelle have leading spaces consistent with the presentation hierarchy, so another step is needed. Insert a new column in front of the tags and fill it with the following formula "=trim(B1)" using a relative cell reference to replicate the "tags" column but with the leading spaces removed (as shown above).

Now do it all over again for the next company you wish to compare! putting it in a new sheet.

Because the tags are the same* regardless of which company you are looking at, you can now use Excel's lookup functions to create standard reports for comparative analysis. Order is unimportant for exact lookup functions, which is what enables us to do this regardless of how the company has chosen to present the data.

The beauty of this is that you can load the data "as presented" whilst viewing the data in a standardised format customised to your choice. Standardised and as presented data connected and all in the same file. Now that is powerful and only possible because of XBRL. More on the mechanics of how to create a standard report for multiple companies in my next post. For now have a look at the example spreadsheet (snippets above) which you can download from my website - www.xbrlxl.com (follow the "Spreadsheet Examples" link on the left hand side).

*Unless a company has made excessive and unnecessary use of the dreaded taxonomy extensions.

Accessing SEC XBRL spreadsheets directly

As I mentioned in a previous post - Seeing XBRL for free, you can circumnavigate the SEC viewer and go directly to a pre-canned spreadsheet of the entire filing!

Here's the specifics of how to do it:

Use a URL with the following format:

     http://www.sec.gov/Archives/edgar/data/[CIK]/[Accession No]/Financial_Report.xls

where [CIK] and [Accession No] are the unique filer and filing identifiers you need to insert which can be found by doing an Edgar search or in an RSS feed of Edgar filings. The Accession Number must be a fixed length of 18 digits so must include any leading zero's. The opposite is true for the CIK - variable length and no leading zero's.

So if you wanted the 2011 Apple 10-K in Excel you would create the following link. Click on it and it will download.

http://www.sec.gov/Archives/edgar/data/320193/000119312511282113/Financial_Report.xls