Login Page - Create Account

Support Board


Date/Time: Sun, 29 May 2022 01:43:02 +0000



[User Discussion] - user2user question regarding excel formula - not a technical SC issue.

View Count: 2067

[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]
tomgilb - Posts: 3277
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]
tomgilb - Posts: 3277
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:

Login

Login Page - Create Account