Sheet Required

[SWV]

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

[Captain Sensible]

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

[SWV]

Thank you Captain

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


[Captain Sensible]

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

[SWV]

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

[Captain Sensible]

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

[SWV]

wow fantastic!

Thank you very much for your help, thank you

[Captain Sensible]

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.

[SWV]

Ok will do. It all seems double dutch to me at the moment!

[SWV]

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

[Captain Sensible]

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?

[SWV]

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

[Captain Sensible]

" 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?

[SWV]

I watched it "live" make about 20 attempts to fetch the markets before i aborted it.

[Captain Sensible]

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.