Welcome to Excel VBA For Football Traders!  In this introductory video, I explain how to approach the course, and introduce the all-important learning journal.  Take the time to familiarise yourself with it, and make your you use it to maximise your learning.

In the first video, we get to grips with a typical dataset that football traders like you might use. Keyboard shortcuts will get you using Excel like a pro and we learn and apply our first ones  - make sure you write them down! We take a simple system - always backing the home team - and use an IF formula to calculate winnings (and losses!) We also deal with how to efficiently copy down a formula and cover relative and absolute references.  Yes, you'll learn how to use those dollar signs! $$$

Topics And Techniques

In video 2, we improve our analysis by incorporating commission, and give the option to include more 'criteria', in this case by focusing on a single team.  You may have seen the SUBTOTAL / filtering method before and though it certainly works, we will explore slicker ways to do the same thing later in the course.  We also discuss the issue of capacity in a spreadsheet: should you simple include whole columns in formula, for example? We discuss the pros and cons.

Topics and Techniques

Using SUBTOTAL and filtering involves a lot of clicking - so what are some of the alternatives?  In the third video in the course, we begin to explore Excel's powerful (and often overlooked) data analysis formulae.  Using SUMIF and COUNTIF, we can get the same result without having to manipulate the filter menus.  Cool!  And we're just getting started ... Finally, we introduce the idea of 'validating' calculations by achieving the same result two different ways.

Topics and Techniques

SUMIFS (with an 'S'!) is an extension of the SUMIF (without an 'S'!) formula and allows is to integrate multiple criteria in the analysis.  For example, we could test an idea such as 'what would returns be if we bet on Man United at home when odds are more than 1.5?'  These formulae can get long, so make sure you are steady and systematic when you put them together.  Take your time and make sure you punch the air when everything works! Oh, and don't forget to validate your calculations as we do in the video.

Topics And Techniques

Though the previous formulae certainly get the job done, putting them together can be onerous.  Is there an alternative that delivers the same power in a simpler format?  Of course there is!  The DSUM and DCOUNT formulae require some time and care to set up, but permit powerful instant analyses with just a few keyboard inputs.  Work along with Chris and take the time to set them up properly. I know you are going to love them!

Topics And Techniques

Things seem to be going well.  We have a file with data and some fairly powerful analyses.  But, if we were to continue with the current worksheet layout and file structure, we would soon run into trouble.  Excel models that remain usable over time have to conform to a number of design principles.  Let's look at those in the this video in addition to some key concepts such as input, process and output.  Then, let's start to re-structure the file on the basis of what we learn.

Topics And Techniques

In video 7, we continue the file re-structure by first giving 'informative' names to worksheets.  We also get into Excel's Name Manager which lists all of the named ranges in the file, and appreciate the benefits of proper naming conventions.  Next, we integrate the named ranges into formulae and carefully re-route the calculations so they are pointing to the right sheet.  Again, nothing particularly exciting, but essential activities for powerful Excel implementations.

Topics And Techniques

Just like Excel itself, formula building a is a skill.  Formulae that reference different sheets are a feature of a powerful Excel model; but, put them together can be a nightmare!  In this video, let's practise creating those tricky references across sheets, testing as we go to ensure accuracy.  Be slow and steady and make sure you celebrate when it all works :-)

Topics And Techniques