[Rogelio Paulino]

Hi David, I still can’t access the XBRL API using the Excel add-ins. I checked my credentials using the online script. It says: ”There was a problem generating an access token with these credentials”. I already tried resolving this by using the new password and then the newly generated client ID and secret ID. Still unsolved.

[david.tauriello@xbrl.us]

Hi Pau – thanks for writing. Please login at https://xbrl.us/access-token and create a new Client ID and secret pair. Use these new strings when you authenticate with email, password, client ID and secret in the Excel add-in or scripts.

[david.tauriello@xbrl.us]

An XBRL API user writes: I am having an issue though getting a complete list of SIC codes in the “or Get Facts By SIC Code (SEC List)” field. I ran a query for a company via entity name then tried to search for similar companies in the same SIC but it doesn’t show up. I only see a list of 10 SICs. Any suggestions on how to search for all companies in SIC 3841?

Thanks for writing – the SIC field supports any 4-digit code that a filer has disclosed on the submitted report. Add any 4-digit SIC to the search field by clicking the + to show a text box, entering the code, then clicking the + that appears to the right of the text box (throughout the add-in, the text box is multi-purpose – it searches existing options and adds new ones).

Most of the search parameters in the XBRL API support multiple attributes – these are separated by commas (report.sic-code=3841,3571) and function as “OR” options.

Also – nearly any attribute listed in the ‘Fields to Return’ is searchable. We kept the default options to a minimum for simplicity and added the ‘Additional Parameters’ field on the task pane to support deeper queries.

For example, a filter like concept.is-base=FALSE added to the the box for a fact query will return only extension concepts (not in base taxonomies – US GAAP, IFRS, FERC eForms, etc.).

Additional Parameters can be combined using the & character (concept.is-base=FALSE&concept.is-monetary=TRUE returns extension concepts that are monetary).

[Aaron Stout]

Very helpful. Thank you!

[david.tauriello@xbrl.us]

A Member writes:

I’m trying to understand why a query using fact.ultimus=true reports different results when I add the unique attribute (checkbox on XBRL Filed Data spreadsheet extension, or &unique directly in the XBRL API query). Here’s the query without the &unique or &fact.ultimus=true attributes:

https://api.xbrl.us/api/v1/fact/search?entity.code=0000940944&period.fiscal-year=2022&period.fiscal-period=Y&concept.local-name=NetIncomeLoss&fact.has-dimensions=false&fields=concept.local-name,fact.value,period.fiscal-period,period.fiscal-year,report.document-type,report.id

1. The unique filter qualifies the request as “if this fact appears more than once in the results, just show it one time”.

   The phrase in the results is an essential characteristic of unique, which parses each row and drops all matches to leave a single case. If you add fact.id to the fields displayed these values will be different for each and every fact, making the result set similar to the ALL results query. Note: unique will not drop instances where the spelling of a company name has changed across reports (eg. Nathan’s Famous Inc and Nathan’s Famous Inc. are two different spellings).

2. The fact.accuracy-index=1 filter operates on the report level to show the first instance of a fact meeting the query conditions.

3. The fact.ultimus=true filter says “show me the latest facts for this query” but does not respect the report (it evaluates all reports), so if the query excludes reports like DEF-14A because you’re looking only at 10-Ks, the results might not include the latest value.

The view below summarizes these distinctions. The left side of the worksheet applies unique and ultimus with unique. In the latter case, the DEF-14A report matches the query.

On the right side, additional display fields for ultimus and accuracy-index help illustrate why accuracy-index would be a better choice to get results if the query was restricted to 10-K reports.

[david.tauriello@xbrl.us]

An XBRL API user writes:

I wanted to try to build a template that pulled historical financial statements once I entered a ticker symbol. How easy or hard is that to do…?

Here’s a query that uses the report endpoint to with stock ticker values to return date/time accepted, entity name, document type and report.id for General Motors: https://api.xbrl.us/api/v1/report/search?entity.ticker=GM&fields=report.accepted-timestamp.sort(DESC),report.entity-name,report.document-type,report.id. From this point, it’s possible to use any report.id in subsequent queries for XBRL facts stored in our Public Filings Database or EDGAR entries.

