Getting Prices from Betfair

Betfair uses a web services system to allow people to query prices and even place bets (although we'll cover that part later). What this means is that your program opens a special web page that returns an XML formatted response. With appropriate wrappers we can easily get useful information into our programs, and make trading decisions based on that.

Now, we don't need to worry too much about the technical details right now, beyond a few important points. Firstly, this is a 'pull' service. Your application queries the Betfair servers - so you never have a truly live price, you only have the price that was valid last time you read the page. Betfair will not tell you that a bet of yours has been taken, or that the price has moved away from you. Therefore, in fast moving markets, you'll want to have your scripts poll the server every 15 to 30 seconds. For slower markets, like UK election constituency betting, you can probably get away with requests every 20 minutes or so. Secondly, if you are going to poll the server regularly (or, indeed, automatically trade), then you will need to spend 200GBP/month for a more expensive Betfair API account.

With this is mind, there are two ways to get prices from Betfair: the hard way and the easy way. The hard way involves writing our own libraries that interact with Betfair's servers. The easy way means taking off the shelf libraries, and using them instead. For now, we'll take the former approach - although in a later piece, well probably do something a ittle more sophisticated.

If you want to use Excel, then let's start with the example spreadsheet from Betfair, which can be downloaded from here. Alternatively, for Resolver you'll need the Betfair libraries which can be got here. I haven't used the Python Betfair library (http://code.google.com/p/pythonbetfairlibrary/), but they might also be worth looking at.

One final bit of background, before we pull out the prices. Betfair divides the world into markets and selections (or runners). So, the 3:30 at Kempton Park might have a two different markets: winner and placed. Each of these markets has its own ID: in Betfair terminology this is the marketID. Inside each market, each selection - whether the horse Dancing Brave or Machester United or John Terry or The Conservative Party - has its own code, the selectionID.

The easiest way to get the marketID (although not the way you'll use in the longer term) is to login to the Betfair web site, navigate to the appropriate market, and then click on the rules tab on the right. You now get a screen like this:

The bit circled in red is the marketID.

(A lot of the information in here is gleaned from the - soon to be an excellent resource I'm sure - Betting on Resolver One blog.)

Right, let's get started. From Resolver One, you need to put the Betfair libraries you downloaded above in the same directory where you're going to save files. Now, in the 'Pre-constants user code' section of the code box, enter the following:

import betfair

betfairGateway = betfair.BetfairGateway('yourusername', 'yourpassword', productId=82)

(This imports the Betfair library, then creates a connection to Betfair using your username and password. The productId of 82 tells Betfair we're using the free API.)

Now hit F9 to recalculate. If you don't get any errors, and an 'OK' is printed in the status window at the bottom - well, congratulations, you've just programatically connected to Betfair. Let's be a bit more sophisticated. Add the following (below the code above):

betfairMarket = betfairGateway.GetMarketById(2725554)

print betfairMarket.GetRunners()

(This creates an object that contains details of a single Betfair market, and then prints the list of runners.)

Hit F9 again... and now, at the bottom of the screen, you see the three 'runners' in the UK General Election Most Seats market: [Conservatives, Labour, Liberal Democrats].

(If you're not - yet - a Python guru, this is a Python list. Python does lists, arrays and dictionaries very well. Once you've used Python, even just a little bit, you will never return to VBA or PHP or Java ever again...) The screen below shows what you should see:

Let's get a little more creative, and get that pricing data out. Delete the line printing the runners, and add the following instead:

for selection in betfairMarket.GetRunners():

print selection.Name, selection.Back, selection.Lay

Hit F9 to recalculate again, and now you'll see something like this:

OK

OK

OK

Conservatives 1.13 1.14

Labour 8.2 8.6

Liberal Democrats 220.0 310.0

The three 'OK's are just the library printing out status messages. The rest is the important bit. Now, pat yourself on the back - you've gotten prices and runners/selections into Resolver. Of course, you'll still want to get them into the grid. But we'll cover that in a future posting.

Let's now do this in Excel.

The good news with Excel is that getting a grid with prices in is easy. I'm assuming you're running Excel 2007. Running the Betfair Excel spreadsheet requires two dependancies to be installed: Microsoft SOAP toolkit 3.0 and Microsoft XML Library (Service Pack 2). You can get them here and here.

Now, restart Excel, and load the spreadsheet you downloaded from Betfair. Your screen should look something like this:

Enter your username and passwords in the appropriate cells, and change the product ID from 0 to 82. (82 is the number for the free, read-only, API.)

Now, click on 'Select Market' on the right. A dialog will open. You can now choose a market; let's choose 'Politics', 'UK', 'Next General Election', 'Most Seats'.

A new tab opens - this gives you the prices of all the selections. In fact, it gives you a rather attractive screen with all the prices and amounts available. A lot of your work is already done.

However, this spreadsheet also demonstrates the disadvantages of the Betfair Excel spreadsheet. We have a nice, pretty display of the prices available. But manipulating these, or indeed, showing multiple selections across different markets, is hard. The Python code we produced was simple. Using the Resolver One grid to display things is pretty simple. It's the best of both worlds. Don't worry Excel gurus - we'll be doing plenty of stuff in Excel over the course of these blog posts. We'll delve into VBA, and write complex multi-line formulae. But for education and edification, we need to put together clean and readable examples. And we probably need to do things from the ground up, rather than relying on what the Betfair developers think we need. (It would also be nice to put together a clean library that allows seamless access to Betfair, Betdaq and other providers of gambling liquidity.) And that's why a lot of what we do will be in Resolver One and Python.

Till next post...