Andreas Djokic
Andreas moved to San Francisco to chase his startup dreams. Following his backend work with algorithm design, he joined Periscope Data to help others find the fun side of data.

The High-Performance SQL Blog

Calculating Expected Value vs. Actual Results for Super Bowl Contenders

Calculating Expected Value vs. Actual Results for Super Bowl Contenders
February 4, 2016

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 for 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.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?