Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Sierra Chart Worksheet System For Trading/Alert Discussions
Sierra Chart's Worksheet System For Trading and Worksheet System/Alert are chart studies that use MS Excel compatible worksheets to build auto-trading systems and complex alerts, respectively. The purpose of this thread is to share the difficulties encountered and the solutions discovered regarding formulas and functionalities.
These worksheets systems of Sierra Chart provide a powerful way to build auto-trading systems and complicated alerts without C++ programming. This is especially useful for those who already have a working knowledge of spreadsheets.
Sierra's Worksheet System for Trading has an option to signal a trade entry only at the close of the bar (the worksheet's row 3), but the design logic used to accomplish this actually does it at the open of the subsequent bar, or later (row 4 or later). This may not seem like it could cause a problem, but it does when the system has two two-timeframe charts. There are several posts on the Sierra Chart forum about this, but the following is one that I posted there recently. It offers my solution, and I thought I would also share it here:
*******************************************
I have a two-chart Worksheet System for Trading chartbook that is experiencing missed or late entries. Chart 1 is a shorter timeframe than chart 2. The signal trigger is a crossover of MAs on chart 1, and the trade is entered on this chart. There are two other criteria on chart 1 that must also be TRUE (both MAs must be sloping in the direction of the trade from one bar ago), plus one criterion on chart 2 that must also be TRUE (one MA must be sloping in the direction of the trade from one bar ago). All criteria on both charts must be TRUE at the close of the bar (row 3) on chart 1 (SignalOnlyOnBarClose = YES).
Here's what is happening:
1) All criteria on both charts are TRUE at the close of the bar (row 3) on chart 1.
2) At the open of chart 1's row 4, the chart 2 criterion is FALSE because it is seeing chart 2's row 4 bar, which is several chart 1 bars ago, since it is a longer timeframe.
3) There may be a signal sometime later in chart 1's row 4 or row 5, depending on what is happening in chart 2's row 4 or row 5.
With SignalOnlyOnBarClose set to YES, the logic looks at row 4 and higher for a signal. With two-chart, two-timeframe systems, this logic design sometimes won't signal correctly:
- it either misses a signal because of (2) above, or
- it signals late in row 4 or even row 5 because of (3) above.
After much trial and error, here is the solution I have arrived at:
A) Greatly increase the granularity* of chart 2 to get enough responsiveness that chart 2's row 4 is much more likely to be the same Boolean value as its row 3, since the SOOBC=Y logic looks at row 4.
-- (*Decreasing the timeframe and increasing the MA period by the multiplicative inverse. BTW, this does not work well for range charts because range bars are not built by accumulation.)
B) Do not overlay chart 2's MA onto chart 1. Instead, add the Worksheet System for Trading study to chart 2 and call the same workbook used in chart 1.
-- Overlaying chart 2's MA onto chart 1 partially defeats the purpose of (A) above because chart 1's bars are now a longer timeframe than chart 2's bars.
C) Call chart 2's worksheet values in chart 1's worksheet formulas.
D) Add this to chart 1's row 3 entry criteria: =IF(A2="Date Time",TRUE,$J$41<A2+(1/86400))
-- This only allows a signal within the initial second of only row 4, accomplishing for me what I expect SignalOnlyOnBarClose = YES to do.
(A) and (B) solve the missed signals of (2).
(D) solves the late signals of (3).
I realize that the SC documentation does not recommend using the Worksheet System for Trading study on multiple charts but it is the only way I have found to make my system viable. I haven't noticed any synchronization issues as mentioned in the documentation.
I use J41 because it is the chart's actual time, updated every new tick, to the second. A3 is another cell with a timestamp but it is the bar's open time so it changes only as frequently as the bar and, depending on the chart's timeframe, it may not be frequent enough. The Sierra documentation examples use NOW(), but that is your computer's time, so it would only work live and not during a replay.
Also, the SC documentation example uses a complicated configuration of hours and minutes that makes it difficult to see what times are used. I like using the TIMEVALUE function so that you can easily see the times. To edit, just change the times between the quotes.
If you want to also avoid the lunchtime trade, you could use this:
=OR(OR(J41-INT(J41)<TIMEVALUE("09:30:00"),J41-INT(J41)>TIMEVALUE("16:00:00")),AND(J41-INT(J41)>TIMEVALUE("11:30:00"),J41-INT(J41)<TIMEVALUE("13:30:00")))
To flatten your position before market close, put this in cell J29:
=J41-INT(J41)>TIMEVALUE("15:59:00")
Hi!
I am trying to build an automated trading system in sierra chart to use with the s&p 500.
I am required to write a function to define when i want the program to make a buy entry.
I dont have enough knowlege in excel :\ and i would be happy if you will help me write the function.
i want the function to do the following: 1. there must be at least 2 points distance between the price at 16:30 that day (gmt +2) to the entry price.
2. i want the program to make a buy when the market price touch a daily pivot points which are in cells - aj3, ak3, ah3, al3, ak3, ag3.
3. i want the program to make a buy only if the distance between the pivot point and one of the values in cells aq3 - bm3 will be 1.5 points or less
thank you in advance!!
The INDEX(...MATCH(...)) function combination is very useful in SC worksheet calculations. It allows you to find values within defined ranges, and in SC's descending-order worksheets. Here are three example variations:
1) Volume sum between a start time and an end time.
Put this in cell Z3 (or in any column O to Z): =A3-INT(A3)
This will extract the time of day from the datetime of each bar in column A
Put this in cell H3 (or in any non-advancing cell): =TIMEVALUE("08:35:00")
This is the start time of your summing window. Edit the time between the quotes as needed.
Put this in cell H4 (or in any non-advancing cell): =TIMEVALUE("09:14:00")
This is the end time of your summing window. Edit the time between the quotes as needed.
Put this in cell H5 (or in any non-advancing cell): =SUM(INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1))
This is the sum of the volume between the start and end times of your summing window (assuming you have the default 1000 rows in your worksheet). The value should remain constant as bars/rows are added.
2) Count the crossovers in the current day.
Put this in cell Z3 (or in any column O to Z): =INT(A3)
This extracts the day from the datetime in column A
Put this in cell Y3 (or in any column O to Z): =OR(AND(E4>AC4,AC3>E3),AND(E4<AC4,AC3<E3))
This goes TRUE at all the crossovers, both directions, of the bar's last price across an indicator in column AC. Edit as needed.
Put this in a non-repeating cell, e.g. H5: =COUNTIF(INDEX(Y3:Y1002,MATCH(INT(A3),Z3:Z1002,-1),1):INDEX(Y3:Y1002,MATCH(INT(A3)-1,Z3:Z1002,-1),1),TRUE)
This counts the crossovers in the current day. (It assumes you have the default 1000 rows in your worksheet, and no more than 1000 bars in the current day)
3) Determine the maximum drawdown for the current day
Add the Trading:Maximum Open Position Loss study to your chart. (For the following formulas, I assume this study is in Column AA)
Put this in cell Z3 (or in any column O to Z): =INT(A3)
This will extract the day from the datetime of each bar in column A
Put this in cell H3 (or in any non-advancing cell): =MIN(INDEX(AA3:AA1002,MATCH(INT(A3),Z3:Z1002,-1),1):INDEX(AA3:AA1002,MATCH(INT(A3),Z3:Z1002,-1),1))
This is the maximum loss incurred for any trade within the current day. (It assumes you have the default 1000 rows in your worksheet, and no more than 1000 bars in the current day).
You can put this in H4 for yesterday's max drawdown: =MIN(INDEX(AA3:AA1002,MATCH(INT(A3)-1,Z3:Z1002,-1),1):INDEX(AA3:AA1002,MATCH(INT(A3)-1,Z3:Z1002,-1),1))
You can negative increment the MATCH(INT(A3) for other past days.
Using the Worksheet Study study, you can color bars multiple colors based on formulas. Here are 3 examples:
1) Coloring Price bars with multiple colors based on formulas
For example:
Green: MACD > 0 and MACD Diff > 0
Red : MACD < 0 and MACD Diff < 0
Brown: MACD > 0 and MACD Diff < 0 or MACD < 0 and MACD Diff > 0
1) Add the MACD study to your chart
2) Add the Worksheet Study study to your chart.
...a) On the Setting and Inputs tab, set the Chart Region to 1 and give the workbook a name in the Workbook Name field.
...b) On the Subgraphs tab, set SG1, SG2, SG3 Draw Style to Color Bar, SG1 color to green, and SG2 color to red, and SG3 color to brown.
...c) Press OK.
3) On the worksheet (assuming the MACD is in column AA and the MACD Diff is in AC):
...a) Put this is cell K3: =AND(AA3>0,AC3>0)
...b) Put this in cell L3: =AND(AA3<0,AC3<0)
...c) Put this in cell M3: =OR(AND(AA3>0,AC3<0), AND(AA3<0,AC3>0))
2) Coloring Volume bars with multiple colors based on formulas
For example:
Green: Close > Open
Red: Close < Open
Orange: Close = Open
Magenta: Volume > Volume sum of previous 2 bars
Add the Worksheet Study study to your chart.
...a) On the Setting and Inputs tab, set the Chart Region to 2 (or higher), and give the workbook a name in the Workbook Name field.
...b) On the Subgraphs tab, set SG1, SG2, SG3, SG4 Draw Style to Bar, SG1 to green, SG2 to red, SG3 to orange, and SG4 to magenta, and bar Width to your preference.
...c) Press OK.
Basic formulas:
=E3>B3
=E4<B3
=E3=B3
=F3<F4+F5
These will return a boolean values (TRUE/FALSE, or 1/0), but since you want it to display the actual value when TRUE, not the value of TRUE (which is 1) you need to give it an if,then,else condition. If it's TRUE, then display the volume value, else display nothing:
However, to properly display all of the volume bar colors, the formulas cannot have overlapping conditions. The formulas must each be exclusive. Since the magenta criteria could include any of the other three color's criteria, the other 3 formulas need to include an exclusion of magenta's criteria:
3) Coloring DMI bars with multiple colors based on formulas
For example:
Green: DI+ > DI-
Red : DI- > DI+
1) Add the DMI & ADX & ADXR study to your chart,
...a) On the Setting and Inputs tab, check Hide Study (optional), or
...b) On the Subgraphs tab, set SG3 and SG4 Draw Style to Ignore (optional).
2) Add the Worksheet Study study to your chart.
...a) On the Setting and Inputs tab, set the Chart Region to 2 (or higher), and give the workbook a name in the Workbook Name field.
...b) On the Subgraphs tab, set SG1 and SG2 Draw Style to Bar, SG1 color to green, and SG2 color to red, and bar Width to your preference.
...c) Press OK.
3) On the worksheet (assuming the DI+ and DI- are in columns AA and AB):
...a) Put this is cell K3: =IF(AA3>AB3,AA3,0)
...b) Put this in cell L3: =IF(AB3>AA3,AB3,0)
Variation : add a 3rd formula and color:
DMI Green: DI+ > DI-
DMI Red: DI- > DI+
DMI Lime: ADX > 50
(On the study’s Subgraph tab, set Subgraph SG3 to lime and Draw Style to Bar)
K3: =IF(AND(AA3>AB3,AC3<50),AA3,0)
L3: =IF(AND(AB3>AA3,AC3<50),AB3,0)
M3: =IF(AND(AA3>AB3,AC3>=50),AA3,IF(AND(AB3>AA3,AC3>=50),AB3,0))
(the ADX is assumed to be in column AC)
Variation : add more formulas and colors:
DMI Green: DI+ > DI- and ADX < threshold
DMI Red: DI- > DI+ and ADX < threshold
DMI Lime: DI+ > DI- and ADX > threshold
DMI Magenta: DI- > DI+ and ADX > threshold
ADX Green: DI+ > DI-
ADX Red: DI- > DI+
On the study’s Subgraph tab, set SG4 to magenta and Draw Style to Bar
On the study’s Subgraph tab, set SG5 & SG6 Draw Style to Dash, or Bar of wider Width (do not use Line because it connects across blanks), and set colors to green and red, respectively. This is the ADX, colored for DMI dominance.
I have a new and better solution to #2 in the above post. Instead of increasing the granularity of chart 2, I use a formula in chart 2's row 2 that gives the same boolean value (TRUE/FALSE) that occurs in row 3, so that when the bar closes and the logic looks at row 4, it still is looking at chart 2's row 3 boolean value.
For example, if the long entry criterion is that an MA on chart 2 must be sloping up from the previous bar, I put this formula in cell P3 of chart 2 (or in any spare column K-Z), assuming the MA is in column AA: =AA3>AA4
I put this in cell P2: =IF(P3>P4,P3+1,P3-1)
Then I include this reference to chart 2's column P in chart 1's long entry criteria: =chart2!P2>chart2!P3
BTW, solution D in the above post (to only allows a signal within the initial second of only row 4) is no longer needed in later versions of Sierra Chart where you set the new worksheet option Strict Signal Only On Bar Close Evaluation to YES.
