Home >> Support Board

Support Board

Date/Time: Wed, 27 Nov 2024 14:21:51 +0000

How to Post a Message for Support

[Programming Help] - Spreadsheet study - How to flatten all positions just on Fridays

View Count: 427

[2023-07-22 18:27:00] #1 [Go To First Post]
Rui S - Posts: 190
Hello. I have been starting my incursions with the Spreadsheet for Trading and I have already made some research but I can't figure out how to make the Spreadsheet to flatten all open positions at the end of the day just on Fridays. To flatten on a daily basis I'm using the following formula on J29, for West European Time: =FRACTIME(J41)>TIMEVALUE("20:59:00"). I would like to have a version of that formula that would flatten just on Fridays. I think I will need to use the "WEEKDAY" Function, but I can´t figure out how. Any help will be appreciated. Thanks.

[2023-07-22 19:06:19] #2 [Go To First Post]
Sawtooth - Posts: 4120
Try this: =AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("20:59:00"))

[2023-07-22 20:22:49] #3 [Go To First Post]
Rui S - Posts: 190
Hi Tom, That's it!! This seems so easy for you. You really are the master of SC spreadsheets. Thank you so much.

[2023-07-22 21:34:32] #4 [Go To First Post]
Rui S - Posts: 190
Hi again. I'm sorry to bother you again, there is one more step I can't figure how out to do it. I'm using this formula bellow and it's working well - it's the trading time frame (14:00:00 to 19:28:00) with a "recess" (15:20:00 to 17:00:00): =OR(OR(FRACTIME(J41<TIMEVALUE("14:00:00"),FRACTIME(J41)>TIMEVALUE("19:28:00")),AND(FRACTIME(J41)>TIMEVALUE("15:20:00"),FRACTIME(J41)<TIMEVALUE("17:00:00"))) Now I need to add a second "recess", e.g. from 17:30:00 to 18:00:00. After several attempts, it comes always with an error (#sintax). Here is one of those attempts: =OR(OR(FRACTIME(J41<TIMEVALUE("15:00:00"),FRACTIME(J41)>TIMEVALUE("19:28:00")),AND(FRACTIME(J41)>TIMEVALUE("15:20:00"),FRACTIME(J41)<TIMEVALUE("17:00:00"),AND(FRACTIME(J41)>TIMEVALUE("17:30:00"),FRACTIME(J41)<TIMEVALUE("18:00:00"))) Could you please take a look and correct it for me? Thank you. Date Time Of Last Edit: 2023-07-22 21:35:14

[2023-07-22 22:27:06] #5 [Go To First Post]
Sawtooth - Posts: 4120
Try this: =OR(OR(FRACTIME(J41)<TIMEVALUE("14:00:00"),FRACTIME(J41)>TIMEVALUE("19:28:00")),AND(FRACTIME(J41)>TIMEVALUE("15:20:00"),FRACTIME(J41)<TIMEVALUE("17:00:00")),AND(FRACTIME(J41)>TIMEVALUE("17:30:00"),FRACTIME(J41)<TIMEVALUE("18:00:00"))) You had the right idea, but you were missing a couple of closing parentheses. - after the first FRACTIME(J41) - after the TIMEVALUE("17:00:00"))

[2023-07-22 22:51:45] #6 [Go To First Post]
Rui S - Posts: 190
Tom, I was close, but not close enough. With so many parentheses I got lost. Maybe one day I get there :) Thank you so much.

[2023-07-27 17:10:27] #7 [Go To First Post]
Rui S - Posts: 190
Hi Tom, I am been making some progress with my incursion with the spreadsheet study, although it hasn't been easy. Meanwhile, I have discovered your website and downloaded your free Alert for lag latency study. Thank you very much for make that stuff available for free. It's a very good way for us, dummies, to learn how things are done and practice with the spreadsheets. I could already see it works as intended and so I have been trying to set an Alert Sound for when it signals a lag, but after several attempts (and hours) I have to confess I can't find a way to do it. If it is not to much to ask, would you be kind enough to tell me how to set that alert, please? Sorry for the inconvenience. Thank you. Date Time Of Last Edit: 2023-07-27 17:13:42


