Support Board
Date/Time: Tue, 26 Nov 2024 18:27:35 +0000
[User Discussion] - sort of a statete machine in a spreadsheet
View Count: 2121
| [2014-06-05 11:54:55] |
| djcurcio - Posts: 236 |
|
Can anyone figure out how to create something similar to a state machine / switch in a spreadsheet? For example: If I have generate a valid buy signal in Column K3, I want to have the switch set to indicate "In Buy Mode" with a 1. Thanks |
| [2014-06-05 14:00:13] |
| Sawtooth - Posts: 4120 |
|
There are two ways to do this: 1) In a single non-repeating cell, e.g. H3 (not in the array): http://www.sawtoothtrade.com/example-9.html 2) In row 3 of a Formula Column, e.g. O3 (in the array): =IF(StartBuyModeCondition,TRUE,IF(EndBuyModeCondition,FALSE,O4)) The idea is to latch a persistent TRUE with the first IF and release it with the second IF. There are many variations of this and you may need to experiment with the idea. Date Time Of Last Edit: 2015-08-14 02:02:55
|
| [2014-06-08 20:17:15] |
| eagle - Posts: 92 |
|
I do this a little differently, with no circular reference. I use four Formula Columns. I'll use AU3, AV3, AW3, AX3 in this example. This example uses a 300 row spreadsheet. In AU3 (labeled Last Buy): =MATCH(TRUE, K4:$K$300, 0) In AV3 (labeled Last Sell): =MATCH(TRUE, M4:$M$300, 0) In AW3 (labeled Buying Mode): =AU3<AV3 In AX3 (labeled Selling Mode): =AU3>AV3 Either AW3 or AX3 will always be TRUE, and the other one will always be FALSE. (So, of course, you don't really need both columns.) I use these with my Buy Exit and my Sell Exit. In L3, Buy Exit: =AND(NOT(M3),AS3<(-11),AW3) In N3, Sell Exit: =AND(NOT(K3),AS3>11,AX3) I am not a programmer. Date Time Of Last Edit: 2014-06-08 20:21:56
|
| [2014-06-08 21:42:55] |
| djcurcio - Posts: 236 |
|
Thanks for the info. Much appreciated. Questions: 1. I assume that AU3 thru AX3 are simply available cells? 2. What is of AS3 re +/- 11? 3. I don't understand the MATCH line - what is it doing? Thanks |
| [2014-06-09 11:48:13] |
| eagle - Posts: 92 |
|
1. They're any available "Formula Column". The Formula Columns are from column "O" thru to the "Number Of Formula Columns" configured in the spreadsheet Study Settings. 2. My personal criteria in the spreadsheet I was using as an example for a BuyExit/SellExit without the additional qualifiers. Ignore that. I suppose I should have written: =AND(NOT(M3),{criteria for a Buy Exit},AW3)
3a. MATCH(lookup_value, lookup_array, [match_type]). In cell AU3, for example, lookup the lookup_value "TRUE" in lookup_array "K4:$K$300" and match the first value that is exactly equal to "TRUE" (without the quotes), specified by match_type "0". MATCH returns the relative position of the lookup_value in the lookup_array; in other words, MATCH returns the number of rows since the last buy entry signal, since column K is the SC Buy Entry Column. Google "excel formula match" (without the quotes). 3b. So, the formula in cell AU3 returns the number of rows since the Last Buy in column K. The formula in cell AW3 returns the number of rows since the Last Sell in column M. Whichever return is a smaller number indicates whether the current mode is Buy Mode or Sell Mode. Chances are my method is more compute intensive than Tom's method or the method mentioned under Locking the State of a One Time Condition on this page: http://www.sierrachart.com/index.php?l=doc/doc_WS_AddInfo.html. It also contains no error checking in case there's no match. But I didn't want to have any circular references. Date Time Of Last Edit: 2014-06-09 11:50:03
|
To post a message in this thread, you need to log in with your Sierra Chart account: