Friday 19 February 2010

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.

13 comments:

  1. I've just finished reading through your posts to date. It's all been very interesting so far and I look forward to following along.

    Is your intention to run the bot in Resolver over the long term or just during development before switching to a standalone script once everything is stable?

    ReplyDelete
  2. Well, I suspect I'll have a standalone Python application, talking to a database. I'll probably also hack together a front-end in Resolver.

    But we'll see. I'm currently putting together a slightly overly clever Betfair market object model. This blogging and betting stuff is time consuming :-)

    ReplyDelete
  3. Sounds like a good solution. It would be nice to have the bot running on a remote server but wouldn't want to lose the ease of interaction of a spreadsheet.

    ReplyDelete
  4. Wow, absolutely fantastic blog. I am very glad to have such useful information.
    Thanks For more information visit

    หนังสงคราม

    ReplyDelete
  5. Very helpful suggestions that help in the optimizing website. thank for sharing the link.

    หนังสงคราม

    ReplyDelete
  6. New fun with online casinos.
    Ruby888The online gambling games site will create all the joy and excitement for the players. We have launched online gambling games that are rich. In order to meet every need, each person has the opposite demand. Use the online game to win real money and give you an advantage. All games are available to players at risk without interruption with the service throughout. The choice of entertaining bets is not boring. Online gambling games that users can choose to gamble as you like. Online casino games with sites. And online gambling games that give users a chance to win chance. It is a great way to make money online. If you want to gamble online games that are ready to make you money with gold shortcuts. Whether it is the type that can create fun and benefits to customers. I do not want the chance to gamble online gambling games most of the festive. To make money, you have the advantage to enjoy many pleasures. Play as you like. IBCbet

    ReplyDelete
  7. Thanks for sharing this blog its very informative and useful for us.

    หนังใหม่

    ReplyDelete
  8. BetsAPI is the sports data provider website that also conducts betting on various sports games. It is greatly reliable and enjoyable for batting lovers!

    ReplyDelete
  9. I have to say thanks you for his great idea,which is very important, do you know any other methods.
    ดูบอลสด
    ผลบอลเมื่อคืน
    ผลบอลสด

    ReplyDelete