Data sort in excel

Please post any questions regarding the program here.

Moderator: 2020vision

Data sort in excel

Postby menlolot » Thu Apr 02, 2009 7:12 am

I have my settings in BA to sort the field according to back prices. When I go back to excel its in numerical order. Please explain how to keep ascending prices in excel.

Thanks
menlolot
 
Posts: 21
Joined: Thu Apr 02, 2009 7:06 am

Postby GaryRussell » Thu Apr 02, 2009 4:44 pm

You cannot change the sort order of data exported to Excel. You can however use the rank function in Excel so that the selection with the lowest back price is ranked 1, etc.

Download an example here http://www.gruss-software.co.uk/Excel_S ... xample.xls

In the above example column AA contains the rank.
User avatar
GaryRussell
Site Admin
 
Posts: 9676
Joined: Fri Nov 18, 2005 8:09 pm
Location: Birmingham, UK

Will Sort Order (and contents) ever change?

Postby Bumble » Tue Aug 03, 2010 9:02 am

Hi folks, great product. I'm learning how to apply VBA and Excel to trading. I'm planning some routines and would really appreciate knowing something.

I log a market to Excel and get a list of runners in a certain order, starting from A5 by default. I've found that if I go back to Gruss and change the Sort Order it doesn't change the order of runners in Excel.

This is exactly what I want. My question is whether there are any situations in which the order of runners in Excel might change. For example, if a non-runner is declared could that lead to it being removed from the Excel log of runners and prices?

What I'm aiming to do is set up a couple of arrays, eg one of prices and another from MyBets. I'd like to index those arrays with a runner number. When working from data in both arrays, I'd rather avoid continually searching through to match a runner name. I'd like to know that if "My Certain Winner" is at, say, cells A10:P10 when I start logging prices to Sheet 1 then it will stay there (so I can use an index into my arrays and store it to cross-refer).

Are there any circumstances in which "My Certain Winner" might start having its prices logged to, say, A9:P9?

I'd guess that if I start logging, change my selected market in Gruss to a different race and then come back, everything might have changed. My question assumes that I don't change the market that I'm viewing in Gruss (although I might change the Sort Order).

Many thanks
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am

Postby GaryRussell » Tue Aug 03, 2010 9:07 am

It will never change the order and it was designed like this with your type of requirement in mind. You are correct in saying if you changed markets and came back then it can change, but as long as you remain on the market the order will stay the same. When a non runner is removed the name will have "(NR)" appended and the prices will be output as zero.
User avatar
GaryRussell
Site Admin
 
Posts: 9676
Joined: Fri Nov 18, 2005 8:09 pm
Location: Birmingham, UK

Re: Will Sort Order (and contents) ever change?

Postby osknows » Tue Aug 03, 2010 11:26 am

Bumble wrote:What I'm aiming to do is set up a couple of arrays, eg one of prices and another from MyBets. I'd like to index those arrays with a runner number. When working from data in both arrays, I'd rather avoid continually searching through to match a runner name. I'd like to know that if "My Certain Winner" is at, say, cells A10:P10 when I start logging prices to Sheet 1 then it will stay there (so I can use an index into my arrays and store it to cross-refer).


One option is to ignore array indexing and use WorksheetFunction.Match which can be quicker than looping arrays

Eg. if in excel data in cells are
A5 = Horse 1
A6 = Horse 2
.
. etc
A50 = Horse 46

And you want to find the location of "Horse 15" in the array

Code: Select all
Sub test()

load_array = Range("a5:z100").Value
array_index = WorksheetFunction.Match("Horse 15", WorksheetFunction.Index(load_array, 0, 1), 0)

End Sub


array_index returns 15 and you can use this to return any other data across the same index of the array

WorksheetFunction.Index(load_array, 0, 1) - slices the array to give column A, WorksheetFunction.Index(load_array, 0, 2) gives column B etc
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby osknows » Tue Aug 03, 2010 12:54 pm

Also, I think WorksheetFunction.Match is even quicker for a range than an array so this should be even quicker


Code: Select all
Sub test()

Set load_array = Range("a5:z100")
array_index = WorksheetFunction.Match("Horse 15", WorksheetFunction.Index(load_array, 0, 1), 0)


End Sub
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby osknows » Tue Aug 03, 2010 1:37 pm

I'm probably talking to myself by now :) but out of curiosity I wanted to see what the performance of each method was

The code below measures the ticks of each method.

Place the text "Horse 15" in cell A65000 for it to search

My results show
Range Search: 0
Array Search: 547
Array Loop: 250
Excel Loop: 610

So range method is lightening fast
Strangely it is quicker to loop the array than slice and MATCH the array
Excel looping is slowest as expected

