--- title: "1.4.2 Cleaning financial market data" description: "Missing values, duplicates, bad ticks, outliers that are not errors, gaps against the NYSE calendar and mixed time zones, with pandas." url: "https://pythonparatrading.com/en/python-trading-course/level-1/cleaning-financial-market-data/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-1/cleaning-financial-market-data.png" --- # 1.4.2 · Data cleaning: missing values, bad ticks, gaps and time zones > No price file arrives clean. This lesson takes a daily NOVA extract with eight kinds of defect and four date formats, finds them one by one with pandas, fixes them without inventing data and leaves a report of what changed. - Course: [Level 1: Financial data, markets and analysis](https://pythonparatrading.com/en/python-trading-course/level-1/) - Module: 1.4 · Financial data engineering - Duration: 60 min - Prerequisites: [0.4.4 · Files (CSV, JSON, YAML, TOML) and dates with time zones](https://pythonparatrading.com/en/python-trading-course/level-0/python-files-pathlib-time-zones/), [0.5.3 · Selection, filtering, groupby, merge, pivot, resample and moving windows](https://pythonparatrading.com/en/python-trading-course/level-0/pandas-groupby-merge-resample-rolling/), [1.1.2 · Sessions, auctions, holidays and calendars](https://pythonparatrading.com/en/python-trading-course/level-1/trading-sessions-auctions-and-market-calendars/) ## By the end of this lesson - Read a defective CSV without pandas guessing the types wrong: repeated headers, decimal commas and empty cells. - Convert times with mixed zones to the correct session date and check it against the XNYS calendar. - Detect bad ticks from a shifted decimal point (×10, ÷10) with OHLC consistency rules, and tell them apart from large real moves. - Find gaps against the exchange calendar and explain why forward-filling or interpolating them distorts a backtest. - Leave a reproducible cleaning report: which defect, how many rows, how it was detected and how it was fixed. ## 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): ```mermaid 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: ```python import pandas as pd ingenuo = pd.read_csv("nova_sucio.csv") print(len(ingenuo), "rows") ingenuo.dtypes ``` ```output 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: ```python 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]}") ``` ```output 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: ```python 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 ``` ```output 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: ```python 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() ``` ```output 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: ```python 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)) ``` ```output 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: ```python 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() ``` ```output 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 ``` *Fig. — UTC time of the NYSE close in every 2024-2025 session, and where the 16 date-only rows of nova_sucio.csv land if read as UTC.* Horizontal bands over time: the close is at 21:00 UTC in winter and 20:00 in summer, switching in March and November; six half days close at 18:00 or 17:00. On a separate row, highlighted, the 16 date-only rows, which a naive conversion places at 00:00 UTC, the previous evening in New York. 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). ```python 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))) ``` ```output 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: ```python 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] ``` ```output 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. ```python 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()) ``` ```output 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: ```python 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) ``` ```output 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: ```mermaid 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): ```python 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]) ``` ```output 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: ```python 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) ``` ```output 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. ```python 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()) ``` ```output 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: ```python 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 ``` ```output 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 ``` *Fig. — Cleaning report for nova_sucio.csv: rows or cells affected by each defect.* Horizontal bars: repeated header 1, decimal comma 12, date without time 16 (highlighted, the quietest defect), duplicate row 6, empty cell 9, zero volume 3, tick ×10 2, tick ÷10 2 and missing session 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. **Solution.** ```python 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%}") ``` ```output With bad ticks: 952% Clean: 29% ``` Two miswritten highs in 500 sessions multiply the volatility by more than thirty: 952% versus 29%. A ×10 tick produces a return of +900% followed by one of −90% (and a ÷10, the other way around), and the standard deviation, which squares, amplifies it. Anything built on that volatility (a position size, a stop, a Sharpe ratio) comes out useless. 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? **Solution.** ```python 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) ``` ```output 20 sessions; with gaps: 0 esperadas reales 2025-06-02 78 78 2025-06-03 78 78 2025-06-04 78 78 ``` The 20 sessions of June 2025 (the 19th is a holiday, Juneteenth) have their 78 bars. Computing the expected ones from the calendar's open and close times, and not with a fixed 78, makes the function work on half-day sessions too (42 bars). 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. **Solution.** Removing rows does not fix them: it turns each defect into a gap. Returns computed with `pct_change` on the trimmed series would mix one-day returns with two- or three-day ones (the one for the day after each removed row), with nothing to indicate it. With some thirty rows with a defect out of 500, that would be dozens of mismeasured returns. Besides, the defects are not spread at random: if a provider fails more on very active days (when the message volume is higher), removing those rows removes precisely the most volatile days and the backtest comes out calmer than it is. The right thing is to fix what has a clear rule, bring in from another source what cannot be deduced and flag the rest so that the backtest does not trade on it. ## Self-assessment 1. A row has `apertura` 660.00, `maximo` 66.40, `minimo` 65.10 and `cierre` 65.90. What is most likely? - A. A huge opening gap that closed during the session - B. A 10-for-1 split - C. Correct data: opens are volatile - D. A shifted decimal point: the real open is 66.00 *Answer D: A shifted decimal point: the real open is 66.00.* The open is outside the range of its own bar (higher than the high) and is almost exactly ten times the other prices in the row. A split would move all the prices at once, and a real gap cannot end up above the session's high. 2. A date without a time, `2024-01-08`, is converted with `pd.to_datetime(..., utc=True)` and then to New York time to get the session. Which session comes out? - A. January 7, a Sunday - B. January 8 - C. January 9 - D. It raises an error *Answer A: January 7, a Sunday.* Without a time, pandas assumes UTC midnight. In New York that is 19:00 on the previous day (UTC−5 in winter), so the row falls on Sunday the 7th, which is not a session. With other dates it falls on the previous session and collides with that day's row. 3. Monday's data is missing. What is the problem with filling the close by linear interpolation between Friday and Tuesday? - A. None: interpolation is neutral - B. It gives negative prices - C. It uses Tuesday's close, which nobody knew on Monday: the backtest sees the future - D. It changes the volume *Answer C: It uses Tuesday's close, which nobody knew on Monday: the backtest sees the future.* Monday's interpolated value depends on Tuesday. A rule that trades on Monday at that price is using information from the following day: look-ahead bias. Forward-filling does not look into the future, but it invents a day with no movement. 4. NOVA's largest daily change in close in 2024-2025 is 15% (z of 6.5), with the open already shifted 4% and twice the median volume. What do you do with it? - A. Remove it: it is an outlier - B. Keep it: it is a real, explainable move; a statistical outlier is not an error - C. Clip it at the 99th percentile - D. Replace it with the mean *Answer B: Keep it: it is a real, explainable move; a statistical outlier is not an error.* The whole bar moved, and with volume: it is the market, not a data error, and these are exactly the days that matter most for risk. Removing true extremes makes the backtest underestimate volatility and losses. You fix what is inconsistent (a price outside its bar), not what is rare. 5. Why should cleaning leave a report with every correction instead of silently overwriting the file? - A. Because pandas requires it - B. To be able to audit and repeat the process: if a result changes, you need to know whether the data or the cleaning changed - C. To make the file smaller - D. It is not needed: clean data is self-evident *Answer B: To be able to audit and repeat the process: if a result changes, you need to know whether the data or the cleaning changed.* A cleaning with no trail can be neither reviewed nor reproduced. With the report (defect, rows, rule and source of the correction) an error in the cleaning can be found and undone; without it, it contaminates everything that comes after. ## Key points - Read as text first and convert the types yourself: that way a repeated header or a decimal comma shows up, instead of turning the whole column into text. - A date without a time is not UTC midnight. Convert everything to the session date in the exchange's time zone and check it against the calendar (XNYS). - A bad tick is inconsistent within its bar (outside the range, ×10 or ÷10 the rest); a large but consistent move is the market and stays. - Gaps are found against the session calendar. They are not filled blindly: forward-filling invents quiet days and interpolation looks into the future. - What cannot be deduced (an empty high, a missing session) is brought in from another source or flagged; it is never invented. - Every correction goes into a report: defect, rows affected, detection rule and correction applied. ## Resources ### Research - [Seasonality in Python. Searching for Intraday Patterns](https://pythonparatrading.com/en/seasonality-intraday-preprocessing/) · Research-0024 · 9 min ### Documentation - [exchange_calendars: exchange calendars (XNYS, sessions, closes)](https://github.com/gerrymanoim/exchange_calendars) · github.com - [pandas: to_datetime (mixed formats, time zones)](https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html) · pandas.pydata.org - [pandas: missing data](https://pandas.pydata.org/docs/user_guide/missing_data.html) · pandas.pydata.org ### Data - [Level 1 data: NOVA extract with defects (CSV)](https://pythonparatrading.com/curso/datos/nivel-1/nova_sucio.csv) · Download · nova_sucio.csv - [Level 1 data: unadjusted NOVA daily bars, the clean version (CSV)](https://pythonparatrading.com/curso/datos/nivel-1/nova_diario_sin_ajustar.csv) · Download · nova_diario_sin_ajustar.csv Previous lesson: [1.4.1 · Downloading over REST and websockets: pagination, rate limits and retries](https://pythonparatrading.com/en/python-trading-course/level-1/rest-websockets-pagination-and-retries/) Next lesson: [1.4.3 · Adjusting for splits and dividends: adjusted and unadjusted prices](https://pythonparatrading.com/en/python-trading-course/level-1/adjusting-prices-for-splits-and-dividends/)