[Tim Bui]

Hi David,

Thank you for explaining the concept.id for me. However, would you please elaborate a little more on these points so that I can gain a better understanding?

1. On your writing, “the concept.id values will differ, because our database assigns concept.id values based on the facts as they appear in the company’s instance”, does this mean that XBRL US assigns the concept.id, the SEC assigns or the company self-assigns?

2. Do you know if there is a relationship from one year to the next (say from 2017 to 2018 and to 2019)? or Is the number for the current year completely different from the previous year?

I am trying to find some commonality to do comparison among companies, but it is difficult. For example, just for Total Revenue (the very top line), I went throught some 100 income statements, yes from roughtly 100 10Ks, and found there are 27 concept.id for 2017 and 16 for 2018.

For 2017:
2017 InvestmentIncomeInterestAndDividend 21655159
2017 OilAndGasRevenue 18613203
2017 RegulatedAndUnregulatedOperatingRevenue 18614810
2017 RegulatedAndUnregulatedOperatingRevenue 21652265
2017 RevenueFromContractWithCustomerExcludingAssessedTax 18602475
2017 RevenueFromContractWithCustomerExcludingAssessedTax 21653976
2017 RevenueFromContractWithCustomerIncludingAssessedTax 21654060
2017 RevenuefromContractwithCustomersTransportationandProcessingCosts 24197257
2017 RevenueMineralSales 15794561
2017 RevenueOilAndGasServices 18604404
2017 Revenues 15796345
2017 Revenues 18605331
2017 Revenues 21659490
2017 RevenuesAndOtherIncome 24259790
2017 RevenuesAndOtherIncome 24488188
2017 RevenuesAndOtherIncome 24508761
2017 SalesRevenueGoodsGross 18616032
2017 SalesRevenueGoodsNet 15800900
2017 SalesRevenueGoodsNet 18614606
2017 SalesRevenueGoodsNetExcludingExciseAndSalesTaxes 20763215
2017 SalesRevenueNet 15803196
2017 SalesRevenueNet 18614636
2017 SalesRevenueNetOfExciseTaxes 21724253
2017 SalesRevenueServicesGross 18614619
2017 SalesRevenueServicesNet 18614749
2017 SellingGeneralAndAdministrativeExpense 21658596
2017 TotalRevenuesAndOtherIncome 24421937

For 2018:

2018 InvestmentIncomeInterestAndDividend 21655159
2018 RegulatedAndUnregulatedOperatingRevenue 21652265
2018 RevenueFromContractWithCustomerExcludingAssessedTax 18602475
2018 RevenueFromContractWithCustomerExcludingAssessedTax 21653976
2018 RevenueFromContractWithCustomerIncludingAssessedTax 21654060
2018 RevenuefromContractwithCustomersTransportationandProcessingCosts 24197257
2018 Revenues 18605331
2018 Revenues 21659490
2018 RevenuesAndOtherIncome 24259790
2018 RevenuesAndOtherIncome 24488188
2018 RevenuesAndOtherIncome 24508761
2018 SalesRevenueGoodsNet 18614606
2018 SalesRevenueNet 18614636
2018 SalesRevenueNetOfExciseTaxes 21724253
2018 SalesRevenueServicesNet 18614749
2018 SellingGeneralAndAdministrativeExpense 21658596

As always, thank you for your help, David!

Tim

[Tim Bui]

By the way, David, I cannot use just one concept.name because some companies use “Revenues” as total revenue while some others use “Revenues” as part of “RevnueandOtherIncome”, and some use totally different concept.names.

Thanks
ps. I just realized I wrongly added the very last line in 2018 concept above. It’s a SG&A line.

[Tim Bui]

Hi David,
Using the code below, I can get the Ticker and Facts. However, is there a code where I can reference to a CIK and get all of the concept.id for each financial statement for a particular year? I can get all of the concept names per CIK from Jim Truscott’s XBRLXL website. But I don’t know how to get all of the concept.id.

