In this case we will be using the https://www.pro-football-reference.com/years/2022/games.htm for our query
Step 3. Transform & Clean Data
I tend to get rid of the date, day of the week, time . and boxscore column
Step 4. Change Criteria From Winning & Losing to Home & Away
In this sheet we used IF statements to rearrange the data into home and away rather than winning and losing
Step 5. Calculate Margin of Victory for Home Team
In this step we just take the home team score and substract the away team score
Step 6. Create Place Holders for Remaining Parameters
Average Margin Is just the average home win margin for the current season, you can calculate the average for all historical NFL season to identify a better home team advantage. Also set all ratings to 0 for each NFL team so you can get a baseline
Step 7. Calculate All Parameters
Calculate predicted win margin by taking average home win margin adding home team rating and subtracting away team rating
Step 8. Calculate Sum of Squared Errors at the end of the last Game
Sum all squared errors, must be deleted and recalculated when query is refreshed after results are posted
Step 9. Use Excel Solver to Create Team Ratings
If you do not have excel solver you must download it before continuing to the next stepDepending on the setup of your sheet reference cells may differ. Essentially for Set Objective you are going to want to input the sum of squared errors and minimize it. In the changing variable cells you are going to want to input the cells referencing your team ratings
Step 10. Your Model Should Now Provide New Ratings
What Can I Do With This Model?
This model will allow you to come up with your own spreads based on how teams have performed during the season. Be cautious as this model does not take into account injuries or substitutions. You can use this as a supplemental tool to make better betting decisions
