In my last post I showed how to connect Resolver One to Betfair using a library built by Kamil, one of my colleagues. This was a neat way of getting started, but what I’d like to do is show step by step how you can build your own library to do this kind of thing. There are a couple of reasons for this:

• It’s actually quite interesting to show how you can build a simple web service API like this in IronPython for Resolver One. It’s not at all difficult!
• It demystifies how this connection library works. If, like Robert over at the Gambling and Technology blog, you’re intending to risk not-insignificant amounts of money with your gambling spreadsheets, it’s a good idea to understand as much of the technology as you reasonably can. As I said, the Web Service library is easy, so it’s worth knowing!
• Betfair often update and improve their APIs to support new features. If you’re using these libraries to connect to them, and you want to use a new feature we’ve not added support for yet, it’s good if there’s some well-documented way for you to add the feature yourself.

So, in this post I’ll describe the first steps you can take in building your own library. I’m targetting people with some programming and general Windows knowledge and an interest in Resolver One, but little more: if you know how to update your path and know one end of a for loop from the other, then you’ll be fine :-)

So, without further ado, let’s get started! A bit of background first. In order to connect to Betfair, we use their Web Service API. A web service is a way for computers to pass information back and forth using web technology; they package the information up in the XML format at each end and just send it along over HTTP. An API is an Application Programming Interface, which is just a general term for a set of functions provided by one system (like Betfair) to another (like things you’re writing). So in order to get, say, a list of all open betting markets on Betfair, we want to be able to send them an XML message saying “list all markets”. They will respond with a XML-formatted list of markets, or an error, equally XML-formatted.

As anyone who’s ever had to do it will attest, writing code to generate and parse XML is boring work. So, like pretty much all web services, Betfair supply machine-readable documentation of the kind of requests they will accept, and what kind of responses they will return, in the aptly-named Web Service Description Language (WSDL, normally pronounced “wiz-dull”). We can then use standard tools to generate code from those WSDL files to do all of the XML stuff, saving us a lot of time.

To get the WSDL files for Betfair’s web service API, you need to go to Betfair’s site. As of this writing, the best way to do that is to go to http://bdp.betfair.com/, click on the link saying “Quick-Start Guide – Sports” (which will take you to here), and scroll down to where it says “Sports API 6″. You’ll see three links, the Sports API (Global Services) WSDL, the Sports API 6 (UK Exchange Services) WSDL, and the Sports API 6 (Aus. Exchange Services) WSDL. If you’re in Australia, you need the first one and the last; if you’re elsewhere, you need the first two. (I’ll explain why that is later on.)

Once you have the WSDL files, you can open them up in a text editor — they’re not particularly readable, which is understandable given that they’re meant to be compiled into code to create and parse the XML that Betfair’s web service expects. So, how do we convert WSDL to executable XML-parsing code? We use a WSDL compiler! Conveniently, Microsoft provide a free one as part of their .NET SDK. Download the SDK from here, and install it. Once it’s done, if you look in C:\Program Files\Microsoft.NET\SDK\v2.0\Bin, you should see a file called wsdl.exe. Add that directory to your path, and then pop up a command prompt window.

If you’re using Vista and aren’t running as the system administrator, you’ll have to start an administrator command prompt, as — for no obvious reason — the WSDL compiler insists on running as administrator. To do this, start a normal command prompt and then type runas /user:Administrator cmd

In your new command prompt window, run the WSDL compiler like this:

wsdl /namespace:Betfair BFGlobalService.wsdl BFExchangeService.wsdl

This tells it to load up the two WSDL files you’ve downloaded, and to generate the appropriate code, with a .NET namespace (which we’ll use later on) of Betfair.

Now, the WDSL compiler produces the code as a C# file; you should see it in your directory as BFGlobalService.cs. This is a useful intermediate step, but what we actually want to do is call the generated code from an IronPython environment like Resolver One. So, we’ll need to be able to compile C# into a .NET DLL. A basic command-line C# compiler called csc.exe comes as part of the .NET 2.0 toolkit; you’ll find it somewhere like C:\Windows\Microsoft.NET\Framework\v2.0.50727 — the exact number at the end of the directory will depend on the versions of .NET you have installed. So, you need to make sure that that directory is on your path too, then in a command prompt, run

