Download spreadsheet & graphs for your Betfair results

As I've mentioned before, it was Matt over at Punt.com that originally got me into looking at my P&L results by average profit per race, average loss per race and strike rate (click here to read that post). This is a good indicator of whether I'm closing my winning trades too soon or getting better and also whether I'm taking too many risks (ever increasing average loss!).

I'm making the spreadsheet that I use available for anyone to download. It's pretty simple so maybe this will only be of use to those who currently don't review their results much. Anyhow, even if only a few find this useful then it's worth it!

Update: April'11.  The much improved Results Summary v2 spreadsheet is now available for download! Including results summarised by sports, Betdaq support and much more.  Check out the post about it here http://betfairtradingmindgames.blogspot.com/2011/04/results-summary-v2-spreadsheet-is-here.html




(note: the screenshots show randomised data, not my real P&L!)

Here's how to get it up and running:

1. Download the past 3 months P&L data from Betfair. You need to save it to the same folder as the "Results Summary" spreadsheet to for the automated import to work. The Betfair spreadsheet needs to have its default name of BettingPandL (and the default extension of .xls).

2. Open the Results Summary spreadsheet. You will need to enable macros. Excel warns you of this when you open a spreadsheet to protect you from spreadsheets that have come from an unknown source. You can open the spreadsheet with macros disabled and look at the code to see that it's nothing naughty and then re-open it and enable macros. Also, your computer probably has a virus scanner and will scan the file when you download it. I understand that some may be a little weary so if anyone out there with some Excel knowledge could post a comment to say it's ok then thanks!

3. Click the blue import arrow. That's it!

If you want to undo the import you've just done then click the red undo button. You need to make sure that the data file you are importing doesn't have P&L data for the dates you've imported previously, so you can just overwrite the Betfair P&L file each day when you download the new results (or rename the old ones if you want to keep them).

Click on the "graphs" tab at the bottom to get the bigger P&L graph and the Average P&L per race and also strike rate. Beyond the obvious appeal of a P&L graph pointing in an upwards direction, I find the Average P&L per event graph really useful.

If the trendline of the average profit per race is pointing upwards, then you're getting better over time (unless you've just increased your stakes)!

If the average loss per race is getting smaller then you really are getting it nailed! If this is increasing but more slowly than average profit per race then you are taking probably taking more risks than before but it is paying off with larger profits (for instance, maybe you are now doing more swing trading than scalping).

If the average loss per race is getting larger faster than average profit per race then you probably need to look at reducing the risk of your trades -UNLESS - you happen to have a high or increasing strike rate - that is the 3rd graph. For instance, if you have 1 or 2 fairly big losses in a day but have traded 20 other markets successfully then you're probably still in good shape.

Everyones style is different so you know what applies to you, hopefully you'll find these graphs revealing and helpful to keep in mind for your trading. I make a note on the "notes" sheet of my averages for each month and then set targets for the coming month - not so much overall monetary targets - but for average P&L per race and strike rate. This is easier to see if my trading is improving. Hopefully some with find this useful, let me know. Cheers, MG.

20 comments:

  1. trying to use this but comes up with "macro WEEKNUM cannot be found"

    i am using excel 2003, which might be the issue

    ReplyDelete
  2. Hi, thanks for trying out the spreadsheet, sorry it's not working for you, I think you are right that it is not compatible with earlier versions (I made it using Excel 2007) which is frustrating.

    I tried to save it in 97-2003 format but Excel came up with several incompatibility warnings saying there would be loss of functionality. I tried to work through the error messages but couldn't get to the bottom of it. It seems I'd basically have to reassign every pivot table variable and re-enter the formulas, i.e. pretty much start over again.

    As I've built this up as I went along over a period of months I can't rememeber how I did most of it! Sometimes (quite often) I really get frustrated with Microsoft making things so difficult.

    Is there anyone out there who knows how to convert this to an earlier version automatically?

    ReplyDelete
  3. Just wanted to say thanks for sharing this. I have just noticed the post and will give this a go tomorrow. Looks very handy indeed.

    ReplyDelete
  4. Cheers! Let me know how you get on.

    ReplyDelete
  5. awesome spreadsheet, thanks for sharing :)

    ReplyDelete
  6. Hi Neil, that's good news. I was starting to wonder if it only works on my PC! Glad it was worth my while to share it. Hope it helps point you in the right direction. MG

    ReplyDelete
  7. Is it ok if I post screenshots of the spreadsheet on my blog, showing my monthly stats? I will also post a link to ur blog and say who made the spreadsheet if u like

    ReplyDelete
  8. Sure, that'd be good to see it being used. Yes, please mention that I made the spreadsheet with a link to my blog for anyone else who wants it. What is your blog?

    ReplyDelete
  9. cool thanks
    my blog is http://fighttheinnerdemons.blogspot.com/

    neil

    ReplyDelete
  10. Hey MG unfortunately I'm having a bit of trouble running the spreadsheet. When getting to step 3 after clicking on the blue arrow I get a runtime error message '1004' appear stating that 'Method Open of object Workbooks failed'. I then have an option to end or debug. When clicking on debug the following line is highlighted in yellow:

    Workbooks.Open Filename:= _
    ActiveWorkbook.Path & "\" & "BettingPandL.xls"

    My P&L is called 'BettingPandL.xls' and is in the same folder as the results summary speadsheet. Sorry for the long post, I'm an Excel novice so I'm not really sure what all of this means! Would you happen to have any ideas?

    ReplyDelete
  11. Hi JS, thanks for letting me know about this issue. I also am not an Excel expert! I've just acquired each step along the way as I've needed it. If you are using Excel 2007 version I don't know why this wouldn't work the same as on my pc. I found a website that explains how to find your excel version here:

    http://www.asap-utilities.com/download-help-excel-version.php


    However! Lets get it up and running.

    Replace these two lines of code:
    Workbooks.Open Filename:= _
    ActiveWorkbook.Path & "\" & "BettingPandL.xls"
    With the filepath specific to your pc. For example, on my pc it would be:
    Workbooks.Open Filename:="\users\mind games\documents\trading\results analysis\BettingPandL.xls"
    You need to replace all the bit in bold with your filepath. "mind games" is where you put your windows username (shown at the top of your start menu), "documents" should be the same if you do have the spreadsheets within your "documents" folder, then you need to name the sub-folder that the speadsheet is in within your documents folder. In my example, I have them in a subfolder of documents called "trading" and a subfolder of trading called "results analysis". You need a backslash "\" between each folder name, as in the example.

    By doing this you are giving Excel a literal filepath of where to look for the BF P&L file so you shouldn't get the error message any more. Let me know how you get on!

    ReplyDelete
  12. Thanks again MG I'm at work till late today, will give it a go when I get back tonight. Fingers crossed, I might get it working in time for my first monthly review!

    ReplyDelete
  13. Dude just wanted to say thanks again for the spreadsheet. Managed to finally get it working in the end :)

    ReplyDelete
  14. Hi JS, that's good news! Did changing that line of code do the trick or were there any other problems? It might be useful for others to know if they had the same error message. Thanks.

    ReplyDelete
  15. Hey guys,
    Thanks for awesome spreadsheat. Just made it working for me. Your computer windows settings "Regional and language options" "standarts and formats" must be set to english(united kindom), otherwise that spreadsheet gets mixed with date formats and not working. :) Hope it helps for international users who have different format settings.

    ReplyDelete
  16. Hi za1jcas,
    Thanks a lot for posting that up, I'm sure that will be helpful. Cheers, MG

    ReplyDelete
  17. Looking forward to the new version, a link to the old version would be great to play around with. Keep up the good work!

    ReplyDelete
  18. Good stuff. The new version is very nearly ready. I hope by early next week. There's so many new features I'm currently making videos to show them as it'd be too long to write about!

    ReplyDelete
  19. Formstudent - the new Results Summary v2 spreadsheet is now posted!

    ReplyDelete
  20. Thanks for the great spreadsheet, and the support you gave, the sheet now works perfectly for me.

    ReplyDelete