Monday, 29 June 2015

External Data Lookup

Another major addition only in Bet Selector Ultimate Professional is the ability to access data from external files. This is done via a Lookup function that can be used in MyCalc as well as in Expert Mode.

For example you might have your own jockey ratings file that you would like to use or perhaps a file of class ratings or even a file of horse ratings. While a lot of work has gone on behind the scenes to make this work, the way you implement it is quite simple. First you need a comma separated values (CSV) file with two columns - the first column contains a character string you wish to search on and the second column the numeric value you wish returned in the event of a successful search.

In all cases the file is saved in text format and commas are used to separate the names from the values. Note this means you cannot have more than one comma on a line. Files must be saved in your Price folder and to stop Bet Selector confusing them with normal system CSV files (eg for System Analyser) you must end them in "-d.csv". So for example a jockey ratings file could be saved using the name "jockeys-d.csv" while for race classes you might uses "classes-d.csv" as a name. Think of the "d" referring to "data" if you need a way to remember it.

The above files can be manually created in Notepad but you can also create them in Excel (or any spreadsheet program for that matter) by simply putting the character strings in the first column and numbers in the second column. Then use "Save As" to save as a CSV file and the text based format with commas between the two columns will automatically be created for you plus it will automatically have the .csv extension. Note there is no need to use quotes around the items in the first column.

This new feature can also be used if you just want to pick horses ridden by a jockey from a list of top jockeys or if you just want to pick races only in say country Victoria. The remainder of this post examines how you do this.

The Lookup Function


A special "Lookup" function has been created to locate the desired data in the first column of the file and return the number in the second column. For example the following when used to look up jockey names in a jockey ratings file will return the actual rating for the jockey being looked up:

Lookup(H:JOCKEY,"jockeys-d.csv")

Note the first argument in the Lookup function is what you are searching for in the first column of the file (eg from the drop down lists in MyCalc/Expert Mode) and the second argument is the name of the file which must be in quotes.

The above could be added to other things in a calculation to create your own ratings for example but it is more likely you would use it directly in an Expert Mode rule by comparing it to something (eg less than a value or greater than a value etc). For example  a rule to pick jockey ratings under 5 would be as follows:

Lookup(H:JOCKEY,"jockeys-d.csv")<5

Note the above assumes your jockey ratings are positive and you want the smaller ones which indicate better jockeys (as is the case with traditional jockey ratings that go from 0 or 1 for the best through to say 8 for the worst).

Jockey and Trainer Ratings


In the case of jockey ratings the file would have jockey names in the first column and the associated jockey ratings in the second column. It would look like this:

J B Mc Donald,1.0
B Shinn,1.5
J Bowman,1.5
....

If you want to access a list of trainer ratings then the format is exactly the same as above with the trainer names in the first column and the associated ratings in the second column. Similarly with horses you would put the horse names in the first column and the associated ratings in the second column.

New in Bet Selector Ultimate Pro you can now run system tests/analysis to create jockey rating files formatted in accordance with the above requirements. The procedure is to run a system test over the desired meetings/dates and then go into System Analyser and run the Jockey and Trainer analyser.

Jockey ratings range from a minimum of 1.0 to a maximum of 9.8 with 1.0 being the best and 9.8 the worst. The reason 9.8 is used as a maximum is because when accessing these ratings in system tests we use a value of 9.9 to indicate a jockey was not found in the file.

The jockey and trainer rating files that are created for use as lookup files in MyCalc and Expert Mode are named as follows:

Jockey ratings file: systemname-jd.csv

Trainer ratings file: systemname-td.csv

Now we will move on to the important task of using the jockey ratings in system tests. Following is an Expert Mode rule stipulating that jockey ratings must be less than or equal to 2:
               
Lookup(H:JOCKEY,"2017-jd.csv")<=2

Basically the above rule uses the special Lookup function that has two parameters namely the code for the jockey being searched for and the name of the file to search. Then the final comparison with the number 2 completes the rule. You can use the above anywhere in an Expert Mode system listing but if you want to be able to view the jockey ratings in your selection lists then I suggest the following "MyCalc implementation" as the first line of your Expert Mode system:

0.CALCMAX=2,CALCCODE=Lookup(H:JOCKEY,"2017-jd.csv")

The above still tests for jockey ratings less than or equal to 2 but as just mentioned it also displays the jockey ratings in your selection lists. But that's not all as it also lets you analyse the jockey ratings and rankings in System Analyser (under the MyCalc value and rank breakdowns) and lets you test the jockey rankings instead of or alongside the value tests. Note that CALCCODE is a special setting that allows you to set a one line MyCalc inside and Expert Mode system without having to retrieve it from a separate MyCalc file.

