#!/usr/bin/env python3 """ Compute Market Efficiency Scores Analyzes which markets (spreads, totals, props) show most inefficiency. Answers Q20: "Which betting markets show the most inefficiency?" """ import psycopg2 from datetime import datetime, timezone import math def load_db_url(): with open('/var/www/html/eventheodds/.env', 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] return '' def compute_market_efficiency(): db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() print("=" * 60) print("COMPUTE MARKET EFFICIENCY SCORES") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) # Create efficiency analysis table cur.execute(''' CREATE TABLE IF NOT EXISTS "MarketEfficiency" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(20), market VARCHAR(30), "subMarket" VARCHAR(50), -- Sample info "sampleSize" INT, "dateRange" VARCHAR(50), -- Win rates "winRate" FLOAT, "expectedWinRate" FLOAT, -- Based on juice (typically ~47.6% for -110) -- Profitability "totalProfit" FLOAT, "roi" FLOAT, "avgProfit" FLOAT, -- Inefficiency score (higher = more exploitable) "inefficiencyScore" FLOAT, -- Breakdown "favoriteWinRate" FLOAT, "underdogWinRate" FLOAT, "homeWinRate" FLOAT, "awayWinRate" FLOAT, "updatedAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, market, "subMarket") ) ''') total_records = 0 # 1. Spread market efficiency by league print("\n1. Analyzing SPREAD markets...") cur.execute(''' SELECT league, COUNT(*) as games, SUM(CASE WHEN ("homeScore" - "awayScore" + "spreadHome") > 0 THEN 1 ELSE 0 END) as home_covers, SUM(CASE WHEN ("homeScore" - "awayScore" + "spreadHome") < 0 THEN 1 ELSE 0 END) as away_covers, SUM(CASE WHEN "spreadHome" < 0 AND ("homeScore" - "awayScore" + "spreadHome") > 0 THEN 1 ELSE 0 END) as fav_covers, SUM(CASE WHEN "spreadHome" < 0 THEN 1 ELSE 0 END) as fav_games FROM "SportsGame" WHERE season >= 2024 AND "spreadHome" IS NOT NULL AND "homeScore" IS NOT NULL GROUP BY league ''') for row in cur.fetchall(): league, games, home_covers, away_covers, fav_covers, fav_games = row if games < 50: continue home_rate = home_covers / games if games else 0 fav_rate = fav_covers / fav_games if fav_games else 0 # Calculate ROI assuming -110 odds expected_rate = 0.476 # Break-even at -110 home_profit = (home_covers * 91) - (away_covers * 100) roi = home_profit / (games * 100) * 100 # Inefficiency = deviation from expected inefficiency = abs(home_rate - 0.5) * 100 cur.execute(''' INSERT INTO "MarketEfficiency" ( league, market, "subMarket", "sampleSize", "winRate", "expectedWinRate", "totalProfit", roi, "inefficiencyScore", "favoriteWinRate", "homeWinRate" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, market, "subMarket") DO UPDATE SET "sampleSize" = EXCLUDED."sampleSize", "winRate" = EXCLUDED."winRate", "totalProfit" = EXCLUDED."totalProfit", roi = EXCLUDED.roi, "inefficiencyScore" = EXCLUDED."inefficiencyScore", "updatedAt" = NOW() ''', (league, 'spread', 'all', games, home_rate, expected_rate, home_profit, roi, inefficiency, fav_rate, home_rate)) total_records += 1 print(f" {league}: {games} games, home covers {home_rate*100:.1f}%, ROI {roi:.1f}%") # 2. Total market efficiency print("\n2. Analyzing TOTAL markets...") cur.execute(''' SELECT league, COUNT(*) as games, SUM(CASE WHEN ("homeScore" + "awayScore") > total THEN 1 ELSE 0 END) as overs, SUM(CASE WHEN ("homeScore" + "awayScore") < total THEN 1 ELSE 0 END) as unders FROM "SportsGame" WHERE season >= 2024 AND total IS NOT NULL AND "homeScore" IS NOT NULL GROUP BY league ''') for row in cur.fetchall(): league, games, overs, unders = row if games < 50: continue over_rate = overs / games if games else 0 over_profit = (overs * 91) - (unders * 100) roi = over_profit / (games * 100) * 100 inefficiency = abs(over_rate - 0.5) * 100 cur.execute(''' INSERT INTO "MarketEfficiency" ( league, market, "subMarket", "sampleSize", "winRate", "expectedWinRate", "totalProfit", roi, "inefficiencyScore" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, market, "subMarket") DO UPDATE SET "sampleSize" = EXCLUDED."sampleSize", "winRate" = EXCLUDED."winRate", "totalProfit" = EXCLUDED."totalProfit", roi = EXCLUDED.roi, "inefficiencyScore" = EXCLUDED."inefficiencyScore", "updatedAt" = NOW() ''', (league, 'total', 'all', games, over_rate, 0.476, over_profit, roi, inefficiency)) total_records += 1 print(f" {league}: {games} games, overs {over_rate*100:.1f}%, ROI {roi:.1f}%") # 3. Props market efficiency from PropBacktest print("\n3. Analyzing PROPS markets...") cur.execute(''' SELECT league, COUNT(*) as props, SUM(CASE WHEN result = 'over' THEN 1 ELSE 0 END) as overs, SUM(CASE WHEN result = 'under' THEN 1 ELSE 0 END) as unders FROM "PropBacktest" GROUP BY league ''') for row in cur.fetchall(): league, props, overs, unders = row if props < 100: continue over_rate = overs / props if props else 0 # Props typically at -115, so break-even is ~46.5% expected_rate = 0.465 over_profit = (overs * 87) - (unders * 100) # -115 odds roi = over_profit / (props * 100) * 100 inefficiency = abs(over_rate - 0.5) * 100 cur.execute(''' INSERT INTO "MarketEfficiency" ( league, market, "subMarket", "sampleSize", "winRate", "expectedWinRate", "totalProfit", roi, "inefficiencyScore" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (league, market, "subMarket") DO UPDATE SET "sampleSize" = EXCLUDED."sampleSize", "winRate" = EXCLUDED."winRate", "totalProfit" = EXCLUDED."totalProfit", roi = EXCLUDED.roi, "inefficiencyScore" = EXCLUDED."inefficiencyScore", "updatedAt" = NOW() ''', (league, 'props', 'all', props, over_rate, expected_rate, over_profit, roi, inefficiency)) total_records += 1 print(f" {league}: {props} props, overs {over_rate*100:.1f}%, ROI {roi:.1f}%") conn.commit() # Summary print("\n" + "=" * 60) print("MARKET EFFICIENCY RANKINGS (by inefficiency score)") print("=" * 60) cur.execute(''' SELECT league, market, "sampleSize", "winRate" * 100, roi, "inefficiencyScore" FROM "MarketEfficiency" ORDER BY "inefficiencyScore" DESC LIMIT 15 ''') print(f"{'League':<10} {'Market':<10} {'Sample':<8} {'Win%':<8} {'ROI%':<8} {'Ineff':<8}") print("-" * 60) for row in cur.fetchall(): print(f"{row[0]:<10} {row[1]:<10} {row[2]:<8} {row[3]:<8.1f} {row[4]:<8.1f} {row[5]:<8.1f}") cur.close() conn.close() print(f"\n✅ Total efficiency records: {total_records}") return total_records if __name__ == '__main__': compute_market_efficiency()