Home >> Support Board

Support Board

Date/Time: Wed, 15 May 2024 23:33:45 +0000

How to Post a Message for Support

CROSSFROMABOVE, CROSSFROMBELOW function

View Count: 4923

[2016-01-03 20:02:45] #1 [Go To First Post]
User87521 - Posts: 81
I am trying to create a fairly simple spreadsheet auto trade program that would take a moving average and trigger a long trade as the moving average crosses up through a horizontal pivot point. Conversely, the long is sold as the m.a. crosses down through a pivot point. There are multiple pivot points. (16) I used the horizontal line study to plot the pivot points. All seems to have gone well. I am having trouble with the range parameters in the CROSSFROMABOVE/CROSSFROMBELOW function. For the m.a. it seems fairly simple, I used the last two m.a.'s produced by the spreadsheet for the first range. It is the second range that I am having trouble with. I used the last two values produced by the horizontal line study in the spreadsheet. These numbers are identical, of course. It did not seem to work as it gave a FALSE output for the entire study. I used a horizontal range (as was output in the spreadsheet from the horizontal pivot study) using two of the pivot points but that doesn't seem right. I also used a single data point for the pivot point to see what output that would produce. My question is which set of parameters should be used for the second range. Once I know for sure which range to use I can troubleshoot the results with more confidence. If you are trying to visualize the pivots better, just assume the SPX with a horizontal pivot every 20 points from 1800. That would work just fine. Thanks.

[2016-01-03 23:54:57] #2 [Go To First Post]
Sawtooth - Posts: 3997
Try using formulas like these: For a cross from below: =AND(AC4>AA4,AA3>AC3) For a cross from above: =AND(AC4<AA4,AA3<AC3) where AC is the horizontal line and AA is the moving average. Notice that the only difference is the comparison operators. Date Time Of Last Edit: 2016-01-04 15:21:29

[2016-01-04 01:37:53] #3 [Go To First Post]
User87521 - Posts: 81
Thanks very much Tom. I did some work on this. Will this work with multiple pivot points? I ask because of the following: My spreadsheet is structured like this: MA last cell - AE3 (most up to date MA value) Pivot point cells - AI3 to AX3 (from horizontal line study) Cells are also populated from AI3 to AI "xx", AJ3 to AJ "xx"- etc. My formula for the Buy Entry is as follows: =OR(AND(AI4>AE4,AE3>AI3),AND(AJ4>AE4,AE3>AJ3),AND(AK4>AE4,AE3>AK3),AND(AL4>AE4,AE3>AL3),AND(AM4>AE4,AE3>AM3),AND(AN4>AE4,AE3>AN3),AND(AO4>AE4,AE3>AO3),AND(AP4>AE4,AE3>AP3),AND(AQ4>AE4,AE3>AQ3),AND(AR4>AE4,AE3>AR3),AND(AS4>AE4,AE3>AS3),AND(AT4>AE4,AE3>AT3),AND(AU4>AE4,AE3>AU3),AND(AV4>AE4,AE3>AV3),AND(AW4>AE4,AE3>AW3),AND(AX4>AE4,AE3>AX3)) And for the Buy Exit: =OR(AND(AI4<AE4,AE3<AI3),AND(AJ4<AE4,AE3<AJ3),AND(AK4<AE4,AE3<AK3),AND(AL4<AE4,AE3<AL3),AND(AM4<AE4,AE3<AM3),AND(AN4<AE4,AE3<AN3),AND(AO4<AE4,AE3<AO3),AND(AP4<AE4,AE3<AP3),AND(AQ4<AE4,AE3<AQ3),AND(AR4<AE4,AE3<AR3),AND(AS4<AE4,AE3<AS3),AND(AT4<AE4,AE3<AT3),AND(AU4<AE4,AE3<AU3),AND(AV4<AE4,AE3<AV3),AND(AW4<AE4,AE3<AW3),AND(AX4<AE4,AE3<AX3)) No syntax errors recognized. Both the buy entry and the buy exit register "FALSE". Currently, the Buy Exit should register "TRUE".

