skip to Main Content

Using SQL Server with Football Data

There is a lot of data available on the web when it comes to football, most of it is ready to consume via various websites and apps whereas some is available in a raw format (e.g. a csv file of results / stats). One of the things you might find (as I have) is that as your football trading progresses so does your need to get more from your data, this means finding and using more detailed sources as well as learning of new tools to help manage and query it.

For many this progression will simply mean going from a stats based website or app onto using a spreadsheet to analyse games on a season-by-season basis. This move represents a big step forwards as a lot can be achieved with spreadsheets and macros however, they are not without their problems and it often becomes difficult to manage and analyse multiple spreadsheets.

football results in a spreadsheet

In fact I would even go as far as to say it is spreadsheets which are ultimately the downfall of many aspiring traders, not because they don’t help with analysis (they do) but more because they are a nightmare to work with and manage when looking at anything more than fairly basic sets of data.

Step in a More Powerful Tool

Go into any big company or organisation and chances are they will be running some very powerful, commercial grade database software to power many of their business systems. Big businesses are very data driven these days and as such the need for a powerful database management system is now a critical part of doing business. These management systems do, after all, allow for data to be efficiently collected, stored and analysed bringing out the real value of said data for the business!

sql server dbms screen image

Another massive benefit of using a database management system is that any analysis work can be programmed to be re-run at any time with the minimal of additional work needed. To illustrate this think about “one-click” end of day sales reports versus having to manually download and process multiple spreadsheets worth of data to get the same results after lots of analysis work!

So, this begs the question, since the technology to efficiently manage huge amounts of data and analyse it deeply, quickly and efficiently already exists why does hardly anyone use it in the trading world. Seriously, I have read absolutely tons of football trading content over the years and I don’t think I have heard of anyone using this technology, not even once, very strange?

SQL Server and Football Data

One of the most common commercial database management systems available today is that of Microsoft SQL Server. Not only is SQL Server extremely powerful software which can help manage virtually unlimited amounts of data but many will be surprised to learn this software is completely free to use on personal (non commercial) projects.

Despite been free the real value in using SQL server lays in two main areas, these are the effective storing of any data whilst keeping it easily accessible and the practise of querying and analysing the data with relative ease.

Storing of data

Imagine you have a spreadsheet containing one full season of stats for a single league, not to difficult to manage, right? Next, imagine you have the last 10 seasons worth of data for that league, this puts us up to 10 spreadsheets, still not a massive amount of data to manage but starting to get tricky. Next imagine you have 10 years of data to manage for 10 different leagues, we’re now up to 100 spreadsheets and things will by now become very tricky to manage.

sql server dbms select screen image

A better way to manage such data would be to use a database with each league and season forming a single table under the main database container. This approach makes managing the data easier (as its all under a single “container”) and also enables us to perform analysis across one or all of the tables simultaneously.

Querying of data

As briefly mentioned in the last section one of the biggest advantages of a database is that we can query as little or as much of our data as we like with relative ease. This means the process of analysing data which covers multiple seasons and or multiple leagues much easier than when fumbling with multiple individual spreadsheets.

This ease of querying large and potentially complex data sets also means much deeper analysis can be performed, for example, when looking for a change across all seasons and then comparing this change in other leagues.

Example of SQL used to query football data

Finally, one of the biggest advantages of querying data within a database is that the SQL used to write the query can be re-used very easily (SQL is the scripting language used to retrieve the data from the database, think of it as a database programming language). This means in practice that one we have written a SQL script which does some analysis for us we can save it and re-run it at any time in the future within a matter of seconds! To do this we simply load up the script and click run and then the work will be run for us, compare this with locating and opening multiple spreadsheets and then copying, pasting, summing and so on (you get the picture).

So, what's the catch?

Here’s the thing, getting up to speed with SQL Server, learning how to design, build and manage databases and then finally becoming proficient with SQL takes a lot of time, time most people simply don’t have! So, for me at least, this is the main reason the use of SQL Server to manage and analyse football data isn’t more commonplace. Combine this steep learning curve wit the relative complexity of SQL as a language and this is probably why the vast majority of traders and bettors simply don’t go anywhere need it.

This is a real shame as once you have mastered these skills, or at least become competent enough to start some analysis the amount of power this gives you over your data is a game changer. Imagine being able to ask pretty much any question you want of your data and getting an answer in moments, or almost instantly if you already have the necessary SQL already saved and ready for use.

And Finally, the Answer!

What if there was a way to get up and running with SQL Server in as little a time as possible? Not only getting the database software installed on your PC but also develop a working football stats database built using real football stats data (available for free) and learning to ask advanced questions of the data within. In addition to this how about having an automated tool which makes the process of updating this database with the latest stats quick and simple. Sounds good right?

Using PowerShell to porcess football data

I myself have worked in the IT industry for over 10 years and a number of those as a database administrator in various different IT roles. During this time, plus my time studying computing I have come to hone my expertise in working with data and databases. As such I have decided to combine my years of experience using databases commercially with my desire to continue developing my football trading to develop my own football stats database and provide a measured framework for developing my edge.

Following on from this I have made the decision to also write a book detailing how anyone, regardless of their IT proficiency can harness the power of a professional stats database and build advanced queries to really get the most from their data. This book is of course aimed at people with little to zero database knowledge and goes step by step through the following skills which are needed to become proficient in building and using stats databases:

  • Installing and configuring SQL Server

  • Setting up databases

  • Finding and importing raw stats data

  • Managing and updating the database (using PowerShell scripting)

  • Learning SQL for querying data

  • Querying the database using SQL

In addition the learning side once you have completed this text and have the database working I have included a number of powerful pre-written SQL scripts to help you get the most of this new database. Simply use them as they are making small adjustments to get the data you want or reverse engineer them to build completely new queries. In fact the power of the SQL and the database developed throughout this book should help anyone develop their football trading stats regardless of what level they are at.

The book will be availabe on Amazon from Friday the 2nd of August, click here for more info

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top