In the first video in the series, we deal with the basic concept of ‘dutching’ (at least as far as I understand it!) and briefly discuss different forms of ‘dutching’.  Based on a request from a community member, this series uses a ‘real-world’ prototype model that aims to evaluate the profit of dutching strategies over a period of time.  With the goal (fairly) clear, can we begin to put the Excel model together for our viewer?

The first step is to build simple ‘dutching model’ for a target profit.  As I explain in the video, I could not find a simple formula-driven way to do these calculations.  So, I demonstrate how to set up a basic model with formulae, then use a data table to identify different options and their profitability.  Along the way, we re-visit basic Excel modelling concepts (eg. input, process, output, dependents, precedents), and implement some non-trivial formulae such as =ABS to get the required functionality. We know how important validation is in Excel model building and that we should test at every step; so, we compare the results of the basic model to an online dutching calculator to check we’re on the right track.

TOPICS AND TECHNIQUES

In video 1, we implemented a data table to give us profit and loss for different stakes on two bets.  This is reasonably useful, and certainly saves time as compared to testing strategies in the online calculation.  We can do better, however.  Rather than ‘manually’ looking through the data table for the best result, could we get Excel to extract it for us?  In the first part of the video, we explore how to use Excel formulae to do this.

This shows us that the solutions the data table is providing are sub-optimal and whether they work for you depends on the level of precision you need.  This level of precision would probably work for ‘high level’ strategic work, but might not be suitable for fine-tuning systems.  With this in mind, It’s useful to know how to create higher solution quality by ‘scaling up’ a data table.  We have to delete it first, however!  There’s a trade-off here: a better solution quality means a slower file – so it’s important to assess what balance suits you best.

Having achieved a better-quality solution, the next step is to convert the basic model we’ve built to a ‘single row’ or sequential model as was originally required by our community member.  It’s worth taking the time to get layout (eg. columns on a sheet) and overall structure (ie. number of sheets in a file and role of each) right because this makes everything easier, including subsequent formulae and (if required) VBA code.

TOPICS AND TECHNIQUES

In video 3, we work to optimize the layout of the ‘row-by-row’ or sequential model that we started in the previous video.  As you will see, it’s difficult to ‘lock down’ the perfect layout, since new modelling requirements and ideas emerge through the development process.  I’m pretty sure we’ll adjust this layout (again!) in the next video!  However, that’s not an excuse for not at least attempting to plan layout (and overall workbook structure), since these are ‘big levers’ that potentially save us a huge amount of stress.

On a practical level, we implement important details such as bet commission.  This is a great example of a ‘parameter’ that might change over time (as Betfair’s terms and conditions change, for example), so it should be adjustable.  This is where the Engine sheet – and named ranges - can help us.  We also encounter a practical limitation of data tables and implement a workaround.

Finally, it’s time to implement a VBA macro to get data from the fixture row in our sequential model into the ‘simple model’ optimization area.  We look at how to access the VBA editor, plan a macro, write some code organically and finally test it to prove it works.  The powerful ‘dot cells’ referencing technique that we use is a must if you’re looking to build powerful macros.  

Have we reached for VBA too early?  Ideally, the layout would be set in stone before implementing any code, since any layout changes must be reflected in the VBA.  Let’s find out in the next video …

TOPICS AND TECHNIQUES

This video deals with the tricky problem of identifying odds for eligible bets.  The original briefing states one or two bets may be ‘dutched’ for any one fixture; for the purposes of the model build, let’s first simplify by assuming only two bets can be considered eligible.  Such assumptions allow us to progress a model build without getting too bogged down, and we can always ‘design them out’ later.  The tricky problem is, ‘of the 3 possible bets, which 2 are eligible, and what are the odds for each?’

I found this very difficult to model, simply, without too many columns and formulae.  To make things easier in the video, I recommend using a ‘sandbox’ – an approximation of the problem in a separate (empty) Excel file.  This allows us to chip away at the problem away from the noise and distractions of the full model.  Along the way, I verbalise and write out the logic to help make things clearer.  In the end, a combination of formulae gets the job done, but perhaps you can find a more concise solution?

The next step is to export the ‘sandbox’ solution from the test area to the actual model.  At the same time, we build the remaining formulae in the row-by-row or sequential model that’s gradually taking shape.  It’s painstaking work but, as always, frequent testing and a systematic approach help us along the way.  Can we finally complete the row-by-row model in the next video?

TOPICS AND TECHNIQUES

There’s more meticulous Excel modelling work in this session.  But, that’s the nature of Excel development: there’s a lot of work up front; hopefully, the prospect of hours of time saved in the future keeps you motivated.  So, let’s get to it!

