1. Calendars, time zones and gaps: when a data point is missing and when it does not exist

Level 4: Cleaning and validation

A price file with fewer rows than you expected is not necessarily broken. Sometimes it is missing a data point that ought to be there, and sometimes it contains exactly what it should contain because there was no market that day. The two situations look identical on screen, a row that is not there, and they are fixed in opposite ways. Confusing them is the most expensive mistake in the whole cleaning phase.

A data point that is missing and a data point that does not exist

It is worth separating four cases that a missing value merges without mercy:

• The data point is missing. The market opened, there was trading, and your vendor has not delivered it: their service went down, a rate limit kicked in, the download was cut off halfway. The value exists in the world and you do not have it. You can ask for it again.
• The data point does not exist because the market was closed. Weekend, public holiday, half-day session. There is nothing to recover and nothing to fill.
• The data point does not exist because the stock was suspended. The market opened, but that particular stock did not trade: volatility auction, pending news, suspended by the regulator.
• The data point does not exist because the company was not listed yet, or because it no longer is. Asking for a share price three years before its IPO is not a gap: it is a meaningless question.

All four arrive in pandas as the same missing value. If you apply the same fill instruction to all of them, you end up with a backtest that buys companies that did not yet exist and a strategy that sails through a trading suspension without breaking a sweat, because the price stayed obligingly frozen while the real world caved in.

The market calendar rules over your file

The only way to know which days should have data is to ask a market calendar, never to infer it from the data itself. Inferring it from the data is circular: if the vendor is missing three sessions, you will conclude that those three sessions never existed.

import pandas as pd
import pandas_market_calendars as mcal

# The calendar rules: it says which days there SHOULD be a session.
# XNYS is New York; XMAD, Madrid; XETR, Frankfurt. They do not share holidays.
calendario = mcal.get_calendar("XNYS")
sesiones = calendario.schedule(start_date="2024-01-01", end_date="2024-12-31")
esperadas = pd.DatetimeIndex(sesiones.index).normalize()

precios = pd.read_csv("aapl.csv", parse_dates=["fecha"], index_col="fecha")
observadas = precios.index.normalize().unique()

# Real gaps: market days with no row. This is a failure of the source.
huecos = esperadas.difference(observadas)

# Phantom rows: days with a row that the calendar says do not exist.
# Almost always badly converted time zones or another market's holidays.
fantasmas = observadas.difference(esperadas)

print("sesiones esperadas:", len(esperadas))
print("huecos reales:", len(huecos), list(huecos[:5]))
print("filas fantasma:", len(fantasmas), list(fantasmas[:5]))

That subtraction of indices in both directions is worth more than any visual inspection. The gaps tell you who to complain to; the phantom rows tell you your date conversion is wrong.

Time zones: where the future sneaks in

A timestamp without a time zone is an incomplete data point. You do not know whether that 09:30 candle is on winter or summer time, nor which universal instant it corresponds to. And the moment you mix two markets, that ambiguity stops being cosmetic and becomes information from the future.

The classic case: you pair the IBEX 35 daily close with the S&P 500 daily close on the index date. Both carry the same date, so it looks simultaneous. It is not: the Spanish close happens in the mid-afternoon and the US one several hours later. A signal that uses today's row to trade tomorrow in Madrid is looking at a price which, at the moment of the decision, had not yet been formed. And it is not constant: between the European and US daylight saving changes there are weeks each year in which the difference shifts by an hour.

# Localize in the market's real zone and convert EVERYTHING to universal time.
intradia = pd.read_csv("spy_1m.csv", parse_dates=["ts"], index_col="ts")
intradia.index = intradia.index.tz_localize(
    "America/New_York",
    ambiguous="NaT",     # hour repeated when the clock goes back in autumn
    nonexistent="NaT",   # hour that never exists in spring
)
intradia = intradia[intradia.index.notna()].tz_convert("UTC")

ibex = pd.read_csv("ibex_1m.csv", parse_dates=["ts"], index_col="ts")
ibex.index = ibex.index.tz_localize("Europe/Madrid").tz_convert("UTC")

# For each IBEX bar, take ONLY New York information already published.
cruce = pd.merge_asof(
    ibex.sort_index(), intradia.sort_index(),
    left_index=True, right_index=True,
    direction="backward", suffixes=("_ibex", "_us"),
)

When to carry the last known value forward and when it is cheating

Carrying the last known price forward is legitimate when the asset was still alive, when that price was already public on the date you are copying it to, and when the row is flagged as carried forward. It is cheating when it papers over a suspension, when it propagates backwards, or when nobody can tell afterwards which rows were real.

rejilla = pd.DatetimeIndex(
    mcal.get_calendar("XMAD")
    .schedule(start_date="2015-01-01", end_date="2024-12-31").index
).normalize()

panel = precios.reindex(rejilla)          # now the absences are genuine gaps
panel["cotizaba"] = panel["cierre"].notna()

# Carry forward AT MOST two sessions: longer than that with no price is not a
# technical gap, it means that stock has stopped trading. And you need to know that.
panel["cierre_relleno"] = panel["cierre"].ffill(limit=2)
panel["dato_arrastrado"] = (~panel["cotizaba"]) & panel["cierre_relleno"].notna()

# Volume is NEVER carried forward: with no session the volume is zero, not yesterday's.
panel["volumen"] = panel["volumen"].fillna(0)

A worked example: the strategy that only made money on the odd dates

Suppose a European and US panel with 2,600 sessions. Joining it on calendar date throws up 22 days on which one market opened and the other did not. If on those days you carry the foreign close forward, the signal is using a price more than twenty-four hours old, but dated today. A breakdown by day usually reveals the pattern: if on those 22 days the average return per trade is three times that of the rest of the sample, you have not discovered a holiday effect, you have discovered your own fill. Take those days out and see whether the edge survives. It almost never does.