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.


  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?

  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 :-)

  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.

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


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


  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

  7. Fun casino you should not miss.
    GclubIs a web casino Asia's leading online gambling establishment for more than 10 years, with a modern and attractive service system, makes it easy to gamble online at the casino site. This club has been And is of interest to Asian gamblers. Since online casino site providers are open with honest and transparent services and continue to develop online gaming services, the risk is always online, so it is not uncommon nowadays to play online casino games. It is very popular among Asian gamblers, the bettor can come and meet the atmosphere of the game. These online gambling players just got easier with Wi-Fi only. Bets can also be played at any online casinos on the casino site. Our club. The easy and easy access to online casino games is very popular and when a player has problems during the game, online gamblers can contact our staff at any time. Betting on the web Royal1688

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


  9. casa98asia online gambling We are direct representatives of casa98 website. The most popular service in Thailand. Online gambling All casinos can play casa98 through the web or on mobile phones (Iphone or Android), gambling websites and foreign gambling websites, which have a trustworthy security system as well as international standards. For the online betting through the Internet and it's sports betting online or casinos. Today's Web Available all day. They can be contacted by calling the vocabulary. Chat through the web or online, with professional staff waiting for service throughout the period without any stop. Take time to deposit no more than 5 minutes and withdraw up to 15 minutes with a lot of promotions if you choose. Online gambling with casa98 will require casa98asia fast service.

  10. I have a big family. I live with my mom, my dad, my two sisters, and my brother. Both of my sisters
    are older than me,but my brother is younger. My sister Elizabeth is the oldest. She is sixteen years
    old. Carrie is thirteen 928bet old.We used to share a He helps little kids are sick. room, when
    we moved to our new town,we each got rooms of our own. My brother John is youngest.He is only
    years old. My mom’s name is Sally. She used to be a teacher, but now she stays home to take
    us.My dad’s name is Tim they is a doc.