Calendar-Aligned Multi-Asset Data Without Lookahead Bias

Hello. Previously, you constructed and validated daily total-return series so that each return represents the economics of holding a security through splits and distributions. Those returns are still security-specific observations: each is produced on that security’s own trading dates. Before estimating a covariance matrix or eventually rebalancing a portfolio, you need a defensible rule for putting many such series onto a common timeline.

This lesson builds that rule. You will define a trading-calendar policy, construct a multi-asset research grid, distinguish genuinely missing data from expected market closures, and use backward-looking as-of joins for data that become available at irregular times. The central principle is:

At any decision timestamp, every value in the input panel must have been available no later than that timestamp.

---

A date is not yet a decision timeline

A daily record labelled 2024-07-05 is incomplete information. For portfolio research, you must distinguish at least four times:

Field	Meaning	Example
trade_date	Session label on which an asset traded	2024-07-05
observation_end	Time at which the measured period ended	NYSE close, 16:00 New York time
available_at	Earliest time your system could legitimately use the value	16:05, next morning, or later
decision_time	Time at which the strategy chooses target weights	16:10 or before the next open

For a daily total return $r_t$, the usual meaning is the close-to-close return ending at session $t$. It is not available before that close. If a strategy makes a decision immediately after the close, it can use $r_t$ only if the data vendor and research pipeline make it available by that decision time. If a vendor publishes final data overnight, using it at the same day’s close is leakage even though its trade_date says $t$.

This is the practical version of the information-set constraint:

$$\text{available\_at}(x)\le \text{decision\_time}$$

for every input $x$ used in that decision.

A well-formed backtest does not merely have dates in ascending order. It enforces this inequality at every rebalance.

---

Trading calendars are part of the investment specification

A generic pandas business-day frequency, such as B, excludes weekends but is not an exchange calendar. It may include exchange holidays, miss exceptional closures, and provide no market-close timestamps or early-close handling. For listed securities, use the calendar of the venue whose sessions define your portfolio’s decision process.

For a U.S.-listed equity or ETF universe, a reasonable initial convention is:

• Master calendar: NYSE sessions.
• Return label: close-to-close total return ending on each NYSE session.
• Decision time: a documented time after the official NYSE close.
• Execution: not assumed to occur at that close; later backtesting lessons will model the execution and holding periods explicitly.

The master calendar is a scaffold, not a justification for inventing observations. It says, “these are the dates on which the portfolio expects to make or assess decisions.” It does not say that every security necessarily has a valid quote or return on every date.

Study the exchange-calendar mechanics now; they are more reliable than manually maintained lists of holidays.

Calendar choices for multi-asset portfolios

There is no universally correct “multi-asset calendar.” Select one based on the mandate, venues, and decision protocol, then record it in the research configuration.

Situation	Calendar policy	Main implication
U.S.-listed stocks and ETFs	A single U.S. exchange calendar, such as NYSE	Straightforward daily alignment; a missing value on an expected session is a data-quality event.
Assets trading on different exchanges	A master decision calendar plus each asset’s own venue calendar	An asset may legitimately be closed when the portfolio calendar is active. Preserve that distinction.
Estimating a strictly synchronous return matrix	Intersection of sessions in which all required markets were open	Produces fewer observations but avoids treating exchange closures as zero returns.
Global end-of-day portfolio monitoring	Union of relevant sessions, with timestamps and stale-value flags	More complete timeline, but some marks are from earlier local closes and must retain their source time.
Scheduled monthly rebalancing	Master calendar determines the final eligible session for each rebalance period	“Month end” means the last valid session under the stated exchange calendar, not simply the final calendar day.

For the first project iteration, a U.S. ETF universe on one exchange calendar is a good controlled setting. It lets you establish point-in-time discipline before confronting non-synchronous international closes, foreign-exchange conversion, and venue-specific settlement assumptions.

---

Construct a complete grid without fabricating returns

Suppose the total-return panel from the previous lesson has this grain:

security_id | trade_date | total_return

The initial integrity requirements remain:

1. One row per (security_id, trade_date).
2. total_return is the close-to-close total return ending on trade_date.
3. The security was eligible for inclusion according to the mandate on that date.
4. Dates are normalized consistently before joining.

The following code creates a NYSE session calendar and an explicit security-by-session grid. It then attaches observed returns through an exact join.

import numpy as np
import pandas as pd
import pandas_market_calendars as mcal

def make_session_calendar(start_date, end_date, exchange="NYSE"):
    exchange_calendar = mcal.get_calendar(exchange)
    schedule = exchange_calendar.schedule(
        start_date=start_date,
        end_date=end_date,
    )

    calendar = pd.DataFrame(
        {
            # Keep date labels separate from timezone-aware timestamps.
            "trade_date": pd.to_datetime(schedule.index.date),
            "market_close_utc": pd.to_datetime(
                schedule["market_close"].to_numpy(),
                utc=True,
            ),
        }
    )

    calendar["decision_time"] = (
        calendar["market_close_utc"]
        + pd.Timedelta(minutes=10)
    )

    return calendar.sort_values("trade_date").reset_index(drop=True)


calendar = make_session_calendar(
    start_date="2023-01-01",
    end_date="2023-12-31",
    exchange="NYSE",
)

universe = pd.Index(["SPY", "IEF", "GLD"], name="security_id")

grid = (
    pd.MultiIndex.from_product(
        [universe, calendar["trade_date"]],
        names=["security_id", "trade_date"],
    )
    .to_frame(index=False)
)

returns = total_return_panel[
    ["security_id", "trade_date", "gross_total_return"]
].copy()

returns["trade_date"] = pd.to_datetime(returns["trade_date"]).dt.normalize()

if returns.duplicated(["security_id", "trade_date"]).any():
    raise ValueError("Duplicate security-date rows in the return panel.")

aligned_returns = (
    grid.merge(
        returns,
        on=["security_id", "trade_date"],
        how="left",
        validate="one_to_one",
        indicator=True,
    )
    .merge(
        calendar[["trade_date", "market_close_utc", "decision_time"]],
        on="trade_date",
        how="left",
        validate="many_to_one",
    )
)

aligned_returns["observation_status"] = np.where(
    aligned_returns["_merge"].eq("both"),
    "observed",
    "missing_on_expected_session",
)

aligned_returns = aligned_returns.drop(columns="_merge")

This output is intentionally a long panel. A typical row contains:

security_id
trade_date
gross_total_return
market_close_utc
decision_time
observation_status

The extra status field is not cosmetic. It prevents a dangerous ambiguity: a null can mean a legitimate venue closure, a vendor outage, an ETF launch date, a delisting-related exception, or an erroneous join. Those cases have different economic meanings and must not receive the same mechanical treatment.

Do not forward-fill return observations

A common but invalid pattern is:

# Do not do this for portfolio return inputs.
wide_returns = aligned_returns.pivot(
    index="trade_date",
    columns="security_id",
    values="gross_total_return",
).ffill()

Forward-filling a price level can sometimes be a valid way to create a marked valuation, provided the prior observation is known at the valuation time and its age is retained. Forward-filling a return, however, makes an old return appear to have occurred again. That changes the return distribution, suppresses volatility, and distorts covariance estimates.

Likewise, filling a closure with zero should not be an unexamined default. A security that simply did not trade during a market closure has no same-session return observation. Treating it as $0$ can bias correlations and make an illiquid or internationally traded asset appear safer than it is.

For a U.S.-listed universe on a single U.S. calendar, an unexpected null is generally an exception to investigate:

missing_expected = aligned_returns.loc[
    aligned_returns["observation_status"].eq("missing_on_expected_session"),
    ["security_id", "trade_date"],
]

