The S&P 500 Is a Time Series

Oct 24, 2019

October 24, 2019

The most dangerous column in an equity backtest is often is_member.

A current constituent file answers which securities are in an index now. A historical screen asks which securities were in that index at a specific market instant, using only information available to the system at that instant. Those are different queries over different clocks.

Projecting today’s S&P 500 constituents backward creates a clean dataset with a false past. Deleted companies disappear. Later additions arrive years early. Acquisitions terminate without their former members. Distress exits vanish near the point where they matter most.

Historical S&P 500 constituents belong in an event log.

Illustrative turnover in a maintained 500-security universe. The upper area contains future members injected before admission. The lower area contains former members erased after deletion. The chart measures wrong membership, not return.

One Boolean, Two Clocks

Index membership looks like a Boolean:

def is_member(index_id, security_id, timestamp):
    ...

That signature is incomplete. A production system needs two timestamps:

def is_member(
    index_id,
    security_id,
    valid_at,
    known_at,
):
    ...

valid_at is the effective market time. It answers whether the security participates in the index calculation for a session, close, or intraday interval.

known_at is the information cutoff. It answers whether the membership record, correction, or announcement had reached the system before the decision.

The distinction produces two legitimate historical views.

A benchmark reconstruction usually wants the latest corrected record of what was effective on a date. In bitemporal notation, it asks for membership at valid_at=t with knowledge taken from the latest available database state.

An operational replay wants the database state that a live process could have observed. It asks for membership at valid_at=t and known_at=k, where k is the strategy’s decision time.

Collapsing those views creates look-ahead bias through metadata. A vendor correction received in 2019 can silently alter a 2014 backtest. The price series remains unchanged while the universe changes underneath it.

An Index Change Is a Transaction

A maintained index does not emit isolated row edits. It emits change sets.

One security leaves. Another enters. Several share classes can change together. A merger can remove a constituent outside the normal rebalance cycle. The effective boundary may be the close of one session or the open of the next. All rows in the change set must become visible atomically.

The minimum event record is small:

from dataclasses import dataclass
from datetime import datetime


@dataclass(frozen=True)
class MembershipEvent:
    index_id: int
    security_id: int
    change_set_id: str
    action: str                 # "add" or "remove"
    announced_at: datetime
    effective_at: datetime
    received_at: datetime
    source_revision: int

announced_at belongs to the provider.

effective_at belongs to the index methodology.

received_at belongs to the data pipeline.

source_revision belongs to the vendor record.

None of them is interchangeable with a filing date, a market-data timestamp, or the date printed in a CSV filename.

Store timestamps as timezone-aware instants. Retain the provider’s session label separately. “Effective after Friday’s close” carries market-calendar semantics that disappear when reduced to a naive date.

A reducer should apply each change set against one immutable state and publish the resulting state once:

from collections import defaultdict
from itertools import groupby
from operator import attrgetter


def bitmap(dense_ids):
    value = 0
    for dense_id in dense_ids:
        if dense_id < 0:
            raise ValueError("dense ids must be non-negative")
        value |= 1 << dense_id
    return value


def apply_change_set(active, events, dense_id_by_security):
    additions = bitmap(
        dense_id_by_security[event.security_id]
        for event in events
        if event.action == "add"
    )
    removals = bitmap(
        dense_id_by_security[event.security_id]
        for event in events
        if event.action == "remove"
    )

    if additions & removals:
        raise ValueError("security added and removed in one change set")
    if additions & active:
        raise ValueError("addition is already active")
    if removals & ~active:
        raise ValueError("removal is not active")

    return (active & ~removals) | additions


def compile_snapshots(
    events,
    dense_id_by_security,
    initial_active=0,
):
    ordered = sorted(
        events,
        key=lambda event: (
            event.effective_at,
            event.change_set_id,
            event.source_revision,
            event.security_id,
        ),
    )

    active = initial_active
    snapshots = []

    for effective_at, effective_group in groupby(
        ordered,
        key=attrgetter("effective_at"),
    ):
        pending = list(effective_group)
        by_change_set = defaultdict(list)

        for event in pending:
            by_change_set[event.change_set_id].append(event)

        next_active = active
        for change_set_id in sorted(by_change_set):
            next_active = apply_change_set(
                next_active,
                by_change_set[change_set_id],
                dense_id_by_security,
            )

        active = next_active
        snapshots.append((effective_at, active))

    return snapshots

