Spreadsheets speed

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

For those of you familiar with using spreadsheets I would appreciate if you could share with me the speed of your VBA code per cycle.

I suspect most of you are using a change_event/calculate_event function to iterate your code. To test the speed of your VBA code in miliseconds use the below code:
The key lines of code are in red

FYI feel free to use the GetTickCount method to pace your code. it's far superior to timers.

As you can see my code iterates approx every 300 milliseconds, which is around 10x slower than the 20 milliseconds streaming in bet angel despite having those settings. i'd like to understand if I should upgrade to a more powerful computer or if this is simply a limitation with excel.

'Declared globally outside methods/functions
Private Declare Function GetTickCount Lib "kernel32" () As Long
Private GetTickCountOffset2

Public Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

Dim KeyCells As Range
Set KeyCells = Range("C2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Me.Cells(4, 39) = GetTickCount() - GetTickCountOffset2


'For Performance
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlManual


'your private code

GetTickCountOffset2 = GetTickCount()

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End If
End Sub
You do not have the required permissions to view the files attached to this post.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

ok managed to speed it up. It is related to my computer processor speed. As a temp measure, I increased the priority to excel in task manager.

Although i did notice having the ladder open slowed it down, and more ladders decreased it further. I think one solution might be for excel to expand the bet angel ladder window only when im exposed, therefore i am still recording my trades, but my trigger isn't impacted. I am sure there is an API i could use.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Another trick you can use is focused your spreadsheet away from your active cells and put the focus on a blank part of the spreadsheet (or less busy part, i.e. a control panel with vital stats).
Very much like our reality, excel doesn't update into a specific state until you observe it.

roughly 2.5x faster
You do not have the required permissions to view the files attached to this post.
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

300ms rings a bell when I last played with it a few years ago - Excel 2010 / Win7 / 3GHz

Didn't know the calc on/off had an impact - always thought when you turned it back on it did a full recalc regardless so have always left it on.

I'm getting interested in trying some .NET addins for Excel which will allow much greater freedom than clunky VBA has.

If you know .NET for any language have a look at https://stackoverflow.com/questions/202 ... g-c-sharp and also the open source library mentioned there - now at https://excel-dna.net/

While that thread was for C#, any of the .NET languages can be used to make an addin.

I would expect an optimised release build .NET addin should work way faster than the equivalent Excel VBA
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

looks interesting. I've not yet required something which isn't readily available through the standard libraries. Maybe you have an example you can share?
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

workpeter wrote:
Fri Sep 01, 2017 4:14 pm
Very much like our reality, excel doesn't update into a specific state until you observe it.
Sounds a bit like schrodinger's cat!

I'm new to betfair and bet angel this week and while I'm impressed with BA I'm already thinking about potential processing speed issues.
c# and .net excel addons may give a speed boost but excel is basically adding an extra layer over say a dedicated c# app.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Wolf1877 wrote:
Fri Sep 08, 2017 5:20 pm
workpeter wrote:
Fri Sep 01, 2017 4:14 pm
Very much like our reality, excel doesn't update into a specific state until you observe it.
Sounds a bit like schrodinger's cat!
seek and you will find
Matthew 7:7
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Peter, I've added your suggested timing tool to my VBA and my macro seems to run in between 0 and 16 milliseconds. The click timer only has a resolution of approx 16 milliseconds according to Microsoft documentation, so sometimes it is not reaching a click boundary and recording a zero milliseconds. Other times it records either 15 or 16. See attached image.

Basically my macro either does VBA calculations on a sheet refresh from Bet Angel and logs race and horse data data by appending to 2 flat files. I am not using any excel formulas.

When the event countdown is > 1 hour out (as per today's races) my Macro only completes data logging a max of once every 10 minutes otherwise it exits the VBA after minimal updates to my RaceSummary worksheet. Columns N and O show the timings for VBA Exit (No Logging) and VBA Run (Logging) respectively. VBA Exit will definitely be quicker as the macro is doing less. Both columns are switching between 0, 15 and 16 milliseconds depending on exactly where in the cycle it catches the GetTickCount.

This is on an i5-3230M running at 2.6 GHz with 8GB Ram. I was on the verge of replacing it last year but I upgraded my drive to SSD last year and it gave a massive performance boost to anything that involves disk IO.
You do not have the required permissions to view the files attached to this post.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

In another thread, you mentioned you are considering using the change_event procedure. Could you give me your timings for that, please?
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

workpeter wrote:
Sun Sep 10, 2017 1:50 pm
In another thread, you mentioned you are considering using the change_event procedure. Could you give me your timings for that, please?
Peter, my 0-16 millisecond timings are with Worksheet_Change triggered by Guardian Bet Angel data refreshes as as follows

I have Worksheets 1 to 20 (named "Bet Angel", "Bet Angel (2)", ....... "Bet Angel (20)") and Worksheet 21 (named "RaceSummary")
Each of Sheet 1 to 20 has the following code assigned except where Call LogData(1) is hard coded as Call LogData(2) ....... Call LogData (20) depending on the sheet.
i.e. I have no intersect logic or anything to determine which cells have changed. (But only Guardian is changing these cells as my macro is not currently updating the Bet Angel sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
Call LogData(1)
' <--- replace (1) with hard coded sheet number for each sheet 1 to 20 ( i.e. Call LogData(2), (3) .......through to (20)) - NB not very elegant
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


and then I have a single module that handles applies the same processing for whenever each of the 20 worksheets is updated by Guardian

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub LogData(s As Integer)

Dim GetTickLocal As Long
GetTickLocal = GetTickCount()

If not wanted condition Then
Sheets("RaceSummary").Cells(s + 1, 14).Value = GetTickCount() - GetTickLocal
Exit Sub
End If

race_id = Sheets(s).Cells(1, 1).Value

....... insert code here (my VBA code is around 154 lines and includes a loop to process data for each runner)
[
b]
Sheets("RaceSummary").Cells(s + 1, 12).Value = updated_time
Sheets("RaceSummary").Cells(s + 1, 15).Value = GetTickCount() - GetTickLocal

End Sub
[/b]
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I think he's looking more for the time it takes for BA to refresh the data rather than how long your VBA within the sheet is taking. So the timer is started at the end of your worksheet_change event and then output to the sheet or debug.print at the start of it to catch the refresh rate from BA
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

spreadbetting wrote:
Sun Sep 10, 2017 5:12 pm
I think he's looking more for the time it takes for BA to refresh the data rather than how long your VBA within the sheet is taking. So the timer is started at the end of your worksheet_change event and then output to the sheet or debug.print at the start of it to catch the refresh rate from BA
OK my misunderstanding was based on the opening sentence of the thread - "I would appreciate if you could share with me the speed of your VBA code per cycle." I agree on closer look that the code suggested looks more like trying to measure the delay between worksheet_change being invoked (by a BA data refresh) rather than the speed of the VBA code.

I also assume this is for standard BA (non Guardian) excel refresh as the suggested code is not relevant for multiple worksheet updates as far as I can see. i.e. say sheet 4 gets updated by BA Guardian and then sheet 6 is next in the cycle, the sheet 6 worksheet_change task thread just picks up the GetTickCountOffset2 global value set by the sheet 4 worksheet_change task thread and the result is pretty meaningless.

I'll take another look at this tomorrow. I'd also be interested how the quoted figure of 20ms was determined for refreshes within standard BA itself.
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Whilst I don't use multiple sheets, the same logic should apply. We can do it on one sheet or many. Yes, GetTickCountOffset2 is declared globally but it's also set to private so its only seen within the sheet/module is declared within. This will be an interesting experiment with multiple sheets because you should notice the offset on each sheet slow down as you add more markets. Whilst using 1 sheet will give you the fastest response. the shortest gap between cycles I have seen on my computer is around 70-90ms.

I think having a SSH is defiantly advantageous due to the many IO calls betangel makes to excel.exe (see attachment).
You do not have the required permissions to view the files attached to this post.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

workpeter wrote:
Sun Sep 10, 2017 8:08 pm
Whilst I don't use multiple sheets, the same logic should apply. We can do it on one sheet or many. Yes, GetTickCountOffset2 is declared globally but it's also set to private so its only seen within the sheet/module is declared within. This will be an interesting experiment with multiple sheets because you should notice the offset on each sheet slow down as you add more markets. Whilst using 1 sheet will give you the fastest response. the shortest gap between cycles I have seen on my computer is around 70-90ms.
If I am understanding your code correctly, your computation is only being performed when the Matched total changes based on the intersect logic, however Excel is triggering the Worksheet_Change process whenever excel detects that ANY SINGLE CELL has changed within the worksheet. And then your code disregards the detected change unless the C2 cell has changed. BA is refreshing cells a lot more frequently than it changes cell C2 (matched total). At least this is what I am observing.

I'd suggest that you move your GetTickCountOffset2 assignment after the End If and before the End Sub to get a more accurate calculation of how long excel is taking to process each individual WORKSHEET_CHANGE task. In my case it is between 0 and 16 ms. Now it might be that excel is slowing things down by being a little too quick off the mark in running WORKSHEET_CHANGE too frequently! Say for example that BA updates 50 cells in a worksheet for a logical collection of updates. Ideally Excel would trigger WORKSHEET_CHANGE just the once after the full group of 50 cells has updated. In practice Excel seems to detect several small groups of cell changes and triggers WORKSHEET_CHANGE many times over rather than just once. Your logic then disregards the change unless cell C2 has changes. I'm not aware of any options available in excel to control or throttle back Worksheet_Change to optimise triggering.

Obviously Excel is a great tool but fundamentally it was designed for general number crunching purposes rather than handling millisecond critical time sensitive control processes like BA automation. Excel still manages to cope pretty well though with it all things considered!

Anyway my code suggestion to calculate the time since the last WORKSHEET_CHANGE excel process.

'your private code

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End If
GetTickCountOffset2 = GetTickCount() '<--- Move your GetTickOffsetCount2 assignment outside the IF THEN ..... ENDIF logic so it refreshes every time the process runs.
End Sub
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

A small addendum to my immediate previous post.

Looking into it further flipping Application.EnableEvents between False and True in your code is temporarily switching off new "Worksheet_Change" events and does throttle it back..
I assume that this is because your code might be loading BA commands into columns L to O and you dont want to trigger BA again - however - this also is potentially slowing down Worksheet_Change from being triggered when C2 would have been updated and slowing down your response.

I'm not sure if this would be because
  • BA deliberately does not update the BA sheet cells while your code is in progress[ or if
    BA cannot update the BA sheet cells while your code is in progress due to locking (so it gives up) or if
    BA does update the BA sheet cells while your code and it goes undetected because Application.EnableEvents is switched off
What I am seeing without Application.EnableEvents switched off at all in my code is a typical delay of between 0 and 16 milliseconds between the end of one Worksheet_Change and the start of a new Worksheet_Change task (for the same worksheet). I'm now storing the GetTickCount() long value from the end of a Worksheet_Change in my independent "RaceSummary" worksheet and then reading it back at the start of a new Worksheet_Change event to compare timings. I should further add that I'm not currently assigning BA commands into columns L to O so I have no concerns about worksheet_change being re-triggered by my own assignments.

Logically It might be better to have two computers running linked to BA, 1 to receive incoming data from BA only and 1 to load commands into BA only!
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”