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.