[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, 8 months ago by Bruno Lerer.
• This topic was modified 2 years, 8 months ago by david.tauriello@xbrl.us.

[david.tauriello@xbrl.us]

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

From a data user: We are trying to get net income for publicly traded companies but the following stocks are missing info through XBRL even through they are tagged by the SEC. Can you let me know when you guys can get this done? My developers are waiting on this data to complete part of a project.

I’m attaching the list for the stocks that are missing net income information even though they have been tagged in the SEC docs.

Can’t post this on the forum as these are attachments.

Thanks for your assistance.

Tanvir.

[david.tauriello@xbrl.us]

Hi Tanvir – We copy each XBRL filing’s details in EDGAR to our database from the .zip file a company submits to the SEC – while we’ve missed entire filings in the past (and I’m not aware of any missing at this point), I’ve never seen us drop a fact within a filing(?).

Can you provide a specific report as an example, instead of files full of tickers?

Also, without an XBRL US Membership, an account won’t be able to return all possible rows and this may be the issue.

[Nik Z]

Hi David,

I am one of the developers on this team. This question is regarding reports not having any data available searchable with XBRL when it looks like they should.

One example is 1-800-Flowers most recent 10-K. Where I receive the error “Inline XBRL viewing features are minimal because no supporting file was found”.

I believe there should be XBRL data as there appears to be XBRL files on the Filing Detail Page. I also noticed “us-gaap” tags in the documents which is initially why I thought it would be possible for these cases.

Note that in these cases I am still able to request the report, just not any facts within it. Any clarification on why this is the case or assistance in resolving it would help us out greatly.

Thank you,
Nik

[david.tauriello@xbrl.us]

Hi Nik – thanks for sharing detail. The 1-800-Flowers 10-K you’re looking at is not an inline XBRL file, so it will not render in the SEC’s inline viewer (the ix?doc= part of the URL).

If you return 1-800-Flowers reports with this query, you’ll see a field called report.entry-type – this distinguishes inline filings from older instance.xml files.

https://api.xbrl.us/api/v1/report/search?entity.id=1085&fields=dts.id,report.filing-date.sort(DESC),report.entry-type,report.document-type,report.entity-name,report.sec-url

Here’s a recent 1-800-Flowers Filing Detail Page for an inline report – compare it to your link for the 10-K

[Peter Miller]

Hello guys,

I am a Python enthusiast and was trying in the past to clean XBRL data from the official SEC database. What is the current state? I can’t follow the SQL code on Tim’s GitHub. I would appreciate a short explanation.

Kind Regards

[Tim Bui]

Could you elaborate what you are looking for or trying do to Peter?

[Peter Miller]

Thank you, Tim, for your replay.

I am trying to clean/standardize the financial statements available on the SEC website. To my knowledge, these financial statements are in XBRL format and as the original question pointed out (to my understanding), the financial statements are not standardized and for several data points there are different key words (Revenues;SalesRevenueNet and so on).

Have you guys found a way to standardize the data (with Python) to make it more useful?

[Tim Bui]

Peter, I use 2 steps to do standardization, but I am no where close to the end.
1. I use the US GAAP Classified Financial Statements created by Mr. Charles Hoffman (The Father of XBRL) to create standardized buckets.
2. I get all of the tags from the SEC Financial Statement and Notes Data Sets and manually group the extensions (non GAAP standards) into the above buckets.
XBRL US reply feature does not seems to allow me to paste the website URL, but you can send me a message on Linkedin and I can send you more info.

[Peter W Reed]

There are a couple of contributors like Peter Guldberg who have accounting concept to GAAP name mapping. What I’m creating is accounting concept to GAAP name on a per company basis for their last five years of filings. I’ve found that concepts like revenue can have a different GAAP tag name over time for any given company. This entails creating a concept mapping to multiple GAAP tags. The link label file in the Edgar filing provides all the concept-to-gaap mappings for that company’s 10-Q/K.

[Peter Miller]

Hello Peter,

< The link label file in the Edgar filing provides all the concept-to-gaap mappings for that company’s 10-Q/K.>

where can i find the “link label file” on the Edgar website?

Kind Regards

[Tim Bui]

Peter, Every book has an ISBN number, but financial tags are in text form. Not having numbers and the names of tags changing over time and not comparable across companies making standardization very difficult–at least for people who lack programming skills like me.

Peter Gulberg is great. Another person who might be able to help you is Jim Truscott (xbrlxl.com). Both Peter Gulberg and Jim Truscott helped me a lot on standardization. I recommend you to check out Jim’s XBRLXL website on this topic.

On standardization, the issue for me is how detail one wants to get. For example, one might look at Total Account Receivables, but I want to break AR further down to AccRec-Trade (from customers), Acc_Rec_NonTrade (from other sources such as tax refund), Acc_Rec_Finance_Oper.

[Peter Miller]

Hi Maththew,

may I ask what is your current state of the Python package? Did you upload it on GitHub?

Kind Regards

[Peter W Reed]

I’m using the “XBRL TAXONOMY EXTENSION LABEL LINKBASE DOCUMENT” file that is part of the Edgar filing. It has the mapping from gaap tag to common name. The following comes from an Apple 10-Q filing. I use the Python module xmlschema to create a dictionary. The real work comes later cleaning the results for usability and extending this back five years.

us-gaap_SalesRevenueNet_lbl = Revenue, Net
us-gaap_SalesRevenueNet_lbl = Net sales
us-gaap_Liabilities_lbl = Liabilities
us-gaap_Liabilities_lbl = Total liabilities
us-gaap_LiabilitiesAndStockholdersEquity_lbl = Liabilities and Equity
us-gaap_LiabilitiesAndStockholdersEquity_lbl = Total liabilities and shareholders’ equity
us-gaap_LiabilitiesAndStockholdersEquityAbstract_lbl = LIABILITIES AND SHAREHOLDERS’ EQUITY:
us-gaap_LiabilitiesCurrent_lbl = Liabilities, Current
us-gaap_LiabilitiesCurrent_lbl = Total current liabilities

[Author]

Posts