More Trigger Grief

[boycee]

I have usually no problems with these triggers, but today I have set one up which refuses to fire the bet into Betfair. So I am missing something obvious, obviously.

I am using 2 sheets with 2 triggers. Ist trigger fires in a back bet. Works perfectly. Once the bet has been taken the 2nd trigger comes in and fires in a lay bet. Except it doesn't. Lay appears in the trigger column ok, but it won't fire it into Betfair.

The stakes and the odds are calculated from the first completed Back bet, using average matched values. I have tried using ROUNDUP and formating the cells but still no luck.

Anyone had similar problems and managed to get a fix?

[thunderfoot]

Might be down to the recent Betfair changes and the problems earlier in the week with the EXCEL link.

[boycee]

Yes tis a weird one right enough - still no fix.

Perhaps I should try a bit more of an elegant solution to this using a single sheet.

This is where my lack of Excel knowledge lets me down though.

Say I have triggered a Back bet and the Average Matched and Matched stake cells are populated. How do I clear them, as well as the original Back trigger and place a Lay bet, using the Average Matched Odds and Matched Stake that had previously been matched?

[Captain Sensible]

Boycee if there's a bet reference left onscreen no further bets will be sent until thats cleared. Just use the CLEAR trigger before placing the next lay/back bet.

[boycee]

Nah the fields are all clear Capt.

I am logging the data into 2 separate sheets on the same excel file. That means having 2 separate pages of Betting Assistant feeding the same data into individual sheets. Is that possibly the problem? Would I have to use 2 excel files, as opposed to 2 excel sheets?

My brain hurts now.

[Captain Sensible]

Can't think of anything obvious might be down to the site change.

Having a couple of sheets wouldn't make any difference and Gary amended the coding on BT so it'd round up be4 submitting bets.

[boycee]

OK Capt.

Will try some amendments in the morning and see if I can crack it.

[boycee]

Well I cracked it by starting from scratch and redoing my sheets. Not sure what the problem was but works fine now.

Another problem I have is the 2nd trade is placed just as the market goes "In Play" and this sets off a trigger fine. But sometimes it does not actually make it to Betfair "Pending" is all I get an it goes no further. Sometimes it goes in OK, so I think Excel might be firing it in too quick for Betfair and it is not getting picked up at their end. Is there a way of delaying the bet say for 2 seconds after the market has gone "In Play"?

[boycee]

Need to find a fix for this "PENDING" problem. When the market turns in play the trigger fires and my bet gets stuck at "PENDING". If I manually clear the "PENDING" the trigger fires it in again and the bet goes through OK. So it seems Excel is definitely presenting the bet too quickly for Betfair as the market is just turning "IN PLAY".

Any ideas how to slow it down, even for a couple of seconds?

[GeorgeUK]

you might be able to use the application.ontime method

eg
application.ontime now + timevalue("00:00:02"), macro-to-send-bet

[boycee]

GeorgeUK,

Can you give me an example of how I would incorporate this into my triggers in column N?

I have used the application.ontime method for refreshing sheets and web query but not for triggers in column N.

cheers

[GeorgeUK]

Depends what is in column N

I'm guessing it's a formula - i use code so i just put the wait in there.

Thought i had it, but need to pass it across to someone else.
What i thought was ...

Code: Select all

Function timedelay(DelayFormula As String)
Dim x As Range
Set x = Application.Caller
If x.Column = 2 Then '14 Then
x.Value = DelayFormula
End If
End Function


in an ordinary module and

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then 'this is the "In Play" cell
Application.Wait (Now + TimeValue("00:00:02"))
End If
End Sub


in the worksheet module

then you would type =timedelay(IF(A1<>"","YES","NO")) (this was just for testing) as the triggers
unfortunately "Delayformula" is calculating in stead of remaining as text - then we could just throw it back in to be Application.worksheetfunction Delayformula
(or something along those lines - probably easier doing it all in code)

[nanook]

I wonder if you are triggering when the market is suspended even though it has gone in play and that is why you are stuck with "PENDING"?

Before the market goes in play you have "Not In Play" in the "E" column.
When the market goes in play you have "In Play" in the "E" column and "Suspended" in the "F" column.
Then when it unsuspends the "F" column cell turns blank.

So your formula would need to also check that the "F" column cell is blank?

[boycee]

You could be right nanook - I will alter the formula and see if that cures it. At the moment I am triggering on "In Play" and haven't covered the "Suspended" scenario. I had assumed that cell cleared before "In Play" appeared.

Cheers I think this will do the business mate!

[boycee]

Magic nanook

SORTED!!!!!!!!!!!!!!!!!!!!!!!