How to get a sample of records via XBRL API for evaluation

[Tim Bui]

I would like to import financial data into my SQL Server for calculating ratios. However, before doing that, I would like test the use of XBRL database via SQL Server. Could any of you please show me how to get some records via API and import to SQL Server to evaluate the XBRL database? Thank you

[David Tauriello]

To return non-dimensionalized monetary facts with metadata for Apple’s 2018 10-K filing in the XBRL Spreadsheet (Google Sheet):

1. Add a row to the Defined Queries section of the Config tab, with cell A as the name for the query, something like – Apple 2018 10-k facts – and paste this query in the B cell of the added row:

   /fact/search?entity.cik=0000320193&period.fiscal-year=2018&report.type=10-K&fact.has-dimensions=false&concept.is-monetary=true&fields=concept.local-name.sort(ASC),fact.*,fact.offset(0)

   NOTE: there are likely a few faster ways to query and return this data. entity.cik, period.fiscal-year and report.type are used here because they are commonly understood parameters. Also, removing the fact.has-dimensions and/or concept.is-monetary filters will return ALL 2018 facts in the filing.

2. On the Main Sheet, change the Query dropdown to match the name you created above. Once it loads, the result matches the details in this filing (which is referenced in the return as report.sec-url https://www.sec.gov/Archives/edgar/data/320193/000032019318000145/0000320193-18-000145-index.htm)

   Using the wildcard (*) on the fact.* field returns all available metadata for the facts (see the XBRL API Documentation for details for these fields). The returned rows can be copied/pasted/edited to other tabs in this spreadsheet, separate files or imported to other aplications or databases.

3. Depending on your account, you might see 100, 200 or 212 records (rows) returned. If 212 records were not returned, you’ll need to use the fact.offset(0) at the end of the query to return the additional data (see 7 Handling Paging in the XBRL API Documentation for complete details. It is essential to use the sort flag on a field that returns unique values, so the offset can reliably return the next set of data – concept.local-name works in this instance).
   • Non-members will see 100 records. To get the next 100 rows, change the fact.offset(0) from 0 to 100, then from 100 to 200.
   • Basic Individual Members will see 200 records. To get the additional records, change the fact.offset(0) from 0 to 200.
   • Power User and Sole Practitioner Individual Members as well as all Organizational Members, will see up to 2,000 records at a time, with no limit on the total number of records that will be returned by any query.

   For reference, there are 905 facts in the query above if the fact.has-dimensions and concept.is-monetary parameters are removed.

We don’t support using the XBRL API to feed another database. Organizational members should contact us for more information (info@xbrl.us).

See our Benefits page for a comparison of records and tools available to Members using our Database of Public of Filings.

[David Tauriello]

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.

[Author]

Posts