How the statements are rebuilt

1. The problem

A 10-K is a document. The SEC does not publish it as a table. What it publishes is the XBRL data behind it, scattered across seven files that each hold one facet of the filing:

None of these files knows what a financial statement looks like. num is a bag of numbers with no order. pre has order but no arithmetic. cal has arithmetic but no order and no labels. The model’s job is to put them back together.

2. Scope: the givens

given:
  CIK       :: number is 320193   -- the company
  FROM_YEAR :: number is 2013     -- earliest filing period to draw values from

Malloy given: parameters cannot be referenced inside a duckdb.sql() string directly. The workaround is the turducken pattern — a Malloy query embedded in the SQL with %{ … }, which the compiler resolves and inlines before the SQL runs:

scope AS (
  SELECT * FROM (%{
    filings_in_scope -> { select: adsh, cik, form, fy, fp, period, filed, name }
  })
)

filings_in_scope is an ordinary Malloy source carrying where: cik = $CIK, so the given flows through. This is the only mechanism that lets one CIK parameter drive a 400-line SQL block.

Reading the raw files

Paths use bracket globs and union_by_name:

read_parquet(['…/sub20[0-9][0-9]q[1-4].parquet',
              '…/sub20[0-9][0-9]_[0-9][0-9].parquet'], union_by_name = true)

Two patterns, because the archive changes naming convention partway through — pre2019q3.parquet (quarterly) up to early 2025, pre2025_07.parquet (monthly) after. Both are listed so new files of either shape are picked up with no code change.

The bracket classes are not cosmetic. The data folder also contains sub267row1m_all.parquet, sub2675_all.parquet, cal_distinct.parquet and other working files. A plain sub*.parquet sweeps those in and silently corrupts the results. duckdb.table() rejects bracket globs, which is why the raw sources are duckdb.sql() instead.

3. Naming the periods

Before anything else, the model works out what to call each period. Three CTEs do this.

period_dim  -- period -> fp (FY/Q1/Q2/Q3), preferring the 10-K's answer
fye_dates   -- the distinct period ends where fp = 'FY'
fye_month   -- the calendar month of the most recent fiscal year end

EDGAR’s fy field is deliberately discarded. It disagrees with itself for filers whose year ends in January. NVIDIA’s 10-K for the year ended 2024-01-31 carries fy = 2023 — NVIDIA itself calls that fiscal 2024 — and its 2025-01-31 filing appears twice under one accession number with fy = 2024 and fy = 2025. Across all 10-K filings since 2013, 6.6% have fy disagreeing with the period-end year, and roughly 4,200 filings appear under conflicting values. Labelling off that field drops years and duplicates others.

Instead, fiscal years are derived arithmetically:

fiscal_year = year(ddate) + CASE WHEN month(ddate) > fye_month THEN 1 ELSE 0 END

A fiscal year is named for the calendar year it ends in — the convention Apple, Microsoft and NVIDIA all use for their own "fiscal 2025". Anything past the year-end month belongs to the next fiscal year.

The quarter is derived the same way, from months since the year end:

(month(ddate) - fye_month + 12) % 12   -->  0 = FY, 3 = Q1, 6 = Q2, 9 = Q3

Anything else is an off-cycle date — a real disclosure at a non-reporting date, such as shares authorised on the day of a stock split. Those are labelled with the month (FY2024 (Jun)) so they can never collide with a real quarter.

Both derivations are arithmetic rather than lookups on purpose. A company files three 10-Qs before the 10-K that closes the year, so the newest quarters have no fiscal-year-end on record to look up yet; a lookup approach labels them a year early.

4. Picking the structure filing

structure AS (SELECT * FROM scope WHERE form = '10-K' ORDER BY period DESC LIMIT 1)

The most recent 10-K. Its labels, ordering and calculation hierarchy define every row in the output. If a company has no 10-K — foreign private issuers file 20-F — this returns nothing and the whole model yields no rows.

5. Narrowing to the financial statements

ren classifies every report in a filing via menucat:

rpt AS (… JOIN structure … WHERE r.menucat = 'S')

ren also supplies shortname, which is where titles like CONSOLIDATED BALANCE SHEETS come from. Without ren you would have to guess a statement’s name from its line items.

6. The presentation lines

pre_all takes every line of those statements from the structure filing. Then pre_raw strips out XBRL scaffolding that carries no value:

tag NOT LIKE '%Axis'   AND tag NOT LIKE '%Table'
AND tag NOT LIKE '%Domain' AND tag NOT LIKE '%Member'
AND tag NOT LIKE '%LineItems'
AND tag NOT LIKE '%ExtensibleList'
AND tag NOT LIKE '%ExtensibleEnumeration%'

These are structural markers (Statement [Table], Product and Service [Axis], Statement [Line Items]) and extensible enumerations, whose "value" is a pointer to another taxonomy element rather than a number. Left in, they produce permanently empty rows — in Tesla’s case, a permanently empty statement tab.

One thing is kept before the strip: report_axes, the set of %Axis tags each report presents. That gates segment expansion in step 11.

7. The calculation hierarchy

cal is a flat edge list: parent concept, child concept, network group (grp), arc order, and a sign. To get depth, walk it recursively.

roots AS (        -- a parent that is never a child within the same grp
  SELECT DISTINCT grp, ptag AS tag FROM cal_raw b
  WHERE NOT EXISTS (SELECT 1 FROM cal_raw b2
                    WHERE b2.grp = b.grp AND b2.ctag = b.ptag)
),
tree AS (
  SELECT grp, tag, NULL AS parent_tag, 0 AS depth, tag AS path FROM roots
  UNION ALL
  SELECT c.grp, c.ctag, h.tag, h.depth + 1, h.path || ' > ' || c.ctag
  FROM cal_raw c JOIN tree h ON c.grp = h.grp AND c.ptag = h.tag
  WHERE h.depth < 12
)

The depth cap is a cycle guard. A concept can sit under several parents in one network, so node collapses each concept to its shallowest placement:

node AS (SELECT grp, tag, min(depth) AS depth, … FROM tree GROUP BY grp, tag)

Shallowest, not deepest, so a subtotal never renders deeper than its own components.

8. Matching reports to calculation networks

Reports (pre.report) and calculation networks (cal.grp) are numbered independently. Nothing links them. The model matches them by set similarity over the concepts they share:

jaccard = hits / (report_concepts + network_concepts - hits)

The best-scoring network per report wins, subject to two thresholds:

WHERE rn = 1 AND hits >= 3 AND jac >= 0.20

The thresholds matter. A pure "most concepts in common" rule mis-assigns the balance sheet’s network to the equity statement, because they share many concepts. The thresholds reject weak matches outright, and a report that fails them keeps cal_depth = NULL throughout and renders as a flat list. That is the honest outcome for statements of shareholders’ equity, which frequently have no calculation network of their own.

9. Depth, and back-filling headers

Abstract header rows — ASSETS:, Current assets:, Operating expenses: — carry no calculation arc, so they have no depth. The SEC dataset has no indentation column, so depth must be inferred.

The rule: a run of unlinked rows sitting immediately above a linked row steps up one level per row.

