Making Money Gambling

Many more people lose money gambling than make money. The equation is pretty simple, and makes salutory reading:

Money Gambled

-

Bookmakers' and Betfair's costs

Bookmakers' and Betfair's profits

=

Money Won by Punters

The numbers in the middle are large: Both Ladbrokes and William Hill have huge chains of stores and each has more than 16,000 employees (albeit not all in the fixed odds business). Betfair and the like have large marketing budgets. And there are hundreds of smaller bookmakers - from on-course betting, to the Tote, to Australian and Swedish imports.

There are a host of small businesses that make money off the bookmakers and betting exchanges as suppliers of technology: I might mention Finsoft or Orbis or Betgenius. Plus the spend with technology suppliers like IBM or HP is pretty enormous - I heard Betfair spends something like £80m a year on technology alone.

And then there are the profits made by these businesses, Betfair is rumoured to make hundreds of millions of pounds of pre-tax profit, William Hill and Ladbrokes probably made £100m last year, and even Paddy Power made £50 or so.

Basically, there are a lot of people who are better placed to hoover up the profits from punters' losing bets than you or me.

But that is not to say it is impossible. Gambling is like financial services - where the profits made by investing go first to the investment banks and stock exchanges and traders, and only eventually to the poor punters' saving for their retirements. And like financial services, the rise of technology is democratising gambling. It used to be that punters' could only bet with Ladbrokes or Hills, and had to live with 17% over-rounds. (In other words, you were placing bets at 17% below NAV. You have to be pretty good to make money in that environment.) The only market makers were the bookmakers' themselves. And if you ever did prove yourself to be a successful gambler... well, then bookmakers simply wouldn't take your bets. (I know one professional gambler who would spend the first half of every day cycling around 30 to 40 bookmakers placing small bets in cash to avoid detection.)

The rise of Betfair and odds comparison engines like Oddschecker and Bestbetting has enormously democratised things. (BTW, it's no coincidence that the bookmakers' use Bestbetting themselves - it has a significantly better odds comparison engine than Oddschecker.) The over-round charged on - say - UK Premiership football has fallen from double digits to mid-single. Bookmaker profits have compressed sharply. Volumes have risen commensurately. You want to know why bookmakers push you towards multiples bets: it's because over-round multiplies up. So, a bet on Chelsea to win 1-0, and John Terry to score first, has twice the margin of a bet on Chelsea alone.

So, faced with this brave new world, armed with access to Oddschecker, Betfair and Betdaq - how do you make money?

Well, let us first define the man (for it is usually a man) who will *lose* money. Let us not forget that every penny you make as a gambler will be a penny someone else has lost. He will have a view: someone will have tipped him a horse that he is sure will win. Or he wants to back England in the football because he always backs England at home. Or he's round a mate's house and the rugby is on, and it'll be a bit more interesting if he's got some money on it. Or he has a compulsive gambling habit, and he thinks this one winner he feels really good about...

But one thing dominate's this man's thought process: he wants to back the winner. He is odds insensitive. He'll almost certainly be backing something, rather than laying it.

And, of course, he has no idea what a balanced book is, nor does he know what the NAV of his positions is. He may have - once or twice - made what he considered a 'value bet', a synonym for a long odds one. But the gambler is really after the thrill of betting and racing. He might as well be at the casino, or putting money on the National Lottery.

(Of course, he'll probably not recognise these symptoms in himself. He may boast of his extensive knowledge of the form book. Or he might swear by a certain racing tipster. One thing I can absolutely guarantee: he will have no idea whether he is up or down. He'll say he 'breaks even'.)

And people like this need to lose billions of pounds, to pay for the tens of thousands of employees of bookmakers' and the sport of racing and the advertisements on Sky TV and the shareholders of Betfair. You, semi-professional or merely interested gambler, need to recognise where and how you will make money. And you need to recognise that this is not going to be easy.

In the next post (or the one after that, or the one after that...), I'll run down four broad strategies for making money. None of them are work, or risk, free. But they all offer you a small, real, opportunity to make money. And, hey, with the right tools in your armoury, you might invent the next uberbot.

