Home › Forums › The XBRL API › Concept name & facts works for some reports but not others
- This topic has 7 replies, 2 voices, and was last updated 2 years, 8 months ago by Samuel Pun.
-
AuthorPosts
-
-
Sunday, March 6, 2022 at 11:45 AM #198405Samuel PunParticipant
I have the following which works (company = Gan Ltd):
=XBRL.showData(CONCATENATE(“https://api.xbrl.us/api/v1/fact/search?entity.cik=0001799332&period.fiscal-year=2021&period.fiscal-period=1Q&concept.local-name=NetCashProvidedByUsedInOperatingActivities&fact.has-dimensions=false&fields=concept.local-name,fact.value,period.fiscal-period,”,”period.fiscal-year,fact.limit(),fact.offset()&”),””, “”, “1”)
However, when I try it with 2021 2Q, 3Q and also 2020 – Y, it doesn’t pull any data even though I can see it in Edgar. Is there a reason for that?
thanks in advance!
Sam -
Monday, March 7, 2022 at 3:37 PM #198449David TaurielloKeymaster
Hi Sam – thanks for writing. One of the great features (/s) of Excel is its limitation on characters in a formula.
By default, the add-in calculates that 255 character limit, splits it with a “,” (or more, as needed) at that point and wraps the query in CONCATENATE(). If you look at your query you can see this (the “,” is right after the field ‘period.fiscal-period’.
If you edit the query in the cell, you’ll need to move the “,” that is splitting the query to accommodate your changes, although it doesn’t need to be exactly at the 255 character mark … just so that it splits before that.
A better approach might be using one cell for the query, and a separate cell for the formula that displays the results: =XBRL.showData(A1, “”, “”, “1”) would work for the following query if it was in cell A1 –
If you’re building queries in the task pane, you can use the radio button for ‘query’ just above the Get button – it won’t be concatenated.
FYI, there are no 2Q values in that query, according to the results – let me know if this is incorrect compared with EDGAR and I’ll be sure we research and resolve.
-
Tuesday, March 8, 2022 at 2:11 AM #198462Samuel PunParticipant
Hi David,
Thanks for the quick response and thanks for the tip.
I think the problem is that it doesn’t return a value for some concept names. The example gave was “NetCashProvidedByUsedInOperatingActivities”. I see no value return for 2Q 2021, 3Q 2021 and Y 2020. However, it returns a value using the same formula for 1Q 2021. I’ve also checked Edgar and those values exist. Is it possible to take a look at why it returns no value?
Thanks again,
Sam-
Thursday, March 10, 2022 at 11:50 AM #198520David TaurielloKeymaster
Hi Sam – sorry for the slow reply; we load SEC filings shortly after they’re submitted to the regulator.
The 10-K that’s ‘missing’ from our database does not have any XBRL (it is HTML) -https://www.sec.gov/Archives/edgar/data/0001799332/000149315221007491/form10-k.htm – so we’re unable to load it at this time.
FWIW, it’s not in the SEC’s API, either (https://data.sec.gov/api/xbrl/companyfacts/CIK0001799332.json)
I’ve written to the SEC to find out if this can be fixed and will let you know if I have any updates.
-
-
Saturday, March 12, 2022 at 5:26 AM #198567Samuel PunParticipant
Thanks David. You been super helpful already and much appreciated.
Regading 0001799332 where the 10-K (Y 2020) is in HTML, I noticed that too. What’s odd is that I can retrieve some balance sheet data (e.g. CashAndCashEquivalentsAtCarryingValue, AssetsCurrent, OtherAssetsNoncurrent) which made me think it wasn’t the HTML issue.
Also for AAPL, I would have thought there would be no issues for such popular stock but I had issues retrieving some cash flow data for 2Q 2021 and 3Q 2021 (e.g. NetCashProvidedByUsedInOperatingActivities, PaymentsToAcquirePropertyPlantAndEquipment) even though I can see it in Edgar. Oddly, it’s fine for the 10-K and 1Q 2021.
I hope these observations would help the investigation.
Kind regards,
Sam -
Saturday, March 12, 2022 at 12:11 PM #198587David TaurielloKeymaster
Hi Sam – the balance sheet elements from the GAN 10-K may be the result of these datapoints being reported in a subsequent filing.
On your question about Apple’s 10-Qs, this may have something to do with the queries you’re using (? – share without the https://api.xbrl.us/ portion of the URI).
When I use the Multi Compare template posted on the Data Community page (updated today to include historic FERC reports), both Qs contain the two cash flow elements.
-
Saturday, March 12, 2022 at 4:34 PM #198591
-
Saturday, March 12, 2022 at 5:28 PM #198592Samuel PunParticipant
Ok so I think I’ve cracked it.
For cash flow, it’s shown as cumulative rather than for the 3 month period. Therefore I cannot use 2Q and 3Q as fiscal-period. I have to use the following:
1Q = 1Q
2Q = 1H
3Q = 3QCUM
-
-
AuthorPosts
- You must be logged in to reply to this topic.
Search Forums
Recent Topics
Recent Replies
-
Spreadsheet Commands: Query Worksheet and Query Workbook Query Workbook 2 weeks, 3 days ago
-
Query for multiple dimensions 2 months, 2 weeks ago
-
Query for multiple dimensions 2 months, 2 weeks ago
-
Showdata function 5 months ago
-
Showdata function 5 months, 1 week ago
Documentation & Discussion
- Get started with Google Sheets OR
- Get started with Microsoft Excel OR
- Get access to as-filed data from us for other tools or your own app
- XBRL API Interactive Documentation
- Ten Tips & Techniques
- The XBRL API
- XBRL Data Community
- 2024 US GAAP Taxonomy Viewer
- Live support - Monday, 3:30 - 4:30 PM ET
Who's using this free data?
API Use 2024 || API Use 2023 || API Use 2022 || API Use 2021 || API Use 2020 || API Use 2019 || API Use 2018
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table
Using the XBRL API with the Public Filings Database
Unless otherwise agreed to in writing, any and all use of the XBRL API to authenticate and retrieve data from the XBRL US Database of Public Filings implies user consent and agreement with the XBRL US API Agreement. If you are unable to agree to these terms, do not use the XBRL API.
To use the XBRL API outside of Google Sheets, your account needs to be provisioned for OAuth2 access.