security_id remains the immutable identifier in the event log. dense_id_by_security assigns compact process-local ordinals solely for bitmap storage.

The bitmap is a Python integer. Arbitrary-precision integers provide a compact C-level bitset with fast union, intersection, subtraction, and equality. For a dense universe of 50,000 securities, the payload is about 6.25 kilobytes per state before object overhead. Index histories contain far fewer state transitions than price histories, so complete snapshots are often cheaper than a complicated cache.

For thousands of indices, checkpoint every fixed number of change sets and retain deltas between checkpoints. The query path stays logarithmic in time and bounded in replay length.

Half-Open Intervals Remove Boundary Ambiguity

Event logs are the source of truth. Interval tables are useful query projections.

Represent membership as [valid_from, valid_to). The start is included. The end is excluded.

A security removed at the effective instant T is absent at T. A replacement added at T is present at T. Adjacent intervals can share the same boundary without overlap, epsilon arithmetic, or date subtraction.

The storage layer needs valid time and system time:

SCHEMA = """
CREATE TABLE index_membership_version (
    index_id          INTEGER NOT NULL,
    security_id       INTEGER NOT NULL,
    valid_from        TEXT NOT NULL,
    valid_to          TEXT,
    recorded_at       TEXT NOT NULL,
    superseded_at     TEXT,
    change_set_id     TEXT NOT NULL,
    source_revision   INTEGER NOT NULL,
    source_hash       BLOB NOT NULL,

    CHECK (valid_to IS NULL OR valid_from < valid_to),
    CHECK (
        superseded_at IS NULL
        OR recorded_at < superseded_at
    ),

    PRIMARY KEY (
        index_id,
        security_id,
        valid_from,
        recorded_at
    )
);

CREATE INDEX ix_membership_valid_time
ON index_membership_version (
    index_id,
    valid_from,
    valid_to
);

CREATE INDEX ix_membership_system_time
ON index_membership_version (
    index_id,
    recorded_at,
    superseded_at
);
"""

A point-in-time query becomes explicit:

POINT_IN_TIME_SQL = """
SELECT security_id
FROM index_membership_version
WHERE index_id = ?
  AND valid_from <= ?
  AND (valid_to IS NULL OR ? < valid_to)
  AND recorded_at <= ?
  AND (superseded_at IS NULL OR ? < superseded_at)
ORDER BY security_id
"""


def members_at(connection, index_id, valid_at, known_at):
    value = valid_at.isoformat()
    knowledge = known_at.isoformat()

    rows = connection.execute(
        POINT_IN_TIME_SQL,
        (
            index_id,
            value,
            value,
            knowledge,
            knowledge,
        ),
    )

    return tuple(row[0] for row in rows)

The query is verbose because the domain is verbose. Removing either clock makes the result easier to compute and harder to trust.

Current Constituents Are Future Information

Consider a value screen run against a 2009 date with a 2019 constituent list.

The current list excludes companies removed after distress, bankruptcy, acquisition, loss of eligibility, or declining size. It includes securities admitted after large changes in capitalization and liquidity. It can also include identifiers, share classes, and ticker mappings that did not exist on the screen date.

The resulting universe has already survived ten years of index maintenance.

This failure is often filed under survivorship bias. The implementation contains several distinct leaks:

1. Membership survivorship: former constituents are absent.
2. Admission look-ahead: later constituents appear before their effective inclusion.
3. Identity look-ahead: current identifiers are projected into periods where they were invalid.
4. Methodology look-ahead: current selection rules are applied to historical markets.
5. Revision look-ahead: later corrections enter an operational replay.

A strategy can pass ordinary price-level look-ahead tests and still fail every item above.

Historical Composition Cannot Be Recalculated From Market Cap

Rebuilding historical index composition by ranking securities on market capitalization is an approximation, not a reconstruction.

A maintained index can use float-adjusted capitalization, liquidity screens, domicile rules, profitability tests, seasoning periods, sector balance, buffer rules, committee judgment, and methodology versions. Data available today can differ from the data observed by the provider. Corporate actions can alter the eligible security set between scheduled reviews.

Even a fully rules-based index is a versioned program. Replaying it requires:

• the methodology effective on the review date;
• the eligible universe as known then;
• point-in-time float and shares;
• the provider’s market calendar;
• tie-breaking and buffer rules;
• corporate-action handling;
• the exact review reference date;
• the publication and effective schedule.

