Data cleaning: missing values, bad ticks, gaps and time zones.

Why cleaning comes before everything

A backtest is only as good as the series it receives. A price with a shifted decimal point, a duplicated session or a date assigned to the wrong day raise no error: they produce a false return that goes into the mean, the volatility and the signals without anything flagging it. Paid providers have them too (lesson 1.3.8), and the files you download and join yourself (lesson 1.4.1) add their own.

The file for this lesson, nova_sucio.csv, is NOVA's daily series for 2024 and 2025 with defects planted on purpose. Its clean version exists (the 2024-2025 rows of nova_diario_sin_ajustar.csv), so at the end you can check whether the cleaning got it right. This is the sequence to follow; the order matters, because each step needs the previous one (you cannot look for duplicates until the dates are in the same format):

flowchart TB
  L[Read as text] --> C[Headers and commas]
  C --> F[Session date]
  F --> D[Duplicates]
  D --> V[OHLC consistency]
  V --> T[Ticks ×10 and ÷10]
  T --> H[XNYS gaps]
  H --> S[Second source]
  S --> I[Report]

Reading without pandas guessing

The default read already warns that something is wrong, though you have to know how to look:

import pandas as pd

ingenuo = pd.read_csv("nova_sucio.csv")
print(len(ingenuo), "rows")
ingenuo.dtypes

502 rows
fecha_hora    str
apertura      str
maximo        str
minimo        str
cierre        str
volumen       str
dtype: object

Every column is text, the volume too. pandas only converts a column to numbers if all its values are numbers; a single odd value is enough for it to leave the whole column as text. And with the column as text, any later calculation fails or, worse, does something unexpected (adding strings concatenates them). To see which values are the odd ones, you attempt the conversion with errors="coerce", which puts NaN where it cannot convert, and look at the ones that fail without being empty:

num = ["apertura", "maximo", "minimo", "cierre", "volumen"]
for col in num:
    convertido = pd.to_numeric(ingenuo[col], errors="coerce")
    raros = ingenuo.loc[convertido.isna() & ingenuo[col].notna(), col]
    if len(raros):
        print(f"{col}: {len(raros)} odd, e.g. "
              f"{sorted(raros.unique())[:3]}")

apertura: 13 odd, e.g. ['46,58', '51,53', '57,67']
maximo: 1 odd, e.g. ['maximo']
minimo: 1 odd, e.g. ['minimo']
cierre: 1 odd, e.g. ['cierre']
volumen: 1 odd, e.g. ['volumen']

Two defects in plain sight. One row literally contains the column names: a repeated header, typical of files made by pasting several chunks together (here, one per year). And the open has numbers with a decimal comma, the way a spreadsheet in Spanish writes them. The correct read is to read everything as text, drop the repeated header, replace the comma and convert:

crudo = pd.read_csv("nova_sucio.csv", dtype=str)
es_cabecera = crudo["fecha_hora"] == "fecha_hora"
coma = crudo["apertura"].str.contains(",", na=False)
print("Repeated headers:", es_cabecera.sum())
print("Opens with a comma:", coma.sum())

df = crudo[~es_cabecera].copy()
df["apertura"] = df["apertura"].str.replace(",", ".")
df[num] = df[num].apply(pd.to_numeric)    # now without coerce
df = df.reset_index(drop=True)
print(len(df), "rows")
df.dtypes

Repeated headers: 1
Opens with a comma: 12
501 rows
fecha_hora        str
apertura      float64
maximo        float64
minimo        float64
cierre        float64
volumen       float64
dtype: object

The final conversion goes without errors="coerce": if any odd value were left, it should break here instead of silently turning into one more NaN. The volume comes out as float because it has empty cells (a NumPy integer does not accept NaN); it is turned back into an integer at the end, once it has no gaps.

Four date formats and a trap

The fecha_hora column is the time of the close, but written in four ways:

fh = df["fecha_hora"]
formato = pd.Series("New York time with offset", index=df.index)
formato[fh.str.endswith("Z")] = "UTC with Z"
formato[fh.str.endswith("+00:00")] = "UTC with +00:00"
formato[fh.str.len() == 10] = "date only"
formato.value_counts()

New York time with offset    434
UTC with Z                    26
UTC with +00:00               25
date only                     16
Name: count, dtype: int64

(The counts include the duplicated rows, which repeat the format of their original.) The first three formats are complete instants: with their zone, any of them can be converted to another. The fourth is not: it is a date. And here lies the most common trap in cleaning financial data. If everything is converted in one go with utc=True, pandas takes the bare date to be UTC midnight of that day, and UTC midnight in New York is the afternoon of the previous day. With the NYSE calendar from exchange_calendars (lesson 1.1.2) the damage is visible:

import exchange_calendars as xcals

xnys = xcals.get_calendar("XNYS")
sesiones = xnys.sessions_in_range("2024-01-02", "2025-12-31")

mal = pd.to_datetime(fh, utc=True, format="mixed")
fecha_mal = (mal.dt.tz_convert("America/New_York")
             .dt.normalize().dt.tz_localize(None))
solo_fecha = formato == "date only"
print("NYSE sessions in 2024-2025:", len(sesiones))
print("Rows on a day with no session:", (~fecha_mal.isin(sesiones)).sum())
print("Dates that collide with another row:",
      fecha_mal[solo_fecha].isin(fecha_mal[~solo_fecha]).sum())
print(pd.DataFrame({"texto": fh, "sesión": fecha_mal.dt.date})
      [solo_fecha].head(3))

NYSE sessions in 2024-2025: 502
Rows on a day with no session: 3
Dates that collide with another row: 13
         texto      sesión
1   2024-01-03  2024-01-02
20  2024-02-01  2024-01-31
41  2024-03-04  2024-03-03

The 16 dates without a time move to the previous day. Three fall on a day with no session (a Sunday or a holiday) and the other thirteen collide with the real row of the previous session: two different prices for the same day and a gap where the good data used to be. None of this raises an error.

The correct conversion treats each format for what it is. The instants are converted to New York time and from there the session date is taken; the bare date already is the session date. Then the time of the close is rebuilt with the calendar, which knows that the close is 16:00 New York time except on half-day sessions (13:00), and that in UTC that is 21:00 in winter and 20:00 in summer. The final check, that the rows with a time match the calendar's close, confirms that the conversion is the right one:

instante = pd.to_datetime(fh.where(~solo_fecha), utc=True,
                          format="mixed")
fecha_ny = (instante.dt.tz_convert("America/New_York")
            .dt.normalize().dt.tz_localize(None))
df["fecha"] = fecha_ny.fillna(pd.to_datetime(fh.where(solo_fecha)))
df["cierre_utc"] = xnys.closes.reindex(df["fecha"]).to_numpy()

print("Rows on a day with no session:", (~df["fecha"].isin(sesiones)).sum())
coincide = instante[~solo_fecha] == df.loc[~solo_fecha, "cierre_utc"]
print(f"File time = calendar close: "
      f"{coincide.mean():.0%}")
df["cierre_utc"].dt.strftime("%H:%M UTC").value_counts()

Rows on a day with no session: 0
File time = calendar close: 100%
cierre_utc
20:00 UTC    328
21:00 UTC    167
18:00 UTC      4
17:00 UTC      2
Name: count, dtype: int64

Four closing times in UTC for the same market: 20:00 in summer (328 rows), 21:00 in winter (167), 17:00 on the half-day session of July 3, which falls in summer, and 18:00 on those of the Friday after Thanksgiving and December 24, which fall in winter. The bottom row of the figure is where the naive conversion left the 16 dates without a time: at 00:00 UTC, which in New York is the afternoon of the previous day. That is why a daily series is identified by the session date in the exchange's time zone and, if the instant is needed, by the actual closing time of that day. When joining this series with another one from a different market (hourly EURUSD, a European index), you join by instant with merge_asof and not by date: the New York close of day D arrives when in Madrid it is almost day D+1.

Duplicates and empty cells

With the session date now common to all formats, duplicates can be searched for. There are two kinds: identical rows (the same chunk pasted twice) and repeated dates with different values (two versions of the same day, a more serious problem because you have to choose which one counts). The same cell counts the empty ones and the zero volumes. A volume of 0 on a liquid stock, on a day when the price moved, is not a day without trading: it is missing data written as zero, and it is more dangerous than a NaN because calculations accept it without complaint (a VWAP or a liquidity filter would use it as is).

columnas = ["fecha"] + num
exactos = df.duplicated(subset=columnas)
print("Rows identical to the previous one:", exactos.sum())
df = df[~exactos].reset_index(drop=True)
print("Repeated dates with different values:",
      df["fecha"].duplicated().sum())
print(len(df), "rows, one per session")

vacias = df[num].isna().sum()
print("Empty cells per column:", vacias[vacias > 0].to_dict())
print("Total:", vacias.sum())
vol_cero = df["volumen"] == 0
print("Volume 0:", vol_cero.sum(), "→",
      list(df.loc[vol_cero, "fecha"].dt.date.astype(str)))

