API NG

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

Moderator: 2020vision

Re: API NG

Postby mak » Mon Jul 24, 2017 4:49 pm

I was getting an error in line
If BA_Array(2, 59) <= 520 And Target(2, 6) <> "Closed" Then

so i changed it to
If BA_Array(2, 59) <= 520 And BA_Array(2, 6) <> "Closed" Then

I also changed
BA_Array = .Range("A1:BZ55").Value
to
BA_Array = .Range("A1:Z55").Value

but it does not populate any value in q2 cell.
it does not move to the next market also..
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby Captain Sensible » Mon Jul 24, 2017 5:23 pm

Why are you referring to those values as part of the array? Any reason why you're not accessing the values directly? When you've changed your array to BA_Array = .Range("A1:Z55").Value it means BA_Array(2, 59) will not even be within the array at all. Why not just refer to the cells directly like

IF .Range("BG2").Value <= 520 .Range("F2").Value <> "Closed" Then
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: API NG

Postby mak » Mon Jul 24, 2017 6:28 pm

Hi Captain
this is a code which user OSknows wrote for me few years earlier
I did realize about Z55 and revert it back to zb55 and testing again

-->it moves to the next market
but the it does not populate FULL STREAM ON or FULL STREAM OFF

--> it is also placing bets but I don't "see them" in BA interface :shock:


the full old code is below (maybe you will understand why we used arrays)

I don't know how to change all of the code for the new

Option Explicit

Dim marketChanging As Boolean, currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Application.Calculation = xlCalculationManual

Dim BA_Array() As Variant, increment As Long, c As Object, firstaddress As String

If Target.Columns.count = 16 Then


With ThisWorkbook.Sheets(Target.Worksheet.Name)

BA_Array = .Range("A1:BZ55").Value 'read whole block A1:BZ55 Once ONLY (can make as large or small as required)



If BA_Array(2, 59) <= 520 And BA_Array(2, 6) <> "Closed" Then
.Range("Q2").Value = 0.2
Else
.Range("Q2").Value = 0.8
End If


If BA_Array(2, 5) = "In Play" And BA_Array(2, 6) = "" Then
'do nothing (this is most frequent scenario so stop further IF statements)

ElseIf BA_Array(2, 5) = "In Play" And BA_Array(2, 6) = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = BA_Array(1, 1)
.Range("Q2").Value = -1
Else
If BA_Array(1, 1) <> currentMarket Then marketChanging = False
End If

ElseIf BA_Array(2, 5) = "In Play" And BA_Array(2, 6) = "Suspended" Then
If Not marketChanging Then
marketChanging = True
currentMarket = BA_Array(1, 1)
.Range("Q2").Value = -1
Else
If BA_Array(1, 1) <> currentMarket Then marketChanging = False
End If

ElseIf BA_Array(2, 5) = "Not In Play" And BA_Array(2, 6) = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = BA_Array(1, 1)
.Range("Q2").Value = -1
Else
If BA_Array(1, 1) <> currentMarket Then marketChanging = False
End If
End If


