World Cup Simulation I created in Excel

[skuban]

Hey guys, I have spent the last few weeks building a World Cup prediction simulation in Excel. It uses actual historical data and different information about it to predict the score between any two teams. I put it all together into Excel, and it's been a really fun way to waste some time for me. I thought some of you World Cup keeners might get a kick out of it. It's especially fun because it produces different results every time. I've put it here:

https://drive.google.com/file/d/0B018ERNCUIVUVkZmQUQ4R2dfdTg/edit?usp=sharing

It uses macros in Excel, so you'll have to enable macros to play with it. Hope some of you can enjoy it like I enjoyed making it!

skuban

PS: If anyone's interested in the mathematical / statistical work generating these numbers, I can talk about that.

[hscer]

I'm sure you listened to Klinsmann and plugged in 0% for USA's chances, right? 

[skuban]

Crap, I put in the wrong link. The simulation can be downloaded here: https://docs.google.com/file/d/0B018ERNCUIVUNUlQWUQwajBxUHc/edit?pli=1

And yes, haha my data has a lot more confidence in the States than their coach ;)

[corndog]

It took me fourteen tries, but the US finally got to the quarterfinals!

Seriously though, this is super cool.  I do have a few questions (know that I am not an expert with Excel and even less of an expert with soccer (sorry...football)).  First, it seems like, on the "Random Score Generator" sheet, the final score is meant to take an average of those calculated; however, only the first column of scores is included in the argument.  Am I missing something?  Second, is it reasonable to assume a 75% success rate for every shot during the shoot out?  I would assume that some teams and players are better at this than others.

Anyway, thanks for the entertainment, skuban!

[skuban]

The Random Score Generator was initially designed to take into account the average score over 5 games. I then reduced it to 3. And then I finally settled on just doing a one-time thing. I decided this because when doing 5 (or even 3), it became much more likely for the averages to converge to their rates (they are Poisson random variables). In most knockout games, the teams are very closely matched, with rates as close as 1.35 and 1.39. Doing multiple iterations and averaging them out was leading me to a lot of draws, so I decided to do a one-time thing, which resembles the sport much more closely anyways.

Regarding penalties, I didn't have the data for penalty shoot-outs. I just estimated it on a personal basis, without favouring any particular team, since for the most part shootouts are random. I did reduce the chance of success to 65% if the scores were tied after 5 each.

[TheCleverone (Erin)]

Well, it took me 18 tries to get England to the quarters, and 20 to the semis (we got fourth place). In those twenty, we got out of the group about five times. But I have to say that this is wildly inaccurate - England won two penalty shootouts on try 24 ;)

I gave up trying to get us further after try 30.

Great bit of fun, skuban - I'm curious as to what all the numbers mean on the form sheet?

[skuban]

I've had England winning the World Cup a few times in my sims, and it's always funny seeing them win on penalties! An updated version of the simulation is here, with a few new variables in consideration and live updating of the form: https://drive.google.com/file/d/0B018ERNCUIVUWWo1RUNJeHNTWVU/edit?usp=sharing

The form sheet is a place where I put in the score from the last ten games each team has played. The first big red section is the number of goals they scored in a game, and the first big blue section on its right is the number of goals they conceded in the game. So the two scores in a game are separated by ten cells (really sucks for readability). Underneath those scores is the ranking of the team that they played. 

The second big red section is the normalized result for the number of goals they have scored. For example, 2 goals against Norway may be similar to 1 goal against Spain or 6 against San Marino. The second big blue section is the normalized result for the number of goals they have conceded, so conceding one against Luxembourg could be like three or four against Brazil. The formulas are underneath the big section, and I calculated them by estimating what kind of goals-for would be equivalent with respect to the ranking of the opposition. 

Finally, the first three columns (after the team name... there are only two in the original spreadsheet I put up). In the new one I've posted, the orange cells represent the number of points a team has gained in the last ten games, and this indicates their recent form. The second number (red) is the sum of the normalized goals they have scored in the last ten games, which gives an indication of the recent attacking strength of the team. And the blue cell is the sum of the normalized goals they have conceded in the last ten games, which gives an indication of the team's recent defensive prowess. 

I use these three variables to calculate an estimate for the number of goals a team is expected to score. (In the new spreadsheet, I use "home attacking form" * "away defensive form"). 

These variables actually give a really cool insight into how well teams are doing. Brazil has won each of their last 9 games, with a form of 27/30. The second best team is Algeria, believe it or not, based on points gained! Dark horses perhaps? Additionally, Bosnia-Herzegovina haven't been as free-scoring recently as everyone seems to thinking. They are actually below-average in their goalscoring form, with a poor defensive record, so the hype surrounding them may be undue. And the hype around Brazil is very real: they are the best in recent overall form, second best in attacking form (behind Chile), and the best in defensive form. Plus home advantage, they have a really good chance this year. Overall of the best attackers, the top four are all South American (Chile, Brazil, Argentina, then Uruguay).

Anyway enough blabber from me. I'm really excited for Thursday as I'm sure you can tell.

[Erin R]

I think your new simulator isn't working ;) - 3rd try and the USA win!