[Peter Guldberg]

If I understand Tim correctly then both the Revenues and SalesRevenueNet facts have ultimus-index of 1.
The problem can be that some companies use one concept name and other companies use another for the same concept. This is partly what we tried to solve in the balance sheet template by mapping other concepts to the same “main” concept.
The way we handled it in there was by using the different concept names as mutually exclusive alternatives. So, we try if the first concept name works and returns a valid number, if it doesn’t we try the next alternative etc.
In the sheet that you and I were working on Tim, I think the lookup function would just pick the first occurence of the two concepts.

[David Tauriello]

Getting ‘No Information Returned’ – ? Try this:

1. Make (or open) a copy of this file in your Google account: https://xbrl.us/xbrl-api-spreadsheet
2. Open the XBRL API add-on and click the authentication link so that your account is logged out
3. Close and re-open Chrome browser (Chrome will work best here)
4. Open the browser’s settings and clear the cache (maybe just the last 24 hours)
5. Open your copy of the file in the link above
6. Open the XBRL API add-on and click the link to authenticate – confirm you are logged out, first
7. Select either authentication option (XBRL US web account or Continue with Google)
8. Change the Main Sheet Query dropdown (cell B1) to any other query – data should now be flowing through the API via your account again.

Tried everything in this thread and it’s still not working? Post back and let us know you’re still not able to use the API with your account.

[Tim Bui]

Hello, as companies have the freedom to create unique names of their tags (concepts), does XBRLUS keep a list of all similar concepts on the edgar_db database so that the users can save time in doing standardization?

For example, to get a simple Total Revenue, so far I found 5 different concepts:

1. Revenues
2. SalesRevenueNet
3. SalesRevenueGoodsNet
4. TotalRevenuesAndOtherIncome
5. RevenueFromContractWithCustomerIncludingAssessedTax

To find Nonrecurring charges (such as impairment and restructuring), so far I found 13 concepts:

1. Assetacquisitioncharge
2. AssetImpairmentCharges
3. ImpairmentOfLongLivedAssetsToBeDisposedOf
4. RestructuringCostsAndAssetImpairmentCharges
5. ImpairmentOfIntangibleAssetsIndefinitelivedExcludingGoodwill
6. Impairmentandrestructuringexpenses
7. RestructuringSettlementAndImpairmentProvisions
8. GoodwillImpairmentLoss
9. RestructuringCharges
10. RestructuringCosts
11. RestructuringChargesAndAcquisitionRelatedCosts
12. GoodwillAndIntangibleAssetImpairment
13. ImpairmentOfLongLivedAssetsHeldForUse

The above 13 non-recurring items are exclusive of the additional 7 gain/loss concepts below:

1. GainsLossesOnExtinguishmentOfDebt
2. DerivativeGainLossOnDerivativeNet
3. GainLossOnSaleOfBusiness
4. GainLossOnSaleOfNonstrategicBusinessesAndAssets
5. GainLossRelatedToLitigationSettlement
6. GainLossOnDispositionOfAssets1
7. GainLossOnDispositionOfIntangibleAssets

Thank you!

[David Tauriello]

Tim – we don’t maintain lists like the ones posted here; you’ve done a great job to create a few groups for standardizing elements and tagged it so others can find it easily and post additional groups or additions to those listed above.

[Tim Bui]

For trend analysis, we need to look at historical numbers. However, some companies changed the tag names so it is very difficult to pull a consistent list of numbers using the API. If anyone has a solution, please share with me.

For example, Microsoft (CIK: 0000789019) used 4 tags for its total revenue over the last few years:
– For FY 6/18 (which has data for 6/18, 6/17,and 6/16) it used “revenuefromcontractwithcustomerexcludingassessedtax”
– For FY 6/17, it used “SalesRevenueGoodsNet”
– For FY 6/15, it used “SalesRevenueNet”
– For FY 6/10, it used “Revenues”
Using only one of the above tags will get only 3 years of data. If we use all 4 tags, then we have duplications such as the list below. Even with 4 tags, I am not able to get Total Rev for FY2018.

