"""Is our Pinnacle feed too stale to anchor the strategy? Reads research/pin_fresh_capture.csv (real-time Pinnacle from pinnapi) against the API-Football relay we have used for everything to date, and answers four questions in increasing order of importance: 1. HOW STALE? update frequency of each feed. 2. HOW WRONG? distribution of |fresh_fair - stale_fair| in cents. This is the size of the error sitting inside every CLV number we have ever computed. 3. DOES THE SIGNAL SURVIVE? recompute the xlow band on BOTH feeds and compare which fixtures/sides qualify. Three outcomes matter: - signals that fire on both -> genuine, staleness was not driving them - fire on stale only -> ARTEFACTS. we were betting lag. - fire on fresh only -> edge we have been blind to 4. WHICH ANCHOR DOES KALSHI TRACK? for each side, does the Kalshi mid move toward fresh fair or stale fair over the following hour? If Kalshi tracks fresh Pinnacle, fresh is the true anchor and our band is mis-calibrated. Question 3 is the one that decides whether to pay for a feed. A large "fire on stale only" bucket means the historical edge was partly an artefact of measuring a live market against a 6-hour-old line. Usage: python research\\pin_fresh_vs_stale.py """ import csv import datetime as dt import math import statistics from collections import defaultdict from pathlib import Path SRC = Path(r'E:\soccer\research\pin_fresh_capture.csv') BAND = (0.05, 0.12) def _f(x): try: return float(x) except (TypeError, ValueError): return None def _pt(s): try: d = dt.datetime.fromisoformat(str(s).replace('Z', '+00:00')) except (ValueError, TypeError): return None return d if d.tzinfo else d.replace(tzinfo=dt.timezone.utc) def fair(o): """Proportional devig -> implied probabilities summing to 1.""" if not all(x and x > 1 for x in o): return None r = [1.0 / x for x in o] s = sum(r) return [x / s for x in r] def load(): if not SRC.exists(): raise SystemExit(f'no capture yet at {SRC} -- run main\\pin_fresh_capture.py first') rows = [] with open(SRC, encoding='utf-8') as fh: for r in csv.DictReader(fh): fr = fair([_f(r['fresh_h']), _f(r['fresh_d']), _f(r['fresh_a'])]) st = fair([_f(r['stale_h']), _f(r['stale_d']), _f(r['stale_a'])]) t = _pt(r['ts']) if not fr or t is None: continue rows.append({'t': t, 'key': (r['lc'], r['home'], r['away']), 'lc': r['lc'], 'fresh': fr, 'stale': st, 'ko': _pt(r['kickoff_utc']), # carried through for section 3 -- pin_fresh_capture # scores the xlow rule on both feeds at capture time 'home': r.get('home'), 'away': r.get('away'), 'verdict': r.get('verdict', ''), 'xlow_fresh': r.get('xlow_fresh', ''), 'xlow_stale': r.get('xlow_stale', ''), # section 4 needs the DATE (the section 1-3 key is # (lc, home, away), which merges a repeat fixture # across dates) and the mids the verdict was scored on 'gd': r.get('game_date'), 'k_home_mid': _f(r.get('k_home_mid')), 'k_away_mid': _f(r.get('k_away_mid')), 'p_home_mid': _f(r.get('p_home_mid')), 'p_away_mid': _f(r.get('p_away_mid'))}) return rows PRICE_COL = {('home', 'kalshi'): 'k_home_mid', ('away', 'kalshi'): 'k_away_mid', ('home', 'poly'): 'p_home_mid', ('away', 'poly'): 'p_away_mid'} def _first_bet(snaps, col): """(side, price) of the FIRST snapshot where `col` fires, mirroring fire_xlow's one-bet-per-fixture, no-look-ahead selection.""" for r in snaps: v = (r.get(col) or '').strip() if not v or '/' not in v: continue side, book = v.split('/', 1) p = r.get(PRICE_COL.get((side, book), '')) if p and 0 < p < 1: return side, p return None def _ret(bet, ftr): """(pnl, staked) in units for one bet, or (0, 0) if the anchor did not fire.""" if not bet: return 0.0, 0.0 side, p = bet won = (ftr == 'H') if side == 'home' else (ftr == 'A') return (((1 - p) / p) if won else -1.0), 1.0 def roi_section(rows): """4. DOES THE ROI DIFFER? -- the question the agreement stats cannot answer. Sections 1-3 measure how often the two feeds disagree. Disagreement is not a cost: a signal the stale feed invents is only expensive if it LOSES, and one it hides is only expensive if it WINS. This prices both. Run as PAIRED POLICIES over the same settled fixtures. Comparing two independent ROIs throws away the fact that both bet on largely the same matches; pairing removes fixture-level variance, which dominates everything else at this sample size. A fixture only one anchor fired on contributes that arm's P&L and zero to the other -- which is what running one policy and not the other actually does to your bankroll. PRICED AT MIDS, because mids are what the capture logs. That flatters both arms identically, so it cancels in the paired difference; the absolute ROIs below are NOT tradeable numbers. """ try: import forward_test_unique_book as F except ImportError: print('\n4. ROI: forward_test_unique_book not importable -- skipped') return res = {} for fx in F.load_fixtures(require_result=True): r0 = fx['pre'][0] if fx['pre'] else {} res[(fx['gd'], fx['league'], r0.get('home'), r0.get('away'))] = fx['ftr'] byfix = defaultdict(list) for r in rows: byfix[(r['gd'], r['lc'], r['home'], r['away'])].append(r) for v in byfix.values(): v.sort(key=lambda x: x['t']) diffs, agg = [], {'fresh': [0.0, 0.0], 'stale': [0.0, 0.0]} buckets = defaultdict(lambda: [0.0, 0]) n_fix = 0 for key, snaps in byfix.items(): ftr = res.get(key) if ftr not in ('H', 'D', 'A'): continue n_fix += 1 bf, bs = _first_bet(snaps, 'xlow_fresh'), _first_bet(snaps, 'xlow_stale') pf, kf = _ret(bf, ftr) ps, ks = _ret(bs, ftr) agg['fresh'][0] += pf; agg['fresh'][1] += kf agg['stale'][0] += ps; agg['stale'][1] += ks diffs.append(pf - ps) if bf and bs: buckets['both (fresh leg)'][0] += pf; buckets['both (fresh leg)'][1] += 1 buckets['both (stale leg)'][0] += ps; buckets['both (stale leg)'][1] += 1 elif bs: buckets['stale-only [ARTEFACT]'][0] += ps buckets['stale-only [ARTEFACT]'][1] += 1 elif bf: buckets['fresh-only [MISSED]'][0] += pf buckets['fresh-only [MISSED]'][1] += 1 print('\n4. DOES THE ROI DIFFER? (paired policies, priced at mids)') if not n_fix: print(' no settled fixtures joined yet -- capture has no played matches.') return print(f' {n_fix} settled fixtures compared') for name in ('fresh', 'stale'): net, stk = agg[name] lbl = 'fresh (pinnapi) ' if name == 'fresh' else 'stale (API-Ftbl) ' print(f' {lbl} {stk:4.0f} bets net {net:+8.2f}u ' f'ROI {net/stk*100:+7.2f}%' if stk else f' {lbl} no bets') n = len(diffs) mu = statistics.mean(diffs) sd = statistics.stdev(diffs) if n > 1 else 0.0 se = sd / math.sqrt(n) if n else 0.0 t = mu / se if se else 0.0 lo, hi = mu - 1.96 * se, mu + 1.96 * se print(f'\n PAIRED per-fixture difference (fresh minus stale)') print(f' n={n} mean={mu:+.4f}u se={se:.4f} t={t:+.2f}') print(f' 95% CI [{lo:+.3f}, {hi:+.3f}] units per fixture') print(f' differed on {sum(1 for d in diffs if abs(d) > 1e-9)} of {n} ' f'fixtures -- the rest contribute nothing to the gap') if abs(t) > 1.96: print(f' SIGNIFICANT: the anchors do not pay the same.') else: need = int((1.96 * sd / abs(mu)) ** 2) + 1 if abs(mu) > 1e-9 else None print(' NOT significant -- no measurable ROI difference yet.' + (f' Resolving an effect this small needs ~{need:,} fixtures.' if need else '')) print('\n where they disagree:') for k in ('both (fresh leg)', 'both (stale leg)', 'stale-only [ARTEFACT]', 'fresh-only [MISSED]'): net, cnt = buckets[k] if cnt: print(f' {k:24s} n={cnt:<4d} net {net:+7.2f}u ' f'ROI {net/cnt*100:+8.2f}%') def main(): rows = load() byfx = defaultdict(list) for r in rows: byfx[r['key']].append(r) for v in byfx.values(): v.sort(key=lambda x: x['t']) print(f'{len(rows)} snapshots across {len(byfx)} fixtures\n') # 1. staleness def rate(v, k): vals = [tuple(round(x, 4) for x in r[k]) for r in v if r.get(k)] if len(vals) < 3: return None span = (v[-1]['t'] - v[0]['t']).total_seconds() / 3600 if span <= 0: return None ch = sum(1 for a, b in zip(vals, vals[1:]) if a != b) return ch / span fr = [x for x in (rate(v, 'fresh') for v in byfx.values()) if x is not None] sr = [x for x in (rate(v, 'stale') for v in byfx.values()) if x is not None] print('1. UPDATE FREQUENCY (changes per hour, median across fixtures)') if fr: print(f' fresh Pinnacle (pinnapi) {statistics.median(fr):.2f}/h' + (f' ~ every {60/statistics.median(fr):.0f} min' if statistics.median(fr) else '')) if sr: print(f' stale Pinnacle (API-Ftbl) {statistics.median(sr):.2f}/h' + (f' ~ every {60/statistics.median(sr):.0f} min' if statistics.median(sr) else '')) if not sr: print(' (no stale odds captured -- API-Football returned nothing)') # 2. divergence d = [] for r in rows: if not r['stale']: continue d += [abs(a - b) * 100 for a, b in zip(r['fresh'], r['stale'])] print('\n2. DISAGREEMENT between the two feeds (cents of implied probability)') if d: q = statistics.quantiles(d, n=100) print(f' median {statistics.median(d):.2f}c p75 {q[74]:.2f}c ' f'p90 {q[89]:.2f}c p99 {q[98]:.2f}c max {max(d):.2f}c') print(f' share of legs disagreeing by >2c: {sum(1 for x in d if x > 2)/len(d)*100:.1f}%') print(' For scale: the xlow band is 5-12% of fair, and a 25c leg in band ' 'is 1.25-3c from fair.') else: print(' no paired rows yet') # 3. does the signal survive # As of 2026-08-07 pin_fresh_capture writes the verdict directly: it runs the # frozen xlow rule twice per snapshot, against fresh fair and stale fair, on # the SAME book mids captured at that instant. So this no longer has to # reconstruct qualification -- it just counts the buckets. print('\n3. WOULD THE SAME SIGNALS FIRE?') verd = defaultdict(int) fired = defaultdict(list) for r in rows: v = (r.get('verdict') or '').strip() if not v: continue verd[v] += 1 xf, xs = (r.get('xlow_fresh') or ''), (r.get('xlow_stale') or '') if xf or xs: fired[v].append((r.get('lc'), r.get('home'), r.get('away'), xf, xs)) total = sum(verd.values()) if not total: print(' no scored snapshots yet -- capture has not written verdicts.') else: both = len([1 for v in fired['same'] if v[3] and v[4]]) print(f' {total} scored snapshots') print(f' agree (same verdict) : {verd.get("same", 0):5d}' f' of which BOTH fired: {both}') print(f' stale-only [ARTEFACT] : {verd.get("stale_only", 0):5d}' ' <- signals we bet that a fresh line does NOT support') print(f' fresh-only [MISSED] : {verd.get("fresh_only", 0):5d}' ' <- edge the stale feed hides from us') print(f' differ (side/book) : {verd.get("differ", 0):5d}') for bucket in ('stale_only', 'fresh_only', 'differ'): for lc, h, a, xf, xs in fired.get(bucket, [])[:6]: print(f' [{bucket}] {lc} {str(h)[:18]} v {str(a)[:18]}' f' fresh={xf or "-"} stale={xs or "-"}') art = verd.get('stale_only', 0) if total >= 200: print(f'\n VERDICT: {100*art/total:.1f}% of scored snapshots are ' 'stale-only artefacts.') else: print(f'\n Sample still small ({total} snapshots); treat as directional.') roi_section(rows) if __name__ == '__main__': main()