Building Our First Bot III - Storing Our Data

(Apologies for the delay in the posting of this… I foolishly decided to move my work PC to Ubuntu. In the process, I discovered GNOME does not handle multiple monitors on multiple graphics cards particularly gracefully.)

Amyway, back to the Betfair ‘bot: it's funny; if I wasn't writing about this as I build it, I'd get out a text editor, hack up some Python, chuck it into Resolver, and see how it did. (With a little bit of testing... probably... to make sure that the idea and the code is not completely hairbrained. Heck, when I wrote my first marketmaking 'bot, that's exactly what I did.)

But when I'm writing for an audience, even a pretty small one, I feel the need to do things at least vaguely properly. No 'magic numbers', relatively well structured code, not using object orientation unless it is strictly necessary.

I'm also starting to think a little about 'bot program structure. And what I've been thinking about is the importance of keeping our own record of bets (aside from Betfair's own one). That is, my spreadsheet should contain a transaction list, and I should be able to find the betIDs of open bets, without querying Betfair.

Why? Well, there are three good reasons:

1. Querying Betfair is expensive. If we do more than a certain number of API calls a month, we'll get charged extra. If our 'bot is low margin (as this first 'bot certainly will be...), this could be a fatal flaw.

2. Querying Betfair is slow. Let's say we want to run this strategy on 200 markets, with 4 selections apiece, then that means we'll be doing close to 1,000 API calls a minute: and that's somewhere between mildly and completely impossible, given how slow the Betfair API can be.

3. We'll want to do analysis of where and why our bot worked, and where and why it didn't. Having easily – machine readable – logs of everything we've done is a clear positive. Yes, we can pull data out of Betfair. But if we don't have to, that's preferable.

So: how to record our activity?

Well: we could use a table/worksheet in Excel or Resolver. But that has a number of problems: it’s difficult to collate across multiple different strategies, markets and selections. It also makes for an often unwieldy and slow spreadsheet. (Spreadsheets aren’t really meant to be used as databases…)

Or we could use flatfiles or self generated XML. But then we’re building a complex interface, and this isn’t really hierarchical data.

No, what we want is a simple (but proper) database of all open and closed transactions, that can be queried extremely quickly and efficiently. Which brings us immediately to the question of which database: mySQL, PostgreSQL, Access, SQL Server or… SQLite.

And it really is no contest: SQLite is the option. Simple, fast to deploy, multi-platform, works with Excel, Resolver, Python, PHP, VBA, .NET, etc. In addition: an SQLite database, sitting in a Dropbox directory, can be used by different instances and computers in different locations, running atop different platforms. Nice.

Now, before we delve into the technical details, let’s ask what we’re going to store in this database:

• key – simple auto-incrementing integer
• betID - integer
• marketID - integer
• selectionID - integer
• amount – integer (we’ll store stuff in pence)
• back – Boolean (if True, we’ve put a back bet into the system, if false, we’ve put a lay)
• open – Boolean (is the bet open, or was it taken)
• cancelled – flag
• odds – float
• strategy – integer (which will be the primary key on a table of strategies. We’ll set this as 1 for now)
• timeopened – time
• timeclosed - time

This is not a complex selection for now; no great need for joins. This is a rapidly searchable data store, not an ERP system. Relationality is for convenience only.

Inside our ‘bot, we’ll have a worksheet that reflects the activity of that strategy in that selection on that market. And it will get this data out through the simple use of SQLite. (Best of all, this datastore will be useful for all your ‘bots going forwards.)

Right: that’s all the theory for this post. Next one, we’ll implement this store.

Off The Shelf Bots

There are an extraordinary number of off-the shelf pieces of software that promise extraordinary trading gains. All you have to do is hand over a small amount of money... load up your Betfair account... and... profit!

I am naturally suspicious of all get rich quick schemes. This blog will not make you rich. It will, I hope, give you the tools that will enable you to write sensible Betfair bots. It will, I hope, improve the quality of your betting, and perhaps your enjoyment of it.

But there are no 'guaranteed winner' Betfair bots out there. As soon as more than one person knows about a strategy, they will ruthlessly exploit it until the gains no longer exist. A year ago there were plenty of opportunities to make money though the arbitrage explained in this post. Now, the opportunities are extremely limited, if they exist at all - because when the smallest gap between bet and lay prices opens up, it is immediately exploited. Two years ago, uberbot sought out thousands of opportunities to make £2 bets at 100/1, which were really 70/1 shots. For an individual, exploiting this was all but impossible. For a bot it was a winning strategy. But that opportunity has been and gone: the uberbot team has had to find other ways to make a turn.

Natural suspicions aside, what's in these 'bots? What does my £25 or $50 or $1,000 buy me? What kind of strategy does it run? Colour me intrigued, if cautious. A little bit of Googling is in order, I think:

Well, first up is the (aptly named) Betfair Bot. This is a "is a brand new Automated Betfair Laying Software which will supercharge your betting activities even while you're away at work or simply want to spend time with your family." There is the obligatory quote about a chap called Adam who "made over £100,000 profit on Betfair from a starting bank of £200 with no knowledge of gambling by trading on Betfair!". Well, I don't know about you, but I'm considering giving up my day job to cash in on this £100,000 bonanza. OK, so this is a system that automatically lays things on Betfair. In other words, it's a super-simplified market making application of some description - I can see that would work. I'll download and review at some point.

Then there is the wonderfully titled Make Money on Betfair, which proudly advertises "the proof is in the video", and claims that the system - in one two hour period - made £200. If all I need (to sell a 'bot) is a video of a making money on Betfair, then I think this should be pretty simple. Even the world's worst system - given vaguely efficient Betfair markets - should occasionally make money, even if only briefly. The site continually harps on about "100% Proof". Sign me up!

And then there is Bet Angel, which has by far the most professional web page. A cursory glance makes it look a pretty useful tool: it seems to introduce a lot of financial world concepts to gambling and Betfair, dressed up into wonderful terms such as a 'riskometer' (which I suspect is a combination of a NAV calculator, with some simple variance calculations). The charts it seems to produce look very Bloomberg - and I wouldn't be surprised if the people involved in this project worked in Financial Services at one point or another. The pricing mechanism also seems very reasonable: I like the way it charges by the month, etc. Most of all, I like the way Bet Angel doesn't promise instant riches: for that reason, I give the first G&T Thumbs Up.

Well: this has been a diversion (caused by lack of access to Betfair from the hotel I'm staying in!) I'll continue with the Betfair 'bot on Monday or Tuesday.

Building Our First Bot - Part II, Framework

Last post, we looked at how out 'bot would make money. This post, we'll put the framework in place. The framework is the outline of the code we'll be using. One of the nice things about this framework is that it is pretty language-independent. With just a little work, it can be converted to pretty much any language: Ruby, VBA, Java, etc. I shall, of course, be using Python - and will probably implement it in Resolver down the road.

The main program loop will look something like this:

Evaluate Status

if No Open or Matched Bets:
 Offer To Back
else if Unmatched Offer to Back:
 See if Conditions for Backing Still Exist
 Move or Cancel Bet if they do not
else if Unmatched Offer to Lay:
 Do Nothing For Now (we're doing this simply, remember)
else if Matched Offer to Back:
 Record the Matched Bet
 Create new Offer to Lay
else if Matched Offer to Lay:
 Record the Matched Bet
 Offer To Back

This is the simplest kind of pseudo-code. Moving it into more real Python gets us this:

import betfair
betfairGateway = betfair.BetfairGateway(username, password, productID)

marketID = xx
selectionID = xx

while not MarketInPlay():

status = EvaluateStatus()

if status == 'No Open or Matched Bets':
  if ConditionsExistToBack():
    OfferToBack()

elif status == 'Unmatched Offer to Back':
  if not MyBestBestBet():
    CancelBets()
  
    if ConditionsExistToBack():
      OfferToBack()

elif status == 'Unmatched Offer to Lay':
  # Do Nothing For Now (we're doing this simply, remember)
  pass

elif status == 'Matched Offer to Back':
  RecordMatchedBackBet()
  OfferToLay()

elif status == 'Matched Offer to Lay':
  RecordMatchedLayBet()
  if ConditionsExistToBack():
    OfferToBack()

Now, while we're not quite at a working, trading 'bot, we have the outline in place. Tomorrow (or in the next few days), I'll fill in the missing methods, and transition the 'bot to Resolver One.

Next week (when I'm back in the UK, with reliable Internet access again...), I'll set it up and running.

Building Our First Bot - Part I, Theory

OK: we've done a lot of theory. Now, let's actually build something that can actually trade. What we're going to do is a very, very simple version of a market-making bot. Why so simple? Because we want to minimise risk during this learning experience, and also because it'll be easier to understand what we're doing. We'll build out this 'bot over the next couple of weeks and months, but this should get you started.

First things first: what's this bot going to do?

Well, it's going to act as a very simple market maker. It will offer liquidity, for a given selection, at a slightly better price than the best price currently offered. Every few minutes it will check whether it is still the best price, and will move it if price change around it. Should our meagre offer be taken, it will jump to the other side of the trade, offering up a price for someone else to take.

Simple? Well, let's explain it using some pictures. A typical Betfair selection looks like this:

Tottenham Hotspur vs Arsenal

	Back	Lay
Tottenham	2.2	2.4
Arsenal	1.5	1.7

Now, for the purposes of our super-simple 'bot, let's simply look at one selection – Tottenham. So, we have an option to back them at 2.2, and to lay them at 2.4.

What out 'bot will do is this: it will offer to back Tottenham with a £2 (the minimum) bet at 2.38.

Tottenham Hotspur vs Arsenal

	Back	Lay
Tottenham	2.2	2.38
Arsenal	1.5	1.7

Now, the market will look like this. (I've highlighted the bet that is ours.) Our 'bot will then monitor the market every 30 seconds or so. If our bet is taken (and nothing else happens), then the market will look like the first figure again. And we will have a profit of £2.76, should Tottenham win.

But we're not interested in whether Tottenham wins or not – our goal is to make money by supplying liquidity to the market. So, what we'll do now is offer to lay Tottenham at 2.22.

Tottenham Hotspur vs Arsenal

	Back	Lay
Tottenham	2.22	2.4
Arsenal	1.5	1.7

Should someone take our bet, then we'll have a (£0.32) profit irrespective of whoever wins.

This will be the simplest Betfair 'bot in the world: it will monitor somewhere between 1 and 100 selections, and will attempt to grab a small chunk of the action by acting as a (very limited) market maker.

But what happens if no-one takes my offered back bet, or if the market moves away from me? Well, no great loss. You can just reset the level of your bet, so that it is inside the best lay price available. The 'bot needs to be smart enough to only put bets on when there is a reasonable (say 3%) gap between the back and lay prices.

And what happens if someone takes my offered back bet, but then no-one takes my offered lay, or the market moves away from me so I am making a guaranteed loss? Well, the 'bot has two possible choices: it can always stay inside the best back price, and so sometimes it will take small losses. Or it can leave the bet in place and hope the market returns. Because the amounts we're playing with are so small, the worst that happens is that we end up with a small uncovered back bet on Tottenham.

This 'bot will work best on markets where the odds move around a lot. And it will work least well on odds which will tend to trend for long-periods in certain directions. It will also work very poorly for in-play markets – not least because it will only be checking on prices every 30 seconds or so. The 'bot needs a reasonably, but not excessively, liquid markets: we need to have a gap between the back and the lay prices, otherwise we're not offering anything useful. (The aim with this 'bot is to get paid for the provision of liquidity.) We'll work on some algorithms that find appropriate markets and selections down the road.

In the second part of this post – which will hopefully be tomorrow, but may be Friday – we'll actually program this 'bot, and get it trading on some real markets.

Four Trading Strategies

There are probably a hundred different, already implemented, trading strategies on Betfair. Someone will have - I'm sure - hooked the Fink Tank football predictor up to Betfair. Some of these will be fiendishly complex, others will simple. Some will make money for a short while, others will remain profitable for a long time, many will never consistently make money.

All trading systems will fall into one of four broad categories:

Algorithms that are better at calculating probabilities than most gamblers. I know of one system that trades only cricket matches, and it relies on the fact that many people over-react when a wicket goes down. Apparently, one wicket falling has less impact than many gamblers expect (in the short run, they over-estimate its importance). By immediately buying the side who has lost the wicket, assuming the odds have swung enough, the system has had significantly positive returns over the last several years. Another - also cricket related - system was based on the fact that people significantly over-estimate the probability of draws. For a long-while this was also pretty profitable - until other gamblers noted that Betfair odds were out of line with history. Each of these systems is based upon a better ability to calculate true odds than most punters.

Arbitrage related algorithms. The system described in this post, was an arbitrage related one. Arbitrage - executed correctly - is riskless profits. The principle is always roughly the same: there will be multiple markets or selections whose results are highly correlated in one way or another - and whose prices should move in lock-step. To take a political example, the Betfair markets on the individual seats should match up pretty accurately to the party seats line (a 50/50 bet on the number of seats individual parties will achieve). Frequently it does not. During the US Primaries, Hillary Clinton was still the favourite, even when the state betting was strongly favouring Barack Obama. There was arbitrage opportunity backing Hillary in the individual States, and backing Barack to be nominee. Some arbitrage happens between Betfair and Betdaq, and/or the spreadbetting firms and/or traditional bookmakers. For example, bookmakers typically give each-way odds based on a simple 1/4 formula. It used to be possible to look for races with strong favourites, back the second or third horses each way with bookmakers, and then sell the win portion of the bet on Betfair. This was enormously profitable - until the bookmakers realised their mistakes.

Flow of Funds Systems. This is what people tend to think of when they think of financial algorithmic trading; it is systems that are plugged into price feeds, monitoring movements, and detecting where prices are going to go. Flow of funds trading is unique because it requires limited information about the underlying market: you don't need to be a horse racing expert, just a maths whizz. The simplest flow of funds trading system will be the one we implement on here first: a simple market making system. Market making involves providing liquidity to a market: i.e., being prepared to take other peoples' bets. In return for this, prices you put into the system include an over-round (that is, the raw implied probabilities add up to more than 100%). If you are able to correctly judge the market so that you get roughly similar numbers of people backing each of the runners, then you will profit.

Informational Advantage Systems. "The most valuable commodity," said Gordon Gecko to Bud Fox, "is information". The first person to see that a cricket wicket has fallen, or a goal scored, is able to adjust prices that bit quicker. (To encourage market making, Betfair has a 5 second delay on bet placing - but this still gives opportunities when a foolish cricket market maker goes on a bathroom break at just the wrong moment.) Given limited resources, it will be extremely hard for you or I to know things other people do not: however, it is possible we will be able to react quicker if we leave the trading in the hands of a 'bot. There are a number of Python Bayesian analysis tools that might be useful to automatically divine of a piece of information is likely to move a market... before human traders can react.

Now, I have never written a bot based around types one or two (although I have made money with manual trading of this type.) I currently run a reasonably successful marketmaking bot that prices up perhaps 20-30 markets, and makes c. £300-400/month. (One of the impetuses behind this blog was that if I wrote about it, I might get around to improving it!) I am very keen to deploy a system based on informational advantage - and after we cover building a market making 'bot, I think we'll go straight onto Bayesian analysis.

So: until next time...

Tabulating Net Asset Values

I've written a lot about generalities. About what works, and how it works. Or more likely, what doesn't work and why it doesn't work.

Now it's time to be a little more practical. There is no more useful thing for a Betfair professional than a dashboard. I find it somewhat bizarre that Betfair doesn't provide one themselves - but nevermind, let's build one. Let's start off really simple: let's allow you to enter a list of MarketIDs, and then the dashboard shows your markets and your NAVs for each one. We'll make it a little bit cleverer and allow you to click on each one to go to direct to the Betfair page.

In other words, we'll build something that looks exactly like this:

You can download this here. But the principle is pretty simple, and the whole sheet is driven by a slghtly improved version of the simple NAV generator discussed here. Let me run you through it:

# Define worksheet names

NAV = workbook['NAV']

This is pretty simple. The worksheet (as you can tell by the tab under it) we want to deal with is called NAV. Normally, to access it, we'd refer to workbook['NAV'], but as that's long-winded, let's create a shortcut.

import betfair

betfairGateway = betfair.BetfairGateway(username.Value, password.Value, productId=productID.Value)

Now, let's import the Betfair libraries, and then create a gateway object. The gateway object is how we interact with Betfair. It initiates the connection, and we can then query it for details on specific markets and bets.

for row in NAV.ContentRows:

Now, one of the great joys about Python (and indeed, Resolver) is the whole iterating process. Here we are asking to iterate over each of the content rows (i.e., the rows excluding the header row at the top). In each iteration, the code will have a row object to play with. You can access the contents of each row by name: so row['marketID'] would contain '100329014' for the first row, etc.

output = RunWorkbook("automated NAV generator.rsl", gatewayObject = betfairGateway, marketID = row['marketID'])

This is where it gets really clever. RunWorkbook allows us to run another workbook, passing it certain parameters. In this case, we're opening up the NAV generator we produced a few posts ago, and we're passing two arguments: firstly we're passing the Betfair Gateway object, so that the workbook doesn't have to generate its own. This is to save on resources (ours and Betfairs!) and to reduce the time needed to run the sheet. Secondly, we're passing the marketID from the row.

To put it another way, we're going through each market listed in the marketID column, and executing the NAV calculating sheet. The completed sheet is stored in the output object, and we could query anything we liked about this sheet. (We could find out the value of Sheet3!A1 by using output['Sheet3'].A1. Cool, huh?)

row['marketName'] = output.marketname

row['NAV'] = output.theNAV

And, indeed, we not pull out two bits of data from the sheet: the matketName that corresponds to the marketID, and the NAV the sheet has calculated. We then put that data into the row object. (Again, we're referencing by name - row['NAV'] means the NAV cell in that particular row.

href = "http://sports.betfair.com/Index.do?mi=%i&ex=1&origin=MRL" % (row['marketID'])

NAV.Cells['marketName', row['marketID']].Href = href

One last cool thing to do: let's create a hyperlink from the name of the market to the Betfair page that holds it.

This whole sheet does one thing reasonably well - it shows the NAVs for all the different marketIDs you feed it. It is not complex, and has no ability to act yet - but it is a useful aide memoir for your betting even at this relatively early stage.

We will enhance this sheet in the coming days and months - adding additional risk metrics, and buttons that allow the system to autimatically bet for you. We'll also get it working *in* a browser, so you won't need to fire up Resolver One, and we'll let it auto-generate the list of marketIDs dependent on what markets you are currently involved in.

'til next time...

Betfair Bots and the Opportunity Out There

I am lucky to know some of the people behind the original Betfair bot: uberbot. At one point uberbot was making more than £100,000 each month in profits - and the computers that ran the system filled a small room. (And generated quite a lot of heat, and some significant electricity bills.) One story I heard, and I don't know if this is true, is that uberbot was accounting for 20 to 30% of Betfair's server capacity at one point.

The success of uberbot has led to a raft of competitors. There are now four to five syndicates running large automated botting (betting, geddit?) networks, with various strategies (automated arbitrage, statistical arbitrage, Duckworth Lewis related algorithms, etc.) And while, as far as I know, these are all nicely profitable, the easy days when three or four people could knock together a system that made them more than £1m a year, while the founders headed off to the pub, are probably over.

Having been involved at the cross-section of the gambling and technology industries for over a decade, I am reasonably au fait with how some of these bots worked. Perhaps taking a single example of a - for a time - very profitable algorithm would be instructive. What I am about to share was once a great secret, known to relatively few people, and which may have generated significant sums. But knowledge spreads, and the more people that know about a strategy, and the more people execute it, the less profitable it will be. This strategy is so simple, and so copyable that BETDAQ themselves run it on their markets! (Personally, I don't think the exchange should be engaged in simple arbitrage, but that's another story.) Many (smart) people who decide to write a Betfair bot stumble across this idea, then spend time implementing it, only to discover they make, alas, no money. So, don't think you can just reimplement this and retire rich!

What's the big idea?

Well, let's start by looking at a simple two outcome market; literally a two horse race. Let's choose some random American Football match: the Miami Dolphins versus the New Orleans Saints. Now, Betfair displays the market like this:

	Back	Lay
Miami Dolphins	2.1	2.4
New Orleans Saints	1.5	1.8

(Regular Betfair bettors will notice I've simplified it - taking out all the non-core elements.)

Focussing just on the top, Miami Dolphins line. I have the choice of betting on the Miami Dolphins to win (i.e., I can back them) at 2.1. This means, if I put £1 down, I get £2.1 back if they win - a profit of £1.1, or lose £1 if the Dolphins lose. Likewise, I can bet on the Miami Dolphins to lose (i.e., I can lay them) at 2.4. When I lay something, I am acting as bookmaker. Somebody else is backing the Miami Dolphins to win. In other words: if they bet £1 and I have laid the Miami Dolphins at 2.4, then I owe them £1.4 if they win. And if the Dolphins lose, then I make a profit of £1.

Now, there is one thing here that is very interesting. A bet on the Miami Dolphins to win is *exactly* the same as a bet on the New Orleans Saints to lose. And backing the Saints is the same as laying the Dolphins.

Got that?

So, if I want to bet on the Dolphins to win, should I take the back Dolphins odds, or should I take the lay Saints odds? Well, let's convert these numbers to raw implied probabilities, to see which one offers the better value.

Miami Dolphins - chance of winning, based on back price = 1 / 2.1 = 47.6%

New Orleans - chance of winning, based on lay price = 1/1.8 = 55.6%

Clear yet? Perhaps not. Let's invert the New Orleans number so it is comparable with the Miami Dolphins win number.

New Orleans - chance of losing, based on lay price = 1 - 55.6% = 44.4%

So, if you want to bet on the Miami Dolphins winning, you can either back them at a 47.6% probability, or at a 44.4% probability. Which is better? Well, given you are paid 100% on win (as opposed to 0% on lose), then you want to bet on the option with the lowest probability. To bet on the the Dolphins, you should lay the New Orleans Saints.

But what has this to do with arbitrage? Well, you can put a new bet in the Betfair system, that will be *better* than the best current back price. And if someone takes your bet, you can they 'lay it off', generating a guaranteed profit. So, we could offer to lay the Miami Dolphins at 2.12. (This would mean that the Betfair odds table would look like this.)

	Back	Lay
Miami Dolphins	2.12	2.4
New Orleans Saints	1.5	1.8

If someone took this bet, we would then bet an equivalent amount on the New Orleans Saints to lose (laying at 1.8), and we'd capture a small but very real profit.

Of course, to do this, you need to be constantly monitoring hundreds of different markets. And if the prices offered changed (so, for example, it was no longer possible to lay the Saints at 1.8), then you'd want to pull your bets. Similarly, you'd need to make sure that as soon as someone took your offered 2.12, you lay it off as soon as possible - otherwise you'd run the risk of someone taking the lay Saints price before you got a chance to.

This system works with multiple selection markets too - although the mathematics start becoming a little more complex. The bad news, though, is that you're unlikely to make any money with it. Because lots and lots of people, with expensive and powerful computer systems, are already running these models, scraping pennies up from markets. The ubiquity of these algorithms can easily be seen by looking at the relative over-round (and under-round) on various events. If the over-round is equal to 1/under-round (or vice-versa) then you have exactly the same take laying the whole market as backing it. (There are very often differences, but the gap is usually so small, it is impossible to benefit because you are forced to bet in certain increments, making exact matches harder than they might be.) This is a characteristic of a market where the differences between back and lay prices have been well and truly arbitraged out. Look at the list below:

(Essentially, any situation where the theoretical opportunity is less than 1% is not going to be exploitable. I am slightly surprised to see that there is an opportunity to make a small amount of money in the Birmingham versus Wolves game. Of course, just because you put a riskless bet into the system, doesn't mean someone is going to take you up on it.)

That this system is - broadly - un-exploitable should not be disheartening. This blog is about giving you the tools to create your own bots, and to understand the interaction of technology and gambling. There are perhaps 1,000 hedge funds with profitable quantitative trading strategies. There is no reason why there cannot be the same opportunities in the betting markets (although the sums involved will be, inevitably rather smaller).

Anyway... till next post...

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.