A reconstructed rank can be useful as a research universe. Label it as a reconstruction. Do not store it in the same table as sourced historical index membership.

Membership Queries Should Be Cheap

A stock screener can evaluate thousands of dates, factor variants, and rebalance schedules. Re-running interval joins for every row is unnecessary.

Compile membership snapshots once, then locate the last state effective at or before the requested instant:

from bisect import bisect_right


class MembershipTimeline:
    __slots__ = ("_times", "_states", "_security_ids")

    def __init__(self, snapshots, security_ids):
        self._times = tuple(item[0] for item in snapshots)
        self._states = tuple(item[1] for item in snapshots)
        self._security_ids = tuple(security_ids)

        if tuple(sorted(self._times)) != self._times:
            raise ValueError("snapshots must be sorted")
        if len(self._times) != len(set(self._times)):
            raise ValueError("duplicate snapshot times")

    def state_at(self, timestamp):
        position = bisect_right(self._times, timestamp) - 1
        if position < 0:
            return 0
        return self._states[position]

    def members_at(self, timestamp):
        state = self.state_at(timestamp)

        while state:
            lowest_bit = state & -state
            dense_id = lowest_bit.bit_length() - 1
            yield self._security_ids[dense_id]
            state ^= lowest_bit

    def contains(self, timestamp, dense_id):
        return bool(self.state_at(timestamp) & (1 << dense_id))

The loop visits active members, not the full security master. For a 500-name index, membership enumeration performs about 500 integer operations regardless of the size of the global equity universe.

The dense ID is an internal ordinal. It must map to an immutable security identifier. Ticker symbols remain attributes with their own validity intervals.

The Backtest Needs a Decision Clock

A rebalance date alone is insufficient.

Suppose a model trades at Monday’s open. Its membership cutoff may be Friday’s close, Sunday’s received vendor state, or an index change announced several sessions earlier and effective Monday. Those choices produce different legitimate strategies.

Express the schedule in market events:

def build_rebalance_universe(
    membership_store,
    index_id,
    execution_session,
    calendar,
):
    execution_at = calendar.session_open(execution_session)
    decision_session = calendar.previous_session(execution_session)
    decision_at = calendar.session_close(decision_session)

    return membership_store.members_at(
        index_id=index_id,
        valid_at=execution_at,
        known_at=decision_at,
    )

The function forces an answer to a practical question: did the strategy know the change before it traded?

A benchmark replication study may intentionally trade on the provider’s announced schedule. A fundamental stock screener may use index membership only as an eligibility filter and wait until the next scheduled monthly rebalance. Both are coherent. A date-only join conceals the distinction.

Corrections Must Preserve the Old Database

Vendors repair history. Sources disagree. Manual research resolves ambiguous events. Overwriting the row destroys the ability to reproduce prior runs.

Append a new system-time version:

def supersede_membership_version(
    connection,
    key,
    corrected_row,
    received_at,
):
    with connection:
        current = connection.execute(
            """
            SELECT recorded_at
            FROM index_membership_version
            WHERE index_id = ?
              AND security_id = ?
              AND valid_from = ?
              AND superseded_at IS NULL
            """,
            key,
        ).fetchone()

        if current is None:
            raise KeyError("open version not found")

        connection.execute(
            """
            UPDATE index_membership_version
            SET superseded_at = ?
            WHERE index_id = ?
              AND security_id = ?
              AND valid_from = ?
              AND superseded_at IS NULL
            """,
            (received_at.isoformat(),) + key,
        )

        connection.execute(
            """
            INSERT INTO index_membership_version (
                index_id,
                security_id,
                valid_from,
                valid_to,
                recorded_at,
                superseded_at,
                change_set_id,
                source_revision,
                source_hash
            )
            VALUES (?, ?, ?, ?, ?, NULL, ?, ?, ?)
            """,
            (
                corrected_row["index_id"],
                corrected_row["security_id"],
                corrected_row["valid_from"],
                corrected_row["valid_to"],
                received_at.isoformat(),
                corrected_row["change_set_id"],
                corrected_row["source_revision"],
                corrected_row["source_hash"],
            ),
        )

The corrected row can participate in a latest-truth reconstruction. The superseded row remains available for an operational replay. Both results are reproducible from the same table.

Validation Starts With State Transitions

