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
Excel Trading Bot Extract Race Distance
here's something I did in .net way back - it's kinda dirty and I'm sure you can refactor to suit in vba:
these days, I'd probably use a couple of regEx matches on the tokens of m/f/y etc... but these were early days
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;
}
Thanks Euler, great formula, except it gives the race number
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.
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.
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!
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!
=VALUE(MID(CELLREF,SEARCH(" ?m ",CELLREF),2))*1609.34 ->miles
=VALUE(MID(CELLREF,SEARCH(" ?f ",CELLREF),2))*201.168 ->furlongs
=VALUE(MID(CELLREF,SEARCH(" ?f ",CELLREF),2))*201.168 ->furlongs
MugPunter wrote: ↑Thu Sep 20, 2018 10:16 amUsing 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!