Automating NAVs

In the previous piece, I showed how to calculate your NAV for a given market. It was theoretical rather than practical: in the real world, you'll want a spreadsheet that looks like this:

If you want to download this sheet, you can get it here. But I'd advise you to build your own. (Actually, I'd advise you develop a more sophisticated one - but this one will do for now.) Now, let us take a typical Betfair market:

Now, copying us across this data to our Excel sheet gives us:

Wow: positive £80.85 - not bad :-) That said, according to the market, there is at least a 45% chance I will lose £177! (This is a classic example of an unbalanced book - and there are trading strategies that seek to, as best as possible, maintain your overall NAV, while balancing your book somewhat. To put in context, a perfectly balanced book with a NAV of £80.85 would have return £80.85 irrespective of the outcome. Sometimes you'll want balanced books, and sometimes you won't.)

But while it's good to be able to generate NAVs, it is pain creating them manually. What we want to be able to do is take what we learnt a few posts ago and automate the procedure. At this point I'm torn: do I continue to use Excel, or do I switch to Resolver? Because I'm lazy, and automating this in Resolver will be clearer (for you) and take less time (for me), I'll make switch.

First things first: I need to recreate the sheet I had in Excel. There is one additional step we should take: right click on first row selector, and choose "Set Header Row":

Now, do the same with selecting the 'A' column, and set the header column. This allows us to reference cells by meaningful names rather than by 'A3', etc. So, we can refer to ['Odds', 'Conservatives'] for example. Once you've used header rows and columns extensively, you'll never go back :-)

Let's do a couple of other things. Let's call the sheet 'NAV'. (In the same way we do in Excel, by double clicking on the Sheet1 tab at the bottom, and changing it to NAV). Then add the following:

The code is pretty self explanatory (and if you don't feel like typing it in, you can download a slightly more sophisticated version of it here, and remember you will need the Betfair libraries from here) - but I'll explain it anyway. The first section ('Pre-constants user code') basically sets up the connection to Betfair, and creates the objects we'll deal with. The grand-daddy of these is the betfairGateway which is our logged in connection. We get other objects - relating to markets or P&Ls - from this parent.

We create two more objects from this gateway object - a market object (betfairMarket) and a P&L object (betfairPL). These objects contain details of runners and prices (betfairMarket) and our positions on the market (betfairPL).

The 'Pre-formula user code' section does the formatting and display of the data in these objects.

row = 2

for selection in betfairMarket.GetRunners():

NAV['Selection', row] = str(selection)

NAV['Odds', row] = selection.Back

row += 1

Firstly, we set the first row that we will copy data into. Then we iterate over the runner objects (GetRunners() returns an array of runner objects).

For each of these selections, we put into the NAV worksheet, in the 'Selection' column, the name of the selection (str(selection)), and then the Back price (selection.Back). We then increment the row number.

for item in betfairPL:

NAV['Outcome', str(item.selectionName)] = item.ifWin

We then iterate over the objects in the betfairPL objects, putting the outcome (item.ifWin) in the appropriate cell. (One of Resolver One's real strength's is the ability to dynamically reference cells by their names.)

for row in NAV.ContentRows:

row['Raw IP'] = 1 / row['Odds']

Now we iterate over each row (ContentRows is rows excluding the Header Row we set earlier), filling in the 'Raw IP' cell with the calculated raw implied percentage.

totalIP = SUM(NAV.Cols['Raw IP'])

Now, we sum the column to see how much we need to deflate individual percentages.

for row in NAV.ContentRows:

row['IP'] = row['Raw IP'] / totalIP

row['NAV'] = row['IP'] * row['Outcome']

This should be pretty obvious now: we're iterating over the rows again, filling in the Implied Percentage (IP) and NAV columns.

rowNum = NAV.MaxRow + 2

NAV[1, rowNum] = 'Total'

NAV.Rows[rowNum].Bold = True

NAV['NAV', rowNum] = SUM(NAV.Cols['NAV'])

Finally, let's add a pretty Total row and calculation at the bottom.

Well - that's it for now. You now have an automated NAV calculator for any given market.

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