[Ashutosh Pandey]

Data is not being loaded even after all the troubleshoots. Though CIK gets loaded for selected ticker.

[david.tauriello@xbrl.us]

Hi Ashutosh – on the Main Sheet of the XBRL Spreadsheet, what do you see when you change the query dropdown (cell B2) to a different query?

Have you tried removing the add-on from your Google account (Add-ons >> Manage Add-ons) and then re-installing it to your Google account – ? If not, please try that and post a reply if you continue to have an issue (and please share the specific text that’s displayed where data should appear on Main Sheet (cell A11)

[Ji Yu]

Where can we get new keys if we want to continue using the data via a key and direct queries to the database?

[david.tauriello@xbrl.us]

Thanks for your interest in using data from the XBRL US Database of Public Filings. Anyone can use the XBRL API to return up to 1,000 records per query from the repository. To use the XBRL API in your own application for private, non-commercial research and development, send a request to have your XBRL US Web Account provisioned to info@xbrl.us.

Power User and Sole Practitioner Individual Members, as well as all Organizational Members, have access to all available data via the API, as well as direct queries to the Database. See the Member Benefits link under “Join XBRL US” at right to learn more and find out about the option that’s right for you and/or your team.

[Ashutosh Pandey]

It Displays on A11 cell of main sheet as well as on A24 of BSCompare Sheet “No information returned”.

I have tried removing and reinstalling the add on also but did not work.

As I mentioned in the earlier post; when I change the ticker in the green cell of BSCompare Sheet the CIK also changes but other than that nothing happens.

[david.tauriello@xbrl.us]

On the XBRL API Access add-on’s Authentication menu that appears on the side of the Google Sheet, do both the ‘Authorize the API’ and ‘Logout’ links appear to work properly?

After clicking the ‘Authorize the API’ link, have you tried both login options (XBRL US Web Account / Continue with Google) with the same result?

If you’re able to use Chrome web browser, do you get the same result (no information returned)?

If you have downloaded multiple copies of the XBRL API Spreadsheet, please delete all but one of them, or at least move them to a folder in your Google Drive account.

The ticker uses a different call, so it will function regardless of the status of the XBRL API Access add-on.

[david.tauriello@xbrl.us]

Like the queries shared in the config tab of the initial Google Sheet, the orange-shaded cells on each of the templates we’ve posted are queries that return as-filed data for companies reporting to the SEC. These queries can be copied out pasted to another sheet, or modified (generally with the green variable cells, as with the ticker cell) to return other data.

For example, if you put the ticker IBM in your copy of the Balance Sheet Compare – SEC Filers Google Sheet (contributed by Peter Guldberg), then scroll down to the corresponding filing detail for the company (row 92 or below), columns F and K have queries that return data used in the presentation at the top of the spreadsheet.

Further, if you copy the corresponding F and K cells from your copy of the Balance Sheet Compare – SEC Filers to another worksheet and ‘paste special’ >> ‘paste values only’ between the quotes of the function =showData(“insert query here”) the query will run again and the data will appear. Reminder – at this point, the query is no longer tied to Peter’s TaxonomyMap – this sheet does the heavy lift of analyzing each report to find a matching element in the filing from several choices, then builds the query that was copied out based on what’s discovered. As a result, another company might not return facts for these same elements.

The API takes a ‘plain-language’ approach to the granularity that is the hallmark of XBRL. In the IBM example, the query (URL) is like a sentence: “For the facts from report A and in the year B and for quarter C and without dimensions and named D return fields sorted by name showing the value, decimals, fiscal year, period and unit for each. The documentation list parameters that can be applied to filter data and fields that can be returned.

Additionally, recognize that Peter ‘daisy-chained’ results from a prior query of reports to identify the report.id of the most recent one filed for the ticker. On the API, report.id and dts.id are unique identifiers for filings.

[Jim Truscott]

Sorry Campbell needed to go off and do something else. Back on it now.

The fact.ultimus sounds fabulous, a boolean flag to filter in only the most up to date version of a value. I assume it hasn’t quite been implemented yet as its returning the same values as the fact.ultimus-index? And from the values I’ve looked at – examples below – an index of 1 would be the original or first reported value and does an index of 5 mean that, in its reporting life time, it has had five unique values or its just been reported 5 times (been in 5 filings of whatever type)?

{
“concept.local-name”: “Assets”,
“fact.value”: “241086000000”,
“fact.has-dimensions”: false,
“report.id”: 191866,
“period.instant”: “2017-07-01”,
“report.period-end”: “2017-06-30”,
“entity.name”: “MICROSOFT CORPORATION”,
“report.type”: “10-K”,
“period.calendar-period”: “2Q”,
“period.fiscal-period”: “Y”,
“period.year”: 2017,
“period.fiscal-year”: 2017,
“report.restated-index”: 1,
“report.restated”: 1,
“fact.ultimus-index”: 5,
“fact.ultimus”: 5
},
{
“concept.local-name”: “Assets”,
“fact.value”: “250312000000”,
“fact.has-dimensions”: false,
“report.id”: 218986,
“period.instant”: “2017-07-01”,
“report.period-end”: “2018-06-30”,
“entity.name”: “MICROSOFT CORPORATION”,
“report.type”: “10-K”,
“period.calendar-period”: “2Q”,
“period.fiscal-period”: “Y”,
“period.year”: 2017,
“period.fiscal-year”: 2017,
“report.restated-index”: 1,
“report.restated”: 1,
“fact.ultimus-index”: 1,
“fact.ultimus”: 1
},

[Ashutosh Pandey]

I am able to fetch the data by using google authentication. Thank you.

[Jim Truscott]

does an index of 5 mean that, in its reporting life time, it has had five unique values or its just been reported 5 times (been in 5 filings of whatever type)?

Having read some other posts, I think I found one of the answers in your third reply, Campbell to this post reports and periods

…Maybe it would be helpful to add a flag to a fact to indicate that it is the latest value reported. This would update like the ultimus but would allow you to get the latest value of assets etc reported. (Wheras ultimus indicates if the same value has been reported subsequently…

Having a pulled back a bunch of values, this appears to be consistent with what I’m seeing.

[Jim Truscott]

/api/v1/fact/search?concept.local-name=Assets&fact.has-dimensions=false&report.type=10-Q&period.fiscal-period=Y&entity.cik=0000789019&fields=concept.local-name,fact.value,fact.has-dimensions,report.id,period.instant.sort(DESC),report.period-end,entity.name,report.type,period.calendar-period,period.fiscal-period,period.year,period.fiscal-year,report.restated-index,report.restated,fact.ultimus-index,fact.ultimus

API call returns values such as…

{
“concept.local-name”: “Assets”,
“fact.value”: “256003000000”,
“fact.has-dimensions”: false,
“report.id”: 204168,
“period.instant”: “2018-01-01”,
“report.period-end”: “2017-12-31”,
“entity.name”: “MICROSOFT CORPORATION”,
“report.type”: “10-Q”,
“period.calendar-period”: “Y”,
“period.fiscal-period”: “2Q”,
“period.year”: 2017,
“period.fiscal-year”: 2018,
“report.restated-index”: 1,
“report.restated”: 1,
“fact.ultimus-index”: 1,
“fact.ultimus”: 1
},

But I thought I’d searched only on period.fiscal-period=Y values. And if I change report.type to 10-K, I get no values that have a period.fiscal-period=Y yet if I remove period.fiscal-period parameter altogether and just search on 10-K values, I get values back that have period.fiscal-period=Y. What am I missing?…

{
“concept.local-name”: “Assets”,
“fact.value”: “258848000000”,
“fact.has-dimensions”: false,
“report.id”: 218986,
“period.instant”: “2018-07-01”,
“report.period-end”: “2018-06-30”,
“entity.name”: “MICROSOFT CORPORATION”,
“report.type”: “10-K”,
“period.calendar-period”: “2Q”,
“period.fiscal-period”: “Y”,
“period.year”: 2018,
“period.fiscal-year”: 2018,
“report.restated-index”: 1,
“report.restated”: 1,
“fact.ultimus-index”: 2,
“fact.ultimus”: 2
},

[david.tauriello@xbrl.us]

Hi Jim – please double check what’s returned from the initial query; I get period.calendar-period = 2Q
and period.fiscal-period = y
for report.id = 204168
and other records returned for this 6/30 year end filer.

Check out this thread for more details about calendar and fiscal periods – I believe the exchange influenced the work of a ‘lookup’ query that notes period and year on Peter’s Balance Sheet Compare – SEC Filers template.

[david.tauriello@xbrl.us]

Jim – you’re correct: fact.ultimus-index is the number of times the fact has been reported, regardless of value, report type, etc. In the index, 1 is the most recent, and the highest reported ultimus integer corresponds to the earliest reporting of the fact.

See Microsoft’s Assets for fiscal year 2015: /fact/search?concept.local-name=Assets&fact.has-dimensions=false&entity.cik=0000789019&period.fiscal-year=2015&period.fiscal-period=Y&fields=concept.local-name,fact.value,report.id,report.type,period.fiscal-year,report.filing-date.sort(DESC),report.restated-index,fact.ultimus-index

Like report.restated, fact.ultimus is planned to become boolean.

[Jim Truscott]

Yep. I searched on period.fiscal-period=Y but double checking using a copied version of the call posted at the start of this post, I get 9 records back in insomnia including records with “period.fiscal-period”: “2Q”.

I’m wondering whether it has anything to do with the fact that asset facts have instant dates so technically speaking there is no period associated with an asset fact.

Yep familiar with the issues around calendar and fiscal years. Similarly we bypass whatever the filing entity tells us and calculate our own consistently comparable fiscal year and quarter dates.

[david.tauriello@xbrl.us]

hmm – with the query posted above, I get 28 records and none have fiscal period 2Q – can you post report.id for one or more records where this is happening – ?

the details you want returned in fields (like period.instant) shouldn’t impact the screen you’ve set – I’ll confirm and clarify if my understanding is not correct.

Also, please check to make sure site_url in insomina is pointed to api.xbrl.us and not betaapi.xbrl.us

[Author]

Posts