Create Own Ratings and Kelly Staking

[SniffTheGlove]

I am looking at creating ratings for all the teams in my database. I have looked around this board and got quite a few ideas, so starting off simple I am using the details from Jenspm's Thread http://www.punterslounge.com/forum/f21/how-beat-bookies-value-betting-guide-inc-spreadsheet-102626/ to create the ratings then using Kelly Criterion for the staking. I have created my own initial test spreadsheet to make sure all the maths and equations work before I code it all into my software. Could people look at my spreadsheet and tell me if it's good, stable or bad with bad maths and also ways to improve the model. You only need to change the data in the yellow cells, all other cells have formula's based on the yellow cell data. The data is taken from a match played on 20th of Jan 2010 (Arsenal - Bolton), the match ended up 4-1 to Arsenal. The odds used are from Bet365 taken from my footbal-data data. The ratings are based per said Jenspm "How to beat the bookies – a value betting guide" thread I just want to know it's all correct before making a fool out of myself. Thanks

[Shinigami]

Re: Create Own Ratings and Kelly Staking Personally, i do not think it's wise to use the last 'X" number of games as calculation of value. i don't find it a reliable form of measure. Recent form and winning rates can change dramatically in a short time, but usually ability to score change slowly. Reversion to the mean is almost a law of nature with respect to team performance. More often than not, teams whose recent performance has been poor tend to perk up and improve. Whereas, a team who has been on a hot streak in the last "x" number of games may just falter. Besides, when you're only drawing recent sample from the last "X" number of games, it's a form of "recency bias" - putting emphasis in recent wins/losses.

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

Personally, i do not think it's wise to use the last 'X" number of games as calculation of value. i don't find it a reliable form of measure. Recent form and winning rates can change dramatically in a short time, but usually ability to score change slowly. Reversion to the mean is almost a law of nature with respect to team performance. More often than not, teams whose recent performance has been poor tend to perk up and improve. Whereas, a team who has been on a hot streak in the last "x" number of games may just falter. Besides, when you're only drawing recent sample from the last "X" number of games, it's a form of "recency bias" - putting emphasis in recent wins/losses.

Thanks for the reply however I am asking if the maths details in the spreadsheet are correct as others would expect it to be, or improvements to the spreadsheet formulas where I have missed something out. PS, I my first posting I put the score line as 4-1 which is incorrect, it should be 4-2.

[Shinigami]

Re: Create Own Ratings and Kelly Staking

Thanks for the reply however I am asking if the maths details in the spreadsheet are correct as others would expect it to be, or improvements to the spreadsheet formulas where I have missed something out. PS, I my first posting I put the score line as 4-1 which is incorrect, it should be 4-2.

Okay. Anyway, i feel there's a flaw in the way you derive the win/draw/loss percentages... it's inaccurate to obtain it by simply averaging H(W) & A(L) to get your home% , H(D) & A(D) to get draw% , H(L) & A(W) to get Away% & so on... if i'm not wrong, i think the number of A's wins should be weighted/proportioned in relation to B's.. the formula is W%(A v. B) = W%(A)*(1 - W%(B))/(W%(A)*(1 - W%(B)) + (1 - W%(A))*W%(B)) likewise for Draw: D%(A v. B) =D%(A)*(1 - D%(B))/(D%(A)*(1 - D%(B)) + (1 - D%(A))*D%(B)) for Away: L%(A v. B) = L%(A)*(1 - L%(B))/(L%(A)*(1 - L%(B)) + (1 - L%(A))*L%(B)) then you average the respective figures to the sum of 100% to get the respective percentages of Home/Draw/Away... to understand better, you can google keywords "log5 odds ratio bayes formula" or http://www.tangotiger.net/wiki/index.php?title=Log5 http://www.williams.edu/go/math/sjmiller/public_html/103/Log5WonLoss_Paper.pdf

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Thanks again for the reply Shinigami.

Okay. Anyway, i feel there's a flaw in the way you derive the win/draw/loss percentages... it's inaccurate to obtain it by simply averaging H(W) & A(L) to get your home% , H(D) & A(D) to get draw% , H(L) & A(W) to get Away% & so on...