The goal this session is (finally) to complete our ‘row-by-row’ model.  The ‘bet 1’ formulae seem to be working well, so in this video we implement the bet 2 formulae.  This should be straightforward since most of the formulae are the same and, crucially, the layout of the worksheet is consistent from one bet to the next.  It’s not a case of simple ‘copy / paste’, however; first, we should consider the cell references in the formulae.  Which is most suitable: relative, partial absolute, or full absolute?  These confused me for years!  Keep your eye on the dollar signs …

Speaking of currency, it’s about time we got the model working so we can earn some.  But, updates are needed in the VBA to reflect our revised model structure.  Yes, this is not ideal (in fact, I call it a ‘cardinal sin’ in the video), but it’s natural for new requirements to emerge during a project.  Let’s look at how to quickly and efficiently update VBA routine(s) when we absolutely must.

With the modelling done, most of the heavy lifting should now be complete in this project.  Next, can we exploit this model with Excel VBA?  See you in the next video.

TOPICS AND TECHNIQUES

With the arduous work of model-building done, it’s time to get our ‘tech bro’ hoodies on and write some code!  And not just any code because, first, we introduce the most powerful construct in Excel VBA (and in computer coding more generally) – that of a ‘loop’.  A For-Next loop in Excel VBA allows us to repeat a set of instructions any number of times.  So, it’s perfect for processing fixtures in our ‘row-by-row’ setup.  Let’s a give it a try … and prepare to be impressed if you’ve never seen a loop in action before.

The first test, predictably, reveals problems.  Where VBA tries to process ‘No Bet’ rows, an error is returned.  How can we get VBA to ‘skip’ these rows?  This is easily done with a conditional statement (comparable to the worksheet IF formula), but building the code into an already busy VBA routine can be tricky.  Good coding habits such as indentation, annotation and informative variable names make the job easier.  Look out for Chris’ Find / Replace trick in the VBA Editor that could save you huge amounts of time …

Finally, we look at improving model efficiency.  If runtime feels slow for 10 or so fixtures, it will certainly be problematic across a whole season.  So, what can be done to speed up the code?  We look at how to toggle calculation on and off in the VBA Editor, which comes with a health warning as it can lead to very unnatural behaviour in Excel.  Applied carefully, however, it can dramatically improve macro efficiency, as we witness in the video.  It feels like things are coming together …

TOPICS AND TECHNIQUES

The main topic in video 7 is upgrading a static model to a dynamic one.  What do these important terms in computer programming actually mean?  Well, let’s look at the model from the end of the previous video.  Though we could ‘copy down’ formulae and clear data from the stake cells, for example, generally it would require some manual work to adapt the model to different numbers of fixtures.  This is why the model is primarily ‘static’ – it doesn’t respond flexibly to changing circumstances.  A dynamic model, by contrast, would support any number of fixtures with very little manual work.  So, how can we create dynamic quality?

Dynamic quality means one thing: sophisticated – and supremely powerful – Excel VBA coding.  So, get ready for an intense session in the VBA Editor!  It’s all about position control.  What row are we starting on?  What row are we ending on?  And how do we get the required formulae onto each row, whilst ensuring we don’t have more formulae than we need?  Look out for powerful coding concepts such as range(range, range) and .end(xlUp).  Also note how I adapt (or ‘recycle’) complex lines of code to new jobs.

With formulae being cleared out and copied down to the required range, are we done?  Not quite!  Having made significant changes to the coding, it’s time to validate our work with a ‘parallel test’ against the file from the last video.  They should give the same result – do they?  This takes longer than needed (I was tired by the end!) but eventually proves that we’ve implemented dynamic coding whilst retaining accuracy.  Great!

TOPICS AND TECHNIQUES

Now we’ve built the model and implemented some VBA to deal with a changing number of fixtures, we’re finally ready to use the model for its purpose: experimentation with different systems.  To do so, however, we’ll need some data.  In the video, I first copy in data from the Doggy Football League (DFL) to get us started.  Along the way, I use keyboard shortcuts to speed up the process and trace dependents to ensure I don’t accidentally overwrite formulae.  I also insert a button to run the macro, so we don’t have to keep jumping into the VBA Editor to set off the code.

With the data in the file, we can set about experimenting with different ideas.  It’s clear model efficiency is still an issue, however, so I implement one final idea to speed it up.  It raises the question: would the optimization mechanism (currently a data table based in the file) work better in VBA?  It’s an intriguing question.  If you’d like to see me build this mechanism in VBA, leave a comment below the video.

Some quick tests with different ideas bring this series to a close.  More videos in the series may follow (particularly if I receive requests), but it’s now up to you to try to get value from the Excel-based Dutching model.  Thank you for watching the series and, as I mentioned, do leave your comments below the video.

TOPICS AND TECHNIQUES