Strange rPSCN results

[CC Brown]

In trying to find races where the ML favorite is a truly false favorite I ran across something I find hard to understand. I am looking at overlays only.
Testing the following
rMLO>1 not the favorite
Fast Dirt Sprints no maidens
nAODD>nKLine Overlay
K=1
HTR=1
rPSCN=1
Results 137 plays 24 wins 18% $ROI 0.76 This doesn't look very good!

So I now try same thing except make nPSCN>1
Results 347 plays 82 wins 24% $ROI 1.19 Big difference.

I then try these setting on Fast Dirt Routes No Maidens

Now on rPSCAN=1 the win rate is higher and the ROI higher and it goes down as you go to
=2 and =3 just as I would expect. The same thing occurs on Maiden Sprints and Routes.

What happens on Sprints-No Maidens that is different. Do I just have much to small a sample? or is there something about crowd knowledge on sprints/overlays/etc

Maybe someone with a bigger DB can get different results with more data.

Thanks
CC

[hurrikane]

htr1 =1 rK =1 rMLO>1 nAODDS>nKLINE nCLA < 3 nDS 1

rPSCN NoRaces TotWon NoWins Win% ROI
1 1295 2319 272 0.21003861003861 0.895366795366796
2 1320 2744.9 296 0.224242424242424 1.03973484848485
3 966 1906.2 194 0.200828157349896 0.98664596273292
4 561 1168.7 112 0.199643493761141 1.04162210338681
5 260 493.2 37 0.142307692307692 0.948461538461539
6 119 217 15 0.126050420168067 0.911764705882353
7 36 66.6 5 0.138888888888889 0.925
8 14 0 0 0 0
9 4 0 0 0 0
10 1 0 0 0 0

[Victor]

Very nice report hurrikane,

how did you do that?

[CC Brown]

Hurrikane Thanks! Your report show the same that mine does essentially. If you use PSAN=1 the win rate is not any better and in some cases worst than PSCN=2,3 and 4. Very strange to me. I ran a test without overlays and keeping the MLO favorite. The results here are more what you might expect.
PSCN=1 975 plays win% 39 roi .88
=2 494 plays win% 35 roi .96
=3 241 plays win% 28 roi .94
=4 115 plays win% 35% roi 1.42
I suppose here the crowd can see this horse and they bet it down but it does have a higher win rate than 2 0r 3 and 4 is short data.

Now when I add either rMLO>1 (not morning line fav.) or nAODD>nKLine (overlay) The PSCN= 1 has a worst win rate than 2,3 or 4 and the PSCN=1 ROI is really lower than the others. This I don't understand!

Is this something to do with the nature of the PSCN. The question again is why on Overlays and/or rMLO>1 does PSCN=1 do worse on both Win% and ROI than on 2,3 or 4. It looks like it should at least have a higher win% if not ROI but it does not.

Maybe I don't fully understand how the PSCN is created. Maybe KM can shed some light on this. The reason I am concerned is I don't want to create a betting strategy based on some unsound fact.
CC

[hurrikane]

Vic,
I don't use the report..takes to long to run so I do the calcs in the query.
paste this in..of course you table has to match mine.

SELECT ALL_HX4.rPSCN, Count(ALL_HX4.nRACE) AS NoRaces, Sum(ALL_HX4.xWIN) AS TotWon, Sum(IIf(([xwin]>0),1,0)) AS NoOfWins, [NoOfWins]/[NoRaces] AS [Win%], [NoOfWins]/([noRaces]*2) AS ROI
FROM ALL_HX4
WHERE (((ALL_HX4.nCLA)<3) AND ((ALL_HX4.rKrat)=1) AND ((ALL_HX4.rHTR)=1))
GROUP BY ALL_HX4.rPSCN;

[Victor]

rPSCN NoRaces TotWon NoOfWins Win% ROI
1 17080 $30,381.00 6401 0.374765807962529 0.187382903981265
2 8233 $14,741.10 2598 0.315559334386008 0.157779667193004
3 4018 $7,140.20 1088 0.270781483325037 0.135390741662519
4 1683 $3,054.40 405 0.240641711229947 0.120320855614973
5 733 $1,444.60 165 0.225102319236016 0.112551159618008
6 264 $481.30 58 0.21969696969697 0.109848484848485
7 76 $127.80 15 0.197368421052632 9.86842105263158E-02
8 21 $8.40 1 4.76190476190476E-02 2.38095238095238E-02
9 9 $0.00 0 0 0
10 3 $12.00 1 0.333333333333333 0.166666666666667
11 1 $19.20 1 1 0.5

[CC Brown]

Hurrikane this is great stuff with these calc. Since I am new to Access I have to run each one then use ROI analysis, then print report. When I modify your SQL to include rMLO>1 and nAODD>nKLine I get the same number of races, same number of winners, same win% but my ROI is different in every case of PSCN. I add up the amt won which matches your and divided by amt bet and it mached my report print out but not yours. Since I don't really understand how you did ROI( I don't understand your expressions) I cannot see how you did it. Can you help because I might have the wrong numbers.
CC

