Start a timer when Market goes INPLAY!

[Sneeto]

Hi Guys
I have just spent three days searching for the right code, without any luck
I am not an Expert at programming and I know this is really easy.
What I basically want the spreadsheet to do is:
When market goes INPLAY, Start the timer.
The timer should be in seconds (only showing seconds in a cell)
I am going to use this, because then I know for how long the market has gone In play, and I can place my bets. This is a strategy for football, match odds.
The timer should stop counting automatically after a period of time (seconds).
Can anybody help me with this?

[Bet Angel]

We are playing with putting the Bet Angel timer into Excel today. So you may want to wait till we have managed to do that.

[Sneeto]

That sounds great, thanks a lot!

[Zenyatta]

As far as I can see the basic Excel functions can't record values at given times (no variables), which is very annoying. The only solution as far as I can see is to resort to VBA programming.

The following simple Excel functions provide a timer giving you the time in seconds before/after the advertised off-time, the trouble is, the advertised time won't neccesserily match the real time, because events are often delayed and don't start until several minutes after advertised time start time.

Put this in cell C70: it extracts the off-time from cell B1:

=RIGHT(LEFT(B1,SEARCH(":",B1,1)+2),5)

Now, make sure cell C71 (the cell you want to use as a timer) is formatted to the 'Number' format, and put this in:

C71=(C70-C3)*86400

This simply subtracts the last refresh time (cell C3) from the off-time (cell C70) and gives you the result in seconds. The timer will show a positive number indicating seconds before the off-time, and a negative number indicating seconds after the off-time.

[Sneeto]

Hey Zenyatta
Thanks for the respond, but unfortunately there is no advertised off-time in the soccer odds market. It would probably work just fine in horse racing markets. That is why I wanted to create the timer when the market goes inplay.
As bet Angel said above, they are going to put the BA timer instead. But this leaves me with two problems.
1. The timer will probably look like this hh.mm.ss. How can I put an IF statement (among other statements), if I wanted to fire a bet between say the first minute and 40. Minute???
2. The second one is as you mentioned before, the events don’t always start on time. But here maybe the bet angel timer don’t start running until the event actually has gone in play??

[PeterLe]

Hi
Can you now create a conditional if when cell g1 states "in-play" ??
Regards
Peter

[Bet Angel]

We should point out that any timer we implement would only count up from that initial suspension or from when you join the market. There is no regular data that comes from the market that exposes the time the actual event went off. So it is not possible to implement that.

[Sneeto]

Hi Peter,
no! Not yet, but I think the only solution is to create a macro. In this topic: viewtopic.php?f=19&t=1377&p=7635&hilit=timer#p7635
Nigel has created a timer, you push a button and it counts in seconds. That would be perfect for counting time when market has gone in play. So the question is, - is it possible to change the scripting and instead of bushing the button, when “in play” appears in G1, the timer starts counting automatically!?
Any help with this, will be appreciated!

[nigelk]

If you right click the BA sheet and view the code, you'll see two instructions under the worksheet change event.

One to run the timer if G1 is "IN PLAY", and one stop it running if G1 is empty. Put "IN PLAY" in G1 and the timer will start running, clear it, and it'll stop.

Please be aware that it is case sensitive, and whatever appears in G1, must also be the same as whatever is in the code, so "In play" and "IN PLAY" will not work

[Sneeto]

thank you a lot! that is just what I needed