Anti-Tanking Draft Formula

Overview

The J4M Draft Formula is a draft pick allocation system I designed to solve a structural problem in competitive progressive sim leagues: tanking. In a standard worst-to-first draft format, there is a direct incentive to lose once a season is effectively over. The J4M Formula eliminates that incentive by rewarding teams not for how many games they win in absolute terms, but for how much they outperform what they should have produced.

The formula has been in continuous use since the 1969–1970 season of the original J4M league (2005), and has undergone five major revisions over its history. The most significant revision added an exponential factor accounting for the fact that a low-salary team faces only higher-salary opponents and vice versa. This adjustment increased the accuracy of Expected Wins as a predictor of Actual Wins from an R² of 0.714 to 0.913 (and up to 0.95 in some samples).

The formula is currently implemented in Google Sheets with Google Apps Script for automated salary and schedule data collection, and has a Python version for standalone analysis or for implementation in other environments with existing DB where there is reason to incentivize winning over tanking for new picks (fantasy leagues, simleagues, video games, real sports leagues, board games, etc). All examples below use the current simleague implementation framework, but these variables are easily swapped out for respective variables in other implementations or different league sizes or schedules.

Design Goals

The Formula

Variable Definitions

Core Equations

Schedule-adjusted salary differential (% +/- $):

(%) = (((OppSal1*(G/162)) + (OppSal2*(G/162)) + ... + (OppSal_N*(G/162)) - TeamSal) / TILAS)

Expected wins:

ExpW = ((81 * -(%) + 81) ^ (% / -10) + 1)

Target wins at 20% outperformance:

TrgtW 20 = (ExpW + (ExpW * 20%))

Target wins at 30% outperformance:

TrgtW 30 = (ExpW + (ExpW * 30%))

Draft score:

Score = ((((AcW - ExpW) / AcW) * 100))

Interpreting the Score

A team with a score of 20.00 overachieved their expected wins by 20%. A team with a score of -20.00 underachieved by 20%. The score is the primary input for first-round draft ordering. Subsequent rounds use standard worst-to-first ordering.

A score of 20 or higher has resulted in a top-5 pick in every season but one across the full history of leagues using the formula. A score of 30 essentially guarantees the first overall pick.

How the Formula Works

The Schedule Adjustment

The core insight behind the schedule adjustment is that no team plays itself. A low-salary team will always face opponents with higher salaries than its own meaning its “expected” wins at face value underestimate the difficulty of its schedule. A high-salary team faces the inverse: it systematically faces easier opponents by salary.

The (%) variable captures this by computing a weighted average of opponent salaries (weighted by games played against each) and comparing it to the team’s own salary relative to the league average excluding that team. This produces a schedule-adjusted salary differential that feeds into the exponential curve governing Expected Wins.

Before this adjustment was introduced, the correlation between salary and draft pick was -0.034 — essentially no relationship. After the adjustment, that correlation moved to -0.278, reflecting that the formula now correctly identifies lower-salary teams as expected to perform better relative to their schedule.

The Exponential Curve

Expected Wins uses an exponential rather than a linear salary-to-wins relationship for two reasons:

1. The marginal return on salary diminishes at the extremes. A team at 200% of league average salary does not win twice as often as an average team.

2. The schedule-adjusted differential itself already captures relative position. The exponential curve then maps that relative position to a realistic win expectation.

Salary Snapshots

Salaries are not taken as a single point-in-time value. The formula captures 162 salary snapshots across the season (one corresponding to each game played) and averages them. This prevents late-season roster moves (trades, releases) from retroactively distorting a team’s salary-adjusted expectations for games already played. A team that carried a high salary for 130 games and stripped down for the final 32 is correctly assessed at its season-average salary, not its end-of-season salary.

In the Google Sheets implementation, salary data is collected via Google Apps Script triggered three times daily to coincide with WIS game simulation times. This produces a continuous record of salary at each game interval without requiring manual entry.

Standings Sheet Structure

The standings sheet displays both Current Standings (based on actual games played and current salary snapshots) and Projected Standings (based on full-season salary projections). The Projected Standings become the primary reference as the season progresses and individual team salary snapshots fill in. Both use the same formula with the difference being the input salary averages.

Sample standings structure (1973 J4M, game 0 — season not yet started):

The TrgtW columns give each team a concrete win target to aim for. A team expecting to win 67 games needs to win roughly 81 for a score of 20. A team expecting to win 112 games needs to win roughly 134. The win target is not arbitrary — it’s a direct function of each team’s specific salary position relative to its schedule.

Implementation

Google Sheets / Apps Script

The primary implementation runs in Google Sheets. The standings formulas are native spreadsheet functions. Salary and schedule data collection runs on a time-based trigger in Google Apps Script, firing three times per day to capture salary at each game interval.

The (%) formula as implemented in Sheets — one team’s block shown; the number of opponent terms scales to league size, one term per opponent. Each team has its own column:

=SUM((((((H2*(J2/162))+(H5*(J5/162))+(H8*(J8/162))+(H11*(J11/162))+(H14*(J14/162))
+(H17*(J17/162))+(H20*(J20/162))+(H23*(J23/162))+(H26*(J26/162))+(H29*(J29/162))
+(H32*(J32/162))+(H34*(J34/162))+(H36*(J36/162))+(H38*(J38/162))+(H40*(J40/162))
+(H42*(J42/162))+(H44*(J44/162))+(H46*(J46/162))+H48*(J48/162))-C2)/C3)))

ExpW formula (native Sheets):

=((81*-PCT+81)^(PCT/-10)+1)

Score formula:

=(((AcW-ExpW)/AcW)*100)

The salary snapshot mechanism uses a conditional formula per team per game slot. Each cell in the snapshot grid checks whether enough games have been played and, if so, pulls the current salary for that team. A single team’s 162-slot column looks like this (Salary!$F$21 is that team’s current salary; Standings!$P$3 is the games-played counter):

=if(Standings!$P$3 >= 1,  Salary!$F$21,)
=if(Standings!$P$3 >= 2,  Salary!$F$21,)
=if(Standings!$P$3 >= 3,  Salary!$F$21,)
  ...
=if(Standings!$P$3 >= 162, Salary!$F$21,)

