Imported from zhengkai-p-unsw/fins5557-rv-forecasting (
.claude/skills/rv-data-cleaning/SKILL.md). Install upstream withnpx skills add zhengkai-p-unsw/fins5557-rv-forecasting --skill rv-data-cleaning. Copyright stays with the author.
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 todata/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
^GSPCofficial 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
^GSPCtrading 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.
