Net Asset Values

When you do a lot of gambling, it is useful to know whether you are up, or down, in a market. Because individual markets can be complex, gamblers tend to talk about whether they are 'in the green' - i.e. whether they are profitable on a market, irrespective of the outcome. But this is simplistic - what you need to be able to do is to mark you positions to market, or, as I prefer to call it, generate a Net Asset Value or NAV.

This is a fairly simple concept: but understanding whether certain positions will be positive or negative to your NAV will be a core component for building trading systems. (Now, the NAV model we're building here is pretty simple - and later we will bring in more complex elements, adjusting for commission and the like - but the concept of the NAV will help you score your trading, and build better systems in the future.)

OK. Let's say there is a market for a football match: say England versus Germany. There are three possible selections (outcomes): England, Germany, and Draw. The current odds for each are:

England: 2.36
Germany: 3.05
Draw: 3.35

And let us assume that - in the event of an England win, we make £23; if Germany wins, we lose £15, and if it's a draw we lose £3. So - are we up or done on this market? Well, the simplistic answer is - we're up if England wins, and down otherwise! But that's not the right way to look at this. We need to convert those odds into percentage probabilities, and then weight the outcomes.

Each odd is - in effect - a measure of probability. If the odds on England are 2.36, that means the (unadjusted) probability is 42.4%. (We get the probability by dividing 1 by the decimal odds. So England is 1 / 2.36 = 0.424 = 42.4%.) So the probabilities the odds are telling us are:

England: 42.4%
Germany: 32.8%
Draw: 29.9%
---
Total: 105.0%

Now, obviously, the true probabilities have to add up to 100% - so we need to divide every probibility by 105% to get a total that adds up to 100%. This tells us exactly what probabilities the gambling markets expect:

England: 40.4%

Germany: 31.2%

Draw: 28.4%

---

Total: 100.0%

To see our expected outcomes, we need to multiply those percentages by the amounts we win on each outcome:

England: 40.4% * £23 = £9.28

Germany: 31.2% * £-15 = £-4.68

Draw: 28.4% * £-3 = £-0.85

---

NAV: £3.74

If we have a positive NAV on a market, we have a positive likely outcome. Of course, things get a little more complex with the concepts of balanced books, and the like. But if you want to 'score' your positions now, you have no better tool in your armoury than the NAV.

In our next post, we'll draw together NAVs with the ability to get prices out of Betfair, so you'll be able to see - on a market by market basis - where you're winning, and where you're losing.

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...

First Post

Let me tell you what this blog is about - it's about linking up programming, spreadsheets and gambling sites like Betfair, Betdaq and other sites where there is a public API. The hope is that I'll develop some automated trading application that will make me some money, and that I'll learn something.

There are three automated trading applications I am keen to put together: (1) a simple automated market making application for relatively illiquid markets. Seeded with basically OK probabilities, it will attempt to maintain a balanced book. In particular, this will be used for the UK general election, where current volumes are modest, and there is little in the way of inside information. (2) something that uses Bayesian analysis to autotrade certain markets. The idea will be automatically 'train' a system to recognise stories (off an RSS feed) that should affect prices, and then get there ahead of human traders. In financial services, such systems already exist; I haven't seen anything like that in gambling. (3) A system that relies on a degree of mean reversion - so when prices move too quickly, particularly on just a single selection in a market, it supplies a small amount of liquidity, in the hope that it will take a small cut when prices stabilise. I may also do some cross-pricing/arbitrage between Betfair and Betdaq and the bookies - although I suspect anything that simple will have been already comprehensively done by others.

What I'm not going to do is to apply any specific sporting knowledge or algorithms. I know plenty of professional gamblers that have their own proprietary cricket or football pricing models. I can't match that.

As far as tools, I'll be mostly using Excel and Resolver One. The former because it is the de facto spreadsheet that everyone uses. The latter because it is much more programmable, and because Python is a much better language than VBA. (Resolver isn't much used by individuals, but is very popular with traders and quantitative analysts in the City. It's also only $99 for individual use - which makes it rather better value than Excel.)

Next post: getting simple prices out of Betfair...