Forum Replies Created
-
AuthorPosts
-
Wednesday, July 5, 2023 at 4:34 AM in reply to: Getting started with the XBRL Filed Data Add-in for Excel #211830Rogelio PaulinoParticipant
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.
Wednesday, July 5, 2023 at 6:54 AM in reply to: Getting started with the XBRL Filed Data Add-in for Excel #211831David TaurielloKeymasterHi 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 TaurielloKeymasterAn 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).Wednesday, July 26, 2023 at 1:06 PM in reply to: Extending search: adding terms and using Additional Parameters #212198Aaron StoutParticipantVery helpful. Thank you!
David TaurielloKeymasterA Member writes:
I’m trying to understand why a query using
fact.ultimus=true
reports different results when I add theunique
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
- 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). - The
fact.accuracy-index=1
filter operates on the report level to show the first instance of a fact meeting the query conditions. - 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
andultimus
withunique
. 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 TaurielloKeymasterAn 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 HicksParticipanti got it linked up with google sheets, and now i just want to pull a company annual revenue. is that to simple?
David TaurielloKeymasterHi 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
Friday, December 15, 2023 at 5:39 AM in reply to: Getting started with the XBRL Filed Data Add-in for Excel #215060Bhushan HuparikarParticipantHi,
Is the excel add-in or XBRL US API compatible with querying data for FERC filing data schedules?
- This reply was modified 8 months ago by Bhushan Huparikar.
Nathan SudermanParticipantHi 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 ReedParticipantUnfortunately, 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 ReedParticipantI 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.
Thursday, March 7, 2024 at 7:04 AM in reply to: Getting started with the XBRL Filed Data Add-in for Excel #216484David TaurielloKeymasterHi 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.
Bryn HarmanParticipantI am super new to this. Can anybody post a simple example of the showdata function that I can try out? I have been experimenting with the API for a couple of hours and I can’t get anything to work. When I try to use the showdata function the cell shows it as text.
David TaurielloKeymasterHi Bryn – it will depend on which spreadsheet application you’re using – Google Sheets or Excel.
In Google Sheets, the syntax is
=ShowData("https://api.xbrl.us/api/v1/report/search?fields=entity.ticker,report.entity-name,entity.code,report.base-taxonomy,report.document-type,report.period-end,report.accepted-timestamp.sort(DESC),report.entry-url,report.limit(10)","","","TRUE")
In Excel, it’s
=XBRL.showData("https://api.xbrl.us/api/v1/report/search?fields=entity.ticker,report.entity-name,entity.code,report.base-taxonomy,report.document-type,report.period-end,report.accepted-timestamp.sort(DESC),report.entry-url,report.limit(10)","","","1")
The second, third and fourth parameters in each function perform the same role; find out about these options from the formula’s help option after completing a query.
The Data Community page linked at right has several spreadsheet templates with queries you can explore and use to get data.
- The
-
AuthorPosts