This produces 162 cells per team, each returning the salary at the moment that game was played (or blank if not yet reached). The column average — ignoring blanks — then gives the season-to-date salary average that feeds into the (%) calculation. The games-played counter (Standings!$P$3) is updated by a Google Apps Script that fires three times daily to coincide with WIS sim times:

function getData() {
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var queryString = Math.random();
var cellFunction = '=IMPORTHTML("http://www.whatifsports.com/mlb-l/view.asp?lid=145090","table",1)';
sheetName.getRange('A2').setValue(cellFunction);
}

A second script freezes the imported data so that accumulated salary values are not lost when the sheet recalculates:

function freezePositiveOutput() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy of Dump");
var range = sheet.getRange("B2:Z163");
var values = range.getValues();
var lastNonzero = Math.max.apply(null, values.map(function(a, i) {
  return a[0] > 0 ? i : 0;
}));
var truncatedRange = range.offset(0, 0, lastNonzero + 1, 24);
truncatedRange.copyTo(truncatedRange, {contentsOnly:true});
}

Team name-to-version mapping is handled via IFS() formulas that link team names in the schedule dump to their salary column references, allowing the formula to work correctly even as team names change across seasons:

=IFS(F2 = "Chicago Thunderbears", "V2", F2 = "Cincinnati Mudcats", "V3",
   F2 = "Georgia Browns", "V4", F2 = "Houston Colt .45s", "V5", ...)

Python Version

A Python implementation handles historical analysis, regression modeling, and correlation calculations across multi-season data sets.

Core formula implementation:

import numpy as np

def calculate_pct(team_sal, opp_sals, games, tilas):
  """Schedule-adjusted salary differential."""
  weighted_opp = sum(s * (g / 162) for s, g in zip(opp_sals, games))
  return (weighted_opp - team_sal) / tilas

def expected_wins(pct):
  """Exponential curve mapping salary differential to expected wins."""
  return ((81 * (-pct) + 81) ** (pct / -10)) + 1

def score(actual_wins, exp_wins):
  """Draft score — % over/underperformance vs expected wins."""
  return ((actual_wins - exp_wins) / actual_wins) * 100

def target_wins(exp_wins, pct_target=0.20):
  """Wins needed to achieve a given score threshold (default: 20)."""
  return exp_wins + (exp_wins * pct_target)

Correlation and regression analysis (used in the quadrant study):

from scipy import stats
import pandas as pd

# Correlation: Pick No. vs Salary
r_sal_pick, p = stats.pearsonr(df['Salary'], df['Pick_No'])
# Result: -0.278 (weak inverse — higher salary -> later pick, slightly)

# R² of Exp W predicting Act W
slope, intercept, r, p, se = stats.linregress(df['Exp_W'], df['Act_W'])
r_squared = r**2
# Result: 0.913 (up from 0.714 pre-schedule-adjustment)

# Correlation: wins above expected vs pick number
r_overperfm, p = stats.pearsonr(df['Act_W'] - df['Exp_W'], df['Pick_No'])
# R² = 0.881 — wins above expected is the strongest predictor of pick position

Statistical Validation

Key Metrics

Interpretation

The R² of 0.913 for ExpW predicting ActW means the formula is genuinely measuring something real about how salary translates to wins. It’s not a perfect predictor — it leaves 9% unexplained, which is where team management skill, roster construction decisions, and the randomness of a sim season all live. But it’s accurate enough that the Expected Wins serve as a meaningful baseline against which outperformance can be credibly measured.

The weak -0.278 correlation between salary and pick number is exactly what the formula is designed to produce. A perfectly salary-ordered draft would produce a correlation near -1.0. The formula deliberately breaks that link while still preserving a slight advantage to lower-salary teams, which is appropriate since those teams face harder schedules relative to their payroll.

The 0.881 R² for wins above expected driving pick position is the formula’s core validation: the thing the formula is designed to reward (winning more than you should) is in fact the thing that determines where you pick.

Quadrant Analysis: Salary vs. Draft Outcomes

Methodology

I divided all teams in the formula’s history into four salary quadrants (bottom 25%, mid-low 25%, mid-high 25%, top 25%) plus two extreme categories (teams more than 20% below league average, and more than 20% above). I then tracked draft pick placement, draft score, and performance vs. Expected Wins for each group. Pre-adjustment data was excluded; teams credited with pre-adjustment pick positions are credited with their raw, un-adjusted placement.

Frequency of Top-25% Pick (First-Round Territory)

Extreme Low salary teams land a top-25% pick 50% of the time — highest of any group. Extreme High salary teams land a top-25% pick 0% of the time.

Frequency of Top-50% Pick

Frequency of Bottom-25% Pick (Late-Round Territory)

Frequency of Bottom-50% Pick

Average Pick Position and Score by Salary Tier

Net Actual Wins vs. Expected Wins by Salary Tier

Analysis

The formula is working as intended. The distribution of picks is balanced across quadrants — no tier is locked out of any range of picks. The extreme-low salary teams on average get the best picks and the highest scores, and are one of only two categories to exceed their expected wins on average. The extreme-high salary teams win the most games but fare worst in draft position.

The mid-low quadrant underperforms relative to the mid-high and highest quadrants despite being in a salary range where the formula should help them. The data points to a specific cause: roster bloat.

I see these teams season after season — $66–74M teams carrying 1,500+ IP and 6,000+ PA. That depth only adds salary, which increases Expected Wins without increasing a team’s actual chance of winning. For example, in one recent season Detroit had a $43.2M pitching staff with 1,820 IP at 3.24/.240/1.27. The NY team had a $30.5M staff with 1,297 IP at 3.59/.236/1.22. In a progressive, it’s rare that more than 1,250 IP get used. Looking at just the top 1,250 IP, Detroit’s is 2.95/.218/1.20 and NYs is 3.46/.221/1.21. DET is still better, but far closer than the full staff numbers suggest.

The practical rule of thumb: for every $1M you add in salary, you need to win approximately 2 additional games to break even on draft position impact. Excess salary that doesn’t translate to wins is a double drag; it raises your expected wins and doesn’t help you meet them.

Strategy Guide

How You Can Win More (With Less)

