Wednesday, 15 February 2012

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.


7 comments:

  1. This looks very promising, but I have not yet been able to create spreadsheets. For ticker BPL the company name, date and form fill in, but when I click on XBRL to XL nothing happens. For tickers EXC and UAN I get a message that there is no matching file, but I know these filings were within the range XBRL filings were required. Can you help me?

    ReplyDelete
  2. @Dave

    Couldn't see any problems with BPL - logs show spreadsheet was generated successfully. Re-tested it and it all looked good to me so not sure what happened there. Give it another go.

    The "EXC" ticker is allocated to 2 companies on Edgar, Exelon Corp and a subsidiary. We've been allocating the filings to the subsidiary. This has now been fixed.

    UAN hasn't filed any 10-k's in XBRL, only 10-Q's. We haven't sufficiently tested 10-Q's yet so only 10-K's available at the moment.

    Thanks for the feedback. Let us know if you experience any further problems.

    Jim

    ReplyDelete
  3. Jim-
    Have you seen Rivet Software's Excel add-in solution for XBRL Analytics. Based on your blog entries, I think you would find it very powerful.
    -Nancy Stockton
    nancy.stockton@rivetsoftware.com

    ReplyDelete
  4. Thanks Nancy. I have asked about a trial but I was told this was not an option. I was offered a demo and I will take up that opportunity when I get a chance as your Crossfire solution looks very interesting. It would also be useful to know how much this software costs.

    Jim

    ReplyDelete
  5. Good article Guys, Thanks. By the way I found this awesome site for Financial Data and Analysis : http://thinknum.blogspot.com/ You can Search over 7,000,000 datasets. It has a financial data analysisengine which brings the functionality traditionally found on wall street proprietary trading desks to an open platform. You can visualize, save and share data. This tool is similar to PlotTool at Goldman Sachs or DataQuery at JP Morgan. Check it out, You may like it!

    ReplyDelete
  6. Very good post, Jim. I'll use your information for any future data analysis needs I have on a personal-level. However, I also wanted to mention that my company recently began using enterprise data modeling software by Modern Analytics which has truly revolutionized all of our data analysis needs. I would definitely recommend taking a look if you have any interest.

    ReplyDelete