Lag Alert+.StdyCollct - Attached On 2023-07-27 17:11:01 UTC - Size: 18.5 KB - 307 views Lag Alert.scss - Attached On 2023-07-27 17:12:15 UTC - Size: 2.37 KB - 471 views Attachment Deleted.

[2023-07-27 17:49:10] #8 [Go To First Post]
Sawtooth - Posts: 4120
Sorry that the explanation on my site didn't explain how. I just now updated it with instructions. https://www.sawtoothtrade.com/free-stuff-2.html To set the alert, go to Analysis >> Studies >> highlight the study in the Studies to Graph list >> Settings button >> Settings and Inputs tab >> Column O Alert... then choose an Alert#. Note: The alert is not set on the Alerts tab. Date Time Of Last Edit: 2023-07-27 17:54:03

[2023-07-27 18:10:21] #9 [Go To First Post]
Rui S - Posts: 190
Hi Tom, Thank you so much for the explanation. I can't believe it was already there right in front of me and I didn't see it! I thought it was supposed for the users to create the alert sound by themselves so I have tried everything I could remember except the obvious! Well, next time I will try and look for the easiest way first. Thanks again. Date Time Of Last Edit: 2023-07-31 14:24:56

[2023-07-31 14:51:03] #10 [Go To First Post]
Rui S - Posts: 190
Hi Tom, I am really sorry to bother you again. From your post #5: Try this: =OR(OR(FRACTIME(J41<TIMEVALUE("14:00:00"),FRACTIME(J41)>TIMEVALUE("19:28:00")),AND(FRACTIME(J41)>TIMEVALUE("15:20:00"),FRACTIME(J41)<TIMEVALUE("17:00:00")),AND(FRACTIME(J41)>TIMEVALUE("17:30:00"),FRACTIME(J41)<TIMEVALUE("18:00:00"))) and from you post #2: Try this: =AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("20:59:00")) Those formulas are both working very well. However, during my experiments and backtesting, I had to "upgrade" my Auto Trading time windows formula to the following one, with 4 "recesses": =OR(OR(FRACTIME(J41<TIMEVALUE("06:00:00"),FRACTIME(J41)>TIMEVALUE("23:30:00")),AND(FRACTIME(J41)>TIMEVALUE("08:30:00"),FRACTIME(J41<TIMEVALUE("14:00:00")),AND(FRACTIME(J41)>TIMEVALUE("15:30:00"),FRACTIME(J41<TIMEVALUE("16:00:00")),AND(FRACTIME(J41)>TIMEVALUE("17:00:00"),FRACTIME(J41)<TIMEVALUE("19:00:00")),AND(FRACTIME(J41)>TIMEVALUE("20:30:00"),FRACTIME(J41)<TIMEVALUE("21:10:00"))) I don't know if this formula is correctly done, but it seems to be working. The problem is with the formula that was closing the positions on Fridays - (=AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("23:20:00")) - it stopped working with this my "upgraded" formula. It never closes the open positions, leaving them open during the weekends. After unsuccessfully spending the whole weekend trying to figure out a solution for it, here I am once more asking for help. Would you please take a look and see what I have done wrong to cause this problem? Thank you so much. P.S. - I totally understand if you don't have the time to address this issue.

[2023-07-31 14:53:26] #11 [Go To First Post]
Rui S - Posts: 190
Sorry, I don't know why my post above is so wide! Date Time Of Last Edit: 2023-07-31 14:54:10

