Converting text to values and sorting data

[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.

[Asif Saberi]

Nevermind, I used the excel function VALUE… and it works.

• This reply was modified 2 years, 3 months ago by Asif Saberi.

[David Tauriello]

Asif – On the Excel version of XBRL Filed Data, if you choose the ‘Data’ option above the ‘Get’ button on XBRL Filed Data, you’ll get the values for fields. I believe the Function + Data (default) setting returns query results in a ‘Dynamic Array’ (this is a Microsoft feature).

[Asif Saberi]

Hi David (and others): Working in excel, when I pull several years of a ‘concept’ for one entity, how do I ensure it is sorted in a certain order. Return seems random…Here is my call and its result
=VALUE(XBRL.showData(CONCATENATE(B8,B10,B11,”&period.fiscal-period=Y&concept.local-name=”,$B17,”&fact.ha”,”s-dimensions=false&fact.ultimus=true&fields=fact.value,period.fisc”,”al-year, fact.limit(),fact.sort(), fact.offset()&”),””, “”, “0”))

$20,289,000,000 2017
$38,016,000,000 2020
$25,913,000,000 2018
$48,844,000,000 2019
$34,940,000,000 2021

[David Tauriello]

Hi Asif – you can use .sort(ASC|DESC) on fields that are being returned to design a sort order – see ‘sort’ in the documentation for details – https://xbrlus.github.io/xbrl-api

This functionality exists on the query tool in the side panel of XBRL Filed Data – use the arrow keys on the selected fields (which can also be re-ordered by dragging and dropping them) to sort fields.

[Asif Saberi]

Another question:This pertains to using the entity.ticker in the search phrase-
The following works (the entity code is hard coded – CIK for AAPL):
=XBRL.showData(CONCATENATE(“https://api.xbrl.us/api/v1/fact/search?entity.code=0000320193&period.fiscal-year=2021&period.fiscal-period=Y&concept.local-name=”,$B12,”&fact.ha”,”s-dimensions=false&fact.ultimus=true&fields=fact.value,period.fisc”,”al-year,fact.limit(),fact.offset()&”),””, “”, “0”)

But the following does not work (cell D5 contains the ticker for Apple inc, AAPL)
=XBRL.showData(CONCATENATE(“https://api.xbrl.us/api/v1/fact/search?entity.ticker=”, $D$5,”&period.fiscal-year=2021&period.fiscal-period=Y&concept.local-name=”,$B12,”&fact.ha”,”s-dimensions=false&fact.ultimus=true&fields=fact.value,period.fisc”,”al-year,fact.limit(),fact.offset()&”),””, “”, “0”)

[David Tauriello]

Hi Asif – apologies for the slow reply; the entity.ticker parameter is not available as a filter for queries on the fact endpoint. You should see the following message in your query:

The following search parameter is not valid: entity.ticker

You’ll need to convert use another value (entity.id, entity.cik or entity.code) to return values for the entity.

[Author]

Posts