runs AS (   -- group each unlinked run with the linked row that follows it
  SELECT *, sum(CASE WHEN cal_depth IS NOT NULL THEN 1 ELSE 0 END)
       OVER (PARTITION BY report ORDER BY line
             ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS run_id
  FROM lines_raw
),
filled AS (
  SELECT *, coalesce(cal_depth,
    greatest(max(cal_depth) OVER (PARTITION BY report, run_id)
             - (count(*) OVER (PARTITION BY report, run_id)
                - row_number() OVER (PARTITION BY report, run_id ORDER BY line)),
             0), 0) AS depth
  FROM runs
)

So ASSETS: lands one level above Current assets:, which lands one level above Cash and cash equivalents. The reverse window frame (CURRENT ROW AND UNBOUNDED FOLLOWING) is what associates a header run with the row beneath it rather than above.

outline_level is then depth + 2, clamped at 8, with statement-title rows pinned to 1.

10. The values

num_raw AS (… WHERE n.value IS NOT NULL AND coalesce(n.iprx, 0) <= 1)

iprx disambiguates a concept tagged more than once for the same period; <= 1 keeps the filer’s preferred fact. Note this scan is not restricted to the statement’s own concepts — alias detection in step 12 needs to see concepts the statement no longer uses.

Then two collapses:

fact_by_filing  -- one row per (concept, period, filing): max(value)
facts           -- one row per (concept, period): the most recently FILED value

arg_max(value, filed)                                AS value_all
arg_max(value, filed) FILTER (WHERE form = '10-K')   AS value_10k

The 10-K value is resolved separately rather than by filtering afterwards. If it were not, a later 10-Q repeating the same year-end balance would win the arg_max and knock the period out of the annual view entirely.

11. Segment (dimensional) breakdowns

dim_kv maps a dimension hash to its axis/member pairs. A composite hash (product × geography) becomes one readable pair via string_agg:

dim_label -- axis: "FairValueByFairValueHierarchyLevel + FinancialInstrument"

The critical gate is dim_report. A breakdown belongs to a statement only if that statement presents an Axis row for every axis in the hash:

JOIN report_axes ra ON ra.axis_tag LIKE '%' || k.key || 'Axis'
GROUP BY d.dimhash, ra.report, d.n_axes
HAVING count(DISTINCT k.key) = d.n_axes

Without it a concept drags every breakdown it has anywhere in the filing into every statement that uses it — Apple’s income statement would show revenue split by product and by geography and by fair-value level, most of which the income statement never renders. With it, the income statement gets exactly the product/service split it actually prints.

(dim_kv.key is the axis name with the Axis suffix and standard prefixes stripped, hence the suffix LIKE rather than an equality join.)

12. Superseded-concept aliases

A filer can retag a line without changing the line. NVIDIA tagged revenue Revenues through FY2018, switched to RevenueFromContractWithCustomerExcludingAssessedTax for FY2019–FY2021 (ASC 606), then switched back in FY2022. Each 10-K carries three years, so FY2019 fell in the gap between the last filing that used the old name and the first filing that restored it. The row built from the newest 10-K comes up empty for exactly that one year.

This is not taxonomy deprecation — Revenues is a live element the filer still uses — so no published successor list would catch it. The equivalence is established from the filer’s own numbers.

alias_pair  -- for each (statement concept, other concept) sharing periods:
            --   agree_n    = periods where both reported the SAME non-zero value
            --   disagree_n = periods where they differed
alias_ok    -- gates:
            --   disagree_n = 0
            --   agree_n   >= 2
            --   same crdr (debit/credit) and same iord (instant/duration)
            --   the alias is NOT itself a line in these statements

Every gate earns its place. Without the debit/credit test, Assets pairs with LiabilitiesAndStockholdersEquity — 55 agreements, because the balance sheet balances. Without the "not itself a row" test, two real lines get merged. Without the non-zero rule, any two concepts that are both zero look identical.

alias_fact picks the best-corroborated alias per concept and period; facts_ext unions those fills onto the real facts only where the canonical concept has nothing for that period. An as-filed value is never replaced. Each filled row carries value_from_alias_tag, alias_agree_periods and alias_disagree_periods so every substitution is auditable.

13. Assembling the rows

Three row types are unioned into a skeleton:

numbered then assigns document order:

row_number() OVER (ORDER BY report, line, sub, axis_k, member_k)

sub is 0/1/2 for section/line/segment, which is what keeps a statement title above its lines and each line above its own segment members.

kids counts calculation children per concept, giving child_count and is_subtotal — the flag that identifies a total rather than a component.

Finally joined LEFT JOINs the skeleton to facts_ext. The LEFT JOIN matters: header rows and statement titles survive with a null period, which is what makes them the blank spacer rows the outline needs.

14. The two frequency sources

statements_core emits one row per line item per period. Two sources filter it:

Two subtleties:

The annual view requires is_fiscal_year_end. Filings carry stray instants at odd dates; without this they land in a fiscal-year column and get summed with the real year-end figure.

The sort key is date-only, never date + duration. A quarter-end balance (qtrs = 0) and that quarter’s flows (qtrs = 1) share one column label, so they must share one sort key. Include the duration and the pivot emits two columns under the same heading and splits the values between them.

qtrs 2 and 3 (year-to-date) are excluded from the quarterly view: a 10-Q tags both the quarter and the cumulative year to date, and keeping both puts two different numbers under one column heading.

15. The concept-change log

tag_history is a separate source with a different grain. Where statements_core is anchored to one filing’s presentation, this walks every 10-K and 10-Q the company has filed and records each time a line’s concept changes.

Line identity is the presentation label, normalised (case, punctuation and spacing stripped). That is the filer’s own name for the line and the only identifier stable across a concept change — the concept is the thing that moved.

seq AS (
  SELECT *, lag(tag) OVER w AS prev_tag, …,
    count(*) OVER (PARTITION BY cik, stmt, form, label_key, tag
                   ORDER BY period, filed
                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      AS times_this_tag_used_before
  FROM per_filing
  WINDOW w AS (PARTITION BY cik, stmt, form, label_key ORDER BY period, filed)
)

10-K and 10-Q sequences are walked separately; the two forms can legitimately tag differently, and interleaving them reports an alternation as a stream of changes. times_this_tag_used_before distinguishes reverted from adopted.

Custom (filer-defined) concepts are detected without an extra join: EDGAR stores a standard taxonomy as us-gaap/2024 and a filer-defined concept as the accession number that introduced it, so version NOT LIKE '%/%' identifies one.

16. Quality-assurance views

Run statement_check first on any new company. A max_level of 2 means that report failed the Jaccard thresholds and rendered flat.

17. Reading order for the source file

1. given: block and the three filing sources — scope
2. scope … fye_month — periods
3. structure, rpt, pre_all, pre_raw — the row set
4. cal_raw … filled — hierarchy and depth
5. num_raw … facts — values
6. row_tag_set … facts_ext — aliases
7. dim_kv_used … member_rows — segments
8. section_rows … joined and the final SELECT — assembly
9. statements_annual / statements_quarterly — the two frequency cuts
10. tag_history — the change log