Using fact.accuracy-index

[Bruno Lerer]

I’ve been playing with the new add in in conjunction with XBRL Statements and Disclosures template and ran into several issues. My sense is that these are mostly, though not exclusively, API issues but I’m not sure.

I don’t know if I should ask about these issues in one question or split them into separate questions; so I’ll only pose a few in here, and if I should indeed split these, please let me know.

First, CTRL-Z doesn’t seem to work, at least not in my version (most recent Excel 365 for Business). If a change is made in the XBRL.showData() cell, once ENTER is pressed, I have to close and reopen the workbook – so I have to turn off auto-save.

Second, multiple duplicates come up, even if fact.accuracy-indexis set to 1. Random example:
=XBRL.showData(CONCATENATE("https://api.xbrl.us/api/v1/fact/search?entity.cik=0000877890&period.fiscal-year=2020&period.fiscal-period=Y&fact.accuracy-index=1&concept.local-name=Assets&fields=concept.local-name,entity.name,fact.accuracy-index,fact.value,period.fiscal-year,fa","ct.limit(),fact.offset()&"),"", "", "1")

results in

concept.local-name entity.name fact.accuracy-index fact.value period.fiscal-year
Assets CITRIX SYSTEMS, INC. 1 4890347000 2020
Assets CITRIX SYSTEMS, INC. 1 4890347000 2020
Assets CITRIX SYSTEMS, INC. 1 4890347000 2020

Third – no Liabilities? “Assets” as a concept.local-name value seem to be doing fine; but for some strange reason, in many reports “Liabilities” doesn’t show up. If you try the previous search, with “Liabilities” instead of “Assets” (even if add fiscal years) nothing comes up.

Lastly, for the time being, unrecognized dts.ids. The templates suggests the user “get dts.id values from the Select Report by Entity Name field on the fact function dropdown of the XBRL Filed Data add-in”. The dts.id value for alphabet (456521, which comes in the downloaded version of the template) indeed works fine. However, if you go to the add in as suggested, get the dts.id value for the same company for for its latest 10-K, copy the resulting value (437526) and paste it in Cell F6 of the template, the search fails. It does work, however, if you paste the dts.id for the previous 10-K (365284). It happened with picked-up dts.id values from other companies as well. Incidentally, I believe it would be a very good idea to be able to filter the resulting drop down list of the fact function by (at least) filing type.

Sorry for the long posting.

• This topic was modified 2 years, 11 months ago by Bruno Lerer.
• This topic was modified 2 years, 11 months ago by David Tauriello.

[David Tauriello]

Hi Bruno – thanks for writing; I’ve updated the topic title so it focuses on fact.accuracy-index. The other issues you’ve noted are addressed at the bottom of this post.