Code: Select all
Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub test()
Dim nTime
 
nTime = GetTickCount
Set load_array = Range("a5:z65000")
array_index = WorksheetFunction.Match("Horse 15", WorksheetFunction.Index(load_array, 0, 1), 0)

rngtimer = GetTickCount - nTime


nTime = GetTickCount
load_array = Range("a5:z65000").Value
array_index = WorksheetFunction.Match("Horse 15", WorksheetFunction.Index(load_array, 0, 1), 0)

arraytimer = GetTickCount - nTime


nTime = GetTickCount
load_array = Range("a5:z65000").Value
For a = LBound(load_array) To UBound(load_array)
If load_array(a, 1) = "Horse 15" Then
array_index = a
End If
Next

arraylooptimer = GetTickCount - nTime

nTime = GetTickCount

For a = 1 To 65000
If Range("a5").Offset(a, 0) = "Horse 15" Then
array_index = a
End If
Next

excelooptimer = GetTickCount - nTime

MsgBox ("Range Search: " & rngtimer & vbCrLf & _
"Array Search: " & arraytimer & vbCrLf & _
"ArrayLoop Search: " & arraylooptimer & vbCrLf & _
"ExcelLoop Search: " & excelooptimer)
End Sub
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby Bumble » Tue Aug 03, 2010 9:17 pm

Gary - many thanks for clarifying things (glad to hear it's deliberate). That gives me a solid basis for proceeding.

osknows - you are a star. Your speed test says "rethink using arrays" and you've pointed me towards some Excel functions I've never used. Food for thought. I'm very new to VBA though I've used Excel and formulae for years. I've seen a few posts (here and in another place) with people setting up formulae in Q5 - and I'm sure that VBA will be quicker and easier (though my programming needs to be brought into the 21st century).
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am

Postby osknows » Tue Aug 03, 2010 9:42 pm

You can still use VBA but in some instances it's quicker to define the data as a range object rather than data in an array. If your using the Private Sub Worksheet_Change event (ByVal Target as Range) the range updated by BA is already in the Target variable as a range :wink:

Also, if you use the speed test and reduce the range to A5:A100 there's hardly any difference between all methods.
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby Bumble » Fri Aug 06, 2010 7:59 pm

Thanks for this osknows. I wonder if I could tap your wealth of knowledge again? From your example above, you are taking a range of cells and storing them in an array, using that range array for analysis.

Is there any way of doing that but selectively? For example, going through a set of worksheet rows containing bet details and picking all with, say, slection name = "My Certain Winner" - so I can then just work with an array of bets on that particular selection?

Many thanks
Bumble
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am

Postby osknows » Fri Aug 06, 2010 9:11 pm

Hi Bumble,

An array is the quickest way to read and write data from excel, especially if you pick up a large range to read and write once only. The range method is the quickest way to filter/match/search etc for text or values.

Below shows a few methods that can be used but it really depends on what your trying to do and how fast that determines the best methods

Code: Select all
Sub test()
Dim newarray() As Variant

'combine several ranges into one range
Set rnga = Range("A1:Q1")              'define range explicitly
Set rngb = Range(Range("A5"), Range("A5").Offset(0, 11)) 'using offset to define range
Set rngc = Range("A10:Q10")

Set rngd = Union(rnga, rngb, rngc) ' combine ranges into one range


'==========================

'quickest method allows rng object & array
Set rnga = Range("A1:Q1")
arraya = rnga
'use the rnga object for quickest method to filter/search/index
'use the arraya array to calculate values and write back
Range("A1:Q1").Value = arraya

'=============================================

'slower method of filling a new array searches for value "x" and fills new array with matches
Set rnga = Range("A1:Q10")
Count = Application.CountIf(rnga, "x")  'count instances of "x"
ReDim newarray(1 To Count, 1 To rnga.Columns.Count)
newarraycount = 1
For i = 1 To rnga.Rows.Count
    If rnga.Cells(i, 1) = "x" Then
    For j = 1 To rnga.Columns.Count
        newarray(newarraycount, j) = rnga.Cells(i, j)
    Next
    newarraycount = newarraycount + 1
    End If
Next
Range(Range("A21"), Range("A21").Offset(UBound(newarray, 1) - 1, UBound(newarray, 2) - 1)) = newarray

End Sub
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby Bumble » Sat Aug 07, 2010 8:36 am

Hi osknows. Thanks for that, very infrormative.

Your last example achievs the result I was looking for - an array loaded with all the data for selection 'x'.

regards
Bumble
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am


Return to Help

Who is online

Users browsing this forum: Bing [Bot] and 36 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

cron