This series is about creating a ‘click-of-a-button’ process to do the Excel-based work you currently do manually before your main football trading activity at the weekend.  

In other words, it’s about saving time that you could use to think more analytically about your trading … or to do something else altogether!  It’s about harnessing the power of Excel and VBA to work efficiently and avoid those situations that leave you thinking, ‘There must be better way!’.  Welcome to Automate Your Weekend Process.

The first session contains no technical content.  It’s about establishing the basics: the purpose of this course, what you can (and cannot) expect to get out of it, and a shared understanding of what is meant by a ‘weekend process’.  I deliver it largely though Powerpoint with just a few quick demos in Excel.

I invite you to think what ‘automation’ might mean to you?  

Yes, a ‘click-of-a-button’ process that provides all the analysis you need would be perfect: but is it realistic?  Many of the real-world solutions I’ve created are more ‘semi’ than fully automatic.  You’ve got to think what is possible for you according to your current level of Excel (VBA) ability and the time you can invest in this course.  Yes, automation cannot be achieved overnight; you should take a few months to work through this course and gradually implement the ideas in your setup.

With that said – what’s this session all about?  I share an overview of a typical ‘weekend process’ based on the dozens of projects I’ve done in football trading.  Hopefully it represents your process well enough for this course to be useful.  You’ll see it consists of 3 files: (1) a fixture file containing the weekend’s games (2) a model that does the analysis and (3) a summary file or ‘database’ of the season’s bets and results.  Note the model itself is not the focus of this course – there are other courses about modelling in Excel (VBA) For Football Traders, including the New Horizons series.

With that framework established, I talk through the issues you’ll encounter when trying to create this ‘automation’ and boil it down into 5 ‘painpoints’ that we’ll focus on through this series.  I preview a few snippets of VBA code that might get the juices flowing.

This course is more challenging than anything we’ve done on Excel (VBA) For Football Traders.  I invite you to be deliberate about it: take the time now to write down your objectives and consider if they’re really achievable.  Managing your own expectations is really important – you are your own toughest customer!  Then, I hope you’ll enjoy the coming weeks and months as we work though the automation process together.

Tools And Techniques

In the first ‘workalong’ video in this series, we deal with the tricky topic of working across workbooks.  Yes, because we’re talking about a ‘process’, multiple files are involved, so ability to move between them with Excel VBA is essential.  In the video, we first look at how to reference another file with Excel VBA.  Then, we consider the more difficult task of transferring data between files, specifically the current day’s fixture data, the logical starting point for any weekend process.

Typically, you might think ‘copy / paste’ for this job.  With Excel VBA, however, a ‘direct data transfer’ (simply a = b) is much more efficient, as long as you can handle the coding!  (I mess things up at least once in the video …)  Having felt the power of this basic mechanism, it’s time to move to the next level and add ‘dynamic quality’; in other words, A VBA routine that works for 1 fixture as well as it works for 100.  Get ready for step up in difficult here!

We use multiple powerful VBA techniques including string variables and the awesome .end syntax to get everything working … eventually.  Yes, another debug is required before the routine works smoothly.  But, that’s the nature of Excel development, and debugging is what I call an Excel ‘metaskill’ – it’s a super power that makes everything easier.  So make sure you can do it!

Finally, we create a button in the Summary file to trigger the routine and there we have it – a slick macro to do a manual job.  It’s only a starting point but we’re now ready for the next stage in automating your weekend process … 

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

The objective of session 4 is to find ‘value’. So far, we’ve imported fixtures to a summary file and generated our own ‘odds’ by transferring each fixture to a model and collating the model outputs.  Now, that’s cool and would save you some time.  But, to find value we need to do more.  In this video, we look at importing real-world odds from a fixture file to compare to our odds, and identify value bets.

This means changing the layout of our main interface (or at least the sheet we interact with most at present) – the Matches sheet in the Summary file.  You might be thinking: Chris, but shouldn’t we have planned for this?  Why are we changing the layout now?  Shouldn’t we fix that at the beginning?

This is a valid point and, perhaps, we should have spent more time planning the file structure and sheet layouts.  At some point, however, you do need to start coding!  Too much planning can risk ‘analysis paralysis’ – doing nothing because the task seems too daunting.  Moreover, new requirements always emerge on Excel development projects: perhaps you’ve already asked yourself, ‘If I can do that, perhaps I could do this too?’  So, although planning is important, it’s normal to adapt the code to a better layout at some point.  That’s what we do in this session.

