Using "RANK" inside vba WorkSheetFunction

[Fixador]

Hi All, with a view to trying 3 horse dutching, i am trying to sort out the data . Basically i have a snapshot of BA output into Excel , at some minutes before the target OFF. The racedata, i append into a list down the spreadsheet , with a blank line between racedatas

However, i have run into a jam , quite early on................

Code: Select all

Private Sub CommandButton1_Click()
For i = 9 To 5130 Step 1
    If Cells(i, 1) = "" Then ' i know i am in the blank row between racedatas, first runner is 5 rows down
    ' Col B contains the odds, Col C contains "Nos of Runners" - from CommandButton1
        NosRunners = Cells(i + 5, 3).Value
        For j = (i + 5) To (NosRunners + i + 5) Step 1   'flicks down rows containing runners in this race
        'go find rank of odds ( lowest odds = 1)
       ' cells(j,15)= worksheetfunction.Rank(cells(j,2),cells(i+5,2):cells(NosRunners+i+5),1)
       
       
        'Outcome of a runner is in col D, how did the ranked runner perform ?
       
       
       
        Next j
       



Next i
End Sub


Worksheetfunction line , vba reported "Compile error : expected : list seperator or ) "

":" is the source of the problem.

Any suggestions ?

This code is a bit of a ' feel in the dark' to sort out the info in each race, so , to later apply dutching strategy

[BlueBoy]

Should there be an endif in there?

[Fixador]

Hello Blueboy, There will be many more "If" statements as well as "EndIf" statements before the coding is finished !

Its the Worksheet function thats giving me grief, I suspect it wants a range coded as say "B14:B20" , but as the vba is written it doesnt suggest that

[GeorgeUK]

Hi Fixador

