Sierra Chart - Worksheet Systems, Alerts and Automated Trading

Worksheet Systems, Alerts and Automated Trading

Related Documentation Pages

On This Page

General Information

[link]

The .Worksheet Study/System/Alert study and the Worksheet System for Trading study can be used to create a Trading System or an Alert for your chart. A trading system or an alert means rules that you define which will provide buy or sell signals when the rules are true for a particular bar in the chart. These signals will be shown as graphical indications on the chart, show text alert lines and give audible alerts. In the case of the Worksheet System for Trading study, actual trade orders will also be given. An alert refers to the signal given when your system/alert rules are true on the last bar. All of this is described in more detail below.

If you're just interested in getting an alert when your condition is true at the end of the chart and you do not need to see historical graphical indications on the chart of this, then in most cases you can just simply use the Simple Alert feature. And not bother using the more advanced Worksheet studies.

The difference between the Worksheet System for Trading study as compared to the .Worksheet Study/System/Alert study, is that the Worksheet System for Trading study has much more advanced logic for creating a trading system. With with the Worksheet System for Trading study you can perform back testing and generate actual trade signals either which will be simulated or can be sent to your trading service. By default they will always be simulated. Even if you don't actually want to place live orders with the Worksheet System for Trading study, and you just want to create a system and Backtest it or just wish to place trades manually from it, you still will want to use the Worksheet System for Trading study rather than the .Worksheet Study/System/Alert study. If you just simply want to create a simple alert to give you an indication when your rules are true, then you will want to use the .Worksheet Study/System/Alert study.

Creating a System/Alert is accomplished by entering a condition formula in the formula columns on the Worksheet. This condition formula can access the price and study data (Study data is outputted beginning at column AA). This condition formula will be applied across the entire price graph. It will be continuously monitored and also can be scanned for (you should not do automated trading from a scan). This formula is used to give an indication of when a certain condition is true for any chart column/bar. These indications are used to create a trading system or an alert. There is no difference between the way the program does systems and alerts. Both give graphical, text, and audible indications when your formula is true. Although, a text message, audible sound, and a trade signal, in the case of the Worksheet System for Trading study, are only given when a condition formula returns true on the very last column in the chart. Also, when a condition formula is true on the very last column in the chart, then an ! mark will be displayed in front of the study name and the study name will be highlighted in red. The alert message text and the audible alert sound are what constitute an alert indication.

In the case of the Worksheet System for Trading study, a trade signal and an alert are not necessarily given when your condition formula in one of the Buy/Sell Entry/Exit formula columns returns TRUE on the last column in the chart. There is other logic that is applied and other conditions have to be met. All of this is explained on this page. Additionally, in the case of the Worksheet System for Trading study, if your condition formula returns TRUE on the last column in the chart, it will be ignored if data is being downloaded into the chart or when the chart is fully recalculated. These are safeties to prevent trades from being given unintentionally, in case you are not in trade simulation mode.

The program provides a text alert for your condition formula when it becomes true by adding a detailed message to the Alert Log. To open the Alert Log window select Window >> Show/Hide Alert Log on the menu. If the condition is true in a column other than the last chart column, then only a graphical indication is given.

[Top]

Sound for Audible Alert and E-Mail Message Notifications

[link]

You can specify an alert sound to be played when your System or Alert condition in cells K3 through Z3 become true. For your formulas in the Worksheet to trigger sound alerts, go to the Input settings for your Worksheet study. For instructions to go to Input settings for a study, see Modifying Studies. Keep in mind, that after a Worksheet study is applied to a chart it changes its name in the Studies to Graph list on the Chart Studies window to the name of the specified Workbook file. On the Settings and Inputs tab you will see the list of all the available Inputs. In the case of the Worksheet Study/System/Alert study, be sure to set the System/Alert Input to 2. You will see several inputs named Column K,L,M,N,O,P,Q,R,S,T,U,V-Z Alert that will let you select the Alert Number to be used for each column. Select the Alert Sound Number that you want to use, or select No Alert. Worksheet columns W through Z use the same alert sound number as column V. To set the sound file that plays for each alert number, select Global Settings >> General Settings on the menu. You can also setup an email message to be sent when an alert sound is triggered.

If the Draw Style for a Worksheet column is set to Ignore, then no system or alert signals can be triggered by your formulas. However, this does not apply to column K through N when using the Worksheet System for Trading study. With that study, trade signals can always be triggered by your formulas regardless of the Draw Style setting.

[Top]

Scanning

[link]

To scan for the system or alert condition in the chart data for all of your symbols see the Scan Menu commands description.

[Top]

Creating a System or Alert

[link]

This section applies to the Worksheet Study/System/Alert study.

  1. Follow the step-by-step Worksheet Study/System/Alert instructions.

  2. When following those instructions and you get to the step to set the Study or System/Alert input, set this input to 2. This input is described below.

  3. When you get to the step where you enter a formula, enter your condition formula. See the Examples section below for example condition formulas.

[Top]

Creating a System for Automated Trading

[link]


If you are using the Worksheet System for Trading study on a Live account and sending orders to the connected trading service, and you wish to halt order entry for any reason, you can do any of the following:

  1. Select File >> Disconnect. Or press the DN button on the tool bar. This will break the connection to the trading service.
  2. Enable Trade Simulation Mode by making sure that there is a check mark by Trade >> Trade Simulation Mode On.
  3. Set the Send Orders To Trade Service input with the study to NO.
  4. Set the Disable Auto Trading cell on the corresponding Worksheet to TRUE.

