Sheet Required

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

Moderator: 2020vision

Sheet Required

Postby SWV » Sat Aug 12, 2017 8:14 am

Hi

I would like a excel sheet that can do the following:

Auto refresh the UK win horse racing market at 8:00am
Select the first race, then 5 seconds later move to the next race, then every 5 seconds move onto the next race and so on.

Please get pm me.

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

Re: Sheet Required

Postby Captain Sensible » Sat Aug 12, 2017 5:26 pm

Gary's already posted an example of how to reload the markets at a set time, all you'd need to do is tweak the times on the sheet from 00:00:00 to 08:00:00 in the this workbook and module. Switching between markets is relatively simple just amend the main code under Sheet1 to

Code: Select all
Option Explicit
Public timeStamp As Date

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False

            If triggerQuickPickListReload = True Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -3.1
            triggerFirstMarketSelect = True
            GoTo Xit
        Else
            If triggerFirstMarketSelect = True Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
                GoTo Xit
            End If
        End If
       
       
        If Now() > TimeValue("00:00:05") + timeStamp Then
        If Range("J3").Value = "L" Then timeStamp = Now(): Range("Q2").Value = -5: GoTo Xit
        timeStamp = Now()
        Range("Q2").Value = -1
        End If
       
       
       
       
       
       
Xit:
        Application.EnableEvents = True
    End If
End Sub
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Sun Aug 13, 2017 11:15 am

Thank you Captain 8)

So the code in sheet1 would like this?

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -3
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
            End If
        End If
        Application.EnableEvents = True
    End If
End Sub

Option Explicit
Public timeStamp As Date

    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False

            If triggerQuickPickListReload = True Then
            triggerQuickPickListReload = False
            Range("Q2").Value = -3.1
            triggerFirstMarketSelect = True
            GoTo Xit
        Else
            If triggerFirstMarketSelect = True Then
                triggerFirstMarketSelect = False
                Range("Q2").Value = -5
                GoTo Xit
            End If
        End If
       
       
        If Now() > TimeValue("00:00:05") + timeStamp Then
        If Range("J3").Value = "L" Then timeStamp = Now(): Range("Q2").Value = -5: GoTo Xit
        timeStamp = Now()
        Range("Q2").Value = -1
        End If
       
       
       
       
       
       
Xit:
        Application.EnableEvents = True
    End If
End Sub
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Sun Aug 13, 2017 11:20 am

You just need to replace the existing code with the one I posted as they're basically the same, I just added a bit to loop thru the markets and then resume from the start once it gets to the last market.


If you don't want it continually looping the markets it's easy to change
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Mon Aug 14, 2017 7:52 am

Modified the time to 07:30 and tried it this morning Captain and it worked a treat.

One thing though it is in a continuous loop so that when it gets to the last race it starts all over again with the first race. Not sure if it is possible would it be able to stop at the last race? No idea how you would do that though to tell it is the last race.

Amazing stuff this excel business, frighting to think what type of sophisticated bots you guys have out there!

Once again thank you :)
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Mon Aug 14, 2017 2:49 pm

If you don't want it to resume looping just remove the line

Code: Select all
  If Range("J3").Value = "L" Then timeStamp = Now(): Range("Q2").Value = -5: GoTo Xit


J3 holds the indcator for how many markets are left in the quick pick list L is the last market.

Or you can amend the coding so it does something else when it hits the last market like going to sleep by setting the refresh rate to 9999. All depends on what you need to do
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Tue Aug 15, 2017 7:52 am

wow fantastic!

Thank you very much for your help, thank you :)
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Tue Aug 15, 2017 9:33 am

No problem, all I did was add a couple of lines to the sheet Gary had posted in the example sheets.

If you ever get stuck to do things have a search of the forum as it's likely someone's already had the same problems and posted code to sort it out.
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Tue Aug 15, 2017 5:35 pm

Ok will do. It all seems double dutch to me at the moment! :roll:
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby SWV » Thu Aug 17, 2017 7:13 am

Hi

I discovered yesterday that the sheet can not auto refresh the quick pick list. For some reason it tries to "fetch" the new markets but fails. Maybe it is because it is trying to move onto the next market at the same time as updating the quick pick list? This possible to fix please?

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

Re: Sheet Required

Postby Captain Sensible » Thu Aug 17, 2017 11:19 am

The program can only enter 1 entry into Q2 cell at a time and it wouldn't be switching markets at 7:30 as there are no UK races around that time.

When you say tried but failed what do you mean? Was it continually trying to retrieve the markets or did you just assume it had been because no new markets were entered?
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Thu Aug 17, 2017 6:28 pm

Hi Captain

Here is the sheet i am using. A couple of slight amendments i.e times etc.

http://s000.tinyupload.com/index.php?fi ... 3214986729
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Thu Aug 17, 2017 7:13 pm

" For some reason it tries to "fetch" the new markets but fails. " Did you see it try to fetch the new markets or just guess it tried and failed because it had't loaded the days races when you looked at it later?
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Re: Sheet Required

Postby SWV » Thu Aug 17, 2017 8:48 pm

I watched it "live" make about 20 attempts to fetch the markets before i aborted it.
SWV
 
Posts: 22
Joined: Sat Aug 12, 2017 8:11 am

Re: Sheet Required

Postby Captain Sensible » Thu Aug 17, 2017 8:54 pm

K, that sounds like it was an API problem, used to happen all the time with the AUS servers. When Betting assistant fails to retreive the new list of markets it keeps re-trying , you'd have to ask Gary how many times it retries before giving up. May have been down to Betfair doing maintenanceon the API , that's one of the reasons most people reload the QPL at midnight as they rarely do maintenance around that time usually it's early in the morning.
User avatar
Captain Sensible
 
Posts: 2676
Joined: Sat Nov 19, 2005 2:29 pm

Next

Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 1 guest

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.