entity.cik period.fiscal-year period.end concept.local-name fact.value
0000789019 2009 2009-07-01 Revenues 58437000000
0000789019 2010 2010-07-01 Revenues 62484000000
0000789019 2009 2009-07-01 SalesRevenueNet 58437000000
0000789019 2010 2010-07-01 SalesRevenueNet 62484000000
0000789019 2011 2011-07-01 SalesRevenueNet 69943000000
0000789019 2012 2012-07-01 SalesRevenueNet 73723000000
0000789019 2013 2013-07-01 SalesRevenueNet 77849000000
0000789019 2014 2014-07-01 SalesRevenueNet 86833000000
0000789019 2014 2014-07-01 SalesRevenueGoodsNet 72948000000
0000789019 2017 2017-07-01 SalesRevenueGoodsNet 57190000000
0000789019 2017 2017-07-01 SalesRevenueNet 89950000000
0000789019 2016 2016-07-01 SalesRevenueGoodsNet 61502000000
0000789019 2016 2016-07-01 SalesRevenueNet 85320000000
0000789019 2015 2015-07-01 SalesRevenueGoodsNet 75956000000
0000789019 2015 2015-07-01 SalesRevenueNet 93580000000

[Tim Bui]

The trend in Gross Margin (GrossProfit / Total Revenue) says a lot about the business condition of a company. However not every company reports Gross Profit. For those who do not, we have to subtract Cost of Goods Sold from Total Revenue. I do not have a solution to get consistent numbers for Total Revenue yet, however, for Cost of Goods Sold, I have found a few different tags below:
– CostOfGoodsAndServicesSold
– CostOfRevenue
– CostOfGoodsSoldExcludingDepreciationDepletionAndAmortization
– CostsAndExpenses

[Tim Bui]

Some companies report depreciation & amortization expenses (DDA) on both the Income Statement and the Cash Flow Statement, while some others only report DDA on the CF statement and not on the income statement. If I use the concept “DepreciationAndAmortization”, I do not know if the data came from the Inc Statement or the CF statement.

Is there a way to have the API to return the concept name that has the source of the statement (Inc Statement or CF Statement)?

This distinction is important because it affects the perception of profitability.
A simple example: two companies A and B below have identical financial results. But company A elects to include DDA expenses within the COGS and reports DDA Exp only on the CF statement. Company B elects to exclude DDA expenses from COGS and has a line DDA Exp on the income statement.

Both report DDA Exp on the CF statement. When using the API to call the concept “DDA”, the same number $20 comes back for both.

Without knowing that the DDA Exp from the income statement of Company A is NULL,
One would have a wrong perception that company A’s gross profit is lower than that of company B ($50 vs $70)

CompanyA CompanyB
Sales $100 $100
COGS $50 $30
Gross Profit $50 $70
DDA Exp $20
SGA Exp $20 $20
Oper Inc $30 $30

[Tim Bui]

I just noticed that the sign for concepts “IncreaseDecreaseInAccountsReceivable” and
“IncreaseDecreaseInInventories” are actually reversed. In case someone didn’t notice, I am alerting this fact so that these numbers can be used properly.

The issue is not a problem with XBRL.US’s API, but it seems to be that is the way companies are reporting these two items.

If AccRec increased and Inventories increased, the API calls return a positive AccRec number and a positive Inventories number. However, the cash flow statements would report a negative change in AccRec (use of cash) and a negative change in Inventories (use of cash). I think this is the reason for the reversed sign. The strange thing is all other items such as Change in AccPayable, Change in Other Working Capital or Change in Accrued Liabilities
have the same directional signs on both the API calls and the 10Ks.

For example:
Merck (MCK, CIK 310158), the API returned a negative $297MM and positive $145MM.
us-gaap IncreaseDecreaseInAccountsReceivable (297,000,000)
us-gaap IncreaseDecreaseInInventories 145,000,000
However, on the 10K, the change in AccRec is a positive $297MM and change in Inventories is a negative ($145)