'''''Garys timer inPlay
'' http://www.gruss-software.co.uk/phpBB2/ ... ight=cells
If BA_Array(2, 5) <> "In Play" And BA_Array(2, 6) <> "Suspended" Then
.Cells(1, 27) = ""
.Cells(1, 28) = ""

ElseIf BA_Array(2, 5) = "In Play" Then
If BA_Array(1, 27) = "" Then
.Cells(1, 27) = BA_Array(2, 3)
BA_Array(1, 27) = BA_Array(2, 3)
End If
If BA_Array(1, 27) <> "" Then .Cells(1, 28) = DateDiff("s", BA_Array(1, 27), BA_Array(2, 3))
End If


'this is quickest method for searching range
With .Range("T5:T55")
Set c = .Find("CANCELLED", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = ""
Set c = .FindNext(c)
If Not c Is Nothing Then
If c.Address = firstaddress Then c = Nothing
End If
Loop While Not c Is Nothing
End If
End With

If Cells(1, 17).Value = "Y" And Cells(2, 17).Value >= 0 Then
Cells(2, 17).Value = -3.14
End If

If Cells(1, 17).Value = "YI" And Cells(2, 17).Value >= 0 Then
Cells(2, 17).Value = -3.14
End If


If Cells(1, 17).Value = "YYY" And Cells(2, 17).Value >= 0 Then
Cells(2, 17).Value = -5
End If

End With
End If
'Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby Captain Sensible » Mon Jul 24, 2017 7:41 pm

mak wrote:Hi Captain
this is a code which user OSknows wrote for me few years earlier
I did realize about Z55 and revert it back to zb55 and testing again

-->it moves to the next market
but the it does not populate FULL STREAM ON or FULL STREAM OFF

--> it is also placing bets but I don't "see them" in BA interface :shock:


So it's not placing FULL STREAM ON or FULL STREAM OFF into cell Q2? Are you sure it's not being replaced further on into your code by something else?

Whenever I used to develop my bots I'd have data echoed back to the screen to check that routines were firing so for example in the routine where you expect FULL STREAM ON to be entered into Q2 you could check if that code was actually working by simply adding another line to the routine to echo a message to the sheet in say Cell U1 , something like


If BA_Array(2, 59) <= 520 And BA_Array(2, 6) <> "Closed" Then
.Range("Q2").Value = "FULL STREAM ON"
.Range("U1").Value=" FULL STREAM ON fired"
else
etc etc

That way you should at least see if the routine fired even if Q2 was later overwritten by something else, even adding something as simple as

.Range("U2").Value=.Range("Q2").Value

at the end of your code just before

'Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

will show you what value was last entered into Q2 after your code ran. Should help you debug where things are going wrong.

As for " it is also placing bets but I don't "see them" in BA interface :shock: " I'm not sure what you mean , do you mean bets are sent to Betfair and you can see them on the website but not in the program?
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: API NG

Postby mak » Tue Jul 25, 2017 6:31 am

Well,
I am using Al's code right now. I did some minor changes and it is firing the FULL STREAM ON
I can not test it completely until the horse market start but I think it is a good start
the only think missing right now (if it will work ok) is to update the markets (UK and IRE) every morning lets say at 07:00
How can I implement this in this code?

Dim marketChanging As Boolean, currentMarket As String, triggerInQ2 As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.count = 16 Then
Dim BA_Array() As Variant, MyQ2 As Variant
Application.EnableEvents = False
With ThisWorkbook.Sheets(Target.Worksheet.Name)

BA_Array = .Range("A1:AA55").Value ' this read is probably too much for every update while full stream is on

If BA_Array(2, 27) <= 28200 And Target(2, 6) <> "Closed" Then
.Range("Q2").Value = "FULL STREAM ON"
.Range("U1").Value = " FULL STREAM ON fired"
Else
.Range("Q2").Value = "FULL-STREAM-OFF"
.Range("V1").Value = " FULL STREAM OFF fired"
End If

If (Target(2, 5) = "In Play" And Target(2, 6) = "Suspended") Or Target(2, 6) = "Closed" Then
If Not marketChanging Then
marketChanging = True
currentMarket = Target(1, 1)
MyQ2 = -1
Else
If Target(1, 1) <> currentMarket Then marketChanging = False
End If


If triggerInQ2 <> MyQ2 Then
.Range("Q2").Value = MyQ2
triggerInQ2 = MyQ2
End If
End If

End With
End If

Application.EnableEvents = True


End Sub
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby alrodopial » Tue Jul 25, 2017 7:31 am

This part of the code
Code: Select all
If BA_Array(2, 27) <= 28200 And Target(2, 6) <> "Closed" Then
.Range("Q2").Value = "FULL STREAM ON"
.Range("U1").Value = " FULL STREAM ON fired"
Else
.Range("Q2").Value = "FULL-STREAM-OFF"
.Range("V1").Value = " FULL STREAM OFF fired"
End If

writes in excel at every update which is not needed, it is heavy for cpu and unnecessary

Is this AA2 info=BA_Array(2, 27) <= 28200 a fixed number or should be read at every update?

There is code in the forum for updating quick list at midnight, search for it
I tried sometime ago to implement it with no success but I think Captain did it
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Re: API NG

Postby alrodopial » Tue Jul 25, 2017 7:33 am

How is the cpu usage with the latest code?
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Re: API NG

Postby mak » Tue Jul 25, 2017 11:43 am

in AA2 there is a formula which replace the time until the start of the race in seconds..
i think it would be more efficient to turn into FULL STREAM ON when the market turns in play (depending the on E2 value)
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby mak » Tue Jul 25, 2017 12:11 pm

forgot to add that cpu usage with this code goes up to 60-65% with 2 xl sheets inplay

I noticed right now that FULL-STREAM-OFF is firing 2 times every second.. is it how it supposed to work?

* if I place 0.2 instead of FULL-STREAM-OFF it just stays there as usual..
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby MarkRussell » Tue Jul 25, 2017 12:37 pm

mak wrote:forgot to add that cpu usage with this code goes up to 60-65% with 2 xl sheets inplay

I noticed right now that FULL-STREAM-OFF is firing 2 times every second.. is it how it supposed to work?

* if I place 0.2 instead of FULL-STREAM-OFF it just stays there as usual..


Hi,

With Full stream on you could be getting updates at every 20ms that's 10 times as many as opposed to using the old polling method set to 0.2 seconds, when a horse race is in play, so for sure it s going to require more CPU to keep updating Excel at that speed.

Regards,
Mark
User avatar
MarkRussell
Site Admin
 
Posts: 1787
Joined: Tue Feb 20, 2007 6:38 pm
Location: Birmingham

Re: API NG

Postby Captain Sensible » Tue Jul 25, 2017 1:00 pm

MarkRussell wrote:
mak wrote:forgot to add that cpu usage with this code goes up to 60-65% with 2 xl sheets inplay

I noticed right now that FULL-STREAM-OFF is firing 2 times every second.. is it how it supposed to work?

* if I place 0.2 instead of FULL-STREAM-OFF it just stays there as usual..


Hi,

With Full stream on you could be getting updates at every 20ms that's 10 times as many as opposed to using the old polling method set to 0.2 seconds, when a horse race is in play, so for sure it s going to require more CPU to keep updating Excel at that speed.

Regards,
Mark


And another good reason why you should avoid unneccessary formulas being done outside the VBA and keep screen writes to a minimum. I'm not sure if you have Application.Calculation = xlCalculationManual & Application.Calculation = xlCalculationAutomatic commented out in your full code as opposed to the snippets you posted but it would be a good idea to uncomment them from your live code if they aren't already.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: API NG

Postby mak » Tue Jul 25, 2017 1:41 pm

thanks Mark!

Captain and Al
my bot must do calculations (excel formulas) in order to decide if and when to place a bet.
what should I use in VBA in order to be efficient?
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby Captain Sensible » Tue Jul 25, 2017 1:57 pm

I've no idea what your formulas are doing or how many are on your sheet mak so can't really comment how to make it more efficient.

All excel formulas can be handled with VBA code or if you don't want to replace them all you should at least turn off excel formula calcualtions whilst the VBA is running especially if the sheet has lots of related formulas. Turning them off whilst the VBA runs won't stop them working.

From the code you've posted it seems pointless you're loading a large array into memory and don't appear to even be doing anything withit other than moving between markets and adjusting the refresh rates so only using a few cells you could access directly.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: API NG

Postby mak » Tue Jul 25, 2017 2:50 pm

i give up
i can not make it work
there is a bug also just to know

the bot is placing bets
i can see them in the black strip in BA (below) but they don 't appear anywhere else
I don 't know when it is happening but it does some times
mak
 
Posts: 1086
Joined: Tue Jun 30, 2009 8:17 am

Re: API NG

Postby Captain Sensible » Tue Jul 25, 2017 3:34 pm

mak wrote:i give up
i can not make it work
there is a bug also just to know

the bot is placing bets
i can see them in the black strip in BA (below) but they don 't appear anywhere else
I don 't know when it is happening but it does some times



Is that the only VBA on your sheet or does it place bets too? Is it simply switching markets and adding streaming on and off?


I dont know what you mean about the bets but remember things on streaming can now be moving around 10 times quicker than before so it's easily possible bets are being sent and cells cleared before you can see them appearing. When you say they don't appear anywhere else are you saying they don't appear on gruss even though they are actually appearing on the website, or are you saying the sheet shows them as being sent but they never actually get submitted?
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

PreviousNext

Return to Discussion

Who is online

Users browsing this forum: No registered users and 10 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.