Sheet Required

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

Re: Sheet Required

Postby SWV » Fri Aug 18, 2017 7:04 am

After i aborted the sheet i "manually" fetched the markets and it worked first time so i don't think it is an API problem. I feel the problem is that is the next market trigger -1 and the fetch new market trigger(not sure which one that is) are conflicting with each other.
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Fri Aug 18, 2017 10:36 am

BA only picks up one Q2 trigger at a time so if it was fetching then it had started that routine internally and subsequent triggers in Q2 wouldn't mae any difference especially as they're only being entered once every 5 seconds. I tried the code just , jut amended the reload times to 10:28 and all worked fine. But you can always just reinstate the lone that decides what to do when it reaches the last market so nothing gets entered into Q2 if you think its the -1 just amend it to


If Range("J3").Value = "L" Then timeStamp = Now(): GoTo Xit

That way it still skips the -1 being sent after it reaches the last market.

But like I said earlier you should really think about what you want it to do when it reaches the last market as it's a bit wasteful on your bandwidth having it continually refreshing throughout the day when it's doing nothing and also Betfair have been limiting accounts if they think they're data gathering rather than betting.
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby Captain Sensible » Fri Aug 18, 2017 3:39 pm

With the delay at Newbury I had time to amend the code for you, once it hits the last market and runs it's 5 seconds it will enter a value of 60 into the Q2 (refresh rate) cell, on the next refresh it will clear the cell Q2 so any refresh rate when it does it's restart at market 1 in the QPL will come from whatever your default rate is.


Code: Select all
Option Explicit
Public timeStamp As Date

Private Sub Worksheet_Change(ByVal Target As Range)
Static lastMarket As Boolean
On Error GoTo Xit
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If IsEmpty(timeStamp) Then timeStamp = Now() + TimeValue("00:01:00") ' set time so we don't rush thru first market when first opening the sheet
         
            If triggerQuickPickListReload = True Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -3.1
            triggerFirstMarketSelect = True
            timeStamp = Now() + TimeValue("00:01:00") ' allow time so refresh can load
            GoTo Xit
        Else
            If triggerFirstMarketSelect = True Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
                lastMarket = False
                GoTo Xit
            End If
        End If
       
       
        If Now() > TimeValue("00:00:05") + timeStamp Then
        timeStamp = Now()
       
        If Range("J3").Value = "L" Then
        If lastMarket <> False Then Range("Q2").Value = "": GoTo Xit 'Clears Q2  so it won't affect first market
        Range("Q2").Value = 60
        lastMarket = True:
        GoTo Xit
        End If
       
       
        lastMarket = False
        Range("Q2").Value = -1
        End If
       
               
       
       
Xit:
        Application.EnableEvents = True
    End If
End Sub
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Wed Aug 23, 2017 8:13 am

Hi Captain once again thank you for your help.

I tried the new code this morning but i couldn't get it to work for some reason. The refresh rate stayed at 60secs regardless and the markets did not update. I must be something wrong but i don't know as this code/vba business is well out of my league. Maybe i expected too much from Betting Assistant i don't know.

Anyway thank you and good luck i really appreciate you taking the time to reply and look at this for me.

All the best

SWV
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Wed Aug 23, 2017 3:31 pm

Did you allow it time to fully run? It'll pause on the first market and also enter 60 in Q2 but that's partly down to the way BA handles refreshes that they don't take affect immediately and won't take effect til the next refresh.

I tested it just and seemed fine, you can add the code below to do testing of the quick pick routine, you'd simply enter the time 16:00:00 into cell S1, press together th following buttons (to bring up the available macros) Alt + F8 and then run TestQPList, that will schedule the Quick pick list to run at whatever time is in S1, it won't overwrite the times you've set so no need to change them. Obviously disable any betting routines whilst testing the QPList routine as you don't want to place bets. Just changing the times in the routines wouldn't schedule them unless the sheet was reopened or the macro run so you would need to run the TestQPList routine for it to schedule the time and work.


Code: Select all
Public Sub TestQPList()


    Application.OnTime TimeValue(Worksheets("Sheet1").Range("S1").Text), "loadQuickPickList"
End Sub





If you don't want refresh rates slowing down once it's finished those parts can always be take out or skipped
User avatar
Captain Sensible
 
Posts: 2687
Joined: Sat Nov 19, 2005 2:29 pm

Previous

Return to Find an Excel developer

Who is online

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