The XBRL US Database of Public Filings is a Postgres resource that is a benefit of XBRL US Membership. The Database mirrors the structure of the XBRL standard so that an XBRL instance document and associated schema can be loaded and stored. This structure makes it very flexible for use in any XBRL data collection environment.
XBRL US populates the public database with XBRL filings submitted to the SEC and other regulators. The data is not amended and reflects the data actually filed.
Availability and Use
We recommend that user use a tool like PGAdmin to access the database.
Commonly run SQL queries:
- How many filings have been submitted in inline XBRL?
SELECT count(*) FROM accession WHERE entry_type = 'inline'
- What are all the details of filings made by Apple Inc in an XBRL format?
SELECT * FROM accession WHERE entity_name ilike '%Apple Inc%'
Here are some additional SQL queries that may be useful.
All XBRL data and in the instance and associated schema is recorded in the database, except for the exclusions listed below. All information about the XBRL documents is also stored such as the documents name, its filing date, if it’s a restatement and other submission related data.
In addition the following factual data is added to the database:
Fact Hash: Every fact reported in an XBRL format is assigned an identifier. The identifier or hash is based on the context of the fact. Facts with identical contexts will have the same hash. The hash does not include the value of the fact, so restated facts will have the same hash. This hash makes it possible to identify where the same information has been reported across multiple reports.
Ultimus Index: This is a number sequence associated with each fact. This sequence indicates when the fact was reported relative to the same fact in other reports. A value of 1 for example means that this is the latest fact reported. If the value was 2 it means the same fact has been reported in a more current report. In theory the fact value should be the same but can be different due to changes across reports, such as a restatement.
Fiscal and Calendar Year: This is the Fiscal or Calendar year when a fact was reported in the format YYYY. This data is calculated by looking at the date associated with the fact data and the financial period end for the reporting entity. Every fact is assigned a fiscal year and a calendar year. A fact reported with a date of 2017-12-31 with a period end of 09-30 will have a fiscal year of 2018 and a calendar year of 2017.
Fiscal and Calendar Period: This is the fiscal or Calendar period when a fact was reported. These can be in the format 1Q, 2Q, 3Q, 4Q, Y, 3QCUM, 1H, 2H. This data is calculated by looking at the date associated with the fact data and the financial period end for the reporting entity. Most facts are assigned a fiscal period and a calendar period. Facts with durations that do not fit into the assigned period types (such s 3 years) are not assigned a period type. A quarterly fact reported with an end date of 2017-06-30 with a period end of 2017-06-30 will have a fiscal period of 4Q and a calendar period of 2Q.
XBRL Features Not Supported
The database supports all XBRL features except:
- Comments in the instance and schema documents.
- Attributes of Schema types defined in an extension taxonomy.
The database can store data submitted in any XBRL format including I-XBRL, JSON and XML.
The database also has an associated load process that is built as an Arelle plugin called xbrlusDB. The load handles populating all the relevant database tables. The load processor ensures the same filing is not loaded twice and consolidates repeated data across filings. For example every filing uses the term Assets. The details of the Assets element are only recorded once, so the load process handles this consolidation. The load process performs the calculations for the ultimus index, updating the filing status and performing date alignment.
The load process is currently set to load one filing at a time, but can be configured to load multiple filings.
Each filing regime has a separate configuration module that is added to the plugin. This defines parameters required for the load, such as what field is the period end date, which field is the legal entity identifier, etc. the loader also has a standard load template that will use default values if a specific configuration is not provided.