I saw the same situation reported at Air Products (APD, CIK 2969 ) and Apple (AAPL, CIK 320193)

[Angelo Brizi]

I ran the following query in Google Sheets

=showData(“https://api.xbrl.us/api/v1/fact/search?&concept.local-name=NetCashProvidedByUsedInOperatingActivities&period.calendar-period=1Q,1H,3QCUM,Y&period.year=2015,2016,2017,2018,2019&entity.cik=0000915840&fact.ultimus=1&dimensions.count=0&fields=fact.numerical-value,period.year,period.calendar-period,concept.local-name,period.end.sort(ASC)”)

Why only the data for 3QCUM and Y are returned? It looks like the data for 1Q and 1H (except for 2016) are missing.
Thanks

[Angelo Brizi]

the above query is not shown properly in the message above but is the following

=showData(“https://api.xbrl.us/api/v1/fact/search?&concept.local-name=NetCashProvidedByUsedInOperatingActivities&period.calendar-period=1Q,1H,3QCUM,Y&period.year=2015,2016,2017,2018,2019&entity.cik=
0000915840&fact.ultimus=1&dimensions.count=0&fields=fact.numerical-value,period.year,period.calendar-period,concept.local
-name,period.end.sort(ASC)”)

[David Tauriello]

Search with the dts.id for the filing on the relationship endpoint and treat the concept.local-name for the fact (DepreciationDepletionAndAmortization in the example below) as relationship.target-name.

For the query, note that network detail – including the network.role-description (report location for the fact) is returned by default. I’m also returning all attributes of the relationship.* in the event other details about the relationship are helpful:

/relationship/search?dts.id=306447&network.link-name=presentationLink&relationship.target-name=DepreciationDepletionAndAmortization&fields=relationship.*

See page 27 of the documentation for details on the Relationship Object https://xbrl.us/xbrl-api-documentation

[David Tauriello]

Hi Angelo – in the query below, I’m returning period.fiscal-period as part of the fields to show how the fact corresponds to reporting periods defined by the filer for their annual cycle, which does not always follow the 1/1 – 12/31 calendar.

/fact/search?&concept.local-name=NetCashProvidedByUsedInOperatingActivities&period.fiscal-year=2015,2016,2017,2018,2019&entity.cik=0000915840&fact.ultimus=1&fact.has-dimensions=false&fields=period.end.sort(DESC),concept.local-name,fact.value,period.fiscal-period,period.calendar-period,period.fiscal-year,period.year

If you replace the concept.local-name in the query with a US GAAP element in every filing called DocumentFiscalPeriodFocus the model for this filer’s fiscal year should become clear (10/1 – 9/30).

See this discussion for more detail on periods, and check out the Balance Sheet Compare – SEC Filers Google Sheet for queries that translate the DocumentFiscalPeriodFocus for the presentation.

[Angelo Brizi]

Thanks a lot for the detailed answer David. That was really helpful.
Best regards,

Angelo

[Brian Fitzgerald]

Is it possible to return all the concepts for a given financial statement (I think we use network to identify a financial statement)? So, for a given company, I would identify the filing by dts.id and then like to return all of the concepts used in a specified financial statement.

[Tim Bui]

Brian,

The website XBRLXL.com created by Jim Truscott might provide what you are looking for: a complete list of all of the concepts for all of the financial statements.

Jim created a wonderful Excel spreadsheet named X Sheet to get all financial statements verbatim easily. All you have to do is put in either the ticker or the CIK on the “Search” tab and hit “Data Refresh All” to get different sheets within Excel populated with either annual or quarterly number.

The basic X Sheet is free. Jim also created other products with standardized concepts for a very, very modest fee. You can check out his website at https://xbrlxl.com/. He also has posted several instructional videos on youtube to explain how to use his products (https://www.youtube.com/user/FundXJim)

He was incredibly helpful in answering my questions. I use his Excel spreadsheets often.

Tim

[Author]

Posts