Excel (VBA) For Football Traders - Member Question Videos

£200

Excel (VBA) For Football Traders - Member Question Videos

#1 - Ian D - How To Work With Scores In Excel

Scores (ie. how many goals the home and away teams score in a game) are fundamental to any trading strategy. Yet, working with scores in Excel can be infuriating! How many times have you tried to input a score, only to see Excel 'auto-format' it as a date? In this video, we look at some practical ways to deal with football scores and get the data displaying in the desired way. Thanks to Ian D for sending in this query.

Topics And Techniques

IF Formula
FIND Formula
REPLACE Formula
LEFT Formula
LEN Formula
TRIM Formula
Discussion Of Data Cleansing Issues In Excel

Member Video #1.mp4

Value Database Simple START.xlsx

Value Database Simple END.xlsx

#2 - Various - Dynamic Data Ranges Using INDIRECT

Selecting a range of data dynamically in Excel is just cool! It allows us instantly to update data analyses at the click of button by instructing Excel to look at a particular part of a dataset. Dynamic data selection mechanisms are at the heart of all the Excel dashboards I build.

For the football trader, dynamic data selection in Excel allows you to answer questions such as 'what was performance of this system in this particular month?' without having to dip into the data yourself with a technique such as filtering.

In this video, I show how I use the INDIRECT formula to get Excel working with data dynamically. I walk you through the whole process, including the other formulae required to get this powerful mechanism working.

Tools and Techniques

TEXT Formula
MATCH Formula
COUNTIF Formula
& to concatenate
INDIRECT formula

Member Video #2.mp4

Dynamic Ranges Data File START.xlsm

Dynamic Ranges Data File END.xlsm

Dashboard Download File Simplified.xlsm

#3 - Jorgen B - How To Collate Data Into One File

In this special video, recorded live with our member Jorgen, we look at how to collate data into a single file. Football data can come from multiple different files / sources, so this could be a real time-saver for you! With VBA, we can get it done (almost!) at the click of a button.

The task does not involve reams and reams of code, but the instructions we use certainly pack a punch! First, we look at how to open a file using Excel VBA, with the name of the file inputted in a cell in a worksheet. Then, we deal with the tricky issue of selecting a data range to work with; since the amount of data in import files will change from week to week (or day to day,) we must set the routine up in a 'dynamic' way so that it works with any amount of data.

We will look to complete this task in the next special video with Jorgen.

Tools and Techniques:

Workbook.Open
String Variables
.End(xldown)
Range(Range,Range)
Dot Cells Technique

Member Video #3

#4 - Various - Dynamic Data Ranges Using OFFSET

Dynamic ranges in Excel are just cool! They allow us to highlight parts of datasets; for example - the performance of particular months of a football betting system across a whole season. Putting them together, however, is challenging. Two weeks ago, we looked at using the INDIRECT formula to do this – did you manage to get that working? This week, we’ll do a similar thing using OFFSET, and go a step further by charting the data to create a dynamic visualisation. There’s lots to do so let’s get started!

Do you need to know both OFFSET and INDIRECT? It’s not absolutely necessary, but it’s helpful to have both in your toolkit. I love the versatility of OFFSET, and think it is important to understand the concept because it is so important in Excel VBA. In the video, I explain step-by-step how to apply this powerful formula, including its last two components – you’ve probably never touched those before.

This is certainly the trickiest formula-building task we’ve undertaken to date on Excel For Football Traders. It’s a real challenge, even for the experienced developer! Kudos if you managed to get it working. Don’t worry if you did not; simply understanding the concept of how OFFSET works together with COUNTIF and MATCH to get the dynamic range is a good outcome for most. Come back to the formula once your formula building skills have improved further.

As always, put your comments below this video and I will get back to you.

Tools And Techniques

=OFFSET
=COUNTIF
=MATCH
Using The Name Manager
Chart Creation
Editing Chart Series
Changing Chart Type

Member Video #4.mp4

Dynamic Ranges With Offset START.xlsm

Dynamic Ranges With Offset END.xlsm

#5 - Jorgen - How To Collate Data Into A Single File 2

In the second video with our member Jorgen, we try to complete the task of transferring data from an 'import' file to a 'collation' or ongoing file. If you're downloading data weekly or even daily for your football trading, you will know this kind of process well. Can we automate it using Excel VBA?

To do so, we touch on a range of advanced VBA techniques. Though the routine uses just a few lines of code, each is complex and powerful and requires foundational understanding. So, don't be overwhelmed and don't try to understand everything if you are a beginner - perhaps focus on just a single concept such as Range(Range,Range) or the .Cells technique.

The session also illustrates the importance of regularly backing up / saving your file! Chris commits a schoolboy error by failing to do this, and loses the completed file. There was only one option ... to do it all again! Which means you get to see the routine built twice in the VBA editor. Practice makes perfect ...

