Jump to content
Announcements
*** Punchestown Festival Competition: Well done to 1st. Craig Bluenose, 2nd. Lee Grays & 3rd. Carole Dawney ***
** May Poker League Result : 1st McG, 2nd muttley, 3rd GirlyWirly **
** Football Tipster Competition Result : 1st CertiF, 2nd PercyP, 3rd adamross, 4th Joelavfc, 5th Chris P **
** May Naps Competition Result: 1st Internetmails, 2nd Rug, 3rd Rolandcooper, 4th Adamross. KO Cup Winner: Xtc12, Most Winners: Mickyftm32 **

Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)


Recommended Posts

Ah, the joys of watching Excel... For those of you who haven't experienced this, the first time at least, s rather special. You make a macro in Excel, it does its business (whether that be scraping data, converting data from one format to another), but you have to click a button every time you want it to do something...but you can push it a step further! If you have a series of repetitive tasks that you want to do (eg. cycle through a list of URL's and get data from each one), you can tell Excel to run on a loop until each action is completed. As Excel silently blasts through the loop, the status bar can be automatically updated to reflect what is happening at that moment in time. You'll look at it one second, and it will say: "Scraping Fixtures for La Liga (25 scrapes remaining)" ...and then a few seconds later it will say: "Scraping Fixtures for Bundeliga (18 scrapes remaining)". For me, being given on-the-second updates from Excel is pretty special, and even though i'll have a macro running and automating all my tasks, sometimes, i just stare at the status bar, fascinated by what will come next (even though I will already know, because I wrote the code)! Anyway, enough Excel love for the moment... Purpose of this thread With the World Cup around the corner, there will be lots of you who will try their hand at predicting the tournament, whether that would be for the office lottery, fun in general, betting or something else. The thing is, that regardless of how good your predictions are, you have to consider the element of chance...after all, there are 3 results that can happen, and just because you rate the chances of one particular result over another, it doesn't completely remove the possibility of the other two results! So what i'm going to show you in this thread, is one method that can be used to increase the reliability of your predictions, by leaving Excel to predict the tournament X number of times, the accuracy of your predictions will (or should at least) increase. What do I need? Since I'm not as nice as you might think, there's no way i'm giving you my own ratings for the World Cup teams! This means that you'll need your own ratings, that have been converted into 1/X/2%'s (eg. Country A vs. Country B 65%-25%-10%), FIFA rankings for each country and Excel! That's it really. The FIFA ranking are necessary, because unless you are also predicting scorelines (we won't be going into that today), you'll need a method for differentiating between two teams with the same points totals (more on that later). If you are simply interested in the concept of modelling tournaments and leagues, rather than generating your own figures, I have attached a spreadsheet with all the group stage fixtures for this tournament, along with the %'s from Betfair. They have been adjusted so that the sum of the 1/X/2% for each match amounts to 100. Setup First off, I recommend that you use the attached workbook (available here: [ATTACH]6003[/ATTACH]) - at least just for starters, so that you can get used to the formulae, ideas and concepts that we'll be working with. It's not at all complicated (for me, at least!), but why not familiarise yourself with the attached, before branching off on your own. One of the benefits of this, would be that you could compare your results with the results I'll post. Yep, i'll be posting up the modelled results of the Group Stages, just so that you get an idea of what kind of output to expect. We're dealing with randomisation here, so my results and your results will not be identical, but they'll probably be similar, since we'll be using the same Betfair ratings as a starting point. If you're stubborn, or just want to feed in your own data, here's how to setup the workbook: 4 Sheets - FIXTURES/COUNTRIES/MODEL/RESULTS FIXTURES - DATE/GROUP/TEAM1/TEAM2/1/X/2 COUNTRIES - COUNTRY/GROUP/FIFA RANKING MODEL_DATA - ITERATION/COUNTRY/FINAL POSITION RESULTS - COUNTRY/GROUP/1/2/3/4 That's the basic setup. We will add additional columns later on, but this is how we start out. The extra columns will come with explanations as to what they contain and why they are used in a particular way, and i'm not doing all your work for you! The Column headings that are red, mean that you have to put data in those columns (right now...hurry up!!). As i've already suggested, just use the attached workbook, but if not, you'll need to input that data yourself. On the fixtures sheet, the only *important* information is TEAM1/TEAM2/1/X/2, the rest is just for your own reference. Let's start!!! Presuming you have all the data in the right place, let's begin. Randomisation (all of this takes place on the FIXTURES sheet)Take the first match of the tournament for example, Brazil vs. Croatia, the %'s are 74%-18%-8%. So that means that according to Betfair there is an 8% chance of Croatia getting a win. I'm sure that most of you will say Brazil win all day long, but if 8 out of 100 times Croatia are expected to win, we should try to incorporate this into our model. To do this, we use the Excel function RANDBETWEEN. This clever little function will give you a number between two specified digits. So our first step, is to make use of this in COLUMN I.

  • Column I should be labelled RAND
  • In I2, copy the following formula, and then copy it down to I49:
=RANDBETWEEN(1,100)/100 The quick amongst you will realise, this formula will provide a random number between 1 and 100, and divide it by 100. This will return a number, which is directly comparable with the percentages that you see in columns E, F & G. You will notice (if you haven't already), that every time you make a change to any cell in the sheet, that your random numbers will change...this is what is supposed to happen. If the numbers aren't changing with every cell modification, you've done something wrong. Press F2 and then enter, that should make the random numbers change...fun to play around with for a while! Determining Result Now we need to apply this random number to our percentages. It's much easier to explain with an example, so let's consider the Brazil vs. Croatia game again. Brazil win is 74% Draw is 18% Croatia win is 8% Let's have a random number of .73, this means that Brazil will win the match. If the number is less than or equal to 74%+18% (92%) then it will be a draw, and the remainder as a win for Croatia. Because of the order of the formula, there is no need to specify that the number has to be between 74 and 92 for the draw, Excel will automatically figure that one out. Create a heading in J called "Result", and copy the following formula down the whole column: =IF(I2
  • Add two extra columns in K and L, named T1pts and T2pts
  • Copy this formula to K2:
  • =IF(J2="1",3,IF(J2="X",1,0))

    • And copy this formula to L2:
    =IF(J2="2",3,IF(J2="X",1,0))
    • Don't forget to copy the formulae down to row 49

    Group Placement (all of this takes place on the COUNTRIES sheet) Now that you have dealt with the randomisation and points distribution process, we need to determine finishing positions. You need to add 6 more columns: D = PTS E = adj. PTS F = RANK G = (leave it blank) H = ITERATION I = COUNTRY J = FINAL POSITION D = PTS In this column, you'll combine two SUMIFS to total the number of points that each team has received in the group phase. The reason why there are two SUMIFS, is because sometimes the team name appears in the TEAM1 column, while other times it will be in the TEAM2 column. Copy the following formula from D2 to D33: =SUMIFS(FIXTURES!K:K,FIXTURES!C:C,COUNTRIES!A2)+SUMIFS(FIXTURES!L:L,FIXTURES!D:D,COUNTRIES!A2) E = adj. PTS In this column, you adjust the points totals to account for any teams that have the same number of points. If you didn't do this, you would find that some teams would all be on the same points and not be given a proper rank. We use the FIFA ranking at this stage to circumvent this problem. Yes, it's a very crude method of doing it, but unless you predict your scorelines too, you'll need one way of differentiating between similar teams - this works. You will notice that this formula divides the FIFA ranking by 10,000, the reason for this, is so that the difference made by the FIFA ranking will not affect the position, just define it a little better. Copy the following formula from E2 to E33: =(C2/10000)+D2 F = RANK Here, a COUNTIF is used to count the number of teams in the same group that have a larger points haul. If this wasn't used, we'd end up ranking all the teams, instead of just the ones in the group. Copy the following formula from F2 to F33: =COUNTIFS(B:B,B2,E:E,">"&E2)+1 H = ITERATION This column is responsible for counting the number of iterations that have occurred, to do this a COUNTIF is used to search the B column on the MODEL_DATA sheet. Copy the following formula from H2 to H33: =COUNTIF(MODEL_DATA!B:B,COUNTRIES!I2)+1 I = COUNTRY Copy from I2 to I33: =A2 J = FINAL POSITION Copy from J2 to J33: =F2 Loops There are no formulae for the MODEL_DATA sheet, but you do need to put a figure into MODEL_DATA!E1. This number will be the number of iterations that Excel will run of the Group Stage. I think a good number would be 5 to start with, just so that you can test that everything is working ok. If it's all clear, i'd go ahead and make that 100 or 1000. The higher the number, the more accurate you could consider your model. Macro It's macro time!

    • Press ALT+F11 to bring up the Visual Basic Editor
    • Right click on ThisWorkbook and choose Insert>Module
    • Copy the following code into the window on the right:

    Sub MODEL_LOOP() START: 'determines if another loop should be run, based on your required number of loops on MODEL_DATA!E1 If Range("COUNTRIES!H2").Value

    • Using COUNTIF, count the number of times that a team finishes 1st, 2nd, 3rd and 4th, and put these figures into columns C,D,E,F. Copy this to C2 and drag it horizontally across to F2, then drag the range C2:F2 down to row 33:

    =COUNTIFS(MODEL_DATA!$C:$C,RESULTS!C$1,MODEL_DATA!$B:$B,RESULTS!$A2) Now you have the number of times that each event has occured. You might be happy enough with that, but i'd suggest taking this a bit further. Let's say that Brazil are predicted to finish 2nd 3 times, and you ran 15 iterations, this means that the %chance of Brazil finishing 2nd is 3/15 or 20%...convert this to odds (1/20%) and you have 5.00, so the odds of Brazil finishing 2nd would be 5.00! Conclusion This took forever to write...and I had to write it twice because I lost internet connection and my text wasn't saved...ah well, it's here now! [ATTACH]6003[/ATTACH] The attached workbook that contains the fixtures is aptly named fixtures & percentages. The format is xls, but after you add the macro, you will need to save it into a different format that allows macros. As promised, I ran this model, using the same Betfair data, and here are the Group Forecasts: [ATTACH]6004[/ATTACH] I can't say that I agree with all of that, but those are based on Betfair's figures, so this is what they think!

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) after a chat with froment, we decided that perhaps Systems would be the best place for this thread. What this means, is that in addition to there being a super-useful tool on here (come on...it's Excel plus automation...what's not to love? :p), i'm going to post the Betfair odds for each team reaching each predicted finish in the group, and then we'll see who comes out where. In effect, it will demonstrate the market efficiency of Betfair when it comes to pricing up teams and matches, and i'm rather interested to see how this will turn out. This will be done in two ways, blind backing (all the teams will be backed to finish wherever the model says they will finish if the % is above 40%) and the second option, which will be only to take on "value" bets. For this project (it's now become a project, that's pretty cool), we'll set "value" at 5%, and they also need to be likely enough to happen. Likely enough to happen = probability of greater than 50%. So, to recap, there will be blind backing (40% probability), and backing everything with a greater than 50% probability that has at least 5% value. The three markets that we'll look at will be:

    • Group Winner
    • To Qualify
    • Rock Bottom

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) We've named the markets - Group Winner, Qualify & To Finish Bottom, so below are the probabilities of each team doing just that: [ATTACH]6006[/ATTACH] Next post will cover the odds assigned for each market

    post-10890-1442928636637_thumb.jpg

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Hi, mate; another excellent topic and food for thought! :ok As agreed, I'm moving this thread in S&S.

    This took forever to write...and I had to write it twice because I lost internet connection and my text wasn't saved...ah well' date= it's here now!
    Ouch, that must have hurted... :\ But you'll probably hate me for this that I'm going to say... :p When writing a reply to the thread, have you spotted a small yellow sign "Auto saved", that pops up every ten seconds or so, in bottom right corner of reply field: ?di=NK8L That means what it says - content of your post was auto saved; if you lose your connection, or you closed the thread for any other reason without posting the reply, next time you return to that thread, yo'll be presented with button "Restore auto saved content", in bottom left corner of reply field: ?di=351L That button also works what it says - restores the content that you previously lost. :) OK, since you lost your connection, probably not all the content would have been saved, but it was worth trying, at least a part would be recovered.
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Here are the 40% blind bets: [ATTACH]6008[/ATTACH][ATTACH]6008[/ATTACH] As you can see, there are 34 of these, i'll post up results after Group Stage has finished

    post-10890-14429286366828_thumb.jpg

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Hi, mate; another excellent topic and food for thought! :ok As agreed, I'm moving this thread in S&S. Ouch, that must have hurted... :\ But you'll probably hate me for this that I'm going to say... :p When writing a reply to the thread, have you spotted a small yellow sign "Auto saved", that pops up every ten seconds or so, in bottom right corner of reply field: ?di=NK8L That means what it says - content of your post was auto saved; if you lose your connection, or you closed the thread for any other reason without posting the reply, next time you return to that thread, yo'll be presented with button "Restore auto saved content", in bottom left corner of reply field: ?di=351L That button also works what it says - restores the content that you previously lost. :) OK, since you lost your connection, probably not all the content would have been saved, but it was worth trying, at least a part would be recovered.
    I'm completely onto that yellow auto-save thing...i've used it a LOT lately. But i'm having to use a VPN service here, so even if my internet connection remains constant, the VPN client can die (it does this too regularly to be honest, my fault for sticking with a poor company) and if I don't notice, i can type away for hours, and it's not auto-saved. I think what happened, was that i'd clicked on the start new thread button, and then wandered off for a cup of tea, came back, not noticed it had disconnected, and so i was typing with no hope of it being saved (because there was no connection when I started). The actual auto-save feature is brilliant, my VPN service, on the other hand, is not so great :\ Thanks for the reminder though, i'm sure there are some folk out there who aren't aware of it :ok
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Here are the apparent value bets: [ATTACH]6009[/ATTACH] I say apparent, because it doesn't seem wise to back two in the same group to finish bottom when both are at evens (Group G), and I suppose that removing the USA one (since the probability of Ghana finishing bottom is 5% stronger), but I don't want to cherry pick...i'll just accept it). The important thing to remember, is that these probabilities are a reflection of the Betfair odds, and i'm not in agreement with most of them. Highlighting a few differences, my personal ratings do not have Italy at 64% to qualify, nor do they have France as low as 48% to win the Group (in case you wondered, my own ratings have them at 74%, which is unsurprising, given the quality of opponents they face). Anyway, if there are any questions about this thread, the manner of calculation, any problems that occur or just general input, i'll try and get back to you as soon as possible. Otherwise, i'll probably not return to post on here until the Group stage has been completed, as there isn't really much to add at this point! Let's hope England do better than Betfair think they will :hope

    post-10890-14429286367014_thumb.jpg

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    very nice thread with lots of insight. i wish i had your excel skills. for my systems i input the data manually.
    Don't worry - you can learn! In the Tools section, I posted two threads about ways to use Excel for data collection, and I'll probably add to that in the future Web Scraping with Excel - Part 1 Web Scraping with Excel - Part 2 I've posted up methods, formulae and macro's...not just so you could do exactly what I could do, but also so that you can modify and create too. One of the great things about using Excel, is that there are plenty of ways to do any given action, it means that i'm just as likely to learn from you, as you can from me :D With your tennis system, for example, the manual input of data might take up some time that you'd rather spend elsewhere specifically looking into other tennis markets and analysing other areas of potential value. For me, one of the biggest benefits of web scrapes, is that after a few tweaks here and there, I can do largescale backtesting. Usually, a site which provides data on upcoming sports events will also provide data on previous ones, so it's simply a matter of having the right URL's to scrape, and then being able to manipulate the data. Backtesting this way, can remove a lot (let's say 75% to 85%) of the doubt and uncertainty about launching a new system. It's probably one of the main reasons why i'm hardly posting as much on PL as I used to, public trials and paper testing has been replaced by cold hard facts and evidence from backtesting! I just noticed that you actually started a thread on backtesting, but nobody got back to you, which was a shame. 2 years too late, i hope that i'll be able to help if you're still looking for it! To answer your main question ("when?"), personally, there is never a time not to backtest. Backtesting goes hand-in-hand with everything else that I do, there are only rare occasions when i don't do it, and that's usually because it's somehow impossible to reverse-engineer the data
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Thanks for the reply here and in my thread too. Just as I said. I have various fields of data, they put out a lot of things to work with. Two areas of betting worked out great so far. Tennis and US Sports.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Absolutely fantastic thread lunatism, I have a couple of questions though; 1. My spreadsheets seems to encounter problems at the "H = ITERATION This column is responsible for counting the number of iterations that have occurred, to do this a COUNTIF is used to search the B column on the MODELS sheet. Copy the following formula from H2 to H33:"=COUNTIF(MODEL!B:B,COUNTRIES!I2)+1 ; and Excel keeps prompting me to open up a file for some reason? 2. Do you have a book or resource from which you learnt all this that I could visit or buy as your skills are very impressive and relevant? I'd love to be able to create graphs and tables from spreadsheet data to visualize, track and predict different events. 3. I am completely lost as to why you are inputting certain codes and what they mean; how do you know what all the different functions do and mean and how they interact with each other? Thanks for an amazing resource as it is something I've been interested in for ages but not actually looked into.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Absolutely fantastic thread lunatism
    :$ it just seems that way, when you get well-versed in Excel, you'll be writing up threads like that in your sleep (and you'll hate me for it!)
    1. My spreadsheets seems to encounter problems at the "H = ITERATION This column is responsible for counting the number of iterations that have occurred, to do this a COUNTIF is used to search the B column on the MODELS sheet. Copy the following formula from H2 to H33:"=COUNTIF(MODEL!B:B,COUNTRIES!I2)+1 ; and Excel keeps prompting me to open up a file for some reason?
    The only reason that Excel would prompt you to open a file at this point would be is you misspelled one of the sheets, or perhaps if you used different names. Excel can't find the sheets that you are referring to, and so it asks you to help it! To test this theory out, type =EXCEL!A1, you will get the same error :ok
    2. Do you have a book or resource from which you learnt all this that I could visit or buy as your skills are very impressive and relevant? I'd love to be able to create graphs and tables from spreadsheet data to visualize' date= track and predict different events. 3. I am completely lost as to why you are inputting certain codes and what they mean; how do you know what all the different functions do and mean and how they interact with each other?
    These two, I answered in your post on Web Scraping with Excel - PART 2 (XMLHttp for specific data) (all I'll add to the answers I gave, would be to avoid duplicate posts on different threads Don't worry about it though, it's not like I haven't done that before :p
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Must be the version of excel' date=' which is in spanish, but I can not make it work.[/quote'] Where's it falling down? I can't say i'm great at Spanish VBA, but I know that in the formulae, you need to substitute the ":" with ";". Some of the function names are also different. In my version of Excel, if you go to File>Optiions>General>Lanaguage, you can change the language. Can't you do that?
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Still does not work, some formulas fail, tell me the last two sumar.si there are too many parameters in function ... Thanks ...
    Gotta flex the "the internet is your library" approach here. Goto http://www.piuha.fi/excel-function-name-translation/index.php?page=english-spanish.html :ok It's worth the effort You have to be willing to deconstruct formula in order to understand them, if you look at the SUMIF function, and see what it does, you will realise why there are two, realising why there are two. Curiosity will then lead you to experiment to see what one does, and if you look at the columns that are referenced, you will see exactly how it works. If I tell you what to change it to, you'll ask me for every single line of code...and then there is no learner autonomy :p I love that you're putting in the effort so far, keep it up!
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) There is someone to help us with the translations? hehe. Yes, I've spent the formulas, but give me error parameters. I modified what I believe, and vba gets into an infinite loop Sorry for the misunderstanding. A greeting.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    There is someone to help us with the translations? hehe. Yes, I've spent the formulas, but give me error parameters. I modified what I believe, and vba gets into an infinite loop Sorry for the misunderstanding. A greeting.
    If Excel is getting into an infinite loop (magical stuff...always moving, never getting anywhere!!), it might be because you have failed to name a range properly or you're referencing an unchanging value (which could totally be my fault)...PM me a link to your saved workbook, and i'll take a look :p
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    FinalRow = Range("MODEL_DATA!A1048576").End(xlUp).Row + 1
    Keep in Mind that the 1048576 Rows is an Office2010 feature. Previous Versions limit was 65536 rows.
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Keep in Mind that the 1048576 Rows is an Office2010 feature. Previous Versions limit was 65536 rows.
    that's true...i sometimes forget that not everyone has the latest version of Excel, although if anybody is getting an error, they would be presented with this particular error message: Run-time error '1004': Method 'Range of object '_Global' failed It is probably down to having an older version of Excel.I'm not sure i'd want to run this in an older Excel anyway - might be considerably slower than otherwise. If anybody does get any errors, crop a PrintScreen of the error and click on the debug button and show us which line is highlighted, it'll make it much easier to identify any problems, and solve them for everyone :ok
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Hi, I had the same problem but resolved it by changing =COUNTIF(MODEL!B:B,COUNTRIES!I2)+1 to =COUNTIF(MODEL_DATA!B:B,COUNTRIES!I2)+1 I also noticed that D = PTS In this column, you'll combine two SUMIFS to total the number of points that each team has received in the group phase. The reason why there are two SUMIFS, is because sometimes the team name appears in the TEAM1 column, while other times it will be in the TEAM2 column. Copy the following formula from D2 to D33: =SUMIFS(FIXTURES!K:K,FIXTURES!C:C,COUNTRIES!A2)+SU MIFS(FIXTURES!L:L,FIXTURES!D:D,COUNTRIES!A2" will result in "NAME" showing up in the column. I figured out that there is a space in SU MIFS. Take out the space and it works great. I don't mean any disrespect lunatism, I think this post is great and really appreciate any help with Excel. I just didn't want anyone else to fall in the same traps as I did.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    I had the same problem but resolved it by changing =COUNTIF(MODEL!B:B,COUNTRIES!I2)+1 to =COUNTIF(MODEL_DATA!B:B,COUNTRIES!I2)+1
    Spot on! I noticed it as soon as you said it...my bad :spank I've edited the first post to reflect it. I changed the sheet name while I was making the tutorial, and I forgot to check the links (since Excel auto-updates all the formalae when you rename an already existent workbook)
    D = PTS In this column, you'll combine two SUMIFS to total the number of points that each team has received in the group phase. The reason why there are two SUMIFS, is because sometimes the team name appears in the TEAM1 column, while other times it will be in the TEAM2 column. Copy the following formula from D2 to D33: =SUMIFS(FIXTURES!K:K,FIXTURES!C:C,COUNTRIES!A2)+SU MIFS(FIXTURES!L:L,FIXTURES!D:D,COUNTRIES!A2" will result in "NAME" showing up in the column. I figured out that there is a space in SU MIFS. Take out the space and it works great.
    I don't mean any disrespect lunatism, I think this post is great and really appreciate any help with Excel. I just didn't want anyone else to fall in the same traps as I did.
    Non taken at all, you've spotted an error and it was something I didn't see, you've done everyone who's tried to use this a favour! :clap (the reason for the space, was because of the editing recognising that is automatic, when i copied the formula into the text box, it put a smile face before I turned off formatting recognition)
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) You've reached the end point, you now have your data modelled, and you simply need to "look" at it to rustle up some odds. The best way to do this, is the following:

    • Using COUNTIF, count the number of times that a team finishes 1st, 2nd, 3rd and 4th, and put these figures into columns D,E,F,G. Copy this to D2 and drag it horizontally across to G2, then drag the range D2:G2 down to row 33:

    =COUNTIFS(MODEL_DATA!$C:$C,RESULTS!D$1,MODEL_DATA!$B:$B,RESULTS!$A2) Hi lunatism, Thanks for the applause. I have been looking for excel sheets along these lines for ages and was chuffed when this appeared in Google. I used to run a fantasy league for my workplace back in the nineties using an Excel spreadsheet that I made. It took me weeks to come up with it from scratch and was over the moon when eventually everything worked just as it should. I remember the satisfaction I felt as like you must do, when you finally overcome an obstacle. You have reignited a pleasure from the past and I really look forward to reading some of your past projects and hopefully future projects. Now may i ask you if the above quote is correct ?. Should I not paste the formula into columns C,D,E,F instead of the above. Again thanks a lot for all this info.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    You've reached the end point, you now have your data modelled, and you simply need to "look" at it to rustle up some odds. The best way to do this, is the following:
    • Using COUNTIF, count the number of times that a team finishes 1st, 2nd, 3rd and 4th, and put these figures into columns D,E,F,G. Copy this to D2 and drag it horizontally across to G2, then drag the range D2:G2 down to row 33:

    =COUNTIFS(MODEL_DATA!$C:$C,RESULTS!D$1,MODEL_DATA!$B:$B,RESULTS!$A2) Should I not paste the formula into columns C,D,E,F instead of the above.

    The model sheet was about experimenting...in C, I had the FIFA ranking (as a reference point), I just didn't mention that when I was listing the sheet columns. So ye, that would be right...except in my one, had the C column taken up, so it was in D. I should probably make another change to the guide, in case people get confused ;) After all, the row heading that that cell is referencing is D1 because in my sheet I had "1" in D1 and "FIFA" in C1, which is not what I suggested doing in the setup section.... You spotting these errors, suggests that you're the only one who actually tried this out, huh? :p
    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage) Hi lunatism! Thank you for this great guide! Just wanted to let you know that I've tried this and liked it very much. I needed to make some changes to formulas also but in the end I got it right. Actually I think I learned more from this because of the mistakes :D Needing to go through all the formulas really made me think about them and what are they meant to do.

    Link to post
    Share on other sites

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Thank you for this great guide! Just wanted to let you know that I've tried this and liked it very much.
    Appreciated, the idea of modelling a series of games in such a simple way had occurred to me before, but I'd never actually tried it out. I don't think that the bookies odds will turn out to be to useful for predicting the winner (i've proceeding with modelling the rest of the tournament, for my own amusement), but this is definitely a good starting point for people who are frustrated at those "random" results screwing up their predictions. I believe that there does need to be a "random" element accounted for in betting, and this is one way in which that can be factored into the betting process. Aside from that, it's simply fun to play with, right? :p
    I needed to make some changes to formulas also but in the end I got it right
    Hmmm...that may be true, but I'm still annoyed that I forgot to check through before posting...error correction - they teach that to nursery kids... :spank After the first bit of advice I'd given out about incorrect sheet names too (#13), I really should have checked through properly....taken my own advice (of sorts)
    Link to post
    Share on other sites
    • 1 month later...

    Re: Using Excel to Model a Football Tournament (World Cup 2014 - Group Stage)

    Here are the apparent value bets: [ATTACH]6009[/ATTACH]
    Well, it seems that "value" bets using the odds set by Betfair were a bust! Italy, England didn't qualify and Costa Rica didn't finish bottom (and all at fairly short odds). The only winning forecasts were that Ghana would finish bottom and that France would win their group! So, two wins out of eight based on value solely from Betfair odds. A return of -4.16pts! What does this say about just following the odds in order to select group winners from a World Cup? Not a good idea :lol In defence of Betfair, this was a trickier World Cup to predict (for certain groups), and the none qualification of England AND Italy was completely against both the odds and the expectations of the majority!!! The purpose of this "experiment", was just piggy-backing on the World Cup, while showing you guys how you can forecast a tournament (or a league, that's possible too...i've been playing around with that) - but before you start trying to forecast things, you need reliable predictions, otherwise you'll end up with a negative bank balance! :ok
    Link to post
    Share on other sites

    Join the conversation

    You can post now and register later. If you have an account, sign in now to post with your account.
    Note: Your post will require moderator approval before it will be visible.

    Guest
    Reply to this topic...

    ×   Pasted as rich text.   Paste as plain text instead

      Only 75 emoji are allowed.

    ×   Your link has been automatically embedded.   Display as a link instead

    ×   Your previous content has been restored.   Clear editor

    ×   You cannot paste images directly. Upload or insert images from URL.

    ×
    ×
    • Create New...