Closing quick Excel link programmatically?

[Joe68]

Using my spreadsheet manually, I got the habit of severing the link to BA shortly after live betting was complete on the thoroughbreds so that the data was not erased by the updates that continue until the market is cancelled.

I have tried to replicate this programatically from Excel incell formulae.

I have a series of refresh rate changes as the time to jump approaches then when the event is finished - distinguished by being both In Play and suspended, my refresh rate code changes the cell value to 1000.

But
the value of 1000 is not passed back to BA. BA simply sits there with the previous refresh rate, pumping data at my spreadsheet so I lose the bet references, SP and other data.

How do I stop updates from BA to Excel at the end of every race?

[Captain Sensible]

Have you got trigger betting enabled?

If you search the forum there's lots of vba code around for copying data you could always set it to take a snapshot of the market and save it at the end of a race instead of doing everything manually.

[alrodopial]

Is 1000 a valid number for refresh rate?
What is the upper limit?

[Captain Sensible]

I think 9999.0 is the limit but I'd imagine there's error coding in there to change anything over 9999 to 9999.0

[Joe68]

Thanks man. Used 1000 as that is the manual limit when setting directly in BA.

Either there's something going on with the limit and error handling or something unusual happens when the race is run.

Trying 999.

[Joe68]

Thanks Capt Sensible. The refresh value is now being passed back to BA successfully.

However, it is not accomplishing the desired effect.

The link updates 999 seconds later and erases my Bet data.

How can I prevent the link from updating after the race is run please?

[alrodopial]

The link updates 999 seconds later and erases my Bet data.

How can I prevent the link from updating after the race is run please?

Is there a Q2 trigger that kills the associated market?
If not, you must copy/paste the data you want at the end of the race

[Captain Sensible]

Thanks Capt Sensible. The refresh value is now being passed back to BA successfully.

However, it is not accomplishing the desired effect.

The link updates 999 seconds later and erases my Bet data.

How can I prevent the link from updating after the race is run please?

What data are you actually trying to save? I don't think there's any Q2 trigger that turns off the auto refresh so your best bet is setting up some VBA to copy whatever data you're looking for, plus you'd be able to let it crawl thru all the days markets. If you're there you could just click the auto refresh box to off to stop any logging. Plus depending on what you're after Betfair do provide alot of data files regarding sp's highs, lows etc quite alot of us download them on a regular basis rather than waste time gathering our own data.

[Joe68]

Thanks mate.
What I'm trying to save is this stuff:

Bet ref Bet time Avg matched odds Matched stake Profit / Loss
2.5
2.5
2.5
2.5
3224357072 4:13:53 PM 2.42 2.67 -3.79

If I could just programmatically prevent updates after the event is finished, all would be good.

The reason is I use a separate worksheet for each event and the type of data above is my record of trades and possible outcomes.

At some point I'll sit down and put a database behind the system, but for now I'd just like the trade data not to be blown away.

Could programmatically take a copy and plonk it elswhere on the worksheet, but seems sort of messy.

What's a Q2 Trigger?

[Captain Sensible]

By Q2 trigger I just meant there's no special command to put in Q2 to stop logging the market.

Not too sure why you consider taking a snapshot at the end and placing it elsewhere is messy as it's pretty much your only option for now unless Gary adds another Q2 command to turn off the auto refresh.

[Joe68]

Thanks. Have looked up reference on those Q2 commands - nice, but not for this problem.

Messy? Just sort of inelegant to have the data there and have to copy it to keep it.

Not to worry - have done exactly that and it sorts the issue for now.

The longer term, more elegant solution I think is to use the COM server object model and shoot all my bits and pieces into MySQL - write a little app to run the strategies.

Still, this Excel link is handy for rapid strategy development. If only the bloody things would make money, I'd feel justified sinking that sort of development time into the project

[austingrd]

You'll get there in time. I mean these things don't have a short cut and it will take a while for us to figure things out.

[Joe68]

Thanks mate. People do it don't they?

It just feels illusory - that whole "I've got it figured out this time, all I've gotta do is XYZ and it'll work." Then you do it and it doesn't work. Again.

As you say - time is the answer.