It was what how the example in Jenspm's Thread was laid out (url in first posting above). Thanks for the revision, I shall incorporate it and try it out. I have never done team ratings before and just starting out and Jenspm's Thread was the best I could find where someone was telling the process (Other threads kept how they derived their rating secret) from a beginners point of view on how to start. I was going to make sure the basic concepts in my spreadsheet were correct and the Maths was OK then start adding in different equations and routines to "fine-tune" and your second posting will help me achieve this. I want to go through my database of 117886 Match results and then work backwards giving each team a rating, then do backtest to check the ratings are close to what the outcome actually was then I could then start working on getting ready for the 2010-2011 season using MY ratings. Thanks

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

the formula is W%(A v. B) = W%(A)*(1 - W%(B))/(W%(A)*(1 - W%(B)) + (1 - W%(A))*W%(B)) then you average the respective figures to the sum of 100% to get the respective percentages of Home

OK, trying to understand the above. I take W%(A) to mean Percent of team A wining, like wise W%(B) OK, I have used the last 6 matches to calculate. Arsenal had 4 wining games out of the 6 matches and Bolton had 2. The percentages come to 66.66 for Arsenal and 33.33 for Bolton, then using the equation replacing W%(A) with 66.66 and W%(B) with 33.33 and the result is 0.496163683 Is this correct is your eyes or did I misinterpret your posting?

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

to understand better, you can google keywords "log5 odds ratio bayes formula"

I just have and the 2nd best search result is this thread ;) Delving deep I find not much in the way of examples on how to use this Log5 formula you posted so you are going to have to explain the maths for me as I just do not understand. I did find a few posts Example that refer to the Log5 formula but they state that the formula does not take past games into consideration as it just works on percentage and these are quite misleading. Take Team A that has played 20 games and won 19 of them and them Team B which has played 2 games and won both, this formula would make Team B more likely to Win even though it has a short track record compared with Team A. How can I get around this using the formula to weight in favour as you suggest? Some help is greatly needed.

[Shinigami]

Re: Create Own Ratings and Kelly Staking

Thanks again for the reply Shinigami. I have never done team ratings before and just starting out and Jenspm's Thread was the best I could find where someone was telling the process (Other threads kept how they derived their rating secret) from a beginners point of view on how to start. Thanks

there's no one-rule-fits-all in soccer ratings, in the sense that different people subscribe to different school of thoughts - different strokes for different folks.. but essentially there are 4 quadrants to the schools of thoughts: (1) recent form & average goals scored/ conceded (2) aggregate form & average goals scored/ conceded (3) recent form & win/draw/loss percentages (4) aggregate form & win/draw/loss percentages for (1) & (2) , it's the belief that winning & losing is a matter of luck (teams cannot control the OUTCOME). However, the goal scoring rates & conceding rates are within their ability. And for that the "Poisson Distribution" comes into play. for (3) & (4), it's the belief that goal scoring/conceding rates are not a good measure, because strong teams may field out weaker players, which undermine their past winning records.For this, some people will adopt the "Elo Ratings". i'll explain further when i come online again...

[Shinigami]

OK, trying to understand the above. I take W%(A) to mean Percent of team A wining, like wise W%(B) OK, I have used the last 6 matches to calculate. Arsenal had 4 wining games out of the 6 matches and Bolton had 2. The percentages come to 66.66 for Arsenal and 33.33 for Bolton, then using the equation replacing W%(A) with 66.66 and W%(B) with 33.33 and the result is 0.496163683 Is this correct is your eyes or did I misinterpret your posting?

this is correct, but that is only one side to the calculation. This is insufficient because the formula was originally designed for baseball which doesn't incorporate the possibility of DRAW. Thus, to adapt this formula into soccer, there's a need to substitute W% to D% & L% respectively... If you twist formula around, you'll get the percentage of Bolton's probability of losing in relation to Arsenal, and the result is 1 You average these two figures, and you get 0.7442 You do the same For Arsenal's probability of losing in relation to Bolton, and Bolton's probability of WINNING in relation to Arsenal, and the result will be 0 and 0.5117 respectively. You average these two figures, and you get 0.2558 Bearing in mind that the original formula was designed for baseball, you assume that Draw as a percentage of total = 5D/(6W+5D+1L) = 0.4166 However if u sum these 3 up they do not equal to full probability 1, so u need to re-adjust by proration. Thus the final answer is: Arsenal Win = 0.52535 Draw = 0.294 Bolton Win% = 0.1805 This is a more realistic representation, compared to your original calculation, which tends to overstate the probability of draw at 41%

[Shinigami]

Re: Create Own Ratings and Kelly Staking

I did find a few posts Example that refer to the Log5 formula but they state that the formula does not take past games into consideration as it just works on percentage and these are quite misleading. Take Team A that has played 20 games and won 19 of them and them Team B which has played 2 games and won both, this formula would make Team B more likely to Win even though it has a short track record compared with Team A.

there's definitely a limitation to Jenspm's method if u want to use the last 6 games to derive the probability. Firstly, the sample is too small for the result to be meaningful - if a team shows WWWWWW, it could be just a lucky winning streak.. or if you have DWDDDL, then your calculation of draw could be overstated unrealistically... in order for you to make any meaning out of past records, you have to be sure that the team have played enough number of games with a fair number of strong, average, and mediocre opponents (law of averages), such that you can assume that the winning percentage takes into account a reversion to the mean. Which is why, i find that using last 6 games as the sole determination of value, is too simplistic & distorting. In order to derive any meaning, i think you should use at least 10 games to be minimally sure that the team has played against a mix of strong/average/mediocre teams that allows their overall strengths to be regressed to the mean.. Besides, the element of luck is a present in their past records, so a better form of measure, in my opinion, is their goal scoring/conceding ratios...

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Thanks again but to be truthful I am still confused. I am the "Monkey see monkey do" type of person. In your previous post you quoted the formula's to be...

the formula is W%(A v. B) = W%(A)*(1 - W%(B))/(W%(A)*(1 - W%(B)) + (1 - W%(A))*W%(B)) likewise for Draw: D%(A v. B) =D%(A)*(1 - D%(B))/(D%(A)*(1 - D%(B)) + (1 - D%(A))*D%(B)) for Away: L%(A v. B) = L%(A)*(1 - L%(B))/(L%(A)*(1 - L%(B)) + (1 - L%(A))*L%(B))

That was 3 formula's to work things out to get Home/Draw and Away. I know I understand the formula's above as you agreed my interpretation was correct. So using this interpretation I get the following results.. For Home Formula = 0.496163683 For Draw Formula = 0.502564103 For Away Formula = 0 Now in your lastest post you tell me that the above is one one side of the calculations.

this is correct, but that is only one side to the calculation. If you twist formula around, you'll get the percentage of Bolton's probability of losing in relation to Arsenal, and the result is 1 You average these two figures, and you get 0.7442

What was the formula you used to get the answer 1. I have tried looking and doing calculations but can not see how you came to 1, if you could help this bit then the light will shine for me to see. I then take this new additonal formula to then do the Draw and Away.

[Shinigami]

Re: Create Own Ratings and Kelly Staking

Thanks again but to be truthful I am still confused. I am the "Monkey see monkey do" type of person. In your previous post you quoted the formula's to be... That was 3 formula's to work things out to get Home/Draw and Away. I know I understand the formula's above as you agreed my interpretation was correct. So using this interpretation I get the following results.. For Home Formula = 0.496163683 For Draw Formula = 0.502564103 For Away Formula = 0 Now in your lastest post you tell me that the above is one one side of the calculations. What was the formula you used to get the answer 1. I have tried looking and doing calculations but can not see how you came to 1, if you could help this bit then the light will shine for me to see. I then take this new additonal formula to then do the Draw and Away.

Okay, my mistake when i introduced that draw is D%(A v. B) =D%(A)*(1 - D%(B))/(D%(A)*(1 - D%(B)) + (1 - D%(A))*D%(B)) This is because the resulting figure will more or less result to 0.5 and it's unrealistic.. Thus, a more realistic figure would be the latest one which i provided. As for how i got "1", it's obtained by: L%(Bolton)*(1 - L%(Arsenal))/(L%(Bolton)*(1 - L%(Arsenal)) + (1 - L%(Bolton))*L%(Arsenal)) = 33.33% * (1 - 0) / 33.33% X (1 - 0) + (1 - 33.33%) * 0 =33.33% * 1 / 33.33% + 0 = 1

