{"id":2824,"date":"2026-04-13T10:00:00","date_gmt":"2026-04-13T10:00:00","guid":{"rendered":"https:\/\/oddspapi.io\/blog\/?p=2824"},"modified":"2026-04-11T14:57:28","modified_gmt":"2026-04-11T14:57:28","slug":"historical-odds-csv-excel-backtesting","status":"publish","type":"post","link":"https:\/\/oddspapi.io\/blog\/historical-odds-csv-excel-backtesting\/","title":{"rendered":"Historical Odds Data: Export to CSV or Excel for Backtesting (Free API)"},"content":{"rendered":"

You need historical odds for backtesting. The options look bleak: OddsJam charges $299\/month<\/strong>, SportsGameOdds walls it behind their Enterprise plan, and The Odds API historical endpoint costs a premium tier just to query one sport at a time. So you give up and try to scrape Bet365’s archive into an Excel file. That dies within a week.<\/p>\n

Here’s the workaround: OddsPapi’s \/historical-odds<\/code> endpoint is free, covers every sport, and returns the full price history for each bookmaker \u2014 every line move, timestamped. This tutorial shows you how to pull it, flatten the nested JSON, and dump it straight to CSV or Excel so you can load it into pandas, a notebook, or even a spreadsheet and start backtesting.<\/p>\n

All code below was tested against the live API on April 11, 2026.<\/p>\n

Why Historical Odds Data Is So Hard to Get<\/h2>\n

Three problems keep killing backtest projects before they start:<\/p>\n

    \n
  1. Paywalls.<\/strong> The big historical providers treat the data as a premium product. $299\/month to OddsJam or $500+\/month to Sportradar-backed feeds just to answer the question “what did Pinnacle close at last Tuesday?”<\/li>\n
  2. Per-sport gatekeeping.<\/strong> Even when you pay, you usually pay per sport<\/em>. Want NFL + Premier League + ATP tennis in one dataset? That’s three subscriptions.<\/li>\n
  3. Nested JSON hell.<\/strong> The providers that do give you historical data hand it back as deeply nested objects \u2014 bookmakers \u2192 markets \u2192 outcomes \u2192 players \u2192 price_history<\/code> \u2014 and leave you to unpack it before you can even load a DataFrame.<\/li>\n<\/ol>\n

    OddsPapi solves all three in one call. Free historical data is on the free tier. One endpoint covers every sport we ingest (soccer, NFL, tennis, esports, cricket, volleyball \u2014 59 sports<\/strong>). And with ~30 lines of Python below, you’ll flatten the nested response into a clean tabular CSV or Excel file.<\/p>\n

    Historical Odds Providers Compared (2026)<\/h2>\n
    \n\n\n\n\n\n\n\n\n\n
    Provider<\/th>\nHistorical Cost<\/th>\nSports Covered<\/th>\nExport Format<\/th>\nPrice History<\/th>\n<\/tr>\n<\/thead>\n
    OddsPapi<\/strong><\/td>\nFree<\/strong><\/td>\n59 sports<\/td>\nJSON (flatten to CSV\/Excel with Python)<\/td>\n\u2705 Every line move, timestamped<\/td>\n<\/tr>\n
    The Odds API<\/td>\n$119\/mo+ (historical endpoint, paid tier)<\/td>\nPer-sport plans<\/td>\nJSON<\/td>\nSingle snapshots per timestamp<\/td>\n<\/tr>\n
    SportsGameOdds<\/td>\nEnterprise plan only<\/td>\nMajor US sports<\/td>\nJSON<\/td>\nLimited<\/td>\n<\/tr>\n
    OddsJam<\/td>\n$299\/mo+<\/td>\nMajor sports<\/td>\nCSV export (built-in)<\/td>\n\u2705 Available<\/td>\n<\/tr>\n
    Bet365 archive scraping<\/td>\n“Free” (until they block you)<\/td>\nBet365 only<\/td>\nHTML (good luck)<\/td>\n\u274c Not exposed<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

    If you already have an OddsJam subscription and just want a CSV export, the built-in tool is fine. If you want free<\/em> historical data across every sport with every price tick, read on.<\/p>\n

    Step 1: Set Up the API and Discover Sports<\/h2>\n

    Authentication is a query parameter (apiKey<\/code>), not a header. Grab a free key at oddspapi.io<\/a> and the rest is three imports.<\/p>\n

    import requests\nimport pandas as pd\n\nAPI_KEY = \"YOUR_API_KEY\"\nBASE_URL = \"https:\/\/api.oddspapi.io\/v4\"\n\n# Discover every sport available\nresp = requests.get(f\"{BASE_URL}\/sports\", params={\"apiKey\": API_KEY})\nsports = resp.json()\n\nfor s in sports[:10]:\n    print(f\"{s['sportId']:>3}  {s['sportName']}\")\n<\/code><\/pre>\n
    Output (partial):<\/p>\n
     10  Soccer\n 11  Basketball\n 12  Tennis\n 13  Baseball\n 14  American Football\n 15  Ice Hockey\n 16  ESport Dota\n 17  ESport Counter-Strike\n 20  MMA\n 23  Volleyball\n<\/code><\/pre>\n
    Every one of those sports has historical data available. Pick a sportId<\/code> and move on.<\/p>\n
    Step 2: Fetch Historical Fixtures for a Date Range<\/h2>\n
    The \/fixtures<\/code> endpoint takes from<\/code> and to<\/code> parameters (max 10 days apart) and returns every fixture played or scheduled in that window. For a backtest, point it at a past date range and pull the full fixture list.<\/p>\n
    # Fetch all soccer fixtures from April 5\u20136, 2026\nparams = {\n    \"apiKey\": API_KEY,\n    \"sportId\": 10,          # 10 = Soccer\n    \"from\": \"2026-04-05\",\n    \"to\": \"2026-04-06\",\n}\n\nresp = requests.get(f\"{BASE_URL}\/fixtures\", params=params)\nfixtures = resp.json()\n\nprint(f\"Fetched {len(fixtures)} fixtures\")\n<\/code><\/pre>\n
    Output:<\/p>\n
    Fetched 985 fixtures\n<\/code><\/pre>\n
    985 soccer fixtures in a single 48-hour window, across every league OddsPapi ingests. Filter by tournamentName<\/code> if you only care about a specific league (e.g. \"Premier League\"<\/code>, \"La Liga\"<\/code>, \"MLS\"<\/code>).<\/p>\n
    Filter to the league you care about<\/h3>\n
    target_tournaments = [\"Premier League\", \"La Liga\", \"Serie A\", \"Bundesliga\", \"Ligue 1\"]\n\ntop_leagues = [\n    f for f in fixtures\n    if f.get(\"tournamentName\") in target_tournaments\n]\n\nprint(f\"{len(top_leagues)} fixtures across top leagues\")\n<\/code><\/pre>\n
    Step 3: Pull Historical Odds for a Fixture<\/h2>\n
    The \/historical-odds<\/code> endpoint takes a fixtureId<\/code> and up to three bookmakers. Each call returns the full price history<\/strong> for every market and outcome offered by those books \u2014 not a single snapshot, but the complete list of line moves with timestamps.<\/p>\n
    # Pick a fixture (any from the filtered list)\nfixture = top_leagues[0]\nfixture_id = fixture[\"fixtureId\"]\n\n# Fetch historical odds from 3 sharp\/soft books\nparams = {\n    \"apiKey\": API_KEY,\n    \"fixtureId\": fixture_id,\n    \"bookmakers\": \"pinnacle,bet365,singbet\",\n}\nresp = requests.get(f\"{BASE_URL}\/historical-odds\", params=params)\ndata = resp.json()\n\nprint(\"Bookmakers returned:\", list(data[\"bookmakers\"].keys()))\n<\/code><\/pre>\n
    Output:<\/p>\n
    Bookmakers returned: ['bet365', 'pinnacle', 'singbet']\n<\/code><\/pre>\n
    Note:<\/strong> The API enforces a max of 3 bookmakers per call. To pull more, loop the request with different bookmaker combinations and merge the responses \u2014 the code in Step 4 handles that naturally.<\/p>\n
    The nested response structure<\/h3>\n
    Here’s the shape of what you get back:<\/p>\n
    {\n  \"fixtureId\": \"id1000119163078643\",\n  \"bookmakers\": {\n    \"pinnacle\": {\n      \"markets\": {\n        \"101\": {                   # marketId = Full Time Result (1X2)\n          \"outcomes\": {\n            \"101\": {                 # outcomeId = Home win\n              \"players\": {\n                \"0\": [\n                  {\"createdAt\": \"2026-04-05T14:58:17Z\", \"price\": 1.85, \"limit\": null, ...},\n                  {\"createdAt\": \"2026-04-05T13:02:18Z\", \"price\": 1.92, \"limit\": null, ...},\n                  {\"createdAt\": \"2026-04-05T11:41:42Z\", \"price\": 1.95, \"limit\": null, ...}\n                ]\n              }\n            },\n            \"102\": { ... X price history ... },\n            \"103\": { ... away price history ... }\n          }\n        },\n        \"1010\": { ... Over\/Under 2.5 price history ... }\n      }\n    },\n    \"bet365\": { ... }\n  }\n}\n<\/code><\/pre>\n
    Every entry in the inner list is one line move. Timestamped. Free. This is the data OddsJam charges $299\/month for.<\/p>\n
    Step 4: Flatten the Nested JSON into Tabular Rows<\/h2>\n
    To get this into a CSV or Excel file, we need one row per bookmaker \u00d7 market \u00d7 outcome \u00d7 timestamp<\/em>. We also want human-readable market and outcome names, not just IDs, so we fetch the market catalog once and build a lookup table.<\/p>\n
    # Fetch the market catalog for the sport (maps marketId\/outcomeId to names)\nresp = requests.get(f\"{BASE_URL}\/markets\", params={\"apiKey\": API_KEY, \"sportId\": 10})\nmarkets_catalog = resp.json()\n\nmarket_names = {m[\"marketId\"]: m[\"marketName\"] for m in markets_catalog}\noutcome_names = {\n    (m[\"marketId\"], o[\"outcomeId\"]): o[\"outcomeName\"]\n    for m in markets_catalog\n    for o in m.get(\"outcomes\", [])\n}\n\ndef flatten_historical_odds(data, fixture):\n    \"\"\"Flatten the nested historical-odds response into a list of flat dicts.\"\"\"\n    rows = []\n    home = fixture.get(\"participant1Name\")\n    away = fixture.get(\"participant2Name\")\n    start = fixture.get(\"startTime\")\n    fid = fixture[\"fixtureId\"]\n\n    for bm, bdata in data.get(\"bookmakers\", {}).items():\n        for mid_str, mdata in bdata.get(\"markets\", {}).items():\n            mid = int(mid_str)\n            for oid_str, odata in mdata.get(\"outcomes\", {}).items():\n                oid = int(oid_str)\n                for _player, price_history in odata.get(\"players\", {}).items():\n                    for snap in price_history:\n                        rows.append({\n                            \"fixture_id\": fid,\n                            \"start_time\": start,\n                            \"home\": home,\n                            \"away\": away,\n                            \"bookmaker\": bm,\n                            \"market_id\": mid,\n                            \"market_name\": market_names.get(mid, f\"Market {mid}\"),\n                            \"outcome_id\": oid,\n                            \"outcome_name\": outcome_names.get((mid, oid), f\"Outcome {oid}\"),\n                            \"price\": snap.get(\"price\"),\n                            \"limit\": snap.get(\"limit\"),\n                            \"recorded_at\": snap.get(\"createdAt\"),\n                        })\n    return rows\n\nrows = flatten_historical_odds(data, fixture)\ndf = pd.DataFrame(rows)\nprint(df.shape)\nprint(df.head())\n<\/code><\/pre>\n
    Output:<\/p>\n
    (8881, 12)\n           fixture_id                home          away bookmaker  market_id       market_name outcome_name  price              recorded_at\n0  id1000119163078643  Berekum Chelsea  Aduana Stars    bet365        101  Full Time Result            1   1.18 2026-04-05T09:38:17+00:00\n1  id1000119163078643  Berekum Chelsea  Aduana Stars    bet365        101  Full Time Result            1   1.10 2026-04-04T12:03:18+00:00\n2  id1000119163078643  Berekum Chelsea  Aduana Stars    bet365        101  Full Time Result            1   1.12 2026-04-04T10:41:42+00:00\n3  id1000119163078643  Berekum Chelsea  Aduana Stars    bet365        101  Full Time Result            X   4.75 2026-04-05T09:38:17+00:00\n4  id1000119163078643  Berekum Chelsea  Aduana Stars    bet365        101  Full Time Result            X   7.00 2026-04-04T12:03:18+00:00\n<\/code><\/pre>\n
    8,881 rows from a single fixture with two bookmakers.<\/strong> That’s thousands of timestamped line moves across every market they offered \u2014 1X2, Over\/Under, Asian Handicaps, Both Teams To Score, Correct Score, the whole book. Multiply by hundreds of fixtures for a full backtest dataset.<\/p>\n
    Step 5: Export to CSV and Excel<\/h2>\n
    With the rows flattened into a DataFrame, the export is one line.<\/p>\n
    # Export to CSV \u2014 the universal option\ndf.to_csv(\"historical_odds.csv\", index=False)\n\n# Export to Excel \u2014 requires openpyxl: pip install openpyxl\ndf.to_excel(\"historical_odds.xlsx\", index=False, sheet_name=\"Historical Odds\")\n\nprint(f\"Exported {len(df)} rows to historical_odds.csv and historical_odds.xlsx\")\n<\/code><\/pre>\n
    Output:<\/p>\n
    Exported 8881 rows to historical_odds.csv and historical_odds.xlsx\n<\/code><\/pre>\n
    Scaling up: loop over an entire season<\/h3>\n
    To build a full season dataset, loop over fixtures and append each flattened batch to a single DataFrame. Keep in mind rate limits \u2014 a small time.sleep(0.2)<\/code> between calls keeps you safe on the free tier.<\/p>\n
    import time\n\nall_rows = []\nfor fx in top_leagues[:50]:        # first 50 filtered fixtures\n    r = requests.get(\n        f\"{BASE_URL}\/historical-odds\",\n        params={\n            \"apiKey\": API_KEY,\n            \"fixtureId\": fx[\"fixtureId\"],\n            \"bookmakers\": \"pinnacle,bet365,singbet\",\n        },\n    )\n    if r.status_code == 200:\n        all_rows.extend(flatten_historical_odds(r.json(), fx))\n    time.sleep(0.2)\n\nseason_df = pd.DataFrame(all_rows)\nseason_df.to_csv(\"season_historical.csv\", index=False)\nprint(f\"{len(season_df):,} rows exported\")\n<\/code><\/pre>\n
    Step 6: Backtest Teaser \u2014 Compute Closing Line Value<\/h2>\n
    Once the CSV is on disk, you can load it back and start doing real analysis. Here’s the simplest thing: compute the closing line<\/strong> (the last price recorded before kickoff) for every market on every book, then compare a hypothetical “bet” against it.<\/p>\n
    df = pd.read_csv(\"historical_odds.csv\", parse_dates=[\"recorded_at\", \"start_time\"])\n\n# Only keep rows recorded BEFORE kickoff\npre_match = df[df[\"recorded_at\"] < df[\"start_time\"]]\n\n# The \"closing line\" is the last price per bookmaker \u00d7 market \u00d7 outcome\nclosing = (\n    pre_match\n    .sort_values(\"recorded_at\")\n    .groupby([\"fixture_id\", \"bookmaker\", \"market_id\", \"outcome_id\"])\n    .tail(1)\n    .rename(columns={\"price\": \"closing_price\"})\n)\n\n# Pinnacle's 1X2 home closing lines \u2014 your backtest benchmark\npinnacle_home_closes = closing[\n    (closing[\"bookmaker\"] == \"pinnacle\") &\n    (closing[\"market_name\"] == \"Full Time Result\") &\n    (closing[\"outcome_name\"] == \"1\")\n]\n\nprint(pinnacle_home_closes[[\"home\", \"away\", \"closing_price\"]].head())\n<\/code><\/pre>\n
    That’s the foundation of every serious betting model: use the closing price from a sharp book (Pinnacle is the industry benchmark) as ground truth, compare your model’s predictions against it, and measure whether you’re beating the closing line. If you want the full walkthrough with staking strategy and Kelly sizing, see our betting model backtest tutorial<\/a>.<\/p>\n
    What You Can Build Once the Data Is in a CSV<\/h2>\n
    \n\n\n\n\n\n\n\n\n\n\n
    Project<\/th>\nWhat you need from the CSV<\/th>\n<\/tr>\n<\/thead>\n
    Model training (ML features)<\/strong><\/td>\nClosing lines, opening lines, line movement velocity, consensus vs Pinnacle disagreement<\/td>\n<\/tr>\n
    CLV tracker<\/strong><\/td>\nYour bet’s price vs the Pinnacle closing price at the same timestamp<\/td>\n<\/tr>\n
    Line movement analysis<\/strong><\/td>\nFull recorded_at<\/code> timeline per market \u2014 plot with matplotlib or Plotly<\/td>\n<\/tr>\n
    Sharp vs soft divergence<\/strong><\/td>\nPinnacle\/Singbet vs Bet365\/DraftKings on the same fixtures<\/td>\n<\/tr>\n
    Arbitrage historical research<\/strong><\/td>\nCross-book pivot: was there ever a real arb on this fixture?<\/td>\n<\/tr>\n
    Steam move detector<\/strong><\/td>\nprice.diff()<\/code> per bookmaker per market, flagged when > N%<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
    None of this requires a $299\/month plan. It requires a free API key, Python, and 30 lines of flattening code.<\/p>\n
    FAQ<\/h2>\n
    How far back does OddsPapi’s free historical data go?<\/h3>\n
    Historical odds are available from our archive ingestion start date and are included on the free tier for all sports. Price history is stored per line move, so a fixture may have dozens or hundreds of snapshots depending on book activity.<\/p>\n
    Can I export player props historical data?<\/h3>\n
    Yes. Player prop markets (NFL QB passing yards, NBA points, soccer shots on target) appear in the same \/historical-odds<\/code> response under their own market IDs. The flattening function above handles them identically \u2014 no special case needed.<\/p>\n
    Why does the API limit me to 3 bookmakers per call?<\/h3>\n
    Each bookmaker adds a full price history payload, and some fixtures have 8,000+ line moves per book. The 3-bookmaker cap keeps response sizes manageable. To pull more books, just loop the request with different bookmaker combinations \u2014 the flatten function in Step 4 merges them cleanly.<\/p>\n
    Is there a row count or request limit?<\/h3>\n
    The free tier has per-minute rate limits but no hard row cap. A time.sleep(0.2)<\/code> between requests keeps you well within the limit for most backtesting workflows. For heavy batch jobs or production backtest pipelines, the Pro tier lifts the rate limits.<\/p>\n
    Can I schedule automated exports?<\/h3>\n
    Yes. Wrap the loop in a cron<\/code> job or a GitHub Actions workflow, write the CSV to S3 or Google Cloud Storage, and you have a nightly historical odds pipeline for free. No paid “data export” add-on required.<\/p>\n
    Does the CSV include closed or settled fixtures?<\/h3>\n
    Yes. The \/fixtures<\/code> endpoint returns fixtures regardless of status \u2014 closed, settled, in-play, or upcoming. Filter by statusId<\/code> if you only want settled fixtures for a backtest (“did the bet win?”) calculation.<\/p>\n
    Stop Paying for Data You Need Once<\/h2>\n
    Historical odds should be a commodity. You need it once \u2014 to backtest a model or research a strategy \u2014 and then you’re done. Paying $299\/month for a one-time data pull is an extraction tax, not a product.<\/p>\n
    OddsPapi gives historical data away free because we want developers to build<\/em> on our API, not bounce off a paywall. Grab an API key, run the code above, and you’ll have a CSV in your working directory in about five minutes.<\/p>\n
    Get your free OddsPapi API key \u2192<\/a><\/strong><\/p>\n
    Related reading: Bet365 Historical Odds Guide<\/a> \u00b7 Backtest a Betting Model with Free Historical Odds<\/a> \u00b7 Build a Value Betting Scanner in Python<\/a> \u00b7 Free Odds API: 350+ Bookmakers<\/a><\/em><\/p>\n