gruss-software.co.uk Forum Index The Ultimate Betting Exchange Software
gruss-software.co.uk
FAQ FAQ Search Search Memberlist Memberlist Usergroups Usergroups
Register:: Log in Log in to check your private messages


Post new topic  Reply to topic
 Data sort in excel « View previous topic :: View next topic » 
Author Message
menlolot
PostPosted: Thu Apr 02, 2009 6:12 am    Post subject: Data sort in excel Reply with quote



Joined: 02 Apr 2009
Posts: 21

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
Back to top
View user's profile Send private message
GaryRussell
PostPosted: Thu Apr 02, 2009 3:44 pm    Post subject: Reply with quote

Site Admin

Joined: 18 Nov 2005
Posts: 8205
Location: Birmingham, UK

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_Samples/SortExample.xls

In the above example column AA contains the rank.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Bumble
PostPosted: Tue Aug 03, 2010 8:02 am    Post subject: Will Sort Order (and contents) ever change? Reply with quote



Joined: 18 Jun 2010
Posts: 16

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
Back to top
View user's profile Send private message
GaryRussell
PostPosted: Tue Aug 03, 2010 8:07 am    Post subject: Reply with quote

Site Admin

Joined: 18 Nov 2005
Posts: 8205
Location: Birmingham, UK

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.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
osknows
PostPosted: Tue Aug 03, 2010 10:26 am    Post subject: Re: Will Sort Order (and contents) ever change? Reply with quote



Joined: 28 Jul 2009
Posts: 882

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:

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
Back to top
View user's profile Send private message
osknows
PostPosted: Tue Aug 03, 2010 11:54 am    Post subject: Reply with quote



Joined: 28 Jul 2009
Posts: 882

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


Code:

Sub test()

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


End Sub
Back to top
View user's profile Send private message
osknows
PostPosted: Tue Aug 03, 2010 12:37 pm    Post subject: Reply with quote



Joined: 28 Jul 2009
Posts: 882

I'm probably talking to myself by now Smile 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:

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
Back to top
View user's profile Send private message
Bumble
PostPosted: Tue Aug 03, 2010 8:17 pm    Post subject: Reply with quote



Joined: 18 Jun 2010
Posts: 16

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).
Back to top
View user's profile Send private message
osknows
PostPosted: Tue Aug 03, 2010 8:42 pm    Post subject: Reply with quote



Joined: 28 Jul 2009
Posts: 882

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.
Back to top
View user's profile Send private message
Bumble
PostPosted: Fri Aug 06, 2010 6:59 pm    Post subject: Reply with quote



Joined: 18 Jun 2010
Posts: 16

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
Back to top
View user's profile Send private message
osknows
PostPosted: Fri Aug 06, 2010 8:11 pm    Post subject: Reply with quote



Joined: 28 Jul 2009
Posts: 882

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:

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
Back to top
View user's profile Send private message
Bumble
PostPosted: Sat Aug 07, 2010 7:36 am    Post subject: Reply with quote



Joined: 18 Jun 2010
Posts: 16

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


DAJ Glass (1.0.8) template by Dustin Baccetti
EQ graphic based off of a design from www.freeclipart.nu
Powered by phpBB © 2001, 2005 phpBB Group