The two simple keys for getting a higher score are to win as many games as possible and to keep your salary as low as possible. I’ll focus on the latter part of that strategy first.

Streamlining Your Team Salary

The easiest way to do this is to not draft for depth. If you have a full time SS, you don’t need to draft or trade for another full time SS or even a SS with 300 PA. All that will do is add salary to your team that will not help you win. If the part time SS is better than your full time SS, you’re better off trading the full time SS for another part time SS and/or picks. You want to have as few PA and IP as you’re comfortable competing with. Don’t shortchange yourself though, there’s no AAA, and tired players play horribly, so plan accordingly. And play smart. Also, remember, draft picks aren’t the only (or even the fastest or best) way to add talent to or rebuild your team.

Winning As Many As You Can

The whole point of the formula is to encourage every team to win all season long, essentially to stop the practice of tanking. That said, you can still play smart with your team to ensure more wins over the course of the season, especially if you are a weaker team, by managing your roster and your losses. Particularly if you are short on PA or IP, or only have a few quality PA or IP, this strategy is recommended. Play your stronger players together to ensure a better chance of winning those games and play them against key opponents (division rivals, interleague games, against stronger or weaker teams, etc…). If you have only one or two quality starters put your better hitters in for their starts and play your weaker hitters when the other starters are pitching (assuming a shortage of quality PA, as well). Likewise, manage the pitchers so that you maximize their chance of wins. If you have two quality starters and a bunch of sub 120 IP pitchers, don’t try to maximize your IP by running a two man rotation with a bunch of low quality arms in the bullpen. Instead run a three, four, or five man rotation so that your two quality starters go deep into their starts and basically assure you of winning their starts and run the rest of the lower quality pitchers in as the rest of the rotation in tandems or as lower IP starters with the bullpen behind them this way you have a legit shot at winning at least 40-67% of your teams games as opposed to the possibility of your bullpen blowing every game and barely winning 25-30% of your teams games.

The Short-Term vs. Long-Term Tension

The formula is primarily focused on single season value not career value in determining the value of a pick relative to another player. A team planning for more than the current year will make decisions that may also affect their draft position — this would be a valuation each owner would make based on their perceived value of their current roster and the estimate of the pick they would receive by eliminating that player’s salary from their team — and may also make decisions in a draft to take players that do not help in the short term for the benefit of the long term (like Larry Gura in 1970 who has no real value until 1976), but this is also what might cause Gura to go 3rd in one draft and 18th in another… his short term value vs his long term value — and how he fits each teams needs.

This is a plus to the formula as it encourages strong teams to draft players like Gura who become helpful as their team starts to age and lose players, and leaves players who are stronger in the short term (but are excessive IP or PA for the strong teams now as they will cost them draft positions due to the added, and unusable salary taking them entails) for the weaker teams that need the immediate help.

How the Formula Affects the League as a Whole

This does two things for the league: First, it assures that parity is greater as everyone is trying to win as many games as possible all season long, and; second, more talent is dispersed as stronger teams trade away their excess to weaker teams to streamline ensuring a stronger competitive balance across the league as the weaker teams then have the PA & IP needed to compete.

Formula FAQ

Why does a team with a lower salary have a worse draft position than a team with a higher salary?

This is most likely due to the other team being in the other league. Each team’s salary is primarily evaluated off of their own league’s salary and often the leagues have a salary that differ by a few million $, so a team with a higher salary in one league may actually have lower expectations than a team with a lower salary in the opposite league due to how those teams relate to their respective leagues. With the new formula, this may also be caused by a team having a more difficult division or schedule than your team. This could also be the difference between looking at a single snapshot or current salary for a team rather than looking at a team’s average salary. The average salary is what is used in the formula.

Why does a playoff team have a better draft position than my low-win team?

Using the example above, an average team, expected to win 81 games, that does everything it’s supposed to do and wins 97 games and gets a score of 20, or wins 100 and gets a score of 25, is very likely to make the playoffs, as well as earn one of the top picks. The goal with the formula is to encourage teams to win, so even if a team makes the playoffs they are going to be rewarded for their success in winning more games than could have been expected of them by earning the pick they were targeting.

Will there be further changes to the formula?

Quite possibly. Since it was first introduced between the 1969 & 1970 season of the original J4M league, the formula has constantly been refined to be more accurate and fair (undergoing five major revisions over the years — this newest one included). I’ve also always tried to keep the formula simple and not overly-complicated. Should an even more fair and accurate change be discovered, it is likely that will be implemented as well. At this point I do not see a need for further adjustments — the main areas of weakness in the formula have been solved, and I think at this point any further adjustments would only complicate without adding enough value to justify the complication.

Why salary rather than other statistics or future-season distribution?

While the salary alone is not reflective of ability to win as there are other variables to consider with your team (ballpark, division, salary distribution, etc.), the salary structure is equal for player skill and quality, so that an inefficient poorly managed team with the same salary as an efficient well managed team will have severely less wins. Yet, both teams have equal opportunity to win and equally good and talented teams. The inefficient team can easily trade their bloat to another team for equal talent and become more efficient and thus win more games without increasing their salary.

Basing the formula on total salary versus distribution of salary does provide a firm base of equal footing for measuring performance based on managing a team on over/under-performance.

Historical Scores — J4M Original

Scores at or above 20.00 are listed (scores below 20 were common; the list shows which seasons produced qualifying scores and how many teams achieved them). This history predates several formula revisions; some seasons used modified or simplified versions of the formula.

39 teams across the first 21 seasons of the original J4M achieved a score of 20 or higher (fewer than 2 per season on average). Of those, 10 received the first overall pick. A score of 20 or higher resulted in a top-5 pick in every season.

Adaptability

Schedule and League Size

The formula scales to any league size and any schedule length. The schedule-weighting in the (%) calculation is built around games played against each opponent as a fraction of total games, so it doesn’t assume a fixed number of games any more than it assumes a fixed number of teams. A league with 17 games works the same as one with 162 — the fractions adjust automatically.

That said, shorter schedules do introduce more variance. In an NFL-length schedule (17 games, unbalanced), a team can significantly over or underperform their expected wins purely through luck in a way that a 162-game sample largely smooths out. The formula would still function correctly in the NFL context — it would still reward teams that outperform relative to expectations — but the signal-to-noise ratio is lower, and a team benefiting from a lucky bounce or two in four games can end up with a meaningfully better draft position than one that played the same quality of ball over a full body of work. It’s worth noting in any NFL implementation, not as a flaw in the formula but as a feature of short schedules.

