Excel with VBA codes, how to optimise

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

I have a strategy, in pre race horse markets. I add all UK & IRE markets at 0:00 hour, and for this day I use a multiple excel so that all competitions are related to betangel with excel.
This is about 40 excel sheets.

All conditions in strategy wrote in excel funktion.

But since I use a lot of commands (LAY / BACK / CANCEL_ALL_BACK / CANCEL_ALL_LAY), the status cells must also be cleared. This definitely requires VBA.

I have a VBA code running on every excel sheets, in Worksheet-Calculate form, with for-next cycle. Therefore it is very slow (refresh time is about ~10 sec).

So, the first problem is the slow refresh time. How can i the VBA codes optimise for a 40 sheet excel?

--------------

The second problem is that excel places double bets. Why?

For example:

Condition is true,, for example i have 2 BACK and 8 LAY matched bets, and theese not hedged. The excel place a BACK bet.
After this, immediately clear the status ("PLACED") and command cells ("BACK").

Matched / unmatched cells are not updated immediately, so the condition is still true. I think not because of the slowness of excel-vba, but because the data from betfair only arrives later.

How can this be eliminated?

Thank you!
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Are you using a class module? If not, is the code for each sheet identical?

And what do you mean by a for/next cycle? Are you running through the 40 sheets on a for/next cycle?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

It helps to disable recalcs and screen refreshing while you're updating multiple cells, and re-enable them afterwards. Otherwise it does those unnecessarily every time you update each cell even if the value hasn't changed.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Use the Worksheet_Change event rather than Worksheet_Calculate and check to see if the status cells are in the range parameter that it gives (its byref so will contain a value). You can check if its in there by using intersect.
If it does exist then use ClearContent on the cell.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Derek27 wrote:
Fri Feb 03, 2023 2:13 pm
Are you using a class module? If not, is the code for each sheet identical?

And what do you mean by a for/next cycle? Are you running through the 40 sheets on a for/next cycle?
I dont use class module, there is the same code for each sheet.
I use for-next cycle from cells i=9 to 67 step 2. Or better, if i dont use this cycle, only az IF commands for all cells?

For example (just the logic, not working form)

Code: Select all

IF "O9"= "PLACED" Then
clear...
End If

IF "O11"= "PLACED" Then
clear...
End If
etc. until 67. (a lot of code)
ShaunWhite wrote:
Fri Feb 03, 2023 3:30 pm
It helps to disable recalcs and screen refreshing while you're updating multiple cells, and re-enable them afterwards. Otherwise it does those unnecessarily every time you update each cell even if the value hasn't changed.
Thanks, i test it. For screen updating i get an error with debug. The calculation works, but i dont know, it is faster?

I use this:

Code: Select all

Application.EnableEvents = False
Application.Calculation = xlManual
...PROGRAM...
Application.EnableEvents = True
Application.Calculation = xlAutomatic
ODPaul82 wrote:
Fri Feb 03, 2023 6:03 pm
Use the Worksheet_Change event rather than Worksheet_Calculate and check to see if the status cells are in the range parameter that it gives (its byref so will contain a value). You can check if its in there by using intersect.
If it does exist then use ClearContent on the cell.
Thanks, but i tried this before, but unfortunately it wasn't faster.
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

rain91 wrote:
Sun Feb 05, 2023 9:35 am
Derek27 wrote:
Fri Feb 03, 2023 2:13 pm
Are you using a class module? If not, is the code for each sheet identical?

And what do you mean by a for/next cycle? Are you running through the 40 sheets on a for/next cycle?
I dont use class module, there is the same code for each sheet.
I use for-next cycle from cells i=9 to 67 step 2. Or better, if i dont use this cycle, only az IF commands for all cells?

For example (just the logic, not working form)

Code: Select all

IF "O9"= "PLACED" Then
clear...
End If

IF "O11"= "PLACED" Then
clear...
End If
etc. until 67. (a lot of code)
ShaunWhite wrote:
Fri Feb 03, 2023 3:30 pm
It helps to disable recalcs and screen refreshing while you're updating multiple cells, and re-enable them afterwards. Otherwise it does those unnecessarily every time you update each cell even if the value hasn't changed.
Thanks, i test it. For screen updating i get an error with debug. The calculation works, but i dont know, it is faster?

I use this:

Code: Select all

Application.EnableEvents = False
Application.Calculation = xlManual
...PROGRAM...
Application.EnableEvents = True
Application.Calculation = xlAutomatic
ODPaul82 wrote:
Fri Feb 03, 2023 6:03 pm
Use the Worksheet_Change event rather than Worksheet_Calculate and check to see if the status cells are in the range parameter that it gives (its byref so will contain a value). You can check if its in there by using intersect.
If it does exist then use ClearContent on the cell.
Thanks, but i tried this before, but unfortunately it wasn't faster.
If there's a lot of code and to save you the bother of duplicating it every time you make changes, it may be easier to place your code in a class module. Then in each sheet you make a reference to it, such as:

Private MyClass as ClassModuleName

Set MyClass = New ClassModuleName


This will create an instance of your class module with a separate set of variables for each sheet, and as the object variable is private you can safely duplicate it for each sheet. You can call a subroutine or function with:

ClassModuleName.MySub
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Does it speed up the system? Or will it be easier just for me?

The target market the UK &IRE horse markets, IN-PLAY. 5 worksheets are enough for me not to miss a race.

I noticed that if I avoid VBA, everything is much faster, so I always use excel functions whenever possible. But I can't do some things, so I have to use vba, for example clearing the status cells (i habe 10-15 bets in a run / runners, so i need fast system.

So i make an excel sheet with functions, and VBA with worksheet calculate. And i duplicate the excel sheets (this also multiplies the vba).

do they run in parallel in this case? Or one after the other, so they slow each other down?

If I call it in a module, will it be faster? Or does it get mixed up if all worksheets call the same module?

What is better, faster?
- Worksheet calculate subs?
- Worksheet change subs?
- For-Next cycle or row by row scan the cells?


--- Other thread, how to avoid the duplacate bets? ---

For example, the conditions:

- Best market Back price < 5
- bets on the selections is hedged (based on matched bets cells)
- unmatched bets on the selection = 0
- Status cells <> PLACING
- Status cells <> PLACED

The command, if the conditions true:
- Place a BACK bet at best market price.

The problem:
- the first Back bet is placed. I get the PLACING and after the PLACED status. Then my VBA clear the PLACED status.
In some cases the matched / unmatched bets cells refreshing a moments later, and my conditions still true (also the market is hedged "condition") -> duplicated bets..

My solution:
- A half-working solution is that I set a timer for deleting placed status (about 3 sec). But sometimes that's not enough, and it isnt a good solution.

Can you suggest a solution for this?
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

I don't know if class modules are faster but it avoids duplicate code and is easier to modify.

The best way to find out what's fastest is to experiment. Use the Timer function to time your code. Avoid frequent reading and writing to spreadsheet cells. For timers, for example, I update them in a variable and update the spreadsheet once a minute.

From what I've gathered, worksheet VBA code does not run in parallel, one finishes before the next can be triggered, assuming you have Application.EnableEvents = False. I presume if you don't disable events while your event code is running it will pause one sheet to run the newly triggered sheet.

In my code, I have a function that fires a SEND command after accepting the parameters for it (it's the only line that fires bets). As well as being able to execute code for each bet fired it returns the current time, so I can always test when the last back or lay bet was fired.
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

I don't know if this is of any use, but this is the order I've figured BA updates Excel spreadsheets.

$A$1:$B$1 - title
$G$1:$H$1 - in-play/suspended
$F$2:$F$4 - tx count/countdown
$A$9:$K$12 - price data
$T$9:$AE$12 - bet data
$AF$9:$AG$12 - additional data
$C$2:$C$6 - total matched/balance


I believe they all get updated in sequence with each refresh so I use the Worksheet_Change event to test that $C$2:$C$6 has changed before executing code.

I've also discovered that editing a spreadsheet while code is running is not a good idea. I've sometimes had 4-5 bets fired. For whatever reason, BA seems to repeat-fire the bet, perhaps because it's unable to write "PLACED" in the status cell while you're editing the sheet.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Thanks for the reply, i will try to use the module.

This formular will be good?

Sub on all worksheet:

Code: Select all

Sub Worksheet_Change
    Call examplemodule
End Sub
In Module:

Code: Select all

Sub examplemodule
My VBA code for clearing status, etc.
End Sub
- Does the module run on the worksheet I called it from?

Timer:
- what timer do you use? Some timer from the forum tested, but if i set for example 5 sec, the VBA / Excel refreshing stop for 5 sec.

I use now this timer, it works:
- start the timer, VBA write the value of cell F4 (countdown time from betangel) to an other cell. Then the timer compare this "other cell" and the actual value of the cell F4. When its greather than for example 5 sec, stop the timer.
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

rain91 wrote:
Fri Mar 03, 2023 8:32 am
Thanks for the reply, i will try to use the module.

This formular will be good?

Sub on all worksheet:

Code: Select all

Sub Worksheet_Change
    Call examplemodule
End Sub
In Module:

Code: Select all

Sub examplemodule
My VBA code for clearing status, etc.
End Sub
- Does the module run on the worksheet I called it from?

Timer:
- what timer do you use? Some timer from the forum tested, but if i set for example 5 sec, the VBA / Excel refreshing stop for 5 sec.

I use now this timer, it works:
- start the timer, VBA write the value of cell F4 (countdown time from betangel) to an other cell. Then the timer compare this "other cell" and the actual value of the cell F4. When its greather than for example 5 sec, stop the timer.
Class modules are explained in the link below. Using a standard module for all sheets will not result in a separate set of variables for each sheet.

https://excelmacromastery.com/vba-class-modules/

I wouldn't recommend storing timers on the worksheet, it's too slow. Use variables. For example, if you want to check 5 seconds have elapsed you can do the following.

Public Const cst5_Secs=5 / 60 / 60 / 24

datLastBet = Timer

If Timer > datLastBet + cst5_Secs then ...


If you need to access the countdown timer and other values on the worksheet read them once and store them in variables as they won't get updated again while your code is running.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

rain91 wrote:
Thu Mar 02, 2023 8:07 am
Does it speed up the system? Or will it be easier just for me?
Class modules have an ever so slight overhead compared to regular modules but this is only really apparent if you are absolutely hammering the RAM on your system
I noticed that if I avoid VBA, everything is much faster, so I always use excel functions whenever possible. But I can't do some things, so I have to use vba, for example clearing the status cells (i habe 10-15 bets in a run / runners, so i need fast system.
Excel functions are developed in either C++ or C# which are both compiled (although technically with .NET it's JIT [Just-In Time] compilation) which will be a hell of a lot faster than VBA which is an interpreted language (and why I prefer rust to python).
do they run in parallel in this case? Or one after the other, so they slow each other down?
All VBA is run in serial, multi-threading doesn't exist for it. This includes for worksheet event trapping such as change/calculate/etc.
If I call it in a module, will it be faster? Or does it get mixed up if all worksheets call the same module?
If you have your code in a module then you only have one code base to modify, you don't have to worry about changing it in multiple locations. It is EXTREMELY good practice to just have your code in one place and call it. There is no performance improvement but I'd rather maintain code called from different worksheets than have the same code repeated 20 odd times.
What is better, faster?
- Worksheet calculate subs?
- Worksheet change subs?
- For-Next cycle or row by row scan the cells?
There isn't a faster/not-faster answer to this and there isn't a better/worse scenario and you're comparing apples & pears throwing in the for/next.
Worksheet calculate triggers when the worksheet has triggered a calculate request
Worksheet change triggers when something on the worksheet has change
For/next is purely a loop.
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

The For-next cycle, that's why I asked, which one is better? The for-next loop is a simpler, shorter code. But when this is running there is always an hourglass at the mouse location and I don't know if that is slower or doesn't matter at all?

Example, for next cycle

Code: Select all

For i=9 to 67 Step 2 ('this is the range of the runners cells)
If Range("O" & i)="PLACED" Then Range("O" & i).ClearContents
Next
or row by row

Code: Select all

If Range("O9"="PLACED" Then Range("O9").ClearContents
If Range("O11"="PLACED" Then Range("O11").ClearContents
If Range("O13"="PLACED" Then Range("O13").ClearContents
etc. until row 67.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

For/next makes it more readable but there is no speed improvement compared to doing it line by line, performance would be exactly the same so For/next is the sensible thing to do.

This is the code I have for my main Excel based workbook.
On each betangel worksheet I have the following which will call gPriceUpdate every single time there is a change on a workbook

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsNumeric(Me.Range("F4")) Then
        gPriceUpdate "BA Data - 1", Me.Range("F4"), Me
    End If
End Sub
This is what is in the code module. Note that I only disable the events as and when required, as in when I am making a modification to the worksheet myself

Code: Select all

Option Explicit

Private Const EMPTY_STRING As String = ""

Private Const BET_RULE_COL As Long = 36

Private Const ROW_START_SEL As Long = 9
Private Const ROW_END_SEL As Long = 21


' This routine places the
Private Sub mTriggerBet(ByRef pobjWs As Excel.Worksheet)

    Dim n As Long
    
    Application.EnableEvents = False
    
    For n = ROW_START_SEL To ROW_END_SEL Step 2                                         ' Iterate through selections
        If pobjWs.Cells(n, BET_RULE_COL).Value <> pobjWs.Cells(n, 12).Value Then        ' Does the recorded rule differ to what we have
             With pobjWs
                .Range(.Cells(n, 12), .Cells(n, 15)).ClearContents                      ' Clear any that has been posted
                .Cells(n, 12).Value = .Cells(n, BET_RULE_COL).Value                     ' Post the new one
            End With
        End If
    Next n

    Application.EnableEvents = True

End Sub

' Clear any of the bet posting cells
Private Sub mClearRuleCells(ByRef pobjWs As Excel.Worksheet)
    Dim n As Long
    
    Application.EnableEvents = False
    
    For n = ROW_START_SEL To ROW_END_SEL Step 2
    
        With pobjWs
            .Range(.Cells(n, 12), .Cells(n, 15)).ClearContents
        End With
    
    Next n
    
    Application.EnableEvents = True

End Sub


Public Sub gPriceUpdate(ByVal pstrSheet As String, _
                        ByVal pdteCountdown As Date, _
                        ByRef pobjParent As Excel.Worksheet)

    Dim objWs As Excel.Worksheet

    Dim lngRow As Long
    Dim n As Long
    
    Const LTP_ROW As Long = 3
    
    Set objWs = ThisWorkbook.Worksheets(pstrSheet)
    
    ' If SET_SVs are set and outside of time range then clear. The first row will have a value populated so can check that
    If pobjParent.Cells(9, 12) <> EMPTY_STRING Then
    
        If pdteCountdown < TimeSerial(0, 0, 30) Or pdteCountdown > TimeSerial(0, 5, 0) Then
            mClearRuleCells pobjParent
        End If
    
    End If

    ' If time is in range then set the values
    If pdteCountdown > TimeSerial(0, 1, 45) And pdteCountdown < TimeSerial(0, 4, 49) Then
    
        If pobjParent.Cells(9, 12) = EMPTY_STRING Then
            mTriggerBet pobjParent
        End If
        
    End If

    ' Time is in capture range, find the associated time and make sure data is empty
    If pdteCountdown <= TimeSerial(0, 10, 0) And pdteCountdown >= TimeSerial(0, 1, 0) Then

        lngRow = objWs.Range("K2").Value
        If lngRow > 0 Then
            ' Empty - populate all selections with the LTP
            If objWs.Cells(lngRow, 2) = EMPTY_STRING Then
            
                Application.EnableEvents = False
            
                For n = 2 To 7 Step 5
                    If Not IsError(objWs.Cells(LTP_ROW, n)) Then
                        objWs.Cells(lngRow, n) = objWs.Cells(LTP_ROW, n)
                    End If
                Next n

                Application.EnableEvents = True
        
            End If
        
        End If

    End If
    
    
    ' Does the associated data worksheet need to have its data cleaned
    If (pdteCountdown < TimeSerial(0, 1, 0) And Application.WorksheetFunction.CountA(objWs.Range("B12:B607")) > 0) Or _
        (pdteCountdown > TimeSerial(0, 10, 11) And Application.WorksheetFunction.CountA(objWs.Range("B12:B607")) > 0) Then

        Application.EnableEvents = False
        For n = 2 To 7 Step 5
            With objWs
                .Range(.Cells(12, n), .Cells(627, n)).ClearContents
            End With
        Next n
        Application.EnableEvents = True

    End If
    
End Sub


User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

ODPaul82 wrote:
Sat Mar 04, 2023 2:07 am
Excel functions are developed in either C++ or C# which are both compiled (although technically with .NET it's JIT [Just-In Time] compilation) which will be a hell of a lot faster than VBA which is an interpreted language (and why I prefer rust to python).
I'm impressed with your knowledge. I thought VBA was pre-compiled, albeit, on the fly?
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”