Support Board
Date/Time: Wed, 12 Feb 2025 10:55:44 +0000
[Programming Help] - How to Reference Time in Spreadsheet for Trading
View Count: 2175
| [2020-10-17 19:09:36] |
| User440656 - Posts: 14 |
|
I would like to back test a system that buys the ES mini five minutes before the close of regular trading hours and then sells in the after hours trading session. Specifically, the system buys at 12:55 Pacific time and exits the next time the market is open at 20:00 Pacific time. For the buy entry in my spreadsheet I have if(A4==12:55:00,1,0) and for the buy exit I have if(A4==20:00:00,1,0). However, column A includes a date and time, so I do not think that is correct. What should I use for the buy entry and buy exit? I have attached my spreadsheet. Thank you. |
Private File |
| [2020-10-17 20:58:43] |
| Sawtooth - Posts: 4173 |
|
Try this in K3/M3: =FRACTIME(MROUND(A3,1/86400))=TIMEVALUE("12:55:00") This assumes your chart is set to Pacific time. 'Signal Only On Bar Close (K,M)' will need to be set to No. Sierra's bar timestamp is at the Open. FRACTIME removes the Date from the DateTime. MROUND is used to round the Time to the second, to remove any floating point errors. (There are 86400 seconds in 24 hours.) TIMEVALUE converts text of the time to the Time in SerialDateTime format. Spreadsheet Functions: Available Functions |
| [2020-10-18 00:37:08] |
| User440656 - Posts: 14 |
|
Thank you. That was a fast reply. I am still having difficulty. I implemented your suggestions, but the system is not taking any trades when I back test it (the J column keeps showing zeros). Do you see any mistakes in my attached spreadsheet?
Date Time Of Last Edit: 2020-10-18 03:02:28
|
 buyclose.scss - Attached On 2020-10-18 00:34:37 UTC - Size: 40.94 KB - 292 views |
| [2020-10-18 02:40:47] |
| Sawtooth - Posts: 4173 |
|
The file you've attached is Private.
|
| [2020-10-18 03:01:47] |
| User440656 - Posts: 14 |
|
Sorry. Here is the file again, this time set to public.
|
| buyclose.scss - Attached On 2020-10-18 03:01:16 UTC - Size: 38.19 KB - 477 views |
| [2020-10-18 12:37:23] |
| Sawtooth - Posts: 4173 |
|
Do not use the IF function. Use the exact formula in post#2 (You can edit the time as needed). The formula will return a boolean 1 or 0. 1 is a TRUE, 0 is a FALSE. The IF function is only needed when you need to return a value. You always need to precede a spreadsheet formula with an = sign. Date Time Of Last Edit: 2020-10-18 13:31:23
|
| [2020-10-19 05:23:28] |
| User440656 - Posts: 14 |
|
Thank you. It will issue a buy signal now during back testing, but it will not close the position. I took a screenshot http://www.sierrachart.com/image.php?Image=1603084738229.png. The buy is marked with a green up arrow. The exit is marked with a red up arrow, but the position did not close. Any idea why buys will not close at 20:00:00? |
| buyclose.cht - Attached On 2020-10-19 05:14:22 UTC - Size: 3.59 KB - 459 views Attachment Deleted. buyclose.scss - Attached On 2020-10-19 05:14:39 UTC - Size: 28.46 KB - 418 views |
| [2020-10-19 12:55:50] |
| Sawtooth - Posts: 4173 |
|
Two things: - When using Attached Orders also with formulas in L3/N3, you must set 'Cancel All Working Orders On Exit' to Yes. - The Sell Entry formula in M3 is the same as the Buy Exit formula in L3. Either delete the M3 formula, or edit it to a time later than the Buy Exit and before the Buy Entry. Are you attempting to do a Reversal at 20:00:00 ? If so, there are other settings to change. |
| [2020-10-19 23:10:09] |
| User440656 - Posts: 14 |
|
I was not attempting to reverse. I entered data for the sell entry because I was trying to exit the buy. It works now with data entered only for the buy entry and buy exit in the spreadsheet. Thanks so much for your patience.
|
To post a message in this thread, you need to log in with your Sierra Chart account: