Spreadsheet logging - thoughts on entry

[fairfranco]

I know there's a few spreadsheet threads recently but I thought maybe we could have some discussions about best ways to log certain things.

I've recorded my bets for a while but have tended to have been a bit slack and directionless meaning it's really not had any value. it's ok listing all your bets with profit loss but if you can't easily get information that will help you improve your bets then it's really not worthwhile.

For example previously I had a sheet for horse racing of a particular system and another for all other sports, I didn't categorise very well so really it would be tough to work out if say my Football unders/overs were doing well or anything else!

Now i'm setting up a new workbook where i'm going to set different sheets for different sports so that I can put in columns for different market types etc and have validation drop down boxes to ensure things match.

What I wanted from this thread was for people to discuss what exactly they feel is worthwhile and what is just adding to the already heavy data input! Also how people look at their results, how they break down bets and results etc.


---

On that note my first query is this:

I quite often make bets both prematch and in play and may back a selection (especially in Cricket) more than once as the price in my view gains value. In this case would you lump together your bets or seperate them?

I think putting them together makes sense but what I was thinking was it might be worth seperating pre match bets from the in play ones as this would allow me to spot how successful my in play bets are, as well as weather my prematch predictions worked out well etc.

Once concern, would this then make entering Lay trades through both bets in play a bit confusing as you decide which relates to which bet?

What do people think on this?

i'll have many more i'm sure as I work on this spreadsheet but would be keen to hear peoples thoughts, questions and suggestions on what is often an overlooked or appreciated part of improving betting profits.

[LLAAYYSS]

Just use subtotal function a top of each column thats applicable to calc lsp. p&l, sr , roi etc, put you filters on and bobs your uncle.

So much easier and flexible as can really drill down then into things to spot strengths and weaknesses.

[23RedRaider]

Hey fairfranco,

Excel is a great tool and it's what I use for all my betting / systems. Obviously a database would be a preferred option, but that's asking for a little more time and effort to setup.

Pivot tables are a great way for you to manipulate your data and quickly see how things are and you can spin the data however you want.

I'd be happy to assist you with any of that sort of stuff / look over your spreadsheet if you like.

[Betfairalfie]

FF - I have found in the past that you often collect more information than you need. So its probably a good idea to think of what analysis you might actually do and then from that work out what you need to collect.

From the example you gave you'd probably want to analyse pre-event and in-play performance seperately. You might also want to find out if those additional in-play backs (on top of the pre-event back) were worthwhile.

So list them seperately ?

Currently I have 4 sheets in a workbook. One for pre-event trading (high turnover), the other is for in-play trading (small turnover) and the final one is for punting (very small turnover) and the 4th sheet is a daily summary of results.

So if I wanted to pre-event trade a horse and also let some run for an in-play trade then it would be entered on two sheets.

Hope that helps.

[fairfranco]

cheers alfie that's useful.

I know most of your trading is on horses but if you trade different events do they all go into those same sheets? Presumably with a category to seperate them?

For football and cricket as i'm "punt trading in play" I think it would be valuable to make sure I have information on what markets they are easily to hand.

on the horses I think i'm really only interested in the off time, and the time backed and layed so that I can see how tight the trade was how early i backed etc. Also the SP would be of use to give an indication the price movement after I traded.

I think I need to get a pad and pen out and do some designing!

[Betfairalfie]

yes I have an identifier so I can quickly determine profitability of each sport. Thats why i don't bet much on football with the real stuff

As you know my pre-match soccer trading career ended when I tried to second guess Rafa's team selection one time

Other suggestions:

Don't forget to add a target achieved figure (say 1% per trade) and maybe a conditional format for the whole line so that if you hit your profit target on that bet/trade the whole line goes green.

Also, a graph helps. Slowly slowly catchee monkey

Finally colour your tabs and have a matching colour for the header row so that you know you are in the right sheet in your workbook.

[fairfranco]

new question, penickety but it's good to be accurate.

when you back and Lay at serveral prices and enter it as one bet on the spreadsheet you are putting in average back and Lay prices which it shows in Betfair P/L.

Fair enough but this doesn't always give a totally accurate figure of your profit/loss and means you end up with your spreadsheet being out on both the comission charges and the P/L in general.

For those that record, do you take take this on the chin and accept that it's not toally accurate but not far out, play with the odds to 3rd/4th decimal place to get it right (tedious), manually change it (makes the whole formula thing a bit of a waste of time!) or do something else?

quick example

Backed Delhi 90 @ 1.75 (average)
Layed Delhi 109 @ 1.45 (average)

Profit pre comission = 19.38
comission = 0.87 (4.49%)

Excel with the same figures says:

Profit = 18.45
com = 0.83

strangely with this one it was quite a way out and 1.76 made it closer but still 0.03 out before comission.

[Betfairalfie]

I'd suggest you overwrite the figures calculated by your sheet with the actual figures from Betfair. Its important that your sheet balances with real life P/L.

For the odds I would record the average shown by Betfair (which I think does not take into account relative stakes when working out average odds) and maybe in a text field explain what stake/odds you took and why you did it twice.

Personally I very rarely back or Lay the same thing twice, unless of course I am trying to get a largish bet matched and the odds start tumbling and I go down a few points in order to get stake fully matched.

[thatsa donkey]

ff,

If it is not very many transactions could you not delete the downloaded Betfair data for that particular event and manually enter the actual figures as shown in your Betfair Account Summary which shows each back/Lay and actual amounts and odds?

Donkey.

[fairfranco]

i've considered that donkey as it does seem like a sensible and obviously accurate thing to do. not got to grips with getting it as i'd like it yet though.

got my spreadsheet doing a decent job logging now and just need to get the reporting sorted out fully which i've started on.

[spider2097]