""" Cashout simulation for pure Away CLV strategy. Loads OOS bets from bets_full_breakdown.csv, matches HT scores from league CSVs, then compares hold-all vs various cashout rules. """ import pandas as pd import numpy as np START = 1_000.0 KELLY_MULTS = [0.10, 0.25, 0.50] seasons = ['1920','2021','2122','2223','2324','2425','2526'] league_names = { 'E0': 'EPL', 'E1': 'Championship', 'E2': 'League One', 'E3': 'League Two', 'EC': 'Conference', 'SP1': 'La Liga', 'SP2': 'Segunda', 'I1': 'Serie A', 'I2': 'Serie B', 'D1': 'Bundesliga', 'D2': '2.Bundesliga', 'F1': 'Ligue 1', 'F2': 'Ligue 2', 'N1': 'Eredivisie', 'B1': 'Belgian Pro League', 'P1': 'Primeira Liga', 'T1': 'Super Lig', 'G1': 'Greek SL', 'SC0': 'Scottish Prem', 'SC1': 'Scottish Champ', 'SC2': 'Scottish L1', 'SC3': 'Scottish L2', } # ── Load league data for HT scores ─────────────────────────────────────────── print("Loading HT scores from league data...") all_dfs = [] for code, name in league_names.items(): for s in seasons: path = f'E:/soccer/league_data/{code}_{s}.csv' try: d = pd.read_csv(path, encoding='utf-8-sig') needed = ['Date','HomeTeam','AwayTeam','HTHG','HTAG'] if not all(c in d.columns for c in needed): continue d = d.dropna(subset=['HTHG','HTAG']) d['Date'] = pd.to_datetime(d['Date'], dayfirst=True, errors='coerce') d = d.dropna(subset=['Date']) d['LeagueCode'] = code all_dfs.append(d[needed + ['LeagueCode']].copy()) except: pass ht_df = pd.concat(all_dfs, ignore_index=True) ht_df['HTHG'] = pd.to_numeric(ht_df['HTHG'], errors='coerce') ht_df['HTAG'] = pd.to_numeric(ht_df['HTAG'], errors='coerce') ht_df = ht_df.dropna(subset=['HTHG','HTAG']) # Build lookup: (date, home_lower, away_lower) -> (hthg, htag) ht_lookup = {} for _, row in ht_df.iterrows(): key = (row['Date'].date(), row['HomeTeam'].lower().strip(), row['AwayTeam'].lower().strip()) ht_lookup[key] = (int(row['HTHG']), int(row['HTAG'])) print(f" {len(ht_lookup):,} matches with HT scores") # ── Load bets ──────────────────────────────────────────────────────────────── bets = pd.read_csv(r'E:\soccer\bets_full_breakdown.csv') bets['date_dt'] = pd.to_datetime(bets['date']) print(f" {len(bets):,} OOS Away CLV bets") # ── Match HT scores ────────────────────────────────────────────────────────── matched = 0 ht_home_list, ht_away_list = [], [] for _, row in bets.iterrows(): dt = row['date_dt'].date() h = str(row['home_team']).lower().strip() a = str(row['away_team']).lower().strip() found = None for delta in [0, 1, -1]: from datetime import timedelta key = (dt + timedelta(days=delta), h, a) if key in ht_lookup: found = ht_lookup[key] break if found: ht_home_list.append(found[0]) ht_away_list.append(found[1]) matched += 1 else: ht_home_list.append(np.nan) ht_away_list.append(np.nan) bets['ht_home'] = ht_home_list bets['ht_away'] = ht_away_list bets_ht = bets[bets['ht_home'].notna()].copy() bets_ht['ht_deficit'] = bets_ht['ht_away'] - bets_ht['ht_home'] # positive = away winning at HT print(f" Matched HT scores: {matched:,} / {len(bets):,} ({matched/len(bets)*100:.1f}%)") # ── HT situation breakdown ─────────────────────────────────────────────────── print(f"\nHT situation breakdown (away team perspective):") print(f" {'Situation':<12} {'n':>5} {'FT_win%':>8} {'Flat_ROI':>9} {'avg_odds':>9}") for deficit, label in [(-3,'down 3+'),(-2,'down 2'),(-1,'down 1'),(0,'level'),(1,'ahead 1'),(2,'ahead 2+')]: if deficit <= -3: sub = bets_ht[bets_ht['ht_deficit'] <= -3] elif deficit >= 2: sub = bets_ht[bets_ht['ht_deficit'] >= 2] else: sub = bets_ht[bets_ht['ht_deficit'] == deficit] if len(sub) == 0: continue wr = sub['won'].mean() * 100 roi = sub['flat_pl'].mean() * 100 avg = sub['odds'].mean() print(f" {label:<12} {len(sub):5d} {wr:7.1f}% {roi:+8.1f}% {avg:9.2f}") # ── Strategy runner ────────────────────────────────────────────────────────── def run_strategy(bets_df, rules, km): """Run Kelly compounding with optional cashout rules. rules: dict of deficit -> recovery fraction (e.g. {-1: 0.50} = recover 50% of stake when down 1) If rules is None, hold all bets.""" bk = START; peak = START; max_dd = 0.0 n_co = saved = missed = 0 for _, row in bets_df.iterrows(): f = min(row['kelly_fraction'] * km, 0.25) stake = bk * f d = int(row['ht_deficit']) co_key = max(d, -3) if rules and d < 0 and co_key in rules: pl = -stake * (1 - rules[co_key]) n_co += 1 if row['won']: missed += 1 else: saved += 1 else: pl = stake * (row['odds'] - 1) if row['won'] else -stake bk = max(bk + pl, 0) peak = max(peak, bk) max_dd = max(max_dd, (peak - bk) / peak) return {'final': bk, 'x': bk/START, 'dd': max_dd * 100, 'n_co': n_co, 'saved': saved, 'missed': missed} # ── Deterministic sweep ────────────────────────────────────────────────────── scenarios = [ ("Baseline (hold all)", None), ("Scaled: -1=50% -2=25% -3=5%", {-1:0.50,-2:0.25,-3:0.05}), ("Scaled: -1=40% -2=20% -3=5%", {-1:0.40,-2:0.20,-3:0.05}), ("Scaled: -1=35% -2=15% -3=0%", {-1:0.35,-2:0.15,-3:0.00}), ("Flat 40% all deficits", {-1:0.40,-2:0.40,-3:0.40}), ("Only cash -2+ (25/5%)", {-2:0.25,-3:0.05}), ("Only cash -2+ (40/5%)", {-2:0.40,-3:0.05}), ] for km in KELLY_MULTS: print(f"\n{'='*90}") print(f" {km:.2f}x Kelly ({len(bets_ht):,} bets with HT scores)") print(f"{'='*90}") print(f" {'Strategy':<38} {'Final':>10} {'x':>7} {'MaxDD':>7} {'CashOuts':>9} {'Saved':>6} {'Missed':>7}") print(" " + "-"*86) for label, rules in scenarios: r = run_strategy(bets_ht, rules, km) print(f" {label:<38} {r['final']:>10,.0f} {r['x']:>6.1f}x {r['dd']:>6.1f}% {r['n_co']:>9} {r['saved']:>6} {r['missed']:>7}") # ── Monte Carlo ────────────────────────────────────────────────────────────── print(f"\n{'='*90}") print(f"MONTE CARLO (10,000 sims, 0.25x Kelly)") print(f"{'='*90}") N_SIMS = 10_000 N = len(bets_ht) odds = bets_ht['odds'].values p_win = bets_ht['p_win_pinnacle'].values kelly = bets_ht['kelly_fraction'].values deficit = bets_ht['ht_deficit'].values.astype(int) won_arr = bets_ht['won'].values # Build per-deficit conditional win probabilities (historical comeback rates) wr_by_deficit = {} for d_val in sorted(bets_ht['ht_deficit'].unique()): d_int = int(d_val) mask = deficit == d_int if mask.sum() > 0: wr_by_deficit[d_int] = won_arr[mask].mean() # Group -3 and worse mask_3 = deficit <= -3 if mask_3.sum() > 0: wr_by_deficit[-3] = won_arr[mask_3].mean() # Build per-bet win probability: use conditional rate based on HT deficit p_win_conditional = np.array([ wr_by_deficit.get(max(d, -3), p_win[i]) for i, d in enumerate(deficit) ]) print(f"\n Conditional win rates by HT deficit:") for d_val in sorted(wr_by_deficit.keys()): n = (deficit == d_val).sum() if d_val > -3 else (deficit <= -3).sum() print(f" deficit {d_val:>2}: {wr_by_deficit[d_val]*100:5.1f}% (n={n})") def mc_run(rules, km): stakes = np.minimum(kelly * km, 0.25) terminal = np.zeros(N_SIMS) max_dds = np.zeros(N_SIMS) for sim in range(N_SIMS): outcomes = np.random.random(N) < p_win_conditional bk = START; peak = START; max_dd = 0.0 for i in range(N): s = bk * stakes[i] d = max(deficit[i], -3) if rules and deficit[i] < 0 and d in rules: pl = -s * (1 - rules[d]) else: pl = s * (odds[i] - 1) if outcomes[i] else -s bk = max(bk + pl, 0) peak = max(peak, bk) max_dd = max(max_dd, (peak - bk) / peak) terminal[sim] = bk max_dds[sim] = max_dd return terminal, max_dds mc_scenarios = [ ("Baseline (hold all)", None), ("Scaled: -1=50% -2=25% -3=5%", {-1:0.50,-2:0.25,-3:0.05}), ("Scaled: -1=40% -2=20% -3=5%", {-1:0.40,-2:0.20,-3:0.05}), ("Flat 40% all deficits", {-1:0.40,-2:0.40,-3:0.40}), ("Only cash -2+ (25/5%)", {-2:0.25,-3:0.05}), ("Only cash -2+ (40/5%)", {-2:0.40,-3:0.05}), ] for km in KELLY_MULTS: print(f"\n --- {km:.2f}x Kelly ---") print(f" {'Strategy':<38} {'Median':>9} {'P(prof)':>9} {'P(>10x)':>9} {'MedDD':>8} {'Ruin':>6}") print(" " + "-"*82) for label, rules in mc_scenarios: t, d = mc_run(rules, km) print(f" {label:<38} {np.median(t)/START:>8.1f}x " f"{(t>START).mean()*100:>8.1f}% " f"{(t>START*10).mean()*100:>8.1f}% " f"{np.median(d)*100:>7.1f}% " f"{(t5.1f}%") print("\nDone.")