Guardian -> Excel update speeds

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Why do you want to check the sheet 500 time a second? I can't imagine Betfair even stream the data at that speed. Surely just using streaming would be much more efficient, streaming is supposed to push to BA so not as if you'd miss any changes as I'd imagine it will also buffer data, the API data is also timestamped but dunno if BA checks the stamp times or sends that data to excel.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

spreadbetting wrote:
Wed Jun 27, 2018 4:41 pm
Why do you want to check the sheet 500 time a second? I can't imagine Betfair even stream the data at that speed. Surely just using streaming would be much more efficient, streaming is supposed to push to BA so not as if you'd miss any changes as I'd imagine it will also buffer data, the API data is also timestamped but dunno if BA checks the stamp times or sends that data to excel.
I don't need to, it was just a measure to see if i was under the 50/s. I'm at about 100/s now that i've got 50 cells and 3 charts being updated.
I'm using streaming, the timestamp doesn't come through. I'm happy with the aprroximation i'm getting now that I've seen more trn/s coming through. I think the api is just a bit sticky atm.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

The API is always gonna be under pressure with the WC and tennis on I guess.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I probably can't see for looking but is there anything obvious with this code?

It works great for changes to L10 that I key manually but it's not spotting any changes to F4 or K10 when BA is connected?

It's coded this way becuase I only want to spot changes to the sheet i'm currently interested in ie SourceSheet$ and CurrMktNum#
The main code loop (while recording is active) looks for Changed(CurrMktNum) <> 0 and calls my processing.


Private Sub Worksheet_Change(ByVal Target As Range)
ChangeCycles = ChangeCycles + 1
DoEvents
If SourceSheet <> "" Then
If Target.Address = Sheets(SourceSheet).Range("F4").Address Then
Changed(CurrMktNum) = 1
End If

If Target.Address = Sheets(SourceSheet).Range("K10").Address Then
Changed(CurrMktNum) = 2
End If

If Target.Address = Sheets(SourceSheet).Range("L10").Address Then
Changed(CurrMktNum) = 3
End If
End If
End Sub
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 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
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.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”