Excel Trading Analysis

Post Reply
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Tub's right. Open Office is a bit limited with macros as compared to Excel

Nigel.
margueston
Posts: 5
Joined: Tue May 05, 2009 12:59 pm

Cheers anyway guys
User avatar
firedave
Posts: 12
Joined: Mon Apr 20, 2009 6:18 pm

What a fantastic spreadsheet. Been meaning to do something like this but no point reinventing the wheel (and a very good wheel it is!)

I have a question about the strike rate as to whether it should count a £0 market as a negative. To me this is the same as not doing anything on the market but I can see it from the opposing side that you put trades into the market and didn't come away with a profit. What are your thoughts - should zero markets be ignored in the strike rate?

The only other thing I have noticed is that downloading the profit and loss sheets from betfair always means I have to open the spreadsheet first which cleans errors out of it before I can import it into the trading spreadsheet. I wouldn't be surprised if this is because I'm trialing Windows 7 but a little frustrating!

Keep up the good work.
David

------------------------------------------
http://firedave.blogspot.com
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Hi Dave,

I did think of ignoring the £0's but then thought
we set out to make a profit, and if we don't we've failed, hence them being counted of as failures.

Both ways are vaild. In football for example, a 0 would represent a draw, but in tennis, you can only win or lose.

Sorry about the spreadsheet problems, sounds like an unwanted feature of Windows 7.

PS. Feel free to mention the spreadsheet on you blog!

Thanks,Nigel.
misteraitch
Posts: 2
Joined: Thu Apr 16, 2009 6:15 pm

Thanks very much for sharing the results of your efforts, great bit of programming! Much appreciated.
speakers
Posts: 52
Joined: Thu Apr 16, 2009 9:44 am

Hi, I'm late to this thread but thought it would be a great tool to use. However, I haven't really got a clue about excel and can't make it work. It's just telling me it can't find a file named BettingPandL.
I saved the PandL from Betfair as "Save target as" and it saved it as BettingPandL on my desktop. It doesn't work. You said it works on Excel 2000/2003 and mine is 2000. Did you mean 2000 to 2003 or only those 2 years shown? I did alt F11 to change the file path, which I copied from the "properties" and it still doesn't work.
An idiot would be pleased for any advice.
User avatar
Westerner
Posts: 161
Joined: Fri Apr 17, 2009 10:03 am

Hi Nigel,

Thanks for the spreadsheet - it's great.

The only thing is, most of my betting revolves around cricket - would you be interested in creating one for that sport?

If so, I'd be happy to re-imburse you with a reasonable amount.

Cheers
maneman
Posts: 11
Joined: Wed Aug 26, 2009 1:36 pm

Nigel,

Thank you very much for a superb spreadsheet, managed to get it up and working with no problems, your instructions were excellent.

Just joined the forum and been using Bet Angel for aprox 2 months, mainly for laying but after yesterdays course at Hook I intend to start trading more than just on the odd race.

Your spreadsheet I'm sure will be a great aid.
:)
theone
Posts: 1
Joined: Wed May 06, 2009 6:32 pm

I downloaded the newest spreadsheet you put up here, its all working fine. All the races course/tracks come up... but i have one which has the name #value! what is that ?

thanks.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

It could be that either your spreadsheet contains place markets or that you are downloading the incorrect sheet ( should be BettingP&L not the Betting History sheet).

Thanks,Nigel
mtrend
Posts: 90
Joined: Sun Sep 06, 2009 6:46 pm

Hi NigelK / Everybody,

I am trying to add the strike rate for each course on the default page so I can sort my courses into strike rate success.

If you double click into the course it gives you the strike rate for that course, I cant see where this data is being held, is there a fairly simple way I can add an extra column onto the main sheet so I can sort the course by strikerates ?

TIA
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Hi Mtrend
Unfortunately there is no simple way to insert a column as you'd have to change various references in the code. ( I think but can't remember for sure!)

Right click on the sheet tab, and 'View code'
This is the line you are after

'strike rate
Sheets("sheet3").Range("b5") = Sheets("sheet1").Range("ap4").Value

and if you select 'format', 'sheet' and unhide sheet 1
Range AP2, AP3, and AP4 contain the figures you are after. AI2 contains the value of the cell you double clicked on and AK to AO all the info for that track.

Hope it helps.
Nigel.
mtrend
Posts: 90
Joined: Sun Sep 06, 2009 6:46 pm

Cool, il take a look, thanks !
Predicton
Posts: 281
Joined: Sun Sep 13, 2009 3:41 pm

This looks really clever, you must be an IT whizzkid,

cheers, P
Seabird
Posts: 70
Joined: Thu Apr 16, 2009 1:31 pm

Excellent work NigelK and many thanks for sorting out a version that works with my data.

One suggestion I have is to have columns which show the P/L per trade for Course, Race Type and Distance. This gives another view on where one is going wrong. I have added these to columns S, T and U (which I presume is OK) but it would be better if they were columns which immediately followed the P/L they were using. Is that possible or a pain?

On the earlier subject of how to treat scratches in the strike rate, my personal view is they should be treated positively. I always note a scratch as +0. My reasoning is:

My primary objective is to make profit on a trade.
My secondary objective is to preserve capital.
My third objective is to limit any loss to a small one.

A scratch trade satisfies objective 2 and I think objectives 1 and 2 are successes. In any case it is psychologically good to consider a scratch in the glass half full category.

One thing your analysis spreadsheet has shown me is that if my scratch level is high on any given day I am more likely to make profit. I believe that that is because I am more on the ball that day. The more I'm prepared to scratch and not let the trade drift all over me the more I'm likely to profit.

Anyone agree that scratches should be considered positively?

Thanks again.
Seabird
Post Reply

Return to “Trade analysis & record keeping”