Tub's right. Open Office is a bit limited with macros as compared to Excel
Nigel.
Excel Trading Analysis
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
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
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.
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.
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.
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.
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
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
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.
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.
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
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
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.
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.
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
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