Concept name & facts works for some reports but not others

[Samuel Pun]

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

[David Tauriello]

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 –

https://api.xbrl.us/api/v1/fact/search?entity.cik=0001799332&period.fiscal-year=2021,2020&period.fiscal-period=1Q,2Q,3QCUM,Y&concept.local-name=NetCashProvidedByUsedInOperatingActivities&fact.has-dimensions=false&fields=concept.local-name,fact.value,period.fiscal-period,period.fiscal-year,fact.limit(),fact.offset()

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.

[Samuel Pun]

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

[David Tauriello]

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.

[Samuel Pun]

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

[David Tauriello]

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.

[Samuel Pun]

Hi David,

the URL is:

https://api.xbrl.us/api/v1/fact/search?entity.cik=0000320193&period.fiscal-year=2021&period.fiscal-period=3Q&concept.local-name=NetCashProvidedByUsedInOperatingActivities&fact.has-dimensions=false&fields=fact.value,fact.limit(1)

Kind regards,
Sam

[Samuel Pun]

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

[Author]

Posts