fact.accuracy-index is a filter at the report level (see slide 9 – 11 https://xbrl.us/wp-content/uploads/2021/07/xbrl-api-webinar-210811.pdf). If you add report.filing-date you’ll see the same fact has been included in three different reports.

Use fact.ultimus-index to see a fact’s history in all reports through time (1 is most-recent). The most-recent reporting of a fact can also be returned by using fact-ultimus=true in the query.

It sounds like your undo issue is tied to our queries and returning external data to the application; I think this disables (clears?) undo, since the data coming back quickly would flood the undo memory space. Try it with Excel on the web and you’ll see a constant ‘saving’ message. Try returning just ‘data’ or ‘query’ with the radio buttons in the task pane above the ‘Get’ button, you can use undo.

I could not replicate the dts.id issue (437526 is Citrix not Alphabet); maybe your connection to the database was lost momentarily (?).

If it happens again, try using the reload option for the add-in:

• there’s a ‘<' button that appears near the top of the task pane if you click your cursor down in the task pane ... click the <
• choose reload,
• click the ‘Query Worksheet’ button on the XBRL Filed Data ribbon.

Thanks for the filtering suggestion for reports – we’ll look to incorporate this on the report dropdown.

[David Tauriello]

Hi Bruno – I wanted to follow up on this thread, as I don’t think I responded full to your questions about element values not appearing.

Missing facts in a report might be due to an account limitation. Non-member accounts return a maximum of 1,000 facts, 100 facts at a time (fact.offset is used in the fields part of the query to get past 100 … set it to 200 and it will return facts 200 – 300 for the query).

So, if you changed the green cells limiting the report section so it returns the full report, you will only get 100 facts.

On the issue of a specific dts.id not returning data, it may be that the report name changed between filings, so if you looked for ‘Balance Sheet’ in one period and in the next it’s gone, the report may have been renamed to ‘Statement of Operations’ (this would be a very extreme circumstance)

We posted a report compare template recently (copying the first columns a couple more times) – https://xbrl.us/wp-content/uploads/2021/08/tri-compare-reports.zip – maybe using this template helps your searching.

[Bruno Lerer]

Hi, David and thanks for your response.

I downloaded and played with the report compare template and it is indeed much better and easier to work with.

To try it out, I used dts.ids from smaller filers (as opposed to the usual suspects like Apple, Microsoft, etc.). Interestingly enough, once you move into that territory, dts.ids frequently (I would say 9 out of 10) result in no response, for some reason. And as I mentioned previously, of the 3 random reports I managed to get balance sheets for, one had Total Assets and Total Liabilities, the second had Assets and Liabilities and the third had Assets but only Liabilities, Current. That would definitely make concept searches a little difficult…

One initial comment would be to repeat a point from my previous post, which seems more important after testing the template.

As before, one needs to “get dts.id values from the Select Report by Entity Name field on the fact function dropdown of the XBRL Filed Data add-in”. If what you are after is the balance sheet or income statement, you are probably looking for either 10-Ks or 10-Qs. The drop down list generated by entering something like “natio” in the Entity Name field can have dozens (sometimes more than a hundred, it seems) entries, most of which being 8-Ks and their ilk. It would be really, really great to somehow be able to filter the drop down list to include only 10-Ks (or 10-Qs) so as to make it easier for the user to drill down to a particular report of a specific entity.

I’ll continue to work with the template and will be happy to report anything interesting I run into.

[David Tauriello]

Bruno – I was thinking about the ’empty report’ issue you mentioned here again:

“To try it out, I used dts.ids from smaller filers (as opposed to the usual suspects like Apple, Microsoft, etc.). Interestingly enough, once you move into that territory, dts.ids frequently (I would say 9 out of 10) result in no response, for some reason.”

Next time you get a ‘blank’ result on the comparison template:

1. clear the green cells to the right of the one that says “-statement” and you should see some data from the report (these cells filter the data from the selected dts.id).
2. use the updated details in the blue cell to identify a specific report,
3. enter that text (or number) in the cell to the right of “-statement” you just cleared, and you should see details for that section of the report.

[Bruno Lerer]

Hi, David. I actually was about to respond to your previous message when I saw this one. But I stopped and followed your instructions – and let me tell you: I don’t know what to think anymore…

FYI – to make sure it wasn’t a problem with my own setup, I replicated the whole thing on another computer (same Windows/Excel 365 versions) – with more or less same results. To confuse things more, when I read this new reply, I downloaded yet another, virgin, copy of the compare workbook and tried everything there – now I’m getting different results in each version of the spreadsheet…

Case in point, dts.id 492480: if I plug it in cell W3 (or its siblings G3 and AO3) of the previous version of the sheet, the output in cell W4 is: “SYNAPTICS INCORPORATED 10-K, filed 2021-08-23:”. Doing the exact same thing in the same places in the new, unsullied version of the sheet outputs only ” , filed: “. This outcome obtain regardless of whether or not I “clear the green cells to the right of the one that says “-statement”.

Similar output results from using:
496106 “Austerlitz Acquisition Corporation I S-4/A, filed 2021-09-10:”
477076 “Sonoma Pharmaceuticals, Inc. 10-K, filed 2021-07-14:”
485391 Magnachip Semiconductor Corporation 10-Q, filed 2021-08-06:

To make things even more interesting (if that’s the right term), if I open a new worksheet in the same workbook, and enter
=XBRL.showData(CONCATENATE("https://api.xbrl.us/api/v1/fact/search?dts.id=492480&period.fiscal-period=Y&concept.local-name=Assets&fact.has-dimensions=false&fields=concept.local-name,fact.value,period.fiscal-period,dts.id,period.fiscal-year,fact",".limit(),fact.offset()&"),"", "", "1")
the output isn’t empty, it’s:
`concept.local-name fact.value period.fiscal-period dts.id period.fiscal-year
Assets 2226800000 Y 492480 2021
Assets 1693800000 Y 492480 2020

Same with the other dts.ids above. And, btw, if I do the same thing in the newly downloaded version of the compare workbook (with the other workbook being closed at the time), the output is “No information returned”.

So, as I said, don’t know what to think anymore…

While I’m at it, can please expand (maybe in a different answer) on the use cells H3 and and J3 (and their siblings)? You mentioned in another answer something about updates to the the relationship query – I suspect these are related but I can’t find any reference that explains how they all works. Specifically, If I’m after a term that appears in both a balance sheet and income statement, it would be useful to be able to limit the search results to one or the other.

Thanks again and sorry for the long post (again).

• This reply was modified 2 years, 10 months ago by Bruno Lerer.

[David Tauriello]

Hi Bruno – Thanks for the dts.id values. I was unable to replicate the issues you describe with a non-Member test account, so I sent you an appointment to do a quick screen share (I’m guessing something I’ve written about how to use the green cells to filter the report has gotten lost in translation). If the time doesn’t work, please propose a couple of alternatives.

[Bruno Lerer]

David,
Thanks again for the illuminating mini-tutorial!
For those following – turns out that clearing the offending green cells did solve the problem.

Next steps – get the most recent version of the template and pay much more attention to the relationship query…

[David Tauriello]

Bruno – thanks for your reply. If you have or find a dts.id for an ’empty’ financial report (10-K or 10-Q) please post it here or email info@xbrl.us. We create a dts.id from the files shared to SEC that are copied to our database; we’ve missed reports at the SEC before (I’m not aware of any missing now), but I’ve never seen a report in our repository that didn’t have the underlying data.

The sections of reports that are displayed by the template are based on how the company has created the report, according to requirements established by the SEC, which allows for latitude regarding element selection (or creation) and element labels. Generally, companies can use any label they like for facts, and this may be the variance you are describing.

In the report comparison Excel template, ungroup the left side of the report to see the relationship query, which was updated again this week to use the ‘preferred label’ for report facts (ensuring the front side text from the report is what’s displayed in the template). The concept.local-name (which you can see by unhiding columns to the right of any of the example reports in the template) is ultimately what can be used to confirm/compare facts. We’re using preferred labels in this view to help users understand that the metadata for facts is a facet of XBRL that gives users control of information display.

Appreciate your interest in a filter for the report listing – this is a bit complex, as it requires a filtering option on the Entity Name. If you know the exact name, keep typing … eventually, you’ll get reports for ‘National Steel Co’ (or whatever ‘natio’ you need) and the list will be shorter.

[Author]

Posts