Let The Overreactions Begin

Well, England have won a match of Association Football this evening, so I suppose it's only right and proper that we now all stand up in our living rooms, dressed smartly in our St. George's underpants, snap out a crisp and efficient salute and boldly declare England to be the greatest football team ever to grace this small and wonderful orb that we fondly call The Earth!


Yes, bipolar nation that we are, let's all flip from morbid depression at our bleak and dire prospects as a footballing nation to absolute cast-iron certainty that we're going to lift the European Championship trophy on 1st July. We only have to beat Italy, Germany and one other top team in the final. Piece of piss.

I sound rather heartless and negative, but of course I want us to do well. There'll be no one more delighted than me if we do somehow manage to progress to the final. But perhaps just a small amount of calm and perspective along the way might just be a good thing. What do you think?

__________________________________

I'm back in my hotel again tonight, and so after having my fingers burnt a little bit last week, I traded the England match using a low-impact, low-risk trading methods. This means rather low profits but I prefer that to silly losses. Before the tournament started, I layed Ukraine to make it out of our group so that small win also came in today. My lay dutch is now looking a little worse than it did yesterday and I'm now showing a £15 red. I've placed orders to close some teams off should they drift, which will effectively scratch those teams. Either way, I don't think I'll be making much money out of this little approach.


Football: £76.55 | Tote: |  Total P&L:  £76.55  

 

Football Showing 1 - 3 of 3 markets

Market Start time Settled date Profit/loss (£)
Football / England v Ukraine : Correct Score 19-Jun-12 19:45  19-Jun-12 21:39  25.37
Football / Group D : To Qualify 11-Jun-12 17:00  19-Jun-12 21:39  47.60
Football / England v Ukraine : Match Odds 19-Jun-12 19:45  19-Jun-12 21:36  3.58

__________________________________

I got this message today on my old match odds compiling post:


jking715 June 2012 14:21
Hi. I'm trying to do a test of the Goal Supremacy system. I have the data of the PL 2011/12 matches in an excel document and now I need a good COPYABLE function to calculate the goal differences.


Thanks for your help.


Grabbing a rolling figure for the last six matches is the trickiest part of this system. There's no absolute correct way and I'm sure there are some Excel gurus out there that can easily improve on the method I use, but here goes.

After downloading the data from football-data, I add some additional columns. One of these is a count of the games that each team has played, so if my teams are in columns B and C (home and away), then my count on each row would be:

=COUNTIF($B$2:B9, B10)+COUNTIF($C$2:C9, B10)+1

If you had plonked this in G10 for example, then you could simply drag this formula down and it will auto-populate the column all the way down. You'd then need to do the same for the away column.

Okay, so this gives us a count of each team's games, but how do we look-up their goal count over the last six matches using this additional information. Again there are plenty of ways to skin a cat, but I add two more columns that I use as lookups. I concatenate (or join) the team name with their game count (team_name & game_count) and then look for all the relevant matches. So if Liverpool had played 15 matches and I wanted to find all their goals scored over the last six matches, I would search for these strings:

Liverpool14
Liverpool13
Liverpool12
Liverpool11
Liverpool10
Liverpool9

This is the my actual formula (goals scored home and away over the last six matches):

=IF(G41<7, "", (SUMIF($H$2:H40,B41&(G41-1),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-1),$E$2:E40)+SUMIF($H$2:H40,B41&(G41-2),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-2),$E$2:E40)+SUMIF($H$2:H40,B41&(G41-3),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-3),$E$2:E40)+SUMIF($H$2:H40,B41&(G41-4),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-4),$E$2:E40)+SUMIF($H$2:H40,B41&(G41-5),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-5),$E$2:E40)+SUMIF($H$2:H40,B41&(G41-6),$D$2:D40)+SUMIF($J$2:J40,B41&(G41-6),$E$2:E40)))

Looks a bit of a mess, doesn't it? Really I should provide a "name" for certain columns. This is something I normally do as it makes formulas much easier to read, but I haven't in this case. Once again, this formula is "draggable" and so will always grab the rolling count of goals scored by any team in question.


I hope this is the answer to the actual question that you were asking me, but if it isn't then perhaps it still gives you the basic idea of what you need to achieve.