Macro stopping Guardian data refresh

[Wolf1877]

Hi all

I've written a macro that loops through the Bet Angel worksheet data loaded via guardian. My macro could run for 2 minutes, 10 minutes or 30 minutes depending on what I want.
My problem is that the data loaded by guardian is not refreshed whilst my macro is running which kind of defeats the point of what I wanted the macro to do.

Has anyone had this problem with a continuously running macro stopping the external data refresh and found a solution?

I'm a BetAngel newbie but no stranger to VBA coding excel macros.

[spreadbetting]

Might be down to the code you're using to loop thru them, Application.Wait, sleep , simple refresh timer etc Have you tried tweaking the code you use to delay the loop see if that's causing the sheet to stop receiving updated data.

[Wolf1877]

As an experiment I introduced Application.Wait of between 5 and 20 seconds - no joy.
The BetAngel data does not repopulate while the macro is running and but it resumes refreshing as soon as the macro finishes its run.

I should mention that I'm using Office Professional Excel 2010 with Windows 7 Pro on a 64 bit i5 with plenty of Ram.

I looked at the Sleep function on the URL below here but the code provided to Declare SLEEP doesnt work.
http://www.exceltrick.com/formulas_macr ... functions/

I'm wondering if it might be due to my ancient 2010 version of Excel and I may have to upgrade it, but I'd hate to do that and then find out it makes absolutely no difference.
Alternative is that I have to keep repeatedly re-triggering separate smaller macros but I have concerns that this would be able to complete my desired processes quickly enough.

[spreadbetting]

I doubt it's your version of excel it's just the fact Application wait and sleep both suspend all excel activity whilst they're running. Have you consider a simple timer just take a timestamp and compare it to NOW() to take a timng, if it's less than your value just exit the sub, when more reset the timestamp do whatever's needed etc

[workpeter]

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.

[Wolf1877]

Basically I have managed to rule out my code as an issue completely.

If I go to the "Bet Angel" or "Bet Angel (2)" worksheet or whatever whilst they are being refreshed from Bet Angel Guardian, the sheets are being refreshed fine UNTIL such time that I click on the Macros icon (within Developer tab) to bring up a list of macros. As soon as I click the Macros icon the refresh stops while the dialog box is open even before I actually start running a macro. If I click cancel in the dialog box (so as not to run a macro at all) the Bet Angel worksheets immediately starts refreshing again. Or if i select a macro and run it via the dialog box, the refresh resumes just as soon as the macro has finished its run.

So basically it looks like data does not refresh after the macros dialog box is opened until a macro is run or it is cancelled.
I've also tried assigning a button to invoke the macro but exactly the same. No refresh of Bet Angel data until the macro run stops.
I should add that any cells populated by my Macro in the my personal worksheets within the workbook are refreshed OK during any macro run.

Very weird. It almost looks like data refresh from Bet Angel Guardian is always suspended (leaving fixed snapshots in the Bet Angel sheets) whilst the macro is running. At least for my set up. I'd be very surprised if nobody else has hit this problem.

[spreadbetting]

It's down to the way excel processes the VBA not BA, whilst any code is being processed excel stops any other further data being sent to the workbook otherwise you'd have allsorts of problems where cells may have been changed by VBA but then overwritten with a BA refresh before the VBA had been fully processed.

When you pull up the dialog box to choose your macro it will also freeze any further data being received by the workbook as it assumes you wish to run any code with the current sheet data. Your vba is free to change any cells as per the code but data can't be entered externally on the sheet until the VBA has fully completed. When you use wait or sleep it delays the code from fully completing so that's why it won't accept any further data from BA until that wait/sleep has expired and the VBA fully run it's routine.

[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.

Yes I think that I will have to go with Worksheet_Change event and trigger a separate small macro task for every update received rather than a long running single macro task. 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.

[Wolf1877]

It's down to the way excel processes the VBA not BA, whilst any code is being processed excel stops any other further data being sent to the workbook otherwise you'd have allsorts of problems where cells may have been changed by VBA but then overwritten with a BA refresh before the VBA had been fully processed.

Intermediate updates would not have been a problem for me as I was able to copy a snapshot of required cell values from each BA sheet (into a personal sheet) using a single VBA range value assignment statement. I was effectively treating the BA populated cells as readonly. I can understand why BA might want to lock the data to prevent the possibility of anybody mixing and matching old and new data and maybe triggering unintended trading but i'd be quite surprised if this a built in excel feature.

Anyway many thanks for your help and general input. Appreciated!

[workpeter]

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.

[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.