by andyh » Wed May 16, 2007 2:49 pm
Hi all, I have been following the instructions shown below to set up triggered betting. My only problem is that I bet to liability so I also need the spreadsheet to take perform a liability calc and insert calculated stake. This would be fine but each time the market refreshes the sheet gets wiped. Can anybody suggest a method to calculate a liability stake when a horse from sheet 2 is matched to sheet 1.
Cheers in advance
Andy
Instructions followed-
Sheet2 to contain
Horse name in column A odds in column B stake column C
Sheet1 as follows
In the odds column O you need to copy and paste the following into O5 to say O50 to cover all runners
=IF(ISNA(VLOOKUP(A5,Sheet2!$A$1:$C$100,2,FALSE)),"",VLOOKUP(A5,Sheet2!$A$1:$C$100,2,FALSE) )
in stake column P copy the follwoing from P5 to P50
=IF(ISNA(VLOOKUP(A5,Sheet2!$A$1:$C$100,3,FALSE)),"",VLOOKUP(A5,Sheet2!$A$1:$C$100,3,FALSE) )
Basically it just check the name of the horse against your list of bets and if it finds a match it'll stick the odds in the column O for you and stake in column P.
for the trigger column just copy and paste the following from N5 to N50
=IF(AND(MINUTE($D$2)<1,O2>0),"LAY","WAITING")
Any problems just give us a shout.
You have to make sure the names match exactly with the way betfair have entered them for it to kick off.
Just have your bettrader set to auto select markets and ensure the clock on your PC is set correctly
Easy enough to add another column to sheet 2 if you're backing and laying and amend =IF(AND(MINUTE($D$2)<1,O5>0),"BACK","") so it picks the LAY or BACK form the column using VLOOKUP same as for the odds etc
Forgot to mention you'll need a macro in the worksheet to clear the cell references when it switches between markets on auto select. I just use the code below that someone else posted a while back
Just open up the visual basic editor and paste below into sheet1
Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("Q5:U80").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub