Rating system from Football-data (...math ) help needed !!

I'm working on a spreadsheet, with a lot of different rating systems.
Came across the "Rating systems for fixed odds football match prediction" on
www.football-data.co.uk/ratings.pdf. There are a few things I can't quite figure out : ( I'm not much of a math buff..so be patient )

1. In the document there's the following equation :
y = 1.56X + 46,47
y=prob. of a homewin X=match rating.
The 46,47 is the homewin % when the matchrate is 0
But where does the 1,56 come from ?
And why is the awayequation : y=0.03X2 - 1.27X + 23.65
drawequation : y=0.03X2 - 0.29X + 29.48

2. As I've understood it, the distribution of H/D/A for a
matchrate of 0, is virtually the same as the dist. of all
games played ( regardless the matchrates ).
Could you take the easy way out, and just use the dist.
for all games and run that through the equation ? Of
course it wouldn't give you the accuracy of the real
statistically calculated values, but it would somewhere
around there....wouldn't it ?!?

Hope somebody can help me.

y = 1.56X + 46,47

the above equation basically describes the best fit line for that data. so if you tap in y, you can find x.

1.56X means multiply 1.56 by the value X.
the 1.56 is the gradient (steepness) of the line

the +46.47 part is the intercept (where it crosses the y axis)

so in your example (1.56 x 0) + 46.47 = 46.47

[Joe]

Muppet is indeed correct.

Regarding the best fit line for the away wins, this is described by curve, which is why there is an x-squared term in the equation.

Remember, these equations only apply to this dataset and to this rating system. The point of the document is to show how to do it.

For the record, the away wins equation fails to make any sort of profit, in fact it's bloody useless, but I have found all away win rating systems to be bloody useless, in part I think because of the greater variability in the data, i.e the best fit line explains much less of the variability, so is a poorer predicter of future games.

Regarding point 2, I'm not too sure what you mean. Matches with zero ratings are about the midpoint of the spread of ratings, which is why the H/D/A distribution is about the same as that for the whole dataset.

Sorry for interrupting your discussion.

As Joe mention in the pdf file that we will take last 6 'Goal Difference' of both teams and compare them. I would like to ask are there any easy methods to find the 'last 6 GD' in Excel? I do it in Mysql (upload the csv file to server and find the GD by php) but it is quite complicated. Any functions in Excel can do this and save our time?

Thx :p

[Joe]

The Average or Sum function can be used here. If the prededing data are in, for example, cell locators A2, A3, A4, A4, A6 and A7, I would use =Sum(A2:A7) and put this output in row 8, to coincide with the next match. Do this for the 6 preceding games for both home and away sides.

The only complication is that with teams playing both home and away in the preceding 6 games, you have to somehow get all the data in the correct column, otherwise if you sort by home team (or away team) and date, you simply get the last 6 home (or away) games (make sense?).

To get round this, I simply make a copy of all the data, swap the home and away columns over, (and their corresponding odds), reference which team is which (using H for home and A for away in a further column), add this data to the bottom of the original set, and then re-sort by team and date. This then gets you the last 6 games played by a team and tells you when they were home and away. A bit convoluted (and a macro would probably do it better) but it suits me.

Hey Joe.

Well, if you say it's crap.....idea is dropped. Had another idea though :

Team A - Team B

Team A. Homegames : 0-4-4. In % = 0% - 50% - 50%
Team B. Awaygames : 2-1-5. In % = 25% - 12.5% - 62.5%

Homewin : homewins+awaylosses/2 = 0 + 62.5 / 2 = 31.25 %
Draw : homedraw+awaydraw/2 = 50 + 12.5 / 2 = 31.25 %
Awaywin : awaywins+homelosses/2= 25 + 50 / 2 = 37.50 %

Is that a usable way to calculate W/D/L distribution ??
Checked it on a few matches ( I know...a few won't do), looks allright to me.

What I can't figure out is wether or not it's fair to just calculate the average %. After all there's always a home-advantage.

Somebody came up with this idea to calc. the homeadvantage:
pub112.ezboard.com/fpunte...=342.topic

Another one could be to take the distribution of W/D/L for the whole season. In the league where Team A and B are playing that would be, say : Homewins : 46.15% Draws : 27.35% and Awaywins : 26.50%
But how the h*** do you find the homeadvantage ?, and even more importantly : how do you incorporate it in the above Team A / Team B calculations ?

[Joe]

I never said it was crap, just that simple rating systems like recnt form based on goals aren't good at predicting away wins. I've found they aren't bad at home wins, and several appear to be profitable. But the basic methodology behind devising and testing the rating system is still sound.

To answer your other questions ("Is that a usable way to calculate W/D/L distribution ??" and "how do you incorporate it in the above Team A / Team B calculations ?", you need to follow through the same basic workings to define a relationship between your ratings and result expectancies, and then test that relationship either by placing imaginary bets, or, to save time, back test using old odds data (provided that the sample of data you use to test the prediction model was not included in the original model development).

I see so many idea for rating systems, but they all remain merely unproven ideas until they are properly tested, and the point of the document is to show how to do this in a structured manner. The level of maths one needs to have is minimal, GCSE at most, and access to Excel or some other similar software.

So if you want to see if your model proposed here is any good, plow through the steps and see what happens.