Welcome to Excel Heatmap Analysis For Football Traders!  The course will teach you how to create heatmap type analyses for football data.  These are commonly associated with odds brackets, but you can analyse many different types of data with heatmap analyses, as we will see.  So, let's get started!

The bad news is that we cannot simply click our fingers and produce a heatmap analysis.  A set of foundational skills, techniques and knowledge is necessary.  The good news is Chris takes you through this learning, step-by-step, in this series.  Make sure you download the Excel files and work along with Chris.

In the first session, we explore how to undertake a basic data analysis of continuous numerical data.  Simply put, how do we understand a set of numbers?  It's an absolutely fundamental skill for any data analyst.  We start with basics then build to more sophisticated techniques.  Watch out for the powerful FREQUENCY formula which facilitates a cool visualisation of the data - it's one of Chris' Excel secret weapons!

Tools And Techniques

- COUNTA
- MAX / MIN
- AVERAGE / MEDIAN
- STDEV
- FREQUENCY
- COLUMN CHART

In the second session on heatmap analysis, we look at one way to create a heatmap.  This method, using a facility in Excel called a 'data table', is not the slickest - but it is powerful, and certainly gets the job done.

We start this session with a minor file re-structure.  This ensures the role of each sheet is clear - and we know, in the long run, that helps keep us sane as Excel programmers!  With that done, we are ready to begin the analysis.

Like many of the more powerful facilities in Excel (FREQUENCY being another,) some preparation is required before implementation.  A data table in particular requires a model in order to work.  This is because the data table would be better described as an 'output table' - it actually displays multiple outputs from a model.

With the model checked and validated, we setup the odds brackets in a row and a column which gives us the basic data table structure.  Only then can we actually create the data table through Excel's 'What-If Analysis' area.  As always, take your time, and don't be discouraged if it doesn't work first time - it probably won't.

Once the table is in place we can harness conditional formatting to create the 'heatmap' formatting overlay.  and there it is - a thing of beauty and a powerful addition to your analytical toolkit.

TOOLS AND TECHNIQUES

- Data Table
- SUMIFS
- Conditional Formatting

We have felt the power of a data table, but perhaps this new capability poses as many questions as it answers.  Would it be possible to do a 3-criteria analysis, for example?  A data table permits only two, of course.  So, what are the options?

Excel VBA opens up a new world of possibilities!  The good news is that any analysis you can conceptualise in your own head is possible with VBA; the bad news is that it takes a lot of time to learn, and it can be infuriating.  Be prepared for a rollercoaster ride!

In this course, we take a practical approach to learning Excel VBA.  After some preparation for using code such as making the developer tab visible, we jump straight into the deep end in this video!

We use the macro recorder facility to record some code, then we review it in the VBA editor.  Then, we tweak the code, attach it to a button, and run it from the spreadsheet.  There, you go - an organically coded macro, you're now a computer programmer!

Topics And Techniques:

- Getting ready for Excel VBA
- Recording code with the Macro recorder
- Creating your own macro in the VBA editor
- Creating a button and assigning a macro

In the previous session, we used Excel VBA to automate the creation of a data table.  Though this saves time, the result is sub-optimal for a few reasons.  First, the code creates a data table linked to the model output cells, which means that the data table updates with new model inputs.  This could confuse, so how could we avoid it?  Also, we know that clearing data tables can be problematic, so could we automate the process of clearing an existing table too?  Finally, could we get VBA to add the cool ‘heat map’ style formatting that is the icing on the cake?

In this video, we take on the above challenges with our usual steady and systematic approach.  Chris shows how to use the macro recorder to record code, tweak and move it around in the VBA editor, and finally to get it working for us.  This process is fundamental in practical VBA coding.  So, make sure you practice all three applications in this video!  You will use it many times in the future.  The result is a clean and well-presented output that avoids the file inefficiencies that data tables can create.

To think about before the next session: how might we create multiple heat maps?  Could we work towards, for example, creating a heat map for each team on a single sheet, all at the click of a button?  What coding concepts would be required to do this; or, what coding techniques are you currently lacking?  See you in the next session!

Tools And Techniques
- Data Table
- Macro Recorder
- Tweak Code In VBA Editor
- Move Code Around VBA Editor
- Paste Special Values
- Conditional Formatting

So, we've managed to automate the process of creating a heat map, including formatting and conversion to values.  Cool!  This gives us some kind of 'click-of-a-button' functionality.  At the same time, we are feeling the limitations of our implementation: for example, the heat map allows only 2 variables to be explored.  What if we wanted to create a heat map for each team in the league, for example?  To do so would be time-intensive, even with our VBA-based solution.

To move forward requires a deeper dive into VBA coding.  We start this process this week, with the aim of generating multiple (20?) heat maps at the click of a button.  Be aware, however, that learning these techniques takes time - you may not have a new analysis at the end of each session!  I hope you're in for the long haul, I guarantee it will be worth it in the end ...

With that in mind, we set about exploring what is required.  We first look at calling multiple macros from a single button.  This technique permits a 'modular' (like IKEA furniture!) approach that can be so powerful in VBA coding.  Watch along with the video and try it out for yourself.

Then we look at entering a value into a cell to determine where the heat map appears in the spreadsheet.  This involves some mind-bending VBA code - make sure you create it and test it yourself.  And don't forget the homework challenge at the end of the session!

Topics And Techniques

- Assign A Macro To A Button
- Call One Macro From Another
- Range(Range(Reference).Value).Value Technique
- Planning A VBA Macro
- Stepping Through VBA Code

