Friday, March 23, 2018

Since 2010 publicly traded companies in the United States have been required to file a machine readable version of their 10-K/Q report. The machine readable format is XBRL (eXtensible Business Reporting Language). Getting data from 10-Ks has historically involved opening and searching throught the document and cutting and pasting the relevant number into Excel. XBRL makes that unnecessary.

Accessing XBRL 10-K/10-Qs through Calcbench will improve the quality and speed of your fundamental equity analysis. An understanding of how XBRL works helps to get the most out of XBRL and Calcbench. What follows are two illustrative examples of where XBRL comes from, what it looks like, and how Calcbench renders it.

The examples that follow link to sections of Calcbench that require a subscription. If you want to follow along you can sign up for two free weeks of Calcbench @ The formulas for this article are collected in an Excel Sheet.

How XBRL works - Simple Example

Our first example is the $61,502 (in millions) Product Revenue number Microsoft reported on line 52 of their 2016 10-K accessed from Edgar. If we open up the XBRL INSTANCE DOCUMENT, we can find the same number as an XBRL fact by using our browser to search for “61502”. Below is the part of the XBRL instance document that containing the fact -

<us-gaap:SalesRevenueGoodsNet contextRef="eol_PE8528----1610-K0009_STD_366_20160630_0" unitRef="iso4217_USD" decimals="-6" id="id_8234187_009AE0D9-A411-48DA-BD5D-0762E35D4AA1_1_1"> 61502000000 </us-gaap:SalesRevenueGoodsNet>

There are several components to the XBRL tagging. Let’s start with the tag itself. The fact is tagged with us-gaap:SalesRevenueGoodsNet. This indicates that the fact comes from the United States GAAP (Generally Accepted Accounting Principles) taxonomy as published by the FASB (Financial Accounting Standards Board) here. This means that the company chose a standard tag from the list published by the FASB. The definition of SalesRevenueGoodsNet is documented in the Taxonomy Viewer.

The element’s contextRef="eol_PE8528----1610-K0009_STD_366_20160630_0" is a pointer to the context of the element which we can find in the document by searching for “eol_PE8528—-1610-K0009_STD_366_20160630_0”.

<context id="eol_PE8528----1610-K0009_STD_366_20160630_0"> <entity> <identifier scheme="">0000789019</identifier> </entity> <period> <startDate>2015-07-01</startDate> <endDate>2016-06-30</endDate> </period> </context>

The context indicates that number is related to CIK (Central Index Key) 0000789019, Microsoft. It also establishes that the number is for the period beginning July 1 2015 and ending June 30 2016, Microsoft’s fiscal year is July to June.

How do you get this data on Calcbench?

Calcbench ingests the XBRL document, stores the data in our database and renders it on our website and through our Excel Add-in. You can see the number on our dashboard page, if you have the Calcbench Excel Add-in installed you can reference the number with the formula =CalcbenchXBRLTagFiscalPeriod("SalesRevenueGoodsNet", "MSFT", 2016, "Y").

The Advantage of Calcbench’s Standardized Data

Another, more complicated, example demonstrates the advantage of using Calcbench’s standardized data. Citigroup (ticker C) reports a line item Gross impairment losses of 63 million dollars in their income statement on page 134 of their 10-K for 2017. The number appears in the corresponding XBRL document as -

<c:ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment contextRef="FD2017Q4YTD" decimals="-6" id="Fact-1336DEBE0F4527C0EFD34C07C3C61F76"unitRef="usd">63000000</c:ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment>

Notice that instead of “us-gaap:” the tag name is preceded by “c:”. This indicates that the tag ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment, is an extension specific to Citigroup. Filers use extension tags when they feel that none of the tags in the taxonomy accurately describe the account they are tagging. In this case we can find the definition for ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment in the label file.

<link:label id="lab_c_ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment_7ED07A8E118A2DA3367780CA68B5845E_documentation_en-US"xlink:label="lab_c_ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment_7ED07A8E118A2DA3367780CA68B5845E"xlink:role="" xlink:type="resource" xml:lang="en-US"> </p> The aggregate amount (i) by which the fair value of an investment is less than the amortized cost basis or carrying amount of that investment at the balance sheet date and the decline in fair value is deemed to be other than temporary, before considering whether or not such amount is recognized in earnings or other comprehensive income; and (ii) of an other than temporary decline in value that has been recognized against an investment accounted for under the equity method of accounting. </link:label>

Retrieving this value by XBRL tag in the Calcbench Excel Add-in is easy, =CalcbenchXBRLTagFiscalPeriod("ImpairmentOfInvestmentsAndEquityMethodInvestmentOtherThanTemporaryImpairment", "C", 2017, "Y").

Because the ImpairmentOfInvestments… tag is unique to Citigroup we cannot use it to retrieve the value other companies are reporting for impairments. For instance Santander’s US entity (SOV) reports line items for Impairment of goodwill and Impairment of long lived assets on their 2017 10-K. From an economic perspective the sum of Santander’s goodwill and long lived assets impairment is equivalent to Citigroup’s Gross Impairment losses. The idea of economic equivalence is captured in Calcbench’s standardized dataset. The Calcbench standardized metric “AssetImpairment” handles Citigroup’s using an extension tag and Santander reporting two different line items, using the Calcbench Excel Add-in

How do you get this data on Calcbench?

In the Calcbench Excel Add-in =CalcbenchData("AssetImpairment", "C", 2017, 0), and =CalcbenchData("AssetImpairment", "SOV", 2017,0).

We can see this data on Calcbench’s multi-company page.

XBRL and Calcbench are a powerful combination, but there is a learning curve. Now that you have some idea of the flow of data from the SEC to Calcbench you are ready to start including XBRL sourced data in your models. If you are new to Calcbench, you can sign-up for a free two week trial. To request a demo of Calcbench send an email to

FREE Calcbench Premium
Two Week Trial

Research financial & accounting data like never before. Get features designed for better insights. Try our enhanced Excel Add-in. Sign up now to try the Premium Suite.