#!/usr/bin/env python3 """ Expected Value (EV) Calculation Engine ======================================= Computes +EV opportunities by comparing book odds to fair (no-vig) odds. For each PlayerPropLine row with both oddsAmerican and fairOdds: - edge% = (bookImpliedProb / fairImpliedProb - 1) * -100 - Positive edge = the book is offering better odds than fair value - Stores results in EVAnalysis table Also computes EV for GameOdds spreads/moneylines/totals using SportsGame opening odds vs fair values from OddsSnapshot. Usage: python3 compute_ev.py [--days 60] [--league nba] [--batch 10000] [--dry-run] """ import os import sys import time import argparse import psycopg2 import psycopg2.extras from datetime import datetime, timezone, timedelta sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) from dotenv import load_dotenv load_dotenv('/var/www/html/eventheodds/.env') DB_URL = os.getenv('SPORTS_DATABASE_URL', 'postgresql://eventheodds:eventheodds_dev_password@127.0.0.1:5433/eventheodds_sports').split('?')[0] def american_to_implied(odds): """American odds -> implied probability (0-1).""" if odds is None: return None odds = float(odds) if odds > 0: return 100.0 / (odds + 100.0) elif odds < 0: return abs(odds) / (abs(odds) + 100.0) return 0.5 def american_to_decimal(odds): """American odds -> decimal odds.""" if odds is None: return None odds = float(odds) if odds > 0: return 1.0 + odds / 100.0 elif odds < 0: return 1.0 + 100.0 / abs(odds) return 2.0 def ensure_ev_table(cur, conn): """Create EVAnalysis table if it doesn't exist.""" cur.execute(""" CREATE TABLE IF NOT EXISTS "EVAnalysis" ( id BIGSERIAL PRIMARY KEY, league TEXT NOT NULL, "gameId" BIGINT, "playerExternalId" TEXT, "propType" TEXT, market TEXT, "lineValue" DOUBLE PRECISION, "bookOdds" INTEGER, "fairOdds" INTEGER, "bookImpliedProb" DOUBLE PRECISION, "fairImpliedProb" DOUBLE PRECISION, "edgePercent" DOUBLE PRECISION, "kellyFraction" DOUBLE PRECISION, "isPositiveEV" BOOLEAN DEFAULT FALSE, "snapshotAt" TIMESTAMP WITHOUT TIME ZONE, "hoursToGame" DOUBLE PRECISION, "createdAt" TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(), UNIQUE(league, "gameId", "playerExternalId", "propType", "lineValue", "bookOdds") ); CREATE INDEX IF NOT EXISTS "EVAnalysis_positive" ON "EVAnalysis" ("isPositiveEV", league, "createdAt" DESC); CREATE INDEX IF NOT EXISTS "EVAnalysis_game" ON "EVAnalysis" ("gameId"); CREATE INDEX IF NOT EXISTS "EVAnalysis_edge" ON "EVAnalysis" ("edgePercent" DESC); """) conn.commit() def compute_prop_ev(cur, conn, league_filter, cutoff_date, batch_size, dry_run): """Compute EV for PlayerPropLine rows with both bookOdds and fairOdds.""" print("\n" + "=" * 70) print("PROP EV: Compute edge for player props") print("=" * 70) # Get leagues to process if league_filter: leagues = [league_filter] else: cur.execute(""" SELECT DISTINCT league FROM "PlayerPropLine" WHERE "oddsAmerican" IS NOT NULL AND "fairOdds" IS NOT NULL AND "createdAt" >= %s """, (cutoff_date,)) leagues = [r[0] for r in cur.fetchall()] print(f" Processing {len(leagues)} leagues: {', '.join(leagues[:10])}...") total_inserted = 0 for league in leagues: cur.execute(""" SELECT COUNT(*) FROM "PlayerPropLine" WHERE league = %s AND "oddsAmerican" IS NOT NULL AND "fairOdds" IS NOT NULL AND "oddsAmerican" != "fairOdds"::integer AND "createdAt" >= %s """, (league, cutoff_date)) count = cur.fetchone()[0] if count == 0: continue if dry_run: print(f" [{league}] Would process {count:,} rows") continue # Process in batches using Python computation offset = 0 league_inserted = 0 while offset < count: cur.execute(""" SELECT id, "gameId", "playerExternalId", "propType", market, "lineValue", "oddsAmerican", "fairOdds"::integer, "snapshotAt", "hoursToGame" FROM "PlayerPropLine" WHERE league = %s AND "oddsAmerican" IS NOT NULL AND "fairOdds" IS NOT NULL AND "oddsAmerican" != "fairOdds"::integer AND "createdAt" >= %s ORDER BY id LIMIT %s OFFSET %s """, (league, cutoff_date, batch_size, offset)) rows = cur.fetchall() if not rows: break batch = [] for row in rows: _, game_id, player_id, prop_type, market, line_val, book_odds, fair_odds, snap_at, htg = row book_imp = american_to_implied(book_odds) fair_imp = american_to_implied(fair_odds) if book_imp is None or fair_imp is None or fair_imp == 0: continue edge_pct = (fair_imp - book_imp) / fair_imp * 100.0 is_pos_ev = book_imp < fair_imp # Kelly fraction: f* = (p*b - q) / b decimal_odds = american_to_decimal(book_odds) if decimal_odds and decimal_odds > 1: b = decimal_odds - 1.0 kelly = (fair_imp * b - (1.0 - fair_imp)) / b else: kelly = None batch.append(( league, game_id, player_id, prop_type, market, line_val, book_odds, fair_odds, book_imp, fair_imp, edge_pct, kelly, is_pos_ev, snap_at, htg )) if batch: psycopg2.extras.execute_batch(cur, """ INSERT INTO "EVAnalysis" ( league, "gameId", "playerExternalId", "propType", market, "lineValue", "bookOdds", "fairOdds", "bookImpliedProb", "fairImpliedProb", "edgePercent", "kellyFraction", "isPositiveEV", "snapshotAt", "hoursToGame" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, "gameId", "playerExternalId", "propType", "lineValue", "bookOdds") DO UPDATE SET "fairOdds" = EXCLUDED."fairOdds", "edgePercent" = EXCLUDED."edgePercent", "kellyFraction" = EXCLUDED."kellyFraction", "isPositiveEV" = EXCLUDED."isPositiveEV" """, batch) conn.commit() league_inserted += len(batch) offset += len(rows) total_inserted += league_inserted print(f" [{league}] {league_inserted:,} EV records", flush=True) # Show summary cur.execute(""" SELECT league, COUNT(*) as total, COUNT(CASE WHEN "isPositiveEV" THEN 1 END) as pos_ev, ROUND(AVG("edgePercent")::numeric, 2) as avg_edge, ROUND(MAX("edgePercent")::numeric, 2) as max_edge FROM "EVAnalysis" GROUP BY league ORDER BY pos_ev DESC """) rows = cur.fetchall() if rows: print("\n League Summary:") print(f" {'League':<20} {'Total':>10} {'+EV':>10} {'Avg Edge%':>10} {'Max Edge%':>10}") for league, total, pos, avg_e, max_e in rows: print(f" {league:<20} {total:>10,} {pos:>10,} {avg_e:>10} {max_e:>10}") return total_inserted def main(): parser = argparse.ArgumentParser(description='Compute Expected Value') parser.add_argument('--days', type=int, default=60, help='Days to process') parser.add_argument('--league', type=str, default=None, help='Filter to league') parser.add_argument('--batch', type=int, default=10000, help='Batch size') parser.add_argument('--dry-run', action='store_true', help='Show counts only') args = parser.parse_args() cutoff_date = datetime.now(timezone.utc) - timedelta(days=args.days) print("=" * 70) print("EXPECTED VALUE (EV) CALCULATION ENGINE") print(f" Days: {args.days}, Cutoff: {cutoff_date.strftime('%Y-%m-%d')}") print(f" League: {args.league or 'ALL'}") print("=" * 70) conn = psycopg2.connect(DB_URL) cur = conn.cursor() start = time.time() try: ensure_ev_table(cur, conn) ev_count = compute_prop_ev(cur, conn, args.league, cutoff_date, args.batch, args.dry_run) except Exception as e: conn.rollback() print(f"\nERROR: {e}") import traceback traceback.print_exc() sys.exit(1) finally: cur.close() conn.close() elapsed = time.time() - start print(f"\n{'=' * 70}") print(f"DONE — {ev_count:,} EV records in {elapsed:.1f}s") print(f"{'=' * 70}") if __name__ == '__main__': main()