Today I took a look at the odds for the next couple of rounds of football matches in the Premier League after the international break. I imported the data directly into Excel from https://www.oddschecker.com/football/english/premier-league.
I removed the columns of data I was not interested in using Power Query, and split the odds columns in two to facilitate the conversion to decimal odds.
Everything worked fine except that when Excel reads in the table of data from the website, the names of the two teams get concatenated into one string. I could have used the league table to get the names of the teams, but different websites use different names for the teams – e.g. C. Palace, Crystal Palace; Sheff Utd, Sheffield Utd, Sheffield United, and when I use this same Excel workbook to look at match odds for different leagues and countries, there could be problems.
Therefore, I decided just to look at the team names string and calculate the position of the last character of the first team name so I could then extract both teams’ names into new columns. A lowercase letter followed by an uppercase letter is the splitting point *.
I used mid() to split each string into its characters, and then code() to get the ASCII code for each character.
97 to 122 are the lowercase letters a-z, and 65 to 90 are the uppercase letters A-Z.
I then used a simple if() to set a cell’s value to 1 if a lowercase letter is followed by an uppercase letter so that xlookups could be used to find the index of the last character of the first team name in each matchup.
* This technique falls over when QPR play a match at home, so I just change their name to QPr with Power Query when loading in the data.
left() and right() can then be used to extract the names of each of the teams using the index of the last character of the first team’s name to decide where to split the string.
The above shows the results of these steps after I had calculated inferred probabilities for the possible outcomes of each match, identified the favourites for each match, and generated a column/data label with the information I wanted to see – the names of the two teams, the favourite identified (using []), and the odds of the favourite winning the match.
The next chart shows all possible outcomes of the matches and their implied probabilities.
…and below is a modified example which labels the implied probabilities for all possible outcomes making for a more useful chart.
With everything now working, I can use the same workbook to look at upcoming matches in other leagues…for example loading in the next two rounds of matches in The Championship – https://www.oddschecker.com/football/english/championship.
…and the same for League One: https://www.oddschecker.com/football/english/league-1.
