Support Board
Date/Time: Sat, 23 Nov 2024 23:07:55 +0000
[User Discussion] - user2user question regarding excel formula - not a technical SC issue.
View Count: 2691
| [2013-08-13 20:16:18] |
| namlem - Posts: 94 |
|
Hello traders, many times I write alert formulas in a worksheet, assume in cell O3. For example, if price above moving average E3>AA3 then draw an arrow at the price and automatically trigger an alert: =IF(E3>AA3,E3,FALSE) (I know I can write it without IF, but this is just a simplified example, in reality it is more complex). now I want to show only one (first) arrow per 20 (next) bars (sometimes i need to show only one arrow/signal per day), what I do is: =IF(and(E3>AA3,sum(O4:O24)=0),E3,FALSE) the problem is, I noticed that this "sum(O4:O24)" is very CPU demanding operation, especially when I do calculations for 30k or more bars and/or in SUM use more than 100 bars... my question: are there any cheaper ways how to show only one signal per X bars? Thanks |
| [2013-08-13 21:42:35] |
| Sawtooth - Posts: 4118 |
|
There is an easy way to show only the first arrow until there is an arrow in the opposite direction. Example for an up arrow: Put this in P3: =IF(E3>AA3,TRUE,IF(E3<AA3,FALSE,P4)) This will stay TRUE until E3<AA3. Put this in Q3: =IF(AND(P3,P4=FALSE),D3,0) This will place a Draw Style object at the low of the bar, but only when P3 changes from FALSE to TRUE. You can modify P3 to meet your needs. |
| [2013-08-13 22:41:15] |
| namlem - Posts: 94 |
|
tomgilb, thanks for coming for a help! it's a nice tip and it works, but in some cases it's not enough.. for example, within an hour I have 4 15 minutes bars, formula gives FALSE[P6],TRUE[P5],FALSE[P4],TRUE[P3], the arrows will be painted on Q5 and Q3 bars.. 2 times.. at the same I don't want them to be painted next X bars, because I treat those as a one signal. So, I must correct my condition, by adding that arrows/signals not necessary go in a row, gaps can happen.. I hope I made myself clear. PS. also your suggestion requires additional column in a sheet. I stared my sheet when only 16 columns were available, so currently I will not have for each existing formula/column one new column. It's not very important, as soon as SC releases Non.-NET platform I will rewrite the code. |
| [2013-08-14 19:56:35] |
| Sawtooth - Posts: 4118 |
|
The Number of Formula Columns can be set to any value 16-60. (Warning: If you change it, you'll have to edit all of your formulas to reference different columns.) Here is a way to paint n bars with the first TRUE: Put this in S1: 20 This is the number of consecutive bars to paint from the first TRUE, inclusive. Put this in S3: =IF(OR(AND(E4<AA4,E3>AA3,S4>$S$1),AND(E3>AA3,S4=$S$1)),1,S4+1) This calculates when to start, and continue, counting bars since the first TRUE. Put this in T3: =IF(AND(S3>=1,S3<=$S$1),TRUE,FALSE) This is the column used to paint the bars when TRUE. Yes, this uses 2 columns to achieve your goal, but you have 60 possible. Date Time Of Last Edit: 2013-08-14 20:03:30
|
| [2013-08-14 21:13:27] |
| namlem - Posts: 94 |
|
Hm.. this increment, after condition returned TRUE, is a good idea!! However I measured the execution time between SUM() and your approach and both are more or less equal. Must be I have a bottle neck in other place. Actually I hoped that I missed something and my task could have been solved in easy way.. Something like to mark a check box in "Study Settings" window, for each Subgraph, which says "Display Subgraph's signal only once per X days/bars" :) Thanks tomgilb for your help, really appreciate it! |
To post a message in this thread, you need to log in with your Sierra Chart account: