Hi,
I’m a newbie in Excel automation. I’d like to create the following rule in excel:
If 30 seconds before event start, the price is within a specific range, the system should trigger a back order (one tick below current price), if not cancel the order.
Could someone help me with that?
Thanks a lot for your help.
Excel automation - Help
Firstly, you need to figure out how to recognise when 30 seconds before the start has arrived. You could use the counter in cell F4 for this.
Thanks to somewhere else on this forum you can work out the time condition based on the Excel time format (d/h/s) or 1/24/3600 - which is the value Excel uses for 1 second.
So 30 seconds is equal to (1/24/3600) x 30
This means you can compare this to the value in cell F4.
Next your formula needs to look at the price window you wish to use. Let's say you want to trigger if the price falls between 3.0 and 4.0. I would use the AND function:
=AND(G9>3.0,G9<4.0)
Where G9 contains the current BACK price of the top runner in the spreadsheet.
Your formula in cell L9 would be:
=IF(AND(G9>3,G9<4,F4<((1/24/3600)*30)),"BACK","")
The price asked for could be read from cell F9 although this may not necessarily be one tick lower in gappy markets.
So in cell M9 it is simply
=F9
And in the stake box enter whatever stake you wish to use.
See attached sheet in cells L9 & M9. Change the value of >3 & <4 to whatever price window you wish to trigger at.
Thanks to somewhere else on this forum you can work out the time condition based on the Excel time format (d/h/s) or 1/24/3600 - which is the value Excel uses for 1 second.
So 30 seconds is equal to (1/24/3600) x 30
This means you can compare this to the value in cell F4.
Next your formula needs to look at the price window you wish to use. Let's say you want to trigger if the price falls between 3.0 and 4.0. I would use the AND function:
=AND(G9>3.0,G9<4.0)
Where G9 contains the current BACK price of the top runner in the spreadsheet.
Your formula in cell L9 would be:
=IF(AND(G9>3,G9<4,F4<((1/24/3600)*30)),"BACK","")
The price asked for could be read from cell F9 although this may not necessarily be one tick lower in gappy markets.
So in cell M9 it is simply
=F9
And in the stake box enter whatever stake you wish to use.
See attached sheet in cells L9 & M9. Change the value of >3 & <4 to whatever price window you wish to trigger at.
You do not have the required permissions to view the files attached to this post.
Hi
A word of advice on time values on excel
Excel willl always use absolute values and as such a horse was never matched at 5.2 but probably 5.1999562 etc and the same is true of time values so
1 . Do not specify a specific time value your processor will miss it
2 . Use a cell to = F4 formatted as a decimal number say to 6 places
3 . Then code that if that cell is less than say <0.000684 and - and - etc - etc
PS 0.000684 is 60 seconds but I just happen to know that of the top of my head
Regards
G
A word of advice on time values on excel
Excel willl always use absolute values and as such a horse was never matched at 5.2 but probably 5.1999562 etc and the same is true of time values so
1 . Do not specify a specific time value your processor will miss it
2 . Use a cell to = F4 formatted as a decimal number say to 6 places
3 . Then code that if that cell is less than say <0.000684 and - and - etc - etc
PS 0.000684 is 60 seconds but I just happen to know that of the top of my head
Regards
G