Support Board
Date/Time: Fri, 07 Nov 2025 18:12:28 +0000
Handling Stop and Limit orders with the Spreadsheet for trading
View Count: 83
| [2025-11-05 13:18:22] |
| Fabio - Posts: 130 |
|
Good morning SC support! I am running in some technical issue, and I am banging my head against a wall... I am trying to automate a strategy with the use of the Spreadsheet System for Trading. I read all the documentations and all, yet I cannot find an answer to my challenge; I want the spreadsheet to fire off not only the entry with a predetermined size, but also a stop loss and target price at a specific price level (i.e. 6912.25 for target and 6864.25 for stop considering a long at 6876.50) Now, I am using the "Use column I and J values Version 2" set to yes, and there is no documentation for that but just for the version one, so I have to reverse engineer all of those cells. Why have I decided to use the version 2 in the first place is a mystery considering that all documentations are for version 1 but this is an argument for another topic (please update the documentation also for version 2 lol!). Now, I figured out how to place the entry limit order as well as the stop order using column L - Buy Exit (Sell). I was able to do this by setting Cell J74 (old J72) to "S" for Stop order, the price I wanted the stop to be in cell J79 (old J22) and with the use of the "AND" function column L for the formula to handle the position quantity in Cell J31 (old J8). This seems to work just fine. However, I couldn't figure out a way to handle the target for the exit with the use of a limit order (sell limit at xyz above the entry price or buy limit "xyz" below current price). I asked the chat gpts and all but I just couldn't figure that part out. Can you please help me figure out how I can handle both stop and target with the use of stop and limit orders? At least pointing me out to the documentations in a way I can handle the workflow because I got lost in it. Thanks and kind regards, Fabio. |
| [2025-11-05 15:34:40] |
| John - SC Support - Posts: 42851 |
|
For a Long position (so you initially have the "Buy Entry" be True for entry) you would use the "Buy Exit" to close out the position. You either use the "Buy Exit" or you use an OCO set of Stop/Target - you can not use both, as this would create a problem should a Target close a position and then the Buy Entry go true. So you are not using the Buy Exit properly. If you want to have a Stop/Target OCO, then the easiest way to do this is to leave the Buy Exit alone and not setup any of the cells as you have stated above. Just set the Attached Orders on the Trade Window for the chart that has the Spreadsheet Study on it the way you want them. As long as the "Trade Window" has the option for "Use Attached Orders" enabled, then when a "Buy Entry" or "Sell Entry" goes True in the Spreadsheet System for Trading, those Attached Orders will be used. If you want more specific control over the Target/Stop, then enter a formula in cells J84 and J85 that gives you the offset you want for the Target and Stop. If these cells have valid numbers in them, then this will override the Attached Orders settings in the Trade Window. Refer to the following: Spreadsheet Systems, Alerts and Automated Trading: Attached Order Target Offset (write only) [v1:J80, v2:J84] -- We are not certain of the issue in terms of the documentation for the Spreadsheet cells. The documentation lists the specific cells for both versions 1 and 2 for Columns I and J. Admittedly, it is a bit messy, but this is due to having both versions available. And if you are curious, the reason for the two versions is that Version 1 came about as things got added to the Spreadsheet System for Trading. So items were not in a good order. They were reordered and hence Version 2 came out. But we could not force people to redo their spreadsheets, therefore we kept both implementations. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2025-11-06 09:23:01] |
| Fabio - Posts: 130 |
|
Hey John, Thanks for coming back to me. I found a part of the documentation that goes against what you said at the beginning: You either use the "Buy Exit" or you use an OCO set of Stop/Target - you can not use both, as this would create a problem should a Target close a position and then the Buy Entry go true. So you are not using the Buy Exit properly.
You can find it here: Automated Trading Management: SupportAttachedOrdersForTrading But I also copy and paste the part I am referring to here for easy reference for you to check: It is possible to use both Attached Orders and BuyExit or SellExit to exit the Position. This is explained in this section.
We are not certain of the issue in terms of the documentation for the Spreadsheet cells. The documentation lists the specific cells for both versions 1 and 2 for Columns I and J.
I found part of the documentation that this was not case (although most of them have both V1 and V2). I got lost in the rabbit hole of the links so I don't know where to find the missing ones anymore, but if I find it again I will let you know.I think I solved the problem with the use of cells J84/ 85 and it looks working just fine so I think I will simplify a little using those. With that said, it would be curious to know how to handle the exits using column L (for the long) and N for the shorts. For those columns to handle both stop and target, it means that cell J74/76 must be left to "M" (market order), right? Only by doing so a market order will be fired off when either/or conditions are met, is that correct? Doing so, won't allow me to visually see the stop and target on the chart, nor to have them in case of a sudden interruption of the platform (i.e. sudden shut down of the platform, the power goes down etc). Look forward to hearing back from you. Thanks, F. |
| [2025-11-06 14:13:21] |
| Sawtooth - Posts: 4287 |
|
IMO it is significantly easier to use Attached Orders for targets and stops than to use various spreadsheet cells to create them. If your targets and stops are dynamic, use the Trade Management by Study study to move them, based on formulas in spreadsheet Formula Columns. Attached Orders: Study Controlled Targets and Stops You can use J80/J81 (v1) for your initial Attached Orders' offsets (in points not ticks). You can use Attached Orders together with L3/N3, if 'Cancel All Working Orders on Exit (ln:24) is set to Yes. Yes, this is messy: Spreadsheet Systems, Alerts and Automated Trading: Special Spreadsheet Outputted Values | Read-Only or Read/Write Since most users are on v1 since it's the default and the legacy version, it would be easier to use if it weren't in v2 order. Suggestion: create a separate link/page for all v2 references. Date Time Of Last Edit: 2025-11-06 14:41:32
|
| [2025-11-06 17:02:00] |
| John - SC Support - Posts: 42851 |
|
I found a part of the documentation that goes against what you said at the beginning:
Yes, this is correct, but we did not want to get into that complexity. With that said, it would be curious to know how to handle the exits using column L (for the long) and N for the shorts.
For those columns to handle both stop and target, it means that cell J74/76 must be left to "M" (market order), right? Only by doing so a market order will be fired off when either/or conditions are met, is that correct? Doing so, won't allow me to visually see the stop and target on the chart, nor to have them in case of a sudden interruption of the platform (i.e. sudden shut down of the platform, the power goes down etc). You do not have to have the Parent order as a Market Order. Any order type will have the appropriate Attached Orders with it. But, depending on your trading service, a non-market order with attached orders may only be local and not held by the trading service. So if you are worried about having a position that is only on your local system, then you need to check with how your trading service handles this situation. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2025-11-07 13:53:42] |
| Fabio - Posts: 130 |
|
Hi @Sawtooth. Thanks for the heads up. I honestly had no idea about that study in the first place. I will definitely look into it. I have another question now :) I have created the system to be semi-automated by design, so I have formulas in cell K3 and M3 that relate to cell O3 (for the buy trigger) and cell P3 (for the sell trigger). What I mean by that is the following. Cell K: IF O3= "xyz" then K3 become TRUE (1) and a limit order is placed based upon conditions. Same thing for cell M3 with cell P3 (P3= "xyz" then M3 become TRUE). So far, everything works as intended. If I remove the conditions from cell O3 or P3, Cell K3 or M3 return to the default (FALSE, or 0) value. And again, all good here. However, the order placed (entry limit order) when the conditions were TRUE (or 1) won't be removed from the chart despite now the cell K3/ M3 are 0. Is this a normal behaviour or am I missing something? Because in my mind the way that should work is as follow: K3/M3 = 1, then Limit order placed. K3/M3 = 0, then the limit order placed (and not filled) is pulled off. What am I missing here? Because I noticed that Cell J39 (on V2 that is "Working orders Exist (read-only)) remains 1 despite cancelling the conditions and Cell J40 ("working orders quantity (read-only) maintain the quantity as well. Then as soon as I cancel the order manually on the chart, those cells return to 0. Shouldn't this behaviour happen when I cancel the conditions? Also, another question that popped in my mind is the following; Is there a formula or something that will allow Cell O3 or cell P3 to reset to "" (empty) once the limit order gets filled? Thanks, Fabio Date Time Of Last Edit: 2025-11-07 14:24:40
|
| [2025-11-07 15:15:22] |
| Sawtooth - Posts: 4287 |
|
the order placed (entry limit order) when the conditions were TRUE (or 1) won't be removed from the chart despite now the cell K3/ M3 are 0.
This is normal behavior.You will need to have a formula that cancels an unfilled BuyEntry/SellEntry order. Here are two v1 examples: Spreadsheet Example Formulas and Usage: Cancel Unfilled Working Order n Minutes After Entry Spreadsheet Example Formulas and Usage: Cancel an Unfilled Order After n Bars Review for Limit entries (v1): K3/M3 is the trigger J22/J24 is the price J71/J73 is the order type BTW, I'm curious, why are you using V2? Reasons not to: - V2 Documentation is messy, confusing, and incomplete. - V1 users know the location of column J cells, so help now must transliterate. - V1 has existed for decades without issue, and this is where the knowledge base is. - V2 is essentially unnecessary. Date Time Of Last Edit: 2025-11-07 15:18:27
|
To post a message in this thread, you need to log in with your Sierra Chart account: