Login Page - Create Account

Support Board


Date/Time: Sun, 03 Mar 2024 21:33:25 +0000



CROSSFROMABOVE, CROSSFROMBELOW function

View Count: 4647

[2016-01-03 20:02:45]
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]
Sawtooth - Posts: 3934
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]
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]
Sawtooth - Posts: 3934
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.
attachmentUser87521.cht - Attached On 2016-01-04 02:27:44 UTC - Size: 76.02 KB - 382 views
attachmentUser87521.scss - Attached On 2016-01-04 02:27:56 UTC - Size: 191.56 KB - 325 views
imageUser87521.PNG / V - Attached On 2016-01-04 02:28:07 UTC - Size: 87.25 KB - 571 views
[2016-01-04 05:13:02]
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]
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]
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]
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]
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]
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

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

Login

Login Page - Create Account