Q. How many filings have been submitted in inline XBRL?

1
2
3
SELECT count(*)
FROM accession
WHERE entry_type = 'inline'

Q. What are all the details of filings made by Apple Inc in an XBRL format?

1
2
3
SELECT *
FROM accession
WHERE entity_name ilike '%Apple Inc%'

Q. What is the list of base Taxonomies in the database?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    uri
    , ns.taxonomy_version_id
    , prefix
    , ns.name AS ns_namespace
    , version
    , tx.name AS taxonomy_name
FROM
taxonomy_version txv
JOIN namespace ns
  ON ns.taxonomy_version_id = txv.taxonomy_version_id
JOIN taxonomy tx
  ON tx.taxonomy_id = txv.taxonomy_id
WHERE is_base = true
ORDER BY tx.name, version DESC, ns.name

Q. What facts were reported for SEC accession 0001393905-17-00010? What were the fiscal and calendar periods of those facts and what were the dimensions of those facts?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT q.namespace
    , q.local_name
    , list_dimensional_qualifications_string2(f.context_id) dimension
    ,CASE WHEN c.period_instant is not null
    THEN c.period_instant::varchar
    WHEN c.period_start is not null
    THEN c.period_start::varchar || ' - ' || c.period_end::varchar
    ELSE 'forever'
    END AS period
    , f.fiscal_period
    ,f.fiscal_year
    ,f.calendar_period
    ,f.calendar_year
    ,f.fact_id
    ,f.fact_value
FROM fact f
JOIN accession a
  ON f.accession_id = a.accession_id
JOIN element e
  ON f.element_id = e.element_id
JOIN qname q
  ON e.qname_id = q.qname_id
JOIN context c
  ON f.context_id = c.context_id
WHERE a.filing_accession_number = '0001393905-17-000104'

Q. What is the latest year end for Apple Inc?

1
2
3
4
5
6
7
8
9
SELECT year_end
 FROM
    get_simple_fact_by_accession('dei','CurrentFiscalYearEndDate'
    ,   (SELECT
        max(accession_id)
        FROM accession
        WHERE entity_name ilike '%Apple Inc%')
    ,'text'::varchar
    )  as year_end

Q. What is the structure of the DTS for the latest BARRACUDA NETWORKS filing?

1
2
3
4
5
6
7
8
9
SELECT  dt.starts_loop AS starts_to_loop
    , repeat('  ', dt.level) ||  d.document_uri AS tree_structure
FROM dts_tree((SELECT MAX(dts_id)
        FROM report
        WHERE entity_name ILIKE '%BARRACUDA NETWORKS INC%'))
        dt
JOIN document d
  ON d.document_id = dt.document_id
ORDER BY tree_order

Q. What are the details of all the US GAAP taxonomies published to date?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT *
FROM taxonomy t
JOIN taxonomy_version tv
    ON t.taxonomy_id = tv.taxonomy_id
JOIN taxonomy_version_dts tvd
    ON tv.taxonomy_version_id = tvd.taxonomy_version_id
JOIN dts
    ON tvd.dts_id = dts.dts_id
JOIN dts_document dd
    ON dts.dts_id = dd.dts_id
JOIN document d
    ON d.document_id = dd.document_id
WHERE t.name = 'US GAAP'
    AND top_level = true
    AND dts_name like name || '%'

Q. How many facts have ever been reported that use an extension line item?

1
2
3
select sum(primary_count) as facts_with_line_item_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that use an extension dimension?

1
2
3
select sum(dimension_count) as facts_with_dimension_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that use an extension member?

1
2
3
select sum(member_count) as facts_with_member_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that have either an extension line item extension member or extension dimension?

1
select count(*) from fact where is_extended is true

Q. What are the values for the following elements for filings on 2018-03-01:

  • Current Fiscal Year EndDate
  • Entity Public Float
  • Entity Filer Category
  • Document Fiscal Year Focus
  • Document Fiscal_Period Focus
  • Document Period End Date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
    filing_date
    , entity_name
    , ticker(entity_id) as ticker
    , document_type(a.accession_id) as Document_Type
    , get_simple_fact_by_accession('dei','CurrentFiscalYearEndDate',a.accession_id, 'text'::character varying) as Current_Fiscal_Year_EndDate
    , get_simple_fact_by_accession('dei','EntityPublicFloat',a.accession_id, null::numeric) as Entity_Public_Float
    , get_simple_fact_by_accession('dei','EntityFilerCategory',a.accession_id, 'text'::character varying) as Entity_Filer_Category
    , get_simple_fact_by_accession('dei','DocumentFiscalYearFocus',a.accession_id, 'text'::character varying) as Document_Fiscal_Year_Focus
    , get_simple_fact_by_accession('dei','DocumentFiscalPeriodFocus',a.accession_id, 'text'::character varying) as Document_Fiscal_Period_Focus
    , get_simple_fact_by_accession('dei','DocumentPeriodEndDate',a.accession_id, null::date) as Document_Period_End_Date
     
    FROM accession a
    WHERE
    filing_date = '2018-03-01'