In terms of a template, this is a possible variation to how our ESEF/SEC template starts – creating a ticker-based lookup instead of the entity name lookup that uses the add-in taskpane.

[Jeremie Hicks]

i got it linked up with google sheets, and now i just want to pull a company annual revenue. is that to simple?

[david.tauriello@xbrl.us]

Hi Jeremie – after you login and re-open the the spreadsheet extension, there are a number of filters to guide this search. On the fact function, you’ll want to use the ‘Get Facts by Entity Name’ for the company (ie. type and select Adobe Inc. when it appears), remove all but the 10-K value in the Filter by Document Type field and add the fiscal year 2022 and period Y (for year) in the corresponding form fields for the search.

Adobe Inc. uses Revenues * in its reporting, so put that in the Filter by Concept Name field, set “Get Latest Facts’ to true and then click ‘Get’ for a result. If you remove the year from the query, you’ll get Adobe’s revenues for all years as reported in XBRL.

We’ve posted several templates on the Data Community page linked at right, along with documentation for the parameters and fields you can use with the XBRL API.

* There are a number of concepts companies can use to report revenue – this decision-making process is guided by the company’s research of the Financial Accounting Standards Board’s US GAAP Taxonomy, where these concepts are defined. Annual versions of the taxonomy are available from the XBRL API, and you can also get details from the ‘Taxonomy Viewer’ link on the right side of this page.

Some of the most common concepts used are:

• Revenues
• RegulatedAndUnregulatedOperatingRevenue
• InterestAndDividendIncomeSecurities
• ResultsOfOperationsRevenueFromOilAndGasProducingActivities
• LeaseIncome
• RevenueFromContractWithCustomerIncludingAssessedTax
• RevenueNotFromContractWithCustomerExcludingInterestIncome
• RevenueFromContractWithCustomerExcludingAssessedTax
• RevenueFromCollaborativeArrangementExcludingRevenueFromContractWithCustomer

[Bhushan Huparikar]

Hi,

Is the excel add-in or XBRL US API compatible with querying data for FERC filing data schedules?

• This reply was modified 4 months, 2 weeks ago by Bhushan Huparikar.

[Nathan Suderman]

Hi I wanted to see if anyone has completed the standardization/grouping of similar financial concepts? I have built out a Python parser and loaded a MySQL DB, but am having difficulty analyzing the data due to the dissimilarity in names being used.

[Peter W Reed]

Unfortunately, the GAAP taxonomy is maintained to hone concepts, not similarity over the years (period of focus). As improvements are made GAAP tags are deprecated and new tags are created. The common labels (e.g. ‘Total Revenue’) are fluid, plus I’ve seen up to four variations for a given gaap tag in a single 10-K.

The GAAP Taxonomy is published yearly. Here is a link to the current year’s – https://xbrl.us/home/filers/sec-reporting/taxonomies/

The common labels are published in an XML file with each company submission. I use BeautifulSoup, not an XML parser.

[Peter W Reed]

I was in a rush when I wrote my last post on 12/21/23. I should have mentioned an approach to a universal tag to extract all of the common name labels for a gaapTag. There are a countable number – no more than four that I have found.

For Intel, a common label for 2018 and 2022 is “Net Revenue”. The gaap tags for those years are different SalesRevenueNet and RevenueFromContractWithCustomerExcludingAssessedTax.

The underlying definitions for the two gaap tags are different. I don’t have an accounting background to understand the nuances. If you were to look at Yahoo Finance all you would see is the common label “Net Revenue” for both years. Python’s NLP might help too. The first sheet in the SEC Taxonomy has a definition for each GAAP tag. Perhaps NLP could help unmangle things.

[david.tauriello@xbrl.us]

Hi Bhushan – yes, we copy every Form 1, 2, 6, 60 and 714 report submitted to FERC into our Public Filings Database. The report.source-name filter can be set to extract only FERC data.

[Author]

Posts