Excel sheet and refresh rates

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
firlandsfarm
Posts: 2687
Joined: Sat May 03, 2014 8:20 am

In this quiet time I'm revitalising a few projects and would just like to check something in particular. I have been having some spreadsheet 'congestion' with one project that I didn't get around to solving so would just like to check a fundamental … Am I right in thinking that the way it works is that Excel is given a set of data from Guardian, processes the data as required and then waits for the next set of data toto process. But if the next set of data arrives before the previous set has been processed I assume that will cause Excel to jam up. So extend the refresh rate and see if that helps … just looking for confirmation that my thinking is along the right lines?
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

firlandsfarm wrote:
Sat May 02, 2020 5:27 am
In this quiet time I'm revitalising a few projects and would just like to check something in particular. I have been having some spreadsheet 'congestion' with one project that I didn't get around to solving so would just like to check a fundamental … Am I right in thinking that the way it works is that Excel is given a set of data from Guardian, processes the data as required and then waits for the next set of data toto process. But if the next set of data arrives before the previous set has been processed I assume that will cause Excel to jam up. So extend the refresh rate and see if that helps … just looking for confirmation that my thinking is along the right lines?
firlandsfarm, if you are talking about excel macros crunching numbers then that has been my observation from some time ago. Basically Guardian refreshes each races excel sheet a frame of data at a time (actually in 6 or 7 separate cell range refreshes per frame) then cycles through the other Guardian Markets. Frame refreshes seem to be skipped if your macros are still running in your workbook.

I personally dont use excel formulae in the cells so I cant speak for the situation where excel is still crunching formulae following a frame refresh but it is highly likely to be the same kind of thing.

I'd say you need to keep your code as efficient as possible to minimise the period where Excel cannot handle Guardian refreshes. Thats why I prefer hand coded macros (not recorded macros) over excel cell formulae as I believe that macros can be written more efficiently than formulae. I only trigger my excel macros when the C2:C6 cell range is refreshed to minimise processing. C2:C6 is one of the 6/7 cell range batches updated by Guardian per frame of data as I dont want to run my actual macro 6 times for every frame refresh.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$2:$C$6" Then
     Call LogData(1)
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
User avatar
firlandsfarm
Posts: 2687
Joined: Sat May 03, 2014 8:20 am

Wolf1877 wrote:
Sun May 03, 2020 10:03 am
firlandsfarm, if you are talking about excel macros crunching numbers then that has been my observation from some time ago. Basically Guardian refreshes each races excel sheet a frame of data at a time (actually in 6 or 7 separate cell range refreshes per frame) then cycles through the other Guardian Markets. Frame refreshes seem to be skipped if your macros are still running in your workbook.

I personally dont use excel formulae in the cells so I cant speak for the situation where excel is still crunching formulae following a frame refresh but it is highly likely to be the same kind of thing.

I'd say you need to keep your code as efficient as possible to minimise the period where Excel cannot handle Guardian refreshes. Thats why I prefer hand coded macros (not recorded macros) over excel cell formulae as I believe that macros can be written more efficiently than formulae. I only trigger my excel macros when the C2:C6 cell range is refreshed to minimise processing. C2:C6 is one of the 6/7 cell range batches updated by Guardian per frame of data as I dont want to run my actual macro 6 times for every frame refresh.
Hi Wolf, thanks for your response. I did actually raise more detailed problems I was having with this project at the end of last year (I've put a link to that thread below). Callum and Spreadbetting kindly tried to explain things but I was struggling to fully understand probably because I was/am not a VBA man. I then I got distracted :( ... don't we all sometimes. :) I started this as a new thread not in duplication but I just wanted to try and get some feedback on this refresh issue without the noise of the detail from before and I think it has worked. :)

I think your input has helped me to better understand what they were saying. I wasn't seeing the operational difference between having Excel or VBA perform calculations, I guess I just looked at VBA as telling Excel what to do a bit like a fancy macro and not that it can do it itself and faster. But I guess I'm now going to have to take my brain back nearly 50 years to coding array manipulating! :)

viewtopic.php?f=19&t=19957&hilit=excel
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

firlandsfarm wrote:
Sun May 03, 2020 4:14 pm
Hi Wolf, thanks for your response. I did actually raise more detailed problems I was having with this project at the end of last year (I've put a link to that thread below). Callum and Spreadbetting kindly tried to explain things but I was struggling to fully understand probably because I was/am not a VBA man. I then I got distracted :( ... don't we all sometimes. :) I started this as a new thread not in duplication but I just wanted to try and get some feedback on this refresh issue without the noise of the detail from before and I think it has worked. :)

I think your input has helped me to better understand what they were saying. I wasn't seeing the operational difference between having Excel or VBA perform calculations, I guess I just looked at VBA as telling Excel what to do a bit like a fancy macro and not that it can do it itself and faster. But I guess I'm now going to have to take my brain back nearly 50 years to coding array manipulating! :)

viewtopic.php?f=19&t=19957&hilit=excel
firlands, I'd say spreadbetting's post on 20th Nov from your old thread contains a lot of sound advice but be aware that there are differences of opinion on what makes excel macros run faster in guardian.

Where I have a slightly different opinion from spreadbettings advice is in the necessity of storing values in an internal VBA array for performance reasons. I personally dont use internal VBA arrays. I note that you are a bit reticent about using VBA arrays. As far as I'm concerned an Excel sheet itself is an in memory array and I have written some excel code that runs very fast with guardian that assigns data from excel cells into variables and vice versa with a workbook connected with up to 80 Bet Angel markets. Some people claim this is slow but I've not found that to be true and my code reads from and write into a lot of cells directly without the performance problems they claim.

IMO the key to coding fast read/write access to cell values is
(a) Always address the excel cells using the Sheets() qualifier and the Cells() qualifier - prefereably with a numeric sheet number, row number and column number.
(b) Avoid using excel formulae for reasons outlined by spreadbetting

Excel VBA stuff that I avoid
(1) Dont change the active sheet via VBA. I cant see any case where you would need to do this in a guardian attached workbook if you use the Sheets() qualifier as per point (a) above.
(2) Dont select/copy cell ranges in VBA
(3) Avoid copying code from recorded excel macros as it simulates manual excel processes but isnt necessarily very efficient.

Some of my guardian linked excel coding methods shown in code box below but bear in mind that people have different opinions on what works fastest.

Code: Select all


Sub LogData(s As Integer)

  r = s + 1    

		'  NOTE: Sheets and Cells can be accessed via a name, an integer or a variable (as below)
  matched_diff = Sheets(s).Cells(2, 3).Value - Sheets("RaceSummary").Cells(r, 5).Value             
  
   		'  NOTE: you can exit and terminate a VBA subroutine for efficiency whenever further code not deemed needed (as below)
  if matched_diff = 0 Then															     
    Exit Sub
  End if

  		' Code below saves matched volume in "RaceSummary" sheet for comparison on next Guardian frame refresh
  Sheets("RaceSummary").Cells(r, 5).Value = Sheets(s).Cells(2, 3).Value                                         
 
  matched_diff = Round(matched_diff, 2)

'---------------------------------------------------------------------------------------
'-- insert more VBA code here to do whatever you want
'---------------------------------------------------------------------------------------
 
 End Sub
  
  
User avatar
firlandsfarm
Posts: 2687
Joined: Sat May 03, 2014 8:20 am

Thanks again Wolf, I'm very grateful for your input you have clarified issues and given food for thought. :)
User avatar
firlandsfarm
Posts: 2687
Joined: Sat May 03, 2014 8:20 am

Wolf1877 wrote:
Sun May 03, 2020 8:00 pm
I only trigger my excel macros when the C2:C6 cell range is refreshed to minimise processing. C2:C6 is one of the 6/7 cell range batches updated by Guardian per frame of data as I dont want to run my actual macro 6 times for every frame refresh.
Hi Wolf … well I'm pleased to say I have resolved my shortcomings and in the process have managed to have a response and solution from BA … I hope they formalise what's in the thread … viewtopic.php?f=20&t=21033 … because I know I'm not the only one to have problems with this. It's probably the first problem anyone new to VBA on BA will come across. They've even published a revised spreadsheet for download although I think it goes too far, it clears everything including your Bet Placement formula, your Odds formula and any formula/entry for staking! I have added a post to the thread warning users of this and advising how to adapt the code which as you will know is quite simple.

I chose the above quote of you for this post because at the time you posted it I had no idea what it was about, I didn't realise BA refreshed in phases but now I do with the BA guys saying the same as you.

Now I'm going to tackle moving my formulae from the cells and into VBA. I don't need extra speed but it will be a good exercise to learn on. :)

Again, many thanks for your assistance.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

firlandsfarm wrote:
Wed May 20, 2020 7:07 am
Again, many thanks for your assistance.
Good luck with your VBA adventure Firlands.
:D
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”