First, we consider the new requirements – including the need to import real-world odds data from the fixture file.  Then, we quickly sketch out a new column structure and set about adapting the code.  This is a tricky job, no doubt, but it’s made easier by the best practices we have applied so far on the project: informative variable and macro names, comments and annotations and concise routines that are easy-to-understand, among others.  With a steady and systematic approach, the revisions are actually straightforward.  Remember to test as you go through, too – don’t leave all the testing until the end!

In the process, we touch on some high-level discussions.  The revised code contains some repeated structures – might we be able to avoid these?  It’s possible with a more sophisticated data transfer mechanism which, though not necessary for the project, would streamline the code.  Though the current approach works well, it’s a practical rather than ideal solution.  Let me know in the comments below the video if you’d like me to explore this further in this series.  Onwards!

Topics and Techniques

Yes, the two activities we cover in this session – formatting and clearing data – are not absolutely essential in your weekend process.  Omitting them, however, could cost you a huge amount of time in the long run.  So, can we deal with them now, in way that means we’ll never have to think about them again?  At least, that’s the goal for this session!

First, a macro to ‘clear out’ the previous week’s data is required.  You could easily assume a maximum number of rows (200?) and simply clear that range each week.  I prefer a dynamic solution, however, since I would always worry about the tiny possibility of more than 200 fixtures!  A dynamic solution can provide peace-of-mind and in the video we explore how to do it. Phew …

Cell formatting is a difficult topic in Excel since what appears ‘good’ is always subjective.  I try to think about formatting in a functional way: does it actually help you read the data and process it?  This is called ‘assimilation’ and formatting should support it.  Having created a helpful look, the next challenge is to programme it in VBA.  ‘Well, that’s easy enough with the macro recorder’, you might be thinking.  You might be surprised by my approach that not only saves on code but allows you to make formatting changes without visiting the VBA editor.  Aparajita, anyone? 

Now we’re deep in this project, we’re never too far from a high level discussion.  And there are several in this video including short vs. long macros, static vs. dynamic approaches and more.  How much these discussions matter to you depends on the extent of automation you’re aiming for.  Want a slick and robust ‘click-of-a-button’ process that will serve you well for years?  Then try to get to grips with the main points, and remember to leave your queries below the video – I’ll answer them there.

In this session, we deal with the next stage of the weekend process: picking out bets which meet one or more criteria, based on the analysis we’ve done so far.  If you spend time filtering Excel to find ‘value’, this session could be for you.  We’ll try to get it done at the click of a button with Excel VBA.

We attempt to programme VBA to ‘pick out’ candidate bets according to one or more criteria which form a ‘system’.  A common criteria is to compare your odds with real-world odds to find ‘value’, and to select bets above a value threshold.  How can we do this with VBA?  We set things up in a flexible way so you can input new systems and tweak existing ones.

Though the code is not simple, it’s nothing we haven’t seen before in this series.  So, it’s a great chance to consolidated previous learning.  The usual suspects make it into the new routine: a loop through systems, then another through the day’s matches, two conditional statements to check criteria and variables to help along the way by storing helpful information.  As usual, we ‘step through’ the code in the VBA editor and call on Msgbox to help us understand what’s going on.

We make good progress with this stage of the process and should complete it in the next session.  But, don’t wait for us – see if you can complete it yourself in the meantime!

Topics and Techniques

The next step in automating our weekend process is to transfer information about selected bets into a ‘database’ for record keeping and analysis.  There are no new VBA techniques in this video; everything we’ve covered before in this series, and certainly elsewhere in the Excel (VBA) for Football Traders community.  Does this mean you can skip this session?  No!  The techniques are high level and require practice.  Excel VBA is a skill so the more you practise, the better you get.  So, let’s get started …

First, we have to find the next row on the Selections sheet – a task we’ve seen before in this series.  So, stop the video and try to do it yourself before viewing Chris’s solution.  Note how Chris separates out this routine to allow it to be referenced from other macros, an example of ‘modulation’. 

With the destination for the data established, we can begin programming the data transfer.  We cover three approaches in the video: first, a single cell data transfer; then, transferring data from - and to - a range of cells.  Finally, a more dynamic transfer that selects an origin column depending on the type of bet – home, draw or away.  Cool!

The video concludes with the usual testing and discussion of where the series goes next.  It would be useful to integrate a mechanism to analyse the database of bets (and results) as it builds up – a kind of ‘Dashboard’ of progress to date.  What do you think?  With only a few videos left in this series, make sure you share your ideas with Chris.

Topics And Techniques