[CC Brown]

Hurrikane, I found it. In the ROI expression it should be TotWon instead of NoOfWins. When I made this change all numbers agreed.

I hope I am smart enough to learn how to do this some day. Until I saw your stuff I didn't even know how to begin. I've learned a lot from trying this. Thanks again for sharing this with us. I look forward to learning more from the July session in LV. Maybe I can learn enough before then so I can understand what is being presented.

[Victor]

rPSCN NoRaces TotWon NoOfWins Win% ROI
1 17080 $30,381.00 6401 0.374765807962529 0.889373536299766
2 8233 $14,741.10 2598 0.315559334386008 0.895244746750881
3 4018 $7,140.20 1088 0.270781483325037 0.888526630164261
4 1683 $3,054.40 405 0.240641711229947 0.907427213309566
5 733 $1,444.60 165 0.225102319236016 0.985402455661664
6 264 $481.30 58 0.21969696969697 0.91155303030303
7 76 $127.80 15 0.197368421052632 0.840789473684211
8 21 $8.40 1 4.76190476190476E-02 0.2
9 9 $0.00 0 0 0
10 3 $12.00 1 0.333333333333333 2
11 1 $19.20 1 1 9.6

[Victor]

Wish I could line this stuff up!
Adding the rMLO>1 and nAODD>nKLine, I get
rPSCN NoRaces TotWon NoOfWins Win% ROI
1 973 $1,639.40 189 0.194244604316547 0.842446043165468
2 870 $1,560.10 170 0.195402298850575 0.896609195402299
3 573 $1,190.70 117 0.204188481675393 1.03900523560209
4 292 $763.00 69 0.236301369863014 1.30650684931507
5 142 $242.10 22 0.154929577464789 0.852464788732394
6 65 $124.20 11 0.169230769230769 0.955384615384615
7 18 $10.00 1 5.55555555555556E-02 0.277777777777778
8 7 $0.00 0 0 0
11 1 $19.20 1 1 9.6

[Victor]

Oh boy, now I added the nDS=1 and basically I see the same thing as CC Brown spoke about: the win percentage and ROI both go up as rPSCN goes from one to four. This is very interesting. I get a positive ROI of 1.4 for rPSCN=4

[Rick]

rPSCN NoRaces TotWon NoOfWins Win% ROI
1 911 $1,457.80 309 33.92% 0.17
2 437 $856.80 149 34.10% 0.17
3 214 $428.20 58 27.10% 0.14
4 96 $279.10 33 34.38% 0.17
5 42 $51.80 7 16.67% 0.08
6 15 $34.00 4 26.67% 0.13
7 8 $10.00 1 12.50% 0.06
8 2 $0.00 0 0.00% 0.00

Now clean up your queries. Open them in design mode. Right click on the column names that need to be formated. Click on Properties, select the right Format and select the number of Decimal Places you want, then save.

You get a much cleaner and easier to read results.

But sometimes you still have to copy to NotePad first to get things to line up. :)

[Victor]

rFr1 NoRaces TotWon NoOfWins Win% ROI
1 119 $157.50 18 15.13% 0.66
2 119 $244.70 21 17.65% 1.03
3 118 $153.20 14 11.86% 0.65
4 118 $158.30 15 12.71% 0.67
5 118 $146.20 13 11.02% 0.62
6 114 $152.20 13 11.40% 0.67
7 101 $53.30 5 4.95% 0.26
8 82 $142.40 4 4.88% 0.87
9 54 $295.00 7 12.96% 2.73
10 34 $4.20 1 2.94% 0.06
11 12 $16.40 1 8.33% 0.68
12 5 $3.80 1 20.00% 0.38

[MikeDee]

Nice query HK. Thanks for sharing it.

If you like to see your results sorted by tracks here is one other enhancement you can apply to Hurrikane query. It is to insert a column before the rPSCN colum, add the tTRK field in this column and make it a "group by" in thei total row. This will group your results by track and then by rPSCN (or whatever factor you may be testing.

[CC Brown]

Rick, Thanks for formatting info. Now report looks good. Still don't understand why PSCN=1 has lower win rate and ROI than PSCN=2,3 or 4. This doesn't seem right to me but Hurrikane got the same on a larger data sample so I will just go with it.

[CC Brown]

Mike Thanks! I tried by track and lo and behold it worked the first time. This is great info for someone like me beginning with Access. These are things I have not been able to get from a book.

[Victor]

Nice query HK. Thanks for sharing it.

If you like to see your results sorted by tracks here is one other enhancement you can apply to Hurrikane query. It is to insert a column before the rPSCN colum, add the tTRK field in this column and make it a "group by" in thei total row. This will group your results by track and then by rPSCN (or whatever factor you may be testing.

and VERY nice query design hurrikane,

I have an idea on how to use it to improve spot plays.

Much appreciated.

[hurrikane]

whoops.
thanks CC. sometimes I get going so fast I run over top of myself.

thanks.

and the lesson learned is....don't believe anything you see until you have checked a dozen times. then have someone else look at it. :D