[Shinigami]

Re: Create Own Ratings and Kelly Staking Hope you have comprehended what i've explained thus far, bcoz English is my 3rd language... My mother tongue is Chinese.

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

Hope you have comprehended what i've explained thus far' date=' bcoz English is my 3rd language... My mother tongue is Chinese.[/quote'] Very good English, I must congratulate you. However, I am still confused :wall In regards to calculating the 1. In my spreadsheet Shinigami I worked out Bolton Loss to be 16.66% which is 1 lost in the 6 games. ((100/6)*1) I can not see how you got 33.33% for Bolton Loss% to start the Calculation with. I agree that Arsenal's loss % is 0. How are you working Botlon loss to 33.33%

[Shinigami]

Re: Create Own Ratings and Kelly Staking

Very good English, I must congratulate you. However, I am still confused :wall In regards to calculating the 1. In my spreadsheet Shinigami I worked out Bolton Loss to be 16.66% which is 1 lost in the 6 games. ((100/6)*1) I can not see how you got 33.33% for Bolton Loss% to start the Calculation with. I agree that Arsenal's loss % is 0. How are you working Botlon loss to 33.33%

Ok, my mistake coz i mistook that the Won % column refers to Arsenal's Win & Bolton's Loss.. & Loss% column refers Arsenal's Loss & Bolton's Win... Let's do it again, referring to your excel cell numbers.. Arsenal Win (in relation to Bolton Win) in your excel spreadsheet is: =E2*(1-E3)/(E2*(1-E3)+(1-E2)*E3) Bolton Loss (in relation to Arsenal Loss) is: =I3*(1-I2)/(I3*(1-I2)+(1-I3)*I2) thus, the average of these two will be: =AVERAGE(E6:I7) ---------------------------------- Arsenal Loss (in relation to Bolton Loss) in your excel spreadsheet is: =I2*(1-I3)/(I2*(1-I3)+(1-I2)*I3) Bolton Win (in relation to Arsenal Win) is: =E3*(1-E2)/(E3*(1-E2)+(1-E3)*E2) thus, the average of these two will be: =AVERAGE(E7;I6) ------------------------------------ Draw will be placed in cell "G8" , & the formula is: =SUM(F2:F3)/SUM(C2:C3) ------------------------------------- Thus, the initial figures will be: Arsenal Win = 0.48 Draw = 0.42 Bolton Win = 0.25 Since they don't sum up to full probability of 1, you re-adjust them in proportion to the sum total & the final result is: Arsenal Win = 0.42 Draw = 0.36 Bolton Win = 0.22 this should be it ;)

[Shinigami]

Re: Create Own Ratings and Kelly Staking

	E	F	G	H	I
1	Won %	Drawn	Drawn %	Loss	Loss %
2	66.6667	2	33.3333	0	0.0000
3	33.3333	3	50.0000	1	16.6667
4
5
6	0.4962		0.4167		0.0000
7	0.5038				1.0000
8	0.4833		0.4167		0.2519
9	0.4196		0.3617		0.2187

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

Let's do it again, referring to your excel cell numbers..

OK Shinigami, I am doing it now and shall see.

[Shinigami]

Re: Create Own Ratings and Kelly Staking

OK Shinigami' date=' I am doing it now and shall see.

i just discovered that if both teams have not lost a single game in their last X games, one of the components in the formula will be 1/0 ==> infinity, and excel will show an error message : #DIV/0! However, i think the way to get round this is to turn 0 into a negligible fraction of 0.001 , so that the formula will still turn out a result in such cases.. Do you know how to create a conditional excel equation that turns 0 into 0.001 in the above circumstance?

[Shinigami]

Re: Create Own Ratings and Kelly Staking

i just discovered that if both teams have not lost a single game in their last X games, one of the components in the formula will be 1/0 ==> infinity, and excel will show an error message : #DIV/0! However, i think the way to get round this is to turn 0 into a negligible fraction of 0.001 , so that the formula will still turn out a result in such cases.. Do you know how to create a conditional excel equation that turns 0 into 0.001 in the above circumstance?