A constituent count check is useful and insufficient. Some indices contain multiple securities for one company. Corporate actions can produce temporary exceptions. Provider methodology determines the expected count.

Audit the transitions first:

def audit_change_set(active, events):
    additions = {
        event.security_id
        for event in events
        if event.action == "add"
    }
    removals = {
        event.security_id
        for event in events
        if event.action == "remove"
    }

    duplicate_actions = additions & removals
    unknown_removals = removals - active
    repeated_additions = additions & active

    errors = []

    if duplicate_actions:
        errors.append(("conflicting actions", duplicate_actions))
    if unknown_removals:
        errors.append(("unknown removals", unknown_removals))
    if repeated_additions:
        errors.append(("repeated additions", repeated_additions))

    next_active = (active - removals) | additions

    return next_active, errors

Then audit the projection:

• no overlapping valid-time intervals for one index and security;
• no open interval followed by another addition;
• every removal closes an active interval;
• every source revision has an immutable hash;
• every effective instant resolves to a market session and phase;
• every ticker joins through a valid security-master interval;
• every backtest run records valid_at and known_at;
• every correction preserves the superseded version;
• every inferred event is marked separately from a sourced event.

Count anomalies should trigger review, not automatic repair. Silently inserting a missing member until the total looks right manufactures history.

Universe Lineage Belongs in Every Result

A factor value without universe lineage is difficult to reproduce.

Store a deterministic fingerprint with each screen, portfolio, and valuation batch:

from hashlib import blake2b
from struct import pack


def universe_fingerprint(
    index_id,
    valid_at,
    known_at,
    methodology_version,
    security_ids,
):
    digest = blake2b(digest_size=16)

    digest.update(pack(">Q", index_id))
    digest.update(valid_at.isoformat().encode("ascii"))
    digest.update(b"\x00")
    digest.update(known_at.isoformat().encode("ascii"))
    digest.update(b"\x00")
    digest.update(methodology_version.encode("utf-8"))
    digest.update(b"\x00")

    for security_id in sorted(security_ids):
        digest.update(pack(">Q", security_id))

    return digest.hexdigest()

The fingerprint should travel with:

• the security-master revision;
• the fundamental-data cutoff;
• the pricing cutoff;
• the corporate-action revision;
• the FX source and cutoff;
• the index-membership fingerprint;
• the screen or model version.

This is the minimum lineage needed to explain why two runs with identical source code produced different portfolios.

Index Membership Is Also a Feature

Membership can enter a model directly or indirectly.

It affects liquidity, analyst coverage, institutional ownership, benchmark demand, borrow availability, and the set of companies used for relative valuation. A fair-value engine that chooses peers from a current index can leak future membership into historical multiples even when the target company’s own statements are point-in-time correct.

Peer selection should therefore carry the same two clocks.

For a valuation at time t, the engine needs the target’s security identity, the issuer hierarchy, the historical eligible universe, the financial statements available by t, and the methodology version used to select comparables. A current peer set produces a historical narrative assembled from survivors.

The error can remain invisible because every individual ratio is arithmetically correct.

Keep the Raw Announcements

Normalized intervals are operationally convenient. Raw provider announcements are evidentiary.

Retain the original payload, retrieval time, content hash, parser version, and normalized change-set identifier. When a vendor file conflicts with an announcement, the raw record provides the path back to the source. When parser logic changes, the event stream can be rebuilt without downloading mutable history again.

The index constituent database should have three layers:

1. Raw source objects: immutable bytes and source metadata.
2. Normalized events: additions, removals, announcements, effective instants, revisions.
3. Query projections: bitemporal intervals, bitmaps, snapshots, and fingerprints.

Screens and backtests read projections. Audits trace projections to events. Corrections append events and rebuild projections.

This design is ordinary event sourcing applied to a domain that is usually delivered as spreadsheets.

The Universe Comes Before the Factor

A factor model begins after the historical universe has been fixed.

Prices, statements, market capitalization, enterprise value, and valuation ratios all depend on the security set presented to the computation. A perfect factor evaluated on a future-filtered universe is a test of the filter.

Before trusting a historical stock screen, ask for four values:

universe_key = (
    index_id,
    valid_at,
    known_at,
    universe_fingerprint,
)

Without them, “S&P 500 on June 30, 2009” is a description, not a reproducible query.

The S&P 500 changes through time. The database must change with it, while preserving every prior state.