Tutorial · Data · DuckDB

Pinnacle closing odds into DuckDB and Parquet

Updated 9 November 2026 · PinnFeed · 2 min read

Answer: on Builder or Pro, run a small daily job that pages GET /v1/closing?date=YYYY-MM-DD (up to 2,000 rows per call), flattens each row's market and outcomes, and writes one Parquet file per day. DuckDB then queries the whole folder with plain SQL, with no database server to run. Records exist from when PinnFeed started storing closes, so your dataset grows from there.

1. Daily export (Python)#

import os, sys, requests, pandas as pd
H = {"x-api-key": os.environ["PINNFEED_KEY"]}  # ps_live_...

def closing_day(day, sport):
    rows, offset = [], 0
    while True:
        r = requests.get("https://pinnfeed.io/v1/closing", headers=H, timeout=30,
                         params={"date": day, "sport": sport, "limit": 2000, "offset": offset})
        r.raise_for_status()                     # 403 plan_lacks_history on Free/Hobby
        batch = r.json()["closing"]
        rows += batch
        if len(batch) < 2000:
            return rows
        offset += 2000

day = sys.argv[1]                                # e.g. 2026-11-08
out = []
for row in closing_day(day, "soccer"):
    m = row["market"]
    for o in m["outcomes"] or []:
        out.append({
            "event_id": row["eventId"], "league": row["league"]["name"], "home": row["home"], "away": row["away"],
            "start_time": row["startTime"], "captured_at": row["capturedAt"],
            "market": m["type"], "period": m["period"], "line": m["line"], "main": m["main"],
            "margin": m["margin"], "max_stake": m["maxStake"],
            "outcome": o.get("name"), "decimal": o.get("decimal"), "fair": o.get("fair"),
        })
os.makedirs("closing", exist_ok=True)
pd.DataFrame(out).to_parquet(f"closing/soccer-{day}.parquet", index=False)   # needs pyarrow
print(len(out), "outcome rows")

Run it once a day for the previous date (cron, GitHub Actions or a scheduled task). Check one response first: if an outcome field you need is missing, adjust the mapping rather than guessing.

2. Query with DuckDB#

-- duckdb shell, from the folder that contains closing/
SELECT league, market, round(avg(margin) * 100, 2) AS avg_margin_pct, count(DISTINCT event_id) AS events
FROM 'closing/*.parquet'
WHERE main AND period = 0
GROUP BY ALL
ORDER BY events DESC
LIMIT 20;

3. Join your own bets for CLV#

Keep a bets.csv with event_id, market, line, outcome, price_taken, then:

SELECT b.*, c.decimal AS close, round((b.price_taken / c.decimal - 1) * 100, 2) AS clv_pct
FROM 'bets.csv' b
JOIN 'closing/*.parquet' c USING (event_id, market, line, outcome)
WHERE c.period = 0;

USING does not match NULL lines, so join moneyline bets on event_id, market, outcome only. CLV as price taken over closing price is one common definition; use the one your tracker already uses. Spreadsheet version: bet tracker with CLV.

Plans#

Builder (€99/mo): closing records, all 13 sports, WebSocket. Enterprise adds bulk CSV/Parquet exports. 3-day trial; a card is required and it is charged after day 3 unless you cancel. Data-science overview: use case.

Educational content for adults (18+). Research use; no tips, no guaranteed profit.

Related: Historical closing odds.

Questions people ask#

Why one file per day?#

Re-running a day overwrites only that file, and DuckDB reads the glob as one table.

Can I backfill earlier dates?#

Yes, for any date PinnFeed already holds closes for. Dates before that return no rows.

pandas instead of DuckDB?#

Both work on the same Parquet files: pandas DataFrame.