{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CLV Research — Improved Methodology\n",
    "\n",
    "**Fixes vs original notebook:**\n",
    "1. **Kelly probability corrected** — uses `pin_fair` as true win probability, not `1/odds`\n",
    "2. **Positive CLV filter** — only bets when CLV exceeds a threshold; no longer always picks \"least bad\" side\n",
    "3. **Single snapshot per game at target window** — picks the snapshot closest to a configurable time before kickoff, not the earliest\n",
    "4. **Temporal analysis** — sweeps target windows from 60–1440 min to find optimal signal timing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker as mticker\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "pd.set_option('display.float_format', '{:.4f}'.format)\n",
    "pd.set_option('display.max_columns', 40)\n",
    "\n",
    "# ── Config ──────────────────────────────────────────────────────────────────\n",
    "DATA_DIR       = 'E:/soccer/live_data/'\n",
    "START_BANKROLL = 1000.0\n",
    "KELLY_FRAC     = 0.25          # fractional Kelly multiplier\n",
    "MIN_STAKE      = 1.0           # Kelly floor\n",
    "CLV_THRESHOLD  = 0.05          # minimum CLV to place a bet (5%)\n",
    "TARGET_MINS    = 480           # default snapshot window: 8 h before kickoff\n",
    "DRAW_EXCLUDED  = True          # draws excluded (no edge historically)\n",
    "\n",
    "print('Config loaded.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Load & Prepare Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "from pathlib import Path\n\ndata_dir = Path(DATA_DIR)\nfiles = sorted(data_dir.glob('2026-*.csv'))\nprint(f'Found {len(files)} CSV files: {[f.name for f in files]}')\n\nif not files:\n    raise FileNotFoundError(f'No CSVs found in {data_dir.resolve()} — check DATA_DIR in cell 1')\n\nraw = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)\n\n# ── Filter to PRE rows only ──────────────────────────────────────────────────\npre = raw[raw['status'] == 'PRE'].copy()\n\n# elapsed is negative (minutes before kickoff). Convert to positive for clarity.\npre['mins_before_ko'] = -pre['elapsed']\n\n# Drop rows without Pinnacle fair odds (no signal possible)\npre = pre.dropna(subset=['pin_fair_home', 'pin_fair_away', 'clv_b365_home', 'clv_b365_away'])\n\n# Propagate ftr to all PRE rows of same game (ftr filled in on FT row, not PRE)\ngame_key = ['game_date', 'home', 'away']\nftr_map = (\n    raw.dropna(subset=['ftr'])\n       .groupby(game_key)['ftr'].first()\n)\npre = pre.join(ftr_map, on=game_key, rsuffix='_filled')\npre['ftr'] = pre['ftr'].combine_first(pre.get('ftr_filled'))\nif 'ftr_filled' in pre.columns:\n    pre.drop(columns=['ftr_filled'], inplace=True)\n\npre = pre.dropna(subset=['ftr'])\n\nprint(f'PRE rows after cleaning: {len(pre):,}')\nprint(f'Unique games: {pre.groupby(game_key).ngroups:,}')\nprint(f\"FTR distribution:\\n{pre.groupby(game_key)['ftr'].first().value_counts()}\")"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. CLV Distribution\n",
    "\n",
    "Before deciding on a threshold, look at the distribution of CLV values across all PRE snapshots."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, axes = plt.subplots(1, 2, figsize=(14, 4))\n",
    "\n",
    "for ax, col, title in [\n",
    "    (axes[0], 'clv_b365_home', 'B365 Home CLV'),\n",
    "    (axes[1], 'clv_b365_away', 'B365 Away CLV'),\n",
    "]:\n",
    "    vals = pre[col].dropna()\n",
    "    ax.hist(vals, bins=60, color='steelblue', edgecolor='white', alpha=0.85)\n",
    "    ax.axvline(CLV_THRESHOLD, color='red', linestyle='--', label=f'Threshold {CLV_THRESHOLD:.0%}')\n",
    "    ax.axvline(0, color='black', linestyle='-', linewidth=0.8)\n",
    "    ax.set_title(title)\n",
    "    ax.set_xlabel('CLV')\n",
    "    ax.set_ylabel('Rows')\n",
    "    ax.legend(fontsize=9)\n",
    "    pct_above = (vals > CLV_THRESHOLD).mean()\n",
    "    ax.text(0.98, 0.95, f'{pct_above:.1%} above threshold',\n",
    "            transform=ax.transAxes, ha='right', va='top', fontsize=9, color='red')\n",
    "\n",
    "plt.suptitle('B365 CLV Distribution — All PRE Snapshots', y=1.02, fontsize=13, fontweight='bold')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# Kalshi CLV if available\n",
    "k_cols = ['clv_kalshi_home', 'clv_kalshi_away']\n",
    "k_data = pre.dropna(subset=k_cols)\n",
    "print(f'Rows with Kalshi CLV: {len(k_data):,} ({len(k_data)/len(pre):.1%} of PRE rows)')\n",
    "\n",
    "if len(k_data) > 0:\n",
    "    fig, axes = plt.subplots(1, 2, figsize=(14, 4))\n",
    "    for ax, col, title in [\n",
    "        (axes[0], 'clv_kalshi_home', 'Kalshi Home CLV'),\n",
    "        (axes[1], 'clv_kalshi_away', 'Kalshi Away CLV'),\n",
    "    ]:\n",
    "        vals = k_data[col].dropna()\n",
    "        ax.hist(vals, bins=60, color='darkorange', edgecolor='white', alpha=0.85)\n",
    "        ax.axvline(CLV_THRESHOLD, color='red', linestyle='--', label=f'Threshold {CLV_THRESHOLD:.0%}')\n",
    "        ax.axvline(0, color='black', linestyle='-', linewidth=0.8)\n",
    "        ax.set_title(title)\n",
    "        ax.set_xlabel('CLV')\n",
    "        ax.legend(fontsize=9)\n",
    "    plt.suptitle('Kalshi CLV Distribution — All PRE Snapshots', y=1.02, fontsize=13, fontweight='bold')\n",
    "    plt.tight_layout()\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Snapshot Selection\n",
    "\n",
    "**Fix**: For each game, pick the single PRE snapshot closest to `TARGET_MINS` before kickoff. This ensures:\n",
    "- One bet opportunity per game (no double-counting)\n",
    "- We evaluate the signal at a consistent point in time\n",
    "- The temporal sweep in Section 5 can meaningfully compare different windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def select_snapshot_at(df, target_mins):\n",
    "    \"\"\"\n",
    "    For each game, return the single PRE row closest to `target_mins` before kickoff.\n",
    "    Only keeps rows where mins_before_ko >= target_mins (i.e. signal taken early enough).\n",
    "    If no row exists at or before target_mins for a game, that game is excluded.\n",
    "    \"\"\"\n",
    "    # Only consider snapshots taken at or before the target window\n",
    "    eligible = df[df['mins_before_ko'] >= target_mins].copy()\n",
    "    # Distance from target\n",
    "    eligible['dist'] = (eligible['mins_before_ko'] - target_mins).abs()\n",
    "    # Pick closest per game\n",
    "    idx = eligible.groupby(['game_date', 'home', 'away'])['dist'].idxmin()\n",
    "    return eligible.loc[idx].drop(columns='dist').reset_index(drop=True)\n",
    "\n",
    "snap = select_snapshot_at(pre, TARGET_MINS)\n",
    "print(f'Games with snapshot at ~{TARGET_MINS} min window: {len(snap)}')\n",
    "print(f'Actual mins_before_ko distribution:')\n",
    "print(snap['mins_before_ko'].describe().round(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Kelly Sizing — Corrected\n",
    "\n",
    "**Fix**: The original notebook used `1/odds` as the win probability, which is just the implied probability (gives near-zero Kelly fraction — it assumes no edge).\n",
    "\n",
    "Corrected formula:\n",
    "- `p = pin_fair_home` (true win probability from Pinnacle after removing overround)\n",
    "- `b = decimal_odds - 1` (net return per unit staked)\n",
    "- `f = (p·b − (1−p)) / b`\n",
    "- `stake = bankroll × f × KELLY_FRAC`, floored at MIN_STAKE when f > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def kelly_stake(p_win, decimal_odds, bankroll, kelly_frac=KELLY_FRAC, min_stake=MIN_STAKE):\n",
    "    \"\"\"Fractional Kelly stake. Returns 0 if no edge.\"\"\"\n",
    "    if pd.isna(p_win) or pd.isna(decimal_odds) or decimal_odds <= 1:\n",
    "        return 0.0\n",
    "    b = decimal_odds - 1\n",
    "    f = (p_win * b - (1 - p_win)) / b\n",
    "    if f <= 0:\n",
    "        return 0.0\n",
    "    stake = bankroll * f * kelly_frac\n",
    "    return max(stake, min_stake)\n",
    "\n",
    "# Quick sanity check\n",
    "print('Kelly sanity checks:')\n",
    "print(f'  p=0.50, odds=2.20 → f={(0.5*1.2-0.5)/1.2:.3f}, stake/1000=${kelly_stake(0.50, 2.20, 1000):.2f}')\n",
    "print(f'  p=0.35, odds=4.00 → f={(0.35*3-0.65)/3:.3f}, stake/1000=${kelly_stake(0.35, 4.00, 1000):.2f}')\n",
    "print(f'  p=0.30, odds=2.00 → f={(0.30*1-0.70)/1:.3f}, stake/1000=${kelly_stake(0.30, 2.00, 1000):.2f} (no edge)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Strategy Backtests\n",
    "\n",
    "Three strategies, each filtered to `CLV > CLV_THRESHOLD` before betting:\n",
    "\n",
    "| Strategy | Signal | Execution |\n",
    "|----------|--------|-----------|\n",
    "| **B365** | B365 CLV vs Pinnacle fair | Bet B365 odds |\n",
    "| **Kalshi** | Kalshi CLV vs Pinnacle fair | Bet Kalshi odds (mid) |\n",
    "| **B365→Kalshi** | B365 CLV vs Pinnacle fair | Execute on Kalshi (when available) |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_backtest(df, strategy='b365', clv_threshold=CLV_THRESHOLD, kelly_frac=KELLY_FRAC):\n",
    "    \"\"\"\n",
    "    Simulate sequential Kelly bets on the given snapshot set.\n",
    "    \n",
    "    strategy options:\n",
    "      'b365'         — B365 CLV signal, bet at B365 odds\n",
    "      'kalshi'       — Kalshi CLV signal, bet at Kalshi mid\n",
    "      'b365_kalshi'  — B365 CLV signal, execute at Kalshi mid (requires kalshi price)\n",
    "    \n",
    "    Returns: list of bet dicts (for analysis), final bankroll\n",
    "    \"\"\"\n",
    "    bankroll = START_BANKROLL\n",
    "    bets = []\n",
    "\n",
    "    for _, row in df.sort_values('timestamp').iterrows():\n",
    "        ftr = row['ftr']\n",
    "        if pd.isna(ftr):\n",
    "            continue\n",
    "\n",
    "        candidates = []\n",
    "\n",
    "        if strategy in ('b365', 'b365_kalshi'):\n",
    "            # Home signal\n",
    "            clv_h = row.get('clv_b365_home', np.nan)\n",
    "            if not DRAW_EXCLUDED or True:  # Home bets always considered\n",
    "                if pd.notna(clv_h) and clv_h > clv_threshold:\n",
    "                    if strategy == 'b365':\n",
    "                        exec_odds = row.get('b365_home')\n",
    "                    else:\n",
    "                        exec_odds = row.get('kalshi_home_mid')\n",
    "                    candidates.append(('H', clv_h, row['pin_fair_home'], exec_odds))\n",
    "            # Away signal\n",
    "            clv_a = row.get('clv_b365_away', np.nan)\n",
    "            if pd.notna(clv_a) and clv_a > clv_threshold:\n",
    "                if strategy == 'b365':\n",
    "                    exec_odds = row.get('b365_away')\n",
    "                else:\n",
    "                    exec_odds = row.get('kalshi_away_mid')\n",
    "                candidates.append(('A', clv_a, row['pin_fair_away'], exec_odds))\n",
    "\n",
    "        elif strategy == 'kalshi':\n",
    "            clv_h = row.get('clv_kalshi_home', np.nan)\n",
    "            if pd.notna(clv_h) and clv_h > clv_threshold:\n",
    "                # Kalshi CLV is pin_fair_prob vs kalshi_mid; for Kelly we need decimal odds\n",
    "                mid = row.get('kalshi_home_mid')\n",
    "                if pd.notna(mid) and mid > 0:\n",
    "                    exec_odds = 1 / mid  # convert probability to decimal odds\n",
    "                    candidates.append(('H', clv_h, row['pin_fair_home'], exec_odds))\n",
    "            clv_a = row.get('clv_kalshi_away', np.nan)\n",
    "            if pd.notna(clv_a) and clv_a > clv_threshold:\n",
    "                mid = row.get('kalshi_away_mid')\n",
    "                if pd.notna(mid) and mid > 0:\n",
    "                    exec_odds = 1 / mid\n",
    "                    candidates.append(('A', clv_a, row['pin_fair_away'], exec_odds))\n",
    "\n",
    "        # If multiple signals on same game, take the highest CLV (one bet per game)\n",
    "        if not candidates:\n",
    "            continue\n",
    "        side, clv, p_win, exec_odds = max(candidates, key=lambda x: x[1])\n",
    "\n",
    "        if DRAW_EXCLUDED and side == 'D':\n",
    "            continue\n",
    "        if pd.isna(exec_odds) or exec_odds is None:\n",
    "            continue\n",
    "\n",
    "        stake = kelly_stake(p_win, exec_odds, bankroll, kelly_frac)\n",
    "        if stake == 0:\n",
    "            continue\n",
    "\n",
    "        stake = min(stake, bankroll * 0.25)  # hard cap 25% bankroll\n",
    "        won = (ftr == side)\n",
    "        pnl = stake * (exec_odds - 1) if won else -stake\n",
    "        bankroll += pnl\n",
    "\n",
    "        bets.append({\n",
    "            'date': row['game_date'],\n",
    "            'home': row['home'],\n",
    "            'away': row['away'],\n",
    "            'league': row['league'],\n",
    "            'side': side,\n",
    "            'ftr': ftr,\n",
    "            'won': won,\n",
    "            'clv': clv,\n",
    "            'p_win': p_win,\n",
    "            'exec_odds': exec_odds,\n",
    "            'stake': stake,\n",
    "            'pnl': pnl,\n",
    "            'bankroll': bankroll,\n",
    "            'mins_before_ko': row['mins_before_ko'],\n",
    "        })\n",
    "\n",
    "    return pd.DataFrame(bets), bankroll\n",
    "\n",
    "\n",
    "# Run all three strategies on the default TARGET_MINS snapshot\n",
    "results = {}\n",
    "for strat in ('b365', 'kalshi', 'b365_kalshi'):\n",
    "    df_snap = snap.copy()\n",
    "    # Kalshi strategy needs Kalshi data available\n",
    "    if strat == 'kalshi':\n",
    "        df_snap = df_snap.dropna(subset=['clv_kalshi_home', 'clv_kalshi_away'])\n",
    "    elif strat == 'b365_kalshi':\n",
    "        df_snap = df_snap.dropna(subset=['kalshi_home_mid', 'kalshi_away_mid'])\n",
    "    bets_df, final_br = run_backtest(df_snap, strategy=strat)\n",
    "    results[strat] = (bets_df, final_br)\n",
    "    print(f\"[{strat:12s}]  bets={len(bets_df):3d}  final=${final_br:,.2f}  \", end='')\n",
    "    if len(bets_df):\n",
    "        wr = bets_df['won'].mean()\n",
    "        roi = bets_df['pnl'].sum() / bets_df['stake'].sum()\n",
    "        print(f\"WR={wr:.1%}  ROI={roi:+.1%}\")\n",
    "    else:\n",
    "        print('no bets')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ── Equity curves ────────────────────────────────────────────────────────────\n",
    "fig, ax = plt.subplots(figsize=(13, 5))\n",
    "\n",
    "colors = {'b365': 'steelblue', 'kalshi': 'darkorange', 'b365_kalshi': 'seagreen'}\n",
    "labels = {'b365': 'B365 signal → B365 odds', 'kalshi': 'Kalshi signal → Kalshi odds',\n",
    "          'b365_kalshi': 'B365 signal → Kalshi odds'}\n",
    "\n",
    "for strat, (bets_df, _) in results.items():\n",
    "    if len(bets_df) == 0:\n",
    "        continue\n",
    "    curve = pd.concat([pd.DataFrame([{'bankroll': START_BANKROLL}]), bets_df[['bankroll']]])\n",
    "    ax.plot(range(len(curve)), curve['bankroll'], label=labels[strat], color=colors[strat], linewidth=2)\n",
    "\n",
    "ax.axhline(START_BANKROLL, color='gray', linestyle='--', linewidth=0.8, label='Start')\n",
    "ax.set_xlabel('Bet number')\n",
    "ax.set_ylabel('Bankroll ($)')\n",
    "ax.set_title(f'Equity Curves — CLV>{CLV_THRESHOLD:.0%} filter, Pinnacle-Kelly {KELLY_FRAC:.0%}, ~{TARGET_MINS} min window',\n",
    "             fontsize=12, fontweight='bold')\n",
    "ax.legend()\n",
    "ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}'))\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ── Summary table ────────────────────────────────────────────────────────────\n",
    "rows = []\n",
    "for strat, (bets_df, final_br) in results.items():\n",
    "    if len(bets_df) == 0:\n",
    "        rows.append({'Strategy': labels[strat], 'Bets': 0})\n",
    "        continue\n",
    "    pnl = bets_df['pnl'].sum()\n",
    "    stakes = bets_df['stake'].sum()\n",
    "    wr = bets_df['won'].mean()\n",
    "    roi = pnl / stakes if stakes > 0 else 0\n",
    "    avg_odds = bets_df['exec_odds'].mean()\n",
    "    avg_clv = bets_df['clv'].mean()\n",
    "    rows.append({\n",
    "        'Strategy': labels[strat],\n",
    "        'Bets': len(bets_df),\n",
    "        'WR': f'{wr:.1%}',\n",
    "        'Avg Odds': f'{avg_odds:.2f}',\n",
    "        'Avg CLV': f'{avg_clv:.2%}',\n",
    "        'Total Staked': f'${stakes:,.0f}',\n",
    "        'Net P&L': f'${pnl:+,.2f}',\n",
    "        'ROI': f'{roi:+.1%}',\n",
    "        'Final Bank': f'${final_br:,.2f}',\n",
    "    })\n",
    "\n",
    "pd.DataFrame(rows).set_index('Strategy')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. CLV Threshold Sensitivity\n",
    "\n",
    "Sweep thresholds from 0% to 15% and see how bet count, ROI, and final bankroll change."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "thresholds = np.arange(0.00, 0.16, 0.01)\n",
    "sweep_rows = []\n",
    "\n",
    "for thresh in thresholds:\n",
    "    for strat in ('b365', 'b365_kalshi'):\n",
    "        df_snap = snap.copy()\n",
    "        if strat == 'b365_kalshi':\n",
    "            df_snap = df_snap.dropna(subset=['kalshi_home_mid', 'kalshi_away_mid'])\n",
    "        bets_df, final_br = run_backtest(df_snap, strategy=strat, clv_threshold=thresh)\n",
    "        if len(bets_df) == 0:\n",
    "            continue\n",
    "        stakes = bets_df['stake'].sum()\n",
    "        roi = bets_df['pnl'].sum() / stakes if stakes > 0 else 0\n",
    "        sweep_rows.append({\n",
    "            'threshold': thresh,\n",
    "            'strategy': strat,\n",
    "            'bets': len(bets_df),\n",
    "            'roi': roi,\n",
    "            'final_br': final_br,\n",
    "        })\n",
    "\n",
    "sweep = pd.DataFrame(sweep_rows)\n",
    "\n",
    "fig, axes = plt.subplots(1, 3, figsize=(16, 4))\n",
    "\n",
    "for strat, color, label in [('b365', 'steelblue', 'B365→B365'), ('b365_kalshi', 'seagreen', 'B365→Kalshi')]:\n",
    "    sub = sweep[sweep['strategy'] == strat]\n",
    "    axes[0].plot(sub['threshold'] * 100, sub['bets'], color=color, marker='o', markersize=4, label=label)\n",
    "    axes[1].plot(sub['threshold'] * 100, sub['roi'] * 100, color=color, marker='o', markersize=4, label=label)\n",
    "    axes[2].plot(sub['threshold'] * 100, sub['final_br'], color=color, marker='o', markersize=4, label=label)\n",
    "\n",
    "axes[0].set_title('Bet Count'); axes[0].set_xlabel('CLV Threshold (%)')\n",
    "axes[1].set_title('ROI (%)'); axes[1].set_xlabel('CLV Threshold (%)')\n",
    "axes[1].axhline(0, color='gray', linestyle='--', linewidth=0.8)\n",
    "axes[2].set_title('Final Bankroll ($)'); axes[2].set_xlabel('CLV Threshold (%)')\n",
    "axes[2].axhline(START_BANKROLL, color='gray', linestyle='--', linewidth=0.8)\n",
    "\n",
    "for ax in axes:\n",
    "    ax.legend(fontsize=9)\n",
    "    ax.grid(alpha=0.3)\n",
    "\n",
    "plt.suptitle('CLV Threshold Sweep — B365→B365 vs B365→Kalshi', fontsize=12, fontweight='bold', y=1.02)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Temporal Window Analysis\n",
    "\n",
    "**Fix**: Sweep `target_mins` from 60 to 1440 min before kickoff. For each window, select the appropriate snapshot and run the B365 strategy. This answers: *does CLV signal quality vary by how far out we observe it?*\n",
    "\n",
    "Note: fewer games qualify as target_mins increases (some games don't have snapshots that far out)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "windows = [60, 120, 180, 240, 360, 480, 600, 720, 900, 1080, 1200, 1350]\n",
    "window_rows = []\n",
    "\n",
    "for win in windows:\n",
    "    snap_w = select_snapshot_at(pre, win)\n",
    "    if len(snap_w) < 5:\n",
    "        continue\n",
    "    bets_df, final_br = run_backtest(snap_w, strategy='b365', clv_threshold=CLV_THRESHOLD)\n",
    "    n_games = len(snap_w)\n",
    "    n_bets = len(bets_df)\n",
    "    stakes = bets_df['stake'].sum() if n_bets > 0 else 0\n",
    "    roi = bets_df['pnl'].sum() / stakes if stakes > 0 else 0\n",
    "    wr = bets_df['won'].mean() if n_bets > 0 else np.nan\n",
    "    window_rows.append({\n",
    "        'window_hrs': win / 60,\n",
    "        'games': n_games,\n",
    "        'bets': n_bets,\n",
    "        'signal_rate': n_bets / n_games if n_games else 0,\n",
    "        'win_rate': wr,\n",
    "        'roi': roi,\n",
    "        'final_br': final_br,\n",
    "    })\n",
    "\n",
    "wdf = pd.DataFrame(window_rows)\n",
    "print(wdf.to_string(index=False, float_format='{:.3f}'.format))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, axes = plt.subplots(2, 2, figsize=(14, 8))\n",
    "\n",
    "ax = axes[0, 0]\n",
    "ax.bar(wdf['window_hrs'], wdf['games'], color='lightblue', edgecolor='steelblue', alpha=0.8, label='Games')\n",
    "ax.bar(wdf['window_hrs'], wdf['bets'], color='steelblue', edgecolor='navy', alpha=0.9, label='Bets placed')\n",
    "ax.set_title('Games available & Bets placed')\n",
    "ax.set_xlabel('Hours before kickoff')\n",
    "ax.legend()\n",
    "\n",
    "ax = axes[0, 1]\n",
    "ax.plot(wdf['window_hrs'], wdf['signal_rate'] * 100, color='purple', marker='o')\n",
    "ax.axhline(0, color='gray', linestyle='--', linewidth=0.8)\n",
    "ax.set_title(f'Signal Rate (CLV>{CLV_THRESHOLD:.0%})')\n",
    "ax.set_xlabel('Hours before kickoff')\n",
    "ax.set_ylabel('%')\n",
    "\n",
    "ax = axes[1, 0]\n",
    "ax.plot(wdf['window_hrs'], wdf['roi'] * 100, color='steelblue', marker='o')\n",
    "ax.axhline(0, color='gray', linestyle='--', linewidth=0.8)\n",
    "ax.set_title('ROI by window')\n",
    "ax.set_xlabel('Hours before kickoff')\n",
    "ax.set_ylabel('ROI (%)')\n",
    "\n",
    "ax = axes[1, 1]\n",
    "ax.plot(wdf['window_hrs'], wdf['final_br'], color='seagreen', marker='o')\n",
    "ax.axhline(START_BANKROLL, color='gray', linestyle='--', linewidth=0.8, label='Start')\n",
    "ax.set_title('Final Bankroll by window')\n",
    "ax.set_xlabel('Hours before kickoff')\n",
    "ax.set_ylabel('$')\n",
    "ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}'))\n",
    "ax.legend()\n",
    "\n",
    "plt.suptitle(f'Temporal Window Analysis — B365 strategy, CLV>{CLV_THRESHOLD:.0%}',\n",
    "             fontsize=13, fontweight='bold', y=1.01)\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "best = wdf.loc[wdf['final_br'].idxmax()]\n",
    "print(f\"Best window: {best['window_hrs']:.1f} h before kickoff  \"\n",
    "      f\"({best['bets']:.0f} bets, {best['roi']:+.1%} ROI, ${best['final_br']:,.2f})\")\n",
    "print(\"\\n⚠️  WARNING: With <200 bets total these results are not statistically significant.\")\n",
    "print(\"    Treat the temporal finding as a hypothesis to monitor, not a conclusion.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Per-League Breakdown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bets_b365, _ = results['b365']\n",
    "\n",
    "if len(bets_b365) > 0:\n",
    "    league_stats = (\n",
    "        bets_b365\n",
    "        .groupby('league')\n",
    "        .agg(\n",
    "            bets=('pnl', 'count'),\n",
    "            win_rate=('won', 'mean'),\n",
    "            avg_clv=('clv', 'mean'),\n",
    "            avg_odds=('exec_odds', 'mean'),\n",
    "            total_pnl=('pnl', 'sum'),\n",
    "            total_staked=('stake', 'sum'),\n",
    "        )\n",
    "    )\n",
    "    league_stats['roi'] = league_stats['total_pnl'] / league_stats['total_staked']\n",
    "    league_stats = league_stats.sort_values('bets', ascending=False)\n",
    "    \n",
    "    # Format\n",
    "    display_df = league_stats.copy()\n",
    "    display_df['win_rate'] = display_df['win_rate'].map('{:.1%}'.format)\n",
    "    display_df['avg_clv'] = display_df['avg_clv'].map('{:.2%}'.format)\n",
    "    display_df['avg_odds'] = display_df['avg_odds'].map('{:.2f}'.format)\n",
    "    display_df['total_pnl'] = display_df['total_pnl'].map('${:+,.2f}'.format)\n",
    "    display_df['total_staked'] = display_df['total_staked'].map('${:,.0f}'.format)\n",
    "    display_df['roi'] = display_df['roi'].map('{:+.1%}'.format)\n",
    "    display_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Bet-Level Detail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(bets_b365) > 0:\n",
    "    show = bets_b365[['date', 'league', 'home', 'away', 'side', 'ftr', 'won',\n",
    "                       'clv', 'p_win', 'exec_odds', 'stake', 'pnl', 'bankroll',\n",
    "                       'mins_before_ko']].copy()\n",
    "    show['clv'] = show['clv'].map('{:.2%}'.format)\n",
    "    show['p_win'] = show['p_win'].map('{:.3f}'.format)\n",
    "    show['exec_odds'] = show['exec_odds'].map('{:.2f}'.format)\n",
    "    show['stake'] = show['stake'].map('${:.2f}'.format)\n",
    "    show['pnl'] = show['pnl'].map('${:+.2f}'.format)\n",
    "    show['bankroll'] = show['bankroll'].map('${:,.2f}'.format)\n",
    "    show['mins_before_ko'] = show['mins_before_ko'].map('{:.0f} min'.format)\n",
    "    show"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Caveats & Next Steps\n",
    "\n",
    "**What this analysis can and cannot tell you:**\n",
    "\n",
    "| Item | Status |\n",
    "|------|--------|\n",
    "| Sample size | ~2 weeks of data. ROI estimates have huge confidence intervals. Need 3–6 months minimum before conclusions. |\n",
    "| Kelly p_win source | Using Pinnacle fair probability. This is the best available public estimate of true probability — same as used in live pipeline. |\n",
    "| Temporal finding | Best window result is almost certainly noise at this sample size. Flag for re-evaluation after 500+ games. |\n",
    "| CLV threshold | 5% is the live pipeline threshold. The threshold sweep shows sensitivity — monitor whether a different value outperforms at larger N. |\n",
    "| Kalshi execution | Mid-price used for simulation. Live execution would be at ask, so Kalshi P&L will be lower than shown here. |\n",
    "| Lookahead bias | None — each snapshot is taken at a point in time before kickoff, ftr is post-game. |\n",
    "\n",
    "**Suggested follow-ups once more data accumulates:**\n",
    "1. Run this notebook monthly and track whether ROI trend is positive\n",
    "2. Compare `B365 signal → Kalshi odds` against live Kalshi bankroll performance\n",
    "3. Test whether restricting to Away-only (CLV >5%) matches backtest findings\n",
    "4. Add confidence intervals to the temporal window chart using bootstrap resampling"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}