Excel blowing my mind

A place to discuss anything.
Post Reply
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

Hi. I'm trying to automate a strategy obviously.

I want to back/lay a horse or dog, say 3 seconds before the off and I want my stake to be placed at an amount relative to the best market price.

Eg.

If the back price is 2.75, I want to stake £2.75
If the back price is 3.75, I want to stake £3.75
If the back price is 4.80, I want to stake £4.80
If the back price is 5.00, I want to stake £5.00

etc

OR

If I want to calculate the stake relative to the price say 10%
If the back price is 2.75, I want to stake £0.28
If the back price is 5.00, I want to stake £0.50
If the back price is 3.30, I want to stake £0.33

I can't find the function that allows me to do this in guardian so I'm opting for excel. I've figured that I can enter the formula into stake column(to calculate my stake relative to price as per aove example) in the basic spreadsheet template but how do i get it to trigger at say 3 seconds out from the off, AND AND feckin AND, ONLY TRIGGER ONCE?

Also when preparing guardian is there a quick way to assign my spreadsheet to all markets as if was 'applying rules to all markets'?
sionascaig
Posts: 1055
Joined: Fri Nov 20, 2015 9:38 am

you can do it without excel

just set a stored value to back price

and set you stake to the stored value or a % of it..
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

In Excel, it will only fire once per runner because "PLACED" appears in the L column, you just need a macro to delete this cell when each new market loads.

Put your stake calculation inside of an if statement that checks if the time is <= 3 seconds.

For example, in Cell O9:

If(F4<=0.00003472,[Your Calculation],"")

F4 is the countdown cell, if you type 00:00:03 in there and then change the cell from a TIME format to NUMBER, you'll see that's where I got that number from. Adjust accordingly.
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

well now i know both. kind of

thanks so much
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

CallumPerry wrote:
Tue Apr 06, 2021 10:12 am
In Excel, it will only fire once per runner because "PLACED" appears in the L column, you just need a macro to delete this cell when each new market loads.

Put your stake calculation inside of an if statement that checks if the time is <= 3 seconds.

For example, in Cell O9:

If(F4<=0.00003472,[Your Calculation],"")

F4 is the countdown cell, if you type 00:00:03 in there and then change the cell from a TIME format to NUMBER, you'll see that's where I got that number from. Adjust accordingly.
I was so close with my formula
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

JAMESGLYNN67 wrote:
Tue Apr 06, 2021 10:24 am
CallumPerry wrote:
Tue Apr 06, 2021 10:12 am
In Excel, it will only fire once per runner because "PLACED" appears in the L column, you just need a macro to delete this cell when each new market loads.

Put your stake calculation inside of an if statement that checks if the time is <= 3 seconds.

For example, in Cell O9:

If(F4<=0.00003472,[Your Calculation],"")

F4 is the countdown cell, if you type 00:00:03 in there and then change the cell from a TIME format to NUMBER, you'll see that's where I got that number from. Adjust accordingly.
I was so close with my formula
EH, nope, still not getting it.

Cell 09: =If(F4<=0.00003472,[N9=G9/10],"") I know this is not correct. Excel tells me so
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Remove the [ & ]
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

ODPaul82 wrote:
Tue Apr 06, 2021 11:34 am
Remove the [ & ]
So just: Cell 09: =If(F4<=0.00003472,,"")

Thanks again guys
gianni
Posts: 23
Joined: Fri Mar 09, 2018 3:40 pm

I would try:
Cell 09: =If(F4<=0.00003472,N9=G9/10,"")

=if(condition is true, then execute, else execute)
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

gianni wrote:
Tue Apr 06, 2021 12:31 pm
I would try:
Cell 09: =If(F4<=0.00003472,N9=G9/10,"")

=if(condition is true, then execute, else execute)
Thanks Gianni
I'm getting an ERROR message in the global status when i input Cell 09 as per above. I copied and pasted it

I put 'BACK' into L9

what i'm trying to set up is a back bet , 3 seconds out , stakes calculated at the time relative to the odds.

thanks again
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

.....the ERROR message in the GLOBAL STATUS cell is because its out with the timeframe and no action to be taken. Maybe?
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

JAMESGLYNN67 wrote:
Tue Apr 06, 2021 10:53 am
JAMESGLYNN67 wrote:
Tue Apr 06, 2021 10:24 am
CallumPerry wrote:
Tue Apr 06, 2021 10:12 am
In Excel, it will only fire once per runner because "PLACED" appears in the L column, you just need a macro to delete this cell when each new market loads.

Put your stake calculation inside of an if statement that checks if the time is <= 3 seconds.

For example, in Cell O9:

If(F4<=0.00003472,[Your Calculation],"")

F4 is the countdown cell, if you type 00:00:03 in there and then change the cell from a TIME format to NUMBER, you'll see that's where I got that number from. Adjust accordingly.
I was so close with my formula
EH, nope, still not getting it.

Cell 09: =If(F4<=0.00003472,[N9=G9/10],"") I know this is not correct. Excel tells me so
My bad, somebody pointed it earlier too I saw, just remove the []. Sorry for confusing you. Make sure L9 = BACK and M9 = one of the odds. K9 is the last traded price so M9 = K9 for example and then your stake = If(F4<=0.00003472,N9=G9/10,"").

I just had to double check my cell references with the template workbook, the stake formula should go into cell N9, not O9. O9 is where the status message appears from Bet Angel once a bet has been confirmed.

Global commands runs separate so if you're getting an error there, you have a problem elsewhere. As long as cell L9 = Back, M9 = odds, N9 = formula, you should be fine.
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

So i think Ive progressed here.

If i use the multi-sheet template will I have to copy and paste the above formula to all the sheets or will they populate from the first one?

Can I delete all the extra sheets and then duplicate the first one as many times as i want?

AND, if I use the switesh multi sheet template will all the data collection magic still work as set up?
JAMESGLYNN67
Posts: 25
Joined: Wed Aug 22, 2018 6:40 pm

JAMESGLYNN67 wrote:
Wed Apr 07, 2021 5:42 pm
So i think Ive progressed here.

If i use the multi-sheet template will I have to copy and paste the above formula to all the sheets or will they populate from the first one?

Can I delete all the extra sheets and then duplicate the first one as many times as i want?

AND, if I use the switesh multi sheet template will all the data collection magic still work as set up?
I dont really have any idea what the magic is but betting itll look cool on my laptop
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

JAMESGLYNN67 wrote:
Wed Apr 07, 2021 5:42 pm
So i think Ive progressed here.

If i use the multi-sheet template will I have to copy and paste the above formula to all the sheets or will they populate from the first one?

Can I delete all the extra sheets and then duplicate the first one as many times as i want?

AND, if I use the switesh multi sheet template will all the data collection magic still work as set up?
:lol: Not looked at the swiftest multi sheet for ages so couldn't tell you but yes, create a master sheet with all of your formulae in first and then once you copy the sheet, it should copy all of the contents also for you to use on multiple markets.
Post Reply

Return to “General discussion”