[2016-01-04 02:29:20] #4 [Go To First Post]
Sawtooth - Posts: 3997
It works for me. Cross from belows are cyan color bars; cross from aboves are magenta color bars. See attached. This means the formulas are valid, so here are some possible reasons you only see FALSE: 1) The symbol you are using has a very small Tick Size and the Value Format of the MA and Horizontal Lines needs to be equal or smaller. 2) There are some crossovers where the MA value equals the Horizontal Line value. 3) The default Tick Size from the data feed is too large. Change the Tick Size in the Symbol Settings to a smaller value and Delete All Data And Download. (This is rare and usually only with Forex). Only #2 would apply to the SPX.


User87521.cht - Attached On 2016-01-04 02:27:44 UTC - Size: 76.02 KB - 406 views User87521.scss - Attached On 2016-01-04 02:27:56 UTC - Size: 191.56 KB - 349 views User87521.PNG / V - Attached On 2016-01-04 02:28:07 UTC - Size: 87.25 KB - 602 views

[2016-01-04 05:13:02] #5 [Go To First Post]
User87521 - Posts: 81
Once again. Thanks very much. I will take this data and do some work with it. Very much appreciated.

[2016-01-04 05:45:26] #6 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
Letting you know we tested this formula and it works just fine: =CrossFromAbove(id2.sg1@3:id2.sg1@4,id3.sg1@3:id3.sg1@4) ID2 is the Line study and ID3 it is the Moving Average study. Result: http://www.sierrachart.com/image.php?l=1451886196206.png We see no problem with the CROSS* functions. 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

[2016-01-04 15:56:20] #7 [Go To First Post]
User87521 - Posts: 81
Thanks very much for looking at this. I am a bit unclear as to the syntax you used, so I will study this and do some experimentation. Thanks again.

[2016-01-04 18:18:43] #8 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
Refer to: https://www.sierrachart.com/index.php?page=doc/doc_Spreadsheets.html#ReferencesStudySubgraphColumnsSpreadsheetStudy 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

[2016-02-03 16:18:54] #9 [Go To First Post]
User87521 - Posts: 81
FYI, I have been using these functions and they seem to be working quite well. Thanks for all your help.

[2016-02-03 18:31:43] #10 [Go To First Post]
Sierra Chart Engineering - Posts: 104368
There have been significant performance improvements with New Spreadsheets. Update Sierra Chart as soon as you can by following these instructions: https://www.sierrachart.com/index.php?page=doc/download.php#FastUpdate 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

[2024-03-30 19:27:58] #11 [Go To First Post]
User411320 - Posts: 266
Hey sawtooth, I downloaded the chartbook you posted and got the following message (2024-03-30 15:24:13.257 \| The Chartbook file is not compatible. Contact Sierra Chart support for help with converting it. File: C:\SierraChart\Data\User87521.cht *) My question is what exactly is is the chartbook about? I see if it crosses from below it plots cyan, but is it based off MA or ranges or both?

[2024-03-30 20:11:07] #12 [Go To First Post]
Sawtooth - Posts: 3997
My question is what exactly is is the chartbook about? The chartbook/spreadsheet colors the bar when the SMA10 (ID3) crosses any of the Horizontal lines; cyan for a crossfrombelow, and magenta for a crossfromabove. It is intended to only be an example. I updated the chartbook for compatibility with the latest versions. See attached.


User87521.cht - Attached On 2024-03-30 20:10:46 UTC - Size: 5.15 KB - 67 views User87521.scss - Attached On 2024-03-30 20:10:52 UTC - Size: 130.02 KB - 61 views

[2024-03-30 22:07:07] #13 [Go To First Post]
User411320 - Posts: 266
Thanks

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