Skip to content

README.md

Latest commit

 

History

History
103 lines (73 loc) · 3.43 KB

README.md

File metadata and controls

103 lines (73 loc) · 3.43 KB
Raw

Trade Data Cleaner

Overview

This script is built to clean and normalize messy trade data from .csv or .xlsx files. It handles real-world formatting issues including inconsistent date formats, incorrect tickers, reversed company names, percentage anomalies, and text issues.

The cleaning logic is applicable to a broad range of structured financial data.

This project is modularized into:

  • loader.py – for file reading
  • cleaner.py – for cleaning logic
  • utils.py – for shared helpers and constants
  • feature_engineer.py – for generating financial modeling features
  • model_preview.py – runs Ridge regression to test explanatory power of features
  • main.py – for running the full end-to-end pipeline

Key Features

Cleaning

  • Standardizes date formats to MM/DD/YYYY
  • Normalizes tickers, CUSIPs, ISINs, and issuer names
  • Converts percentages (%, basis points, etc.) to decimal values
  • Extracts numeric values from strings like "$105.75" or "one thousand"
  • Corrects company names
  • Cleans all text fields of Unicode and special characters
  • Drops columns that are 100% empty
  • Saves cleaned data as cleaned_(filename).csv in the same directory

Feature Engineering

If the relevant columns exist, the pipeline also generates:

  • daily_return: percent change in price
  • rolling_vol_20d: 20-day rolling standard deviation of return
  • benchmark_spread: yield - benchmark_yield
  • near_parity: binary flag for prices within $5 of par value
  • called_early: binary flag if call_date is before maturity_date
  • If par_value is missing, the script assumes a default value of 100.
  • Feature computations like called_early and benchmark_spread are only performed when required columns are present.
  • A blank column named ---- is inserted before all generated feature columns to visually separate them from the original data.

Model Preview

After feature engineering, the pipeline:

  • Detects actual_price and theoretical_price columns (e.g., price, model_price)
  • Computes price_deviation = actual - theoretical
  • Selects usable numeric features using keyword matching (e.g., volatility, spread, parity)
  • Drops rows with missing or non-numeric data
  • Trains a Ridge regression model to predict the deviation
  • Prints:
    • Test set MSE (Mean Squared Error)
    • Feature coefficients
    • 3 example predictions (actual vs predicted)

This helps assess whether your features meaningfully explain pricing error.

How to Run

  1. Open a terminal.

  2. Navigate to the folder containing main.py.

  3. Activate your virtual environment (if applicable), then run the script:

    python main.py

  4. When prompted, enter the full path to the raw input file (CSV or Excel):

    Enter file path (.csv or .xlsx): /path/to/your_file.xlsx

  5. Output will be saved as:

    cleaned_your_file.csv

Dependencies

  • Python 3.10 or higher
  • pandas
  • numpy
  • openpyxl
  • scikit-learn

Install dependencies using:

pip install -r requirements.txt

Supported Columns

The cleaner will automatically detect and process columns with names like:

  • Ticker, CUSIP, ISIN
  • Trade Date, Execution Timestamp, Call Date, Maturity Date
  • Notional Amount, Price, Quantity
  • Conversion Ratio, Coupon, Yield, Benchmark Yield
  • Issuer, Side, Venue

Column detection is based on keyword matching in column names.

Output

  • The cleaned file will be saved as a .csv in the same directory as the input file.

Contact

Jack Young
youngjh@iu.edu