Home >> Support Board

Support Board

Date/Time: Sun, 28 Apr 2024 14:47:38 +0000

How to Post a Message for Support

[User Discussion] - Lock a One Time Value in a spreadsheet

View Count: 2584

[2014-02-02 07:40:47] #1 [Go To First Post]
User025429 - Posts: 26
I have tried to use the “locking the State of a One Time Condition” but have not been successful. I want to lock a specific time/bar number and then use this value in a formula in a spread sheet trading system. I can achieve what I want by copy/past/special/value, but obviously this is not suitable for trading. Please see attached file. Thank you for looking


FIXED BAR NUMBER.xlsx - Attached On 2014-02-02 07:40:01 UTC - Size: 10.13 KB - 399 views

[2014-02-02 15:03:33] #2 [Go To First Post]
Sawtooth - Posts: 3980
The "Locking the State of a One Time Condition" only locks a TRUE. It cannot retain a variable. Retaining a variable is possible using a formula like this: O3: =IF(F3>5000,A3-INT(A3),O4) This will retain the bar time of the bar that exceeds 5000 volume, and retain it until the next time the bar exceeds 5000 volume.

[2014-02-02 16:26:25] #3 [Go To First Post]
User025429 - Posts: 26
Thanks Tom. I am trying to retain either a bar number or the bar time, not both. I want to list all subsequent bars that come after the specified bar/time to perform calculations only on these bars. In your example, in the place of the 5000, I want to reference a cell to "get" the number and then not refresh that number after that. Is there a more efficient way of doing it?

[2014-02-06 18:09:45] #4 [Go To First Post]
User025429 - Posts: 26
Support Could you add such a formula?

[2014-02-07 00:12:22] #5 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
This is not really clear to us, and is outside the scope of our support. Also if this is a feature request, this kind of feature request is rather advanced and has to be analyzed later. And as busy as we are, we do not foresee getting to this anytime soon. The general rule is that if you cannot do something easily with spreadsheets, use ACSIL: http://www.sierrachart.com/index.php?l=doc/doc_CreatingDLLs.html Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, change to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2014-02-07 00:14:36

[2014-02-07 06:28:39] #6 [Go To First Post]
vegasfoster - Posts: 444
Just add another condition that the previous cell has to be 0, =IF(and(F3>5000, O4=0), A3-INT(A3),O4)

[2014-02-08 10:08:57] #7 [Go To First Post]
User025429 - Posts: 26
Thanks vegasfoster for your input. I may not be explaining what I need correctly. I want to do calculations, on the bar data after a order is placed. So the "5000" would need to come from a dynamic cell, like the current time(=Now)and only become a fixed value once the order has been placed. I another program for MT4, Mexceltrader, only the current data line is active, once that bar closes, all the data is kept but as fixed values. Is there a way of doing this in Sierra?

[2014-02-08 15:23:19] #8 [Go To First Post]
Sawtooth - Posts: 3980
I don't know of any way in a Sierra Chart spreadsheet study to capture the value of a dynamic cell, like the chart time in J41, at the occurrence of another dynamic event that is not in the array. The historical time of any event is solely in column A, so the dynamic event must somehow be recorded in the array. Even so, if the bar duration is lengthy, the time of the event cannot be more precise than the bar's timestamp.

[2014-02-08 16:10:33] #9 [Go To First Post]
User025429 - Posts: 26
Thanks Tom

[2015-08-06 17:30:26] #10 [Go To First Post]
mario mario - Posts: 22
Is it not possible to capture the value in a spreadsheet from a dynamic variable? I use the lock variable for the signal to stay if true but having the value when the signal became true will be very very useful. thanks

[2015-08-06 18:01:10] #11 [Go To First Post]
Sawtooth - Posts: 3980
If the value is in the array, you could use a double IF formula, or an INDEX...(MATCH...)) formula, to bring the value from the row it occurred up to row 3. Since there are so many possible scenarios, I would not be able to offer any specific examples. However, as I said in post#8, the value captured would be at the close of the bar, and the time of the occurrence would not be any more precise than the bar's timestamp.

[2015-08-06 20:06:56] #12 [Go To First Post]
mario mario - Posts: 22
at any particular time/bar if the signal condition is met, how can we bring that value from (B:3 TO E3) TO (K:3 to N:3) and that k3 to n3 value should stay as a constant number irrespective of the b3 to e3 value later on. thanks tom.

[2015-08-06 21:21:12] #13 [Go To First Post]
Sawtooth - Posts: 3980
Here's a general example of an MA cross up setting a value, and an MA cross down setting another value: =IF(CROSSFROMBELOW(AQ3:AQ4,AR3:AR4),E3,IF(CROSSFROMABOVE(AQ3:AQ4,AR3:AR4),C3,S4)) where this formula is in cell S3. This will repeat the value until the MA crosses in the opposite direction. Substitute the CROSSFROMBELOW/ABOVE condition for your condition.

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