Professional Leagues: A WAR-Based Variation

The salary input can be replaced with any comparable resource metric, which opens the door to applying this directly to real professional leagues. The most natural substitution is WAR — replacing team salary with projected team WAR (based on depth charts at the start of a season, updated as rosters change) and running the same schedule-adjusted expected wins calculation against actual wins.

The reason to do this is the same reason I built the formula for WIS leagues in the first place: the current draft systems in professional sports are broken in ways that actively harm competitive balance and the fan experience, and the lottery fixes nothing.

Baseball’s draft system creates a direct incentive to lose. Once a team is out of contention, there is no upside to winning — every win moves you further from the first overall pick. Teams have figured this out, and the Cubs and Astros showed everyone the playbook: dump salary, lose on purpose for a few years, hoard top picks and international signings, and eventually build a juggernaut. The problem is that when it worked, every other front office noticed. Now instead of 1-2 teams doing this in any given year you have 5, 6, 7 teams that have openly decided they aren’t trying to win. The fans of those teams know it. The league knows it. And the product suffers for it.

The draft lottery doesn’t solve this — it makes it worse (see the NBA). The lottery creates a threshold effect. Teams aren’t just trying to lose, they’re trying to lose enough to get under whatever the lottery cutoff is. A team sitting at 5 wins with a good shot at the lottery has no reason to win games 6 through 20. A team sitting just above the cutoff has an incentive to lose to get back under it. You’ve taken a bad incentive structure and made it more complicated and more gameable, while doing nothing to address why teams were tanking in the first place.

The WAR-based variation of this formula solves the core problem the same way it solves it in WIS leagues. Every team has an incentive to win every game all season long, because the score is based on winning more than your projected WAR suggests you should. A team with a lot of talent that wins exactly as many games as their roster predicts gets a neutral score. A team that wins significantly more than expected gets a top pick. A team that wins fewer than expected — regardless of whether they were supposed to be good or bad — gets a lower pick. There is no advantage to losing. A team with a 40-win projection that wins 40 games picks later than a team with a 40-win projection that wins 50.

The result is the same thing I’ve seen in WIS leagues running this formula for 50+ seasons: everyone tries to win all season long, teams trade talent they don’t need to teams that do (because carrying excess bloat raises your expectations without helping your results), and the draft order ends up reflecting genuine outperformance rather than who was most committed to losing. Competitive balance improves because you removed the incentive to destroy it.

The WAR snapshot mechanism works a little differently than the salary snapshot in the WIS version — projected WAR updates as rosters change through trades, injuries, and transactions, so you’d want to capture depth-chart WAR at game intervals through the season rather than a single preseason projection (similar to how salary snapshots work here). The schedule adjustment translates directly; real MLB schedules have the same imbalanced structure that makes the adjustment meaningful.

Since it is just applying math equations to captured and stored values (Salary/WAR, wins) it can also be run as is in SQL on a DB, or using Python/R on a DB into a frontend. I haven’t built out the DB schema for some these functions as I don’t have an applicable use case that demands it, but the core formula logic runs against the existing table structure, and the calls to the relevant fields are already mapped.

Version History

Code Implementations: Python, R, and SQL

The following implementations translate the J4M Draft Formula into three languages. Each version uses the same underlying schema and produces the same output: a ranked list of teams sorted by J4M score (actual wins minus schedule-adjusted expected wins), with positive scores indicating outperformance relative to salary expectations and negative scores indicating underperformance.

The Python and R versions read from CSV files. The SQL version runs against the relational tables directly, using a parameterized season ID. All three versions scale automatically to any league size, schedule length, or base value structure (i.e., no hardcoded team counts, game totals, or salary vs WAR etc).

Schema

All three implementations assume the following five-table structure:

CREATE TABLE teams (
  team_id     INTEGER PRIMARY KEY,
  team_name   TEXT NOT NULL,
  league_id   INTEGER NOT NULL
);

CREATE TABLE seasons (
  season_id    INTEGER PRIMARY KEY,
  year         INTEGER NOT NULL,
  total_games  INTEGER NOT NULL   -- full schedule length (e.g. 162, 154, 60)
);

CREATE TABLE schedules (
  season_id       INTEGER REFERENCES seasons(season_id),
  home_team_id    INTEGER REFERENCES teams(team_id),
  away_team_id    INTEGER REFERENCES teams(team_id),
  games_played    INTEGER NOT NULL,
  PRIMARY KEY (season_id, home_team_id, away_team_id)
);

CREATE TABLE salary_snapshots (
  snapshot_id  INTEGER PRIMARY KEY,
  season_id    INTEGER REFERENCES seasons(season_id),
  team_id      INTEGER REFERENCES teams(team_id),
  week         INTEGER NOT NULL,   -- 1..162
  salary       NUMERIC NOT NULL
);

CREATE TABLE standings (
  season_id  INTEGER REFERENCES seasons(season_id),
  team_id    INTEGER REFERENCES teams(team_id),
  wins       INTEGER NOT NULL,
  losses     INTEGER NOT NULL,
  PRIMARY KEY (season_id, team_id)
);

Python Implementation

Dependencies: pandas. Input: five CSV files in a data directory. Output: printed DataFrame sorted by j4m_score descending.

"""
J4M Draft Formula — Python Implementation

Inputs : teams.csv, seasons.csv, schedules.csv, salary_snapshots.csv, standings.csv
Output : DataFrame sorted by j4m_score descending

Formula overview
----------------
half        = total_games / 2
team_sal    = average salary across all weekly snapshots for the team
tilas       = average of all other teams' avg_salary (league avg excluding team)
pct         = (sum over opponents of opp_sal * (games_vs_opp / total_games)) - team_sal) / tilas
exp_wins    = ((half * -pct + half) ** (pct / -10)) + 1
j4m_score   = ((actual_wins - exp_wins) / actual_wins) * 100  (positive = outperformed)
trgt_wins_20 = exp_wins * 1.20   (target: 20% above expected — adjust multiplier here)
trgt_wins_30 = exp_wins * 1.30   (target: 30% above expected — adjust multiplier here)

To change schedule length : update total_games in seasons.csv — formula scales automatically.
To change target thresholds : edit the 1.20 / 1.30 multipliers in the results block below.
"""

