Cymatic Trader Community

[tjmicro]

Hi,
I've written a vba class in Excel, and I call a process within it from the Worksheet_Calculate event for specific runners in horse race.

The process checks for the current odds matching some nominated odds and places a back bet.

This seems to work fine in debug mode, stepping through the code.

However, 10 minutes before a race, the odds are moving quite fast, and either the process doesn't create a back bet at all, or if it does, the bet doesn't get matched before the current odds change.

Is this my code that's inefficient (using a class), or is there a means of setting parameters in Cymatic to scan the bet command range more frequently to ensure the bet is picked up and fired into the market?

I set Application.EnableEvents = False to ensure the process doesn't get interrupted whilst building the bet and set it back to True after the event.

It's as if Excel is running too slow to be able to catch the current odds before they change?

Do I possibly need to slow down Cymatic's refresh rate, rather than speed something up?

Any clues would be greatly appreciated.

[nig299]

Using a class is very helpful and not at all inefficient.

I notice you say "whilst building the bet". What steps do you take to build it?

I've attached a sample spreadsheet that you might find helpful, and like yours, uses the calculate event. This will place a back bet if the lay odds on the first runner (J8) are 2.58

There's a simple formula in BJ8 that checks J8 and returns a "1" if it's true

The code monitors the range BJ8:BJ14 and fires if it finds the "1", placing a "BACK" command in the appropriate command cell.

Notice it also checks the command cell before hand to see if a bet has already been placed, else it'll keep populating the command cell (even though you can't see it).

[Gavin]

I set Application.EnableEvents = False to ensure the process doesn't get interrupted whilst building the bet and set it back to True after the event.

I think you'll find that the bet won't be submitted if you turned off Application.Events and didn't turn it back on until after you had put the desired bet into the command cells.

Setting Application.Events to false, turns off all events including the ones that cause Cymatic to notice that you want to submit a bet. Cymatic subscribes to the events in Excel, that is how it reads your bet orders.

The bet will probably eventually be submitted, but only when another event event fires sometime after you've turned Application.Events back on.

So assuming that you actually do need to use VBA, rather than just formulae, I would suggest that you either don't turn off Application.Events (if that doesn't cause an infinite loop) or that you turn it back on just before your code fills in the bet details into the command cells, to ensure that the bet is instantly submitted by Cymatic.

I vaguely recall that Excel takes quite a lot of time to turn Application.Events on or off. If so, it might be counter-productive to turn it off momentarily, if your only reason for doing so was to try to speed up Excel. If you are doing it to avoid triggering an infinite loop, then it would be better to instead put a clause into the Calculate / Changes events that checks that the cells changed are not the cells that your code is changing (command cell range) by using the INTERSECT command to test which cells changed. Then there is no need to turn Application.Events off.

Kind regards,

Gavin

[nig299]

Also I should point out that the samples I post are not necessarily "best practise". It (hopefully) just gives a few ideas of how to go about solving a problem and might give the user a few ideas.

In this case using the vba 'find' option rather than using a for/next loop to locate what you are after, and acting on the information once you have located the specific cell you need.

[tjmicro]

Thanks for the responses to my query and sorry for the late reply.

I think Gavin may have hit the nail on the head when he mentions that turning off event trapping could be the cause. I'll investigate this further, using the INTERSECT function.

The trouble I have with Event trapping is understanding how the events interact in VBA code. Do they cause the execution of current code to be terminated immediately, or do they form an orderly queue and wait for the execution of the current code to finish before they start their processing?

Knowing this might give me some insight into the sequence of events in the VBA code.

Cheers.

[Gavin]

The trouble I have with Event trapping is understanding how the events interact in VBA code. Do they cause the execution of current code to be terminated immediately, or do they form an orderly queue and wait for the execution of the current code to finish before they start their processing?
.

Events fire when new prices / bet status updates arrive from Betfair and are sent to Excel by Cymatic. I don't think that such data arriving into Cymatic from Betfair would ever cause any VBA code running in Excel to be interrupted.

Kind regards,

Gavin

[tjmicro]

Thanks for that, Gavin.

I'm still a bit uncertain, though, on when Excel fires the Worksheet_Calculate event, and the VBA script starts processing. What happens if the Worksheet_Calculate event gets fired many times before the VBA script has completed? Does the event handler in VBA ignore all subsequent firings until it's current execution has completed and then it fires once? This would probably be the most logical approach to handling events, I should imagine, but is this a built-in feature of VBA, or do I have to control it by turning off Application.Events, and turn them back on immediately before I clear the Bet Status cell and end the event processing?

Do you know of any online material that explains how this event handling process works?

Thanks again for the help.

[Gavin]

The events in Excel are triggered when Cymatic sends price/bet data to Excel, which Cymatic does whenever data arrives into Cymatic from Betfair.

It has been a very long while since I have looked at the part of the Cymatic code that sends refreshed market data to Excel. So from memory, I think that Cymatic checks whether Excel is available and ready before it sends each batch of new data to Excel. So if VBA is still processing something, then Cymatic will realise that Excel is not ready and so it will not send the update to Excel. Hence the events won't be fired if VBA is still processing something. In those circumstances, Cymatic will conflate the data, so that when data is eventually sent to Excel, it will be the latest data.

Kind regards,

Gavin

[tjmicro]

Thanks, Gavin.

That's saved me a lot of time trying to figure out whether or not I can trust the event processing and my own understanding of it.
Much appreciated.
Cheers.