Tools And Techniques

.Cells Technique
.Count Method
.End(xlup)
Find The Last Row With VBA
Range(Range,Range)
Advanced Position Control
Copy / Paste In VBA vs. Direct Transfer
With … End With

Member Video #5.mp4

#6 - Neil - Return Row And Column Headers For Maximum Value In A Table

First, thank you to our community member Neil for sending in this question.

If you have a tabulated analysis (for example, a heat map,) you might wish to find the highest value in the table. In other words, the most profitable system! If your table is small, this might be simple enough to do without a special mechanism.

But, if the table has lots of rows and columns, you might want something to tell you quickly what the corresponding row and column headers are. For example, the most profitable odds bracket. So, how can you do it?

It’s a great example of the power of Excel modelling. There’s no single Excel formula that can do this easily. But, combined together, Excel modelling formulae get the job done every time! Such is the power of simple Excel formulae working together …

I argue that problems in Excel are often more conceptual than technical. In other words, formula-building can be done once somebody explains it to you, or after a few ‘trial-and-error’ attempts. The actual difficulty is understanding what formulae are needed, and why. Some clear conceptual thinking is required first.

In the video, I break the task down into 5 steps. Each of the 5 steps is fairly straightforward and achievable with a single Excel formula. The steps provide a blueprint for working through the task. Then, it’s just a case of putting the formulae in! But, the technical stuff is so much easier once the concept is clear – make sure you download the Excel file and work along with me.

Tools And Techniques

- Planning An Excel Model

- MAX

- COUNTIF

- MATCH

- OFFSET

- ADDRESS

- Conditional Formatting

Member Video #6.mp4

Member Question #6 v2.xlsm

#7a - Alan - Working Out Long Formulae And Finding Alternatives

Thanks to community member Alan for sending in this question. Alan’s file contains multiple long Excel formula that are difficult to manage – can we help him? The purpose of this mini-series is first to explore the pros and cons of long formulae, then to examine alternatives. Yes, one of the alternatives is a VBA (or at least involves VBA) – but that doesn’t mean VBA fixes everything in Excel. It certainly doesn’t, as I explain in the first video. It’s a case of understanding the options, appreciating the pros and cons of each, and selecting the one that suits you best.

In the first video, I attempt to understand the long formula, with no prior preparation! The idea is to show you how I work through long Excel formulae, a process which always seems to be stressful. Having a clear approach in mind that’s steady and systematic is key …

With the formulae (largely) understood, I offer a different approach that fulfils most of the old formula’s function, but using simpler syntax. Can you guess what formula I select? It’s a good option for football traders working with Excel, but it’s not actually my preferred option, we’ll get to that in the next video.

TOPICS AND TECHNIQUES

Discussion: Is VBA the answer to all Excel problems?
What’s wrong with long formulae in Excel?
How to understand a long formula in Excel
SUMPRODUCT
OFFSET
COLUMN
ROW
SUBTOTAL
Discussion: Pros and cons of SUBTOTAL
Switching off calculation in Excel
SUMIFS
Managing datasets that change in size
Excel’s table notation (vs. dynamic ranges)
How to test Excel formulae

Member Video #7a.mp4

Sample File Vid 1 End.xlsm

Sample File Vid 1 Start.xlsm

#7b - Alan - Using DSUM To Substitute A Long Formula

In the second video in this mini-series, I begin to demonstrate the approach I would implement in the real world to get the analysis Alan needs. We’ve seen DSUM several times before on Excel VBA for football traders - you may know it as one of my Excel ‘secret weapons’. One thing is for sure, it’s a shorter formula than the current solution! It does require some setup, however – I take you through it step-by-step in this video. How do you think it compares to SUMPRODUCT?

With DSUM working and tested against several scenarios for accuracy, it’s time to start thinking about VBA. In the video, I argue a key to a successful macro (that’s often overlooked) is what I call ‘planning and conceptualisation’. Are you planning your VBA routines? The first step is literally to ‘talk through’ what you intend to do – what I call ‘conceptualisation’. I do this in the video, generating a clear idea in my mind of the steps we’ll need to go through to automate this manual process. Can you plan the macro in more detail before the next video? Only then are you really in a position to jump into the VBA editor.

TOPICS AND TECHNIQUES

When should you use Excel VBA?
DSUM
Concepts in DSUM: database, field, criteria
The importance of overall file structure and worksheet layout
How to quickly find a column number
Using table notation in Excel formulae
Why your spreadsheet is like your house
How to de-stress Excel development
How to plan an Excel VBA macro

Member Video #7b.mp4

Sample File Vid 2 Start.xlsm

Sample File Vid 2 End.xlsm

#7c - Alan - Writing 'Pseudo Code' For A VBA Macro