Hate to say it, but that code totally baffled me.
The layout may be a little easier to understand like this (or maybe it's just me)

Code: Select all

Private Sub CommandButton1_Click()
Dim i As Range
Dim j As Range

For Each i In Range(Range("A9"), Range("A65536").End(xlUp))
    If i = Empty Then
        NosRunners = i.Offset(5, 3)
        For Each j In Range(i.Offset(5, 0), Range(Cells(i.Row + 5 + NosRunners)))
        '
        '
        '
        '
        Next j
    End If
Next i
End Sub

I think your problem was that the cells were not grouped quite right.
Cells(j, 15).Value = WorksheetFunction.Rank(Cells(j, 2), Range(Cells(i + 5, 2), Cells(NosRunners + i + 5, 2)), 1)

I put the 2nd and 3rd cells mentioned into a range, so it knows you are looking at from x to y
and i think you missed off the last cell's column number.

Hope this is of use.

[Fixador]

Hi GeorgeUK - thanks for chipping in , I did say i was feeling my way in the dark ( translates as : i dont know how to do this ! ). My Excel is rather limited - seems from your code , i need to look up on declaring / use of arange as a variable !

Early days yet !

I came across a webpage with dutching formula , thought I'd give it a whirl on my historical data, whilst we are in that dismal period when the field is filled with 3 legged horses are on the tracks

http://home2.btconnect.com/jmandjp/Trading/dutching.htm

[GeorgeUK]

Nothing too wrong with what you were doing - it would have worked.
As you said, it was just the ":" that it didn't understand.

I think just adjusting that rank line is all that was needed.

I think most of the formulas etc from that workbook will be covered on the forum somewhere.
Just holler if you need any help.

[Fixador]

Hello George, I think the basic issue is , i dont have a plan for extracting the data out from each race , ie, the 3 shortest odds and their outcomes into a simple range of cells

This is what i am feeling my way toward

I reverted to my code

Code: Select all

Private Sub CommandButton3_Click()
Dim i As Integer, j As Integer, NosRunners As Integer
For i = 9 To 5130 Step 1
    If Cells(i, 1) = "" Then ' i know i am in the blank row between racedatas, first runner is 5 rows down
    ' Col B contains the odds, Col C contains "Nos of Runners" - from CommandButton1
        NosRunners = Cells(i + 5, 3).Value
        For j = (i + 5) To (NosRunners + i + 5) Step 1   'flicks down rows containing runners in this race
            Debug.Print Cells(j, 1), Cells(j, 2) 'ok to here
             '
        Next j
    End If
Next i

End Sub

But ofcourse, now i hit a jam , since i can only refer to a single value of j, ie , i am scuppered , if i want to call Worksheetfunction.Small ( Rank ), since i cant insert a range into the parameters of Worksheetfunction.Small ( or Rank ) in term of j

So, forget worksheetfunctions , instead , i need to get the macro to test for the min value , 2nd min value, 3rd min value. I think this means copying my range of odds values , and outcomes elsewhere, and then sorting the array ....... i have the code for that lurking around for array sort .somewhere !

[Fixador]

DEVICE FOR SORTING WITHIN A 2-D ARRAY ( sorts first column, and carrys 2nd column through )

Code: Select all

Option Explicit
Option Base 1
Dim thearray()
Private Sub CommandButton1_Click()
Dim thearray() As Single    ' Create the array.
    Dim rw As Integer, cl As Integer
    rw = 5
    cl = 2
    'populate array
    ReDim thearray(rw, cl)  ' ( rows,columns)
     For rw = 1 To 5
        For cl = 1 To 2
            thearray(rw, cl) = Cells(rw, cl).Value
        Next cl
    Next rw
    'print array to ensure we have taken it in properly
    For rw = 1 To 5
        For cl = 1 To 2
             Cells(rw + 10, cl).Value = thearray(rw, cl)
        Next cl
    Next rw
    ' Sort the Array and display the values in order.
    SelectionSort thearray()
    'print out new order below orginal
    For rw = 1 To UBound(thearray())
        For cl = 1 To 2
             Cells(rw + 20, cl).Value = thearray(rw, cl)
        Next cl
    Next rw
End Sub

Option Explicit
Option Base 1
Dim TempArray() As Single
Function SelectionSort(TempArray)
    Dim MaxVal As Single, MaxIndex As Integer, MaxValCompanion As Single
    Dim i, j As Integer
    ' Step through the elements in the array starting with the last element in the array.
    For i = UBound(TempArray) To 1 Step -1
    ' Set MaxVal to the element in the array and save the index of this element as MaxIndex.
        MaxVal = TempArray(i, 1)
        MaxValCompanion = TempArray(i, 2)
        MaxIndex = i        ' Loop through the remaining elements to see if any islarger than MaxVal.
                             'if it is then set this element to be the new MaxVal.
        For j = 1 To i
            If TempArray(j, 1) < MaxVal Then ' have found max value !
                MaxVal = TempArray(j, 1)
                MaxValCompanion = TempArray(j, 2)
                MaxIndex = j
            End If
        Next j        ' If the index of the largest element is not i, then exchange this element with element i.
        If MaxIndex < i Then
            TempArray(MaxIndex, 1) = TempArray(i, 1)
            TempArray(i, 1) = MaxVal
            TempArray(MaxIndex, 2) = TempArray(i, 2)
            TempArray(i, 2) = MaxValCompanion
        End If
    Next i
End Function


[GeorgeUK]

I think i've missed something somewhere. You may be overthinking this a bit.

You are wanting to dutch the 3 smallest values? Is this right?
If so, then all you need is the code to find the ranges firstMin, scndMin and thrdMin

Code: Select all

Sub test()
Dim firstmin As Range, scndmin As Range, thrdmin As Range, i As Range

Set myrange = Range(Range("B3"), Range("B65536").End(xlUp)) 'if B had the odds
For Each i In myrange
    x = WorksheetFunction.Rank(i.Value, myrange, 1)
    If x = 1 Then Set firstmin = i
    If x = 2 Then Set scndminmin = i
    If x = 3 Then Set thrdmin = i
Next i
'now you know the cells that have the 3 lowest values.
'To bet from this, i'd just use offset and you can calculate the stake required
End Sub

[Fixador]

Oh GeorgeUk - how could you !

re yr code : thats far too much shorter piece of code than i have embarked upon. !!!!!!

I had just finished rehashing it to accept mixed datatypes , ie dim array as varient ! Unfortunately , it gives the max odds at the top of the list , but that is no great deal .

I think my problem is i learnt BASIC in the 1970's and really havent moved on much ! Range as a datatype ? Woosh - over my old head !

Yes, i am pratting around , looking to dutch 3 lowest odds on some old data intially...........

ok, i will give yrs a whirl

[Fixador]

Georgeuk, the downside of not understanding yr code, is i cant debug it, and there seems to be a bug, it is returning i.Value as empty.........any suggestions ? I think i can load sample data somewhere you can access it ....back later

The good news is : my huge bit of lashed together code is working,

[GeorgeUK]

That's, like, sooo unfair

typo: scndminmin should be scndmin

put this under the notes at the end

Code: Select all

Range("D1").Value = firstmin.Value & "  " & firstmin.Address
Range("D2").Value = scndmin.Value & "  " & scndmin.Address
Range("D3").Value = thrdmin.Value & "  " & thrdmin.Address

Just incase you didn't know - if you use F8, you can step through the code a line at a time and hover the cursor over different parts to see the values it is looking at.

Congrats on the code working. For me that would just mean even more typos

[Fixador]

Hello Georgeuk, sorry for the delay , have been testing this dutching 3 shortest runners to rules thing - nothing to get overwhelmingly excited about , hmm , thats not strictly true, but there are complications that i cant see a way out of , could be seasonality factors. At the mo, only way to keep the papertrading equity lsp curve gently upward, is to pretty much supress activity So probably end up in the bin - like wot most 'ideas' do !

Anyway, will have a look/whirl at yr code soon. Reckon it will be off to college in the evenings this winter - to learn excel properly , so i can understand this 'joined up' code

[Fixador]

Ok, so its being a long time picking up on this !

GeorgeUK's code works fine , but i have an issue with using 'RANK'

Perhaps better to explain it via an example:-

Say in a race, you wanted to find the the first 3 favs.

As long as the 3 runners all have DIFFERENT ODDS, then using RANK is fine.

BUT : IF you have 2 jt favs, RANK cant do it , it will return ONE of the 1st favs, and the third fav. AND declare that there is NO 2nd Fav

So, say you wanted to be sure that the first 3 favs are running : Then the answer would be : No -they arent.

Afraid, going to have to revert to my monster code ... ..... Unless GeorgeUK , or someone else can come to the rescue !

Please !

[MarkRussell]

Hi,

You can create a rank tie break column.

Set the first cell in the tie break column to 0.01 then increment the subsequent cells by 0.01

Add the cells in your tie break column to the actual ranking to create unique values e.g. if you had 2 ranked at 1 the new values would be 1.01 and 1.02

Then rank on these unique values and you have your ranking.

Hope that makes sense.

Cheers,
Mark