Home Forums The XBRL API Concept name & facts works for some reports but not others

Viewing 6 reply threads
  • Author
    • #198405
      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!

    • #198449
      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 –


      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.

    • #198462
      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,

      • #198520
        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.

    • #198567
      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,

    • #198587
      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.

      Apple cash flow comparison

    • #198591
      Samuel Pun
    • #198592
      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

Viewing 6 reply threads
  • You must be logged in to reply to this topic.


Upcoming XBRL US Events

Center for Data Quality Committee Meeting
Wednesday, June 29, 2022

Domain Steering Committee Meeting
Tuesday, July 19, 2022

Communications & Services Steering Committee Meeting
Tuesday, July 19, 2022

Dive into Debt Securities – Illustrations of the FASB Implementation Guide
Wednesday, July 20, 2022

Public Review: Annual Comprehensive Financial Report Taxonomy
Monday, August 15, 2022