Click here for Worksheet Study Videos. Including the Automated Worksheet Trading Video.

  1. Make sure there is a check mark by Trade >> Trade Simulation Mode On on the menu. This is so that orders will not be sent to your trading service. Instead all trades will be simulated. Additionally, the Send Orders To Trade Service input with the Worksheet System for Trading study is set to No by default, which also prevents the orders from being sent to your trading service. With these two settings, there are two layers of protection against sending actual orders.

  2. Go to an open chart or open a chart. Select Analysis >> Studies on the menu. Select Worksheet System for Trading in the Studies Available list on the left. The study is near the bottom of the list. Please don't confuse it with the .Worksheet Study/System/Alert study near the top of the list. Press the Add button.

  3. For this example, also add the study named Moving Average-Simple (the default settings are fine). We will create a system that will buy when the current price moves above a moving average and sell when the current price moves below a moving average. This is only intended as an example in these instructions and not necessarily an actual trading system.

    If you want to make a system different than this example by using other studies, add them to the chart now if you have not already added them. Adjust the settings as you require them for each study.

  4. Highlight the Worksheet System For Trading study in the Studies to Graph list box. Press the Settings button. In the Technical Study Settings window on the Settings And Inputs tab, there is a text box for the Workbook Name (lower left). Enter a name in the text box without a file extension. This is the name of the workbook that will be opened or created to contain the data and formulas. The name should be related to what your custom system will do.

  5. In the Input list, select the input named Default Order Quantity and enter the number of shares or contracts you wish to trade when an order is sent. It is also possible to override this quantity in cell J26. You will also find in the Input list an input named Maximum Position Allowed in Same Direction. Set this input as you require. It is the maximum position in shares/contracts that is allowed.

  6. If you want to send the orders to the Sierra Chart internal trade simulation system, the Send Orders To Trade Service input needs to be set to No. Note that in Trade Simulation Mode, the Order System is the trade simulation system. Therefore, even if this input is set Yes while in Trade Simulation Mode, the orders will still be simulated. To send order signals to the live trade service, the Send Orders To Trade Service input must be set to Yes and the Order System must be set to the live trade service by disabling Trade >> Trade Simulation Mode On on the menu.

  7. The other inputs are correct as they are for this example. For more information about all of the inputs, see Inputs that are specific to Systems, Alerts, and Automated Trading.

  8. In the list of available study Inputs you will see inputs named Column K-V Alert. Set these to the alert sound number you want to hear when your system/alert condition formula in the corresponding column gives a trade/alert signal. For more information, see Sound for Audible Alert and E-Mail Message Notifications.

  9. Press the OK button on the Technical Study Settings window.

  10. Press the OK button on the Chart Studies window.

  11. The workbook will be opened in the background. Go to it by selecting Window >> and the workbook name on the menu. The name will be what you have specified in the settings window for the study. A workbook contains multiple worksheets. The worksheet number the data is outputted to, is the same worksheet number as the chart number. For example, chart #2 will be outputted to Sheet2 in the workbook. The worksheets can be selected with the tabs at the bottom of the workbook. All of the individual chart studies are also outputted to the same worksheet beginning at column AA.

  12. All new orders use the settings of the Trade and Trade Settings Window. While the chart that contains the Worksheet System for Trading study is active, select Trade >> Open Trade Window for Chart to see what the settings are set to. For example, if the expiration type is set to Day, all new orders places from the Worksheet will automatically have an expiration type of Day. The settings that you can set in the Trade Window also include advanced features such as Attached Orders. For more information about Attached Orders, please see the Attached Orders Documentation page.

    The following steps will give you an example of a trading system using spreadsheet formulas.

  13. Now we will give an example of simple Buy Entry and Sell Entry formulas. Let's say we want to buy when the last price crosses from below the moving average and sell when the last price crosses from above the moving average. In cell K3 (labeled: Buy Entry), type =CROSSFROMBELOW(E3:E4,AA3:AA4), where E3:E4 refers to the Last Prices and AA3:AA4 refers to the moving average values. In cell M3 (labeled: Sell Entry), type =CROSSFROMABOVE(E3:E4,AA3:AA4), where E3:E4 refers to the Last Prices and AA3:AA4 refers to the moving average values. Now, whenever the last price crosses from below the moving average, K3 (Buy Entry) will trigger a Buy order, and whenever the last price crosses from above the moving average, M3 (Sell Entry) will trigger a Sell order. By default, only one order can be sent until a corresponding exit signal is given. Therefore, all other Buy Entry signals are ignored until the Buy Exit formula becomes true and triggers a sell, and all other Sell Entry signals are ignored until the Sell Exit formula becomes true and triggers a buy. This behavior is controlled by using the Allow Multiple Entries In Same Direction input.

    If you have multiple conditions for your trading system, then you should use the AND() function. For example, if you have a RSI study on the chart and you wish to combine that with the Moving Average use something like this formula for the Buy Entry: =AND(CROSSFROMBELOW(E3:E4,AA3:AA4),AC3<40)

  14. In this example we will create a Buy Exit (Sell) that exits a position when a stop or target is reached. A Buy Exit is optional. A Sell Entry could close out a position created by a Buy Entry. Or, the position can be manually or externally closed out, such as by an Attached Order. In this example, we are using a Buy Exit formula to show you an exit strategy managed completely by the Worksheet study.

    In cell L3 (labeled: Buy Exit), type =OR(AND($J$8>0,$E$3<=$J$9-1), AND($J$8>0, $E$3>=$J$9+2)). E3 refers to the Last Price, J8 refers to the Position Quantity, and J9 refers to the Position Average Price. When this formula in cell L3 returns TRUE, it will trigger a Sell order. What this formula means is that when there is a long position AND the last price is less than the price the Buy Entry was filled at minus 1 full point (Stop), OR there is a long position AND the last price is greater than the price the Buy Entry was filled at plus 2 full points (Limit), then return TRUE. This will cause a Sell order to be placed. This is just an example and you need to use what is appropriate.

    The $ means a cell reference refers to an absolute cell and the column and row will not be adjusted when the formula is filled down. This formula is relevant only to the topmost row in the worksheet (corresponds to the last bar in the chart), even though it is applied to historical bars as well. See the Special Worksheet Outputted Values section for more information on the data in the J column.

    When entering numbers and formulas, keep in mind to use the proper delimiters in your numbers and formulas, according to your Regional Setting. For more information, see the Regional Setting section.

  15. In this example we will create a Sell Exit (Buy) that exits a position when a stop or target is reached. A Sell Exit is optional. A Buy Entry could close out a position created by a Sell Entry. Or, the position can be manually or externally closed out, such as by an Attached Order. In this example, we are using a Sell Exit to show you an exit strategy managed completely by the Worksheet study.

    In cell N3 (labeled: Sell Exit), type =OR(AND($J$8<0,$E$3>=$J$9+1), AND($J$8<0, $E$3<=$J$9-2)). E3 refers to the Last Price, J8 refers to the Position Quantity, and J9 refers to the Position Average Price. When this formula in cell N3 returns TRUE, it will trigger a Buy order. What this formula means is that when there is a short position AND the last price is greater than the price the Sell Entry was filled at plus 1 full point (Stop), OR there is a short position AND the last price is less than the price the Sell Entry was filled at minus 2 full points (Limit), then return TRUE. This will cause a Buy order to be placed. This is just an example and you need to use what is appropriate.

    The $ means a cell reference refers to an absolute cell and the column and row will not be adjusted when the formula is filled down. This formula is relevant only to the topmost row in the worksheet (corresponds to the last bar in the chart), even though it is applied to historical bars as well. See the Special Worksheet Outputted Values section for more information on the data in the J column.

  16. NOTE: These stops and targets are sent by the program to the exchange when the conditions are met. This is necessary in order to accomplish advanced exit strategies. Although these are not advanced exit strategies, we demonstrate how you can do advanced exit strategies. In order for an order to be placed as fast as possible, it is a good idea to make sure your chart update interval, which is set through Global Settings >> General Settings on the menu, is set to 1000 milliseconds or less. However, avoid making it very small unless your computer is very fast. You can also use service managed exit strategies if you service trading supports them. These are set under Trade >> Open Trade Window for Chart >> Attached Orders.

  17. Now that we have entered our worksheet formulas on the worksheet, it is necessary to go back to the chart. Select Window on the menu and select the chart that you applied the Worksheet System for Trading study to. To ensure the formulas are fully copied down the formula columns on the worksheet, select Chart >> Reload And Recalculate on the menu. You will see the buy and sell arrows on the chart. These arrows represent the TRUE values returned from the Buy Entry and Sell Entry condition formulas in formula columns. By default the Subgraph Draw Style for the Exit columns (L and N) are set to Do Not Draw so they do not appear. In this example, we used exits which are based upon position data which is only known for the current position so the buy/sell arrows are not relevant historically. If your exits were based upon the price and/or study data, then you could set the corresponding Exit columns to a visible Draw Style.

    As your chart is updated, either live or during a replay, buy and sell entry and exit signals and orders will be generated and shown in various logs. These signals produce Alerts and these can be viewed by selecting Window >> Alert Log on the menu. To view the all trading activity, select Trade >> Trade Activity Log on the menu. To view the Trade Service Log, select Trade >> Trade Service Log on the menu. The Trade Service Log shows the orders that have been given as well, whether simulated or not, and ignored signals from the formulas.

  18. The next step is to simply watch your system perform in real-time as your chart is updated or replay your chart to perform Back Testing. Back testing through replays is a very good way to test your system and get a good feel for it.

  19. If and when you are ready to have your trading system place real orders, then the first step is to reset your system to a default state. You should reset the Session Position data by selecting Trade >> Positions Window on the menu, highlight the symbol line on the Session Positions tab and press the Clear Selected Session Position Data button. Next, go to live mode by making sure there is no check mark by Trade >> Trade Simulation Mode On. Keep in mind though that if you are using a simulated account with your trading service, that even though you are not in Sierra Chart Trade Simulation Mode, the orders will still be simulated. And finally go to the Study Settings for the Worksheet System for Trading study on your chart and set the Send Orders To Trade Service input to Yes. At this time any trading signals given will be sent as orders to your trading service assuming you are connected to a valid trading service and you're connected to the data and trade server (File >> Connect to Data Feed).

