[Samuel Pun]

Hi David,
I’m using Excel. The auto generated formula is, for example, =XBRL.ShowData(“https://api.xbrl.us/api/v1/entity/search?entity.cik=0001799332&fields=entity.name,entity.limit(),entity.offset()&”,””, “”, “1”)

no issue with number of characters as it’s <255.

i think Excel is not recognising =XBRL.ShowData or any add-in functions.

Werid thing is that I can request data but just not using the functions.

Not sure if it’s because I’m using Excel 2019?

Regards,
Sam

[david.tauriello@xbrl.us]

Hi Sam – thanks for the additional detail and yes, your version of Excel is not compatible with the add-in – I’ve written to Microsoft to see why the add-in is available to users who cannot use it.

The query you posted will work in Excel for Office 365. If you create a free account with Microsoft – https://www.office.com/launch/excel – you can install the add-in and use it with the web version of Excel.

[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,&#8221;,”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

[Chris Martinez]

Hi David,
I used the “XBRL API Classroom Starter”worksheet as my base. I “ran” the Facts for 2018 from Apple 10-K query and it worked.

I then amended one command from the above query; changed 2018 to 2019. I copied the amended commands and placed them on a new Google Sheet. Also ensured that I was authorized to access the API.

The new Google sheet did not function.

The commands that I used are set out below.

Any suggestions?

Thanks,
Chris

https://api.xbrl.us/api/v1/fact/search?dts.id=306447&period.fiscal-year=2019&fields=concept.local-name.sort(ASC),fact.decimals,fact.value,unit,entity.name,entity.cik,repot.filing-date,report.id,entity.id,fact.ultimus-index,fact.id,dimensions,dimensions.count,period.fiscal-year.sort(DESC),period.fiscal-period.sort(DESC)

[david.tauriello@xbrl.us]

Hi Chris – thanks for writing; the query needs to be wrapped quotes and be in the first postition in the =showData() function in order to work.

paste this over the top of the cell where you put the query:

=showData(“https://api.xbrl.us/api/v1/fact/search?dts.id=306447&period.fiscal-year=2019&fields=concept.local-name.sort(ASC),fact.decimals,fact.value,unit,entity.name,entity.cik,repot.filing-date,report.id,entity.id,fact.ultimus-index,fact.id,dimensions,dimensions.count,period.fiscal-year.sort(DESC),period.fiscal-period.sort(DESC)“)

You should get two rows – a header and the data for 2019:

concept.local-name fact.decimals fact.value unit entity.name entity.cik report.id entity.id fact.ultimus-index fact.id dimensions dimensions.count period.fiscal-year period.fiscal-period
EntityCommonStockSharesOutstanding -3 4745398000 shares APPLE INC 0000320193 226128 5927 1 188336575 null 0 2019 2018-10-27

[david.tauriello@xbrl.us]

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.

[Chris Martinez]

Thanks, David

[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@xbrl.us]

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@xbrl.us]

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

[John Summersgill]

Hi, I’m trying to qenerate a query to access 3 known concepts across 8 known entities. The issue I’m having is those concepts sometimes having multiple dimensions levels (not of proper nomenclature).

As an example I’m query entity CIK ‘0000896159’ for concept ‘PremiumsEarnedNet’. I have parameter to local only for member.local-name ‘SegmentGlobalReinsuranceMember’. The issue is that there is another dimension nested underneath that gets pulled into the query as well. What’s the best way to filter out the second dimension level? Below are the two dimensions returned. I only want the first one.

[{‘dimension_namespace’: ‘http://fasb.org/us-gaap/2021-01-31&#8217;, ‘dimension_local_name’: ‘StatementBusinessSegmentsAxis’, ‘member_namespace’: ‘http://investors.chubb.com/20211231&#8217;, ‘member_local_name’: ‘SegmentGlobalReinsuranceMember’}]

[{‘dimension_namespace’: ‘http://fasb.org/us-gaap/2021-01-31&#8217;, ‘dimension_local_name’: ‘StatementBusinessSegmentsAxis’, ‘member_namespace’: ‘http://investors.chubb.com/20211231&#8217;, ‘member_local_name’: ‘SegmentGlobalReinsuranceMember’},

{‘dimension_namespace’: ‘http://fasb.org/us-gaap/2021-01-31&#8217;, ‘dimension_local_name’: ‘SubsegmentsAxis’, ‘member_namespace’: ‘http://investors.chubb.com/20211231&#8217;, ‘member_local_name’: ‘PropertyandothershorttailMember’}]

[david.tauriello@xbrl.us]

Hi John – thanks for the email follow-up (our system moderates any posts with URLs, and the queue is checked a couple times a week).

I think you’re on the right track filtering with dimensions.count combined with the specific member.local-name (note that dimensions.count is plural – https://xbrlus.github.io/xbrl-api).

This seems to be headed in the right direction:

/fact/search?entity.cik=0000896159&concept.local-name=PremiumsEarnedNet&period.fiscal-period=Y&fact.ultimus=true&fields=entity.name,period.fiscal-year.sort(DESC),fact.numerical-value,member.local-name,dimensions&member.local-name=SegmentGlobalReinsuranceMember&dimensions.count=1

Reply on the thread if you have additional questions about this or if it’s not quite right.

[Author]

Posts