Excel Trading Bot Extract Race Distance

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
MugPunter
Posts: 42
Joined: Fri Sep 14, 2018 10:58 pm

Hi,
I'm trying to develop a trading bot in excel that places bets in-play according to approximate distance and time.
My problem is extracting the distance from the race description string ('Bet Angel'!$B$1), I'm assuming I need some VBA code to do this.
The code would run when the market changes in the workbook, it would then apply several masks to the string, like " ####m " " ###m " " #m " " #f " " #m#f " etc then convert each masked string to metres, and place the value into a specific worksheet cell.
Is there a simpler way to do this? Has someone already created this code?

Any assistance appreciated.

Thanks,
Matt
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

here's something I did in .net way back - it's kinda dirty and I'm sure you can refactor to suit in vba:

Code: Select all

private double ConvertRaceDistanceToNumber(string distance)
{
    double furlongTotal = 0;
    const double oneFurlong = 220.00;
    const int furlongInMile = 8;
    
    // distance expected in string as 2m5f, 1m6f, 2m3f33y etc...
    distance = distance.Replace("m", "m ").Replace("f", "f ").Replace("y", "y ");
    var distanceSplit = distance.Split(' ');
    
    // loop round each split - find the token (m/f/y) and nullify
    // a little hacky but works      
    foreach (var splitPortion in distanceSplit)
    {
        if (splitPortion.Contains("f"))
        {
            int furlongItem = Convert.ToInt32(splitPortion.Replace("f", ""));
            furlongTotal += furlongItem;
        }
        if (splitPortion.Contains("y"))
        {
            var furlongItem = Convert.ToDouble(splitPortion.Replace("y", ""));
            double furlongFraction = furlongItem / oneFurlong;
            furlongTotal += furlongFraction;
        }
        if (splitPortion.Contains("m"))
        {
            int furlongItem = Convert.ToInt32(splitPortion.Replace("m", ""));
            furlongTotal += furlongItem * furlongInMile;
        }
    }
    return furlongTotal;
}
these days, I'd probably use a couple of regEx matches on the tokens of m/f/y etc... but these were early days :)
User avatar
Euler
Posts: 24702
Joined: Wed Nov 10, 2010 1:39 pm
Location: Bet Angel HQ

If you have a cell containing the market name from Betfair here is how you can capture the distance.

=MID(CELLREF,(SEARCH(":",CELLREF)+4),FIND(" ",MID(CELLREF,(SEARCH(":",CELLREF)+4),7)))
User avatar
MugPunter
Posts: 42
Joined: Fri Sep 14, 2018 10:58 pm

Thanks Euler, great formula, except it gives the race number :oops:

I did come up with this though:
=SUMIF(CELLRANGE,"<>#VALUE!")
=VALUE(MID(CELLREF,SEARCH(" ????m ",CELLREF),5))
=VALUE(MID(CELLREF,SEARCH(" ???m ",CELLREF),4))
=VALUE(MID(CELLREF,SEARCH(" ?m ",CELLREF),2))*1609.34
=VALUE(MID(CELLREF,SEARCH(" ?f ",CELLREF),2))*201.168
etc.
Euler wrote:
Tue Sep 18, 2018 5:29 pm
If you have a cell containing the market name from Betfair here is how you can capture the distance.

=MID(CELLREF,(SEARCH(":",CELLREF)+4),FIND(" ",MID(CELLREF,(SEARCH(":",CELLREF)+4),7)))
User avatar
Euler
Posts: 24702
Joined: Wed Nov 10, 2010 1:39 pm
Location: Bet Angel HQ

OK, I guess you are trying to use it in AUS then?
User avatar
MugPunter
Posts: 42
Joined: Fri Sep 14, 2018 10:58 pm

Using in Aus?
Race descriptions follow a similar order, I'm seeking to extract race distance regardless of imperial or metric presentations in the race description. AUS races are metric, but if you examine the race descriptions in GB (imperial) you will see race distances are presented in miles, furlongs and fractional furlongs.
Thanks for the banter. I'm sure I can code this into a simple excel function!
Euler wrote:
Thu Sep 20, 2018 9:10 am
OK, I guess you are trying to use it in AUS then?
User avatar
MugPunter
Posts: 42
Joined: Fri Sep 14, 2018 10:58 pm

=VALUE(MID(CELLREF,SEARCH(" ?m ",CELLREF),2))*1609.34 ->miles
=VALUE(MID(CELLREF,SEARCH(" ?f ",CELLREF),2))*201.168 ->furlongs

MugPunter wrote:
Thu Sep 20, 2018 10:16 am
Using in Aus?
Race descriptions follow a similar order, I'm seeking to extract race distance regardless of imperial or metric presentations in the race description. AUS races are metric, but if you examine the race descriptions in GB (imperial) you will see race distances are presented in miles, furlongs and fractional furlongs.
Thanks for the banter. I'm sure I can code this into a simple excel function!
Euler wrote:
Thu Sep 20, 2018 9:10 am
OK, I guess you are trying to use it in AUS then?
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”