What amounts have been reported with an element using the string 'derivative liabilit' since 2013?

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT on (element_local_name) element_local_name
    , fact_id
    , element_namespace
    , fiscal_period
    , fiscal_year
    , calendar_period
    , calendar_year
    , fact_value
FROM fact f
WHERE LOWER(element_local_name) LIKE '%derivative%liabilit%'
and fiscal_year > 2013
ORDER by element_local_name, fact_id DESC;

Q. What were the last 100 IFRS filings made to the SEC?

1
2
3
4
5
6
7
8
9
SELECT r.*
FROM document d
JOIN dts_document dd
  ON d.document_id = dd.document_id
JOIN report r
  ON dd.dts_id = r.dts_id
WHERE d.document_uri like 'http://xbrl.ifrs.org/taxonomy/%'
ORDER BY accepted_timestamp DESC
limit 100

Q. How many filings have been made using the 2017 taxonomy?

1
2
3
4
5
6
7
SELECT count(*)
FROM  document d
JOIN dts_document dd
  ON dd.document_id = d.document_id
JOIN report r
  ON r.dts_id = dd.dts_id
WHERE d.document_uri = 'http://xbrl.fasb.org/us-gaap/2017/elts/us-gaap-2017-01-31.xsd'

Q. What examples of non numerical transforms have been used in the latest SEC inline filings?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT a.entity_name
    , document_type
    , filing_date
    , filing_accession_number
    , creation_software
    , f.fact_value
    , f.inline_display_value
    , f.element_local_name
    , element_namespace
    , inline_scale
    , q.local_name as transform
FROM accession a
JOIN fact f
    ON f.accession_id = a.accession_id
JOIN qname q
    ON q.qname_id = f.inline_format_qname_id
WHERE entry_type = 'inline'
AND f.inline_is_hidden = false
AND inline_format_qname_id <> 22041680
AND inline_format_qname_id <> 22041682
AND filing_date > '2018-01-01'
ORDER BY a.accession_id desc
LIMIT 1000

Q. What are the largest XBRL filings (In facts Reported) since the 1st March 2018?

1
2
3
4
5
6
7
8
9
SELECT r.entity_name, r.source_report_identifier, count(*)
FROM
report r
JOIN fact f ON f.accession_id = r.report_id
WHERE true
AND accepted_timestamp > '2018-03-01'
GROUP BY 1,2
ORDER BY count(*) DESC
LIMIT 10

Q. How many hidden elements have ben used in inline filings by report since the start of 2018?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    entity_name
    , entry_url
    , r.properties ->> 'document_type'::text AS document_type
    , r.properties ->> 'filing_date'::text AS document_type
    , count(*)
FROM fact f
JOIN report r
    ON r.report_id = f.accession_id
WHERE true
    AND entry_type = 'inline'
    AND inline_is_hidden
    AND accepted_timestamp > '2018-01-01'
GROUP BY  1,2,3,4
ORDER BY count(*) DESC

Q. What elements has Microsoft used in its presentation linkbases?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
en.entity_code
, r.entity_name
, (r.properties ->> 'standard_industrial_classification'::text)::integer AS standard_industrial_classification
, r.properties ->> 'document_type'::text AS document_type
, r.report_id
, r.dts_id
, q.local_name
, n.description
, q.namespace
from report r
join entity en ON r.entity_id = en.entity_id
JOIN dts_network n ON n.dts_id = r.dts_id
join dts_relationship re ON re.dts_network_id = n.dts_network_id
join element e ON e.element_id = re.to_element_id
join qname q ON q.qname_id = e.qname_id
where r.entity_name ilike '%microsoft%'
and period_index = 1
--and abstract = false
and n.extended_link_qname_id = 10
order by description, tree_sequence

Q. What Taxonomy versions were used for Federal Signature Corporation?

1
2
3
SELECT  base_taxonomy_name(report_id), *
FROM report
WHERE entity_name = 'Federal Signal Corporation'

Comment

Using Data