3 Excel Data Analysis Techniques You Should Be Using
Are you a football trader working with Excel? Like many, perhaps you’re struggling to get Excel to work with your football data? Perhaps you’re tired of spending hours clicking through filters or fixing formulae, for example? I imagine you would you prefer to spend time perfecting your systems than doing onerous manual work in Excel.
I’m Chris Mortimer, founder of Tiger Spreadsheet Solutions. Over the past five years, I’ve created dozens of Excel-based tools for the best football traders in the business. I’ve learned what football traders need to know in Excel (as well as what might be wasting their time).
I’ve seen that so many people are missing out on Excel’s awesome power. As we will learn in this video, implementing just a few of Excel’s in-built tools can have a dramatic effect on the way you work with Excel. The result? Less frustration, a clear head to focus on trading, and better systems from analyses you never thought possible.
Most football traders using Excel are familiar with filters. In fact, all football traders I have worked with seem to spend a lot of time filtering! A filter ‘filters out’ some of the data, allowing us to focus only on data conforming to particular criteria. Combined with formulae such as SUBTOTAL, filtering allows us to test different systems and, hopefully, to find a few profitable ones … eventually!
So what’s the problem? Filters are hard work! Not only do you have to select the whole dataset to apply a filter, many mouse clicks are required to filter out data. Try filtering for a particular team name, for example. So, what if we could get this kind of analysis, without applying a filter at all?
This is where DSUM comes into play. It is so good, I use it in almost all of the Excel-based football analysis tools I create. Give it a try!
This powerful Excel formula allows us to type criteria into CELLS and to view results, with no mouse or button clicks. Yes, DSUM fulfils the same role as the filter / SUBTOTAL combination, but by applying an Excel formula – so you can say goodbye to the tricky filter interface! Also look out for DSUM’s buddies DCOUNT and DAVERAGE. You can see me using DSUM in this video and here is a complete tutorial:
There is no doubt that exploring a data set and hunting for profitable systems can be interesting, but it is usually a very ‘manual’ process. There is no substitute for checking different criteria by changing a value in a cell, or applying a filter, then noting down the result … or is there?
What if I told you that Excel can do exactly that kind of manual work for you? Yes, Excel can check any number of combinations of inputs for us, noting down the output for each combination. Profit / Loss when West Ham are longer than 2.7 at home, for example? Done, and we’ll check another 100 odds for you too, in an instant. Even better, no complicated VBA code is involved. What technique can do this?
Many things in Excel are not helpfully named and a Data Table is one of them. It is so much more interesting than the name suggests. It does more than display ‘data’; it actually displays outputs from a model and, as such, is a powerful optimization tool. It pairs beautifully with data analysis formulae such as DSUM, too.
See me demonstrate the data table in the video, or check out a full tutorial here:
Basic numerical data analysis is an important skill for all professionals these days, but particularly for football traders who are surrounded by numerical data. How would you go about analysing a set of odds, for example? Say all home win odds in a particular league and a particular season?
Perhaps you would start by looking through the data, sorting it and doing some filtering. Such techniques are certainly not unhelpful, but only really scratch the surface in terms of serious data analysis. So, what might help?
You really need to visualise a data set. This means creating a chart, or a graph. Perhaps you’ve tried to do this with a big set of numbers, and created something that just isn’t helpful. What we need is a simple preliminary analysis that gets the data into shape. Then, Excel can do the visualisation.
I’m very happy to introduce you to the FREQUENCY formula! This powerful formula uses the concept of ‘bins’ to analysis continuous numerical data such as football odds. By defining bins using upper bounds (eg. 1.75-2), FREQUENCY tells us how many values fall into each category. This analysis can be easily visualized and provides a quick picture of the data set.
It is the foundation of all of my numerical data analyses and I know you are going to love it! Here is a full tutorial for the FREQUENCY formula:
If you are serious about data analysis, you are going to love Excel VBA For Football Traders. This unique community allows you to access video materials, Excel files, expert advice from Chris and a group of like-minded individuals. Find out more below and access the first 5 sessions for FREE.
