BACKR and LAYR

[chopper]

Hello,

When I use excel to place bets I expect the odds and stakes that I wish to trade at to be constantly changing. I therefore expect to only be using the BACKR and LAYR triggers.

Because of this I need to remove the 'N' from the end of the bet reference. Is there an option to do this for BACKR and LAYR? If not I can do this just using an if statement unless anyone has any other better suggestions?

The Bet reference goes in column T. Does BA work by always going to this particular column? If so what do I do, should I insert another column so I can make a new reference without the 'N' but how do I make BA use the new reference?

What happens if I'm partially filled? That will put a 'P' at the end of my reference. Will I have to modify this or will I have to do the same thing as I do with the 'N'?

Thanks.

[GaryRussell]

N will always be appended I'm afraid. It's to prevent BACKR/LAYR from continuously being triggered.

It will always use the ref from column T.

If it is partially matched you will also need to remove the P before the trigger will work.

N or P cannot be removed using a formula, but you could use a formula to populate another cell with a value then you could write a very small amount of VBA which checks this cell and removes the N or P if a specific value appears in this cell.

[chopper]

Thanks for the quick response Gary.

Are these BACKR and LAYR functions effectively cancelling my current order and then sending a new order? Does this mean it takes longer than just updating the unmatched trade? I suppose it means I make more requests as well?

I was thinking of just using an IF statement to take the text string before the N or the P and putting this in column T and get the original reference to come in somewhere else but yeah I thought it might not be possible.

I've never used VBA before and was hoping to get this up and running for the Nadal vs Murray match tonight! Is there any chance you could give me an idea of what the code should like?

Cheers.

[Castillo]

Is there any chance you could give me an idea of what the code should like?

Try looking at pages 4 & 5 on this thread, hope this helps.

http://www.gruss-software.co.uk/forum/v ... php?t=2840

Cheers
Castillo

[chopper]

Thanks for that Castillo.

I see that you started off the same way as me with an IF statement. Then another poster suggested using this code:

If Right(Range("T6").Value, 1) = "N" Then
Range("T6").Replace What:="N", Replacement:=""
Else
End If

So should that do the trick? Is that what you use?

[GaryRussell]

1) Insert following formula in cell Y3

Code: Select all

=COUNTIF(Y5:Y50,"=1")

2) Press alt-f11 to open the code editor, double click your worksheet name on the left and insert the following code.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer, ref As String
    If Cells(3, 25) <> 0 Then
        Application.EnableEvents = False
        For r = 5 To 50
            If Cells(r, 25) = 1 Then
                ref = Cells(r, 20)
                If Right(ref, 1) = "N" Or Right(ref, 1) = "P" Then Cells(r, 20) = Left(ref, Len(ref) - 1)
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub


You can now put formulas in cells Y5 to Y50 that put 1 in column Y next to the bet you want the N removed from.

[GaryRussell]

Take care that column Y does not always contain 1 otherwise it will constantly be removing the N and therefore allowing the BACKR/LAYR triggers to fire, replacing the same bet over and over again. This could potentially be costly as you will incur transaction charges. If you make more than 1000 bets in an hour you are charged 1 pence per excess request.