1. Aligning data and signals without look-ahead

Level 6: From data to decision

The previous five levels have left you with loose pieces: prices from a vendor with stable identifiers, a history adjusted for splits and dividends, a cleaning routine that detects the impossible and a store that updates without duplicating rows. This module is where those pieces come together into a single table, and it is also where the most expensive mistake in the craft gets made, because it is the only one that raises no exception at all: the join comes out perfect, the backtest runs, and the returns look gorgeous because the table contains things that on that date nobody knew.

Three clocks that do not tell the same time

Any half-serious strategy mixes series of different frequencies. Prices are daily and arrive at the close of the session. Fundamentals are quarterly, but they carry two dates that do not coincide: the end of the period they describe and the day the company filed them. Macro data is monthly or quarterly, arrives weeks late and, on top of that, gets revised after publication, sometimes substantially.

That lag is not an administrative detail. In the United States, a large company has some forty calendar days after the quarter end to file its quarterly report and some sixty for the annual one; smaller companies have longer deadlines still. If your table sticks first-quarter earnings onto the March 31 row, you are handing the strategy between five and eight weeks of advantage over the rest of the market, precisely in the stretch where the reaction to the announcement is concentrated.

The backward join is the only honest one

The correct operation is not an exact-key join nor a forward fill over a daily calendar. It is an approximate backward join: for each price row, take the latest observation whose publication date is at or before that row's date.

import pandas as pd

precios = pd.read_parquet("precios_diarios.parquet")
# columns: fecha, simbolo, apertura, cierre_ajustado
fundamentales = pd.read_parquet("fundamentales_trim.parquet")
# columns: simbolo, fin_periodo, fecha_publicacion, bpa, patrimonio_neto

# The join's time key is NEVER fin_periodo: it is the day the figure became public.
precios = precios.sort_values("fecha")
fundamentales = fundamentales.sort_values("fecha_publicacion")

panel = pd.merge_asof(
    precios,
    fundamentales,
    left_on="fecha",
    right_on="fecha_publicacion",
    by="simbolo",                       # matches within each company
    direction="backward",               # only looks backwards
    allow_exact_matches=True,           # the filing day itself already counts
    tolerance=pd.Timedelta("400D"),     # an old quarter is not dragged along forever
)

Three details people overlook. One: merge_asof requires both tables to be sorted by their global time key, not within each group. Two: if a company files late, the panel will drag an old quarter along for weeks, and that is exactly right, because it was the only thing known. And three: the tolerance stops a company that has stopped reporting from keeping a three-year-old figure alive.

Resampling badly does not throw an error, it throws off returns

The shortcut that ruins the most backtests is this one, and at first glance it looks reasonable:

# WRONG: reindex on period end and propagate forwards.
malo = (fundamentales
        .set_index("fin_periodo")
        .groupby("simbolo")["bpa"]
        .resample("D")
        .ffill())
# The March 31 row already carries the first quarter's earnings,
# which were not known until the end of April. Forty days of future, zero warnings.

The same problem shows up when aligning macro data: if you take the GDP series with its reference date and extend it backwards with a resample, your strategy will know in January how the quarter ended. And there is a subtler variant: any transformation that looks at the whole series at once, such as normalizing by the mean and standard deviation of the entire history or filling gaps by interpolation, injects the future into every row without a single date being wrong.

Signal, decision and execution are three dates

Even if the join is impeccable, the second lag remains: between knowing something and collecting on it. These are three distinct moments and confusing them costs money:

• The signal date is the last instant whose data goes into the calculation, normally the close of the session.
• The decision date is when that signal turns into a concrete order, with its size and its type.
• The execution date is when the order is filled, and only from then on does the price affect you.

panel = panel.sort_values(["simbolo", "fecha"])

# 1) Signal: computed with what is available up to the close of t.
panel["senal"] = panel.groupby("simbolo")["cierre_ajustado"].pct_change(60)

# 2) Decision: the signal from the close of t becomes an order that night,
#    so the position exists from the open of t+1.
panel["posicion"] = panel.groupby("simbolo")["senal"].shift(1)

# 3) Execution: you earn the stretch running from the open of t to the open of t+1.
panel["ret_apertura"] = panel.groupby("simbolo")["apertura"].pct_change()
panel["pnl"] = panel["posicion"] * panel.groupby("simbolo")["ret_apertura"].shift(-1)

If your operation cannot touch the open, one more shift is needed, and if on top of that you place orders by hand, count the real delay and not the ideal one. The practical check is brutally simple: when you are unsure which way a shift goes, try the other one. If the result improves, the one that improves is the wrong one. Alignment errors have an unmistakable signature, which is that they always fall in your favor.

A worked case: how many days of future you are slipping in

Do the arithmetic before arguing. Quarterly fundamentals, four filings a year, a median of some forty calendar days between period end and filing. If you join on period end, your table contains a figure that was not yet public for 4 × 40 = 160 days out of every 365: close to 44% of the history. And they are not just any 160 days, they are precisely the ones surrounding the earnings announcement, where a large stock routinely moves several percentage points in a single session.

When you fix this, what normally follows is not an improvement or a fine adjustment: it is that the annualized return collapses and the curve loses its suspicious smoothness. Marcos López de Prado has spent years pointing to look-ahead bias as one of the main reasons backtests do not resemble reality; it is worth treating that drop as the project's first piece of good news, because it is the first figure that describes the market rather than your spreadsheet.