[Ashutosh Pandey]

Can these APIs be used on the XBRL data shredded on MongoDB?

[david.tauriello@xbrl.us]

There were several questions we didn’t answer completely from this week’s webinar:

[david.tauriello@xbrl.us]

Do you provide any historical trace for deprecated concepts? Could we trace the lineage of a concept between taxonomies using the API?

You could use the dts for approved base taxonomies (US GAAP, IFRS) to query concepts and look for ‘Deprecated’ in the label … something like:

/concept/search?dts.id=257581,257584,257585,257586,257587,257588,257589,257590,292503&concept.local-name=ResaleAgreementsMaturities&fields=dts.id.sort(ASC),concept.local-name,concept.namespace,label.*

If you do this for the example, you’ll see the element was deprcated as of 2016 US GAAP. This is not particularly effective, though, and in addition, FASB has moved the notation for deprecated concepts from the label to a reference, so the detail exists in two places.

We’ve got plans to implement string search in a couple of areas and these will be included (thanks for a great suggestion), so a query on ‘deprecat’ will pull only those values.

[Peter Guldberg]

It used to be possible to sort on a field that was not returned, for example like this:

https://api.xbrl.us/api/v1/fact/search?report.id=22860,22861,22862&fact.ultimus=1&report.id.sort(ASC)&concept.local-name=DocumentPeriodEndDate&fact.has-dimensions=false&fields=period.fiscal-period,period.fiscal-year

but that no longer seems to be working. If I move the report.id into the fields list it works fine, but then it also gets returned in the query.

[david.tauriello@xbrl.us]

Is there a way to go back from the data received through the API to the actual human-readable SEC filings where the data come from, to quickly double check the data for example?

On the webinar, we mentioned that you can use report.sec-url from the report endpoint to return the URL to the SEC index page for the filing. You can also use report.entry-url for the location of the instance (facts) for the filing.

Further – if you combine the report.entry-url with the fact.xml-id you have a citation to the location of the fact within the instance (NOTE: not all facts have a corresponding xml id in EDGAR).

https://api.xbrl.us/api/v1/fact/search?entity.cik=0000320193&period.fiscal-year=2018&concept.local-name=AccumulatedOtherComprehensiveIncomeLossNetOfTax&report.type=10-K&fact.has-dimensions=false&concept.is-monetary=true&fields=report.entry-url,fact.xml-id,fact.value,concept.local-name.sort(ASC)

If you open the report.entry-url in a browser and use ‘find in page’ to search on the fact.xml-id value, it will bring you to the AOCI value.

[david.tauriello@xbrl.us]

I checked with team and there haven’t been any changes that would impact .sort, which is applied on returned fields per the documentation:

Any value returned can be sorted in ascending or descending order, by adding an additional property to a field value.

Maybe the sort is on one of the period fields in the query you posted – ? the BS Compare template uses this: https://api.xbrl.us/api/v1/fact/search?report.id=22860,22861,22862&fact.ultimus=1&concept.local-name=DocumentPeriodEndDate&fact.has-dimensions=false&fields=period.fiscal-period,period.fiscal-year,report.id.sort(DESC)

[Peter Guldberg]

All right – maybe it was never meant to work. Basically, the query I posted definitely used to work, but now it doesn’t. However, it’s entirely possible that it was never meant to work.

I think my question here really is: Is there a way to sort the returned fields on a field that is not itself returned?
I agree that the documentation would seem to indicate that you can only sort on fields that are actually returned.

[Tim Bui]

I finally learned to use the API (of course through the patience and help from David and Peter) and all I can say is it’s an amazing system.

A question on the report.id number: does each company has one report.id (as the cik) or each financial statement has its own report.id number (different balance sheet of different years has its own report.id number)?

Secondly, is there anyway that we can query using pd Admin to get a list of ticker, cik and report.id?

Thanks

[Tim Bui]

Is there any way to increase the limit of the length on the code?
When I try to standardize a concept that is composed of more than 4 other concepts, I get an error “Limit Exceeded: URLFetch URL Length. (line 106)”
For example, if I want to have a standardized Selling, Gen & Admin Expenses (SGA_Tot) from these concepts below, I receive an error:
SellingGeneralAndAdministrativeExpense
GeneralAndAdministrativeExpense
SellingAdministrativeAndResearchAndDevelopmentExpenses
GeneralAndAdministrativeExpenseAndOtherOperatingExpenses
Thank you!

[Tim Bui]