[Top]

Initiating Trades with Auto-Trading and Using Manual or Attached Orders Exits

[link]

It is not necessary to use the Buy Exit or Sell Exit columns in the Worksheet Trading study to exit a position. Exits can be done by manually entering an order through the Trade Window (Trade >> Open Trade Window for Chart) or can be performed by using the Attached Orders feature (targets and stops). Targets and stops are configured through the Trade Window on the Attached Orders tab. For more information, please refer to the Attached Orders documentation page.

A set of Attached Orders which is entered on the Trade Window for a chart is saved when a chartbook is saved. The trade window configuration, which also includes Attached Orders can also be saved to a trade window configuration file. To save or load a configuration file select the More tab on the Trade Window. If you are providing an automated trading system to users that uses Attached Orders, then you should provide them your saved chartbook file which contains your Worksheet System for Trading study and also will have the complete Trade Window configuration containing the Attached Orders. Or you can provide them a Study Collection file they can use to apply the automated trading system to the chart and also provide them a trade window configuration (.twconfig) file that they can use to load the Attached Orders set into the Trade Window for use with the automated trading system.

Orders that are given by the Buy/Sell Exit column formulas do not use Attached Orders configured in the Trade Window. Only Buy/Sell Entry do.

When you are using the automated trading functionality in Sierra Chart, it is recommended that you always enter and cancel any manual orders from within Sierra Chart to avoid any unexpected auto trade issues due to Sierra Chart not being aware of the orders and position changes you do from other trading programs.

