There are many ways and various approaches on how to build a betting model. This article gives an example of how to build a (very) simple power-rating based NBA-model and shows how to implement each step in an Excel workbook. The Excel-file can be downloaded below.
What are we trying to achieve? The aim is to create a projection for an NBA game. When we have the projection (probabilities) we can compare it to odds bookmakers are offering.
Step 2 - Collect data
In theory data could be any numbers that have some link (correlation/explanatory power) to the target. As the goal with a model is to predict uncertain future events as accurately as possible, it is of highest importance to have:
1) Enough data
2) Use data that is accurate
3) Use data that your understand
For this example we take a shortcut and ignore point 1) just to make the model easier to present. As data we use team averages for field goal percentage (FG%), offensive rebounds per game (ORB), defensive rebounds per game (DRB) and personal fouls per game (PF). The data in the excel-file is randomly generated for this example.
Step 3 - Construct the model
To create a projection for the game we calculate expected points for home and away teams by using a power ratings approach. Power ratings usually refers to team quality compared to league average.
We first calculate power ratings for offense- and defense quality for the home- and away team. When we have the offense- and defense power ratings, we calculate a total rating for the home- and away team. For a model that you would actually use, you would probably like to use more variables, maybe different ones than in this example, different horizons (trends) etc.
To calculate offense rating we use FG% and ORB and construct a weighted average (50% of both variables) of these. The offense rating for the home team (Milwaukee Bucks) is calculated in cell G5 and the formula is shown below. The offense rating for the away team (New Orleans Pelicans) is calculated in cell G6.
For defense rating we use DRB and PF and construct a weighted average (50% of both variables) of these. The defense ratings are calculated in cells H5 and H6. Take note that for defensive rating a lower value (rating) is better.
Total rating, which is calculated in cells I5 and I6, is a combination of offense- and defense ratings. For the home team total rating is home team offense rating x away team defense rating and for away team vice versa (away team offense rating x home team defense rating).
Next step is to convert total ratings to expected points, which is done in cells J5 and J6. Expected points is calculated by multiplying total rating with league average points.
Win probability is calculated (cells K5 and K6) by using a Poisson distribution and using the expected points we just calculated together with league average points as inputs. A Poisson distribution matrix is set up on the 'poisson' -worksheet and it is generated based on the expected points and league average points (cell AS4). The values in the matrix present the probabilities for different scorelines. Using a Poisson distribution for modelling the probability for different scorelines is a good place to start when building a prediction model. It is not a perfect fit though, and you might wan't to consider making some adjustments to it. But that's not within the scope of this example, so let's forget it for now. If you are interested, you can take a look at an article by Pinnacle that touches the subject.
Now when we have win probabilities for both the home and away team we want to compare these with odds that bookmakers are offering. To convert a probability to fair odds you simply divide 1 with the probability.