Okay,what i've done to resolve this issue is to add 0.0001 to the equations for each cell for number of games won , draw, & loss.. for example: =LEN(P2)-LEN(SUBSTITUTE(UPPER(P2);"W";""))+0.00001 this prevents the result from going into infinity if both teams have not won a single game, or both have not lost a single game, or both have not drawn a single game...

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Thanks for posting the table view. Using the table you supplied, I see what you mean and all the results For cells E6, E7, I6 and I7 I understand now and my calculations agree with what you had typed, however I am confused with cell E8. This is the cell that averages E6:I7 and comes out for me as 0.748081841 and not 0.4833 as you specify. I tried many different ways but can not get 0.4833 I wonder instead of bashing backwards and forwards with this thread with me trying to understand so I wonder if you could amend the initial spreadsheet football.xls and either attach to the post here or email it to me. I don't know if you have attachment posting capabily on the forum else could you send it to my email address. I have tried sending my address via PM however you do not accept PM's or have the permissions to accept them, so my email address is ***** Thanks Shinigami PS, when I get the spreadsheet I will amend this post to remove my email address.

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Just noticed that you have posted whilst I was typing, again if you could add that into the spreadsheet to send me my gratitude will be enormous for you.

[Shinigami]

Re: Create Own Ratings and Kelly Staking

Thanks for posting the table view. Using the table you supplied, I see what you mean and all the results For cells E6, E7, I6 and I7 I understand now and my calculations agree with what you had typed, however I am confused with cell E8. This is the cell that averages E6:I7 and comes out for me as 0.748081841 and not 0.4833 as you specify. I tried many different ways but can not get 0.4833 address.

Yes, my apologies... coz i mis-typed E6;I7 as E6:I7 in the cell... the typo error caused the difference....

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Thanks very much, I shall now go away to digest this along with a few other things then I shall come back and upload the latest spreadsheet to help others. Thanks again Shinigami

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking It has taken a few weeks but I have now completed a spreadsheet to act as a test bed for prediction equations for back testing. Some of the equations were with help from Shinigami To play with the data, you just need to enter data into the yellow cells in the first 3 worksheets (MatchData, HomeTeamData and AwayTeamData) and the rest of the sheets use this data to present everything. On the first sheet you fill in the Date, Teams, Odds and what the actual score was. 2nd and 3rd sheets are the previous team matches in match date order with the latest match being at the top of the sheet and the oldest match at the bottom. Use at least 6 previous matches for data. The 4th sheet is called InitialComparrision does an inital comparrision of the data and form using WDL format and then you get to see the simple percentages of the matches. The 5th Sheet is called Log5 and it uses the Log5 formulas to give percentages of the match data. Shinigami helped me with this. The 6th Sheet is a data dump from my database of all previous scores for all matches and list this as total numbers and also percentages so you can see what score lines are most likly. The 7th Sheet is to be used to allocating points etc to different type of form patterns. The 8th Sheets is a dual Poisson sheet that shows a complicated heavy calculation poisson method using quite a few variables generated from your inputted data from HomeTeam and AwayTeam Datasheets. There is also a much simpler poisson menthod taken from just the typical for/Against scores. The 9th sheet is the outcomes page where all the results of the data calculations are shown with value bets and kelly staking etc. I hope this all helps a few people. I will always keep an updated copy on my website

conv_1359.xls

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking I also forgot to mention that I chose the match Birmingham-Man City from 29th March 2008 because in one PL thread quite a few posters all put there data for this match into that thread, so I choose this so I can see how good my formulas were compared to theirs. Their match prediction percentages are.... Rushian Home 35 Draw 32.6 Away 32.4 Hooloovoo Home 37 Draw 30 Away 33 Lunatism Home 25 Draw 30 Away 45 Grex Home 39 Draw 28 Away 33 Average of all above Home 33.65 Draw 29.19 Away 37.16

[Grex]

Re: Create Own Ratings and Kelly Staking Looks impressive.... but, unfortunately, way above my simple head :eek

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking

Looks impressive.... but' date= unfortunately, way above my simple head :eek

Thanks Grex. I only started out to create a simple rating system which I could then tweak but it has grown into much more and I still have some few extra routines to put onto the sheet to help with Correct Score Predictions etc and of course a good rating system!

[SniffTheGlove]

Re: Create Own Ratings and Kelly Staking Sorry, I have not updated it any more as it was just a test bed for me looking at different equations.