Using Both Attached Orders and Buy Exit and Sell Exit at the Same Time

[link]

It is possible to define a set of Attached Orders to use with your Worksheet System for Trading system and use the Buy/Sell Exit columns at the same time. For this to work properly, you need to set Cancel All Working Orders On Exit input to Yes.

[Top]

Study Settings Inputs that are Specific to Systems, Alerts, and Automated Trading

[link]

For the other available Study Settings Inputs, see the Worksheet Study Inputs page.

Study or System/Alert input

This input does not apply to the Worksheet System for Trading study. By default the Worksheet System for Trading study always functions as a system/alert study. For more information see Column K, L, M, N Order Actions.

This input is set in the Technical Study Settings window for the Worksheet Study/System/Alert study. When this input is set to 1, the formula results from the worksheet will be displayed as normal indicator study on the chart. When this input is set 2, the formula results from the worksheet will be displayed in the main price graph area (Region 1) of the chart and drawn with arrow styles by default. The formulas on the Worksheet should return either a 1 (TRUE) or zero (FALSE) value. A value of 1 or TRUE indicates the system/alert condition has been met. For example: You would enter =E3 < 75 in Row 3 of the K formula column to set up a Long Entry alert when when the closing price is less than 75.

Long Entry

The K column is the Long Entry column. If a cell in this column is true (1), then an arrow (depends upon the subgraph Draw Style) will be drawn on the chart at the low value of the bar to indicate a Long Entry for that bar.

Long Exit

The L column is the Long Exit column. If a cell in this column is true (1), then an arrow will be drawn on the chart at just below the high value of the bar to indicate a Long Exit for that bar.

Short Entry

The M column is the Short Entry column . If a cell in this column is true (1), then an arrow will be drawn on the chart at the high value of the bar to indicate a Short Entry.

Short Exit

The N column is the Short Exit column. If a cell in this column is true (1), then an arrow will be drawn on the chart at just above the low value of the bar to indicate a Short Exit.

These are the default names. You can rename the columns and use them for any kind of condition.

You can also return values other than 1 or zero. If the value is not 1 or zero, then the arrow will be drawn at the value you return. If you are graphing columns other than K, L, M, or N, the return value must be within the range of values of the graph your study is overlaid on.

Reset (System/Alert) Condition On New Bar

If this option is set to Yes, then a system or alert signal will be given when a new bar is added to the chart and the system/alert condition is TRUE at row 3 even though the system/alert condition was previously TRUE on the prior bar/row. Normally, when a formula returns TRUE or a nonzero value in one of the worksheet columns used for systems or alerts and a new bar is added, a new signal will not be given even if it still TRUE. It will have to go back to FALSE and then back to TRUE again, unless this input is set to Yes.

Alert/Signal Only Once Per Bar

If this option is set to Yes, then an alert or trading signal will be given only once per bar. When a Signal is given once, another will not be given until there is a new bar and your System/Alert condition formula goes back to false and then to true.

Allow Multiple Entries In Same Direction

[link]

This input applies only to the Worksheet System for Trading study.

See the description on the Auto Trade Management page.

Maximum Position Allowed

This input applies only to the Worksheet System for Trading study.

See the description on the Auto Trade Management page.

Default Order Quantity

[link]

This input applies only to the Worksheet System for Trading study. This is the quantity of shares or contracts to buy or sell when orders are triggered through the Worksheet System for Trading study. For convenience, it is possible to override this input within the worksheet. Simply put in a Custom Order Quantity in cell J26. For more information about special worksheet inputs, please see the Special Worksheet Inputs section.

Allow Opposite Entry with Opposing Position or Orders

This input applies only to the Worksheet System for Trading study.

See the description on the Auto Trade Management page.

Send Orders To Trade Service

See the description on the Auto Trade Management page.

Support Reversals

See the description on the Auto Trade Management page.

Cancel All Orders on Entry and Reversals

[link]

See the description on the Auto Trade Management page.

Signal Only on Bar Close

This input is relevant to the Worksheet System for Trading study and the Worksheet Study/System/Alert study when it is set to work as a system or alert. There are two versions of this input, one for K and M and the other for L, N-Z. When this input is set to Yes, condition formulas that return a non-zero value at row 3 in the corresponding formula columns are ignored until the bar closes. The way this is determined is when a new bar is added. In other words row 4 is monitored and not row 3. When this input is set to Yes, then anywhere in the documentation row 3 is mentioned, consider it as row 4.

There is one scenario that you should keep in mind about. Let's say you have a formula in one of the formula columns and it returns true at row 3 (the last bar of the chart) in the column. You see the arrow on the chart. A new bar is added, the bar therefore is considered closed, the formula moves down to row 4, it's no longer true and the arrow disappears. In this case when you are using Signal Only on Bar Close, you will not get a signal.

Buy Entry: Use Limit Order

[link]

