[david.tauriello@xbrl.us]

From a user:

I am writing to inquire about a possible XBRL extraction issue. When I try to extract simple quarterly revenue figures, some of the last fiscal quarters are zero.

For example, for MSFT and AAPL last available FQ4 is zero (even though previous FQ4 were available).

I was not able to find a ‘0’ reported in Q4 for either company. It seems these companies have moved away from disclosing Q4 revenue in their 10-Ks, so you’ll need to back out 3QCUM from Y to get this detail.

https://api.xbrl.us/api/v1/fact/search?entity.code=0000320193,0000789019&period.fiscal-period=Y,3QCUM,4Q&concept.local-name=RevenueFromContractWithCustomerExcludingAssessedTax&fact.has-dimensions=false&fact.ultimus=true&fields=report.filing-date.sort(DESC),entity.name.sort(ASC),period.fiscal-year.sort(DESC),concept.local-name,fact.value,period.fiscal-period,report.id,report.sec-url

Don’t hesitate to post follow up here.

[Timur Mirzaev]

Some facts are repeating when extracting several documents in a single call. I can see the reason (tag/concept change), but don’t know the solution. Is there any way to remove those duplicating values, but keep the two tags/concepts in a query?

Here is my API call with row 2 and 3 being similar:

XBRL.showData(""&fact&"&entity.cik="&CIK&fiscal&"3Q&concept.local-name="&"IncomeLossFromContinuingOperationsBeforeIncomeTaxesExtraordinaryItemsNoncontrollingInterest,IncomeLossFromContinuingOperationsBeforeIncomeTaxesMinorityInterestAndIncomeLossFromEquityMethodInvestments"&","&Legend!C6&"&fact.has-dimensions=false&fact.ultimus=true&report.document-index=1,2,3,4&fields=report.period-end,period.fiscal-year.sort(DESC),concept.local-name,fact.numerical-value,period.fiscal-period.sort(DESC)","","","1")

[Timur Mirzaev]

Just a little update: Missed part of my query that refers to a named range “CIK”.
In this very case it should stand for “0000789019” (for MSFT).

[Timur Mirzaev]

Hi Tim,

Have you been able to resolve your issue with duplicate values once there is a tag change? I am faced with exactly the same issue.

Regards,

Timur

[Tim Bui]

Hi Timur,
I am still struggling to make the vast amount of data provided by the SEC be useful for my analysis. So far I have no solutions to standardize the financial items to make them comparable inter-industry (financial items have to be compared to others in the same industry) or overtime (when the tag names changed). Your question brings up an idea for me to ask the SEC staff if they provide a code to alert users if there are changes in the tag names or reporting format. So far, the SEC only provides a code if a company changes its name (Submission file, code ‘former’ and ‘changed’ in the Financial Statement Data Sets and Financial Statement and Notes Data Sets)

Regards,

Tim

[Peter W Reed]

I hope this helps – In the “US_GAAP_Taxonomy_YYYY.xlsx” spreadsheet the tab ‘Elements’ has deprecation description. Columns ‘N’ and ‘O’ provide the information on a tags status.

I gave up scraping the 10-K/Q for just the reason mentioned. The XBRL tag names change over time. So I went back to Yahoo! and Alpha Vantage.

One reverse engineering activity I started doing was to map the Yahoo! element name to the XBRL tag name. The correlation id is the value. For example Alpha Vantage – Total Asset 10,000 and XBRL Asset 10,000. Good hunting!

[david.tauriello@xbrl.us]

Hi Timur – thanks for writing. I’m not sure what additional parameter you’re using from C6 on the Legend worksheet (?). If I remove that and update ‘fiscal’ to be the parameter ‘period.fiscal-period’ I don’t see any duplication:

https://api.xbrl.us/api/v1/fact/search?entity.cik=0000789019&period.fiscal-period=3Q&concept.local-name=IncomeLossFromContinuingOperationsBeforeIncomeTaxesExtraordinaryItemsNoncontrollingInterest,IncomeLossFromContinuingOperationsBeforeIncomeTaxesMinorityInterestAndIncomeLossFromEquityMethodInvestments&fact.has-dimensions=false&fact.ultimus=true&report.document-index=1,2,3,4&fields=report.period-end,period.fiscal-year.sort(DESC),concept.local-name,fact.numerical-value,period.fiscal-period.sort(DESC)