Rows identical to the previous one: 6
Repeated dates with different values: 0
495 rows, one per session
Empty cells per column: {'apertura': 1, 'maximo': 3, 'minimo': 1, 'cierre': 1, 'volumen': 3}
Total: 9
Volume 0: 3 → ['2025-04-11', '2025-06-23', '2025-11-05']

Neither an empty high nor a missing volume can be deduced from the rest of the row: the high is not the larger of the open and the close (it is almost always higher), and the volume has no fixed relationship with anything. They are flagged as pending and resolved at the end with a second source.

Bad ticks: inconsistent within their bar

A typical bad tick is a shifted decimal point: 660.00 where it should say 66.00, or 4.96 instead of 49.64. The robust way to find them is not to search for extreme returns, but to check each bar's consistency with itself. An OHLC bar always meets three rules: the high is the largest of the four prices, the low the smallest, and all of them are positive. And the four prices of a single day are close to one another: it is rare for a stock's high and low to be more than 20% apart in one session. Comparing each price with the median of its row detects the ×10 and the ÷10 without looking at any other day:

import numpy as np

precios = ["apertura", "maximo", "minimo", "cierre"]

def incoherentes(d):
    p = d[precios]
    return ((p.max(axis=1) > d["maximo"])
            | (p.min(axis=1) < d["minimo"]) | (p <= 0).any(axis=1))

print("Inconsistent bars:", incoherentes(df).sum())
mediana = df[precios].median(axis=1)
escala = np.log10(df[precios].div(mediana, axis=0)).round()
sospechosos = escala.ne(0) & escala.notna()
filas = sospechosos.any(axis=1)
df.loc[filas, ["fecha"] + precios]

Inconsistent bars: 3
         fecha  apertura  maximo  minimo  cierre
238 2024-12-17     57.23  605.10   57.21   60.50
330 2025-05-06      4.96   52.97   49.39   52.23
341 2025-05-21     52.37    5.28   51.21   51.72
450 2025-10-28    660.00   67.78   65.89   67.75

Four rows, and in each one a single price is off by a factor of 10: two opens and two highs. The factor comes from rounding the base-10 logarithm of the ratio to the median: $k = +1$ for the ×10 and $k = -1$ for the ÷10. The range test only caught three of the four (a ×10 high is still “the largest in the bar”); the median test catches all four. That is why it pays to have more than one rule.

The correction is not the same in both directions. A price with two decimals multiplied by 10 keeps all its digits: 605.10 is exactly 60.51. One divided by 10 and written with two decimals has lost the last one: 4.96 can come from 49.60 or from 49.64, and multiplying by 10 would give a price invented to the cent. The ×10 ones are divided; the ÷10 ones are marked as empty to bring them in from another source.

antes = df[precios].copy()
por10 = sospechosos & (escala > 0)     # ×10: recovered exactly
entre10 = sospechosos & (escala < 0)   # ÷10: lost a decimal
df[precios] = df[precios].mask(por10, df[precios] / 10)
df[precios] = df[precios].mask(entre10)        # empty: source B
for col in precios:
    for i in df.index[sospechosos[col]]:
        print(f"{df.at[i, 'fecha'].date()} {col}: "
              f"{antes.at[i, col]:.2f} → {df.at[i, col]:.2f}")
print("Inconsistent bars:", incoherentes(df).sum())

2025-05-06 apertura: 4.96 → nan
2025-10-28 apertura: 660.00 → 66.00
2024-12-17 maximo: 605.10 → 60.51
2025-05-21 maximo: 5.28 → nan
Inconsistent bars: 0

No inconsistent bars. The two pending prices join the nine empty cells from before.

An outlier is not an error

The temptation, after the bad ticks, is to run a filter for extreme returns and remove whatever lies beyond three or four standard deviations. With NOVA that would be a serious mistake:

d = df.set_index("fecha")
r = d["cierre"].pct_change()
z = (r - r.mean()) / r.std()
hueco = d["apertura"] / d["cierre"].shift() - 1
vol_rel = d["volumen"] / d["volumen"].median()
mayores = r.abs().nlargest(5).index
pd.DataFrame({"rent_pct": 100 * r[mayores], "z": z[mayores],
              "hueco_pct": 100 * hueco[mayores],
              "vol_rel": vol_rel[mayores]}).round(1)

            rent_pct    z  hueco_pct  vol_rel
fecha
2025-02-04      15.0  6.5        4.1      2.2
2025-03-26     -12.1 -5.4       -3.8      2.2
2025-04-16      10.2  4.4        3.6      2.5
2025-04-10      -9.5 -4.2       -1.6      2.1
2024-12-17       8.4  3.6        2.6      3.6