This input only applies to the Worksheet System for Trading study. When this input is set to Yes, the Buy Entry column will only send Limit orders instead of Market orders. The limit price is set in cell J22. If you wish to dynamically alternate between limit and market orders in your trading system, then set this to Yes and set a limit value which is above the market price for an immediate fill. For more information about special worksheet inputs, please see the Special Worksheet Inputs section. If the limit price cell is set to 0 or is blank, then the Limit price will be set to the current Ask price.

Buy Exit: Use Limit Order

[link]

This input only applies to the Worksheet System for Trading study. When this input is set to Yes, the Buy Exit column will only send Limit orders instead of Market orders. The limit price is set in cell J23. If you wish to dynamically alternate between limit and market orders in your trading system, then set this to Yes and set a limit value which is below the market price for an immediate fill. For more information about special worksheet inputs, please see the Special Worksheet Inputs section. If the limit price cell is set to 0 or is blank, then the Limit price will be set to the current Bid price.

Sell Entry: Use Limit Order

[link]

This input only applies to the Worksheet System for Trading study. When this input is set to Yes, the Sell Entry column will only send Limit orders instead of Market orders. The limit price is set in cell J24. If you wish to dynamically alternate between limit and market orders in your trading system, then set this to Yes and set a limit value which is below the market price for an immediate fill. For more information about special worksheet inputs, please see the Special Worksheet Inputs section. If the limit price cell is set to 0 or is blank, then the Limit price will be set to the current Bid price.

Sell Exit: Use Limit Order

[link]

This input only applies to the Worksheet System for Trading study. When this input is set to Yes, the Sell Exit column will only send Limit orders instead of Market orders. The limit price is set in cell J25. If you wish to dynamically alternate between limit and market orders in your trading system, then set this to Yes and set a limit value which is above the market price for an immediate fill. For more information about special worksheet inputs, please see the Special Worksheet Inputs section. If the limit price cell is set to 0 or is blank, then the Limit price will be set to the current Ask price.

BuyEntry (K), BuyExit (L), SellEntry (M), SellExit (N) Worksheet Order Action Columns

[link]

The following Worksheet Order Action Columns only apply to the Worksheet System for Trading study.

Note: For all of the below Order Actions: When an order is sent, an alert will also be sounded if you selected an Alert Sound for the corresponding worksheet column in the Study Input settings. An alert message will also be added to the Alerts Log.

Buy Entry (column K)

[link]

When your condition formula in row 3 of column K on the Worksheet returns a nonzero value, then a Buy order will be given if certain conditions are met. For complete details refer to the Auto Trade Management page.

Buy Exit (column L)

[link]

When your condition formula in row 3 of column L on the Worksheet returns a nonzero value, then a Sell order will be given if certain conditions are met. For complete details refer to the Auto Trade Management page.

Sell Entry (column M)

[link]

When your condition formula in row 3 of column M on the Worksheet returns a nonzero value, then a Sell order will be given if certain conditions are met. For complete details refer to the Auto Trade Management page.

Sell Exit (column N)

[link]

When your condition formula in row 3 of column N on the Worksheet returns a nonzero value, then a Buy order will be given if certain conditions are met. For complete details refer to the Auto Trade Management page.

[Top]

Ignored Signals When Using Worksheet Systems or Alerts

[link]

There are many cases in which a Buy or Sell signal from the formula columns will be ignored. When a signal is ignored from a formula that returns a nonzero number or TRUE, then there will be no alert sound, alert message, or a trade order given. This section explains all of this.

With the Worksheet System for Trading study, Buy and Sell signals from the formula columns are ignored on historical data. They are monitored for when the chart is actually updating with new data. This would occur during real-time chart updating with live data and during replays. If you want to see the backtest results from historical Buy and Sell signals from the formula columns, then refer to BackTesting section.

When using the Worksheet System for Trading study and a formula returns TRUE in one of the Buy/Sell Entry/Exit cells, a trade signal is not necessarily going to be given. You will see an arrow on the chart indicating a buy or sell signal, assuming the corresponding Subgraph Draw Style is set to a visible style. However, this does not necessarily mean that the formula result will be followed and a trade signal will be given. The BuyEntry (K), BuyExit (L), SellEntry (M), SellExit (N) Worksheet Order Action Columns rules must be met and the additional conditions imposed by the related study Input settings must be met, for a trade signal to be actually given. If one is ignored, then a message will be added to the Trade >> Trade Service Log indicating the reason. Please review that log for the reason. To see on the chart the actual filled orders from trades made, refer to Displaying Filled Orders.

When using either the Worksheet System for Trading or Worksheet System/Alert studies, the TRUE signals given from cells K3 through Z3 may not always be followed and give an alert or trade signal, even though they still return TRUE or a non zero value and an arrow shows on the chart. If a cell is TRUE and there is a new bar and the cell continues to be TRUE, because the state is already TRUE, you will not get a new signal unless the input Reset Condition on New Bar is set to Yes. If a TRUE signal has been given by a cell and then it goes back to FALSE and back to TRUE on the same bar, you will not get a signal if Signal Only Once Per Bar is set to Yes. If you have the Signal Only On Bar Close (Column K,M) input set to Yes, then a TRUE signal will be ignored on cells K3 and M3, and only the cells K4 and M4 will be monitored for signals. Likewise with the Signal Only On Bar Close (Column L,N-Z) input. In all these cases, except for Signal Only Once Per Bar, when a signal is ignored, there is no message added to the Trade Service Log or the Alerts Log.

Another thing to also consider is that you may see an arrow on the chart, but it is on the bar prior to the last bar in the chart and it appeared after a new bar was added to the chart. Therefore, unless the Alert/Signal Only On Bar Close Input is set to Yes, then it's going to be ignored as well.

