Login Page - Create Account

Support Board


Date/Time: Sun, 21 Apr 2024 22:06:39 +0000



CROSSFROMABOVE, CROSSFROMBELOW function

View Count: 4801

[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: 3976
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: 3976
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 - 392 views
attachmentUser87521.scss - Attached On 2016-01-04 02:27:56 UTC - Size: 191.56 KB - 337 views
imageUser87521.PNG / V - Attached On 2016-01-04 02:28:07 UTC - Size: 87.25 KB - 589 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
[2024-03-30 19:27:58]
User411320 - Posts: 240
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]
Sawtooth - Posts: 3976
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.
attachmentUser87521.cht - Attached On 2024-03-30 20:10:46 UTC - Size: 5.15 KB - 32 views
attachmentUser87521.scss - Attached On 2024-03-30 20:10:52 UTC - Size: 130.02 KB - 31 views
[2024-03-30 22:07:07]
User411320 - Posts: 240
Thanks

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

Login

Login Page - Create Account