if not missing_expected.empty:
    print("Unexpected missing observations:")
    print(missing_expected.head(20))

Only after classifying the cause should you decide whether to exclude an asset, truncate its history, alter the estimation sample, or apply a documented valuation rule.

---

A shared date is not always a shared observation

Now consider a portfolio containing a U.S. ETF and a London-listed ETF. On some dates, one exchange may be open while the other is closed. Even when both exchanges trade, their official closes occur at different UTC times.

There are two distinct operations that are often confused:

• Calendar alignment: placing records on a common index of decision dates.
• Economic synchronization: ensuring that observations represent information available by a common timestamp.

An inner merge of NYSE and LSE schedules identifies dates when both venues were open. That can be appropriate for a deliberately restricted covariance sample. But it does not make the two close-to-close returns perfectly simultaneous. The London close occurs before the New York close, and information released during the U.S. afternoon can affect the U.S. return without being reflected in that day’s London close.

For now, retain the metadata needed to reason about this later:

security_id
trade_date
venue_calendar
observation_end
available_at
decision_time
gross_total_return

This structure lets you answer an essential audit question: which market information was known when this portfolio decision was made?

---

Use an as-of join for irregularly available information

Exact joins are correct for a return panel when you expect an observation on a particular session. They are not sufficient for data released irregularly, such as:

• benchmark constituent files,
• fund holdings,
• accounting data,
• analyst estimates,
• economic indicators,
• security metadata revisions,
• model parameters saved after a previous estimation run.

For these data, you often want the latest record that was already available at each decision time. This is a backward-looking as-of join.

The pandas documentation is worth reading because the direction parameter is a direct research-control choice, not just an implementation detail.

Suppose feature_history records a point-in-time feature with these columns:

security_id
available_at
source_observation_end
value

Here, available_at must be the earliest time the feature could have been used, not the report period’s nominal date. An earnings figure for the quarter ending March 31 is not automatically usable on March 31; it may be released weeks later.

# One row per portfolio decision and eligible security.
decision_grid = (
    grid.merge(
        calendar[["trade_date", "decision_time"]],
        on="trade_date",
        how="left",
        validate="many_to_one",
    )
    .sort_values(["decision_time", "security_id"])
)

feature_history = feature_history.copy()
feature_history["available_at"] = pd.to_datetime(
    feature_history["available_at"],
    utc=True,
)
feature_history["source_observation_end"] = pd.to_datetime(
    feature_history["source_observation_end"],
    utc=True,
)

if feature_history.duplicated(["security_id", "available_at"]).any():
    raise ValueError(
        "Duplicate security and availability timestamps in feature history."
    )

# merge_asof requires ordering by its time key.
left = decision_grid.sort_values(["decision_time", "security_id"])
right = feature_history.sort_values(["available_at", "security_id"])

features_at_decision = pd.merge_asof(
    left=left,
    right=right,
    left_on="decision_time",
    right_on="available_at",
    by="security_id",
    direction="backward",
    tolerance=pd.Timedelta(days=180),
    allow_exact_matches=True,
)

With direction="backward", pandas selects, for each decision row, the last feature record whose available_at is less than or equal to decision_time. This is the correct default for point-in-time research.

The other directions have very different meanings:

Direction	Meaning	Appropriate for a live decision input?
backward	Most recent record available at or before the decision	Yes, usually
forward	First record available after the decision	No; this is future information
nearest	Record with the smallest time distance, whether past or future	No; it can select future information

allow_exact_matches=True is appropriate only when equality is economically valid. For example, if a finalized feature is reliably available at 16:05 UTC and your decision is at 16:10 UTC, an exact match is usable. If you only know the data were published “after market close,” do not assume a 16:00 decision can use them. Encode a conservative available_at instead.

The tolerance is also a modeling choice. In the example, a 180-day limit prevents a very old fundamental record from propagating indefinitely. The appropriate maximum age depends on the type of feature; it should be configured, monitored, and reported.