One way to get an understanding of how the TRUE signals in K3, L3, M3, N3 are processed is to do the following:

  1. Remove the formulas from those worksheet cells.
  2. Start a chart replay. Enter a 1 in a cell to simulate becoming TRUE.
  3. Enter a 0 to clear that state.
  4. Look at the Trade >> Trade Service Log and the Window >> Show/Hide Alerts Log to see the signals and ignored signals.

[Top]

Disappearing Arrows or Signals

[link]

When using either the Worksheet System for Trading study or the Worksheet System/Alert study and it is configured for a system or an alert, then when a formula cell in one of the columns that is graphed on the chart (typically this is going to be K,L,M,N) returns a nonzero or TRUE value, you will see an arrow on the chart representing the TRUE condition. These signals can trigger an Alert Sound as well and also possibly a Trade signal if these signals are given in row 3 or row 4 if you have set the system to evaluate on a bar close. If the formula no longer is returning a nonzero or TRUE value and instead returning FALSE/0, then the arrow will disappear even though the alert and the trade signal was previously given.

In summary, the standard Up/Down arrows on a chart displayed by a Worksheet System study, indicate the present state of the formulas. To actually see where the trades were made, you need to display filled orders.

If you are using Worksheet System for Trading study and you wish to visibly see where trades have been made on the chart in the case of when the Arrows disappear, then what you need to do is make sure there is a checkmark by Trade >> Show Filled Orders. Various options affecting the display of filled orders can be set by selecting Trade >> Global Trade Settings >> Chart Trade Settings. On the Main tab you will see the Filled Orders Settings section with the options.

[Top]

Special Worksheet Outputted Values

[link]

The Worksheet will output special values in the I and J columns for information and calculation purposes. This is in addition to the main price graph and study data that is outputted to other columns. The following is a list of all the special values that are outputted.

Special Worksheet System for Trading Values

These values are outputted for the Worksheet System for Trading study only. These values can be used in formulas. Below is a description of what each Row means.

Working Orders Quantity [Cell J3]

This is the combined quantity for all the working orders for the chart symbol. This is positive for Buy orders and negative for Sell orders. If there is one Buy working order with a quantity of 5, it will be +5. If there is one Buy order and one Sell order, each with the same quantity, it will be 0.

Session Position with Working Orders Quantity [Cell J4]

This is the Session Position quantity combined with the quantity from working orders. Buy and Sell Entries depend on this number to determine whether or not another entry should be allowed or not.

Session Position Quantity [Cell J5]

This is the quantity for the current Session Position for the chart symbol. This value can be either negative (a short quantity) or positive (a long quantity).

Session Position with Working Exit Orders Quantity [Cell J6]

This is the Session Position quantity combined with the quantity from working orders that could potentially exit the current position. Therefore, if the Session Position quantity is +5 and there is a sell order with a quantity of 4, this will be 1.

Session Position Average Price [Cell J9]

This is the average fill prices of the Session Position.

Session Open Profit/Loss [Cell J10]

This is the open profit or loss of the current Session Position.

Session Cumulative Profit/Loss [Cell J11]

This is the cumulative profit or loss of the closed out trades maintained by the Session Position data.

Maximum Open Position Profit [Cell J31]

The maximum open position profit is what the maximum profit is before the position is flattened.

Maximum Open Position Loss [Cell J32]

The maximum open position loss is what the maximum loss is before the position is flattened.

Win Trades [Cell J33]

The total number of winning trades maintained by the session position data. A winning trade is defined as a trade that reduces or closes out a position while the Open Profit/Loss is positive.

Lose Trades [Cell J34]

The total number of losing trades maintained by the session position data. A losing trade is defined as a trade that reduces or closes out a position while the Open Profit/Loss is negative.

Total Trades [Cell J35]

The total number of closing trades maintained by the session position data. This number does not necessarily equal the winning trades plus the losing trades, some trades could have broke even.

Last Bar DateTime [Cell J41]

This cell displays the latest date and time of the last bar in the chart. This number can be used for various comparisons within formulas that are based on times. If you want to see what date and time this number represents, you can right click on the cell and select Number Format from the menu. Change the Number Format to the Date format of m/d/yyyy h:mm:ss.

Last Entry Trade DateTime [Cell J42]

This cell displays a number that represents the Date and Time of the bar at which the last Entry trade was triggered and processed. This number can be used for various comparisons within formulas that are based on times. If you want to see what date and time this number represents, you can right click on the cell and select Number Format from the menu. Change the Number Format to the Date format of m/d/yyyy h:mm:ss.

Last Exit Trade DateTime [Cell J43]

This cell displays a number that represents the Date and Time of the bar at which the last Exit trade was triggered and processed. This number can be used for various comparisons within formulas that are based on times. If you want to see what date and time this number represents, you can right click on the cell and select Number Format from the menu. Change the Number Format to the Date format of m/d/yyyy h:mm:ss.

Current Quote Values

Cells J12 through J21 display the Current Quote Values for the chart symbol. These are always displayed on the Worksheet System for Trading study worksheet, and will be displayed if the Output Current Quote Data setting is Yes for the .Worksheet Study/System/Alert study.

[Top]

Special Worksheet Inputs

[link]

Special Inputs for the Worksheet System for Trading study

The following is a list of special Worksheet inputs in columns I and J used by the Worksheet System for Trading study.

Limit Prices

[link]

Cells J22 through J25 allow you input the limit prices for each of the 4 different Worksheet Column Signal Types. If any of the Worksheet Column Signal Types are set to use Limit orders through the Inputs for the Worksheet System for Trading Study, the corresponding limit prices are set through these cells.

