Friday, January 4, 2019 at 5:14 PM #119805
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:
To find Nonrecurring charges (such as impairment and restructuring), so far I found 13 concepts:
The above 13 non-recurring items are exclusive of the additional 7 gain/loss concepts below:
Monday, January 7, 2019 at 2:18 PM #119925
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.
Monday, January 7, 2019 at 9:46 PM #119958
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
Monday, January 7, 2019 at 9:58 PM #119959
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:
Thursday, February 14, 2019 at 7:30 PM #123764
Tim – to get lists of concepts in the base (standard) US GAAP and IFRS Taxonomies, query:
With each dts.id from above that you want concept information for, query like this:
Every company references a base taxonomy in its filing as a starting point. The company filing is essentially a taxonomy that is inheriting from the base and adding concepts as necessary according to the company’s policies and practices with respect to its financial statements.
NOTE: you could include concept.local-name with a comma-delimited list before the fields that are returned to get only specific concepts.
If you pull an entire taxonomy, understand that there’s a great deal of information and a significant number of records involved, so this may take a while. Fortunately, these taxonomies don’t change – new releases annually – so the details only need to be pulled one time.
Thursday, February 21, 2019 at 11:19 AM #124615
I finally got around to apply the codes that you described above. It’s just amazing how much information returned from the call.
A couple questions please:
1. Using =CONCATENATE(A1&”/concept/search?dts.id=257590&fields=concept.local-name.sort(ASC),concept.*,label.*”) only return about 2000 concept local names. How can I change the code so that I can get a complete list so that I can pick and choose what I need?
2. I am still trying to find ways to shorten my code to get more factual data for more companies per call (to be under the length of the code that Google restricts). Can I use the concept.id in place of the concept.local-name in the call?
As always, thank you for help!
Thursday, February 21, 2019 at 1:49 PM #124655
ENDPOINT.offset(integer)in the fields= portion of your query to get additional concepts. Something like
concept.offset(2001)will show concepts after 2,000 (for Power User and Sole Practitioner Individuals, as well as all Organizational XBRL US Members). This offset works for all endpoints (fact, dts, etc.)
See the documentation and this thread for more information: https://xbrl.us/forums/topic/how-to-get-a-sample-of-records-via-xbrl-api-for-evaluation/#post-116618.
Again – because you’re querying for the base taxonomy concepts, the details you return will not change (these are published once), so your best/most effective approach will be to copy the details to a tab (or file) and use that as your reference.
Like other “id” parameters,
concept.idreturns a unique integer that corresponds with
concept.local-nameso it can be used as a reliable substitute.
Thursday, February 21, 2019 at 4:53 PM #124690
Thank you, David. I was able to download 17,035 concepts. The next part is to understand these data and select which one to use.
Thank you again for your help
Friday, April 19, 2019 at 6:38 PM #133617
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.
Saturday, April 20, 2019 at 6:35 AM #133655
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.
Saturday, April 20, 2019 at 1:18 PM #133680
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:
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!
You must be logged in to reply to this topic.
Lists of similar concepts 4 days, 18 hours ago
Lists of similar concepts 5 days, 1 hour ago
Lists of similar concepts 5 days, 13 hours ago
Extracting Data From A Particular Financial Statement 2 weeks, 4 days ago
Extracting Data From A Particular Financial Statement 2 weeks, 4 days ago
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table
XBRL API Resources
- The XBRL API
- XBRL Data Community
- XBRL API Documentation (PDF)
- Getting Started with Google API Access
- Sample Queries (Insomina REST client) - requires account provision