Forum Replies Created
-
AuthorPosts
-
David TaurielloKeymaster
Boolean is now enabled for fact.ultimus and report.restated, and we’ve added a label endpoint.
fact.ultimus=true
returns the same asfact.ultimus-index=1
example:/fact/search?fact.ultimus=true&period.fiscal-year=2017&period.fiscal-period=Y&fact.has-dimensions=false&concept.local-name=AssetsCurrent&fields=entity.name.sort(ASC),entity.cik,concept.local-name,fact.value,fact.ultimus-index,fact.ultimus,unit.qname
report.restated=false
returns the same asreport.restated-index=1
example:/report/search?entity.cik=0000357097&report.period-end=2011-12-31&report.restated-index=1,2,3,4,5,6,7,8,9,10&fields=dts.id,report.accession.sort(ASC),report.restated,report.restated-index
We’ve also added a label endpoint and updated the XBRL API Documentation
We’ll cover these topics, take questions and more at next week’s Member-only webinar – https://xbrl.us/events/20190220
David TaurielloKeymasterThis query returns concept.local-name consistent with what’s in the xml the issuer submitted (we copy these files into our database every 10 – 15 minutes):
/report/fact/search?fact.id=129160850&fields=report.sec-url,report.entry-url,fact.xml-id,fact.value,concept.local-name
If possible, please post back and let me know where you’re noting the inconsistency with what we’ve captured to the XBRL US Database of Public Filings (https://www.sec.gov/cgi-bin/viewer?action=view&cik=320187&accession_number=0000320187-16-000336&xbrl_type=v# or https://www.sec.gov/Archives/edgar/data/320187/000032018716000336/nke-5312016x10k.htm).
David TaurielloKeymasterTim – to get lists of concepts in the base (standard) US GAAP and IFRS Taxonomies, query:
/dts/search?dts.taxonomy=US%20GAAP,IFRS&fields=dts.id,dts.taxonomy-name
With each dts.id from above that you want concept information for, query like this:
/concept/search?dts.id=257590&fields=concept.local-name.sort(ASC),concept.*,label.*
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.
Peter GuldbergParticipantI guess it must be NKE that have filed it wrong then.
The fact above is from 2016 10-K but actually relates to the 2014 annual number. It is filed as “StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest”
but the value of 85000000 is actually “AccumulatedOtherComprehensiveIncomeLossNetOfTax”It appears correctly in the interactive report at https://www.sec.gov/cgi-bin/viewer?action=view&cik=320187&accession_number=0000320187-14-000097&xbrl_type=v#
so I think they have filed it – mistakenly – under both XBRL names.If you run the query:
https://api.xbrl.us/api/v1/fact/search?report.id=163622&concept.local-name=StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest,AccumulatedOtherComprehensiveIncomeLossNetOfTax&period.fiscal-year=2015,2016&fact.has-dimensions=false&fields=concept.local-name,period.fiscal-year,period.fiscal-period,fact.value,fact.has-dimensions
the value appears under both concepts. But those are very different concepts, and would never be the same. In the case of NKE, they do not have a minority interest and should not be using the StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest concept at all. They should use StockholdersEquity, which they do, and that is filed correctly.
They make this mistake consistently in later reports – so I guess the question becomes, is there a place – perhaps at the SEC? – where you can report obvious filing mistakes, to try and get them to get the filer to fix it?
Tim BuiParticipantHi David,
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!
Tim
David TaurielloKeymasterUse
ENDPOINT.offset(integer)
in the fields= portion of your query to get additional concepts. Something likeconcept.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.id
returns a unique integer that corresponds withconcept.local-name
so it can be used as a reliable substitute.Tim BuiParticipantThank 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
TimTim BuiParticipantWhen I use code A below, I get the desired concept.local-names, but the call does not return the “network.role-description” (the financial statements where the concept came from)–even as I added different “relationship” items in the code.
A—=CONCATENATE(“https://api.xbrl.us/api/v1/concept/search?dts.id=257590&fields=concept.offset(0),concept.local-name.sort(ASC),concept.*,relationship.source-name,relationship.id,relationship.*,label.*”)
When I use code B below, I do get the “network.role-description”, which I need.
Is there a way that I can get the “network.role-description” to work in code A above?B–=CONCATENATE(“https://api.xbrl.us/api/v1/relationship/search?dts.id=257590&fields=concept.offset(2000),concept.local-name.sort(ASC),concept.*,relationship.source-name,relationship.id,relationship.*,label.*”)
Thank you!
Tim BuiParticipantTo shorten the code, rather than using concept.local-name, I started to use concept.id to call for fact.values. However, I started to realize that the numbers of concept.id change depending on the CIK and the fiscal year–even though the concept.local-names are exactly the same.
Can you please describe what drives this difference so that I can better construct the code?
Here are some examples of different concept.ids that have the same concept.local-name:
entity.cik period.fiscal-year concept.local-name concept.id
0001109879 2011 IncreaseDecreaseInInventories 9049
0000090168 2011 IncreaseDecreaseInInventories 68486
0000027093 2011 IncreaseDecreaseInInventories 6249201
0000812074 2012 IncreaseDecreaseInInventories 10288733
0000110621 2014 IncreaseDecreaseInInventories 15795815
0001122976 2013 IncreaseDecreaseInInventories 12743567
0001421182 2015 IncreaseDecreaseInInventories 10288733
0000811596 2016 IncreaseDecreaseInInventories 18601247
0001524741 2017 IncreaseDecreaseInInventories 21660607
0000004281 2018 IncreaseDecreaseInInventories 21660607Tim BuiParticipantHello,
I see there is a table named cik_tickers on pgAdmin, under xbrl_public database. However, when I try to open it, it says ” ERROR: permission denied for relation cik_tickers, SQL state: 42501.”Could I have access to this table or can you point me to a place where I can get an up-to-date cik list of companies?
I found this list of Tickers and CIKs but it is a few years old and does not include recent public companies.
http://rankandfiled.com/#/data/tickers
Thank you!
Tim BuiParticipantThe query based on ‘Label.text’ that Campbell had demonstrated today is amazing. Using the simple word ‘Debt’, the query returned 1999 rows of label.texts that contain the word ‘Debt’ just on dts.id 292503 alone.
Rather than querying based on label.text, can we query based on concept.local-name?
For ease of changes, I moved the label.text to the end of the query:
https://api.xbrl.us/api/v1/label/search?fields=label.*,concept.local-name.sort(ASC)&dts.id=292503&label.text=DebtHowever, when I replaced label.text with concept.local-name, no information returned.
Thank you!
David TaurielloKeymasterI’m not clear on what you are trying to return, and as far as I know, there’s no intersection of
concept
andrelationship
in the API currently. Please see the diagram on p. 20 of the documentation – https://xbrl.us/xbrl-api-documentation.In the A query, you cannot get
relationship
details from theconcept
endpoint, so those parameters should be removed from the ‘fields’ portion of the query. And with B, you cannot getconcept
details from therelationship
endpoint, so those shouldn’t be included as ‘fields’ to return.Maybe you’re trying to use the
relationship
endpoint to get concepts included in a specific part of the filer’s report – ?A query like this will return details for Apple:
/relationship/search?network.id=37096851,37096836&relationship.target-is-abstract=false&network.link-name=presentationLink&fields=network.id.sort(ASC),relationship.tree-sequence.sort(ASC),relationship.*
If you look at
relationship.target-name
in the fields returned, these are the concepts included for the disclosures listed innetwork.role-description
David TaurielloKeymasterEach company submits its own ‘extension taxonomy’ to the SEC – it’s essentially a cut-down version of only the US GAAP elements needed, plus any extension elements the company creates because it cannot find an existing element that adequately covers the fact(s) being reported by the extension elements.
So, while the
concept.local-name
values in your list above are identical, theconcept.id
values will differ, because our database assignsconcept.id
values based on the facts as they appear in the company’s instance, not as they may relate to the US GAAP taxonomy.If you use
concept.is-base
(set as =true in the query or returned as true/false in fields) you can quickly distinguish whether the concept.id is a US GAAP element.Sunday, March 3, 2019 at 1:31 PM in reply to: Access to cik_tickers table on PostgreSQL Server #126422David TaurielloKeymasterThis SQL query should provide all CIK detail (
entity_code
)SELECT * FROM entity ORDER BY entity_code
Sunday, March 3, 2019 at 1:36 PM in reply to: Can I substitute Label.Text with concept.local-names? #126425David TaurielloKeymasterThe text matching Campbell demonstrated during the Member-only webinar is exclusive to the
label
endpoint at this time. Stay tuned, as we may roll out enhancements like this to other endpoints in the coming months. -
AuthorPosts