You can set these to either the Bid or Ask prices by using these formulas: =J12 (Bid price) or =J14 (Ask Price). This allows you to buy at the Bid or sell at the Ask respectively.

Custom Order Quantity

[link]

Cell J26 allows you input a custom order quantity for the orders that are placed through the Worksheet System for Trading study. Usually the Order Quantity is set through the Input settings for the Worksheet System for Trading study with the Default Order Quantity input. However, sometimes you may want to configure the order quantity through a formula or temporarily set it to another value. If the cell is empty, zero or has a negative value, the order quantity is simply the one specified through the Inputs.

Cancel Working Orders

[link]

Cell J27 allows you to cancel all working orders for the symbol that the Worksheet System for Trading study is currently applied to. Simply setting this cell to TRUE or any non-zero number (such as 1) will trigger the canceling of orders. You can also input a formula here to cancel all working orders when certain conditions are met.

The canceling of orders occurs before any of the Entry and Exit signal cells are evaluated. When this cell is set to a nonzero or TRUE value, additional cancellations will not occur until it is set back to zero or FALSE and then back to a nonzero or TRUE value.

When Disable Auto Trading (J28), is set to TRUE, then it is still possible to use this cell to cancel working orders.

If you wish to cancel working orders at a specific time, then you could use a formula like this =(NOW()-INT(NOW())) > ((16*60+15)/(24*60)). This example would return true when the time is 16:15 or greater (16*60+15).

Flatten Position and Cancel Working Orders

[link]

Cell J29 allows you to flatten your position and cancel all working orders for the symbol that the Worksheet System for Trading study is currently applied to. Simply setting this cell to TRUE or any non-zero number (such as 1) will trigger the flattening of the position and canceling of orders. You can also input a formula here to perform the action when certain conditions are met.

The flattening of the position and canceling of orders occurs before any of the Entry and Exit signal cells are evaluated. When this cell is set to a nonzero or TRUE value, additional flattening of the position and canceling of orders will not occur until it is set back to zero or FALSE and then back to a nonzero or TRUE value.

When Disable Auto Trading (J28), is set to TRUE, then it is still possible to use this cell to flatten the position and cancel working orders.

If you wish to flatten your position and cancel working orders at a specific time, then you could use a formula like this =(NOW()-INT(NOW())) > ((16*60+15)/(24*60)). This example would return true when the time is 16:15 or greater (16*60+15).

Disable Auto Trading

[link]

Cell J28 allows you to disable all trading from the Worksheet. When this cell is set to TRUE, all Worksheet triggered Entries and Exits will all be ignored. You can input a formula here to Disable Trading when certain conditions are met or even when the current time is between some specified times. Manually setting this input to TRUE or any non-zero number will simply disable all Entries and Exits until you have set the cell back to FALSE.

If you wish to disable automated trading at a specific time, then you could use a formula like this =(NOW()-INT(NOW())) > ((16*60+15)/(24*60)). This example would return true when the time is 16:15 or greater (16*60+15).

[Top]

Calculation Order

[link]

When your formula results are evaluated for order or alert signals, column K is evaluated first, then L, then M, and so on through and including column Z.

[Top]

Back Testing

[link]

If you are using the Worksheet System for Trading study or you have created an Advanced Custom System Study that uses the ACSIL Trading functions, then you can perform back testing by using Trade Simulation Mode and replaying the chart. In order to see how your system has performed historically and to see the historical trades for past data, it is necessary to run a back-test. For more information see Trade Simulation Mode and Replaying Charts. It simply is a matter of doing the following:

  1. Make sure there is a check mark by Trade >> Trade Simulation Mode On if there isn't one already.
  2. Reset the Session Position data for the symbol by selecting Trade >> Positions Window >> Session And Simulated Trade Positions. Select the line for the symbol in the Position list if it exists and press the Clear Selected Session Position Data button. You can also quickly clear and reset all of the Trade Simulation orders and data by selecting Trade >> Clear Trade Simulation Orders & Data.
  3. Replay the chart from the point where you want to begin your backtest. Select Chart >> Replay Chart to display the replay window. On the Replay Window be sure to select the Backtest Accuracy: Medium or Backtest Accuracy: High option for the most accurate backtest. For more information, see Backtest Accuracy. Scroll to the point in the chart where you want to begin the replay and press the Play button.
  4. You can view the detailed results of your trading system under Trade >> Trade Activity Log on the menu. Select the Fill tab for the detailed results.
  5. To view your filled orders visually on the chart enable Trade >> Show Filled Orders.
  6. If you want to run another back test, you can quickly clear and reset all of the Trade Simulation orders and data by selecting Trade >> Clear Trade Simulation Orders & Data.
  7. Easy One Step Back Testing: Select Chart >> Chart Settings and set the Days to Load the number of days you wish to Backtest over. Press OK. Once this is set it does not have to be changed again. Select Trade >> Perform Auto Trade System Backtest on the menu. This command clears all simulated trade data for the chart's symbol and replays the entire chart from the beginning at a high speed. You can see the results of your Backtest as explained above. Note: When using the Worksheet System for Trading study, there is one additional step to perform after selecting this command. Scroll the chart to where you want to begin the Backtest and press the Play button on the Replay window.

Notes about Real-Time and Back Testing Consistency and Accuracy

