Build a quarterly panel from SEC company facts
A quarterly fundamentals panel must preserve what each financial fact actually measures. Select the company, concept, unit, original start and end dates, filing form and source version before assigning a quarter. A six-month revenue fact cannot be loaded as one quarter, and four cumulative disclosures cannot simply be added to obtain annual revenue. Build explicit conversion rules and retain the facts behind every derived cell.
This guide executes a synthetic SEC-style revenue panel and separately checks one issuer’s original filing and concept response. The runnable fixture makes no network requests and does not claim every XBRL concept is available through CoTrading. The reference capability concerns SEC-covered issuers; A-share and Hong Kong fundamentals need other sources and their own mappings.
Acquire and retain the original fact context
The SEC API documentation describes company facts and concept data with separate unit arrays and reporting contexts. Its aggregate APIs have scope restrictions, including standard taxonomy and entity-level facts. Do not assume a missing custom concept can be reconstructed by choosing a similarly named standard tag without review.
Save the raw response, retrieval date, company identifier, taxonomy and tag. For each selected fact, keep start, end, value, unit, filing date, accession and form. Preserve repeated and amended disclosures rather than overwriting their history before selection. A quarterly panel should link back to these records, not only to a general company page.
Choose a concept mapping for each company and explain any equivalence across tags. This fixture accepts the declared Revenues concept and USD only. It deliberately rejects an EUR row. That exclusion is a measurement-contract decision, not an automatic currency conversion. Instant balance-sheet facts need a separate panel policy and must not pass through a duration-flow subtraction.
Supply a fiscal calendar instead of guessing from labels
The synthetic companies use January-through-December 2025, with four explicit quarter intervals. A real issuer may have a different fiscal year or a week-based calendar. Do not infer the quarter solely from a filing’s current fiscal label or its submission year when the fact’s original duration is available.
For a cumulative flow, the first quarter equals its year-to-date value. Later single quarters equal current cumulative value minus the preceding cumulative boundary, provided both use the same company, concept, unit, fiscal start and compatible accounting scope. Annual minus nine-month cumulative value can produce the fourth quarter under the same requirements.
| Synthetic AAA duration | Revenue, USD millions | Single-quarter result |
|---|---|---|
| January–March | 100 | 100 |
| January–June | 260 | 160 |
| January–September, amended | 450 | 190 |
| January–December | 700 | 250 |
The raw values are stored in USD, then divided by one million for display. A label saying millions in an issuer’s presentation does not mean an API value should be divided twice. Keep source units and display scale as separate metadata.
Select a dated version and validate direct quarters
For each original duration, the fixture selects the latest eligible filing date at the declared cutoff. A conflicting value on the same date stops selection rather than letting input order decide. The original record collection remains unchanged. Repeated identical first-quarter revenue in a later filing is a new source reference, not a second quarter of revenue.
current = selected.get((symbol, fiscal_start, quarter_end))
previous = selected.get((symbol, fiscal_start, preceding_quarter_end))
derived = D(current["val"]) - D(previous["val"])
if direct_quarter is not None and D(direct_quarter["val"]) != derived:
raise ValueError("direct_ytd_conflict")The full code handles the first quarter and missing boundaries explicitly. When a direct-quarter observation exists, it is used after agreement with the derivable value is checked. If either cumulative boundary is absent, do not subtract a more distant period and call the result one quarter. That would combine several quarters without showing it.
Download the complete example. Use Python 3.12+, the standard library and synthetic facts; run python R534.py. The authoring source is docs/growth/research/round-5/examples/R534.py.
Actual AAA output is 100, 160, 190 and 250 USD million. The fourth quarter is derived from annual 700 minus amended nine-month 450 and carries both source records. Before the amendment, a cutoff of 1 November produces third-quarter 185 from cumulative 445; the later amendment changes it to 190. Eleven original records remain retained, including the excluded unit and older disclosures.
Read availability as source-version metadata
Each output includes the selected source facts and an available_on date. For a difference, this is the latest filing date among its inputs. It is a conservative date for that selected derivation, not proof of an intraday market-availability timestamp. A later repeated fact can therefore carry a later source-version date even when the same number appeared earlier.
Keep first publication evidence separately if the research needs historical daily availability. This panel does not create a complete point-in-time financial database from date-only filings. A downstream price join must acquire reliable publication or processing timestamps and define its own decision cutoff instead of treating fiscal period end as the date the value was known.
The Python decimal documentation supports explicit arithmetic and scaling. It cannot establish that two cumulative facts share an accounting scope. Acquisitions, discontinued operations or restatements can require a comparability review even when the subtraction is numerically valid.
Check an actual original filing
Microsoft’s Form 10-Q for the period ended 31 December 2024, printed page 3, presents total revenue of 69,632 for three months and 135,217 for six months, in USD millions. Those headers were read in the original issuer-hosted filing, not inferred from a search snippet.
Loading 135,217 as that single quarter would be incorrect. Subtracting the stated three-month amount from the six-month amount gives 65,585 for the preceding three-month portion. This duration check is separate from the synthetic company’s January fiscal calendar. The runnable file records the frozen original amounts without making a live request.
A separate read-only authoring check of the SEC Microsoft revenue concept response confirmed the original accession 0000950170-25-010491, filed 29 January 2025. The USD facts have starts 1 October and 1 July 2024, the same 31 December end, and values 69,632,000,000 and 135,217,000,000. Dividing by one million matches the original filing. This bounded source check does not validate the issuer’s full history or every concept.
Keep missing boundaries visible
Synthetic BBB has first-quarter 50 and nine-month cumulative 180, but no six-month boundary. Its third quarter remains unavailable because 180 minus 50 describes six months, not the third quarter alone. The fixture does not divide that difference evenly or manufacture a missing second quarter.
This missing-boundary case differs from the EUR row, which is present but outside the accepted unit. It also differs from a revision, where a new eligible fact replaces the selected version while the old evidence remains. Give those conditions separate reasons so the repair task requests the right source or mapping.
Deliver the concept contract, fiscal intervals, raw fact archive, version-selection log, quarterly panel and unresolved facts. Reconcile each output cell to a direct duration or a valid cumulative difference. Rerun affected quarters after amendments and reopen dependent derived cells. The result is a calculable, source-linked quarterly dataset, not a header-preservation exercise or a claim of universal SEC historical coverage.
Validate overlapping disclosures before publishing cells
Keep a duration reconciliation table alongside the panel. It should show each direct-quarter value, the two cumulative inputs where available and their difference. Agreement is a useful arithmetic check; disagreement identifies a version, scope or extraction question that must be resolved before the cell is accepted.
The full fixture injects a later six-month value of 261 while the direct second quarter remains 160 and the first quarter remains 100. The difference becomes 161, so execution raises direct_ytd_conflict instead of quietly publishing whichever value was encountered first. The original successful panel remains a separate run. This tests an actual blocked calculation, not a note suggesting that someone check the numbers later.
Version selection first groups the eligible records, identifies the newest filing date and then checks same-date consistency. Identical newest-date values can use a deterministic accession choice for the selected reference while every original fact stays archived. This avoids row-order dependence when a response contains repeated disclosures. For historical cutoffs, repeat that selection within the cutoff rather than reusing today’s latest result and changing only its label.
Accepted-unit facts must also be finite before version selection or cumulative subtraction. The script parses the value as Decimal and rejects NaN and either infinity with nonfinite_fact_value and the source accession. Its self-check injects all three forms. This is a source-input failure, not an unavailable quarter that can be filled or a valid unbounded revenue amount; finite zero and negative observations remain distinct from these malformed financial values.
FAQ
Can four YTD disclosures be summed as annual revenue?
No. They overlap. Derive nonoverlapping quarters or use the appropriate annual duration.
Is a filing date enough for an intraday historical join?
No. It identifies a dated source version; obtain reliable availability times and a decision policy separately.