Green up auto following Back Bet

[Sussexguy44]

Hiya,

I have a spreadsheet based on placing a back bet dependant on an increase in matched volume between a certain period.Once the bet is matched what I am looking to do is to then auto set up a Greenup/Redup bet at 15 seconds before the off(or when a profit/loss is reached) I have noted the spreadsheet Greenup auto which I have downloaded. Is what I am looking to do possible?

[Sussexguy44]

Re above, another option is to place a lay bet at 15 seconds before the off however the stake would need to be changed accordingly - I hope this makes sense.

[brumbie]

In the log multiple sheets quick link, you need to tick Close Profit/Loss. This will add another column which will let you know that if any bet you have made is at a level profit/loss.
By referring to this column you can decide to CLOSE or not. CLOSE will close the position at a level profit/loss.
So bearing in mind that, now add this formula at the top cell of the column (or anywhere else):

=IF(LEFT($D$2)<>"-",(HOUR($D$2)*3600)+(MINUTE($D$2)*60)+SECOND($D$2),-((HOUR(SUBSTITUTE($D$2,"-",""))*3600)+(MINUTE(SUBSTITUTE($D$2,"-",""))*60)+SECOND(SUBSTITUTE($D$2,"-",""))))

This will give you the countdown seconds to the off.
All you have to do now is refer to that cell for your seconds b4 the off and any corresponding cell in the below column (the Close Profit/Loss column) to decide whether to CLOSE or not.

Assuming it is column AA then something simple like:

=IF($E$2="Not In Play",IF(AA5>0,"CLOSE",IF($AA$1<=15,"CLOSE","")),"")

I think that will work.

[brumbie]

That, of course, is the scheduled off.

[Sussexguy44]

Hiya,

Sincere thanks for your reply, Im nearly there. I have a trigger in the trigger column for my back bet. = IF(AND(AG5=6,$E$2="Not In Play"),"BACKR","") This works OK - Is the BACKR trigger for getting a bet matched the quickest?

The close profit loss column is Y. My formula is

=IF($E$2="Not In Play",IF(Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),""). Where do I put this formula to ensure that the CLOSE trigger works and on the back bet that has been placed?

All the best

[brumbie]

Let's make the Y5>0.1 absolute. This means you can put this formula in any column:

=IF($E$2="Not In Play",IF(Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"") becomes

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")

As regards to BACKR, this is only used after a normal BACK has been unsuccessful in being matched. If you are putting a BACK bet in at a particular time, then you could just use the countdown clock to put a BACKR bet in say, 10 seconds later if the original is not matched.
But as you are waiting for AG5 to = 6 you will need a timestamp in a column to refer to when deciding when to put a BACKR bet in. You can do that by using the same principle as the Captain gave you when showing you how to record odds at 10 minutes before the off.

Personally, rather than use BACKR I would first try to use UPDATE , something like

=IF($E$2="Not In Play",IF($AG5=6,IF(T5="","BACK","UPDATE"),""),"")

If you prefer to use BACKR and need a timestamp in a column when a bet goes in then I will figure one out for you when time permits but have a look at the code the Captain gave you and see if you can adapt it.

[brumbie]

Probably the best way of resubmitting an unmatched bet after so many seconds would be to use a trigger modifier.

=IF($E$2="Not In Play",IF($AG5=6,IF(T5="","BACK-F10",IF(AND($T5="CANCELLED",$V5=0),"BACKR","")),""),"")

This would put another bet in if the first one was not matched before 10 seconds. However, you have to be very careful of these triggers and make sure they do not loop. BACKR is fine but trying to cancel/clear column T and then sending another BACK in could break your bank.
I haven't tested this trigger but it looks ok.
Incidentally, having put another column in (Close profit/loss), although formulas will automatically adjust to the extra column VBA generally wont. So you will have to check any VBA and adjust manually.

[brumbie]

Forget that, it doesn't even work anyway. It shows how long it's been since I was messing around with times. If you realy want to wait for a number of seconds before putting another bet in then this looks the easiest way.
Add this to AL5:

=IF(ISNUMBER($U5),$C$2-$U5,"")

and this to AM5:

=IF(ISNUMBER(AL5),IF(LEFT(AL5)<>"-",(HOUR(AL5)*3600)+(MINUTE(AL5)*60)+SECOND(AL5),-((HOUR(SUBSTITUTE(AL5,"-",""))*3600)+(MINUTE(SUBSTITUTE(AL5,"-",""))*60)+SECOND(SUBSTITUTE(AL5,"-","")))),"")

That will give you in AM5 the seconds passed since the bet went in. Then you just refer to column AM. (these formulas can be moved to any 2 columns next to each other)

So, something like:

=IF($E$2="Not In Play",IF($AG5=6,IF($T5="","BACK",IF($AM5>=10,"BACKR","")),""),"")

You just change columns AL/AM to whichever suits you.
Hope this works, I tested it and it seemed ok.

[Sussexguy44]

Hiya,

Thanks again. To confirm the column Y is the new Close Profit/Loss column. It will show zero unless a back bet is made. I am able to show, action and see back bets with the help you have given. The back bets will range from row 5 to row 20 on my spreadsheet. So if a profit/loss appears in column Y (rows 1-20) then a close bet (basically a lay bet) should be actioned. The formula below is in a spare column AR and in cells 5 to 20. All that happens is the word close appears in the cell however no close(lay) bet is made. I may have missed something.

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")

[Sussexguy44]

Meant rows 5-20

[brumbie]

It looks like you have

=IF($E$2="Not In Play",IF($AG5=6,IF($T5="","BACK",IF($AM5>=10,"BACKR","")),""),"")

in column Q which would mean you have to refer to the column that you have

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")

in, in that formula.

A better way of doing it is this:
place
=IF($E$2="Not In Play",IF($AG5=6,IF($T5="","BACK",IF($AM5>=10,"BACKR","")),""),"")
in a convenient column (not Q)
place
=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")
in the next column across then concatenate both these columns in the next column across again. Then refer to this concatenated columns in column Q. So, for arguments sake, you have used AE, AF, AG then AG would be your concatenated column. So in Q you would put Q5=AG5. Just having my morning coffee at 5.30 am so more to follow prob.

[brumbie]

Thats in Q place
=AG5
then run all formulas down to row 20
Whatever time you are anticipating your back bet to go in, be sure to leave plenty of time for the actions to take place.

[brumbie]

I see that you have

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")

in column AR so, assuming AS and AT is free you place

=IF($E$2="Not In Play",IF($AG5=6,IF($T5="","BACK",IF($AM5>=10,"BACKR","")),""),"")

in AS5 then

=CONCATENATE(AR5,AS5)

in AT5

Also change

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF($Y$1<=15,"CLOSE","")),"")

to

=IF($E$2="Not In Play",IF($Y5>0.1,"CLOSE",IF(AND($Y$1<=15,$T5<>""),"CLOSE","")),"")

This will prevent the formula from attempting to CLOSE when no bet has been placed.

Don't forget, if this is the same Workbook you have used the Captains VBA in, then because you have added a column (Close Profit/Loss) you may have to manually alter the VBA to refer to 1 column across.

[brumbie]

IN Q5
= $AT5
(I've been known to be difficult to follow )