The following example picks the top 5 ranked jockeys in each race:

0.CALCRANKMAX=5,CALCCODE=Lookup(H:JOCKEY,"2017-jd.csv")

You can also test for ranks and values at the same time. The following example picks the top 5 ranked jockeys in each race but removes those rated above 3:

0.CALCMAX=3,CALCRANKMAX=5,CALCCODE=Lookup(H:JOCKEY,"2017-jd.csv")

Please understand the above examples are unlikely to be used as systems in their own right as normally you would add other rules to select venues, day of the week, top rated horses etc. Note also that because jockey ratings now take into account class, specifying a maximum rating of 3 will usually only pick Sydney, Melbourne and Hong Kong jockeys so if you want to test jockey in other states including provincial and country tracks, then you will have to use higher maximums.

Race Ratings


In the case of past race class values the file would have race class/class abbreviation in the first column and your class values in the second column. It would look like this:

G1,63
G2,61
G3,59
LR,57
Open,55
....

Note that for upcoming races the class names are not as abbreviated so a file for them would look like this:

GroupI,63
GroupII,61
GroupIII,59
Listed Race,57
Open,55
....

Lists without Ratings


Suppose you only want to pick horses ridden by a jockey from a list of say 20 jockeys. You can do this using the above feature by creating a file with your desired jockeys, giving each jockey a non-zero ratings (eg you could give them all a value of one) and then testing for jockey ratings above zero.

You can do the same thing for trainers by creating a list of desired trainers and giving them all a value of say 1. And with horses this is the ultimate Black Book feature as you can have a list of horses as short or as long as you like (just remember to give each horse a non-zero value like 1 so that you can test for it being non-zero).

Another application would be if you wanted to run systems only for say Victorian country tracks or NSW country tracks. This could not be done before but now can by using a file containing the 3 letter codes for the tracks you want to test.

To save you the trouble of creating it, a file including all Victorian country tracks can be downloaded via the following link:

http://www.capitalinfo.com.au/ctryvic-d.csv

The Expert Mode rule to select tracks from this list is as follows (note because it is a venue rule you can prefix it with "1." to speed things up):

Lookup(V:TCODE,"ctryvic-d.csv")>0

Files for other States will be added here soon.

Spelling and Case Sensitivity


Searches are not case sensitive so there is no need to worry about upper and lower cases being different in Bet Selector compared to your file. As long as the letter to letter match is exact the search will succeed and the relevant number will be returned. Note if an exact match cannot be found in the file (or the file can't be found) then zero is returned.

You will need to be particularly careful with jockey and trainer names as they can often differ very slightly due to the presence or absence of second initials or the use of different initials. To check the exact name that needs to be used you should go into the Price Predictor part of the program and click the LOAD DATA TABLES button to see the data exactly as it appears in the form downloads.

Note: Do not read the jockey and trainer names off the main Price Predictor field list as it uses abbreviated names to save space.

Advanced Use


You might have an application where you want to look up two things at once. For example if you wanted to import your own ratings for the current day you could just do a lookup based on the horse name. But if you also wanted to do this for past days then you would have to do a lookup based on both the horse name and the date.

The way to lookup horse name and date together is to add a character representation of the date to the horses name and use this to perform the lookups. Since horse names can vary in length it is best to start with the date string and then add the horse name.

The best date string to use is in the format yyyymmdd (note it sorts better than ddmmyyy). For example 29 June 2015 is represented by 20150629. Following are a few lines to show you how the required file would look (note you must not use quotation marks around the dates/horse names):

20150629Overdrawn,55.5
20150629Cugine,54.0
20150629Heavenly Dancer,53.5
20150614Overdrawn,52.5

As with previous examples, the lookup data file must be saved with a name that ends in "-d.csv". If for example your own ratings file is called myratings-d.csv then the lookup to search for the date and horse combination in System Selector is as follows:

Lookup(DTOS(V:MDATE)+H:HORSE,"myratings-d.csv")

The above will return your rating for the desired horse on the desired date (if found). If you want to test for your top rated horse then the following one line Expert Mode system will test for the top ranked horse:

0.CALCMIN=1,CALCRANKMAX=1,CALCCODE=Lookup(DTOS(V:MDATE)+H:HORSE,"myratings-d.csv")

Note that DTOS()is a speial (Date To String) function that converts the date to yyyymmdd format.