csc /target:library /out:betfair.dll BFGlobalService.cs

If all goes well, you should see a file called betfair.dll appear. Congratulations! You’ve just built a library that can connect to a web service!

Now, the pre-built library we used to connect Resolver One to Betfair in the previous post had some IronPython code wrapping around the raw .NET WSDL DLL, and that provided us with some useful extra functionality. But there’s nothing stopping us from connecting directly to Betfair using just the DLL that we’ve just created. To do this, start up Resolver One, create and save a new file called, say, test-raw.rsl inside the same directory as betfair.dll.

The first step to using our DLL is to load it up. Add the following code to the pre-constants user code:

import clr
clr.AddReference("betfair")
from Betfair import *

Hit F9 to run the code, and you should see it just recalculate with no errors.

The next thing we want to do is to create some objects that will represent the connection to the Betfair servers. Add this code:

globalService = BFGlobalService()
exchangeService = BFExchangeService()

Our Betfair connection is represented by two objects rather than one because their API is split in two; one part is used for basic functionality like logging in and out, and the other is used to actually get market information and place bets. The reason for this split is regulatory; the process of logging in and out is handled by Betfair’s central computer systems, but for their clients in Australia, any actual betting needs to be done using computers that are physically located in Australia. Betfair handle this by having a service for the globally-handled stuff, and then different “exchange” services for the stuff that’s split between Australian and non-Australian. This is, of course, why Australian users had to download a different WSDL file; the functionality in there is exactly the same, but it specifies different physical servers to communicate with.

So, now we’ve connected to Betfair, the next step is to log in:

request = LoginReq(username="your-user-name", password="your-password", productId=82)
response = globalService.login(request)
if response.errorCode != LoginErrorEnum.OK:
    print "Login failed"
    raise Exception("Failure")
print "Login successful"
sessionToken = response.header.sessionToken

So, what’s happening here? We’re creating a login request object, with the appropriate username and password. The productID tells Betfair that we want to use their free API. We send the request off to their servers using the global service object, and then check the response. If it failed, we print an error and stop. Otherwise, we print out an appropriate message, and store away a session token. This is a special number assigned to us by the Betfair servers, which we need to pass along with all of our future requests as proof that we’re a user who has safely logged in to the service.

Now that we’re logged in let’s prove that we can do what we did last time: get all of the markets from Betfair:

request = GetAllMarketsReq()
request.header = APIRequestHeader(sessionToken=sessionToken)
response = exchangeService.getAllMarkets(request)
print "Response from getting list of markets", response.errorCode, response.header.errorCode
print "Market data", response.marketData

This is a similar pattern to the login call; we create the request object specifying what we want to do, send it, and look at the response. One difference is that we have to pass along the session token we got when we originally logged in, but apart from that it’s pretty much the same.

So, add that code, hit F9, and take a look at the output pane: you should see the messages signaling a login scroll past, and then a load of stuff that looks a bit like this:

2010~/26397698/26558634/101151471~0~1~~1266427038600~8~1~0.0~N~N:101151472~Mad Dingo Downs 17~O~ACTIVE~1266460030000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151472~0~1~~1266427038600~8~1~0.0~N~N:101151473~Wombat Bay 18~O~ACTIVE~1266461110000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151473~0~1~~1266427038600~8~1~0.0~N~N:101151474~Mad Dingo Downs 18~O~ACTIVE~1266461470000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151474~0~1~~1266427038600~8~1~0.0~N~N:101151475~Pacific Park 19~O~ACTIVE~1266461830000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151475~0~1~~1266427038600~8~1~0.0~N~N:101151476~Pacific Park 20~O~ACTIVE~1266464350000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151476~0~1~~1266427038600~8~1~0.0~N~N:101151477~Mad Dingo Downs 19~O~ACTIVE~1266466870000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151477~0~1~~1266427038600~8~1~0.0~N~N:101151478~Wombat Bay 19~O~ACTIVE~1266467230000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151478~0~1~~1266427038600~8~1~0.0~N~N:101151479~Pacific Park 21~O~ACTIVE~1266467950000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151479~0~1~~1266427038600~8~1~0.0~N~N

