A few years ago, I was working with a client who ran a small syndicating operation focused on thoroughbred racing. Every morning, his team manually copied odds from three different bookmaker websites into a spreadsheet. It took about ninety minutes a day, introduced transcription errors, and by the time the sheet was ready, some of the odds had already shifted. The inefficiency was costing real money.
That project became one of the more satisfying builds I've worked on at Helion 360 — and it taught me a lot about the practical intersection of data automation, Excel's underused capabilities, and the specific quirks of racing odds data. Here's how I approached it, what worked, and what I'd do differently today.
Why Excel? Can't You Just Use Python?
The first question anyone in a technical role asks is: why not just pipe everything into a Python script or a proper database? The honest answer is that most racing syndicates, punters, and small-scale analysts are not developers. Excel is their native environment. They want to see the data in a grid, apply their own conditional formatting, and run their own familiar formulas. Asking them to switch tools is a bigger barrier than the automation problem itself.
Excel is also more capable than most people realise. Between Power Query, VBA macros, and external API connections, you can build surprisingly robust pipelines without ever leaving the application. So the goal became: automate data extraction entirely within or adjacent to Excel, in a way the client could maintain themselves.
Step One — Identifying the Right Data Sources
Before writing a single line of code or query, I mapped out the data landscape. Horse racing odds data typically comes from a few categories of source:
- Bookmaker websites — live odds, updated dynamically via JavaScript rendering, which makes scraping more complex
- Odds aggregators — sites like OddsChecker or Racingpost compile multiple bookmakers into one view
- Official racing APIs — some jurisdictions offer structured data feeds; in the UK, the Racing API and Sporting Life provide tiered access
- Betfair Exchange API — for exchange-based odds, Betfair's API is the gold standard and provides real-time market data in JSON format
For my client's use case, we decided to combine two sources: a paid odds aggregator API for pre-race prices and the Betfair Exchange API for in-play and closing prices. This gave us a clean, legally compliant, structured feed rather than relying on fragile web scraping.
Step Two — Setting Up the API Connection in Excel
Power Query is the engine here. Most Excel users have never gone deeper than basic table imports, but Power Query can connect directly to REST APIs and parse JSON responses. Here's the general flow I implemented:
- Create a base query that calls the API endpoint with the required authentication headers and date parameters
- Parse the JSON response using Power Query's built-in JSON parsing functions, expanding nested records into flat columns
- Apply transformations — filtering to the relevant race meetings, converting fractional odds to decimal if needed, and standardising horse name formatting
- Load into a named table in Excel, which then feeds all the downstream analysis sheets
For the authentication piece, I stored API keys in a separate configuration table rather than hardcoding them into the query. This made it easy for the client to rotate keys without touching the query logic. A small but important practice when handing a tool over to a non-technical user.
Step Three — Scheduling Refreshes Without Manual Triggers
One limitation of native Excel Power Query is that it doesn't self-refresh on a timer — someone has to click Refresh All or open the file. For a client who wants the data ready before morning trackwork discussions, that's not good enough.
We solved this two ways:
- A VBA macro set to run on file open, which triggers all query refreshes automatically — so simply opening the file at 7am pulls the latest data
- A Windows Task Scheduler job that opens the Excel file at a set time each morning using a small VBScript wrapper, then saves and closes it
This combination effectively gave us automated daily extraction without any third-party tools or subscription services. It's not elegant by enterprise standards, but for a small operation it was reliable and simple to troubleshoot.
Step Four — Handling the Messiness of Odds Data
Anyone who has worked with racing data knows it's dirty. Horse names get abbreviated differently across sources, race times drift by a minute or two depending on how the source timestamps them, and scratched runners sometimes appear in one feed but not another.
I built a matching layer in Power Query using fuzzy matching logic and a lookup table the client could update manually when new horses or tracks appeared. It wasn't perfect — fuzzy matching in Power Query has its limits — but it handled about 94% of rows automatically, which dramatically reduced the manual correction burden.
I also added a dedicated Data Quality sheet that flagged any rows where odds looked statistically anomalous (more than three standard deviations from the field average), where runner counts didn't match between sources, or where timestamps were more than five minutes apart. This gave the client visibility into data integrity without requiring them to audit every row.
What the Final System Looked Like
By the time we finished, the client had a single Excel workbook that:
- Pulled fresh odds data from two API sources every morning automatically
- Standardised, merged, and cleaned the data across a matching layer
- Populated a master odds comparison table with conditional formatting highlighting value discrepancies
- Logged historical odds to a separate archive sheet for back-testing analysis
- Surfaced data quality warnings so nothing slipped through unnoticed
The ninety-minute manual process became a two-minute review. Errors dropped significantly. And because everything lived in Excel, the client could extend and adapt it themselves without needing to call us every time the requirements changed.
What I'd Do Differently Today
If I were building this system from scratch now, I'd probably use Python with openpyxl or xlwings to write directly into Excel from an external script, scheduled via a proper cron job or cloud function. This separates the extraction logic from the presentation layer cleanly, makes it easier to version control, and handles JavaScript-rendered pages better if scraping becomes necessary.
That said, the Power Query approach remains the right answer when the end user needs to own and maintain the system themselves. Choosing the right tool isn't just a technical decision — it's a handover decision. The best automation is the one that keeps working after you've left the room.
If you're running any kind of data-intensive operation and still relying on manual copy-paste workflows, the question isn't whether automation is feasible. It's how quickly you can get it running.