Bob has invited me to join his Blog so here goes..........
I've been a Dataform subscriber since Dec 03, found Bob, JohnB, Martin & others through the VFB Forum back in the day - but it was closed down............
Now living in Ocala, Florida but an avid follower of the Horse Racing back in blighty. I'm 5hrs behind GMT which doesn't help and gets worse as the winter gets nearer. I get most of the stuff I need through the internet of course but Betfair won't allow me access through VFB - so I have to work harder on non-runners and latest pricing.
I'm a total "number cruncher" and use Excel spreadsheet extensively to help me get the winners I need. Currently I'm watching the races, recording the results for a new formula I want to do to improve my selections. It's not doing too bad on it's own currently but I feel it's missing a little something which I hope to squeeze down with yet another formula -Mark has given me a useful pointer to look at.
To give you an idea of what I create each day
1/. I export 4 Data Export Sheets covering 16 runners
2/. This is converted into Excel via 3 spreadsheets through Macros (hence Macroman)
2/. My final Excel spreadsheet then creates Sheets of data to help me choose which to back & lay
3/. I have a sheet which gives me the 4 Most Likely Winners for each race
4/. it all takes 10mins and all I do is press "Ctrl and C"
Not sure if this will come through OK but here's todays final 1st & 2nd MLW data
| Name(1) | Name(2) |
| EVA'S TIME(IRE) | INDEPENDENT GIRL(IRE) |
| CATHCART CASTLE(GB) | DUQUESA(IRE) |
| BURMA ROCK(IRE) | SAVARONOLA(USA) |
| SUDDENLY SUSAN(IRE) | TOP CARE(USA) |
| RULING(IRE) | CATALPA SAIL(IRE) |
| JINKY(GB) | CAPE RAMBLER(GB) |
| PRIMA VISTA(GB) | BUCK BARROW(IRE) |
| SPLASH POINT(USA) | JARIDH(USA) |
| MAJOR MUSCARI(IRE) | LENJAWI PRIDE(GB) |
| MUJADY STAR(IRE) | CEIST NUA(IRE) |
| DAKIYAH(IRE) | VIA GALILEI(IRE) |
| APT MANOR(IRE) | STYLE OF CAMPILE(IRE) |
| CHARLESTON LADY(GB) | FORTUNATEENCOUNTER(FR) |
| POSTMAN(GB) | MY ONE WEAKNESS(IRE) |
| TIZ THE SHOT(IRE) | PIRATE CHEST(IRE) |
| AGONY AND ECSTASY(GB) | JORDAURA(GB) |
| LETS GO SUNSHINE(IRE) | KILLEENMORE(IRE) |
| MASTER MYLO(IRE) | BLUE LYRIC(GB) |
| LAY CLAIM(USA) | DIVINE CALL(GB) |
| ASTRAKHAM(IRE) | JEANGEORGES(IRE) |
| DR WINTRINGHAM(IRE) | JUST FIVE(IRE) |
| NATURAL HIGH(IRE) | DONNAS PALM(IRE) |
| CHARLIE SMIRKE(USA) | MUSTAKMIL(IRE) |
| POWERFUL MELODY(USA) | BECAUSEWECAN(USA) |
| FINAL FLASHBACK(IRE) | DUC DE SAVOIE(USA) |
| MISS EXHIBITIONIST(GB) | SHOW RAINBOW(GB) |
| CORSKEAGH ROYALE(IRE) | COOLAVANNY GAFFER(IRE) |
| THREE SPARROWS(IRE) | PROPHET IN A DREAM(GB) |
| TAKAJAN(IRE) | PAVEMENT GAMES(GB) |
| FAMOUS NAME(GB) | CHOOSE ME(IRE) |
| BARISTA(IRE) | MARY BOYLE(GB) |
| WISE OLD OWL(IRE) | WELLFORTH(IRE) |
| JOVIAL(IRE) | CAPTAIN COOL(IRE) |
| CAPTAIN KALLIS(IRE) | DESERT STRIKE(GB) |
| BOULAY(IRE) | WHITE DIAMOND(GB) |
| MR UDAGAWA(GB) | BERMONDSEY BOB(IRE) |
| MASDAR(IRE) | SKY PILOT(IRE) |
Red is a Winner - pink is a 2nd - Bold horse name is the favourite
As Bob recommends I too tend to steer clear of Maidens, bumper, novices races and these are coded so that I can exclude them when betting and for when I'm compiling my formulas for data that contributes to a winner.
I'm thinking I'll have a first look at my new formula soon.
Hours and hours have been spent by me in getting to this point, many formulas, many macros, much thought, much testing and recording each race, but my summer flat season was brilliant.
I don't listen to tips, read opinions or nothing - tis all in the numbers for me.
John
PS Secretariat is a fantasic movie
PPPS Just so you can see a snapshot of the complexity here is just one small formula in my spreadsheet
(SUM(BB2:BB17)-(IF(HW2-D2=0,0,1)*((HW2-D2)*998))-(IF(HW2>16.1,0,1)*(IF(D2<16.1,1,0) *( (16-D2)*998))))
and here's just one Macro
Sheets("Lay Data").Select
Cells.Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Sheets("P").Select
Cells.Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Sheets("---First 16---").Select
Cells.Select
Selection.Copy
Sheets("Lay Data").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("R:U").Select
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Font.ColorIndex = 3
Columns("O:O").Select
Selection.Font.ColorIndex = 3
Columns("M:M").Select
Selection.Font.ColorIndex = 5
Columns("P:P").Select
Selection.Font.ColorIndex = 3
Columns("BC:BC").Select
Selection.Delete Shift:=xlToLeft
Columns("BF:BF").Select
Selection.Delete Shift:=xlToLeft
Columns("BI:BI").Select
Selection.Delete Shift:=xlToLeft
Columns("BL:BL").Select
Selection.Delete Shift:=xlToLeft
Columns("BO:BO").Select
Selection.Delete Shift:=xlToLeft
Columns("BR:BR").Select
Selection.Delete Shift:=xlToLeft
Columns("BU:BU").Select
Selection.Delete Shift:=xlToLeft
Columns("BX:BX").Select
Selection.Delete Shift:=xlToLeft
Columns("CA:CA").Select
Selection.Delete Shift:=xlToLeft
Columns("CD:CD").Select
Selection.Delete Shift:=xlToLeft
Columns("CG:CG").Select
Selection.Delete Shift:=xlToLeft
Columns("CJ:CJ").Select
Selection.Delete Shift:=xlToLeft
Columns("CM:CM").Select
Selection.Delete Shift:=xlToLeft
Columns("CP:CP").Select
Selection.Delete Shift:=xlToLeft
Columns("CS:CS").Select
Selection.Delete Shift:=xlToLeft
Columns("CV:CV").Select
Selection.Delete Shift:=xlToLeft
Columns("CY:CY").Select
Selection.Delete Shift:=xlToLeft
Columns("DB:DB").Select
Selection.Delete Shift:=xlToLeft
Columns("DE:DE").Select
Selection.Delete Shift:=xlToLeft
Columns("DH:DH").Select
Selection.Delete Shift:=xlToLeft
Columns("DK:DK").Select
Selection.Delete Shift:=xlToLeft
Columns("DN:DN").Select
Selection.Delete Shift:=xlToLeft
Columns("DQ:DQ").Select
Selection.Delete Shift:=xlToLeft
Columns("DT:DT").Select
Selection.Delete Shift:=xlToLeft
Columns("DW:DW").Select
Selection.Delete Shift:=xlToLeft
Columns("DZ:DZ").Select
Selection.Delete Shift:=xlToLeft
Columns("EC:EC").Select
Selection.Delete Shift:=xlToLeft
Columns("EF:EF").Select
Selection.Delete Shift:=xlToLeft
Columns("EI:EI").Select
Selection.Delete Shift:=xlToLeft
Columns("EL:EL").Select
Selection.Delete Shift:=xlToLeft
Columns("EO:EO").Select
Selection.Delete Shift:=xlToLeft
Columns("ER:ER").Select
Selection.Delete Shift:=xlToLeft
Columns("EU:EU").Select
Selection.Delete Shift:=xlToLeft
Columns("EX:EX").Select
Selection.Delete Shift:=xlToLeft
Columns("EZ:EZ").Select
Selection.Delete Shift:=xlToLeft
Range("J2").Select
Sheets("Max").Select
Columns("AR:AR").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("N:N").Select
ActiveSheet.Paste
Selection.Font.ColorIndex = 0
Selection.Font.Bold = False
Columns("L:P").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns("J:J").ColumnWidth = 16.14
ActiveWindow.LargeScroll ToRight:=5
Columns("DZ:EY").Select
Selection.Delete Shift:=xlToLeft
Columns("EJ:FG").Select
Selection.Delete Shift:=xlToLeft
Columns("EA:EA").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 12
Range("J2").Select
Columns("R:DY").Select
Selection.Interior.ColorIndex = xlNone
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll ToRight:=-1
Columns("AX:AX").Select
Selection.Replace What:="999", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Max").Select
Columns("A:A").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("AW:AW").Select
ActiveSheet.Paste
Selection.Font.ColorIndex = 5
Columns("BB:BB").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("BB2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[77]<1,500,RC[-1])"
Range("BB2").Select
Selection.AutoFill Destination:=Range("BB2:BB1073"), Type:=xlFillDefault
Range("BB2:BB1073").Select
ActiveWindow.ScrollRow = 2
Range("BA1").Select
Selection.Copy
Range("BB1").Select
ActiveSheet.Paste
Columns("BB:BB").Select
Application.CutCopyMode = False
Selection.Copy
Columns("BB:BB").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BA:BA").Select
ActiveSheet.Paste
Columns("BB:BB").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BE:BE").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=14
Range("BE2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[74]<1,500,RC[-1])"
Range("BE2").Select
Selection.AutoFill Destination:=Range("BE2:BE1073"), Type:=xlFillDefault
Range("BE2:BE1073").Select
ActiveWindow.ScrollRow = 2
Range("BD1").Select
Selection.Copy
Range("BE1").Select
ActiveSheet.Paste
Columns("BE:BE").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BD:BD").Select
ActiveSheet.Paste
Columns("BE:BE").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BH:BH").Select
Selection.Insert Shift:=xlToRight
Range("BH2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[71]<1,500,RC[-1])"
Range("BH2").Select
Selection.AutoFill Destination:=Range("BH2:BH1073"), Type:=xlFillDefault
Range("BH2:BH1073").Select
ActiveWindow.ScrollRow = 2
Range("BG1").Select
Selection.Copy
Range("BH1").Select
ActiveSheet.Paste
Columns("BH:BH").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BG:BG").Select
ActiveSheet.Paste
Columns("BH:BH").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BK:BK").Select
Selection.Insert Shift:=xlToRight
Range("BK2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[68]<1,500,RC[-1])"
Range("BK2").Select
Selection.AutoFill Destination:=Range("BK2:BK1073"), Type:=xlFillDefault
Range("BK2:BK1073").Select
ActiveWindow.ScrollRow = 2
Range("BJ1").Select
Selection.Copy
Range("BK1").Select
ActiveSheet.Paste
Columns("BK:BK").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("BK:BK").Select
Application.CutCopyMode = False
Selection.Copy
Columns("BJ:BJ").Select
ActiveSheet.Paste
Columns("BK:BK").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Columns("BN:BN").Select
Selection.Insert Shift:=xlToRight
Range("BN2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[65]<1,500,RC[-1])"
Range("BN2").Select
Selection.AutoFill Destination:=Range("BN2:BN1073"), Type:=xlFillDefault
Range("BN2:BN1073").Select
ActiveWindow.ScrollRow = 2
Range("BM1").Select
Selection.Copy
Range("BN1").Select
ActiveSheet.Paste
Columns("BN:BN").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("BN:BN").Select
Application.CutCopyMode = False
Selection.Copy
Columns("BM:BM").Select
ActiveSheet.Paste
Columns("BN:BN").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BQ:BQ").Select
Selection.Insert Shift:=xlToRight
Range("BQ2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[62]<1,500,RC[-1])"
Range("BQ2").Select
Selection.AutoFill Destination:=Range("BQ2:BQ1073"), Type:=xlFillDefault
Range("BQ2:BQ1073").Select
Range("BP1").Select
Selection.Copy
Range("BQ1").Select
ActiveSheet.Paste
Columns("BQ:BQ").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BP:BP").Select
ActiveSheet.Paste
Columns("BQ:BQ").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=10
Columns("BT:BT").Select
Selection.Insert Shift:=xlToRight
Range("BT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[59]<1,500,RC[-1])"
Range("BT2").Select
Selection.AutoFill Destination:=Range("BT2:BT1073"), Type:=xlFillDefault
Range("BT2:BT1073").Select
Range("BS1").Select
Selection.Copy
Range("BT1").Select
ActiveSheet.Paste
Columns("BT:BT").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BS:BS").Select
ActiveSheet.Paste
Columns("BT:BT").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BW:BW").Select
Selection.Insert Shift:=xlToRight
Range("BW2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[56]<1,500,RC[-1])"
Range("BW2").Select
Selection.AutoFill Destination:=Range("BW2:BW1073"), Type:=xlFillDefault
Range("BW2:BW1073").Select
Range("BV1").Select
Selection.Copy
Range("BW1").Select
ActiveSheet.Paste
Columns("BW:BW").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BV:BV").Select
ActiveSheet.Paste
Columns("BW:BW").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("BZ:BZ").Select
Selection.Insert Shift:=xlToRight
Range("BZ2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[53]<1,500,RC[-1])"
Range("BZ2").Select
Selection.AutoFill Destination:=Range("BZ2:BZ1073"), Type:=xlFillDefault
Range("BZ2:BZ1073").Select
Range("BY1").Select
Selection.Copy
Range("BZ1").Select
ActiveSheet.Paste
Columns("BZ:BZ").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("BY:BY").Select
ActiveSheet.Paste
Columns("BZ:BZ").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CC:CC").Select
Selection.Insert Shift:=xlToRight
Range("CC2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[50]<1%,500%,RC[-1])"
Range("CC2").Select
Selection.AutoFill Destination:=Range("CC2:CC1073"), Type:=xlFillDefault
Range("CC2:CC1073").Select
Range("CB1").Select
Selection.Copy
Range("CC1").Select
ActiveSheet.Paste
Columns("CC:CC").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CB:CB").Select
ActiveSheet.Paste
Columns("CC:CC").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=12
Columns("CF:CF").Select
Selection.Insert Shift:=xlToRight
Range("CF2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[47]<1%,500%,RC[-1])"
Range("CF2").Select
Selection.AutoFill Destination:=Range("CF2:CF1073"), Type:=xlFillDefault
Range("CF2:CF1073").Select
Range("CE1").Select
Selection.Copy
Range("CF1").Select
ActiveSheet.Paste
Columns("CF:CF").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CE:CE").Select
ActiveSheet.Paste
Columns("CF:CF").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Range("CI2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[44]<1%,500%,RC[-1])"
Range("CI2").Select
Selection.AutoFill Destination:=Range("CI2:CI1073"), Type:=xlFillDefault
Range("CI2:CI074").Select
Range("CH1").Select
Selection.Copy
Range("CI1").Select
ActiveSheet.Paste
Columns("CI:CI").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CH:CH").Select
ActiveSheet.Paste
Columns("CI:CI").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CL:CL").Select
Selection.Insert Shift:=xlToRight
Range("CL2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[41]<1%,500%,RC[-1])"
Range("CL2").Select
Selection.AutoFill Destination:=Range("CL2:CL1073"), Type:=xlFillDefault
Range("CL2:CL1073").Select
Range("CK1").Select
Selection.Copy
Range("CL1").Select
ActiveSheet.Paste
Columns("CL:CL").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CK:CK").Select
ActiveSheet.Paste
Columns("CL:CL").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CO:CO").Select
Selection.Insert Shift:=xlToRight
Range("CO2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[38]<1%,500%,RC[-1])"
Range("CO2").Select
Selection.AutoFill Destination:=Range("CO2:CO1073"), Type:=xlFillDefault
Range("CO2:CO1073").Select
Range("CN1").Select
Selection.Copy
Range("CO1").Select
ActiveSheet.Paste
Columns("CO:CO").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CN:CN").Select
ActiveSheet.Paste
Columns("CO:CO").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=13
Columns("CR:CR").Select
Selection.Insert Shift:=xlToRight
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("CR2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[35]<1%,500%,RC[-1])"
Range("CR2").Select
Selection.AutoFill Destination:=Range("CR2:CR761"), Type:=xlFillDefault
Range("CR2:CR761").Select
Range("CQ1").Select
Selection.Copy
Range("CR1").Select
ActiveSheet.Paste
Columns("CR:CR").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CQ:CQ").Select
ActiveSheet.Paste
Columns("CR:CR").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CU:CU").Select
Selection.Insert Shift:=xlToRight
Range("CU2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[32]<1%,500%,RC[-1])"
Range("CU2").Select
Selection.AutoFill Destination:=Range("CU2:CU761"), Type:=xlFillDefault
Range("CU2:CU761").Select
Range("CT1").Select
Selection.Copy
Range("CU1").Select
ActiveSheet.Paste
Columns("CU:CU").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("CT:CT").Select
ActiveSheet.Paste
Columns("CU:CU").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("CX:CX").Select
Selection.Insert Shift:=xlToRight
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("CX2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[29]<1%,500%,RC[-1])"
Range("CX2").Select
Selection.AutoFill Destination:=Range("CX2:CX761"), Type:=xlFillDefault
Range("CX2:CX761").Select
Range("CW1").Select
Selection.Copy
Range("CX1").Select
ActiveSheet.Paste
Columns("CX:CX").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("CX:CX").Select
Application.CutCopyMode = False
Selection.Copy
Columns("CW:CW").Select
ActiveSheet.Paste
Columns("CX:CX").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DA:DA").Select
Selection.Insert Shift:=xlToRight
Range("DA2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[26]<1%,500%,RC[-1])"
Range("DA2").Select
Selection.AutoFill Destination:=Range("DA2:DA761"), Type:=xlFillDefault
Range("DA2:DA761").Select
Range("CZ1").Select
Selection.Copy
Range("DA1").Select
ActiveSheet.Paste
Columns("DA:DA").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("DA:DA").Select
Application.CutCopyMode = False
Selection.Copy
Columns("CZ:CZ").Select
ActiveSheet.Paste
Columns("DA:DA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=10
Columns("DD:DD").Select
Selection.Insert Shift:=xlToRight
Range("DD2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[23]<1%,500%,RC[-1])"
Range("DD2").Select
Selection.AutoFill Destination:=Range("DD2:DD761"), Type:=xlFillDefault
Range("DD2:DD761").Select
Range("DC1").Select
Selection.Copy
Range("DD1").Select
ActiveSheet.Paste
Columns("DD:DD").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DC:DC").Select
ActiveSheet.Paste
Columns("DD:DD").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DG:DG").Select
Selection.Insert Shift:=xlToRight
Range("DG2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[20]<1%,500%,RC[-1])"
Range("DG2").Select
Selection.AutoFill Destination:=Range("DG2:DG761"), Type:=xlFillDefault
Range("DG2:DG761").Select
Range("DF1").Select
Selection.Copy
Range("DG1").Select
ActiveSheet.Paste
Columns("DG:DG").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DF:DF").Select
ActiveSheet.Paste
Columns("DG:DG").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DJ:DJ").Select
Selection.Insert Shift:=xlToRight
Range("DJ2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[17]<1,500,RC[-1])"
Range("DJ2").Select
Selection.AutoFill Destination:=Range("DJ2:DJ761"), Type:=xlFillDefault
Range("DJ2:DJ761").Select
Range("DI1").Select
Selection.Copy
Range("DJ1").Select
ActiveSheet.Paste
Columns("DJ:DJ").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DI:DI").Select
ActiveSheet.Paste
Columns("DJ:DJ").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DM:DM").Select
Selection.Insert Shift:=xlToRight
Range("DM2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[14]<1,500,RC[-1])"
Range("DM2").Select
Selection.AutoFill Destination:=Range("DM2:DM761"), Type:=xlFillDefault
Range("DM2:DM761").Select
Range("DL1").Select
Selection.Copy
Range("DM1").Select
ActiveSheet.Paste
Columns("DM:DM").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DL:DL").Select
ActiveSheet.Paste
Columns("DM:DM").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DP:DP").Select
Selection.Insert Shift:=xlToRight
Range("DP2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[11]<1%,500%,RC[-1])"
Range("DP2").Select
Selection.AutoFill Destination:=Range("DP2:DP761"), Type:=xlFillDefault
Range("DP2:DP761").Select
Range("DO1").Select
Selection.Copy
Range("DP1").Select
ActiveSheet.Paste
Columns("DP:DP").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DO:DO").Select
ActiveSheet.Paste
Columns("DP:DP").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DS:DS").Select
Selection.Insert Shift:=xlToRight
Range("DS2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]<1%,500%,RC[-1])"
Range("DS2").Select
Selection.AutoFill Destination:=Range("DS2:DS761"), Type:=xlFillDefault
Range("DS2:DS761").Select
Range("DR1").Select
Selection.Copy
Range("DS1").Select
ActiveSheet.Paste
Columns("DS:DS").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DR:DR").Select
ActiveSheet.Paste
Columns("DS:DS").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DV:DV").Select
Selection.Insert Shift:=xlToRight
Range("DV2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[5]<1%,500%,RC[-1])"
Range("DV2").Select
Selection.AutoFill Destination:=Range("DV2:DV761"), Type:=xlFillDefault
Range("DV2:DV761").Select
Range("DU1").Select
Selection.Copy
Range("DV1").Select
ActiveSheet.Paste
Columns("DV:DV").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DU:DU").Select
ActiveSheet.Paste
Columns("DV:DV").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("DY:DY").Select
Selection.Insert Shift:=xlToRight
Range("DY2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]<1%,500%,RC[-1])"
Range("DY2").Select
Selection.AutoFill Destination:=Range("DY2:DY761"), Type:=xlFillDefault
Range("DY2:DY761").Select
Range("DX1").Select
Selection.Copy
Range("DY1").Select
ActiveSheet.Paste
Columns("DY:DY").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Columns("DX:DX").Select
ActiveSheet.Paste
Columns("DY:DY").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Max").Select
Columns("AT:AT").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("DY:DY").Select
ActiveSheet.Paste
Sheets("Max").Select
Columns("AT:AU").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lay Data").Select
Columns("DV:DV").Select
ActiveSheet.Paste
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Max").Select
Columns("AT:AU").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("DS:DS").Select
ActiveSheet.Paste
Columns("DP:DP").Select
ActiveSheet.Paste
Columns("DM:DM").Select
ActiveSheet.Paste
Columns("DJ:DJ").Select
ActiveSheet.Paste
Columns("DG:DG").Select
ActiveSheet.Paste
Columns("DD:DD").Select
ActiveSheet.Paste
Columns("DA:DA").Select
ActiveSheet.Paste
Columns("CX:CX").Select
ActiveSheet.Paste
Columns("CU:CU").Select
ActiveSheet.Paste
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Max").Select
Columns("AT:AU").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("CR:CR").Select
ActiveSheet.Paste
Columns("CO:CO").Select
ActiveSheet.Paste
Columns("CL:CL").Select
ActiveSheet.Paste
Columns("CI:CI").Select
ActiveSheet.Paste
Columns("CF:CF").Select
ActiveSheet.Paste
Columns("CC:CC").Select
ActiveSheet.Paste
Columns("BZ:BZ").Select
ActiveSheet.Paste
Columns("BW:BW").Select
ActiveSheet.Paste
Columns("BT:BT").Select
ActiveSheet.Paste
Columns("BQ:BQ").Select
ActiveSheet.Paste
Columns("BN:BN").Select
ActiveSheet.Paste
Columns("BK:BK").Select
ActiveSheet.Paste
Columns("BH:BH").Select
ActiveSheet.Paste
Columns("BE:BE").Select
ActiveSheet.Paste
Columns("BB:BB").Select
ActiveSheet.Paste
Sheets("Max").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("AY:AY").Select
ActiveSheet.Paste
Sheets("Max").Select
Columns("AQ:AQ").Select
Selection.Copy
Sheets("Lay Data").Select
ActiveWindow.LargeScroll ToRight:=3
Columns("CF:CF").Select
ActiveSheet.Paste
Columns("CI:CI").Select
ActiveSheet.Paste
Columns("CL:CL").Select
ActiveSheet.Paste
Columns("CO:CO").Select
ActiveSheet.Paste
Columns("CR:CR").Select
ActiveSheet.Paste
Columns("CU:CU").Select
ActiveSheet.Paste
Columns("CX:CX").Select
ActiveSheet.Paste
Columns("DA:DA").Select
ActiveSheet.Paste
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Max").Select
Columns("Aq:Aq").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("DD:DD").Select
ActiveSheet.Paste
Columns("DG:DG").Select
ActiveSheet.Paste
Columns("DJ:DJ").Select
ActiveSheet.Paste
Columns("DP:DP").Select
ActiveSheet.Paste
Columns("DS:DS").Select
ActiveSheet.Paste
Columns("DV:DV").Select
ActiveSheet.Paste
Columns("DY:DY").Select
ActiveSheet.Paste
Sheets("Lay Data").Select
Columns("AY:AY").Select
ActiveSheet.Paste
Sheets("---First 16---").Select
Columns("fD:gl").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("DZ:DZ").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=9
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("EC:EC").Select
Selection.Delete Shift:=xlToLeft
Columns("EF:EF").Select
Selection.Delete Shift:=xlToLeft
Columns("EI:EI").Select
Selection.Delete Shift:=xlToLeft
Columns("EL:EL").Select
Selection.Delete Shift:=xlToLeft
Columns("EO:EO").Select
Selection.Delete Shift:=xlToLeft
Columns("ER:ER").Select
Selection.Delete Shift:=xlToLeft
Columns("EU:EU").Select
Selection.Delete Shift:=xlToLeft
Columns("EX:EX").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=5
Columns("EZ:EZ").Select
Selection.Delete Shift:=xlToLeft
Columns("EZ:EZ").Select
Sheets("Max").Select
Columns("AU:AU").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lay Data").Select
Columns("DZ:DZ").Select
ActiveSheet.Paste
Columns("EC:EC").Select
ActiveSheet.Paste
Columns("EF:EF").Select
ActiveSheet.Paste
Columns("EI:EI").Select
ActiveSheet.Paste
Columns("EL:EL").Select
ActiveSheet.Paste
Columns("EO:EO").Select
ActiveSheet.Paste
Columns("ER:ER").Select
ActiveSheet.Paste
Columns("EU:EU").Select
ActiveSheet.Paste
Columns("DX:EV").Select
Range("EV1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Max").Select
Columns("AQ:AQ").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lay Data").Select
Columns("EB:EB").Select
ActiveSheet.Paste
Columns("EE:EE").Select
ActiveSheet.Paste
Columns("EH:EH").Select
ActiveSheet.Paste
Columns("EK:EK").Select
ActiveSheet.Paste
Columns("EN:EN").Select
ActiveSheet.Paste
Columns("EQ:EQ").Select
ActiveSheet.Paste
Columns("ET:ET").Select
ActiveSheet.Paste
Columns("EW:EW").Select
ActiveSheet.Paste
Columns("DX:EY").Select
Range("EY1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("Max").Select
Columns("A:A").Select
Selection.Copy
Sheets("Lay Data").Select
Columns("EX:EX").Select
ActiveSheet.Paste
Columns("EV:EZ").Select
Range("EZ1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.LargeScroll ToRight:=2
Columns("FI:GG").Select
Range("GG1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.LargeScroll ToRight:=-6
Range("J2").Select
Sheets("LO").Select
Range("R1:AV2").Select
Selection.Copy
Sheets("Lay Data").Select
Range("R1").Select
ActiveSheet.Paste
Range("R2:AV2").Select
Application.CutCopyMode = False
Selection.Copy
Range("R3:R1073").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Lay Data").Select
Range("J2").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("R:AV").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("AV:AV").Select
Columns("AV:AV").EntireColumn.AutoFit
Columns("AW:DY").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("fh2") _
, Order2:=xlAscending, Key3:=Range("H2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("P").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AV2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells.Select
Selection.Copy
Sheets("Lays").Select
Cells.Select
ActiveSheet.Paste
Rows("15:8947").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("33:495").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("J2").Select
Sheets("P").Select
Cells.Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Sheets("---First 16---").Select
Range("EB2").Select
ActiveWindow.ScrollColumn = 12
Range("J2").Select
Sheets("Lay Data").Select
Range("J2").Select
3 comments:
Hi John --- made my head turn to pudding - number crunching is just not for me - we have had this conversation before and you know that to be true - First off not many guys use the Visual Form Book on here - that does not mean you are not the most generous of souls in putting up your spread sheet calcs- but those who do want to use Excel would not have the information you have at input. Most of the work you already have on the stats are provided by VfB in terms of each of your headings - but they are nowhere else so using your calculations would be next to impossible.
So far (i think) your the only number cruncher on the blog - yes i remember the accuracy and predicted first and seconds - even your massive record with lay horses to make it worthwhile but the old forum there were only a few crunchers - the rest of us having trainers, jocks breeding in sufficient information on the VfB straight analyser.
I am not sure how anyone without the book can interpret and or use the information but am sure you will be able to answer any questions (and put up the results on your selections) I in turn will always look at the end table produced - thanks Mate - hope the Art Gallery is going great business and all your missing is the odd pint of Old and filthy - love to Candy
Bob
Well I had too many pints of real ale recently, was over in the UK late Sept and had the chance to go to a Beer fesitival - I pigged out.
Can get bottled Old Speckled Hen here but not the same as being in a pub with buddies - but the sunshine more than makes up.
Art Gallery doing well, Candy is happy and that's the main thing.
My Excel system reflects "System Analyser" theories - I analyse 1st, 2nd and 3rd bests of all the headers and crunch these together in formulas with extra pointers to give me my MLW lists. The whole thing is huge - 35Mb.
The resulting sheet is what it's all about & this used in conjunction with notes, breeding, trends, inside form study etc etc would be just so profitable. I get a lot of winners from the first two selections and it's just a case of then deciding which is better from non numbers perspective.
As time allows I can post my lists but it's already getting a little too early for me with races starting at 1.30pm. Soon we'll be 4hrs different.
John
Wow - welcome - it all looks very impressive but I don't understand any of it - I like the pretty colours though ;)
I will follow your table of end results with interest - good luck
Post a Comment