Five days with z between 3.6 and 6.5: under a normal distribution, a z of 6.5 would be a once-in-millions-of-years event, and here there are several in two years. But there is nothing inconsistent about them. The whole bar has moved (the open already gaps by between 1.6% and 4.1% in the same direction) and the volume is between 2.1 and 3.6 times the median: the whole market took part. They are real moves, and for risk management they are the days that matter most; a z-score filter that removed them would make the strategy look calmer than it is. Fat tails are a stylized fact of markets (lesson 1.6.2), not a defect in the data.

One detail in the table anticipates the next section: the 10.2% of April 16, 2025 is not a one-day return but a two-day one, because the session of the 15th is missing from the file. A gap turns a daily return into a two-day return without warning.

The rule for this lesson is the one the diagram shows:

flowchart TB
  O[Extreme value] --> B{Consistent bar?}
  B -- no --> E[Error: fix]
  B -- yes --> M{Whole bar moves?}
  M -- yes --> R[Market: keep]
  M -- no --> X[Review by hand]

An error breaks the consistency of its bar (or that of one series with another: a price that does not match another source's on the same day). A market move shifts the whole bar and usually has a cause in the calendar: earnings, macro data, a corporate action (lesson 1.4.3).

Gaps: the calendar rules

A missing session leaves no trace in the file: it simply is not there. To see it you have to compare with the list of sessions there should be, which is exactly what the XNYS calendar gives (with its holidays and its closure on January 9, 2025):

faltan = sesiones.difference(df["fecha"])
sobran = pd.DatetimeIndex(df["fecha"]).difference(sesiones)
print("Missing:", len(faltan), "· extra:", len(sobran))
print([d.strftime("%Y-%m-%d %a") for d in faltan])

Missing: 7 · extra: 0
['2024-01-29 Mon', '2024-05-31 Fri', '2024-09-27 Fri', '2024-11-12 Tue', '2025-02-06 Thu', '2025-04-15 Tue', '2025-05-29 Thu']

Seven sessions. A resample("B") or bdate_range would not have worked: pandas business days know nothing of NYSE holidays, so they would have reported July 4 or Thanksgiving as missing. What has to be decided is what to do with the gaps, and the two most common automatic solutions are bad for different reasons:

serie = df.set_index("fecha")["cierre"].reindex(sesiones)
tramo = slice("2024-01-25", "2024-01-31")
comp = pd.DataFrame({
    "hueco": serie[tramo],
    "ffill": serie.ffill()[tramo],
    "interpolado": serie.interpolate()[tramo],
})
comp["rent_ffill_pct"] = 100 * comp["ffill"].pct_change()
comp.round(2)

            hueco  ffill  interpolado  rent_ffill_pct
2024-01-25  48.84  48.84        48.84             NaN
2024-01-26  46.81  46.81        46.81           -4.16
2024-01-29    NaN  46.81        47.36            0.00
2024-01-30  47.91  47.91        47.91            2.35
2024-01-31  49.06  49.06        49.06            2.40

• Forward-filling (ffill) copies Friday's 46.81 into Monday. It does not look into the future, but it invents a day with no movement (a return of 0) and loads onto Tuesday a +2.35% that belongs to two days. Volatility comes out lower, and a rule that trades on Monday does so at a price that was already old: a stale price. With seven gaps in a year the effect is small; with an illiquid stock, or with intraday data where many bars are missing, it is not.
• Interpolating puts 47.36 on Monday, the average between Friday and Tuesday. It looks more reasonable and it is worse: Monday's value depends on Tuesday, which nobody knew on Monday. Any signal computed that day uses information from the following day: look-ahead bias (lesson 0.1.1), hidden in the preprocessing, where nobody looks for it.

And in neither case can the volume, the high or the low be filled meaningfully. If there is no other source, the honest thing is to leave the gap flagged and have the backtest not trade that day. Forward-filling does have a legitimate use, with different semantics: in intraday bars, a bar with no trades is not missing data but an interval without trading, and it is represented with the last price and volume 0. That holds within a session, never to cross from one session to the next.

Second source and cleaning report

What cannot be deduced is brought in from another source: here, nova_diario_sin_ajustar.csv acts as a second provider. Only the empty cells, the zero volumes and the missing sessions are taken from it; everything else comes from the rules above, and that allows an honest check at the end: the cells the cleaning did not take from the second source have to match it.

b = pd.read_csv("nova_diario_sin_ajustar.csv", parse_dates=["fecha"])
b = b.set_index("fecha")[num].reindex(sesiones)

limpio = df.set_index("fecha")[num].reindex(sesiones)
limpio.loc[limpio["volumen"] == 0, "volumen"] = np.nan
de_b = limpio.isna()              # cells that come from source B
limpio = limpio.fillna(b)
limpio["volumen"] = limpio["volumen"].astype("int64")

iguales = np.isclose(limpio, b)
print("Cells taken from B:", de_b.to_numpy().sum())
print("Rest equal to B:", iguales[~de_b.to_numpy()].all())
print("Sessions:", len(limpio), "· inconsistent:",
      incoherentes(limpio).sum())

Cells taken from B: 49
Rest equal to B: True
Sessions: 502 · inconsistent: 0

Of the 2,510 cells (502 sessions times 5 columns), 49 come from the second source: 9 empty, 3 zero volumes, 2 ÷10 prices and the 7 entire missing sessions. The other 2,461 all match the clean version, so every rule got it right: the header, the commas, the session date, the duplicates and the ×10 ticks. What remains is the report, which is the piece that turns a cleaning into an auditable process. If a result changes tomorrow, the report says whether the data or the cleaning changed:

informe = pd.DataFrame([
    ("Repeated header", "fecha_hora == 'fecha_hora'", "drop",
     es_cabecera.sum()),
    ("Decimal comma", "',' in apertura", "',' → '.'", coma.sum()),
    ("Date without time", "len == 10", "session date",
     solo_fecha.sum()),
    ("Duplicate row", "duplicated()", "drop", exactos.sum()),
    ("Empty cell", "isna()", "source B", vacias.sum()),
    ("Volume 0", "volumen == 0", "source B", vol_cero.sum()),
    ("Tick ×10", "log10(p / median) = 1", "÷ 10",
     por10.to_numpy().sum()),
    ("Tick ÷10", "log10(p / median) = −1", "source B",
     entre10.to_numpy().sum()),
    ("Missing session", "XNYS − file", "source B", len(faltan)),
], columns=["defecto", "deteccion", "correccion", "filas"])
informe

             defecto                   deteccion    correccion  filas
0    Repeated header  fecha_hora == 'fecha_hora'          drop      1
1      Decimal comma             ',' in apertura     ',' → '.'     12
2  Date without time                   len == 10  session date     16
3      Duplicate row                duplicated()          drop      6
4         Empty cell                      isna()      source B      9
5           Volume 0                volumen == 0      source B      3
6           Tick ×10       log10(p / median) = 1          ÷ 10      2
7           Tick ÷10      log10(p / median) = −1      source B      2
8    Missing session                 XNYS − file      source B      7

In a project, this report is saved alongside the clean data (a CSV or a JSON per run) and the rules live in functions with tests, like the quality checks in lesson 1.4.8. The figures in the table match those in the data README: no rule has gone too far or fallen short.

Exercises

1. Without fixing the four bad ticks, how much does the annualized volatility of the daily returns computed with each day's high change? Compare it with that of the clean high.

maximo_sucio = antes["maximo"].set_axis(df["fecha"])
maximo_limpio = limpio["maximo"]

def vol_anual(s):
    return s.pct_change().std() * np.sqrt(252)

print(f"With bad ticks: {vol_anual(maximo_sucio):.0%}")
print(f"Clean:          {vol_anual(maximo_limpio):.0%}")

With bad ticks: 952%
Clean:          29%

2. Write a function huecos_intradia(barras) that, for the 5-minute bars in nova_barras_5min.parquet, returns how many bars each session of June 2025 has and how many it should have according to the calendar (78 in a normal session). Is any missing?

barras = pd.read_parquet("nova_barras_5min.parquet")

def huecos_intradia(barras, desde, hasta):
    ses = xnys.sessions_in_range(desde, hasta)
    esperadas = ((xnys.closes[ses] - xnys.opens[ses])
                 / pd.Timedelta("5min")).astype(int)
    fecha = (barras["ts"].dt.tz_convert("America/New_York")
             .dt.normalize().dt.tz_localize(None))
    reales = barras.groupby(fecha).size().reindex(ses, fill_value=0)
    return pd.DataFrame({"esperadas": esperadas, "reales": reales})

h = huecos_intradia(barras, "2025-06-01", "2025-06-30")
print(len(h), "sessions; with gaps:",
      (h["reales"] != h["esperadas"]).sum())
h.head(3)

20 sessions; with gaps: 0
            esperadas  reales
2025-06-02         78      78
2025-06-03         78      78
2025-06-04         78      78

3. A colleague proposes cleaning the data “by removing every row with any problem”. With what you have seen in this lesson, explain what consequences it would have on the daily returns and on a backtest.