Guardian -> Excel update speeds

Discussion regarding the spreadsheet functionality of Bet Angel.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

spreadbetting wrote:
Mon Jul 02, 2018 3:04 pm
I don't use BA but what gets sent to those cells? Wsheet_change won't recognise a calculate event, do they contain formulas rather than having data sent to them?

You can always switch them within the code to

Code: Select all

If  Range("L10").Value ="whatever you're looking for"  Then
F4 & K10 are updated by BA
mmm
I had something like If Range.Value <> Last RangeValue Then before and just blazed round a loop. It worked but I was trying to be more efficient by detecting just changes because I was finding that after 30mins the sheet was getting slower and slower to be updated. Toggling DDE in the options restores the speed but it's hardly practical.

The problem seems to be that Target is being returned as the whole sheet range changing, not just cells 1 by 1. I might need an Intersect or something.
One for later anyway...thx.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

BA probably sends the data in separate batches on each refresh and each batch will trigger a worksheet_change event, you could always check how the data is sent to excel then limit your code to whichever data refresh is relevant

Check what data gets set using the intermediate window

Code: Select all

Debug.Print time() & " " & Target.Address & " ~ " & Target.Columns.Count
Then exit if it's not the data refresh you want

Code: Select all

If Target.Columns.Count <> ? Then Exit Sub
Even if it's not relevant for this situation it's worth using so you only fire your coding when all the data you need has been sent to the sheet and you don't either duplicate thngs or act on data from a previous refresh.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I've always avoided worksheet_change, it's a pita. As soon as you've got some code in there, whenever you touch your sheet the sub launches.

Is there a way to disable it so it only runs when you actually want to execute your code?
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

The whole point of worksheet_change is that it monitors the whole sheet, if you only want to monitor a single cell you'd use something like

Code: Select all

 If Target.Address = "$F$4" Then
I'm not sure what

Code: Select all

If SourceSheet <> "" Then
means in your code I'm basically a tinkerer with excel, but they'd be no need to refer to the sheet as the worksheet change is only monitoring the sheet under which you've entered the worksheet_change for, simply referring to the cell on that sheet is sufficient as far as I'm aware. Maybe remove any reference to sourcesheet and try again. Plus to limit the amount of checks you'd use the code posted above to limit to run only for the data refresh you wanted, also you'd bookend your code with turning events off to stop any data being written to the sheet possibly triggering off an endless loop.

Code: Select all

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


.......coding


'turn it all back on again

Application.Calculation = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


If you want to look at ranges you can use things like

Code: Select all

If Not Intersect(Target, Range("F4,L10,K10")) Is Nothing Then
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

spreadbetting wrote:
Mon Jul 02, 2018 5:36 pm
The whole point of worksheet_change is that it monitors the whole sheet, if you only want to monitor a single cell you'd use something like

Code: Select all

 If Target.Address = "$F$4" Then
I'm not sure what

Code: Select all

If SourceSheet <> "" Then
means in your code
That first one is what I started with :)

All my processing code exists in 1 module and I like it that way :) The idea of SourceSheet was that it's the name of sheet I'm currently monitoring via a DashBoard tab. Therefore, if any other sheets have their WS_Change event triggered (maybe an overlap on the refresh from BA) it's supposed to be ignored unless it's happened on whichever SourceSheet I'm monitoring. I'll find a way round it but I'm determined not to have 70 copies of similar code.

Thanks for the heads up about the events methods, I hadn't thought about toggling those at various times.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

In the sourcesheet range a1 i've got the name of the sheet i want to monitor.

The sub will only be called if changes happen on that sheet.
You do not have the required permissions to view the files attached to this post.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Tue Jul 03, 2018 9:22 am
In the sourcesheet range a1 i've got the name of the sheet i want to monitor.

The sub will only be called if changes happen on that sheet.
Thanks nigel, much appreciated. I hadn't thought about going down the Me. route.

I'm still not sure why If Target.Address = Sheets(SourceSheet).Range(ValueCellRef).Address Then doesn't work but sometimes it's not worth trying to find out if a workaround does the job.

It might all come down to VBA internals and the fact they've only ever provided a WorkSheet_Change event rather than Workbook_Change event. I don't think 'Target' is aware of what sheet it relates to and just returns K10, F4 etc and not the full reference including the sheet name.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If you want something that works on all sheets and returns the sheet name and address, put in in as a workbook event (not as a sheet event) and also use the parent property

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


MsgBox ActiveCell.Parent.Name & ": " & ActiveCell.Address

End Sub
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Tue Jul 03, 2018 2:56 pm
If you want something that works on all sheets and returns the sheet name and address, put in in as a workbook event (not as a sheet event) and also use the parent property

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


MsgBox ActiveCell.Parent.Name & ": " & ActiveCell.Address

End Sub
That's the event I was looking for! I'm off and running now. Brilliant.

I think I need to RTFM :) I was a vb guy but never a vba guy.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

As a PS to this one.

Is there anyway to auto 'Remove suspended markets' ? Either std functionality or a workaround.(is there an Alt-keystoke I could auomate in Hotkeys or similar?)

I know the idea is that by using restricted refresh they become irrelevant but I'm definately getting slowdowns once about 10 or so build up. The moment I click 'Remove suspended markets' my performance returns to normal.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Never looked into that aspect of it before.

Have you taken into account the number of formulas you have in each sheet and to see if you can convert them to values where necessary?

For example, if I record data once a second for the first 10 horses from ten minutes out that’s 600*10 rows of data or 6,000 formulas in one sheet. Times that by ten and you’ve got 60,000 formulas in your workbook.

I’m inclined to convert them to values once I’ve finished with them. If on the first row (600 seconds out), I have a formula telling me that the runner price is 5.50, then on the second row (599 seconds out), the first formula is no longer needed, it’s done it’s job, so I can then put something along the lines of “Range A1”=”Range A1”. Value

Also, if you are not going to use your data afterwards, there’s no harm in clearing down the whole sheet once you’ve finished with it.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Thanks nigel.
I don't have any formulas at all. While the event is recording everything goes into a 2 dimensional array and I only write it when the markets switch and a couple of seconds to dump it isn't an issue. I have one small chart which is populated directly from the arrays rather than from data on a sheet. But there's no memory leak, every variable is type correct and everything is fully qualified with a complete clear down when there's a change of market.
Clicking 'remove suspended markets' instantly speeds it up even though my restricted refresh is - 600 to +10. That's fine if I'm nursing it but not much good if I want to leave it running and go out.

Performance goes down from 8 caps/s to about 3. That sounds trivial but not for what I'm doing with it.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”