At the start of this session, we take some time away from the heat map sheet to get acquainted with two powerful VBA techniques - offset and range(range,range).  These two drive so many Excel implementations and are a must for any serious VBA analyst.

Why are they so important?  Coding in Excel is really about one thing: control of position.  Excel gives us an infrastructure (or a canvas?) to work with, comprising files, sheets and cells.  How well we navigate this infrastructure is the main determinant of our impact as VBA programmers.

We start with Offset.  As with many Excel tools, it is not well-named!  Offset can be understood as 'to move away from'.  We choose an anchor point and then 'move away' from there by a number of rows, then a number of columns.  It sounds pretty ordinary but, crucially, it replaces static worksheet references such as Range("A1:G4").  This makes all the magic happen later.

Range(range, range) allows selection of multiple cells.  Using the technique, we determine a start cell and an end cell, then Excel selects all the cells in-between.  We start with some 'simple' examples - make sure you build on these and do your own experimentation.  Finally, we bring the two techniques together to achieve powerful position control.

How this supports the heat map analysis is not yet absolutely clear - watch this space, it will be worth it!  In the mean time, make sure you develop confidence with these two powerful VBA techniques.

Topics And Techniques

- Offset
- Range(Range,Range)

In this week’s session, we explore the power of computer programming’s (and Excel VBA’s) most powerful technique – the loop.  Loops are useful when we are repeating something: a task, a process, running a model.  What specifically are we looking to repeat?  The creation of heatmaps, one for each team in the league.  It will take a few sessions, but loops are going to make this easy, with just a few lines of code.

Our first challenge is to try to create multiple heatmaps in a more ‘manual’ way.  What’s a good way to get started with a VBA job, and to give you some code to work with?  Yes, the trusty macro recorder!  First, therefore, we record the code for creating row headers for 3 tables.

This gives us something to work with, which is better than a blank canvas.  We can now set about improving the routine – so what needs to be improved?  Two things to avoid in VBA code are ‘hard-coded’, static cell references, and repeated structures.  We use offset to clear up the first problem, and a loop to clear up the second.  The loop being a new concept, we begin with a small-scale example in a ‘safe space’.

As with many powerful techniques in Excel / VBA, setting up a loop takes some time and care.  We first must declare a variable (just a place in the computer’s memory to store some information relevant to the programming,) to control the loop.  Then, we build the loop and tell Excel how many times to work through it using For Counter = 1 to 3.  This allows us to repeat the process of creating the data table headers.  Congratulations – you just harnessed computer programming’s most powerful concept!

Yes, lots remains to be done with this task.  However, having learned position control (.offset), loops, and about the VBA editor more generally, we are now equipped with the basic tools to get it done.  Can you try to do it yourself before next week’s video?  I will see you then!

Tools and Techniques:

-          Use the Macro Recorder

-          Tweaking recorded VBA code

-          Offset method

-          Variable declaration

-          Integer variable

-          For Next Loop

-          Message Box

In the previous session, we just began to feel the power of loops in Excel VBA.  Though the mechanism we created is powerful, it’s not yet doing anything useful for us.  So, in this session, let’s harness the loop to generate some heatmap-type analysis.

To do so, we’ll have to replicate the basic heatmap layout in each of the heatmaps we generate.  This is fairly easily done by creating a named range for a layout ‘storage’ area at the top of the sheet.  Perhaps we’ll move this to another sheet later – using a named a range allows us to do this easily without having to change the code in the VBA editor.

With the basic layout replicated each time we go through the loop, we look at actually creating the calculations!  This is the point, of course.  We do this by integrating the instruction to create a data table.  Carefully ‘stepping through’ the VBA code allows us to understand it is working, and checking the calculations against the DSUM formula allows us to build confidence in the outputs.  Remember, testing at every step is key, and it’s best to check each result at least two different ways.

This gives us the same data table duplicated many times, which still isn’t very useful!  Let’s look at creating analyses for different teams at the click of a button, one data table for each team.  This sounds cool, but requires the integration of a simple but powerful mechanism in the VBA code, based around the loop variable and our old favourite, the offset method.

All that remains to do is to ensure each data table is converted to values when it is created.  Chris manages to do this at the end of the session.  Now, can you improve / scale up this routine to get exactly the analysis you need?

Tools And Techniques

For Next Loop

Integer Variable

Offset Method

Recycling Code

Organic Code

PasteSpecial Values In VBA

In this session, we move towards completing the first part of the heatmaps task.  We do this by creating heatmaps for each of the 20 teams in the Premier League.  This is reasonably straightforward - the hard work really is creating the mechanism, which we've already done!  It's just a case of changing the loop parameters.  Note how easy it is to 'scale up' a loop once the basic mechanism is in place.

We also look at how to get 'unique values' from a set of data.  Excel's UNIQUE formula, a relatively new offering that 'spills' into the rows below, really helps us here.  As always, however, there are multiple options for getting this done, including the traditional 'remove duplicates' facility, available in the Data tab on all versions of Excel.  This helps us to get a list of all teams in the league.

To complete the task, we 'tidy up' the formatting, discuss the importance of the conditional formatting rules manager, and switch off screen updating in the VBA editor to dramatically improve the speed of code execution.

So, have you managed to create your own heat maps?  Don't stop here!  You now have the tools to get the bespoke analysis you need to support your trading activities.  We will return to heat maps in the future to create even more powerful analyses.

Tools And Techniques:

- UNIQUE
- Remove Duplicates Facility
- Scale Up A Loop
- .Cells Referencing Technique
- Conditional Formatting Rules Manager
- Application.ScreenUpdating