Welcome to the New Horizons series!  Now, statistics is littered with obscure concepts that make it difficult to understand - remember those A-Level Maths lessons?  I certainly do!  That's why we can't just 'jump into' predictive modelling techniques.  Very quickly we'd get lost - and demoralised.  Rather, we have to build from the ground up with the basic ideas.  As always on Excel (VBA) for Football Traders, the purpose is to equip you with the skills and knowledge, not simply to show you impressive Excel models.

In this first video, we consider how we might 'model' the number of home goals scored in an EPL game and use the question to explore some basic concepts in statistical modelling.  The idea of a 'distribution' is key, so we build one ourselves (after a 'pen-and-paper' demo!), then discuss some of the useful probability distributions that statistics offers us.  We also do a quick data analysis and run through how to create a chart to 'visualise' a distribution to allow us to analyse it, and consider the critical question: 'to what extent does this distribution represent what we wish to model?'

Topics And Techniques

- Probability Distribution
- Normal Distribution
- Uniform Distribution
- COUNTIF
- Column Chart
- Line Chart
- RANDBETWEEN
- RAND

In the second part of the New Horizons series, let's begin to get to grips with probability distributions.  A 'probability distribution' is simply a typical arrangement of data.  Over time, statistians have found that certain distributions of data occur often in the real world.  Our question today is: which is these distributions best represents number of home goals scored in a game?  Using a distribution leads to theoretical odds for particular outcomes; that, in turn, can help identify 'value' and promising bets.

In this video, we look at the normal distribution which you might know as a 'bell curve'.  It's a symmetrical distribution and is one of the most commonly used in statistical analysis.  But, is it useful for forecasting number of home goals?  We learn how to 'set up' the distribution in Excel using the mean and standard deviation parameters, then 'sample' the distribution by inputting a random number between zero and one.  After some adjustments, we can then create a chart and evaluate its suitability by comparing to the real-world phenomenon: number of home goals.  How good is the 'fit'?  It gets quite theoretical in places but hang in there!  It should all make sense in the end ...

Chris also explains why football trading is guerilla warfare!

Tools And Techniques

- NORM.DIST formula
- NORM.INV formula
- Mean Average
- Standard Deviation
- RAND formula
- Sampling A Distribution
- Goodness Of Fit
- COUNTIF
- Line Chart

You've probably heard people talking about the Poisson distribution in relation to football trading.  But, why is that?  And, is it actually any good?  Let's find out in this video ...

First, we consider the concept of the Poisson distribution - its shape, and why it lends itself to modelling the number of goals scored by a single team in football matches.  Next, we consider the tools available to us in Excel to model the Poisson distribution: yes, a =POISSON formula is available, but a formula to 'invert' the distribution like =NORM.INV is not.  This poses a problem: how to 'sample' the Poisson distribution, as we did for the Normal distribution, in the previous video?

The answer lies in Monte Carlo simulation and custom probability distributions in Excel.  Understood correctly, this bundle of techniques opens up a world of possibilities for football traders.  That's because a custom probability distribution allows us to 'tweak' a conventional distribution to optimise 'goodness-of-fit' (how well it replicates what we're trying to model in the real world eg. no. of home goals).  Moreover, using Monte Carlo simulation, we can create our own 'bespoke' probability distributions from scratch!  Let's see how in this video ...

Tools And Techniques

- Poisson Distribution
- POISSON Formula
- Custom Probability Distribution
- Monte Carlo Simulation
- VLOOKUP Formula with TRUE
- Implied Ranges

We've done enough preparation in the introductory concepts - now it's time for us to try to build a statistical sports model in Excel that might help with football trading.  To help us, we'll adapt Andrew Macks' model from his book Statistical Sports Models In Excel.  Yes, you should absolutely buy this book.  You can access his Excel models that I reference in these videos when you do so, but I do not intend to share them; but, you can of course access the files I use, as usual.

The truth is that, for the non-specialist, the models are rather complicated.  So, let's take the first part of the Bradley Terry model ONLY and attempt to adapt it to our purpose.  The model uses different types of regression analysis to generate so-called 'logistic strengths' (simply 'ratings') for each team; these are translated into 'margins of victory' (MOV) which gives us a result, and implied odds.  Also included in the model is a facility to 'predict' a result and find value by comparing with real-world odds - we'll get to that later!

In the first video on this model, I explain the concept of 'regression' (as far as I understand it!) and demonstrate how the model is built according to Andrew's explanation.  Along the way, we tap into some of Excel's most powerful facilities (Solver, anyone?) and even improve the layout and structure of the proposed model.  Sorry, I just couldn't resist ...

Tools And Techniques

- Concept of Regression
- Calculate MOV (Margin Of Victory)
- Model Layout And Structure
- VLOOKUP
- Named Ranges
- EXP
- LN
- IF
- Excel Solver

Regression!  For me, it's something of a nemesis.  I've never fully understood it, let alone applied it successfully in the football trading world.  Hopefully, that'll all change today!

The second part of the Bradley Terry model from Andrew Mack's Statistical Sports Models In Excel book centres on regression analysis.  First, what on earth is 'regression'?  Good question!  Regression tells us the correlation between two real-world variables.  In our model, for example, we would like to understand to what extent the 'logistic function' (calculated in the last video) explains margin of victory.  In others words, does the fancy measure we have created actually explain real-world performance.  And, to what extent?

If the answer is 'very much so', then we have an interesting model!  First though, we have to understand a regression analysis report since, without interpreting this, we can't see if the model is any good.  It's not easily interpreted, however, since it's littered with statistical terms.  ANOVA, anybody?  Sum of squares?  In the video, I do my best to explain the terms and point out the most important features of the report.

Finally, we replicate the analysis that Mack demonstrates in the book for our Premier League model.  Does our fancy 'logistic function' measure really explain the results of football games.  Well, kind of ...

Tools And Techniques

- Regression Analysis
- X and Y Variable
- Hypothesis
- Line Fit Plot
- R Square
- Residuals
- Sum Of Squares
- Standard Error

No football trading model is complete without a prediction mechanism. These do something essential: they generate 'theoretical' or 'implied' odds according to the model; we can then compare these odds to what is on offer in the real world (ie. Betfair) to identify value opportunities.  It's a common process in football trading, and the best in the business are really good at it!

In the video, I build a prediction mechanism for the Bradley-Terry model based closely on the example from Andrew's book, though we do make some minor improvements :-)  Along the way, we apply formulae such as VLOOKUP, the normal distribution and complete the process with a data table to instantly create odds for multiple 'margins of victory'.

So, is this it?  Are we ready for success in sports trading now we have completed the Bradley-Terry model for Premier League data?  Well, I wouldn't say so.  As I explain at the end of the video, the real skill lies in exploiting a model (not simply 'building' it), and constructing a repeatable process around it that allows you to exploit a small edge over a long period of time.

Want to see more from the New Horizons series?  Let me know in the comments under the video, or in the community forum.

Tools And Techniques

- VLOOKUP
- COUNTIF
- Column Chart
- Normal Distribution
- NORMDIST
- Data Table