Friday 13 April 2012

Accessing information

I've had a couple of people ask me about using Microsoft Access to record trading since my post mentioning record keeping.

It's taken me a long time to get round to recording my trades...despite the fact that I'm computer literate enough to have done so from day one. At the moment the financial side of my trading is recorded on an Excel spreadsheet as I would guess most do.

In lots of ways Excel is the ideal tool to accomplish this, but, in my opinion is not without its problems. A total of say 100 trades is manageable, but what about a thousand? Or a couple of  hundred each in three or four different sports? Pretty quickly the sheer real estate such a spreadsheet uses on your screen can become unwieldy, and any summary sheets you compile can quickly become a spaghetti junction of references to named cell ranges, or worse 'A35:A35678'. The problem escalates when you think to yourself 'It would be useful to record.. (insert whatever data you would like recorded) ' and then summarise that.

Access makes storing diverse sets of information and pulling together detailed summaries arranged as you wish, (week by week, sport by sport, start odds by profit being some examples) if not exactly simple then at least achievable with a bit of thought and planning.

The way in which the programme stores data is similar to Excel in that the raw data is stored in what looks very much like an Excel worksheet. Where Access comes into its own is that it stores data using what is known as a 'relational model'. To give a simple example of this,  your Excel spreadsheet might have three different tabs, storing exactly the same sort of data for three different sports whereas the same data in Access is stored in two individual tables - one for the trades themselves and one for the sports. These two tables are then 'related' to each other under the hood and allow for relatively easy accessibility to the answers to all sorts of questions.

To cut a long story short I have decided to build an Access database to record my trading - both the note keeping info mentioned in my previous post, and the financial data as well. If there is enough interest I'll happily share the nuts and bolts of building such a beast on the proviso that anyone working through it with me understands that I am NOT a software professional and that the database purists will probably recoil in horror at what they see.

Let me know if you are interested and if there's  a decent number I'll give it a go. At the very least two things will result..

1) You might come out with something useful that you can tailor to your own needs.

And the big one...

2) I won't be short of blog copy for a while :-)

6 comments:

  1. Good luck with that Dave. I'd be interested for sure. Hope you are keeping well?

    ReplyDelete
  2. Good thanks mate-busy as ever but getting some golf in once a week at last. You played this year yet?

    ReplyDelete
    Replies
    1. Once, and incredibly badly at that! My left knee is giving me considerable jip at the moment, an injury from long ago exacerbated by carrying too much weight! I am basically swinging shoulders only at the moment and not generating any real power plus the walking isn't that easy. Funnily enough I discovered a spring in my step walking up the nineteenth!

      Delete
  3. I bet you did mate! One day, one day we will have to meet up for a round. Golf that is!

    ReplyDelete
  4. Would be very interested in that Acess know-how ;)

    ReplyDelete