I’ll be straight with you: I thought we’d get this mini-series finished with this third video! In the end, however, I thought it better to split the last part into two: the first part is about writing what’s called ‘pseudo code’; the second about implementing and testing the macro. Both stages are critical in Excel VBA development.

Pseudo code is a set of instructions that describes, step-by-step, the function of a computer programme written in English (or your native language.) The purpose of pseudo code is to make it crystal clear in your mind what the macro should do, by divorcing the conceptual challenge of how the code works from the technical challenge of expressing those ideas programmatically. In this video, we’re in the conceptual world. Think of it as like drawing up a blueprint for your macro. You wouldn’t try to build a house with a set of plans – let’s apply that same ‘design’ mindset to your Excel development.

Topics And Techniques

Planning a VBA macro
Pseudo code
The Name Manager
Named range naming conventions
COLUMN
Automatic and manual calculation modes

Member Video #7c

Sample File Vid 3 Start.xlsm

Sample File Vid 3 End.xlsm

#7d - Alan - Implementing, Testing And Improving A VBA Macro

With a blueprint for the macro created in the previous video, now we can approach the coding with confidence. I find VBA coding much easier when working from pseudo code – it’s simple line-by-line translation. And that’s a good thing, since low stress levels mean long and productive Excel development sessions!

In the video, I build the macro step-by-step, combining powerful VBA constructs such as loops and position control. Using the pseudo code as a guide, the process is fairly smooth, and ‘stepping through’ the routine shows it’s doing what’s required. There’s a sting in the tail, however! Running the routine in full exposes some major inefficiencies – the macro takes several minutes to complete. The video concludes with implementation of changes to improve the execution speed, and the efficiency of the file as a whole. It reminds us of the need to keep our Excel files ‘lean and mean’!

So, the VBA-based solution is complete. The purpose of this mini-series was to explore alternatives to long formulae in Excel. The solution is not perfect but, for me, is preferable to the formula-based approach for a number of reasons. But, what do you think? Let me know in the comments below the video, I’ll get back to you there.

Topics And Techniques

Variable declaration
Improving macro execution speed
With … End With
Code indentation
For … Each loop
Msgbox to test a loop
Combining a worksheet formula (DSUM) with VBA code
.Cells referencing technique
How complicated formulae impact the efficiency of a spreadsheet

Member Video #7d

Sample File Vid 4 Start.xlsm

Sample File Vid 4 End.xlsm

#8 - Chris - Creating A File Summary With INDIRECT

This question came in from me!

I love everything Excel has to offer: formulae, facilities such as Power Query and, of course, the VBA programming language. But, what am I keen on above all of this? Effective file structure. Yes, how you set up your file - in terms of the number of sheets and the role of each – really does change everything. The truth is you might not need VBA or other advanced tools if the file is put together in a logical and coherent way.

I felt the benefit of clear and consistent file structure when making this video. What’s the context here? Well, it’s something close to my heart – the Excel (VBA) for Football Traders content navigator. Don’t know about the content navigator? Where have you been?! It collates all the information about the community in one place and is an essential for any community member. I’ve uploaded the file alongside the video and the latest version is available here. Make sure you check it out!

What do I mean by ‘consistent’ in this case? Well, it’s simple. The file comprises one sheet per video series (‘Heatmaps’ is an example of a video series). And, the layout of each sheet is exactly the same. Right down to the number of rows and columns above and to the left of the main data on each sheet. Moreover, the sheets are formatted in the same way – that’s only an aesthetic thing, but does create a sense of order!

The file structure and sheet layout would help me with one specific task: creating a summary sheet for the whole file. I don’t want community members to have to click through all the sheets to understand what’s on offer. It would be great for the main information to be summarised in one place. So, a ‘summary’ sheet is needed: but how to do it in a way what isn’t too onerous / time-consuming?

Key to the mechanism is what some would consider a controversial choice: Excel’s INDIRECT formula. Yes, it’s a ‘volatile’ formula: so, too many INDIRECT formulae can slow your spreadsheet down. But, used judiciously, it delivers a unique benefit, as I demonstrate in the video, and it won’t necessarily slow things down. And, it’s all possible because of a disciplined approach to file structure and individual sheet layout.

I hope you’ll enjoy this video which demonstrates what really matters in spreadsheet design and development.

Topics And Techniques

Planning a spreadsheet layout
How to quickly format a spreadsheet
Keyboard shortcuts for formatting a spreadsheet
Tips for column widths
The unique contribution of INDIRECT
The concept of ‘writing a reference in a cell’
VBA to list all sheet names in the file
Excel UPPER formula with INDIRECT
Excel COUNTA formula with INDIRECT
Excel SUM formula with INDIRECT
How to quickly format a date or time cell

Member Video #8.mp4

EFT Content Navigator 220223.xlsm