{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# SPY Direction Training Dataset\n", "\n", "Build the first model-ready dataset from IBKR daily candle Parquets. The target is whether `SPY` closes above, below, or unchanged from today's close five trading days later." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Setup\n", "\n", "The notebook is deliberately plain pandas so feature logic remains easy to inspect. `VIX_INPUT_SYMBOL` defaults to `VIXY` because that is the VIX-related Parquet currently present locally; switch it to `VIX` after fetching a `VIX.parquet` file." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(PosixPath('/home/jarno/repos/trading-bot'),\n", " PosixPath('/home/jarno/repos/trading-bot/data/ibkr/daily'),\n", " PosixPath('/home/jarno/repos/trading-bot/data/training/spy_direction_5d.parquet'))" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pathlib import Path\n", "\n", "import numpy as np\n", "import pandas as pd\n", "\n", "\n", "def find_project_root(start: Path | None = None) -> Path:\n", " current = (start or Path.cwd()).resolve()\n", " for candidate in [current, *current.parents]:\n", " if (candidate / \"pyproject.toml\").exists():\n", " return candidate\n", " raise RuntimeError(\"Could not find project root containing pyproject.toml\")\n", "\n", "\n", "PROJECT_ROOT = find_project_root()\n", "RAW_DATA_DIR = PROJECT_ROOT / \"data\" / \"ibkr\" / \"daily\"\n", "OUTPUT_PATH = PROJECT_ROOT / \"data\" / \"training\" / \"spy_direction_5d.parquet\"\n", "\n", "SPY_SYMBOL = \"SPY\"\n", "VIX_INPUT_SYMBOL = \"VIXY\"\n", "TLT_SYMBOL = \"TLT\"\n", "USO_SYMBOL = \"USO\"\n", "\n", "TRAIN_FRACTION = 0.70\n", "VALIDATION_FRACTION = 0.15\n", "TEST_FRACTION = 0.15\n", "\n", "PROJECT_ROOT, RAW_DATA_DIR, OUTPUT_PATH" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Load Close Prices\n", "\n", "Each symbol file is read, sorted by trading date, and reduced to a single close-price column. `SPY` defines the observation calendar through the inner join." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "DatetimeIndex: 1251 entries, 2021-07-30 to 2026-07-24\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 SPY_close 1251 non-null float64\n", " 1 VIX_close 1251 non-null float64\n", " 2 TLT_close 1251 non-null float64\n", " 3 USO_close 1251 non-null float64\n", "dtypes: float64(4)\n", "memory usage: 48.9 KB\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPY_closeVIX_closeTLT_closeUSO_close
date
2021-07-30438.51495.4149.5250.66
2021-08-02437.59513.6150.6749.18
2021-08-03441.15488.0150.7548.85
2021-08-04438.98487.6151.0647.20
2021-08-05441.76474.8150.2948.10
\n", "
" ], "text/plain": [ " SPY_close VIX_close TLT_close USO_close\n", "date \n", "2021-07-30 438.51 495.4 149.52 50.66\n", "2021-08-02 437.59 513.6 150.67 49.18\n", "2021-08-03 441.15 488.0 150.75 48.85\n", "2021-08-04 438.98 487.6 151.06 47.20\n", "2021-08-05 441.76 474.8 150.29 48.10" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def load_close(symbol: str, alias: str | None = None) -> pd.DataFrame:\n", " alias = alias or symbol\n", " path = RAW_DATA_DIR / f\"{symbol}.parquet\"\n", " if not path.exists():\n", " raise FileNotFoundError(f\"Missing raw data file: {path}\")\n", "\n", " frame = pd.read_parquet(path)\n", " if \"date\" in frame.columns:\n", " frame = frame.set_index(\"date\")\n", " if \"close\" not in frame.columns:\n", " raise ValueError(f\"{path} does not contain a close column\")\n", "\n", " frame = frame.copy()\n", " frame.index = pd.to_datetime(frame.index)\n", " frame.index.name = \"date\"\n", " frame = frame.sort_index()\n", " return frame[[\"close\"]].rename(columns={\"close\": f\"{alias}_close\"})\n", "\n", "\n", "prices = pd.concat(\n", " [\n", " load_close(SPY_SYMBOL, \"SPY\"),\n", " load_close(VIX_INPUT_SYMBOL, \"VIX\"),\n", " load_close(TLT_SYMBOL, \"TLT\"),\n", " load_close(USO_SYMBOL, \"USO\"),\n", " ],\n", " axis=1,\n", " join=\"inner\",\n", ")\n", "\n", "prices.info()\n", "prices.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Generate Features And Target\n", "\n", "Feature values use only current and prior rows. The target looks five trading rows ahead in `SPY_close`; unchanged future prices are encoded as `0.5`." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPY_ret_5SPY_ret_20SPY_dist_sma50VIX_change_5VIX_rank_20TLT_ret_10USO_ret_5SPY_TLT_ratio_retspy_up_5d
date
2021-10-080.008336-0.017017-0.011155-0.0752390.125-0.0342390.0414950.0329981.0
2021-10-110.014114-0.026625-0.018145-0.0908690.200-0.0331350.0310150.0389081.0
2021-10-120.001201-0.023752-0.020386-0.0740910.100-0.0010410.008628-0.0013031.0
2021-10-130.000644-0.028356-0.016597-0.0810060.0500.0069280.036928-0.0058971.0
2021-10-140.008754-0.010443-0.000214-0.0967590.0500.0108090.026192-0.0119911.0
\n", "
" ], "text/plain": [ " SPY_ret_5 SPY_ret_20 SPY_dist_sma50 VIX_change_5 VIX_rank_20 \\\n", "date \n", "2021-10-08 0.008336 -0.017017 -0.011155 -0.075239 0.125 \n", "2021-10-11 0.014114 -0.026625 -0.018145 -0.090869 0.200 \n", "2021-10-12 0.001201 -0.023752 -0.020386 -0.074091 0.100 \n", "2021-10-13 0.000644 -0.028356 -0.016597 -0.081006 0.050 \n", "2021-10-14 0.008754 -0.010443 -0.000214 -0.096759 0.050 \n", "\n", " TLT_ret_10 USO_ret_5 SPY_TLT_ratio_ret spy_up_5d \n", "date \n", "2021-10-08 -0.034239 0.041495 0.032998 1.0 \n", "2021-10-11 -0.033135 0.031015 0.038908 1.0 \n", "2021-10-12 -0.001041 0.008628 -0.001303 1.0 \n", "2021-10-13 0.006928 0.036928 -0.005897 1.0 \n", "2021-10-14 0.010809 0.026192 -0.011991 1.0 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = prices.copy()\n", "\n", "df[\"SPY_ret_5\"] = df[\"SPY_close\"].pct_change(5)\n", "df[\"SPY_ret_20\"] = df[\"SPY_close\"].pct_change(20)\n", "\n", "sma_50 = df[\"SPY_close\"].rolling(50).mean()\n", "df[\"SPY_dist_sma50\"] = (df[\"SPY_close\"] - sma_50) / sma_50\n", "\n", "df[\"VIX_change_5\"] = df[\"VIX_close\"].pct_change(5)\n", "df[\"VIX_rank_20\"] = df[\"VIX_close\"].rolling(20).rank(pct=True)\n", "\n", "df[\"TLT_ret_10\"] = df[\"TLT_close\"].pct_change(10)\n", "df[\"USO_ret_5\"] = df[\"USO_close\"].pct_change(5)\n", "df[\"SPY_TLT_ratio_ret\"] = (df[\"SPY_close\"] / df[\"TLT_close\"]).pct_change(5)\n", "\n", "spy_forward_close = df[\"SPY_close\"].shift(-5)\n", "df[\"spy_up_5d\"] = np.nan\n", "df.loc[spy_forward_close > df[\"SPY_close\"], \"spy_up_5d\"] = 1.0\n", "df.loc[spy_forward_close < df[\"SPY_close\"], \"spy_up_5d\"] = 0.0\n", "df.loc[spy_forward_close == df[\"SPY_close\"], \"spy_up_5d\"] = 0.5\n", "\n", "FEATURE_COLUMNS = [\n", " \"SPY_ret_5\",\n", " \"SPY_ret_20\",\n", " \"SPY_dist_sma50\",\n", " \"VIX_change_5\",\n", " \"VIX_rank_20\",\n", " \"TLT_ret_10\",\n", " \"USO_ret_5\",\n", " \"SPY_TLT_ratio_ret\",\n", "]\n", "TARGET_COLUMN = \"spy_up_5d\"\n", "\n", "df_model = df[FEATURE_COLUMNS + [TARGET_COLUMN]].dropna().copy()\n", "df_model.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Chronological Splits\n", "\n", "Splits are assigned by row order after feature/target cleanup: oldest rows for training, newer rows for validation, newest rows for test. The `split` column is metadata and should not be used as a model input." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
rowsstart_dateend_datetarget_mean
split
train8372021-10-082025-02-070.583035
validation1792025-02-102025-10-240.653631
test1812025-10-272026-07-170.558011
\n", "
" ], "text/plain": [ " rows start_date end_date target_mean\n", "split \n", "train 837 2021-10-08 2025-02-07 0.583035\n", "validation 179 2025-02-10 2025-10-24 0.653631\n", "test 181 2025-10-27 2026-07-17 0.558011" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def assign_chronological_splits(\n", " frame: pd.DataFrame,\n", " train_fraction: float,\n", " validation_fraction: float,\n", " test_fraction: float,\n", ") -> pd.Series:\n", " total_fraction = train_fraction + validation_fraction + test_fraction\n", " if not np.isclose(total_fraction, 1.0):\n", " raise ValueError(f\"Split fractions must sum to 1.0, got {total_fraction}\")\n", "\n", " n_rows = len(frame)\n", " train_end = int(n_rows * train_fraction)\n", " validation_end = train_end + int(n_rows * validation_fraction)\n", "\n", " split = pd.Series(index=frame.index, dtype=\"object\")\n", " split.iloc[:train_end] = \"train\"\n", " split.iloc[train_end:validation_end] = \"validation\"\n", " split.iloc[validation_end:] = \"test\"\n", " return split\n", "\n", "\n", "df_model[\"split\"] = assign_chronological_splits(\n", " df_model,\n", " TRAIN_FRACTION,\n", " VALIDATION_FRACTION,\n", " TEST_FRACTION,\n", ")\n", "\n", "split_summary = df_model.groupby(\"split\", sort=False).agg(\n", " rows=(TARGET_COLUMN, \"size\"),\n", " start_date=(TARGET_COLUMN, lambda values: values.index.min().date()),\n", " end_date=(TARGET_COLUMN, lambda values: values.index.max().date()),\n", " target_mean=(TARGET_COLUMN, \"mean\"),\n", ")\n", "split_summary" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Inspect And Save\n", "\n", "`model_input_columns` is the source of truth for columns that are allowed into the model. The saved Parquet includes `date`, features, target, and split metadata." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Rows: 1,197\n", "Feature columns: ['SPY_ret_5', 'SPY_ret_20', 'SPY_dist_sma50', 'VIX_change_5', 'VIX_rank_20', 'TLT_ret_10', 'USO_ret_5', 'SPY_TLT_ratio_ret']\n", "Target column: spy_up_5d\n", "Training matrix shape: (837, 8)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPY_ret_5SPY_ret_20SPY_dist_sma50VIX_change_5VIX_rank_20TLT_ret_10USO_ret_5SPY_TLT_ratio_retspy_up_5dsplit
count1197.0000001197.0000001197.0000001197.0000001197.0000001197.0000001197.0000001197.0000001197.0000001197
uniqueNaNNaNNaNNaNNaNNaNNaNNaNNaN3
topNaNNaNNaNNaNNaNNaNNaNNaNNaNtrain
freqNaNNaNNaNNaNNaNNaNNaNNaNNaN837
mean0.0025560.0098410.011067-0.0086460.387009-0.0040830.0046620.0049270.589808NaN
std0.0230810.0436340.0373710.0924130.3336970.0285570.0523190.0276560.492074NaN
min-0.114962-0.123975-0.141995-0.3877090.050000-0.092880-0.196652-0.1172080.000000NaN
25%-0.009535-0.017017-0.008847-0.0583760.075000-0.023517-0.026200-0.0100130.000000NaN
50%0.0038690.0157970.017906-0.0206550.250000-0.0043390.0048170.0057101.000000NaN
75%0.0159570.0383850.0383320.0277780.7000000.0142720.0318830.0214061.000000NaN
max0.0828430.1575660.0881030.9378281.0000000.0913220.3272730.1292041.000000NaN
\n", "
" ], "text/plain": [ " SPY_ret_5 SPY_ret_20 SPY_dist_sma50 VIX_change_5 VIX_rank_20 \\\n", "count 1197.000000 1197.000000 1197.000000 1197.000000 1197.000000 \n", "unique NaN NaN NaN NaN NaN \n", "top NaN NaN NaN NaN NaN \n", "freq NaN NaN NaN NaN NaN \n", "mean 0.002556 0.009841 0.011067 -0.008646 0.387009 \n", "std 0.023081 0.043634 0.037371 0.092413 0.333697 \n", "min -0.114962 -0.123975 -0.141995 -0.387709 0.050000 \n", "25% -0.009535 -0.017017 -0.008847 -0.058376 0.075000 \n", "50% 0.003869 0.015797 0.017906 -0.020655 0.250000 \n", "75% 0.015957 0.038385 0.038332 0.027778 0.700000 \n", "max 0.082843 0.157566 0.088103 0.937828 1.000000 \n", "\n", " TLT_ret_10 USO_ret_5 SPY_TLT_ratio_ret spy_up_5d split \n", "count 1197.000000 1197.000000 1197.000000 1197.000000 1197 \n", "unique NaN NaN NaN NaN 3 \n", "top NaN NaN NaN NaN train \n", "freq NaN NaN NaN NaN 837 \n", "mean -0.004083 0.004662 0.004927 0.589808 NaN \n", "std 0.028557 0.052319 0.027656 0.492074 NaN \n", "min -0.092880 -0.196652 -0.117208 0.000000 NaN \n", "25% -0.023517 -0.026200 -0.010013 0.000000 NaN \n", "50% -0.004339 0.004817 0.005710 1.000000 NaN \n", "75% 0.014272 0.031883 0.021406 1.000000 NaN \n", "max 0.091322 0.327273 0.129204 1.000000 NaN " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "model_input_columns = FEATURE_COLUMNS\n", "model_target_column = TARGET_COLUMN\n", "\n", "X_train = df_model.loc[df_model[\"split\"] == \"train\", model_input_columns]\n", "y_train = df_model.loc[df_model[\"split\"] == \"train\", model_target_column]\n", "\n", "print(f\"Rows: {len(df_model):,}\")\n", "print(f\"Feature columns: {model_input_columns}\")\n", "print(f\"Target column: {model_target_column}\")\n", "print(f\"Training matrix shape: {X_train.shape}\")\n", "\n", "df_model.describe(include=\"all\")" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(PosixPath('/home/jarno/repos/trading-bot/data/training/spy_direction_5d.parquet'),\n", " (1197, 11),\n", " date SPY_ret_5 SPY_ret_20 SPY_dist_sma50 VIX_change_5 \\\n", " 0 2021-10-08 0.008336 -0.017017 -0.011155 -0.075239 \n", " 1 2021-10-11 0.014114 -0.026625 -0.018145 -0.090869 \n", " 2 2021-10-12 0.001201 -0.023752 -0.020386 -0.074091 \n", " 3 2021-10-13 0.000644 -0.028356 -0.016597 -0.081006 \n", " 4 2021-10-14 0.008754 -0.010443 -0.000214 -0.096759 \n", " \n", " VIX_rank_20 TLT_ret_10 USO_ret_5 SPY_TLT_ratio_ret spy_up_5d split \n", " 0 0.125 -0.034239 0.041495 0.032998 1.0 train \n", " 1 0.200 -0.033135 0.031015 0.038908 1.0 train \n", " 2 0.100 -0.001041 0.008628 -0.001303 1.0 train \n", " 3 0.050 0.006928 0.036928 -0.005897 1.0 train \n", " 4 0.050 0.010809 0.026192 -0.011991 1.0 train )" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "OUTPUT_PATH.parent.mkdir(parents=True, exist_ok=True)\n", "dataset_to_save = df_model.reset_index()\n", "dataset_to_save.to_parquet(OUTPUT_PATH, index=False)\n", "\n", "OUTPUT_PATH, dataset_to_save.shape, dataset_to_save.head()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.15" } }, "nbformat": 4, "nbformat_minor": 5 }