---
name: rv-data-cleaning
description: Professional data-cleaning spec for this project (FINS5557 RV forecasting). Must trigger when cleaning/parsing/aligning any raw data — 5-minute S&P 500 CFD intraday data, Wind macro/cross-asset data, and building daily realized measures (RV5/RS±/BV/RQ). Trigger keywords: data cleaning, clean, parse, read_csv, timezone, UTC, timestamp, RTH, trading session, 5-minute, intraday, resample, missing values, alignment, realized variance, bar, raw, processed. In-project spec, travels with the repo, applies to all members.
---

# RV project data cleaning (professional spec)

Observe the following points before cleaning any raw data (full version: `docs/ai/rules/data-cleaning.md`). Cleaning is a **deterministic pure function** and must not introduce future information (jointly with `rv-leakage-guard`).

## General
- **Raw `data/raw/` is read-only and immutable**; cleaning outputs go to `data/processed/` + manifest (referencing raw sha256).
- Every removal/fill/flag step **counts and writes `quality_report.md`**; prefer flagging NaN + flag over silently altering data.
- Cleaning decisions use only information available at or before the point in time; whole-sample statistics and future-aware interpolation are forbidden.

## 5-minute SPX CFD (`USA_500_Index_GMT+0_NO-DST_M5.csv`)
- No header: `date, time(UTC), O,H,L,C, vol(tick count)`.
- **Timezone is the first trap**: source is GMT+0, must `tz_convert('America/New_York')` (DST-aware); **a fixed offset is strictly forbidden**. Summer should yield `-04:00`, winter `-05:00`.
- **First intersect with the NYSE trading calendar (XNYS)**: this CFD still trades on US market holidays (MLK, Presidents' Day, etc.); those days are **removed for the whole day** (empirically such days exist), and must not be treated as low-coverage half-days.
- Then **keep only RTH 09:30–16:00 ET** (a full day = 79 points = 78 5-min returns); a half-day 09:30–13:00 = 43 points / 42 returns, using the actual close per the NYSE calendar.
- Missing bars: previous-tick forward fill (only for sporadic gaps); coverage <0.90 or <15 bars → set that day's RV to NaN + flag, **keep the calendar row**.
- **Overnight is computed separately** (`ln(open_0930/prev_close_1600)`), never mixed into intraday RV.
- Daily measures: `rv5=Σr²`, `rs_minus/rs_plus`, `signed_jump`, `bv=(π/2)Σ|r_i||r_{i-1}|`, `jump=max(rv5-bv,0)`, `rq=(N/3)Σr⁴`.
- Convention: for alignment with IV use **close-to-close** (`rv5 + on_ret²`); must be consistent with evaluation / Member B's τ.
- The CFD is an SPX proxy: run a tracking-error check against `^GSPC` official daily close, check for level breaks; **tick vol cannot be used as a volume feature**.
- Output `data/processed/rv_daily.parquet` (index=ET trading day; rv5/rs±/bv/rq/on_ret/n_bars/is_half_day/quality_flag).

## Wind macro (`gold-macro-legacy.xlsx`)
- Drop VIX (IV exclusion), S&P 500 index (= redundant with the core), and all gold-specific columns; keep only the columns locked in `data-sources.json` (e.g. DXY, GPR).
- **Monthly macro is aligned to the real release date, not the Wind reference period-end** (otherwise look-ahead leakage); prefer using a release-day dummy.
- **left-join to the `^GSPC` trading calendar**, set gaps to NaN without backfilling, only add columns without changing rows.

## Forbidden
Fixed UTC offset; counting overnight into intraday RV; whole-sample-statistic filling; cross-day/backward ffill; deleting poor-quality calendar rows; using tick vol; aligning monthly frequency to the reference period-end; overwriting `data/raw/`.

> Full version + config keys + verification checklist: see `docs/ai/rules/data-cleaning.md`.