Balancing the books

Fed asks whether we will be able to track bank withdrawals when we get to that stage of the journey, and I thought that as a break from the practical we could have a look at the theoretical for a while.

A bank balance, ultimately, is the sum of a column of numbers, some positive, some negative. That, in itself, is meat and drink for a programme like Access or Excel, but the approach in the two programmes is different.

I've got to say in this particular instance it probably makes much more sense to keep a running balance in the spreadsheet. One way you could achieve this is with four columns, a date, a balance bought forward, a credit / debit and a balance carried forward. The only other thing to consider is the start balance, but that is easily overcome by placing that figure in the original Balance C/F column. If we wish to know what the start balance was on 15th April 2012 we just look for that cell. If we want to make a graph of end of day balances from 1/3/12 to 31/3/12 that also is relatively simple.

Moving over to Access, if all we want to do at any time is to know how much is in the betting bank at that time, that's a simple thing to do. If, however, we want to be able to see the bank account balance over say a month's period then that becomes a little more tricky and there are a number of ways you might approach it.

You might well think that we could just replicate the four columns I described above into an Access table, and you'd be right. However the two Balance columns are calculated values, whereas the Date and Credit/Debit figures are hard entities. Good practice in database design and implementation requires calculated fields not to be stored as part of the data - the reason - as far as I know - relating once again to storage space. Having said that I note that Access 2010 apparently now does include the capability to store calculated fields in a table but I've not explored any limitations to this. We could use this route but I'm not going to!

If you stop to think for a second, we are already recording the Profit / Loss from our trading activities in the Trades table. In my opinion this is absolutely the right place to do this... the P/L is independent of last night's balance in a data sense (obviously there is a dependency - if last night's balance was £2.50 today's P/L will not be brilliant unless we can lay a 1.01 shot for £2.50 liability AND win it!). The PL is completely dependent on the trade and the stake used. Let's face it, the main thrust of this exercise is to track our trading activity rather than track a betting bank balance. The point is that the P/L shows the trading debits and credits to the betting bank, leaving us to worry about only the Deposits and Withdrawals from said bank. Also note that by recording losing trades as negative P/L figures we don't have to worry about our stakes in a bank sense.

For this model to work we could create a Bank table, with a date column, a credit /debit amount and an ID to track it. The opening bank balance would be deposit #1. From here it is extremely simple to calculate the current bank balance.... Sum of PL from the trades table PLUS sum of Dep/Cred from the Bank table. Easy.

How, though, to find the balance at the end of 15th April, 2012?? It's actually quite easy to work this out logically... we need the balance at the end of April 14th plus any P/L and Cred/Deb on the great day itself.

What about the data needed to build a graph showing the effects of the slings and arrows of outrageous fortune on our bank balance for the month of March, 2012? This is a little more tricky. The balance on the 29th February is esay enough to compute, as is that for the 1st April. Subtracting one from t'other shows how much the bank has grown or shrunk in that month. But we would then need to know how that actually happened day by day....and hence to find some way to represent that graphically. I'm on the case!

I'm conscious that this post hasn't really advanced our cause much, but I think it's worth lies in pointing out that stopping to think about the kind of information you need, before jumping in with both size 12's will help get the design of your db right from the outset.

I'll leave you with another thought.... your current balance is available to you on the Betfair site, and on any software that you might use. Do you really need your Access DB to tell you as well? Or do you just want to know at a glance what your total number of trades is, your total stakes, p/l, winners and losers? Your ROI and your Strike rate?

If the answer is the latter... we've already got it - you just don't know how to extract and present it yet!

You might run a separate bank in your own mind for trading a particular sport or strategy, but one which is 'witihin' your normal account so to speak. As you play around with Access and read on the web about how to query it I'm sure you'd be able to come up with something suitable in the not too distant future to handle three, four or more banks in that way. There's a challenge for you!