Nine Named Ranges and a Macro Called alternate_qb

The football folder had twelve .xlsm files in it, all with the same file date because I had copied them off an old drive in one go. Windows was no help. The real dates were inside — every Office file carries its own modified timestamp in docProps/core.xml, and a spreadsheet is just a zip archive, so you can read it without opening Excel at all — which is how I tracked down the file that became this NFL lineup optimizer for DraftKings and FanDuel.

Eleven of them were from 2014 and 2015. One was from October 2021, had nineteen worksheets instead of five, and was called fanduel football 2021 fast.xlsm. That was the one worth porting.

The spec was in the Name Manager

There was no documentation. There never is. But a spreadsheet optimizer built on OpenSolver has to name the ranges it optimizes over, and those names turned out to be the specification I would have written if I had bothered to write one in 2021:

• qb_stack and qb_stack_big — the same rule at two thresholds: at least one teammate with the quarterback, at least two.
• wr_te and attack — two different counters, one for receivers and tight ends, one for those plus kicker and defense. So the stack could be specified by position, not just by headcount.
• team_plus_oppo — the bring-back.
• oppo_used — the defense-versus-offense exclusions.
• wr_per_team, cap 2. rb_per_team, cap 1.
• score_cap, score_slope — a ceiling that walked downward after each lineup.

Two of those I would not have thought to build from scratch. rb_per_team is the sharper one: two running backs from the same team are splitting one set of carries, so their projections are not independent — they are competing for the same football. A cap of one is a correlation rule wearing a roster-limit costume. And wr_per_team at two is the same instinct applied more gently to receivers, who at least can all catch on the same drive.

The macro that generated constraints as text

The VBA was where it got interesting. Excel has no natural way to say “this lineup must differ from the nineteen I already built,” so 2021-me had written macros that built the constraint as a string and dropped it into a cell as a formula. diversity walked the picked-player column, concatenated the addresses of every selected cell into =$G$14+$G$36+$G$44+..., and wrote that into the next free row of the constraint block. Next solve, that row is a new upper bound.

Crude, and it worked. diversity_core did the same thing twice more with filters — once over quarterbacks, receivers and tight ends, once over everything except the defense. Two overlap caps at two different granularities: you can reuse the skeleton of a lineup as long as you do not reuse its passing game.

Then there was alternate_qb, which shifted a chain of eight named cells down by one — eigth_last_qb takes seventh_last_qb, which takes sixth_last_qb, and so on — so that the last eight quarterbacks were all blocked, not just the previous one. In Python that entire mechanism collapses to three lines, because I can keep a list:

recent = prior_lineups[-window:]
for pid in {qb_of(lu) for lu in recent}:
    prob += pick[index_of[pid]] == 0

That is the honest summary of the whole port. Nothing about the model got better. What got better is that the model no longer has to be expressed in a language that cannot hold a list.

What did not survive

A tweaks sheet with per-position multipliers — running backs at 1.1, kickers and defenses at 0.9 — and a Vegas adjustment that scaled each team’s projections by the square root of its implied total over the slate maximum. That is projection modelling, not optimization, and I left it out on purpose. The optimizer’s job is to squeeze the most out of whatever projections you hand it, not to have opinions about them. You can upload your own projections file and overwrite every number in the pool.

The kicker also did not survive, for the simpler reason that neither DraftKings nor FanDuel uses one in classic NFL any more. Both now run the identical nine-slot roster — quarterback, two backs, three receivers, a tight end, a flex and a defense. The only difference between the two sites is the salary cap: $50,000 on DraftKings, $60,000 on FanDuel. Eleven years of drift between my two workbooks, and the two platforms had quietly converged.

The part I did not rewrite

The baseball optimizer has been live for two months and I did not want to touch it to ship this. So instead of one engine with a sport flag bolted through it, I pulled out the half that is true of every salary-cap sport — fill the slots, respect the cap, make the set diverse — into a core module, and left the sport-specific half behind a small interface. Football supplies quarterback stacking and bring-backs. Baseball will supply batting-order adjacency when I migrate it, which will be after the football version has been wrong in public for a while and I have fixed it.

Both tools run in the same process, on the same server, and answer on different hostnames. Hockey and basketball, when I get to them, are a rules file each.

This is number seven in a series about dragging twenty years of Excel models into Python, one at a time.

Why those constraints are the right ones for football in the first place is its own post. The finished tool is here: a free NFL lineup optimizer for DraftKings and FanDuel.

Things that I use, like, and am affiliated with:
Mint Mobile offers great cell phone service for $15 flat, get $15 off using the link. Get discounted phones with service activation and no contract.
I never spend money before I check Mr Rebates or Rakuten to get cashbacks, rebates, discounts, coupons or cheaper gift cards.