Grab the raw data, raw and relentless
First thing’s first: you need the stats before you can talk money. Pull the play‑by‑play CSV from the NFL API, scrape Vegas odds from a reputable site, merge them into one spreadsheet, and stop whining about missing numbers. Data is the fuel; without it, your model is a dead horse.
Clean it like a surgeon
Now you’ve got a mess of columns—yards, pass attempts, over/under, spreads—like a junkyard in a hurricane. Use FILTER to yank out rows with blanks, TEXT‑TO‑COLUMNS to split “Team (Home)” into separate fields, and TRIM every string. A single #N/A can poison the whole analysis, so hunt it down.
Build the core metrics
Here is the deal: calculate EPA (Expected Points Added) per play, then aggregate by team and by game. A quick SUMIFS with a helper column for “offensive” versus “defensive” does the trick. Next, crank out a rolling 5‑game average; the Excel function OFFSET lets you slide the window without VBA.
Turn numbers into edge
Next, compare your EPA projection to the opening spread. If your model says Team A should win by 7 and the line is -3, you’ve spotted a 4‑point value. Use IF to flag every line where your projection outpaces the book by more than a threshold. Conditional formatting will light up the cells—green for profit, red for loss—so you can eyeball opportunities faster than a quarterback reads a defense.
Visualize the trend
Throw a scatter plot on the sheet: x‑axis, projected margin; y‑axis, implied odds. Add a trendline and watch the R‑squared climb. A well‑placed chart is worth a hundred rows of formulae; the brain digests visual patterns quicker than any number dump.
Automate, don’t babysit
Set up a macro that refreshes the data source each Sunday night, runs the calculations, and emails you a summary PDF. One click, zero stress. If you’re comfortable with Power Query, let it handle the import and transformation—so you never stare at raw CSV again.
Finally, remember the rule of thumb from americanfootballbetuk.com: never trust a model that doesn’t account for injury risk. Add a simple VLOOKUP that pulls the latest injury report into a “risk factor” column, multiply your projected margin by (1‑risk), and you’ve built a safety net directly into Excel.
Actionable advice: tomorrow, open a fresh workbook, drop in last week’s game data, and build the EPA‑vs‑spread IF‑flag formula. Watch the conditional formatting light up and place your first data‑driven bet.