import pandas as pd

SEASON_ID = 1
DATA_DIR  = "./data"

teams     = pd.read_csv(f"{DATA_DIR}/teams.csv")
seasons   = pd.read_csv(f"{DATA_DIR}/seasons.csv")
schedules = pd.read_csv(f"{DATA_DIR}/schedules.csv")
snapshots = pd.read_csv(f"{DATA_DIR}/salary_snapshots.csv")
standings = pd.read_csv(f"{DATA_DIR}/standings.csv")

total_games = int(seasons.loc[seasons["season_id"] == SEASON_ID, "total_games"].iloc[0])
half        = total_games / 2

sched = schedules[schedules["season_id"] == SEASON_ID].copy()
snaps = snapshots[snapshots["season_id"] == SEASON_ID].copy()
stand = standings[standings["season_id"] == SEASON_ID].copy()

avg_salary = snaps.groupby("home_team_id")["salary"].mean()

league_sal_total = avg_salary.sum()

tilas = (league_sal_total - avg_salary) / (len(avg_salary) - 1)

sched["team_sal"] = sched["home_team_id"].map(avg_salary)
sched["opp_sal"]  = sched["away_team_id"].map(avg_salary)
sched["weighted_opp_sal"] = sched["opp_sal"] * (sched["games_played"] / total_games)

schedule_weighted_opp = sched.groupby("home_team_id")["weighted_opp_sal"].sum()

pct = (schedule_weighted_opp - avg_salary) / tilas

exp_wins = ((half * -pct + half) ** (pct / -10)) + 1

results = stand[["home_team_id", "wins"]].copy()
results = results.merge(exp_wins.rename("exp_wins"), on="home_team_id")
results = results.merge(teams[["home_team_id", "team_name"]], on="home_team_id")

results["j4m_score"]    = ((results["wins"] - results["exp_wins"]) / results["wins"]) * 100
results["trgt_wins_20"] = results["exp_wins"] * 1.20
results["trgt_wins_30"] = results["exp_wins"] * 1.30

results = (
  results[["team_name", "wins", "exp_wins", "trgt_wins_20", "trgt_wins_30", "j4m_score"]]
  .rename(columns={"wins": "actual_wins"})
  .sort_values("j4m_score", ascending=False)
  .reset_index(drop=True)
)

print(results.to_string(index=False))

"""
Edge case note — ExpW exponentiation
The base of the ExpW formula (half × (1 − %)) can theoretically go negative if % > 1,
which occurs when a team's schedule-weighted opponent salary sum exceeds its own salary
by more than one full TILAS unit. A negative base raised to a fractional exponent is
undefined in real numbers and will produce a math error or NaN depending on implementation.
Within the current WIS salary framework, % returns values in [0, 1] by construction,
so this is handled correctly without a coded correction. For non-WIS implementations
with higher salary disparity or unbalanced schedules, this may require either a floor
on the base (e.g. clip to a small positive minimum) or a cap on % (implying a team can
only be so outmatched before penalization stops). The % > 1 case may not be achievable
at all within WIS constraints — noted here for completeness and for any future
adaptations outside that framework.
"""

R Implementation

Dependencies: dplyr, readr (tidyverse). Input: same five CSV files. Output: printed tibble sorted by j4m_score descending with team names joined.

# J4M Draft Formula — R Implementation
#
# Inputs : teams.csv, seasons.csv, schedules.csv, salary_snapshots.csv, standings.csv
# Output : tibble sorted by j4m_score descending
#
# Formula overview
# ----------------
#   half        = total_games / 2
#   team_sal    = average salary across all weekly snapshots for the team
#   tilas       = average of all other teams' avg_salary (league avg excluding team)
#   pct         = (sum over opponents of opp_sal * (games_vs_opp / total_games)) - team_sal) / tilas
#   exp_wins    = ((half * -pct + half) ^ (pct / -10)) + 1
#   j4m_score   = ((actual_wins - exp_wins) / actual_wins) * 100  (positive = outperformed)
#   trgt_wins_20 = exp_wins * 1.20   (target: 20% above expected — adjust multiplier here)
#   trgt_wins_30 = exp_wins * 1.30   (target: 30% above expected — adjust multiplier here)
#
# To change schedule length : update total_games in seasons.csv — formula scales automatically.
# To change target thresholds : edit the 1.20 / 1.30 multipliers in the results block below.

library(dplyr)
library(readr)

SEASON_ID <- 1L
DATA_DIR  <- "./data"

teams     <- read_csv(file.path(DATA_DIR, "teams.csv"),                  show_col_types = FALSE)
seasons   <- read_csv(file.path(DATA_DIR, "seasons.csv"),                show_col_types = FALSE)
schedules <- read_csv(file.path(DATA_DIR, "schedules.csv"),              show_col_types = FALSE)
snapshots <- read_csv(file.path(DATA_DIR, "salary_snapshots.csv"),       show_col_types = FALSE)
standings <- read_csv(file.path(DATA_DIR, "standings.csv"),              show_col_types = FALSE)

total_games <- seasons |> filter(season_id == SEASON_ID) |> pull(total_games)
half        <- total_games / 2

sched <- schedules |> filter(season_id == SEASON_ID)
snaps <- snapshots |> filter(season_id == SEASON_ID)
stand <- standings |> filter(season_id == SEASON_ID)

avg_salary <- snaps |>
group_by(home_team_id) |>
summarise(avg_salary = mean(salary), .groups = "drop")

n_teams          <- nrow(avg_salary)
league_sal_total <- sum(avg_salary$avg_salary)

tilas <- avg_salary |>
mutate(tilas = (league_sal_total - avg_salary) / (n_teams - 1))

schedule_weighted_opp <- sched |>
left_join(avg_salary |> rename(opp_sal = avg_salary), by = c("away_team_id" = "home_team_id")) |>
mutate(weighted_opp_sal = opp_sal * (games_played / total_games)) |>
group_by(home_team_id) |>
summarise(schedule_weighted_opp = sum(weighted_opp_sal), .groups = "drop")

pct_tbl <- schedule_weighted_opp |>
left_join(avg_salary,  by = "home_team_id") |>
left_join(tilas |> select(home_team_id, tilas), by = "home_team_id") |>
mutate(pct = (schedule_weighted_opp - avg_salary) / tilas)

results <- stand |>
select(home_team_id, wins) |>
left_join(pct_tbl |> select(home_team_id, pct), by = "home_team_id") |>
mutate(
  exp_wins     = ((half * -pct + half) ^ (pct / -10)) + 1,
  j4m_score    = ((wins - exp_wins) / wins) * 100,
  trgt_wins_20 = exp_wins * 1.20,
  trgt_wins_30 = exp_wins * 1.30
) |>
rename(actual_wins = wins) |>
left_join(teams |> select(home_team_id, team_name), by = "home_team_id") |>
select(team_name, actual_wins, exp_wins, trgt_wins_20, trgt_wins_30, j4m_score) |>
arrange(desc(j4m_score))

print(results, n = Inf)

# Edge case note — ExpW exponentiation
# The base of the ExpW formula (half × (1 − %)) can theoretically go negative if % > 1,
# which occurs when a team's schedule-weighted opponent salary sum exceeds its own salary
# by more than one full TILAS unit. A negative base raised to a fractional exponent is
# undefined in real numbers and will produce a math error or NaN depending on implementation.
# Within the current WIS salary framework, % returns values in [0, 1] by construction,
# so this is handled correctly without a coded correction. For non-WIS implementations
# with higher salary disparity or unbalanced schedules, this may require either a floor
# on the base (e.g. clip to a small positive minimum) or a cap on % (implying a team can
# only be so outmatched before penalization stops). The % > 1 case may not be achievable
# at all within WIS constraints — noted here for completeness and for any future
# adaptations outside that framework.

SQL Implementation

Pure SQL using CTEs. Tested against PostgreSQL; compatible with any ANSI SQL engine that supports WITH clauses. Replace :season_id with the target season value. Output includes a performance_label column for quick reading with comments added in-line for this portfolio and ease of implementation.

-- J4M Draft Formula — SQL Implementation (ANSI SQL / PostgreSQL)
--
-- Replace :season_id with the target season before running.
--
-- Formula overview
-- ----------------
--   half        = total_games / 2
--   team_sal    = average salary across all weekly snapshots for the team
--   tilas       = average of all other teams' avg_salary (league avg excluding team)
--   pct         = (sum over opponents of opp_sal * (games_vs_opp / total_games)) - team_sal) / tilas
--   exp_wins    = ((half * -pct + half) ^ (pct / -10)) + 1
--   j4m_score   = ((actual_wins - exp_wins) / actual_wins) * 100  (positive = outperformed)
--   trgt_wins_20 = exp_wins * 1.20   (target: 20% above expected — adjust multiplier here)
--   trgt_wins_30 = exp_wins * 1.30   (target: 30% above expected — adjust multiplier here)
--
-- To change schedule length : update total_games in the seasons table — formula scales automatically.
-- To change target thresholds : edit the 1.20 / 1.30 multipliers in the final SELECT below.

WITH

avg_salary AS (
  SELECT
      team_id,
      AVG(salary) AS avg_salary
  FROM salary_snapshots
  WHERE season_id = :season_id
  GROUP BY team_id
),

league_totals AS (
  SELECT
      SUM(avg_salary)   AS league_sal_total,
      COUNT(*)          AS n_teams
  FROM avg_salary
),

tilas AS (
  SELECT
      a.team_id,
      (lt.league_sal_total - a.avg_salary) / (lt.n_teams - 1) AS tilas
  FROM avg_salary a
  CROSS JOIN league_totals lt
),

schedule_weighted_opp AS (
  SELECT
      sc.home_team_id,
      SUM(a_opp.avg_salary * (sc.games_played::NUMERIC / se.total_games)) AS weighted_opp_sal
  FROM schedules sc
  JOIN seasons se
      ON se.season_id = sc.season_id
  JOIN avg_salary a_opp
      ON a_opp.team_id = sc.away_team_id
  WHERE sc.season_id = :season_id
  GROUP BY sc.home_team_id
),

pct_calc AS (
  SELECT
      swo.home_team_id                              AS team_id,
      (swo.weighted_opp_sal - a.avg_salary) / t.tilas AS pct
  FROM schedule_weighted_opp swo
  JOIN avg_salary a
      ON a.team_id = swo.home_team_id
  JOIN tilas t
      ON t.team_id = swo.home_team_id
),

exp_wins AS (
  SELECT
      p.team_id,
      p.pct,
      se.total_games::NUMERIC / 2                                        AS half,
      (((se.total_games::NUMERIC / 2) * -p.pct + (se.total_games::NUMERIC / 2))
          ^ (p.pct / -10.0)) + 1                                         AS exp_wins
  FROM pct_calc p
  CROSS JOIN (SELECT total_games FROM seasons WHERE season_id = :season_id) se
)

SELECT
  t.team_name,
  st.wins                                                              AS actual_wins,
  ROUND(ew.exp_wins::NUMERIC, 2)                                       AS exp_wins,
  ROUND((ew.exp_wins * 1.20)::NUMERIC, 2)                             AS trgt_wins_20,
  ROUND((ew.exp_wins * 1.30)::NUMERIC, 2)                             AS trgt_wins_30,
  ROUND(((st.wins - ew.exp_wins) / st.wins::NUMERIC * 100), 2)        AS j4m_score,
  CASE
      WHEN ((st.wins - ew.exp_wins) / st.wins::NUMERIC * 100) > 0 THEN 'Outperformed'
      WHEN ((st.wins - ew.exp_wins) / st.wins::NUMERIC * 100) < 0 THEN 'Underperformed'
      ELSE 'Met Expectations'
  END                                                                  AS performance_label
FROM standings st
JOIN teams t
  ON t.team_id = st.team_id
JOIN exp_wins ew
  ON ew.team_id = st.team_id
WHERE st.season_id = :season_id
ORDER BY j4m_score DESC;

