If you’re trading football matches weekly, it’s likely you use some kind of ‘model’.  An Excel model consists of input, process and output cells linked usually by Excel formulae.  Examples of Excel models for football trading including the one we build in New Horizons for Football Traders (based on Andrew Mack’s work) - and a Poisson model is another common one in football analysis.  The inputs are teams and other information such as goals scored; the outputs are usually ‘implied odds’ that we can compare to real-world odds to find value.

But, modelling is not the focus of this series; the question is rather: how can we quickly put dozens (or hundreds?) of fixtures into a model and extract the figures we need?  You might spend hours doing this every weekend, so how can we speed it up?  Welcome back to Automate Your Weekend Process!

To start with, a health warning!  We’re now working with three open files (fixtures, master, model) so it’s important to understand which file you’re working in when writing VBA code.  Too many times I have closed (and not saved) a file having inadvertently written 2-3 macros in there!  To do so loses the code – and a good chunk of your sanity.  So make sure you’re using the object explorer in the top-left of the VBA editor to understand where you’re working, and which files you can safely close.

The techniques I use in the video we have seen many times on Excel (VBA) for Football Traders.  First, we define a range to work with, in this case the home teams.  Second, we create a loop to work through the range.  Third, we use an a=b operation to transfer the home and away teams into the model.  Finally, we reverse this operation (effectively b=a) after having ‘run’ the model, to transfer implied odds back into the master file.

Try to focus not just on the techniques but also on what I called the Excel ‘metaskills’ – the skills that make everything else easier.  Debugging is one of these: if you can learn to debug frequently and quickly, your programming can take a big step forward.  I do it in the videos all the time!  So, don’t just copy the code I write, adapt it to your situation and take the time to get it working.

Topics and Techniques