HOORAY I DID IT! now i need help...

[Dmite036]

Just announcing i have created my first trigger in excel after initially thinking this is way too hard. (who cares i hear you all sigh!)

I have set up a lay trigger to lay my personal selections under $7.80.
What is the best way to feed my own individual selections in to excel?

I can easily cut and paste the names of my selections in to excel but i'm not sure how to link it all up.

I would also like some advice on the best way to request the lowest lay price. I was thinking a formula where i can request maybe one tick above the last back price matched.. not sure.

Any help i would be rapt

[danjuma]

I am no expert myself, still learning, but here is a suggestion to the first part of your question. More experienced people on this forum might have a better suggetsion.

Copy and paste your selections in say column AA:AA. Better to copy them from BA or from Betfair so you get the exact text (spelling, spaces abbreviations etc) right.

Then in your trigger formualr, you can use a statement like

=If(AND(ISNUMBER(MATCH(A5,AA:AA,0)),F5<7.8),"LAY","")

What this does is that it checks the selection in A5 against your list in AA:AA, and if there is a match and odds are below 7.8, it triggers.

Hope this helps.

[throwmeadisc]

If you are going to compare horse names for a match then my suggestion is to remove all spaces and make both texts UPPERCASE or lowercase.

It helps get better matches especially if the names are not generated from Betfair names. As sometimes for example, Sporting Life will have a horsename as "Mr Big" but betfair will have it as "Mrbig"

Also it may be worth you adding in the time of race as sometimes two horses with the same name are running on the day. 'Free World' anyone? It's quite rare, but does happen.

A simple formula to remove spaces and make uppercase:

Code: Select all

=UPPER(SUBSTITUTE(A1," ",""))

Al

[danjuma]

...Also it may be worth you adding in the time of race as sometimes two horses with the same name are running on the day. 'Free World' anyone? It's quite rare, but does happen...[/code]Al

Good tip about adding the time, throwmeadisc. Never thought of that. What formula can one use to extract the time from A1?

Cheers
Dan

[Captain Sensible]

Have a look at this thread as it basically covers what you're trying to do

http://gruss-software.co.uk/forum/viewt ... an+element

[Dmite036]

Works awesome so far guys, thanks very much.

On a smaller note, have you any suggestions on a way to lay on potentially low liquidity markets such as the Aus horse racing. I don't want to just take any old lay price under $7.80 if the back price is only sitting at $5's.

You guys have been a big help so again, thanks.

[barnov]

Here's some code I use to capture events & runners from BA, whcih I then sort and use as a table for a VLOOKUP function from the main BA sheet. (I use it for dogs hence use of the term trap, but for you that shouldn't matter)

I run the code from a sheet called wsWrk1 and save runners to wsLook1

Code: Select all

' Declare Variables

Private MyMarket As String
Private RefreshCount As Long

Private Sub worksheet_change(ByVal Target As Range)
       
 Application.EnableEvents = False
 Application.Calculation = xlCalculationManual
   
 Select Case Target.Columns.Count
   Case 16 'odds and market info update
     
     Call Market_Change
     
     If RefreshCount = 4 Then Call Lookup_Runners
         
   Case Else
 End Select

LeaveRoutine:
 Application.EnableEvents = True
 Application.Calculation = xlCalculationAutomatic

End Sub
Private Sub Market_Change()

 'check to see if market has changed
 If Worksheets("wsWrk1").Range("A1").Value = MyMarket Then
    RefreshCount = RefreshCount + 1
 Else
    MyMarket = Worksheets("wsWrk1").Range("A1").Value
    RefreshCount = 1
 End If
       
End Sub

Private Sub Lookup_Runners()
Dim dcell As Range
Dim trap As String
   
For Each dcell In Worksheets("wsWrk1").Range("a5:a10")
    TrapCell = TrapCell + 1
    Worksheets("wsLook1").Cells(TrapCell, 1).Value = MyMarket
    Worksheets("wsLook1").Cells(TrapCell, 2).Value = dcell.Value
Next dcell
   
End Sub


Hope it helps

Paul

[barnov]

Should have also added that currently I manually go through the races I'm interested in after checking that the wsLook1 sheet has been updated... so a little time consuming & manual/repititive.

I beleive that there is a way to use the Q2 cell to auto scroll through races, but not sure what value should be

perhaps others could add comment here

Paul.

[Dmite036]

Well some of the extra formulas are a bit beyond me. Thanks for the effort though. I had this thing working last night, but now it just sits there not triggeriing.

I am using:
=If(AND(ISNUMBER(MATCH(A5,AA:AA,0)),F5<7.,"LAY","")

but nothing happens so i am officially lost....

[Dmite036]

All sorted....

Slight typo...