Betfair Data formulae
 

I Need help.
I want to check the Betfair data sheets from http://data.betfair.com.au/hr_files_finaldata.php
I want to know how to automatically work out the profit /loss of laying each race to various scenarios.
So firstly laying the field to SP liability say $100.
Secondly laying to the runners to a set SP ie $20 max.
Thirdly laying the favourite only
Fourthly laying the first few favourites only (1st and 2nd or 1,2,&3)
Conditional highlight fav and winner
I know it can be done but completely outside my capability at present.
Thanks in advance Beton

Something like this beton?

As I had to use array formulas to calculate the favourite returns, the calculations are demanding on most pc's so I have limited the worksheet size to 2000 records, which should suffice for you average day.

Dont think I have made any errors, but I did make a mistake once, so buyer beware;)

Thanks stugots
exactly what I need.
I will have a glass or two of red for you tonight.
beton

benton, it may make things easier allround to delete out all the place records prior to using bf's data

The code below does the job, let me know if you need instructions on use

stugots
Thanks again.
I have absolutely no idea where and how to put the code in. But I would like to. My New Years resolution "Learn Excel"
I am bringing a 2000 Glenowen Shiraz down to table To have a drink for you. Organic from 100yr old rootstock.
beton

Blimey... perhaps send the man a bottle??

Cheers LG

Next case.

No problem benton, when I get a bit of time tomorrow I will upload a new workbook with an option to delete the place bets - do you also want to get rid of the trot & pace races as well?

Looking back it wasn't that long ago I knew naff all about Excel & got a great deal of help from the Excel For Dummies book which I can highly recommend to get you going. Once that's under your belt then Excel VBA For Dummies is also a must read.

Thanks stugots
I will buy them tomorrow.
What you have done is great. I have just gone thru and highlighted the races that I am interested in testing against other data. (starting in DEC) It was only Saturday and Sunday 42 races. I deleted all other races manually. It worked a treat.
If you can delete the placed and the trots and pacers that will be great.

My initial aim is to check laying against data we have collected since Sept. However if we can get the excess removed I will go back as far as I can against BF data, and send you the summary. Beton

new version attached beton

As the number of records is greatly reduced after removing place/trots/pace records each betfair file will comfortably fit on one excel worksheet, anyway see how you go with it, any questions let me know.

Thanks again stugots, I will look at it tonight. beton

few changes to also delete jockey/specials/antepost entries

Thanks stugots. Off to the bookstore

came across another problem, product testing is such a b**ch;)

I would not worry too much. what you have given is more than enough. I will look at it tonight. In the main it will give an indicative result. Either the result will be good which will then need accurate results or no good which means that we have an education exercise. Either way we win. Thanks stugots for your effort I will keep you posted on the results. beton

Stugots
Thanks for your sheet. I encountered a problem using it last night. I downloaded it, enabled the macros, then entered the data, changed the date and pressed create. all it wanted to do then is download the program off the forum again. A similar thing occurred when I pressed the render button.
If this is easy to fix then great. If not, I can presevere with the mark 1 model. I have purchased the excel for dummies books. I intend to learn this ASAP. beton

sounds weird, let me know your email & I will send thru a copy

wally at wallyhughes dot com dot au It was frustrating. Thanks stugots.

Help Stugots please!
 

Stugots as you are the VBA Guru, I wanted to ask for your help. I want to open the Betfair data files and then extract the odds of the horses in each race, identify the winner in red font and sort (in ascending order) in rows.eg:
1.6 3.3 19 46 200 250
1.86 3.65 9.8 13 55
3.5 3.8 5.5 9.4 10 25 34
At present I have to manually mark the winner in red then Copy & Paste every race, then sort. The aim of the excercise is to look down columns of 1st Fav, 2nd Fav... and identify no. of consecutive losses and wins. Also Strike Rate, Average Div etc
The reasoning behind identifying no's of consecutive losses/wins is because they are the arch enemy of recovery staking plans. Most plans if staked right can handle two or three consecutive losses but any more and then followed shortly after by another loss and you've just been "shaken out of the tree". I was considering setting up multiple instances, using the same selection system, whereby instead of betting consecutive races each instance is programed to randomly miss a race or two or three in unison with each other so there's no double ups. Hopefully this will break up any clusters of losses. I'm not a mathematician and maybe this will have no affect?
Thanks in advance,
RP

As far as I know, Benjamin Franklin wasn't a scientist either. Interesting insights RP.. thanks for your contribution to date.

Cheers LG

Apple just fell on my head!
 

Since my last post I did some work on the random races sequence. It won't work!
Take this sequence of races: W W L L L W L W L L L L W
To avoid the cluster of 4 losses let's bet every second race with two bots. You'd get: W L L L L L W and W L W W L L
Now you've got a cluster of 5 losses with one bot!
The only time it might work is with a selection system with a 9 -11% chance of a win, like horses with odds around $10. Mathematically they rarely exceed 3 consecutive wins, so splitting races might work but then you might strike a race sequence like: L L L W L W L W L L L L L L and you'd still get three wins in a row.
Back to the drawing board, RP

Hi Rinconpaul, just saw your post, assume from your last post that you don't need assistance?

Thanks for your reply Stugots. Yes I've been able to source some codes from the net to speed things up. About the only one I'm still having to manually every race is when I've applied a conditional format (colour font red) to identify the winner in a range. But when I go to transpose the range the formatting doesn't come with it. So I have to manually click on the winner and format it red in every race. can you help?
Cheers RP