Excel sheet

[dixiedean]

Hi

I have the greyhound markets in seperate tabs with ba so some days there will be 9 or 10 sheets linked, sometimes my sheet is slow, i have noticed it says calculating constantly on the bottom of my sheet, is this normal? If not is there a way to stop my sheets from calculating until the quick pick list changes to the event 1 minute before the race or another solution?

thanks

[Captain Sensible]

Without knowing what formulas or VBA your sheet is running it's hard to say why your sheet/s are running slow. Is there any reason why they can't run one after the other on the same sheet? Otherwise maybe use the Q2 sheet to adjust the refresh rate so it only runs fast when needed not all the time, that should lessen the data going thru excel

[dixiedean]

Hi Captain

Thanks for the reply, i have seperate market and results sheets for each market so i can keep track of results from different meetings not sure how i would achieve this in one sheet? There are no macros in my sheet only some calculations which are probably a bit long winded as i am no excel expert. What is the q2 sheet? I am using a sheet i downloaded and tailored myself so the only refresh rate i can see in my sheet is cell q2 which requires a manual input to refresh

thanks

D

[Captain Sensible]

I don't use the results sheets so no idea what data gets sent to them but surely they have the market data with any pnl, do you use the results to adjust stakes after loss etc Otherwise is there any reason why you can't use a formula or two to show the pnl at the separate tracks. I've also seen quite a few betting pnl sheets out there that'll use the betfair pnl to break down the data by track, distance etc too.

The refresh rate can be put into the cell by a bit of VBA as well as manually , plenty of use it slow down bots when they're doing nothing or speed them up when it's time to bet

[dixiedean]

How would i make my refresh rate slower then in cell q2 until the time before race is 1 minute?

Thanks again

[Captain Sensible]

You can just use some VBA to enter data into cell Q2, all depends on what you're trying to do as how to best acheive it. The time to off gets sent to D2 but that gets quirky when it's gone past the off time as it now gets sent as text so you have to decide how to deal with times after the scheduled off. Another quirk is that sending the refresh rate change to Q2 doesnt get picked up til the next refresh so you have to get creative if you want your bot to sleep for long periods between races.

Here's some VBA to get you started, it's a bit basic but takes into account times after the off. What it should do is check the time to the off and set the refresh rate to 30 seconds if it's more than 1:30, we use 1:30 rather than 1:00 as we want to allow ourselves a 30 second leaway because the refresh will take 30 seconds before it gets picked up by BA. Under 1:30 it'll set the resh to 1 second, it'll also take into account the market has gone past the off time and remain at 1 second refresh until 1:30 after the scheduled off then go back to 30 seconds.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Time2Off As Variant

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

    With Target.Parent
   
    If Application.WorksheetFunction.IsText(Range("D2")) Then
    Time2Off = TimeValue(Replace(Range("D2").Value, "-", ""))
    Else
    Time2Off = Range("D2").Value
    End If
   

   
If Time2Off >= TimeValue("00:01:30") Then

Range("Q2").Value = 30

Else

Range("Q2").Value = 1

End If



    End With

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

[dixiedean]

Cheers Captain ill have a play with it thanks for your help

Cheers

D