Excel Trading Analysis

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

Trading Record2.xls
All,

I've made a spreadsheet that can be used in conjunction with the spreadsheet that can be downloaded from Betfair that lists your trades over a period of time.

It gives the total races you have traded and the overall profit/loss figure

It also lists details of your trades by course/racetype/distance and gives the profit/loss figure against each one.

You can also double click on a track/race type/distance to get further details.

On the sheet attached, you can see that at Bangor, I traded 5 races, double clicking brings up more details (under the blue header), that lists all 5 trades, their distances, race types and profit loss.

You can do the same with the race type and distances.

Ticking one of the boxes at the top will also sort
by profit/loss and number of trades

It will work on Excel 2000/2003

However, you do need to change the pathway to tell it where the Betfair spreadsheet is.

Press alt/f11 scroll down a little bit down you should see:

'***************************************************************


'COPY DATA FROM DOWNLOADED BETFAIR SPREADSHEET

ChDir "C:\Users\nigel\Desktop"
Workbooks.Open Filename:="C:\Users\nigel\Desktop\BettingPandL.xls"

As long as you save the Betfair spreadsheet to the desktop and don't rename it, all you should have to change is the user name.

Once you have done that, just press the "update" button.

Sheets 1 and 4 are hidden, purely for cosmetic purposes. However, deleting/moving columns will have a detrimental effect as I have not included any error checking routines.

It does contain macros, so don't be alarmed about the warning that pops up when you first open it.

Any problems, please get back to me.

Nigel

Trading Record2.xls
You do not have the required permissions to view the files attached to this post.
chris_ri
Posts: 36
Joined: Wed Apr 15, 2009 1:57 pm

Sounds interesting Nigel, however there is no spreadsheet attachment with your message (or at least I cant find one).

Cheers,

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

Ok, Excel files are not allowed to be uploaded.

The only way around it is to ask Admin, or leave your e-mail address so I can send you a copy directly.

Admin, Can you help?

Thanks,Nigel
User avatar
jimrobo
Posts: 1289
Joined: Wed Mar 25, 2009 12:49 pm

like the sound of this if someone can get allow .xls files to be uploaded and someone could test it would like to have a go myself
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

Sounds like a really useful tool. Please it email support and we will find a way of putting it up for you.
User avatar
BAP-Admin
Forum Admin
Forum Admin
Posts: 147
Joined: Tue Mar 17, 2009 1:11 pm
Location: UK

You could have upload it within a zip file.

Anyway, I have enabled xls files.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

Please see attached..
You do not have the required permissions to view the files attached to this post.
User avatar
jimrobo
Posts: 1289
Joined: Wed Mar 25, 2009 12:49 pm

very good!!! Great piece of work!

The only addition I wanted was an extra column next to each profit/loss which is an average.

Any advice on the best way to do it?? If I do it manually then it messes up the display with the analysis

It would be nice to have strike rate somewhere next to the profit columns to!
User avatar
mugsgame
Posts: 1235
Joined: Wed Mar 25, 2009 11:41 pm

Top work Nigel

Thanks very much

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

Glad you all like it.

I've added an average column as Jimrobo asked next to the profit/loss figures and also an overall strike rate.

I'll have a look at putting the strike rate alongside the other figures, but it might be a bit of a struggle fitting it all on the same screen.

I should also mention that sometimes the Betfair spreadsheet does contain some oddities. For example,
a 2m1f national hunt flat race was listed as a "2m INHF"
and also sometimes a GRP3 is also listed as a GRP 3.

So be prepared to see some unusual race types. My favourite so far was an "XC Chase".
Thanks,
Nigel.
Last edited by nigelk on Tue Oct 27, 2009 2:51 pm, edited 2 times in total.
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

I've got to say..... this is top notch!

Well done and huge thanks. This is invaluable. I can already see that I make most profit on Group 1, claimers and Grp2 and least on Maiden Stakes. Folkstone is my happy course and I shall be avoiding Pontefract!

It would be fun to hear what others find!

There are a couple of cells that say '#VALUE!' but I'm sure I can sort those out.

An impressive job.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Thanks Tub.
Can you let me know what cells the #values are appearing in?

Nigel
User avatar
jimrobo
Posts: 1289
Joined: Wed Mar 25, 2009 12:49 pm

Thanks nigel great addition!

Great information from it! I can already see the irish tracks are going to be completely avoided for the summer! As well as sell stks!

I dumped 2 months worth of results in there and overall strike rate was 73%. Not sure whether that is good, bad or average!
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

nigelk wrote:Can you let me know what cells the #values are appearing in?

Nigel
Sorted it - My BettingPandL.xls sheet also featured place markets. It was these causing the '#value!' entries.
taxbreaks1
Posts: 23
Joined: Thu Apr 16, 2009 12:25 pm

Nigel spreadsheet great but will not work for me?

ChDir "C:\Users\nigel\Desktop"
Workbooks.Open Filename:="C:\Users\nigel\Desktop\BettingPandL.xls"

I replace your name with mine and it will not update takes me back to debug same lines? Any ideas?

Thanks
Post Reply

Return to “Trade analysis & record keeping”