Multi-dimensional analysis in Excel focusing on business sectors (Add on to existing XBRl to XL functionality)
Jim Truscott firstname.lastname@example.org
Sector3 enables you to model a sector to allow meaningful comparisons between company business segments. You define the sectors and Sector3 will supply you with appropriate data in Excel.
Sector3 was created by Fundamental X and is part of “XBRL to XL” which can be found at www.xbrlxl.com.
3 Steps to using Sector3
Step 1: Classifying sectors.
Full details on using XBRL to XL are covered on the FAQ or watch the video here. With the release of Sector3, you can now choose companies by sector (based on the SIC codes held by the SEC).
Once you have chosen your filings, press the “Sector3” button. At this point, the Fundamental X processing engine will start whirring away so there will be a short delay whilst the XBRL filings are taken apart and re-assembled as a collection of business segments. They will appear as a list below the search fields.
Before you go any further, you may want take a look at the step by step video for Sector3 here.
Ultimately a sector will be classified in 3 ways: Automatically using the same classification technologies that we use to help our clients out of the extension quagmire (a future feature); Secondly based on how everyone else has classified it (crowd sourced – again coming soon) and thirdly, however brilliant these technologies prove to be, you will always want the final say so you can enter your own code in the “code” box next to the sector. Now you may not know all 2,214 NAIC codes off by heart so we have provided a “chooser” to help you navigate to your chosen sector. Click twice on the NAIC name to select it in “Code” box.
SIC codes are also available (this chooser also becomes visible when searching companies in the first instance). And country codes for when we provide geographic segments.
But you don’t have to use standard codes – you may have your own system. Whatever you enter, be it a number or a word, that will become a segment’s classification. And you don’t have to classify every segment, just the one’s you are interested in. Keep in mind the end goal – to model a sector to allow meaningful comparisons between company business segments.
Note you can choose the entire company entity as one segment – particularly pertinent where they only operate in one sector.
Once you have made your classifications, you can click on the “Rank Sectors” button above. This will provide a summary of the choices you have made.
Step 2: Download the sector data.
You can then proceed to Excel by downloading the XBRL sector data we have created. Click on “Sector3 to XL” to create the spreadsheet followed by the appropriate download button. The file created is a standard spreadsheet so can be used in any other spreadsheet program such as Google Docs or Open Office.
Step 3: Analysing sector data in Excel.
An example of what the data looks like in Excel can be downloaded from here.
In a sense our work is done once we’ve got the data to Excel as Excel’s supreme analytical capabilities can take over. Sector3 has extracted the relevant data and shaped it so Excel can make the maximum use of it. Note it is organised by the section in which the data items were disclosed so you can check the values with the reported presentation. If you look in the “XBRL” tab you will see this data – all looking pretty flat at this stage – not an extra dimension in sight.
This all changes when we get to the “Sectors” tab. Time for a pivot table or two. If you move across to the “Sectors” tab, you will see a couple of limp looking pivot tables.
Click on a cell in a pivot table (if one isn’t the active cell already). Then “refresh” the pivot table. In Excel 2007 this can be done from the “Options” ribbon which can be selected whenever a pivot table is in focus. All the pivot tables relating to the same data set (our Sector3 data) will be refreshed at the same time. The tables will spring to life in all their multi-dimensional glory. They have initially been set to show aggregated values for the sector classifications you have chosen. Change the filters to show values for different years or data items.
Each XBRL data item has been given a standardised equivalent (still in XBRL – taken from the US-GAAP Taxonomy). This is the Fundamental X processing engine at work again here whereby every item is standardised to the chart. This ensures that every segment of data is included in the analysis regardless of any esoteric tagging by the filer.
Beneath the pivot tables are some ratios that reference these tables and show the performance of the top 3 sectors you have classified (just two shown for the example). Of course in Excel you can quickly add to these ratios.
If you don’t like pivot tables, you can use lookups and other in-built functions like SUMIF to query the Sector3 data – we have shaped the data for all kinds of analysis.
If you look in the “Companies” tab, you will see something similar. Here we take a company centric view of the data, enabling you to compare the companies sectors’ in your selection against the sector as a whole.
Finally, no sector analysis would be complete without a pie chart or two. Creating charts from this data in Excel is, as you would expect, a cinch and the two you see above were keyed off the sector breakdowns of sales in the last table in the “Companies” tab of the example spread.
A video is available here showing Sector3 data in Excel.
Thinking behind Sector3
Historically, corporate data has been made available by vendors in fairly flat files. XBRL was designed with multiple dimensions. We could see that opening up these extra dimensions could be extremely useful in certain areas of analysis.
One of the hottest questions we got when I was designing data products at the FT was “Could we provide more structured data about the business sectors in which a company operated?”
And the reason for this was obvious. Pretty much all analysis is relative to this company or relative to that market. And with large listed companies (the ones most of our money is invested in), that invariably operate in more than one business segment, this cannot be done at the company level.
XBRL with its extra dimensions offers the potential to take the business analysis down a level into a third segmental dimension. This is what we’ve harnessed with Sector3 enabling more comparisons and better comparisons with other companies and other sectors. And potentially with other non-gaap sector data such as market research statistics.
Sector3 utilises the Fundamental X processing engine to do the following things:
- Unlock the data from the XBRL
- Segment classification
- Period alignment
- Data item standardisation
- Extension tag alleviation (partially implemented)
- Re-purpose the data for accessible multi-dimensional analysis
- Maintain the connections to the “as presented” data – data remains tethered to its source
XBRLXL.com is where you choose the data you wish to analyse, Excel is where we put it and Fundamental X does the boring but clever bit in the middle.
Of course we don’t have to put the data in Excel. With the Fundamental X processing engine acting as an API, connected to a web service, the data can be pushed or pulled wherever you want it to go. But with XBRL to XL, it goes into Excel.
As with XBRL to XL, Sector3 is not about re-creating fancy front end tools but creating data and structures which can be used with the best data manipulation tools that already exist.
Jim & Phil work out of the America Ground (the USA’s lesser known 24th state!) in Hastings, England.
Jim founded Fundamental X in the year 2000, shortly after XBRL was first conceived. The aim of Fundamental X was (and is) to automate the collection of structured data from unstructured sources to aid analysis. XBRL has made his life a lot easier. Jim successfully entered the 2012 Challenge with “XBRL to XL” becoming one of the finalists. Jim’s background in collecting, preparing and developing financial data, including a long stint at Financial Times information, has made him passionate about getting highly accurate, detailed and comparable data onto the analyst’s desktop. The way Jim sees it, XBRL finally offers that possibility.
Phil joined the team in March of last year and has been working initially on the database side of things as well as becoming quite an expert in open XML! Phil was originally a physicist, but has worked in software development for many years. The last 15 years in particular have been spent writing software to handle structured accounting data in a non-XBRL environment.