Support Board
Date/Time: Tue, 04 Mar 2025 18:07:37 +0000
[User Discussion] - Spreadsheet for trading, persistent data is reset when a cell is modified.
View Count: 899
| [2021-12-16 10:07:33] |
| User346098 - Posts: 47 |
|
Hi, Using Spreadsheet System for Trading study Buy Exit orders are not being sent to Interactive brokers TWS I did some debugging: but first I have these: Global Settings -> Spreadsheet Settings -> Recalculate the Chart When a Formula Column is Changed : Yes Signal Only Once Per Bar : No Signal Only On Bar Close COLUMNS L,N-Z: No Signal Only on Bar Close : COLUMNS K,M: No Strict Signal Only On Bar Close Evaluation: No I noticed the order from Buy Exit trigger is sent to IB TWS only when there is continuous PRINTING of this every few mins/secs in Trading Service.log: Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:40:45.248 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is FALSE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:40:45.248 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:40:45.248 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:40:45.248 HERE I AM TINKERING WITH SETTING BUY EXIT TO 0/1/ back and forth on the spreadsheet cell L3 FRSX[M] 0.50 Range #20 | source1 | Not evaluating trade signals because is full recalculation. | 2021-12-16 04:41:47.886 FRSX[M] 0.50 Range #20 | source1 | Not evaluating trade signals because is full recalculation. | 2021-12-16 04:44:31.716 FRSX[M] 0.50 Range #20 | source1 | Not evaluating trade signals because is full recalculation. | 2021-12-16 04:44:41.683 I wait printing is FREEZED, order not sent I go to the chart menu: Chart -> Recalculate FRSX[M] 0.50 Range #20 | source1 | Not evaluating trade signals because is full recalculation. | 2021-12-16 04:46:31.661 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:31.948 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.021 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.021 Trading message from IB: Warning: The 'EtradeOnly' order attribute is not supported. Error Code: 2168. ServiceOrderID: 183. InternalOrderID: 2342. | 2021-12-16 04:46:32.026 Trading message from IB: Warning: The 'FirmQuoteOnly' order attribute is not supported. Error Code: 2169. ServiceOrderID: 183. InternalOrderID: 2342. | 2021-12-16 04:46:32.027 TradeActivity files query time: 0.152577 seconds. Entries: 18. Current queries: 1 | 2021-12-16 04:46:32.094 FRSX[M] 0.50 Range #20 | source1 | Not evaluating trade signals because is full recalculation. | 2021-12-16 04:46:32.103 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.447 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.447 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.447 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:32.447 Interactive Brokers | Requesting Open orders. | 2021-12-16 04:46:47.016 Interactive Brokers | Sent reqAutoOpenOrders request | 2021-12-16 04:46:47.016 Interactive Brokers | Received Open orders end message. | 2021-12-16 04:46:47.017 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:47.024 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:47.024 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:47.024 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:46:47.024 Order is sent to IB TWS printing resumes: Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.048 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.048 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.048 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.048 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.558 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.558 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.558 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:20.558 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:21.050 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:21.050 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:21.050 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:21.050 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:26.073 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:26.073 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:26.073 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:26.073 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.091 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.091 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.091 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.091 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.589 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.589 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.589 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:32.589 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:39.609 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:39.609 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:39.609 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:39.609 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:40.111 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:40.111 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:40.111 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:40.111 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: K3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:48.112 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Buy Exit (Sell) | Signal ignored because current Alert Condition formula is TRUE | Cell: L3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:48.112 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Entry | Signal ignored because current Alert Condition formula is FALSE | Cell: M3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:48.112 Spreadsheet System - FRSX[M] 0.50 Range #20 | Spreadsheet: source1 | Sell Exit (Buy) | Signal ignored because current Alert Condition formula is FALSE | Cell: N3 | Bar start date-time: 2021-12-15 16:00:00 | 2021-12-16 04:49:48.112 Date Time Of Last Edit: 2021-12-16 10:09:32
|
| [2021-12-16 16:57:49] |
| John - SC Support - Posts: 38368 |
|
Refer to the following: Spreadsheet Systems, Alerts and Automated Trading: Ignored Signals With Spreadsheet Systems or Alerts For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-12-16 17:45:46] |
| User346098 - Posts: 47 |
|
I read it, it shouldn't be Ignored according to that documentation. OK so I read it again : " Ignored Signals During Historical Data Downloading [Link] - [Top] With the Spreadsheet System for Trading study and the Spreadsheet System/Alert study, System and Alert signals from the Sheet formula columns are ignored on historical data and when historical data is being downloaded. They are monitored for when the chart is actually updating with new data. A chart will be updating with new data during real-time chart updating with live data, during replays, or during a Back Test. If you want to see the Back Test results from historical Buy and Sell signals from the Buy/Sell formula columns in the case of when using the Spreadsheet System for Trading study, then refer to the Back Testing section." So signals are not monitored if the market was closed and no new data was coming in? 2) different question: is it possible to have different formulas at each row in a column copied from a different column ? I found Spreadsheet Studies Special Tasks: Using Different Formulas or Data at Each Row of the Formula Columns (K-Z) but this only copies the result of the formulas entered from the source column I need this since I am trying to set different formulas on a single sheet in a single column but different rows and have the formulas copied from other sheets in the spreadsheet, I need them copied because they refer to data local to the sheet they are copied to, I tried workaround like this: AX3 on formulasheet contains a sheet number: =INDIRECT(CONCATENATE("Sheet",formulasheet!AX3, "!","$J$8"))*INDIRECT(CONCATENATE("Sheet",formulasheet!AX3, "!","$J$9")) thinks start getting complicated just to get a single cell from the formulasheet when I use: =INDIRECT(CONCATENATE("formulasheet!BL", ROW(VLOOKUP(J$91, formulasheet!AX:AX,1))))) 3) Last question Is it also possible to have a value/state from a formula saved and not reset when I change any other cell on the sheet or source sheet, per example using Spreadsheet Studies Special Tasks: Locking the State of a One Time Condition if this formula entered on K3: =AND(OR(E3=10,K3>0),H3=FALSE) ,K3 will remain true when E10 is true, but if condition for E10 is false AND I change some other cell on the sheet it will reset back to false ! Thanks, Marcelo. Date Time Of Last Edit: 2021-12-16 18:34:22
|
| [2021-12-16 18:44:18] |
| John - SC Support - Posts: 38368 |
|
1. So signals are not monitored if the market was closed and no new data was coming in?
Sierra Chart, in general, only updates the studies when there is new data added. Therefore, when the market is closed and no new data is coming in, there are no updates or calculations performed. This is true for the Spreadsheet as well, unless you make a manual change to it. 2. 3. These are outside the scope of our support. We have set this as a User Discussion for others to comment. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-12-16 23:07:34] |
| Sawtooth - Posts: 4179 |
|
1. There would not be any signals to monitor if the market was closed and no new data was coming in. 2. You can retrieve values but not formulas. Have a look at this function: https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#GetCorrespondingMatch_Function IMO, it's not a good idea to use a blank column beyond the Formula Columns because any addition, removal, or rearrangement of studies in the Studies to Graph list could overwrite that column. If the formula doesn't need to be aligned with a chart bar, you can use Column H, or you can add a sheet that is not associated with a chart. Spreadsheet >> Add Sheet, then give it a unique name. 3. It's possible to lock a condition TRUE, and automatically release it, but it can be tricky to get it to work. The formula only requires one static cell, and it shouldn't be K3 because that is the Buy Entry cell of a Formula Column. Here is an example: https://www.sawtoothtrade.com/example-9.html And see post #4 here: Spreadsheet programming question #CREF using Locking the State of a One Time Condition |
| [2021-12-16 23:40:09] |
| User346098 - Posts: 47 |
|
Using this example 1. Put this formula in cell H3: =AND(OR(H3=TRUE,H4>1),H5) mine I put in P3 instead I am using this to prevent K3 from becoming TRUE, what I am doing is if stop price is hit then, disable K3 for good until I reset the lock MANUALLY Simple example: $H$10 has my Stop Loss price P3 cell: =AND(OR(ID0.SG4@3<=$H$10,P3),$H$7=0) H7 is a cell I manually turn on and back off to reset the lock state and in K3 I am checking for P3 state ... this WORKS fine, the issue is that if LAST price travels back ABOVE the stop (meaning ID0.SG4@3<=$H$10 condition is FALSE AGAIN) AND I work on the sheet modify some cells, the LOCK IS RESET it's like P3 cell previously saved TRUE value is set back to 0 if any cell on the sheet is modified it recalculates the spreadsheet possibly? and resets and previous saved value ? Date Time Of Last Edit: 2021-12-16 23:46:22
|
| [2021-12-17 02:37:47] |
| Sawtooth - Posts: 4179 |
|
You shouldn't use a Formula Column cell for the lock formula. It only needs a single static cell. Use P1 if you want. You'll need to use something else to reset the lock. I did mention that getting this to work can be tricky. |
To post a message in this thread, you need to log in with your Sierra Chart account: