Macro stopping Guardian data refresh

[Wolf1877]

For monitoring and logging market data changes that will be fine but I'm not sure it will be as fast as I would like when I move onto automated trading.

If 100-200ms is too slow for your automation then excel is probably not for you. Either look into guardian only or build your own program without BA.

I'm probably getting ahead of myself here. As a newbie first I need to properly gather and analyse data from the existing markets and then experiment to see how critical any timing/latency delays are to successful profit making via automation. If speed becomes a serious issue for me then at that stage I'll definitely consider all options. First I need to learn.

[Wolf1877]

Using Worksheet_Change event will execute your code every time bet angel sends data to your sheet. Realistically you can expect your code to be executing every 100-200ms if you have a decent computer.

Within this method, if you wish to delay execution of specific code then I would advise you to look into the GetTickCount function. it returns a perpetually increasing long integer based on how long your computer has been on for.
Below is an example how you can use it to delay execution of a particular piece of code by 4000ms.

If GetTickCount() > GetTickCountOffset + 4000 Then
Me.Cells(6, 12) = "GREEN_ALL"
Me.Cells(6, 15) = ""
GetTickCountOffset = GetTickCount()
End If

FYI dont forgot to declare GetTickCountOffset globally so it retains its value and to declare GetTickCount (google it).

This is better than timers/sleep because your application wont freeze. Its also better than using time millisecond conversion comparisons because time has a habit of resetting every minute/hour/day depending on your granularity.

Thanks for the tip Peter. Using Worksheet_Change is definitely the way to go.

I'm doing quite a lot of number crunching per update and also appending logging data for analysis purposes to a couple text/csv flat files. It runs far quicker than I expected and it is keeping up with several updates per second in VBA. Thats not to say I might not need more speed later but for the time being it is just what I need.

[spreadbetting]

To speed up your code you should look to cut down on formulas on the sheet and do all calculations within in the vba. Also loading the data into an array , doing calculations on the array and writing back to the sheet will get your sheet updates to 1 and drastically speed things up.

[Wolf1877]

To speed up your code you should look to cut down on formulas on the sheet and do all calculations within in the vba. Also loading the data into an array , doing calculations on the array and writing back to the sheet will get your sheet updates to 1 and drastically speed things up.

I agree VBA cell assignment is faster. One frustration I do have is how Guardian binds to excel. I was out on Saturday afternoon and I wanted to log UK race data for later analysis. Ideally I would have been able to have had over 50 Bet Angel sheets bound to Guardian to log each race in my absence. Guardian will handle this but obviously very slowly whilst it tracks 50+ events and cycles through refreshing each sheet. In the end I had to settle for logging 15 races only.

In an ideal world Guardian would have an automation feature for its own bind processes so that I could pre specify exactly when it refreshed a "Bet Angel (n)" excel sheet in it to say only connect to a bound sheet say a pre-defined number of minutes before each event start time and to auto terminate a binding immediately after in-play/suspension. Guardian itself would then be quicker cycling through as it could disregard refreshing events outside required monitoring period. I have optimised my VBA code to ignore updates outside of required period but it would be better still if Guardian only refreshed data within the required period. Am I missing something?

[workpeter]

In guardian have a look at advanced settings and have a play with the 'restrict refresh' option.

[Wolf1877]

In guardian have a look at advanced settings and have a play with the 'restrict refresh' option.

This looks like it will solve my problem for now. Thanks.

There is scope for improvement in a future version of the software to allow fine tuning of refresh frequency by event rather than a global on/off setting.
e.g. 1+ hour out an refresh every 5 minutes, 60 to 30 minutes out a refresh every minute, < 30 minutes out full refresh. Just an idea for the Bet Angel developers.