(CONCATENATE(A1&”/report/fact/search?entity.cik=”&TEXTJOIN(“,”,TRUE,GetTickers!B10:B100)&”&fact.has-dimensions=false&fact.ultimus-index=1&concept.local-name=”&GetTickers!B8&”&period.fiscal-period=”&GetTickers!D1&”&period.fiscal-year=”&TEXTJOIN(“,”,TRUE,GetTickers!A2:C2)&”&fields=entity.cik,period.fiscal-year,period.fiscal-period,concept.local-name.sort(ASC),concept.id,fact.value,entity.name,entity.ticker”)

By the way, thank you for pointing me to get the Quarterly Financial Data Set from the SEC website. Nevertheless, to make this data set useful, I think I need the concept.ID from XBRL US to group and manage data because concept.id is much more fiscal year and company specific.

As always, thank you for your help, David!

[David Tauriello]

Tim – in the forumula you posted, remove fact.has-dimensions=false to return all facts. See page 13 of API Documentation for details on this parameter.

[Tim Bui]

Got it. Thank you, David!

[Tommy Carstensen]

Tim, did you ever manager to create a list of identical concepts? It’s a bit of a mess, when it’s not standardized. I’m surprised the SEC chose to allow companies to use random names as they see fit. It completely defeats the purpose of XBRL.

[Tim Bui]

Hi Tommy,

I started on the standardization but have not finished it yet. At the beginning, I planned to use data from XBRL US but David Tauriello pointed me to the SEC website where I can get all of the data from all filers more efficiently (https://www.sec.gov/dera/data/financial-statement-data-sets.html). I am importing these data into a SQL Server to do standardization. I am learning the SEC data structure and think I have a way to do better standardization, but I need to test this method further. I would be happy to share my methodology with you if it works. In the meantime, if you want, I can give you what I have done so far, but it is incomplete. I use financial information for investing so I only standardize the items that I think relevant to my work.
Yes, I agree that whichever entity (SEC, AICPA, CFA,…) that allows companies to name their tags (concepts) at-will really weaken the case for XBRL and disadvantage “smaller” financial data users like me. It’s illegal to fudge the numbers but accountants can name the numbers whatever they want and can change these names when it’s convenient–making peer comparison or historical comparison extremely difficult. Data providers such as Bloomberg, Factset, CapitalIQ, Thomson Reuters will be in business for a very long time.

[Tommy Carstensen]

Aye Tim, I think Morningstar and the rest of them will continue to thrive as long as the data is not standardised. I wanted to plot the data over time and across companies within an industry, but that turned out not to be trivial, because there is no requirement for the data to be homogenous over time and across industries. I hope the law and XBRL specifications are changed and data is homogenised going forward to enable small fintech companies to compete.

Here my first attempt to plot data for General Mills over time:
http://midgardinvest.com/10q10k/G/GIS/

I shall be watching this thread to learn more about your attempts to standardise the data. I appreciate your efforts on behalf of the community. Thanks!

[Peter Guldberg]

Are the report.id and dts.id fields generated in the load process by XBRL US, or do they match anything that can be found in the filings on SEC Edgar?

[Peter Guldberg]

The 3 latest quarterly reports from BBBY (CIK 0000886158) seem to be missing from the database. Those filed on 2018-07-06, 2018-10-10 and 2019-01-09.

Would this be because the DB is not necessarily complete, or am I perhaps searching for them the wrong way?

[David Tauriello]

These are internal to the XBRL Database of Public Filings.

[David Tauriello]

Peter – thanks for bringing this to our attention; we’re still assessing what happened, but the filings are there:

/report/search?entity.id=7813,12275&fields=report.*,report.limit(20),report.accepted-timestamp.sort(DESC)

NOTE: the 7813 id will be merged into 12275 id in the near term.

[D Q]

I too am interested in standardizing concepts for investing purposes.
I don’t know SQL but DM me if I can help.

[Tim Bui]

Hello DQ, I am still working on standardizing the tags. I think I am on the right track, however this is not a trivial task. I was able do download 87MM (yes million) lines of data from the SEC. And now I am parsing them out using SQL Server. It is slow moving because I have to check and recheck to make sure the data match with the 10Ks and 10Qs. I am happy to give the results to whoever wants them because they are not proprietary data and I am too a beneficiary of communities like XBRL US (David Tauriello at XBRL US has spent a lot of time bringing me up to speed.)

To use this massive amount of data, one will need to use some database for sorting and screening. Microsoft Excel or Access cannot handle this much of data. Maybe we all can put in a request to XBRL US to allow members to contribute to this standardization efforts by creating a depository area on XBRL US pgAdmin.

Commercial data providers do provide their own standardization but depending one’s need, the standardization has to be customized somewhat. Sorry for this oxymoron word of customizing the standardization. But for example, companies reports several types of Account Receivables. There are 948 distinct tags on just AccRec. Most data providers have just 1 line for AccRec. I try to break them down to 4 subcategories: AccRec_Trade_Short_Term, Acc_Rec_Finance_Short_Term, Acc_Rec_Trade_Long_Term and Acc_Rec_Trade_Long_Term. Acc_Rec_Trades are the receivables from regular customers. Acc_Rec_Financing are the receivables from financing activities such as a promissory note coming due or GM financing the dealers’ floorplans. ST or LT determines whether they are in current assets or long term assets. The change of each of these subcategories provides different type of information to the financial readers.

In the meantime, I would highly recommend you to check out the XBRL XL (https://xbrlxl.com/) website created by Jim Truscott. Jim also tries to do standardization. Jim had a demonstration of his Excel API hosted by XBRL US a few months back.

Let’s hope we here from XBRL US on this matter.
Tim

[Tim Bui]

By the way, DQ, There is a company named Intrinio (https://intrinio.com/) that provides standardization on Excel. The prices seem to be very reasonable. I I tested their system out and found that their intereface is very to use. I think their data is suitable for most purposes. I do I own parsing raw data because I wanted to do further subsegments for my own use.

[Author]

Posts