Football (yes, soccer in countries where it is hardly played is big in Barcelona. Really big! I have always enjoyed watching football games, but nothing like I’m now, here with FC Barcelona! Especially this season is spectacular. I also started, together with my girlfriend placing bets or “doing the quiniela”.

The quiniela you play by predicting the outcome of 15 games played, mixing 1st and second division games. You either bet on 1, X or 2. hometeam losing, equalled or lost. this is how the form looks like:

I wont explain how it works, it is far too complex, and even after many years here, I have still not discovered them all.The idea is that I play with my girlfriend(who luckily likes football too) and this gives us a tiny bit of football thrill in the weekend. A few weeks ago I started to look and see if I could make this in an excel sheet, so I can have a view on how the games are going on each goal scored.

This excel sheet started out very simple. I would manually copy my bets, and enter the scores every time I heard “gooooooool” on tv, or checked using teletext(a technology with no reason to exist). Then, after a while, basically because one weekend we where late, filled in the sheet, and did not get to the betting office on time, I filed my bets on line.

Sometimes, when you live in spain, you expect certain things to either not work, or not work well. This was not the case,  and I found the spanish betting website to be very practical. It lets me fill in the exact same form, by ticking the boxes.

(If the Internet was not partly blocked here I would have had a screen shot…. Somehow they do not see betting as a work related activity…)

After validating the bet you get your bet presented on your screen like the ticket you get in the Betting office. a text overly on the image of the ticket, basically like this:

1    1    1    1    X     X
2    2    X     2    1    2
3    1    1    1    1    2
4    1    1    1    1    1
5    X     X     1    1    1
6    1    X     1    X     X
7    X     1    1    1    1
8    1    X     1    X     X
9    2    2    X     1    2
10    1    X     1    2    2
11    1    2    2    2    1
12    2    X     2    X     X
13    X     2    2    1    1
14    1    1    1    1    1
15    X

I copy pasted this in excel and one part of my excel sheet was already a bit automated (there was no more need to manually get the bets in) This made me think about a fully automated one, that would display the live scores and display the cells with the bets color-coded, and at the bottom the total amount of winning bets. Here is where the problems started. The color coding was pretty easy with excels’ conditional formatting. With a few IF functions this was starting to look good:

The yellow column is the game status changing when I started entering the results. The blue cells are my bets, changing colour when games are playing according to the live result.

I use Office 2007 at work, and at home. When I browsed around in excel, I found how easy they made the data import! getting the data from a text file where I saved my bets was a 2 minute job.

I also discovered the get data from web functionality. I started playing with to find out if I could get the live results from some website, and use this to refresh my sheet each minute. This was not as easy as I thought. It would be, if the websites would update their data each minute, but I guess it is hard to find a volunteer to go to the office on Sunday morning to cover just 1 second division game. The only Live source I found was … TVE Teletext …

If you check the link, you’ll see their page has exactly what I needed, and on top of that it is updated almost by the minute. The only but… is that they present it as an image!!

That made me have a more thorough look at the page. after looking at the source code, I saw that the alt tag of the image contains all the data. Ready to be extracted!

<img id=“FABTTXImage” src=“210_0001.png” width=480 height=336 usemap=“#210_0001″ border=0 alt=“210.1 Q U I N I E L A tve Jornada 40 22 MAR 2009 1 Getafe C F -Rec Huelva 1 FINAL 2 Sevilla -Valladolid 1 FINAL 3 Barcelona -Málaga 1 FINAL 4 Real Madrid-Almería 1 FINAL 5 Villarreal -Athletic 1 FINAL 6 Mallorca -At. Madrid 1 FINAL 7 Osasuna -Espanyol 1 FINAL 8 Deportivo -Betis X FINAL 9 Numancia -Sp de Gijon 1 FINAL 10 Elche -Hércules X FINAL 11 Murcia -Alavés 1 FINAL 12 R.Sociedad -Girona 1 FINAL 13 Salamanca -Tenerife 2 FINAL 14 Albacete -Huesca 2 FINAL 15 Racing -Valencia 2 FINAL 15 25 55.295,44 12 23,31 14 88 18.850,72 11 3,60 13 3874 285,47 10 1,00 CUENTA NARANJA 3,5% TAE 4 MESES.553″>

Great I thought. I am still playing with my Google maps page, and was already learning quite a lot of javascript with that, so this was a perfect chance to keep learning. I used the PHP include to get all the pages code, and then made a small (java)script that reads the HTML code, then with a few regular expressions, it displays an html table with the live results.

<html><head>
<script type=”text/javascript” src=”jquery-1.3.2.js”></script>
</head>
<body>

<?php Include (“http://www.rtve.es/tve/teletexto/200/210_0001.htm”);?>

<TABLE border =”1″>

<TR><TD>Results</TD></TR>

<script type=”text/javascript”>

var page210, page210New, page210Res;

// get the alt text of the image in a variable

var page210 = document.getElementById(“FABTTXImage”).getAttribute(“alt”);

// remove first part

remove = “210.1 Q U I N I E L A tve Jornada “;

page210noIntro = page210.replace(remove, “”);

// Take date off

page210noYear = page210noIntro.replace(/\d{2}\s\d{2}\s\w{3}\s\d{4}\s/,”");

// should be the line to extract score and status

page210Res = page210noYear.match(/(\s(-|1|X|2)\s(FINAL|-)\s)/g);

// Build the table

// start a for loop to run through the elemnts of the array

for (i=0;i<page210Res.length;i++){

// write a row for each entry

document.write(‘<TR><TD>’ + page210Res[i] + ‘</TD></TR>’)}

</script></TABLE></body></html>

This did the job fine, If you like have a look.

When I tried to import this into excel, all went pear-shaped! Excel could only read the first line of the table and nothing else. AAARRGHHH!!! was my first reaction, until I thought about it. Excel might have problems with client side javascript. Don’t ask me why, but that was my first thought.

(It could have been anything to be honest, maybe even some security setting in Excel!)

Again, I was sat back a bit. The next and last thing I wanted to try, I sto do the same thing completely in PHP. As this is a server-side language, I thought that excel must be able to render its contents.

This is the PHP script I wrote:

$page = “http://www.rtve.es/tve/teletexto/200/210_0001.htm”;

// read from where to where

$start = ‘alt=”210′;

$end = ‘>’;

// open the page

$fp = fopen( $page, ‘r’ );

$cont = “”;

// read the contents

while( !feof( $fp ) ) {

$buf = trim( fgets( $fp, 4096 ) );

$cont .= $buf;

}

// get html contents

preg_match( “/$start(.*)$end/s”, $cont, $match );

// tag contents

$contents = $match[ 1 ];

//Start stripping text
$remove = ‘/.1 Q U I N I E L A tve Jornada /’;
$replacement = ”;
$contents = preg_replace($remove, $replacement, $contents);
$remDate = ‘/\d{2}\s\d{2}\s\w{3}\s\d{4}\s/’;
$contents = preg_replace($remDate, $replacement, $contents);
$addBR = ‘/\s(-|1|X|2)\s(FINAL|-|1ºT|2ºT|DES)\s/’;
preg_match_all($addBR, $contents, $array, PREG_SET_ORDER);
$count = count($array);

echo “<font face=’arial’ size=’8′>”;
echo “<table border=’1′ width=’250′>”;
echo “<tr><td width=’125′><B>Resultado</B></td><td width=’125′><B>Status</B></td></TR>”;
for($i = 0; $i <= $count; $i = $i + 1)
{
echo “<tr><td>{$array[$i][1]}</td><td>{$array[$i][2]}</td></TR>”;
}
echo “</table>”;

I’m sure that I can reduce a lot of code here and merge a few regular expressions. Maybe, Maybe not.

To my big surprise it did. It rendered the table very nicely, without the ugly include I needed with the previous solution. And best of all, I could import the data directly in excel!

Job Done, Mission accomplished. Last weekend was the first test and we both enjoyed watching the “minuto y resultado“  television show, with the excel sheet on the side updating itself every minute, and showing us how each goal influences our bets. Really nice.

(by the way, I play now for over 6 years , and managed to win 20€ once!)

EDIT: I believe that I can automate the import of my bets a little bit more. Maybe a button in firefox, that saves the bets to the text file…. To be continued…

Technorati Tags: Quiniela