[2023-07-31 18:34:22] #12 [Go To First Post]
Sawtooth - Posts: 4120
Get rid of the leading left parenthesis: =AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("23:20:00")) Your J28 formula is missing two right parentheses, closing two FRACTIME(J41)s: =OR(OR(FRACTIME(J41<TIMEVALUE("06:00:00"),FRACTIME(J41)>TIMEVALUE("23:30:00")),AND(FRACTIME(J41)>TIMEVALUE("08:30:00"),FRACTIME(J41)<TIMEVALUE("14:00:00")),AND(FRACTIME(J41)>TIMEVALUE("15:30:00"),FRACTIME(J41)<TIMEVALUE("16:00:00")),AND(FRACTIME(J41)>TIMEVALUE("17:00:00"),FRACTIME(J41)<TIMEVALUE("19:00:00")),AND(FRACTIME(J41)>TIMEVALUE("20:30:00"),FRACTIME(J41)<TIMEVALUE("21:10:00")))

[2023-07-31 20:17:18] #13 [Go To First Post]
Rui S - Posts: 190
Hi again, Sorry, I don't really understand what you mean when you say "Get rid of the leading left parenthesis" here: Get rid of the leading left parenthesis: =AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("23:20:00")) This is the formula nicely provided by you and it works very well. Regarding the bigger formula, the lack of that parenthesis you mentioned was somehow a typo of my part when I wrote my post. The formula is working and not returning any error. Here it is as I'm using it: =OR(OR(FRACTIME(J41)<TIMEVALUE("06:00:00"),FRACTIME(J41)>TIMEVALUE("23:30:00")),AND(FRACTIME(J41)>TIMEVALUE("08:30:00"),FRACTIME(J41)<TIMEVALUE("14:00:00")),AND(FRACTIME(J41)>TIMEVALUE("15:30:00"),FRACTIME(J41)<TIMEVALUE("16:00:00")),AND(FRACTIME(J41)>TIMEVALUE("17:00:00"),FRACTIME(J41)<TIMEVALUE("19:00:00")),AND(FRACTIME(J41)>TIMEVALUE("20:30:00"),FRACTIME(J41)<TIMEVALUE("21:10:00"))) I realize now though that I wasn't clear with my question, so I'll try again but shorter: When I put that big formula you see above in J28, the existent formula in J29 =AND(WEEKDAY(J41)=6,FRACTIME(J41)>TIMEVALUE("23:20:00"))) stops working, meaning that the open positions don't get closed anymore on Fridays. I hope I have explained better this time. Thank you. Date Time Of Last Edit: 2023-07-31 20:18:37

[2023-07-31 22:19:56] #14 [Go To First Post]
Sawtooth - Posts: 4120
The latest time in the J28 formula should be earlier than the time in the J29 formula. IWO, if you flatten at 23:20, you could still get an entry until 20:30, which would immediately flatten. However, this is not the reason for this issue. For your J29 to go TRUE, there must be a chart bar that includes the FRACTIME(J41) of 23:20:00 Most Futures contracts have no bars after 17:00:00 ET on Fridays. Note: I always add a reference to J8=0 in my J28 formula. Add AND(J8=0, in front of your J28 formula, and close it with a right parenthesis at the end of the J28 formula. https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#DisableTradingOutsideOfRegularTradingHoursAnd2HoursMidday This allows an exit after the latest time, but not an entry. This is necessary because J29 is ignored if J28 is TRUE. Spreadsheet Systems, Alerts and Automated Trading: Disable Auto Trading [J28] So if you have a position after the latest time in J28, with the exit allowed, J28 is still FALSE after its latest time, allowing J29 to function. Example: If you have a position when the latest time in J28 passes, it will play out as late as the time in J29, but no new entries will occur because J8<>0. If the trade exits before the time in J29, J28 will be TRUE and no new entries, or exits, will occur because J41 is later than J28, and J8=0. And since you are now flat, and later than J28, J29 is moot because it is now ignored. This is also why I don't use J85-J89. Date Time Of Last Edit: 2023-08-01 20:33:39

[2023-08-01 00:27:54] #15 [Go To First Post]
Rui S - Posts: 190
Hi Tom, Thank you for your reply. Your explanation is simply great. I very much prefer a good explanation (when possible) than just the work "done". This way I can understand and learn! For your J29 to go TRUE, there must be a chart bar that includes the FRACTIME(J41) of 23:20:00 Most Futures contracts have no bars after 17:00:00 ET on Fridays. This is the problem for sure!! The Market (CME-NQ) closes at 22:00 Western Europe Time and here I am going around and around and then taking your time with such a silly thing!! My sincere apologies for that. Once again the obvious answer was right in front of my eyes but I kept looking to the formulas... I'm having a busy day out tomorrow for health tests, but as soon as I get back home I will rearrange that time of close issue and will also add the AND(J8=0 to the formula. Thank you so much for taking the time to check my issue and for the thorough explanation. Date Time Of Last Edit: 2023-08-01 00:31:22

To post a message in this thread, you need to log in with your Sierra Chart account: