help with Auto beting football markets

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

help with Auto beting football markets

Postby samhayer » Sun Jan 28, 2018 1:13 pm

Hi everyone I need help /advice please , I have setup a tab in BA conected to exel name Market. I down load all the football markets for the day which I want to bet on into Quick pick list for this Tab. At 10 min before start of each game I want the program to move through each game and exel will place a bet if odds match up to what I want and then move to the next game, If odds are out of range just move to the next game .So if there are more then one game at the same time 10 min should be enough to go through all the games starting at the same time I have the exel side working but dont know how to get it to move through all the games checking odds and pehaps deleting finished games etc please can some one help Thanks all have a great day
samhayer
 
Posts: 66
Joined: Wed Apr 11, 2012 4:57 am

Re: help with Auto beting football markets

Postby Captain Sensible » Sun Jan 28, 2018 2:23 pm

You'll probably need some VBA knowledge to do what you want. There are plenty of special triggers that allow you to move thru the markets and delete markets from the quickpick list. You could even export the quickpick list to the spreadsheet if they're not sorted by time so you can then check times of when markets are due off and go direct to them using the GO:marketId command.

I know it's a bit of a vague answer but it really depends on your knowledge of excel/vab as to how you approach it

Here the list of the Q2 commands that allow you to navigate thru the markets

Q2 Other

Use the triggers listed below to force Betting Assistant to do the actions specified.

Trigger Description Notes
n Sets the Refresh Rate where n represents the seconds required.
-1 Select the next market in the Quick Pick List. If no market is already selected then the first in the list will be selected.
-2 Select the previous market in the Quick Pick List.
-3 Reload the Quick Pick List according to the option set in preferences. This option can be used to run BA continuously without user intervention
(eg. Reload the quick pick list each morning to load the current day’s racing)
Use -4 to load the different venues into separate tab pages
-5 Select the first market in the Quick Pick List.
-6 Refresh the bet results sheet, if you are using this option.
-7 Clears the bet results sheet, if you are using this option.
-8 Removes the current market from the Quick Pick List
-9 Adds existing market bets to Results
GO:n Use this trigger to open a specific market where n
represents the Betfair Market ID The Market ID is output in cell N3.
You must already have a market open in the worksheet before using this trigger.
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby samhayer » Sun Jan 28, 2018 2:44 pm

Thanks for your reply I will try and work through what you have given me and hope to get there cheers thanks a lot
samhayer
 
Posts: 66
Joined: Wed Apr 11, 2012 4:57 am

Re: help with Auto beting football markets

Postby samhayer » Sun Jan 28, 2018 2:54 pm

Hi captain can you tell me is thia what I have to try and put in to the exel sheet using VBA ,I can do simple copy and paste type code or is this just exel code to go into the Market sheet .The cell Q2 is empty at the momentand I dont have a QPL sheet
samhayer
 
Posts: 66
Joined: Wed Apr 11, 2012 4:57 am

Re: help with Auto beting football markets

Postby Captain Sensible » Sun Jan 28, 2018 3:10 pm

When you're putting data into Q2 you're best to use VBA rather than formulas as you'll get so much more control. You might not neccessarily need the QPL sheet in your excel workbook but I've no idea how your QPL is ordered or how many matches you need to cycle thru. You really have to tailor your coding to the best way forward.

I.E. if your QPL is simply filled with 8 matches all in time order with 20 minutes between each game it's easy enough to pick out your 10 minutes to start time from cell D2 place your bets and then move onto the next game by sticking -1 in the Q2 cell. If you have say 10 matches all starting at the same time then obviously it gets harder for you to navigate thru all those matches at the same time. If the gaes aren't in any time order then you need to obviously have some other way and exporting the QPL list to excel gives you that option as obviously all matches are on one sheet so you can now order then and navigate to them separately.

For someone with little VBA knowledge I'd imagine it might be a bt of a task depending on how many markets etc you need to cycle thru.
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby mouse » Thu Sep 22, 2022 11:30 am

Need some advice from CS or GR or anyone else who can help on cycling thru a large number of football markets, uk and international pre off. Have modified some vba code found elsewhere on the forum, which works fine. I am trialling my system with the code in sheet 1 which is attached to BA_ using v small Lay stakes. My question is many how "counts" should there be so that my trigger in cell q5 has time to fire off a lay bet when the criteria are met. I want to use a shorter count as possible, since on a saturday, there could be 80 games K off at 3pm and then overseas matches at 3.15. I assume that having stagger start times by 30s checked in prefernces wont solve my problem.
Is the Refresh window on the grid interface relevant. I have it set currently at 0.6 with auto checked.?
The vba Is -

Dim counter As Currency
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1
If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 12 Then
counter = 0
[Q2].Value = -1
End If
If [E2].Value = "In Play" Or [E2].Value = "Closed" Or [X6].Value <> 0 Then
[Q2].Value = -8
End If

End Sub


.
mouse
 
Posts: 28
Joined: Thu Oct 21, 2010 12:51 am

Re: help with Auto beting football markets

Postby Captain Sensible » Thu Sep 22, 2022 10:10 pm

Guess a lot depends on what your criteria to bet is. If the criteria is met on the first count it'll fire a bet into the market on that refresh.

Seems you're betting pre off so you should be looking to whittle down the markets in your loop if possible by using -8 when the market has no chance of ever meeting your criteria. Obviously that depends on the criteria to bet but if you can remove markets that aren't likely to ever qualify you've more chance hitting the ones that do if the loop is smaller especially on a Saturday.
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby mouse » Fri Sep 23, 2022 8:06 pm

Thanx for taking the time to rreply Captain. Have acted on your advice and amended the code to

Dim counter As Currency
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1
If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -5
[Q2].Value = -1
If [E2].Value = "In Play" Or [F2].Value = "Closed" Or [X6].Value <> 0 Or [AB12].Value = "FALSE" Then
[Q2].Value = -8
End If
End If

End Sub


It seems to work well so from early morning this cycles continually thru the qpl removing games that are "false" in my cell ab12 or in play or are a matched bet.
I dont know much about vba, so
questions can this vba be improved and should it go into a module or leave it attached to worksheet ? In future will attempt to use more vba to auto reload the qpl a few times in the day.
mouse
 
Posts: 28
Joined: Thu Oct 21, 2010 12:51 am

Re: help with Auto beting football markets

Postby Captain Sensible » Sat Sep 24, 2022 3:47 pm

You'd generally start sticking the code into modules when there's a lot of code so it'd make life easier to amend and read sections. Your code is so small that you shouldn't really have problems amending or reading it so no need now for modules but obviously the more complicated things get you'd generally use modules so certain routines can be used without having to duplicate them throughout the coding.

As for improving code it's kind of up to you , many of us will hard code the cells to the sheet they're on because if that code kicks off whilst you're looking at say sheet 2 instead of sheet 1 it'd read the values from Sheet 2. Many ways to do that from using

Code: Select all
 With Target.Parent
 .Range("Q5").Value =   -5
 End With


or

wrting the full address
Code: Select all
Worksheets("Sheet1").Range("J3").Value = "L"


etc

Also your code only checks for the market being in play or closed when the counter is over 10, maybe you'd want to check that at the start as pointless counting ten refreshes just to delete a market.

Other than that you might want to turn off events and calculations whilst the code is running in case any future amendents cause problems.

Code: Select all
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Then turn back on after the code has finished


Code: Select all
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby mouse » Mon Sep 26, 2022 4:55 pm

Many thnx CS for taking the time to reply. Any one else feel free to chip in.
Have hard code to the sheet now, but having problems still .
I found using the idea of^
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


etc stopped all actions Does the count idea allow time for excel to fire a bet from trigger cell Q6 if other criteria wrt to odds, vol, T6 is empty etc, are met

In fist instance Am trying to achieve code to cycle thru the QPL, from early a.m. trigger firing a bet when criteria are met, and deleting games when ab11=false, or game in play, or X6<>0;0. When last game in qpl is reached it repeats from start of the now revised qpl in attempt to get more games matched. Cannot get the code below to do this. Where am i going wrong ?

Dim counter As Currency
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.count <> 16 Then Exit Sub

counter = counter + 1
If counter > 8 And Worksheets("Sheet5").Range("J3").Value = "L" Then
counter = 0
Worksheets("Sheet5").Range("Q2").Value = -5
ElseIf counter > 8 Then
counter = 0
Worksheets("Sheet5").Range("Q2").Value = -5

If Worksheets("Sheet5").Range("E2").Value = "In Play" Or Worksheets("Sheet5").Range("F2").Value = "Closed" Or Worksheets("Sheet5").Range("X6").Value <>"0;0" Or Worksheets("Sheet5").Range("AB11").Value = "FALSE" Then
Worksheets("Sheet5").Range("Q2").Value = -8
End If
End If

End Sub
mouse
 
Posts: 28
Joined: Thu Oct 21, 2010 12:51 am

Re: help with Auto beting football markets

Postby Captain Sensible » Tue Sep 27, 2022 2:41 pm

mouse wrote:Many thnx CS for taking the time to reply. Any one else feel free to chip in.
Have hard code to the sheet now, but having problems still .
I found using the idea of^
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


etc stopped all actions Does the count idea allow time for excel to fire a bet from trigger cell Q6 if other criteria wrt to odds, vol, T6 is empty etc, are met


When you turn events off at the start of the code you need to remember to turn them back on at the end of the code otherwise events will no longer fire.

If all the critieria are met then your trigger would fire but you need to remember BA adds data in two lots on each refresh , one is price data columns A:P and the other is additional data like matched bets etc Column T onwards depending what additional columns you add. Each one of those data dumps triggers an event that's why people put in their code.

