Financial Statements Have Versions
Published May 23, 2019
Financial Statements Have Versions
A fundamental database usually starts with one row per company, metric, and fiscal period. Revenue for the period ended December 31 has a value. Operating income has a value. Shares have a value.
That model works until a company files an amendment, corrects an XBRL fact, or recasts a comparative period in a later filing. The database then has to choose between preserving the old value and replacing it. Most systems replace it.
Replacement creates a clean current snapshot and a false historical record. A backtest executed in March can silently receive a number published in May.
Illustrative values. The highlighted interval is history invented by a latest-value database.
One period has several dates
A financial fact has at least three relevant dates:
- Period end: the business date described by the fact.
- Accepted time: the time the filing became available through EDGAR.
- Ingested time: the time the data pipeline successfully stored it.
For a December 31 annual period, the first complete filing may arrive in late February. An amended filing may arrive in May. A later annual report may recast the same comparative period again.
The period end stays fixed. The reported value can change. The set of values available to an investor changes over calendar time.
This is a bitemporal database problem. One clock describes the business period. The other describes when the system could have known the value.
The ingestion clock is operational evidence. It shows whether a backtest is using the source publication time or granting the strategy access before the pipeline had processed the filing.
The usual key is incomplete
A common stock screener database uses a key like this:
bad_key = (
company_id,
metric,
fiscal_period_end,
)That key permits one revenue value for one company and period. An amended 10-K has nowhere to go. The new row conflicts with the old row, so an UPDATE removes the first version.
A usable fact identity includes the filing version and the full reporting context:
fact_key = (
company_id,
taxonomy,
concept,
period_start,
period_end,
unit,
dimensions_hash,
accession_number,
)The accession number identifies the filing. The dimensions distinguish consolidated facts from segment facts. The unit distinguishes dollars from shares. The period start separates duration facts from instant facts and prevents quarters from colliding with year-to-date values.
The database may still maintain a normalized metric such as revenue. That metric is derived from filed facts. It does not replace their identity.
Filings should be immutable
The source layer should behave like an append-only log. A filing is inserted once. Its facts are inserted once. Corrections arrive as new filings and new facts.
The following SQLite schema is small enough for a prototype and preserves the fields required for point-in-time financial data:
import sqlite3
SCHEMA = """
PRAGMA foreign_keys = ON;
CREATE TABLE filings (
accession_number TEXT PRIMARY KEY,
company_id TEXT NOT NULL,
form TEXT NOT NULL,
period_end TEXT NOT NULL,
accepted_at TEXT NOT NULL,
ingested_at TEXT NOT NULL,
amendment_of TEXT,
FOREIGN KEY (amendment_of)
REFERENCES filings(accession_number)
);
CREATE TABLE facts (
accession_number TEXT NOT NULL,
company_id TEXT NOT NULL,
taxonomy TEXT NOT NULL,
concept TEXT NOT NULL,
period_start TEXT NOT NULL,
period_end TEXT NOT NULL,
unit TEXT NOT NULL,
dimensions_hash TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (
accession_number,
taxonomy,
concept,
period_start,
period_end,
unit,
dimensions_hash
),
FOREIGN KEY (accession_number)
REFERENCES filings(accession_number)
);
CREATE INDEX facts_lookup
ON facts (
company_id,
concept,
period_end,
unit,
dimensions_hash
);
CREATE INDEX filings_point_in_time
ON filings (
company_id,
accepted_at,
accession_number
);
"""
connection = sqlite3.connect("fundamentals.db")
connection.executescript(SCHEMA)The numeric value is stored as text in this example so application code can parse it with Decimal. Binary floating-point is a poor storage format for filed decimal values. Store timestamps in UTC. For instant facts, set period_start equal to period_end so every primary-key field is non-null.
The amendment relationship is useful metadata. Query correctness does not depend on following that link. The accepted timestamp and accession number establish the version order.
Query the value that was knowable
A current screener wants the latest eligible value. A historical screener wants the latest eligible value as of a specified timestamp.
Those are the same query with a different upper bound.
from decimal import Decimal
def fact_as_of(
connection,
company_id,
concept,
period_end,
as_of,
unit="USD",
dimensions_hash="{}",
):
row = connection.execute(
"""
SELECT facts.value
FROM facts
JOIN filings USING (accession_number)
WHERE facts.company_id = ?
AND facts.concept = ?
AND facts.period_end = ?
AND facts.unit = ?
AND facts.dimensions_hash = ?
AND filings.accepted_at <= ?
ORDER BY
filings.accepted_at DESC,
filings.accession_number DESC
LIMIT 1
""",
(
company_id,
concept,
period_end,
unit,
dimensions_hash,
as_of,
),
).fetchone()
if row is None:
return None
return Decimal(row[0])For a backtest dated March 1, the as_of timestamp is March 1. A May amendment is excluded. For a screen dated May 9, the amended value is eligible.
A production query also applies an availability policy. Filing acceptance at 17:29 Eastern does not justify a trade at that day’s closing price. Daily systems can make the filing eligible on the next trading session. Intraday systems need market calendars, timestamp normalization, and an explicit execution delay.
The policy belongs in code and tests. It should never be an undocumented vendor convention.
Restatements are events
An amendment is economically relevant. It can change margins, leverage ratios, growth rates, accrual measures, and valuation multiples. Overwriting the old value destroys the event.
Preserving versions permits several useful calculations:
- magnitude of a restatement
- frequency of amendments by issuer
- time from initial filing to correction
- factor sensitivity to later revisions
- data-vendor latency
- disagreement between filed and normalized values
A stock screener can expose the latest version while the research system retains the path. A fair valuation engine can price the company with current information and still reproduce the valuation that existed on any prior date.
This separation matters when a model is audited. A result should identify the exact filing versions that produced it.
Comparative periods also move
Amendments are the obvious case. Recast comparative periods are more common and easier to miss.
A 2019 annual filing can present 2018 values that differ from the 2018 annual filing. Discontinued operations, segment reorganizations, accounting changes, and correction of errors can all alter the comparative presentation.
A database keyed only by fiscal period end will treat the later comparative value as a correction to the old row. A versioned database records both observations:
observations = [
{
"period_end": "2018-12-31",
"accepted_at": "2019-02-28T21:42:10Z",
"accession_number": "0000000000-19-000101",
"concept": "Revenue",
"value": "1000",
},
{
"period_end": "2018-12-31",
"accepted_at": "2019-05-08T21:11:04Z",
"accession_number": "0000000000-19-000244",
"concept": "Revenue",
"value": "940",
},
]Both rows are correct records of what was published. Only the second is the latest statement.
The distinction is the basis of historical fundamentals. Without it, a backtest can reproduce neither the information set nor the accounting presentation available on its evaluation date.
Test the absence of time travel
Point-in-time behavior needs a regression test. The useful assertion is that a later filing changes future queries and leaves prior queries unchanged.
from datetime import datetime, timezone
from decimal import Decimal
def insert_filing_with_revenue(
connection,
company_id,
accession_number,
form,
period_end,
accepted_at,
revenue,
):
ingested_at = datetime.now(timezone.utc).isoformat()
with connection:
connection.execute(
"""
INSERT INTO filings (
accession_number,
company_id,
form,
period_end,
accepted_at,
ingested_at
)
VALUES (?, ?, ?, ?, ?, ?)
""",
(
accession_number,
company_id,
form,
period_end,
accepted_at,
ingested_at,
),
)
connection.execute(
"""
INSERT INTO facts (
accession_number,
company_id,
taxonomy,
concept,
period_start,
period_end,
unit,
dimensions_hash,
value
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
(
accession_number,
company_id,
"normalized",
"Revenue",
period_end,
period_end,
"USD",
"{}",
revenue,
),
)
def test_amendment_does_not_leak_backward(connection):
company_id = "example-co"
period_end = "2018-12-31"
insert_filing_with_revenue(
connection=connection,
company_id=company_id,
accession_number="0000000000-19-000101",
form="10-K",
period_end=period_end,
accepted_at="2019-02-28T21:42:10Z",
revenue="1000",
)
before_amendment = fact_as_of(
connection,
company_id=company_id,
concept="Revenue",
period_end=period_end,
as_of="2019-03-01T23:59:59Z",
)
insert_filing_with_revenue(
connection=connection,
company_id=company_id,
accession_number="0000000000-19-000244",
form="10-K/A",
period_end=period_end,
accepted_at="2019-05-08T21:11:04Z",
revenue="940",
)
same_historical_query = fact_as_of(
connection,
company_id=company_id,
concept="Revenue",
period_end=period_end,
as_of="2019-03-01T23:59:59Z",
)
after_amendment = fact_as_of(
connection,
company_id=company_id,
concept="Revenue",
period_end=period_end,
as_of="2019-05-09T23:59:59Z",
)
assert before_amendment == Decimal("1000")
assert same_historical_query == Decimal("1000")
assert after_amendment == Decimal("940")This test catches the most damaging implementation shortcut: updating a canonical fact row in place.
Additional tests should cover late ingestion, duplicate filings, amended filings without new financial facts, quarter versus year-to-date contexts, unit changes, segment dimensions, and filings accepted outside market hours.
Store lineage with every derived metric
A normalized ratio should carry enough lineage to reproduce its inputs.
For enterprise value to revenue, that means more than storing the final multiple. The calculation should identify the market timestamp, price source, share-count version, debt facts, cash facts, revenue facts, currency conversions, and filing accession numbers.
A compact provenance record can be serialized beside the result:
valuation_lineage = {
"calculated_at": "2019-05-23T14:30:00Z",
"market_data_as_of": "2019-05-22T20:00:00Z",
"fundamentals_as_of": "2019-05-22T23:59:59Z",
"filings": [
"0000000000-19-000101",
"0000000000-19-000244",
],
"formula_version": "enterprise_value_to_revenue:v3",
}This is inexpensive compared with reconstructing an unexplained valuation six months later.
Lineage also makes cache invalidation tractable. A new filing invalidates derived values that depend on the affected company, period, and concepts. It does not require rebuilding every historical result.
The database should answer two questions
A fundamental stock screener needs both of these queries:
- What is the best value available now?
- What was the best value available then?
A latest-snapshot table answers the first quickly. An immutable fact store answers both correctly.
The practical architecture is a versioned source layer plus derived current views. Current views can be rebuilt. Source history cannot.
Financial statements have versions. Once the database records those versions explicitly, restatements become data, historical screens become reproducible, and valuation results acquire an audit trail.