NFL Odds Spreadsheet Makes It Easy. After years of being tired of switching back and forth between Excel and my browser to see how the lines compared to my model, I decided to build something that would make things easier. A sports bet tracker is the simple, but essential tool to maximizing your potential on the betting market. This useful, preforumulated Excel spreadsheet, will help you manage your wagers, by keeping track of.
Ever wondered how you are actually doing in sports betting?
Just like tracking your finances can be an eye opening experience (I spent how much at restaurants last month?!), tracking your bets can shed some light on your performance.
Download the free sports bet tracking spreadsheet below to get started (available for both Excel and Google Sheets):
If you want to measure your performance and see where you are succeeding and failing, you need to track it.
With this free tool, you can see your performance broken down by various dimensions.
Have a great ROI on betting NBA 2nd halves? Getting solid closing line value on NFL point spreads? This spreadsheet allows you to answer questions like this and more.
While the spreadsheet is pretty straightforward, I’d like to walk you through how it works.
Everything lives in the “Bet Log” tab. This is the only place information is manually entered. Once the data is entered there, all other tabs will automatically populate.
In the “Bet Log” tab, blue columns are required while red columns are optional. The more information you input, the more useful the spreadsheet will be.
Entering things like the closing line, while slightly annoying, will also be the most important to your success.
Each tab will have different graphs and tables that show your performance. The beauty of this is that you can filter the data by any dimension you like.
Any yellow cell is an “input” cell that can be changed. All of these are dropdowns that are pre-populated based on the information you enter in the Bet Log.
To add new leagues and teams, you will do so in the “REF” tab. This tab holds all of the lookup information for the dropdowns throughout the spreadsheet.
Again, the cells available to modify are in yellow. You can add the following dimensions:
Deciding what to track is important in determining how you measure success. The spreadsheet tracks the following key metrics:
Closing line value (CLV) is a measure of how much better or worse the odds you bet at were compared to where they closed.
If you believe the markets you are betting into are efficient (NFL point spreads, MLB moneylines, etc.), then CLV is a great predictor of long term success.
All you need to do is input the odds you placed your bet at as well as where the odds closed. Preferably you use a market making sportsbook like Pinnacle to decide what the “true” closing line was.
Profit is about as simple as it gets. Are you making or losing money?
While this is the “bottom line”, surprisingly it isn’t always predictive of long term success. Still, you will obviously want to see how much money you have made or lost.
This is what most people tend to look at. It is a measure of how profitable you are relative to how much you are risking.
While at the end of the day, the money in your pocket is what matters, this metric focuses more on results rather than process and is a measure of efficiency.
ROI isn’t as predictive of long term winning as CLV, but is useful to track to see where you stand.
This one is simple, yet will likely give you insights into where you are putting your money.
If you have a model, does it consistently value the Dallas Cowboys differently than the market? Thus making many of your bets on the Cowboys? Analyzing your risk by league/team/bet type can give you these types of answers.
Bankroll will track our running total of how much money you have in your accounts across all sportsbooks. You can also see this trended over time to help you see any changes in your betting strategy and how that has affected your bankroll.
It is very useful to see, at a glance, where your money lies. Is 95% of our bankroll at FanDuel? Maybe you should shift some to DraftKings.
Having these metrics available is important, but insights really come from slicing the data by different dimensions.
Tracking your performance by league or team can give you clues into where your strengths or weaknesses are.
Do you watch every second of every New York Knicks game? Think you have an edge on Knicks games? You can find out using the spreadsheet.
Same goes for leagues. Do you follow NFL closely but use strictly numbers for NCAA Basketball? Compare the performance of the two and see what’s working.
Looking at performance by bet type can also shed some light on your process, especially if it is model driven.
Track your performance by the following bet types:
You can also use the “Tag” field to designate special types of bets. For example, if you want to see your performance on moneylines for NBA 2nd halves, you would put “2H” (or something similar) in the Tag field and “moneyline” in the bet type field.
A common way to analyze performance is to look at metrics trended over time.
Look at any of the metric/dimension combinations above trended over any time period you’d like.
Want to see your performance over the last 14 days? Or how about the last 12 weeks? Both are possible here.
The sports betting tracker is also available on Google Sheets. While the features are the same as the Excel file, Google Sheets has some notable benefits:
How do you build a sports betting model? What steps are involved? What do you need to consider? Follow these steps to build your own quantitative model, and take your betting to the next level.
In it's simplest form a sports betting model is a system that can identify unbiased reference points from where you can determine the probability for all outcomes in a particular game.
The model will ultimately be able to highlight profitable betting opportunities, by judging a team's true ability more accurately than a bookmaker.
However, building a sports betting model can be difficult and time consuming. There are various instructions and orders advised for you to follow when creating a model, which can complicate the process.
With that said, once you have created a successful betting model, it can show you opportunities that the general betting public simply wouldn't consider.
Let's begin.
For this example we use an approach similar to the Actuarial Control Cycle – a quantitative risk assessment employed by insurance companies. There are five main features:
This appears simple, but many sports bettors miss the point their betting model is trying to accomplish.
Once you have created a successful betting model, it can show you opportunities that the general betting public simply wouldn't consider.
Without an aim you could be overwhelmed with numbers and lose focus of your overall goals.
Although you may argue you can get the data first to see if there are any patterns, this would still need to be tested against a number of hypothesis, each with a different aim.
Therefore starting with a specific, rather than a generic aim, is strongly recommended.
The next step is to formalise your investigation into numerical form by selecting a quantifiable metric.
These first two steps relate to defining the problem stage of the Actuarial Control Cycle.
Every model needs data so you can integrate it into your algorithm. There are two ways of collecting data – by yourself, or by using other published data online.
Luckily, there is a plethora of data available on the Internet, some of which is free, while some websites offer a paid service.
Once you have the data, you may realise that there are queries that need to be taken care of.
If we are looking at Premier League teams for instance, should you consider all matches or just their league games? It's possible to make adjustments if the team in question had players missing, or had a mid-week Champions’ League clash.
This is where you can exercise your judgement, determined by what your aim is.
This is where the mathematics comes into play given there are so many models to choose from or invent.
There is a plethora of data available on the Internet, some of which is free, while some websites offer a paid service.
We have proposed a number of models in the past and they can be as complex or as simple as you wish. Our recommendation is not to overcomplicate.
This step can be interchanged with step 3 as the data may lead you to use a particular model, or a particular model may require specific data.
Each model will have a number of assumptions, and you should be aware of their limitations. You may forget to do this, but it's absolutely vital.
For example a significant contributor to the financial crisis in 2007-08 was the misuse of derivatives caused by a misunderstanding of assumptions in contracts such as Collateralised Debt Obligations and Credit Default Swaps.
Previously in this article we highlighted how averages and standard deviations assume events are normally distributed. This for example would need be tested.
The next step is to actually build the sports betting model. There are numerous tools to use including online calculators, Excel, MatLab, Java, R programming and VBA.
You don’t have to be a wiz at programming to build a sports betting model, but the more you understand the functionality, the better equipped you will become when testing and analysing the data.
You don’t have to be a wiz at programming to build a sports betting model, but the more you understand the functionality, the better equipped you will become when testing and analysing the data.
It's paramount that you test the efficiency of any sports betting model to understand how sensitive it is to the results.
In any case the results of the model may lead us to reconsider any of the previous steps.
The key question as always is whether or not the model is making a profit? Therefore you’d need to test that – leading you to running through the cycle again.
Assuming that an adequate model has been built and tested, it needs to be maintained as time progresses. This leads us back to the starting point – defining future aims.
Understanding the processes involved is paramount when learning how to build a sports betting model.
Quantitative modelling isn’t just about taking a model and applying it, there are a number of processes – not necessarily in the order stated – which should be completed.
Following this process won't guarantee a profit-making model, but it will ensure you are considering the fundamental aspects that are needed to build a new sports betting model.
For an example of how to build a betting model, click here.
Dominic Cortis is a lecturer with the Department of Mathematics at The University of Leicester; and an assistant lecturer at The University of Malta. He is an associate actuary and his research focuses on sports analytics as well as financial and betting derivatives.