Drop the query in A1, and use =XBRL.showData(A1,””,””,”1″) in A2

Let me know if there’s something else I’m missing.

[Tim Bui]

Thank you for pointing this out, Peter!
As you pointed out col N and col O alerts the tags that were deprecated (referenced to col A which has the actual tag names that were deprecated).
I think it will take some programming skills (way beyond my current ability) to match these deprecated tags and their years to the point-in-time data to line up the numbers properly.
It is so frustrating to see there are so much data there but we individuals (at least those with low technical skills like me) can’t take advantage of them yet.

Thanks again, Peter!

[Timur Mirzaev]

Hi, David!

Thank you very much for your reply. I forgot to fully correct my query for named ranges, hence the error.
But your correction is exactly what my query should have looked like. I have done exactly what you showed above and I DO get duplicating values – see D4 and D5 of the output (if xbrl.showData is in A2), which is $17,236,000,000 for 3Q of FY’2021.

So my question here is whether it is possible to introduce any parameter that would remove those duplicates (sort of a “unique” function maybe, but on the query level so that the per query limit is not effected that much).

Regards,
Timur

[Timur Mirzaev]

Hi, Tim and Peter!

Thank you both for your replies. Could you please guide me to where I can get the Taxonomy xlsx file mentioned above? I went to XBRL.us Taxonomy page but the xlsx links (for 2020-2022 periods) were broken (“BlobServer.txt” was all I was getting).

Thank you in advance!

Regards,
Timur

[Peter W Reed]

Something on the SEC site changed, or I’m not remembering correctly. Below is the link I remember using to get the Excel files for taxonomies. All I’m seeing now are xml schema (.xsd) files.

https://www.sec.gov/info/edgar/edgartaxonomies.shtml#IFRS2018

[Tim Bui]

Timur, I tried to reply 3 times with the URLs but this text app probably does not allow URL.

You can google “US GAAP Taxonomies 2022” and it will lead you to the FASB site to download the Excel sheet.

You can also get the Excel sheet from this XBRL US website if you search for US Taxonomies

[Peter W Reed]

Tim,

I didn’t have any success with the FASB website earlier today. That is where I remember getting it before, too.

So I tried a different track and went to the IFRS site. The link below is for 2019. The information is presented in a different manner – it may even be better for your application. I tried using the “Link” function for this link. I hope it works,

IFRS_landing page for 2019 taxonomy

[david.tauriello@xbrl.us]

Hi Timur – these are different facts appearing in different reports. Look at the full concept.local-name for rows 4 and 5. Further, if you add report.sec-url to the fields to be returned, you can browse to the EDGAR entries and confirm the values are different.

IncomeLossFromContinuingOperationsBeforeIncomeTaxesMinorityInterestAndIncomeLossFromEquityMethodInvestments – https://www.sec.gov/Archives/edgar/data/789019/000156459021020891/0001564590-21-020891-index.htm

IncomeLossFromContinuingOperationsBeforeIncomeTaxesExtraordinaryItemsNoncontrollingInterest – https://www.sec.gov/Archives/edgar/data/789019/000156459022015675/0001564590-22-015675-index.htm

When you review the face of the report these fact values do have the same label – https://api.xbrl.us/api/v1/label/search?dts.id=456408,583061&concept.id=27969523,31293140&fields=concept.id,concept.local-name,label.role-short,label.text – it may be that the company’s external reporting team reconsidered the definition it had been using, and decided to transition to a new concept.

The XBRL API does not currently normalize facts with a mechanism as you describe that would evaluate and eliminate facts with duplicated labels like this.

[Asif Saberi]

Hi David: Thanks for your pointers. I am able to download data into excel, but the downloaded data formatting must be text since I cannot do mathematical operations on returned values. I am also not able to force format the cells into which these values are returned or point to these cells from another pre-formatted cell. Appreciate any pointers.

[Author]

Posts