---

Make leakage checks executable

A good time-alignment pipeline produces evidence that it obeys its own rules. After an as-of join, retain the matched timestamp and test it directly.

matched = features_at_decision.copy()

future_match = (
    matched["available_at"].notna()
    & matched["available_at"].gt(matched["decision_time"])
)

if future_match.any():
    bad_rows = matched.loc[
        future_match,
        [
            "security_id",
            "trade_date",
            "decision_time",
            "available_at",
        ],
    ]
    raise AssertionError(
        f"Found {len(bad_rows)} future feature matches."
    )

matched["feature_age_days"] = (
    matched["decision_time"] - matched["available_at"]
).dt.total_seconds() / 86_400

stale_features = matched.loc[
    matched["feature_age_days"].gt(180),
    ["security_id", "trade_date", "feature_age_days"],
]

Apply the same mindset to the daily return panel. A practical release checklist is:

1. Calendar validity: every master trade_date appears in the selected exchange schedule.
2. Panel uniqueness: no duplicate (security_id, trade_date) rows before reshaping or computing returns.
3. Expected-session coverage: missing observations on an expected session are visible and classified.
4. Timestamp consistency: all intraday timestamps use a timezone-aware standard, normally UTC.
5. Availability rule: each attached feature satisfies available_at <= decision_time.
6. No future-oriented fill: no bfill, direction="forward", or direction="nearest" is used in decision inputs.
7. Staleness limits: any carried-forward price, feature, or metadata record has an explicit age and a maximum permitted age.
8. Reproducible policy: store the exchange calendar name, decision-time convention, source time zone, and tolerance parameters with every research run.

For your project repository, make the calendar policy a configuration object rather than embedding it in notebook cells:

CALENDAR_POLICY = {
    "master_exchange": "NYSE",
    "decision_delay_minutes": 10,
    "feature_tolerance_days": 180,
    "timestamps_are_utc": True,
    "asof_direction": "backward",
}

This is valuable in interviews and in production research alike: it turns a vague claim of “no look-ahead bias” into inspectable assumptions and testable code.

---

From aligned panels to an estimation matrix

Once the panel has been validated, you can reshape observed returns into a matrix for later risk estimation:

return_matrix = (
    aligned_returns
    .loc[
        aligned_returns["observation_status"].eq("observed"),
        ["trade_date", "security_id", "gross_total_return"],
    ]
    .pivot(
        index="trade_date",
        columns="security_id",
        values="gross_total_return",
    )
    .sort_index()
)

At this stage, nulls are information. Do not immediately replace them with zeros or forward-filled values. A simple synchronous sample would retain only dates with returns for every asset:

synchronous_returns = return_matrix.dropna(how="any")

That choice is conservative and transparent, but it may discard many rows for a heterogeneous universe. In the next module, you will examine how the remaining sample size and sampling frequency affect expected-return and covariance estimates. The important point here is that the estimator should receive a matrix whose timing and missing-data treatment are deliberate, not an accidentally filled spreadsheet.

---

Key takeaways

Multi-asset alignment is fundamentally an information-timing problem, not just a date-formatting task.

• Use a real exchange calendar rather than generic weekday logic when exchange sessions define your investment process.
• Keep trade_date, observation_end, available_at, and decision_time conceptually and, where possible, physically distinct.
• Build an explicit security-by-session grid, then attach returns with exact joins so missing expected observations remain visible.
• Do not forward-fill returns or silently convert market closures into zero returns.
• For irregular data releases, use merge_asof with direction="backward", a security-level by key, and a defensible tolerance.
• Treat direction="forward" and direction="nearest" as unsafe for live decision inputs because they can use future information.
• Preserve source timestamps and automate assertions that no matched record became available after its decision time.

Next, you will turn this aligned panel into a more robust research dataset by implementing automated checks for missingness, duplicates, outlier returns, adjustment errors, and changes in the eligible universe.