Code: Select all
If Target.Columns.count <> 16 Then Exit Sub


That ensures your code only fires once per refresh but it doesn't take into account the data column T onwards, you'd be much better amending that so your routine fires when all data is written to the sheet. I've can't tell you what number to put in as I've no idea how many extra columns you add, SP, Saddlecloth etc. If you don't rely on data from column T onwards then there's no need to amend that.

The other thing with your code that's stopping things is you declare Counter at the start of your routine so it will always be 0 , your code then adds 1 ( Counter now 1) Then when the code starts again you declare it again to 0 (Dim counter as Currency) . If you want the value to be retained you need to declare the counter as a Static variable, that way it get's remembered. But you'll also want to reset that to zero when you hit a need market so you'd add a line to check when a new market has been entered

Code: Select all
If .Range("A1").Value <> MyMarket Then MyMarket = .Range("A1").Value: counter = 0


So you'd end up with something a bit like this with you switching code in the middle

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As String
Static counter As Long

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo xit



With Target.Parent
If .Range("A1").Value <> MyMarket Then MyMarket = .Range("A1").Value: counter = 0


'Your code in here




End With



xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby Captain Sensible » Tue Sep 27, 2022 2:53 pm

Had a look through my old codes and here's one I'd used to loop every 10 seconds, might be of use. Just change the 10 to however many seconds. Basically it just took a timestamp and stuck it in AA1 then after 10 seconds had passed it stuck in either -1 or -5

[code]
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

On Error GoTo ErrorHandler

With Target.Parent

If .Range("AA1").Value = "" Then .Range("AA1").Value = Now()
.Range("AA2").Value = DateDiff("s", .Range("AA1").Value, Now())


If DateDiff("s", .Range("AA1").Value, Now()) >= 10 Then

If .Range("J3").Value = "L" Then
.Range("Q2").Value = -5
Else
.Range("Q2").Value = -1
End If

.Range("AA1").Value = Now()

End If

End With



ErrorHandler:

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub/code]
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby mouse » Wed Sep 28, 2022 6:02 pm

Thanks again Captain for your time on this . Am structuring and correcting the VBA with your suggestions. A few questions.
The vba tests cells in column X (profitl/loss). So tried changing 16 to 24. in
If Target.Columns.Count <> 16 Then Exit Sub

It didn't work, the qpl did not cycle. It stayed on a single game So have left it at 16.
The qpl now cycles and games are deleted when in play or closed, but a market is not deleted when AB11 is false.
An excel formula is in ab11 and it uses info from cell A1. Is that an issue with this vba ?

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As String
Static counter As Long
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo xit

With Target.Parent
If .Range("A1").Value <> MyMarket Then MyMarket = .Range("A1").Value: counter = 0

'Code to cycle continually thru qpl deleting games inplay,or closed etc or if AB11 is false

counter = counter + 1
If counter > 8 And .Range("J3").Value = "L" Then
.Range("Q2").Value = -5
ElseIf counter > 8 Then counter = 0
.Range("Q2").Value = -1
If .Range("E2").Value = "In Play" Or .Range("F2").Value = "Closed" Or .Range("X6").Value <> 0 Or .Range("AB11").Value = "FALSE" Then
.Range("Q2").Value = -8

End If
End If
End With

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

End Sub
mouse
 
Posts: 28
Joined: Thu Oct 21, 2010 12:51 am

Re: help with Auto beting football markets

Postby Captain Sensible » Wed Sep 28, 2022 9:27 pm

The refreshes updates A:P 16 columns and then T: whatever so it wouldn't be 24 it'd be 5 if you haven't additional columns ticked .

How is AB11 displaying False is it displaying it as text or a boolean? A boolean is from a formula that is either true or false, different than a formula set to display the word FALSE. If it's a boolean your formula would be
Code: Select all
Or .Range("AB11").Value = False
without the quotation marks that define it as text.
User avatar
Captain Sensible
 
Posts: 2888
Joined: Sat Nov 19, 2005 2:29 pm

Re: help with Auto beting football markets

Postby mouse » Thu Sep 29, 2022 6:03 pm

I appreciate your time on this Captain. Slowly getting it sorted. Still unsure about how to alter the line
If Target.Columns.count <> 16 Then Exit Sub
I have not got anything ticked for SP, or saddle cloth etc , but I do have an If statement that tests cell X5, profit/loss, to see if it is not 0. This is obviously beyond column T. I have tried for many hrs
changing the line ----count <> 16 Then Exit Sub
but cannot get it right. Pls advise
A bit hopeless at vba. Slightly better at excel formulas.
Secondly in future thinking of vba can I write to trigger cell Q5 e.g.
IF .Range("T6").Value = "" Then
Range("Q6").Value = “BACK”
Looks ok to but does not work for me.
mouse
 
Posts: 28
Joined: Thu Oct 21, 2010 12:51 am

Next

Return to Discussion

Who is online

Users browsing this forum: No registered users and 97 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.