With the Super Bowl quickly approaching, what better time than now to explore expected value and results in SQL! Using Las Vegas’s money lines for the year, and Pro Football Reference’s Win Probability Values, we can calculate the expected value of a $100 bet on each Denver Broncos and Carolina Panthers game this season.
Expected Value Breakdown
The formula for expected value is defined as:
The x-variables represent a value, and the p-variables represent the probability of that value. Let’s say that we have tables for each of the two teams that holds the weeks of their games, their win probabilities, their money lines, and the results. So it looks something like this:
From this table, we already have the probabilities that we need to calculate expected value. But we don’t have the necessary x-variable values. To do that, we need to convert our money lines into actual money values.
Converting Money Lines into Money
A positive money line represents how much you win by betting $100. So if the money line is +141, then winning a bet of $100 will give you a net sum of $241.
On the other hand, a negative money line is how much you have to spend to make $100. Thus, winning a $100 bet on a -227 money line would give you: 100 + 100 * 100.00 / 227 → $144.05
Keep in mind that we need a value for if the team wins and if the team loses. We just showed how to compute the value for the former case. In the latter case, the value will always be -100 since you’re only losing the money you bet.
The probabilities we’ll use to multiply by the losing values are equivalent to 1 – P(Win). Let’s translate this into SQL, so we have all the relevant pieces to calculate expected value in one place.
If our table of Broncos stats is creatively named broncos_stats, then the query is:
with broncos_moneyline_value as ( select week , date , win_probability::decimal(8, 3) , 1 - win_probability::decimal(8, 3) as lose_probability , moneyline , case when moneyline < 0 then 100.0 * -100.0 / moneyline else 100.0 + moneyline end as value_for_win , -100 as value_for_loss , result from broncos_stats order by week )
The resulting table will look like:
Calculating Expected Value
Now that we have probabilities and corresponding values, we can compute the expected value following the formula shown earlier. For good measure, we’ll include a baseline of 0 to help differentiate positive from negative expected values.
select * , win_probability * value_for_win + lose_probability * value_for_loss as expected_value , 0 as baseline from broncos_moneyline_value where date is not null -- accounts for bye weeks
And the resulting chart shows the expected value for a $100 bet each week!
Plotting Actual Result with Expected Value
Let’s take things one step further, and factor in the actual result of the games to show how we performed relative to the expected value. Since we don’t have a definitive result for the Super Bowl yet, we’ll just set that value to be 0.
select * , win_probability * value_for_win + lose_probability * value_for_loss as expected_value , 0 as baseline , case when result = 'win' then value_for_win when result = 'pending' then 0 else value_for_loss end as actual_value from broncos_moneyline_value where date is not null -- accounts for bye weeks
After applying this to the data for the Broncos and Panthers, we can see how they’ve both performed this year relative to their expected values in each game.
At the moment, the odds seem to favor the Panthers enough to skew the Broncos’ expected value to be $44.30 against the Panthers’ -$9.61. But given the Panthers’ continued success in the face of low expected values, it might be prudent to apply the maxim of “Any Given Sunday” to expected value.