Cleaning Messy Financial Data

Cleaning Messy Financial Data 🧹

Scene: 9:02 AM on the market-data desk. Overnight the vendor feed dropped two closing prices, the ETL job double-loaded a row, and one tick printed $9,999 on a stock that trades around $155. Nobody sends you a warning - you find out when the risk dashboard reports a volatility spike that never happened. Real market data feeds are never clean: missing prices, duplicate rows, and erroneous spikes are routine. This lesson covers the three essential cleaning steps every quant analyst performs - and the classic trap hiding inside each one.

1. Handling Missing Values

In finance, the standard approach for missing prices is forward-fill (ffill) - carry the last known price forward until a new one arrives. This mimics reality: if no trade happened, the last price holds.

df['close'] = df['close'].ffill()   # preferred in finance
df['close'] = df['close'].fillna(0) # only for non-price data!
df = df.dropna()                    # remove rows still missing

🧠 The mental model: no trade ≠ no value

When a stock does not trade, the exchange does not mark it down to zero - the last traded price stands until a new one prints. That is exactly what ffill() does. fillna(0), by contrast, tells every model downstream that the company went bankrupt at the close and was resurrected the next morning: a fake −100% return followed by a fake near-infinite rebound. One wrong fill, and every volatility, VaR and Sharpe number built on that column is fiction.

2. Removing Duplicates

Duplicate records arise from feed errors or ETL bugs:

df = df.drop_duplicates(subset=['date', 'ticker'])

3. Clipping Outliers - and the masking trap

The $9,999 print is almost certainly a data error (a misplaced decimal, a test tick). The textbook rule says: flag anything 3+ standard deviations from the mean. Try that here, though, and nothing gets flagged - the spike inflates the very mean and std you measure it with. In fact, with 10 rows no single point can mathematically sit more than (n−1)/√n ≈ 2.85 sample standard deviations from the mean. The outlier hides itself; statisticians call this masking.

The robust fix is Tukey's IQR fences - the boxplot rule. Quartiles are computed from ranks, so they barely move when one point goes crazy:

q1, q3 = df['close'].quantile([0.25, 0.75])
iqr = q3 - q1
df['close'] = df['close'].clip(lower=q1 - 1.5 * iqr,
                               upper=q3 + 1.5 * iqr)

Why not just drop outliers? In financial time-series, an outlier price is usually a data error, not a real event. Clipping preserves the row structure (critical for rolling windows) while removing the bad value.

Your Task

The three-step pipeline is already built - but Step 1 currently zero-fills the missing prices, the one thing you must never do to a price column. Find the 👉 YOUR TURN line and apply the last-price rule.

Predict before you run: the close for 2023-01-03 is missing. Run the starter as-is and look at that row - the zero-fill is so wrong that Step 3 mistakes your fabricated 0.0 for an outlier and "corrects" it to 143.91, a price that never existed. Two bugs do not make a right. Now fix the fill and run again: Jan 3 should show the carried-forward 150.00, and the outlier fence should tighten from about [143.91 .. 161.81] to [148.98 .. 158.77]. Cleaning steps interact - a bad fill in Step 1 quietly poisons the outlier detection in Step 3.