The BuyEntry, BuyExit, SellEntry, SellExit Order Actions are only going to be evaluated for trade signals when the Worksheet Study is calculated. This happens at the Chart Update Interval set under Global Settings >> General Settings. However, when the Chart Update Interval elapses, the Worksheet Study will only be calculated when there is data being received from the data feed, there is data to be read from the chart data file during a replay, or there is new/updated trade order or position data. During a replay with the Backtest accuracy set to Medium, High or Very High, the study is calculated more often than the Chart Update Interval. During real-time updating, between the times the study is calculated, there potentially may be more than one data record added to the chart. This is especially true with tick by tick data. Therefore, the study is not calculated at every single tick. If you want your Order Actions evaluated as fast as possible, then you may want to decrease the Chart Update Interval. The Chart Update Interval is internally decreased for the chart when an accelerated replay is occurring.

In the case of the Worksheet System for Trading study, if you wish to evaluate your condition formulas only on a bar close, then set the corresponding Signal Only on a Bar Close inputs with the Worksheet System for Trading study to Yes. This will give your system greater stability and not be affected by the Chart Update Interval. In the case of an ACSIL trading system, you will want to use sc.GetBarHasClosedStatus().

During a replay, for the most accurate fill prices and system results, you need to set the Backtest Accuracy setting on the Replay window to either High or Very High.

[Top]

How Formula Columns are Evaluated

This section explains how the K through Z formula columns that contain formula results are evaluated based on the Signal Only on Bar Close inputs. There are two of these inputs and this section applies to both of them. For example, if you use the Signal Only on Bar Close input for columns K and M, then this description would apply to columns K and M.

If Signal Only on Bar Close is set to NO, then the following occurs:

If there are no new bars added to the chart during a chart update, then only row 3 is checked for TRUE/FALSE values that are returned by formulas in the Worksheet System/Alert signal columns (K-Z).

If there are new bars added to the chart during a chart update, then the first row that is looked at for TRUE/FALSE values that are returned by formulas, is the previous topmost row during the prior update. Next, the evaluation function moves up the rows all the way up to 3, evaluating for TRUE/FALSE conditions row by row. So if there is one new bar added, then first row 4 is looked at and then row 3 is looked at. One potential issue to be aware of with this processing logic is that if a formula is TRUE at row 3 and then a new bar is added and it becomes FALSE at row 4, then the internal state for the System/Alert column goes to FALSE and can then go back to TRUE if the formula is TRUE at row 3 during that update. The potential issue with this is an unexpected signal due to a state change.

If Signal Only on Bar Close is set to YES, then the following occurs:

What occurs is exactly as above except the row numbers are incremented by one. So row 3 becomes row 4 and row 4 becomes row 5.

[Top]

Examples

[link]

The following are example formulas you can use in the formula columns on a worksheet for the Worksheet Study/System/Alert Study. The worksheet should have the Study or System/Alert input set to 2 so that these formulas will show buy and sell arrows on the chart.

When entering numbers and formulas, keep in mind to use the proper delimiters in your numbers and formulas, according to your Regional Setting. For more information, see the Regional Setting section.

  1. Add a stochastic study to the chart. Enter the formula: =AND(AA3 > AB3,AA4 < AB4) in cell k3 on the worksheet (the worksheet number where you enter the formula is the one the chart data is on and is in the workbook you specified in the Worksheet System study added to the chart) and enter the formula: =AND(AA3< AB3,AA4> AB4) in cell M3 on the worksheet. When the %K line crosses the %D line either a Long Entry or Short Entry signal will be given indicating a cross over. The cell references AA and AB may need to be modified if the stochastic is not shown in those columns. It might be in a different column if there are other studies on the chart and they are listed before it. Or use the formulas: =AND(AA3 > AB3,AA4 < AB4,AA3 < 30) , =AND(AA3 < AB3,AA4 > AB4,AA3 > 70). When the %K line crosses the %D line AND %K is below 30 or above 70, then an alert will be given. Open or go to a chart. Select Analysis >> Stochastic Crossover System on the menu for an example of these formulas.

    The Stochastic Crossover System is an example of what you can do with the Worksheet System study. It is not intended to be used for actual trading. It provides no separate exit signals. The long and short entry signals also indicate an exit from a short or long position. It is normal for the study to give a signal on the last bar in the chart and for that signal to disappear if there is no longer a crossover in the last column of the stochastic study.
  2. Enter the formula: =E3 > 50 in cell M3 on the worksheet. This formula means if the close is greater than 50, then give a sell signal.
  3. To scan for charts with increasing volume: Add a Moving Average Study to the chart. Set the Input Data input to volume. Set the Chart Region to 2 for that study. This study should be the only study on the chart. Enter the formula: =AA3 > AA8 into cell K3. This formula means if the moving average value on the current column is greater than the moving average value five columns prior, then give an alert. Select Chart >> Start Scan on the menu to scan your charts for this condition.

Multiple Exit Automated Trading Example

[link]

This is an example of an automated trading exit which utilizes two targets. This example applies to the Worksheet System for Trading study. A stop is not used in this example. There are three formulas involved in this strategy and they are used in the following cells: Buy Exit, Sell Exit, and Custom Order Quantity.

In this example, these formulas set a price target of 3 points and 5 points away from the Position Average Price (Cell J9). It is assumed that there is a position quantity of 5. For the first target, two contracts will be exited. For the second target, three contracts will be exited.

Custom Order Quantity Formula (Cell J26): =IF(ABS(J8)=5,2,IF(ABS(J8)=3,3,0)).

Buy Exit Formula (Cell L3): =OR(AND($J$8=5,$E$3-$J$9>3),AND($J$8 = 3,$E$3 - $J$9 > 5))

Sell Exit Formula (Cell N3): =OR(AND($J$8=-5,$J$9-$E$3 >3),AND($J$8 = -3,$J$9-$E$3 > 5))

This is merely a simple example and this will need to be tested and modified to be certain it will work for you.

[Top]