So far to get around the RL Length, I break the code into 2 and do the summation in Excel.
SGA1:SellingGeneralAndAdministrativeExpense, generalandadministrativeexpense,SellingAdministrativeAndResearchAndDevelopmentExpenses

SGA2: GeneralAndAdministrativeExpenseAndOtherOperatingExpenses, OtherSellingGeneralAndAdministrativeExpense, SellingAndMarketingExpense

My question: is there a way to add the results of 2 or more concepts within an API code? This would help to reduce the number of calls.
Thanks

[Tim Bui]

I was trying to download the revenues of different companies, using Rev_Tot = Revenues, SalesRevenueNet, SalesRevenueGoodsNet or TotalRevenuesAndOtherIncome

However, some companies changed their tags and the API returns 2 numbers of sales for the same year. Using APD Corp (CIK 0000002969) as an example:

As you can see below, APD used the tag “SalesRevenueNet” from 2009 to 2017, but
in 2018 it started to use “Revenues.” Thus the “Revenues” are reported for 3 years, 2018,2017 and 2016. Since APD did not go back to change the tag for the previous years, we end up have duplications for 2016 and 2017 that were reported under “SalesRevenueNet” tag.

I noticed that this situation occurs at other companies as well.

Is there any way we can eliminate the duplications?

Thank you!

APD Revenues_Y16 9/30/2016 7503.7
APD Revenues_Y17 9/30/2017 8187.6
APD Revenues_Y18 9/30/2018 8930.2
APD SalesRevenueNet_Y09 9/30/2009 8256.2
APD SalesRevenueNet_Y10 9/30/2010 8616.1
APD SalesRevenueNet_Y11 9/30/2011 9673.7
APD SalesRevenueNet_Y12 9/30/2012 9611.7
APD SalesRevenueNet_Y13 9/30/2013 10180.4
APD SalesRevenueNet_Y14 9/30/2014 8384
APD SalesRevenueNet_Y15 9/30/2015 7824.3
APD SalesRevenueNet_Y16 9/30/2016 7503.7
APD SalesRevenueNet_Y17 9/30/2017 8187.6

[Tim Bui]

I worked around the duplication issue by copying the data into Excel and naming the concept as X_Rev_Tot_Yxx, then use “Remove Duplicate”. In this case the duplicates are 2016 and 2017.

ADP X_Rev_Tot_Y10 09/30/10 8,616.10
ADP X_Rev_Tot_Y11 09/30/11 9,673.70
ADP X_Rev_Tot_Y12 09/30/12 9,611.70
ADP X_Rev_Tot_Y13 09/30/13 10,180.40
ADP X_Rev_Tot_Y14 09/30/14 8,384.00
ADP X_Rev_Tot_Y15 09/30/15 7,824.30
ADP X_Rev_Tot_Y16 09/30/16 7,503.70
ADP X_Rev_Tot_Y16 09/30/16 7,503.70
ADP X_Rev_Tot_Y17 09/30/17 8,187.60
ADP X_Rev_Tot_Y17 09/30/17 8,187.60
ADP X_Rev_Tot_Y18 09/30/18 8,930.20

[david.tauriello@xbrl.us]

Tim – thanks for posting back with your solution to the issue in the initial post.

The message ‘Limit Exceeded: URLFetch URL Length.’ is a Google Services error (https://developers.google.com/apps-script/guides/services/quotas). According to the page, there’s a 2kB limit (about 2,000 characters).

Some things to try:

• restructure the query to eliminate any unnecessary filters/screens and/or fields returned (maybe there’s a more efficient way to call for the data you need)
• try a URL shortener (bit.ly is one such service) – I don’t have any experience with this, but it may be worthwhile if it works

[david.tauriello@xbrl.us]

Tim – fact.ultimus-index=1 returns the latest reported value for the fact(s) in the query. Each increment of the integer represents a previous report in which the fact.value was reported, although the number(s) may or may not have changed.

We’re planning to convert fact.ultimus to a boolean in 2019 (it’s currently integer, so it functions the same as the ‘index’), so eventually ‘fact.ultimus=true’ will return the same data points as fact.ultimus-index=1

See Restated markers for more information.

[david.tauriello@xbrl.us]

The XBRL API is designed to work with any database containing data that conforms to the 2.1 XBRL Specification.

Please note: We don’t support using the XBRL API and our Database of Public Filings to siphon/feed another database – this will strain our resource and may result in termination of access.

Organizational members should contact us for more information (info@xbrl.us).

[Author]

Posts