[David Tauriello]

Boolean is now enabled for fact.ultimus and report.restated, and we’ve added a label endpoint.

fact.ultimus=true returns the same as fact.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 as report.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 Tauriello]

This 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 Tauriello]

Tim – 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 Guldberg]

I 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 Bui]

Hi 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 Tauriello]

Use 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.id returns a unique integer that corresponds with concept.local-name so it can be used as a reliable substitute.

[Tim Bui]

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
Tim

[Tim Bui]

When 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 Bui]

To 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 21660607

[Tim Bui]

Hello,
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 Bui]

The 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=Debt

However, when I replaced label.text with concept.local-name, no information returned.

https://api.xbrl.us/api/v1/label/search?fields=label.*,concept.local-name.sort(ASC)&dts.id=292503&concept.local-name=Debt

Thank you!

[David Tauriello]

I’m not clear on what you are trying to return, and as far as I know, there’s no intersection of concept and relationship 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 the concept endpoint, so those parameters should be removed from the ‘fields’ portion of the query. And with B, you cannot get concept details from the relationship 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 in network.role-description

[David Tauriello]

Each 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, 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, 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.

[David Tauriello]

This SQL query should provide all CIK detail (entity_code)

SELECT * 
FROM entity
ORDER BY entity_code

[David Tauriello]

The 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.

[Author]

Posts