
<p>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 <a href="https://www.pro-football-reference.com/about/win_prob.htm" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Pro Football Reference’s Win Probability Values</a>, we can calculate the expected value of a $100 bet on each Denver Broncos and Carolina Panthers game this season.</p>



<h2 class="wp-block-heading">Expected Value Breakdown</h2>



<p>The formula for expected value is defined as:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/formula.png" alt="Expected value formula" class="wp-image-77849"/></figure>



<p>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:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/denver-broncos-stats.png" alt="Denver bronco stats" class="wp-image-77855"/></figure>



<p>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.</p>



<h2 class="wp-block-heading">Converting Money Lines into Money</h2>



<p>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.</p>



<p>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</p>



<p>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.</p>



<p>The probabilities we’ll use to multiply by the losing values are equivalent to 1 &#8211; P(Win). Let’s translate this into SQL, so we have all the relevant pieces to calculate expected value in one place.</p>



<p>If our table of Broncos stats is creatively named broncos_stats, then the query is:</p>



<pre class="wp-block-code"><code>
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 &lt; 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
)</code></pre>



<p>The resulting table will look like:</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/denver-broncos-converted-value.png" alt="Denver Broncos converted value" class="wp-image-77862"/></figure>



<h2 class="wp-block-heading">Calculating Expected Value</h2>



<p>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.</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>And the resulting chart shows the expected value for a $100 bet each week!</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/denver-broncos-expected-value-weekly.png" alt="Expected weekly value" class="wp-image-77868"/></figure>



<h2 class="wp-block-heading">Plotting Actual Result with Expected Value</h2>



<p>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.</p>



<pre class="wp-block-code"><code>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</code></pre>



<p>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.</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/denver-broncos-actual-value-1.png" alt="Actual value chart" class="wp-image-77877"/></figure>



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/denver-broncos-actual-value-2-770x257.png" alt="Actual value 2" class="wp-image-77883"/></figure>



<p>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.</p>


Calculating Expected Value vs. Actual Results for Super Bowl Contenders

curve-image-unique-image-unique