Place a back bet and moving though the markets - Frustration

[StuT]

Hi all,

I wonder if someone could point me in the right direction...

I am fairly okay with VBA but am struggling to get things in the correct order in terms of placing a bet and moving through the quick pick list.

I want to do the following task.
1. At the scheduled start time (D2=0) place a BACKSP trigger in Q5
2. When betref is returned, I want to clear the trigger and betref and select the next market (Q2 = -1)

I have my code in Worksheet_Change but no matter how I structure my code it either goes forward 2 markets on setting Q2 to -1 or clears the Q5 trigger and repopulates it at the wrong time.

I need to get these fundamentals right otherwise bets could be fired all over the place.

Would any kind folk be able to supply me some pseudo type code just to do those simple steps:
1 - When D2 = 0 place BACKSP trigger on selection in Q5.
2 - Clear Trigger in Q5 and BetRef in T5
3 - Move to the Next market
Repeat...

Thanks a lot in anticipation.

[Captain Sensible]

1) Is there any reason why you don't want to use the existing BA option to auto select the market before the off? You can set it as low as 6 seconds if needed. You can always add additional conditions to your trigger if you only want to go at D2=0, although from experience I'd avoid having something so strict as problems with your connection/BF website latency etc mean you could easily miss D2=0 better to set a range i.e 2 to -2.

2) Most of us just set a variable to hold the market name, and check that first in the VBA if it's changed from the previous market we just clear any variables like bet refs etc . Here's the original code gary posted to clear bet refs triggers etc when markets change,

Code: Select all

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(Cells(5, 17), Cells(100, 20)).Value = ""
End If





Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

3) Moving to the next market with -1 can be a pain. Seems BA does two refreshes of data so you need to ensure your code only triggers when the price refresh is sent to the sheet i.e 16 columns . So people wrap the code within

If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
'all your code
End if

You also have to make sure your criteria for the -1 is not present once the market is on the next sheet also or it will retrigger, I reset Q2 to my starting refresh rate in the MyMarket code above setup . Theres an example sheet in the spreadsheet samples here viewtopic.php?f=8&t=5163 showing the use of -1

[StuT]

Thanks a lot for your response CS.

Yeah I did look at auto select but I want to place the bets as close to the off as possible so I will use D2 <= 0 and another explicit flag to ensure it only passes through once.

At the point of setting Q2 = -1 I will also reset the trigger, betref and the flag that triggers the next market.
Hopefully that should do it.

I see you have that code in Worksheet_Calculate.
Can I still have it in WorkSheet_Change and code it within the Target.Columns.Count <> 16 ?

[Captain Sensible]

As soon as D2 goes below zero it gets sent as text so you might want to use something like OR(D2=0,ISTEXT(D2) rather than D2<=0.

WorkSheet_Change is fine I just copied and pasted the snippet of code Gary originally posted, I use WorkSheet_Change in all my bots and it works fine.

[Captain Sensible]

The point of mentioning the auto select markets was just to avoid having to code your -1 you could still use D=0 just that it'd move to the market 1 minute out and clear all redundant data etc.

[Captain Sensible]

It's generally best to use the most reliable methods in your bots and the auto select in BA has been around since it started so is very reliable,, coding your own routines to move markets means you have to anticipate quirks like closed markets where meetings have been abandoned etc Auto select just does what it says on the tin and moves to the market in the quick pick list whatever time you set before the off, you can set your criteria to place bets within the triggers.

[StuT]

Good point about the Auto Select options in BA, I was going to add additional code to check for Market = Closed and issue a Q2 = -1 to move markets.

Using the Auto Select Features is it impossible to place a bet at the scheduled start time (D2=0 ish) and let AutoSelect move to the next market.
How close can I get, is it 6 secs as you previously said?

[Captain Sensible]

Using the Auto Select Features is it impossible to place a bet at the scheduled start time (D2=0 ish) and let AutoSelect move to the next market.
How close can I get, is it 6 secs as you previously said?

The auto select just moves to that market, any criteria to bet is set within your betting trigger criteria. I'm not sure what relevance when you open the market has, if you think about it you moving to the market staright after betting means you'll be on the market a lot quicker than using Auto select at one minute beforehand.

Any timing of when the bet gets sent is all down to your trigger as that will only populate the Q column when it meets all the criteria you set which I assume will include the time.

I don't know what criteria you're using to select selections but can only assume you need to be as close to the off as possible to select the favourite, number of runners etc because if your selections are pre determined there's no price advantage to be made by using BACKSP close to the off

[StuT]

Thanks CS, I've got some thinking to do.

Just one more question if you don't mind, In your snippet of code below if [A1] = myMarket it jumps to the exit, otherwise it resets flags.
Where do I put any 'main' code that I want to execute each time within Worksheet_Change?
I'm asking this because if the markets equal it jumps out, do I put it below the Xit:


If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range(Cells(5, 17), Cells(100, 20)).Value = ""
End If

[Captain Sensible]

You'd just want your market setup routine to come first in your code , the xit: was just part of Garys example

Basically the code checks to see if the market is the same if not it goes thru a setup routine, you could either put your code within than loop

Code: Select all

If [A1].Value = MyMarket Then

'your betting code goes here as we've set up the market

Else
MyMarket = [A1].Value
Range(Cells(5, 17), Cells(100, 20)).Value = ""
End If

Or to make things cleaner you exit that loop and run your code after

Code: Select all

If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range(Cells(5, 17), Cells(100, 20)).Value = ""
End If


Xit:

'Your code now goes here after the market setup has run or then routine has sent us here 



Either way is up to you just depends on your coding preferences and if you use things like GoTo to jump around your coding or not.

[StuT]

Excellent, thanks CS, you've been a good help.
I will spent the rest of the week playing around with minimum stakes attempting to sail my way through a full pick list.

Can you place bets below the minimum £2 via excel triggers?

[Captain Sensible]

Yep no problem placing bets under £2 just stick the stake in the stake column and BA takes care of the rest. Remember that Betfair Starting Price has a minimum staking amounts of £2 back(£10 liability laying) though for bets to go into the BSP pool.

[StuT]

Brilliant, thanks again CS.