-- Edge case note — ExpW exponentiation
-- The base of the ExpW formula (half × (1 − %)) can theoretically go negative if % > 1,
-- which occurs when a team's schedule-weighted opponent salary sum exceeds its own salary
-- by more than one full TILAS unit. A negative base raised to a fractional exponent is
-- undefined in real numbers and will produce a math error or NaN depending on implementation.
-- Within the current WIS salary framework, % returns values in [0, 1] by construction,
-- so this is handled correctly without a coded correction. For non-WIS implementations
-- with higher salary disparity or unbalanced schedules, this may require either a floor
-- on the base (e.g. clip to a small positive minimum) or a cap on % (implying a team can
-- only be so outmatched before penalization stops). The % > 1 case may not be achievable
-- at all within WIS constraints — noted here for completeness and for any future
-- adaptations outside that framework.

Automated Pipeline: Google Suite Integration

The Python and R implementations above assume manual CSV input and manual script execution. The automation layer below removes both requirements. When fully wired up:

1. The SQL database exports updated CSVs to Google Drive on a schedule (triggered by a Google Apps Script time-based trigger).
2. The same Apps Script trigger wakes the Google Colab notebook via the Colab API.
3. Colab mounts Drive, reads the fresh CSVs, runs the J4M formula, generates charts, and writes output back to Drive.
4. A connected dashboard (Google Sheets, Looker Studio, or Slides) reads the output files from Drive and refreshes automatically.

The SQL pipeline schedules internally against the DB. The Colab/Drive layer handles the Python and R automation.

Component 1: Apps Script — Trigger and Orchestration

Runs on a time-based trigger. Verifies the CSVs have been updated recently, then wakes the Colab notebook.

// J4M Pipeline Orchestrator — Google Apps Script
//
// Set on a time-based trigger: Edit > Current project's triggers
// Verifies all five CSVs exist in DRIVE_FOLDER_ID and were updated within the last 2 hours,
// then triggers the Colab notebook. On any failure, sends an email alert to the active user.
//
// To change the staleness window : edit the cutoff calculation (currently 2 hrs).
// To change the notification recipient : replace Session.getActiveUser().getEmail().

function runJ4MPipeline() {

var DRIVE_FOLDER_ID = "YOUR_FOLDER_ID_HERE";
var cutoff          = new Date(Date.now() - 2 * 60 * 60 * 1000);
var folder          = DriveApp.getFolderById(DRIVE_FOLDER_ID);
var required        = [
  "teams.csv",
  "seasons.csv",
  "schedules.csv",
  "salary_snapshots.csv",
  "standings.csv"
];

for (var i = 0; i < required.length; i++) {
  var files = folder.getFilesByName(required[i]);

  if (!files.hasNext()) {
    MailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      "J4M Pipeline Failed — Missing File",
      "Pipeline aborted: required file not found in Drive folder.

Missing: " + required[i]
    );
    Logger.log("Missing: " + required[i]);
    return;
  }

  if (files.next().getLastUpdated() < cutoff) {
    MailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      "J4M Pipeline Failed — Stale File",
      "Pipeline aborted: file has not been updated within the required window.

Stale: " + required[i]
    );
    Logger.log("Stale: " + required[i]);
    return;
  }
}

Logger.log("CSVs verified. Triggering Colab...");
}

Component 2: Colab Notebook — J4M Formula with Drive I/O

Mounts Drive, reads the five CSVs, runs the full formula, saves a bar chart and results CSV back to Drive with auto-incrementing filenames, and optionally writes a summary Google Doc via the Docs API.

"""
J4M Draft Formula — Google Colab Automation

Reads five CSVs from Google Drive, runs the full J4M formula, saves a bar chart
and results CSV back to Drive with auto-incrementing filenames, and writes a
summary Google Doc via the Docs API.

Formula overview
----------------
half        = total_games / 2
team_sal    = average salary across all weekly snapshots for the team
tilas       = average of all other teams' avg_salary (league avg excluding team)
pct         = (sum over opponents of opp_sal * (games_vs_opp / total_games)) - team_sal) / tilas
exp_wins    = ((half * -pct + half) ** (pct / -10)) + 1
j4m_score   = ((actual_wins - exp_wins) / actual_wins) * 100  (positive = outperformed)
trgt_wins_20 = exp_wins * 1.20   (target: 20% above expected — adjust multiplier here)
trgt_wins_30 = exp_wins * 1.30   (target: 30% above expected — adjust multiplier here)

To change schedule length : update total_games in seasons.csv — formula scales automatically.
To change target thresholds : edit the 1.20 / 1.30 multipliers in the results block below.
"""

import os
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
from google.colab import drive
from googleapiclient.discovery import build
from google.oauth2 import service_account

DRIVE_ROOT    = "/content/drive/My Drive"
DATA_FOLDER   = f"{DRIVE_ROOT}/j4m_data"
OUTPUT_FOLDER = f"{DRIVE_ROOT}/j4m_output"
SERVICE_ACCT  = f"{DRIVE_ROOT}/j4m-service-account.json"
SEASON_ID     = 1
SCOPES        = [
  "https://www.googleapis.com/auth/documents",
  "https://www.googleapis.com/auth/drive"
]

drive.mount("/content/drive")
os.makedirs(OUTPUT_FOLDER, exist_ok=True)

teams     = pd.read_csv(f"{DATA_FOLDER}/teams.csv")
seasons   = pd.read_csv(f"{DATA_FOLDER}/seasons.csv")
schedules = pd.read_csv(f"{DATA_FOLDER}/schedules.csv")
snapshots = pd.read_csv(f"{DATA_FOLDER}/salary_snapshots.csv")
standings = pd.read_csv(f"{DATA_FOLDER}/standings.csv")

total_games = int(seasons.loc[seasons["season_id"] == SEASON_ID, "total_games"].iloc[0])
half        = total_games / 2

sched = schedules[schedules["season_id"] == SEASON_ID].copy()
snaps = snapshots[snapshots["season_id"] == SEASON_ID].copy()
stand = standings[standings["season_id"] == SEASON_ID].copy()

avg_salary = snaps.groupby("home_team_id")["salary"].mean()

league_sal_total = avg_salary.sum()
tilas            = (league_sal_total - avg_salary) / (len(avg_salary) - 1)

sched["opp_sal"]          = sched["away_team_id"].map(avg_salary)
sched["weighted_opp_sal"] = sched["opp_sal"] * (sched["games_played"] / total_games)

schedule_weighted_opp = sched.groupby("home_team_id")["weighted_opp_sal"].sum()

pct      = (schedule_weighted_opp - avg_salary) / tilas
exp_wins = ((half * -pct + half) ** (pct / -10)) + 1

results = stand[["home_team_id", "wins"]].copy()
results = results.merge(exp_wins.rename("exp_wins"),          on="home_team_id")
results = results.merge(teams[["home_team_id", "team_name"]], on="home_team_id")

results["j4m_score"]    = ((results["wins"] - results["exp_wins"]) / results["wins"]) * 100
results["trgt_wins_20"] = results["exp_wins"] * 1.20
results["trgt_wins_30"] = results["exp_wins"] * 1.30

results = (
  results[["team_name", "wins", "exp_wins", "trgt_wins_20", "trgt_wins_30", "j4m_score"]]
  .rename(columns={"wins": "actual_wins"})
  .sort_values("j4m_score", ascending=False)
  .reset_index(drop=True)
)

print(results.to_string(index=False))

def next_file(folder, base, ext):
  files = [f for f in os.listdir(folder) if f.startswith(base) and f.endswith(ext)]
  nums  = [int(f[len(base)+1:-len(ext)]) for f in files if f[len(base)+1:-len(ext)].isdigit()]
  return os.path.join(folder, f"{base}_{max(nums)+1 if nums else 1}{ext}")

colors   = ["#2E75B6" if s >= 0 else "#CC4444" for s in results["j4m_score"]]
fig, ax  = plt.subplots(figsize=(12, 6))

ax.barh(results["team_name"], results["j4m_score"], color=colors)
ax.axvline(0, color="black", linewidth=0.8)
ax.set_xlabel("J4M Score ((Actual Wins - Expected Wins) / Actual Wins × 100)")
ax.set_title(f"J4M Draft Formula Scores - Season {SEASON_ID}")
ax.xaxis.set_major_formatter(ticker.FormatStrFormatter("%.1f"))
plt.tight_layout()

chart_path = next_file(OUTPUT_FOLDER, "j4m_scores", ".png")
plt.savefig(chart_path, dpi=150)
plt.show()
print(f"Chart saved: {chart_path}")

csv_path = next_file(OUTPUT_FOLDER, "j4m_results", ".csv")
results.to_csv(csv_path, index=False)
print(f"Results saved: {csv_path}")

creds  = service_account.Credentials.from_service_account_file(SERVICE_ACCT, scopes=SCOPES)
svc    = build("docs", "v1", credentials=creds)
doc    = svc.documents().create(body={"title": f"J4M Scores - Season {SEASON_ID}"}).execute()
doc_id = doc["documentId"]

top3  = results.head(3)
bot3  = results.tail(3)
lines = [f"J4M Draft Formula Results - Season {SEASON_ID}

", "Top Outperformers:
"]
lines += [f"  {r['team_name']}: +{r['j4m_score']:.2f}
" for _, r in top3.iterrows()]
lines += ["
Bottom Performers:
"]
lines += [f"  {r['team_name']}: {r['j4m_score']:.2f}
" for _, r in bot3.iterrows()]

svc.documents().batchUpdate(
  documentId=doc_id,
  body={"requests": [{"insertText": {"location": {"index": 1}, "text": "".join(lines)}}]}
).execute()

print(f"Doc: https://docs.google.com/document/d/{doc_id}")

"""
Edge case note — ExpW exponentiation
The base of the ExpW formula (half × (1 − %)) can theoretically go negative if % > 1,
which occurs when a team's schedule-weighted opponent salary sum exceeds its own salary
by more than one full TILAS unit. A negative base raised to a fractional exponent is
undefined in real numbers and will produce a math error or NaN depending on implementation.
Within the current WIS salary framework, % returns values in [0, 1] by construction,
so this is handled correctly without a coded correction. For non-WIS implementations
with higher salary disparity or unbalanced schedules, this may require either a floor
on the base (e.g. clip to a small positive minimum) or a cap on % (implying a team can
only be so outmatched before penalization stops). The % > 1 case may not be achievable
at all within WIS constraints — noted here for completeness and for any future
adaptations outside that framework.
"""

Component 3: Scheduling Options

Google Colab does not natively support scheduled execution. Recommended options:

Option A — Apps Script + Colab Execution API: The Apps Script orchestrator triggers the notebook via the Colab API. Cleanest path; API access may require allowlisting.

Option B — Zapier or Make: A third-party automation service watches for new files in Drive and fires a webhook that wakes Colab. Lower setup complexity.

Option C — Cloud Function bridge: A Cloud Function triggered by Drive file changes calls the Colab API. Most robust for production; requires GCP project setup.

SQL pipeline: Runs on whatever the DB supports natively (pg_cron for PostgreSQL, SQL Server Agent, MySQL Event Scheduler). The CSV export to Drive is a separate scheduled script.

Component 4: Dashboard Refresh

Once results CSVs are written to Drive on schedule, connecting a live dashboard requires no additional code:

Google Sheets: Use IMPORTDATA() or a connected Sheets app to pull the latest results CSV. Refreshes on open and on a configurable schedule.

Looker Studio: Connect to the Drive CSV as a data source. Refreshes the connector automatically and propagates changes to all charts.

Google Slides: Charts embedded from Sheets or Looker Studio refresh automatically, enabling a presentation-ready dashboard with no manual intervention.

Code Implementation Notes

Why the formula scales

The schedule weight (games_played / total_games) and win probability (team_salary / sum of salaries) are computed as fractions, so the formula produces correct expected wins regardless of whether the schedule has 60 games or 162, and regardless of whether the league has 8 teams or 30. No constants need updating when league parameters change.

Snapshot averaging

Salary is averaged across all game snapshots rather than using a single point-in-time value. This accounts for mid-season trades, salary changes, and roster moves, giving a more accurate picture of the resources each team had available across the full season (also easily adaptable to real world implementations around projected WAR+depth charts or other metrics leagues/teams may want to use as their baseline success framework).

CSV column requirements