Take close look at it; it looks like nonsense, but it’s actually Betfair’s internal representation of a large number of markets, separated by colons. So, for example, the last market is this:

101151479~Pacific Park 21~O~ACTIVE~1266467950000~\Horse Racing - Virtual\18-Feb-2010~/26397698/26558634/101151479~0~1~~1266427038600~8~1~0.0~N~N

This line describes one of the Betfair markets in terms of a number of fields, separated by tilde (“~”) characters. The first is an ID, the next the name, and so on. If you scroll up in the output pane, you’ll see that there are a pretty vast number of them!

Now, this is a brilliant proof of concept — with almost no code, we’ve created something that can download a list of betting markets from Betfair. But we don’t really want to have to build a complex spreadsheet with tilde- and colon-handling code everywhere. The next step is clearly to start parsing the output from the Betfair API — and in my next post, that’s what I’ll show you how to do.

In order to build spreadsheets that make bets, the obvious first requirement is to connect it to a betting exchange. I decided on Betfair, as it’s the most popular exchange in the UK. They have an API that one can use to write programs that interact with their exchange, and you can sign up to use it here. When you sign up, you can either get free access to somewhat limited data (with no technical support), or paid-for access with support and no limitations. Free was good enough for my purposes, so that’s what I went for!

The next step is a bit trickier. In order to communicate with Betfair from Resolver One, you obviously need some kind of code that knows how to talk to Betfair’s servers. There are WSDL files on Betfair’s API pages to help accomplish this. WSDL is a language that allows suppliers of Web-based APIs to specify how other computers should connect to them; at some later stage I’ll describe all of the steps necessary to take these files and use them to create code to connect Resolver One to Betfair, but for this first post we can use some code that my colleague Kamil prepared earlier :-)

Here’s a screenshot of a really simple test spreadsheet running:

If you have a Betfair account, you can download the test spreadsheet and try it out yourself. (Betfair isn’t licensed to operate in all countries — a notable exception is the US — so you may not be able to sign up with them. Check their site for details.)

The file on the Resolver Exchange is a ZIP; unpack it somewhere, and look inside: you’ll see four files:

1. betfair.dll. This is interface code that knows how to talk to Betfair’s servers, and was automatically generated from the WSDL.
2. betfair.py. This is code that Kamil wrote that provides a nice Python-style interface to the code in the .dll file. (The auto-generated stuff can be a bit of a pain to use.)
3. lib.py. This is some extra utility code used by betfair.py.
4. test1.rsl. This is the important one: a Resolver One file that uses all of the others to connect to Betfair.

If you have a Betfair account (and, of course, Resolver One!), try opening the RSL file. You will see two yellow cells near the top; the rest of the sheet will show errors. You need to enter your Betfair username and password into the yellow cells, and you should then see the errors disappear, and a list of some of Betfair’s betting markets appear from row 10 downwards.

Let’s take a quick look at the formulae. The first interesting cell is B4, which contains this:

=betfair.BetfairGateway(betfairUsername, betfairPassword)

betfairUsername and betfairPassword are just named cells (referencing B1 and B2 respectively as of this writing), and contain exactly what you’d expect. betfair is the Python module defined in betfair.py. This is imported from the spreadsheet’s pre-constants user code. The formula as a whole creates a new BetfairGateway object, whose purpose (as you’d expect) is to handle our interactions with Betfair.

Let’s move on to B6, which contains this:

=dir(B4)

One nice thing about Python is that if you don’t know what methods an object supports, you can easily find out. This cell just shows the list of methods on the BetfairGateway object.

Moving on to B8, we find this:

=B4.GetMarkets()

So, we call the GetMarkets method on the BetfairGateway. This returns a generator object, which we could access repeatedly to find out all of the markets available to us on Betfair.

As we only want a quick list of the first few dozen markets, what I did was enter the formula =B8 into cell B10, selected the range B2:I32 and then used the Unpack feature (from the tools menu) to tell Resolver One to expand the generator into that range. You can see the result: the first few dozen markets appear in the cell range.

So, so far I’ve connected to Betfair from Resolver One and got a list of all of the available markets, the displayed the first few. I’ll stop at that for this post; next time I’ll start looking at the markets and seeing what we can do with them.