dennisthiessenandClaude Opus 4.8
921f3d06fb
The A5 parity report surfaced coverage gaps and wrong values that all traced to the SEC facts parser and read-time derivation rather than to bad source data. Fixes, each validated by replaying the production parser + derivation against live company facts: - Period identity is derived from period_end against the issuer's fiscal calendar, not SEC's fy/fp fields, which collide (two period ends on one key, one silently discarded) and invert (a period sorting before one that precedes it) often enough to break the quarter chain. Recovers BXP, CRM, CRWD, FRT, MTD, NTAP, PPL, STX, WDAY. Fixed labels are internal ordering keys only (not in any API schema), so a filer whose year ends in early January shifting by one is harmless. - Revenue concept list gains RevenuesNetOfInterestExpense (banks) and the IncludingAssessedTax variant (REITs/consumer); EPS gains the continuing-ops variant (REG/FCX) and, last, basic EPS for a period tagging no diluted variant at all (PPL). All appended, so any issuer that already resolved keeps its concept. - YTD span tolerance 20 -> 25 days, covering 4-4-5 retail calendars whose 36-week YTD-Q3 (251-252d) previously missed by ~2 (COST, PEP, DPZ). - Amendment resolution is per field: a partial 10-K/A (Part III only, no financial facts) no longer blanks the period (DVN). - TTM diluted EPS is suppressed when a split contaminates the trailing window (BKNG's mixed-unit sum produced a P/E of 1.10 that clamped to a perfect fundamental sub-score). A post-filing split with no share-count evidence (KLAC) remains undetectable from this data. - Multi-class share fallback: weighted_avg_diluted_shares is captured and used for market cap when the cover-page count is absent (dimensional, so missing from company facts for META/CMCSA/CHTR/FOXA/NWSA/LEN). Within ~0.6% of the true count on controls; flagged shares_estimated in the API. BRK-B has no weighted-average fact either and stays unavailable. 820 unit tests pass; new tests confirmed to fail against the pre-fix code. Effect is inert until existing rows are reparsed (see reparse path). Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
88 lines
5.1 KiB
Python
88 lines
5.1 KiB
Python
from datetime import date, datetime
|
|
|
|
from sqlalchemy import Date, DateTime, Float, ForeignKey, Index, String, UniqueConstraint
|
|
from sqlalchemy.orm import Mapped, mapped_column
|
|
|
|
from app.database import Base
|
|
|
|
|
|
class FundamentalSnapshot(Base):
|
|
"""CIK-keyed, one immutable row per SEC accession.
|
|
|
|
Keyed by issuer (CIK), not ticker — multi-class issuers (GOOG/GOOGL) share
|
|
one CIK and one set of fundamentals; the ``tickers.cik`` column is the only
|
|
join point. Amendments are retained: every accession is a distinct immutable
|
|
row, and readers resolve (cik, fiscal_year, fiscal_period) at read time by
|
|
taking the newest ``accepted_at`` **per field**, falling back to the newest
|
|
accession that actually reports one — a partial amendment (a 10-K/A adding
|
|
Part III reports no financial facts) must not blank the period — no flags, no mutation.
|
|
|
|
**Facts are stored as the filing reports them, never as derived quarters.**
|
|
Duration facts (revenue, net_income, operating_income, diluted_eps, cfo,
|
|
capex, depreciation_amortization) hold the filing's normalized **cumulative
|
|
YTD/FY** value over (period_start -> period_end). Balance-sheet facts
|
|
(cash_and_st_investments, total_debt, shares_outstanding) are **period-end**
|
|
values. ``shares_outstanding`` is a single consolidated point-in-time count —
|
|
the ``dei:EntityCommonStockSharesOutstanding`` cover-page fact, or
|
|
``us-gaap:CommonStockSharesOutstanding`` at period end when no dei fact exists
|
|
(e.g. Alphabet). It is never a class sum (companyfacts is non-dimensional) nor
|
|
the weighted-average diluted count, since both consumers (estimated market cap,
|
|
YoY dilution read) want a point-in-time value. Discrete quarters (10-Q YTD
|
|
deltas, Q4 = FY - Q1..Q3), TTM, YoY and
|
|
the quarter tape are all derived at read time — so non-calendar fiscal years
|
|
resolve correctly and a later amendment never leaves a stale frozen quarter.
|
|
"""
|
|
|
|
__tablename__ = "fundamental_snapshots"
|
|
__table_args__ = (
|
|
UniqueConstraint("accession", name="uq_fundamental_snapshots_accession"),
|
|
Index("ix_fundamental_snapshots_cik_period", "cik", "fiscal_year", "fiscal_period"),
|
|
Index("ix_fundamental_snapshots_cik_period_end", "cik", "period_end"),
|
|
)
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
cik: Mapped[str] = mapped_column(String(10), nullable=False)
|
|
accession: Mapped[str] = mapped_column(String(25), nullable=False)
|
|
form: Mapped[str] = mapped_column(String(12), nullable=False) # 10-Q, 10-K, 10-K/A ...
|
|
filed_date: Mapped[date] = mapped_column(Date, nullable=False)
|
|
# Kept although PIT enforcement is deferred (one timestamp now vs painful retrofit).
|
|
accepted_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
|
|
|
|
# Period identity — required to align non-calendar fiscal years and to derive
|
|
# discrete quarters from cumulative facts.
|
|
period_start: Mapped[date | None] = mapped_column(Date, nullable=True)
|
|
period_end: Mapped[date] = mapped_column(Date, nullable=False)
|
|
fiscal_year: Mapped[int] = mapped_column(nullable=False)
|
|
fiscal_period: Mapped[str] = mapped_column(String(4), nullable=False) # Q1|Q2|Q3|Q4|FY
|
|
|
|
# Duration facts — cumulative YTD/FY over (period_start -> period_end).
|
|
revenue: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
net_income: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
operating_income: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
diluted_eps: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
cfo: Mapped[float | None] = mapped_column(Float, nullable=True) # cash flow from operations
|
|
capex: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
depreciation_amortization: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
|
|
# Balance-sheet facts — period-end values.
|
|
cash_and_st_investments: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
total_debt: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
shares_outstanding: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
# The cover-page share count (dei:EntityCommonStockSharesOutstanding) is
|
|
# reported "as of" its own date, which can differ from period_end — store it
|
|
# so market cap uses the right point-in-time count.
|
|
shares_outstanding_date: Mapped[date | None] = mapped_column(Date, nullable=True)
|
|
# Weighted-average diluted count for the filing's most recent quarter — the
|
|
# market-cap fallback when the cover-page count is absent, which it always is
|
|
# for multi-class issuers (per-class facts are dimensional, and companyfacts
|
|
# is not). An average is not cumulative, so unlike the duration facts above
|
|
# this is NOT a YTD value: it is the shortest-span fact ending at period_end.
|
|
weighted_avg_diluted_shares: Mapped[float | None] = mapped_column(Float, nullable=True)
|
|
|
|
import_run_id: Mapped[int | None] = mapped_column(
|
|
ForeignKey("data_import_runs.id", ondelete="SET NULL"), nullable=True
|
|
)
|
|
created_at: Mapped[datetime] = mapped_column(
|
|
DateTime(timezone=True), default=datetime.utcnow, nullable=False
|
|
)
|