import pandas as pd import numpy as np seasons = ['1920','2021','2122','2223','2324','2425','2526'] leagues = {'E0':'EPL','E1':'Championship','F1':'Ligue 1','I1':'Serie A'} def remove_margin(h, d, a): over = 1/h + 1/d + 1/a return 1/h/over, 1/d/over, 1/a/over dfs = [] for code, name in leagues.items(): for s in seasons: try: d = pd.read_csv(f'E:/soccer/league_data/{code}_{s}.csv', encoding='utf-8-sig') d['Season'] = s d['League'] = name dfs.append(d) except: pass df = pd.concat(dfs, ignore_index=True) df = df.dropna(subset=['FTR','Date','B365A','PSCH','PSCD','PSCA']) df = df.copy() df['Date'] = pd.to_datetime(df['Date'], dayfirst=True) df = df.sort_values('Date').reset_index(drop=True) # Pinnacle closing fair prob for away tH,tD,tA = zip(*df.apply(lambda r: remove_margin(r['PSCH'],r['PSCD'],r['PSCA']), axis=1)) df['p_away'] = pd.Series(tA, index=df.index) # true prob of away win (Pinnacle closing) df['PSC_fair_A'] = 1 / df['p_away'] df['b'] = df['B365A'] - 1 # net odds df['edge'] = (df['B365A'] * df['p_away']) - 1 # expected value per unit staked # Kelly fraction per bet: f = (b*p - q) / b = edge / b df['kelly_f'] = df['edge'] / df['b'] # Only qualifying bets (>5% edge on B365 vs Pinnacle closing) df['qualifies'] = (df['B365A'] > df['PSC_fair_A'] * 1.05) & (df['kelly_f'] > 0) qualifying = df[df['qualifies']].copy() print(f"Qualifying bets: {len(qualifying)}") print(f"\n=== Kelly fraction distribution ===") print(qualifying['kelly_f'].describe().round(4)) print(f"\nMedian Kelly: {qualifying['kelly_f'].median():.4f} ({qualifying['kelly_f'].median()*100:.2f}%)") print(f"Mean Kelly: {qualifying['kelly_f'].mean():.4f} ({qualifying['kelly_f'].mean()*100:.2f}%)") print(f"Max Kelly: {qualifying['kelly_f'].max():.4f} ({qualifying['kelly_f'].max()*100:.2f}%)") # Per-week total Kelly stake (sum of individual Kellys) df['Week'] = df['Date'].dt.to_period('W') qualifying_w = qualifying.copy() qualifying_w['Week'] = qualifying_w['Date'].dt.to_period('W') weekly_kelly = qualifying_w.groupby('Week')['kelly_f'].sum() print(f"\n=== Weekly total Kelly stake distribution ===") print(weekly_kelly.describe().round(4)) print(f"Median weekly Kelly total: {weekly_kelly.median()*100:.1f}%") print(f"Mean weekly Kelly total: {weekly_kelly.mean()*100:.1f}%") print(f"Max weekly Kelly total: {weekly_kelly.max()*100:.1f}%") # ============================================================== # SIMULATION: stake Kelly fraction per bet (proportional to edge) # For simultaneous bets, use individual Kelly fractions # ============================================================== def run_kelly_sim(data, kelly_multiplier=1.0, starting_bankroll=1000.0): bankroll = starting_bankroll history = [] data = data.copy() data['Week'] = data['Date'].dt.to_period('W') for week in sorted(data['Week'].unique()): week_bets = data[data['Week'] == week] if week_bets.empty: continue week_pl = 0 wins = 0 total_staked = 0 for _, bet in week_bets.iterrows(): f = bet['kelly_f'] * kelly_multiplier f = min(f, 0.25) # cap individual bet at 25% of bankroll stake = bankroll * f total_staked += stake if bet['won']: week_pl += stake * bet['b'] wins += 1 else: week_pl -= stake bankroll += week_pl history.append({ 'Week': str(week), 'N': len(week_bets), 'Total_staked': round(total_staked, 2), 'Week_PL': round(week_pl, 2), 'Bankroll': round(bankroll, 2), 'Wins': wins, 'Bankroll_x': round(bankroll/starting_bankroll, 3), }) if bankroll <= 0: break return pd.DataFrame(history) qualifying_w['won'] = qualifying_w['FTR'] == 'A' # ============================================================== # COMPARE KELLY FRACTIONS # ============================================================== print("\n" + "="*70) print("KELLY SIMULATION — vary Kelly fraction (with 25% per-bet cap)") print("="*70) print(f"{'Kelly fraction':<20} {'Final £':>12} {'x':>8} {'Max DD%':>9} {'Bust?':>7}") print("-"*70) best = None for km in [0.10, 0.15, 0.20, 0.25, 0.30, 0.40, 0.50, 0.75, 1.0, 1.5, 2.0]: h = run_kelly_sim(qualifying_w, kelly_multiplier=km) if h.empty: print(f" {km:.2f}x Kelly {'BUST':>12}") continue f = h['Bankroll'].iloc[-1] pk = h['Bankroll'].cummax() dd = ((pk - h['Bankroll']) / pk * 100).max() bust = f <= 1.0 label = f" {km:.2f}x Kelly" marker = " <-- optimal?" if (best is None and not bust and f > 1000) else "" if best is None and not bust and f > 1000: best = (km, f, dd) print(f"{label:<20} £{f:>11,.2f} {f/1000:>8.1f}x {dd:>8.1f}% {'YES' if bust else 'no':>7}{marker}") # ============================================================== # FULL DETAIL: Quarter Kelly (conservative) # ============================================================== print("\n" + "="*90) print("QUARTER KELLY (0.25x) — week by week") print("="*90) h = run_kelly_sim(qualifying_w, kelly_multiplier=0.25) print(f"\n{'Week':<14} {'Bets':>4} {'Staked':>9} {'W/L':>5} {'Week P&L':>10} {'Bankroll':>10} {'x':>6}") print("-"*70) for _, row in h.iterrows(): wl = f"{int(row['Wins'])}/{int(row['N']-row['Wins'])}" print(f"{row['Week']:<14} {int(row['N']):>4} {row['Total_staked']:>9.2f} {wl:>5} {row['Week_PL']:>+10.2f} {row['Bankroll']:>10.2f} {row['Bankroll_x']:>6.2f}x") f = h['Bankroll'].iloc[-1] pk = h['Bankroll'].cummax() dd = ((pk - h['Bankroll']) / pk * 100).max() trough = h['Bankroll'].min() print(f"\n Final: £{f:,.2f} ({f/1000:.1f}x) | Peak: £{pk.max():,.2f} | Trough: £{trough:,.2f} | Max DD: {dd:.1f}%") # ============================================================== # HALF KELLY # ============================================================== print("\n" + "="*90) print("HALF KELLY (0.5x) — week by week") print("="*90) h2 = run_kelly_sim(qualifying_w, kelly_multiplier=0.50) print(f"\n{'Week':<14} {'Bets':>4} {'Staked':>9} {'W/L':>5} {'Week P&L':>10} {'Bankroll':>10} {'x':>6}") print("-"*70) for _, row in h2.iterrows(): wl = f"{int(row['Wins'])}/{int(row['N']-row['Wins'])}" print(f"{row['Week']:<14} {int(row['N']):>4} {row['Total_staked']:>9.2f} {wl:>5} {row['Week_PL']:>+10.2f} {row['Bankroll']:>10.2f} {row['Bankroll_x']:>6.2f}x") f2 = h2['Bankroll'].iloc[-1] pk2 = h2['Bankroll'].cummax() dd2 = ((pk2 - h2['Bankroll']) / pk2 * 100).max() print(f"\n Final: £{f2:,.2f} ({f2/1000:.1f}x) | Peak: £{pk2.max():,.2f} | Max DD: {dd2:.1f}%") # ============================================================== # PER-SEASON with quarter Kelly # ============================================================== print("\n" + "="*65) print("PER-SEASON — Quarter Kelly (0.25x), start £1000 each season") print("="*65) for season in seasons: sub = qualifying_w[qualifying_w['Season'] == season] if sub.empty: continue h = run_kelly_sim(sub, kelly_multiplier=0.25) if h.empty: continue f = h['Bankroll'].iloc[-1] pk = h['Bankroll'].cummax() dd = ((pk - h['Bankroll']) / pk * 100).max() print(f" {season} bets={len(sub):>3} £{f:>9,.2f} {f/1000:>6.2f}x max_dd={dd:.1f}%")