Spreadsheet Systems, Alerts and Automated Trading

Long Entry Column

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

Although any value other than 0 is considered a TRUE condition, the position of the Up Arrow is handled as a special case when the cell has a value of 1. Any other value will draw the Up Arrow at the value given in the cell. It is recommended to only use 0 and 1 for cells in this column.

Long Exit Column

The L column is the Long Exit column. If a cell in this column is TRUE (1), then a Left Arrow (depends upon the Subgraph Draw Style) will be drawn on the chart at the high value of the corresponding bar to indicate a Long Exit for that bar.

Although any value other than 0 is considered a TRUE condition, the position of the Left Arrow is handled as a special case when the cell has a value of 1. Any other value will draw the Left Arrow at the value given in the cell. It is recommended to only use 0 and 1 for cells in this column.

Short Entry Column

The M column is the Short Entry column . If a cell in this column is TRUE (1), then a Down Arrow (depends upon the Subgraph Draw Style) will be drawn on the chart at the high value of the corresponding bar to indicate a Short Entry.

Although any value other than 0 is considered a TRUE condition, the position of the Down Arrow is handled as a special case when the cell has a value of 1. Any other value will draw the Down Arrow at the value given in the cell. It is recommended to only use 0 and 1 for cells in this column.

Short Exit Column

The N column is the Short Exit column. If a cell in this column is TRUE (1), then an Right Arrow (depends upon the Subgraph Draw Style) will be drawn on the chart at the low value of the bar to indicate a Short Exit.

Although any value other than 0 is considered a TRUE condition, the position of the Right Arrow is handled as a special case when the cell has a value of 1. Any other value will draw the Right Arrow at the value given in the cell. It is recommended to only use 0 and 1 for cells in this column.

Other Columns

When graphing columns other than K, L, M, or N with a visible Draw Style, the return value of the Condition Formula must be within the range of values of the main price graph your study is overlaid on. Otherwise, the subgraph display will adversely affect the display of the chart.

There is no automatic translation from TRUE (1) or FALSE (0) to high and low bar values, from the formula columns O and higher.

An example formula that you can use for formula columns O and higher is =IF(ID0.SG4@3 > 100, C3, 0).

Changing Column Names

The above are the default System/Alert formula column names. You can rename the columns at row 2 in the Sheet and use them for any kind of condition. To rename a column, click two times on the cell with the name in row 2 and type a new name.

Controlling the Location of Arrows or Subgraph Values

You can also return values other than TRUE (1) or FALSE (0) in your System/Alert Condition Formulas. This can be done by using the IF function. For example: =IF([formula expression], D3, 0). This will return the low value of the chart bar. =IF([formula expression], C3 ,0). This will return the high value of the chart bar.

If the value returned by the formula is not TRUE (1) or FALSE (0), then the arrow will be drawn at the value the formula returns when using columns K, L, M, or N when using the Spreadsheet System/Alert study.

For formula columns O and higher, the Subgraph arrows or whatever the Subgraph Draw Style is, will always be drawn at the value returned from the formula.

Steps To Create a Trading System for Automated Trading

1. First make certain that auto trading is enabled. Make certain there is a checkmark by Trade >> Auto Trading Enabled - Global and Trade >> Auto Trading Enabled - Chart on the menu.
2. 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 Spreadsheet System for Trading study must be set to No . This also prevents the orders from being sent to the connected Trading service. With these two settings, there are two layers of protection against sending actual orders.
   
   For complete details, refer to SendOrdersToTradeService.
3. Go to an open chart or open a chart with File >> Find Symbol.
4. Enable Trade >> Chart Trade Mode . Select Trade >> Attach Trade Window to Chart. When chart trading mode is activated on a chart, you will see the Orders and the Position from your automated trading system displayed on the chart. By attaching the Trade Window to the chart, you will have a complete trading control panel available to take any manual actions that you require and to set Attached Orders. More details can be found in the Chart Trading documentation.
5. Select Analysis >> Studies on the menu.
6. Select Spreadsheet System for Trading in the Studies Available list on the left. Do not confuse this study with the Spreadsheet Study or the Spreadsheet System/Alert studies.
7. Press the Add button to add the Spreadsheet System for Trading study to the Studies to Graph list.
8. For this example, also add the study named Moving Average-Simple. You do not need to change the settings for this study, the defaults are acceptable. 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. This is not necessarily a practical trading system for actual use.
   
   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.
9. Highlight the Spreadsheet 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 Spreadsheet Name (lower left). Enter a name in the text box without a file extension. This is the name of the Spreadsheet that will be opened or created to contain the data and formulas. The name should be related to what your custom system will do.
10. To have the orders sent to the Sierra Chart internal Trade Simulation system, the Send Orders To Trade Service Input needs to be set to No. To send order signals to the connected Trading service, the Send Orders To Trade Service Input needs to be set to Yes and you also need to disable Trade >> Trade Simulation Mode On on the menu. For complete details, refer to SendOrdersToTradeService.
11. In the study Inputs list, you will find an Input named Maximum Position Allowed . Set this Input as you require. It is the maximum Position in shares or contracts that is allowed. For complete details, refer to Maximum Position Allowed.
12. The other inputs are correct as they are for this example. For more information about all of the inputs, refer to Inputs That Are Specific to Systems, Alerts, and Automated Trading.
13. In the list of available study Inputs you will see inputs named Column K-Z Alert. Set these to the Alert Sound Number you want to hear when your System/Alert Condition Formula in the corresponding column becomes TRUE at the last bar. For more information, refer to Sound for Audible Alert and E-Mail Message Notifications.
14. Press the OK button on the Technical Study Settings window.
15. Press the OK button on the Chart Studies window.
16. The Spreadsheet will be opened in the background. Go to it by selecting CW on the menu. The name of the spreadsheet will be listed and can be selected. The name will be what you have specified in the Study Settings window for the study.
17. A Spreadsheet contains multiple Sheets. The Sheet number the data is outputted to, is the same Sheet number as the chart number. For example, chart #2 will be outputted to Sheet2 in the Spreadsheet.
    
    The Sheet can be selected with the list box at the top left of the Spreadsheet. All of the individual chart studies are also outputted to the same Sheet beginning at column AA (the default when using 16 formula columns).
18. Select the Sheet# from the list box at the top left of the Spreadsheet window that corresponds to the Chart Number that contains the Spreadsheet System for Trading study.
19. Set cell J26 (Order Quantity) to the number of shares or contracts you want to trade when an order is submitted. If this order quantity cell is set to 0, then in the case of a Buy Entry or Sell Entry, it will be interpreted as a quantity of 1.
20. All new orders use the settings of the Trade Window for the chart.
    
    Go to the chart that contains the Spreadsheet System for Trading study. Select Trade >> Attach Trade Window to Chart to see what the settings are set to.
    
    For example, if the Time in Force is set to Day, then all new orders submitted from the Spreadsheet will automatically have a Time in Force of Day.
    
    The settings that you can set in the Trade Window also include Attached Orders. For complete instructions for Attached Orders, refer to the Attached Orders page.
21. On the attached Trade Window, set the Trade Account to the trading account that you want to use.
22. The following steps provide an example of a trading system using Spreadsheet formulas. Buy Entry and Sell Entry formulas are given.
23. To Buy when the Last price crosses from below the moving average, type in cell K3 (labeled: Buy Entry):
    
    =CROSSFROMBELOW(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4)
    
    Where ID0.SG4@3:ID0.SG4@4 refers to the current and previous last trade prices of the chart bars and ID2.SG1@3:ID2.SG1@4 refers to the current and previous Moving Average - Simple study values which are located in column AA . In all places in the example formulas on this page, ID2 may need to be changed to the actual ID# number used by the Moving Average study. You can see the actual ID# number in row 1 in the column that the study is outputted to. Refer to the image below:
    
    
    
    
    You can also optionally use: =AND(CROSSFROMBELOW(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4), $J$8=0)
    
    $J$8 is the current Trade Position quantity. This can be used to check the Position is flat before entering the market. The underlying automated trading logic checks this, but it can be checked in your formula to avoid unnecessary messages to the Trade >> Trade Service Log. The disadvantage is that you cannot see on the chart your historical buy and sell signal arrows without running a back test.
24. For an understanding of references like ID2.SG4@3, refer to References to Study Subgraph Columns when using the Spreadsheet Study.
25. To Sell when the Last price crosses from above the moving average, type in cell M3 (labeled: Sell Entry):
    
    =CROSSFROMABOVE(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4)
    
    Where ID0.SG4@3:ID0.SG4@4 refers to the current and previous last trade prices of the chart bars and ID2.SG1@3:ID2.SG1@4 refers to the current and previous Moving Average - Simple values which are located in column AA.
    
    You can also optionally use: =AND(CROSSFROMBELOW( ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4), $J$8=0)
    
    $J$8 is the current Trade Position quantity. This can be used to check the Position is flat before entering the market. The underlying automated trading logic checks this, but it can be checked in your formula to avoid unnecessary messages to the Trade >> Trade Service Log. The disadvantage is that you cannot see on the chart your historical buy and sell signal arrows without running a back test.
26. Using these formulas, when the Last price crosses from below the moving average, K3 (Buy Entry) will trigger a Buy order, and when 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, if there has already been a Buy Entry, then all other Buy Entry signals are ignored until the Buy Exit formula becomes TRUE and triggers a Sell or the Position is flattened by another method.
    
    If there has already been a Sell Entry, then all other Sell Entry signals are ignored until the Sell Exit formula becomes TRUE and triggers a Buy or the Short Position is flattened by another method.
    
    This behavior is controlled with the Allow Multiple Entries In Same Direction Input. If the Allow Opposite Entry with Opposing Position or Orders Input is set to Yes, which is the default, then if there currently is a Long Position, then a Sell Entry can exit that Position. The same with a Short Position and Buy Entry.
    
    If you have multiple conditions for your trading system, then you will need to use the AND() function. For example, if you have a RSI (Relative Strength Index) study on the chart and the data is outputted to column AC on the Sheet, and you want to combine that with the Moving Average study, use a formula similar to this for the Buy Entry: =AND(CROSSFROMBELOW(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4), ID3.SG1@3 < 40). ID3.SG1@3 references the RSI study assuming it has a study ID of 3.
    
    For further information, refer to References to Study Subgraph Columns when using the Spreadsheet Study.
27. In this example we will create a Buy Exit (Sell) that exits a Position when a Stop or Target value 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 closed out manually or with an Attached Order. In this example, we are using a Buy Exit formula to show you an exit strategy managed completely by the Spreadsheet System for Trading study.
    
    In cell L3 (labeled: Buy Exit), type =OR(AND($J$8 > 0, ID0.SG4@$3 <= $J$9-1), AND($J$8 > 0, ID0.SG4@$3 >= $J$9+2)). ID0.SG4@$3 refers to the Last price of the most recent chart bar, $J$8 refers to the current Position Quantity, and $J$9 refers to the current 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 in your specific case.
    
    Note: It is essential that the formula contains a check that there is a Position > 0. This is explained in more detail in the Buy Exit section.
    
    The $ means a cell reference refers to an absolute cell and the column and row will not be adjusted when the formula is automatically filled down. This formula is relevant only to the topmost row in the Spreadsheet (corresponds to the last bar in the chart), even though it is applied to historical bars as well. Refer to the Special Spreadsheet 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 the numbers and formulas, according to the Global Settings >> Spreadsheet Settings >> Decimal and Function Delimiters setting. For more information, refer to the Decimal and Function Delimiters section.
28. 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 closed out manually or with an Attached Order. In this example, we are using a Sell Exit to show you an exit strategy managed completely by the Spreadsheet System for Trading study.
    
    In cell N3 (labeled: Sell Exit), type =OR(AND($J$8 < 0, ID0.SG4@$3 >= $J$9+1), AND($J$8 < 0, ID0.SG4@$3 <= $J$9-2)). ID0.SG4@$3 refers to the Last price of the most recent chart bar, $J$8 refers to the Position Quantity, and $J$9 refers to the Position Average Price.
    
    When this formula in cell N3 returns TRUE, it will send 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 in your specific case.
    
    Note: It is essential that the formula contains a check that there is a Position < 0. This is explained in more detail in the Sell Exit section.
    
    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 Spreadsheet (corresponds to the last bar in the chart), even though it is applied to historical bars as well. Refer to the Special Spreadsheet Outputted Values section for more information on the data in the J column.
29. Note: In the case of actual live trading, these Stops and Targets are sent by Sierra Chart to the connected trading service 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.
30. In order for an order to be placed as fast as possible, it is a good idea to make sure the Chart Update Interval, which is set through Global Settings >> General Settings on the menu, is set to 800 milliseconds or less. However, avoid making it very small unless your computer is very fast.
    
    This setting can be changed for an individual chart instead of globally, refer to Chart Update Interval.
31. You can also use Attached Orders for exiting a Position. Refer to Initiating Trades with Auto-Trading and Using Manual or Attached Orders Exits.
32. Now that we have entered the Spreadsheet formulas on the Spreadsheet, it is necessary to go back to the chart. Select CW on the menu and select the chart that you applied the Spreadsheet System for Trading study to, or select it from the chart window tabs which are located at either the top or bottom of the main window.
    
    To ensure the formulas that have been entered are fully copied down the formula columns (K through Z) on the Spreadsheet, select Chart >> Recalculate on the menu.
    
    Selecting this command is not necessary if Global Settings >> Spreadsheet Settings >> Recalculate the Chart when a Formula Column is Changed is enabled.
33. Refer to 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 the formula columns. By default the Subgraph Draw Style for the Exit columns (L and N) are set to Hidden, so they do not appear.
    
    Understand that these do not represent actual submitted orders or order fills. There is an important difference. Refer to Buy/Sell Arrows Do Not Mean Trade Signals for more information.
34. In this example, we use exits which are based upon Trade Position data which is only known for the current Trade Position, so the Buy/Sell arrows are not relevant historically. If the exit formulas were based upon the price and/or study data, then you could set the corresponding Exit columns to a visible Draw Style.
35. Make certain there is a check mark by Trade >> Auto Trading Enabled - Global and Trade >> Auto Trading Enabled - Chart so the TRUE results that your Condition Formulas provide, will be able to submit Trade Orders and not be ignored.
36. As the chart is updated, either with real-time streaming data received from the connected data feed, or during a chart replay, the Buy Entry, Buy Exit, Sell Entry, Sell Exit formulas at row 3 when they become TRUE/1 will cause Trade Orders to be submitted, either in Trade Simulation Mode or to the connected Trading service.
    
    These signals also produce Alert Messages and these can be viewed by selecting Window >> Alerts Manager >> Alerts Log on the menu.
37. There are various conditions which can cause the TRUE/1 formula results to become ignored and not result in Trade Orders from being submitted. For complete details, refer to Ignored Signals With Spreadsheet Systems or Alerts. This is an essential section to read over.
38. It is important to understand that Buy/Sell Arrows Do Not Mean Trade Signals
39. To view the submitted Trade Orders, select Trade >> Trade Activity Log >> Trade Activity on the menu. For detailed documentation, refer to Viewing Historical Trade Activity Log.
40. To view the Trade Service Log, select Trade >> Trade Service Log on the menu. The Trade Service Log displays ignored signals from your Condition Formulas.
41. The next step is to simply watch your system perform in real-time as your chart is updated. Or perform Back Testing. Back Testing is a very good way to test your trading system on historical data and get a good understanding of it.
    
    There is no other way to test the trading system on historical chart data other than through Back Testing or a chart replay. The buy and sell arrows you see on the chart do not actually represent submitted Trade Orders. Order Actions will only happen through a Back Test, during a chart replay or during real-time updating of the chart.
42. To go from simulated trading to non-simulated (Live) trading, follow the instructions in Going From Simulated Trading to Non-Simulated (Live) Trading.

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

It is possible to define a set of Attached Orders to use with your Spreadsheet System for Trading study and also 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.

Trade Window Order Quantity with Attached Orders

For more information about setting the Order Quantity for the Spreadsheet System for Trading study, refer to Cell J26 (Order Quantity).

Reset 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 Spreadsheet 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 Reset Condition On New Bar is set to Yes.

Setting Reset Condition On New Bar to Yes does not in any way change the values returned from the formulas in the Buy/Sell Entry and Exit columns. It only affects the interpretation of the TRUE values returned.

Allow Multiple Entries In Same Direction

This Input applies only to the Spreadsheet System for Trading study.

Refer to the description on the Auto Trade Management page.

Alert/Signal Only Once per Bar

If the Alert/Signal Only Once Per Bar option is set to Yes, then an alert or trading signal will be given only once per Formula Column per bar in the chart.

When an alert or trading is given once for a specific Spreadsheet Formula Column, then another will not be given until there is a new bar in the chart and the System/Alert Condition Formula goes back to FALSE and then to TRUE.

This applies to each formula column independently. For example, in the case of the Spreadsheet System for Trading study, if there is a Buy Entry at a particular bar, there can still be a Buy Exit given at the same bar. However, there cannot be 2 Buy Entries on the same bar when this option is enabled.

It is recommended that if you have any trouble obtaining a trading signal when you normally would expect one, that you try setting this option to No.

In the case of the Spreadsheet System for Trading study, when a signal is ignored because of this option, that ignored signal will be logged in the Trade >> Trade Service Log. This is very helpful for troubleshooting purposes. The message is: Signal ignored because 'Signal Only Once Per Bar' is enabled and a signal was already given from formula column for bar.

Cancel All Orders on Entry and Reversals

Refer to the description on the Auto Trade Management page.

Cancel All Orders On Reversals

Refer to the description on the Auto Trade Management page.

Cancel All Orders On Entries

Refer to the description on the Auto Trade Management page.

Allow Entry With Working Orders

Refer to the description on the Auto Trade Management page.

Signal Only on Bar Close

The Signal Only on Bar Close Inputs are specific to the Spreadsheet System for Trading study and the Spreadsheet System/Alert study. There are two versions of this Input, one for Sheet columns K and M and the other for columns L, N-Z.

When this Input is set to Yes, Sheet formulas that return a non-zero (TRUE) value at row 3 in the corresponding formula columns are ignored until the last bar closes. The way this is accomplished is row 4 is monitored instead of row 3.

When this Input is set to Yes, then anywhere in the documentation row 3 is mentioned, it is row 4 instead. In the same way that row 3 is continuously monitored for TRUE results from formulas in the corresponding formula columns, row 4 will be continuously monitored when this Input is set to Yes.

You may want to set Strict Signal Only On Bar Close Evaluation to Yes, when you set one of these inputs to Yes.

There is one scenario that you should keep in mind about when using Signal Only on Bar Close. Consider you have a formula and one of the Sheet formula columns and it returns TRUE at row 3 (the last bar of the chart) in the column. You refer to the arrow on the chart. A new bar is added, the bar therefore is considered closed, the formula moves down to row 4, it is 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.

Signal Only on Bar Close is completely separate from and does not conflict in any way with the Evaluate on Bar Close Alert Option. Evaluate on Bar Close is ignored by the Spreadsheet System/Alert and the Spreadsheet System for Trading studies.

Maximum Position Allowed

This Input applies only to the Spreadsheet System for Trading study.

Refer to the description on the Auto Trade Management page.

Send Orders To Trade Service

Refer to the description on the Auto Trade Management page.

This Input applies only to the specific instance of the Spreadsheet System for Trading that it is set on. It has no effect on manually entered orders on the chart.

It also only applies to the specific Spreadsheet being used by the Spreadsheet System for Trading study.

Support Reversals

Refer to the description on the Auto Trade Management page.

Allow Opposite Entry with Opposing Position or Orders

This Input applies only to the Spreadsheet System for Trading study.

Refer to the description on the Auto Trade Management page.

Cancel All Working Orders On Exit

This Input applies only to the Spreadsheet System for Trading study.

Refer to the description on the Auto Trade Management page.

Column [K, L, M, N, O, P, Q, R, S, T, U, V-Z] Alert

These Inputs only apply to the Spreadsheet System/Alert and the Spreadsheet System for Trading studies.

These Inputs set the Alert Number for a specific Sheet column. Alert Numbers are supported up to the first 16 formula columns K through Z.

The Alert Number is specified for each individual Sheet column independently. Although for columns V through Z, they share the same Alert Number. You can select among 150 different Alert Numbers.

These Alert Numbers can then be configured to play specific sound files, and/or perform other actions. Refer to Alert Sound Settings in the General Settings documentation.

The possible choices are:

• Alert Disabled: The setting means that the Alert functionality for the Sheet formula column is completely disabled and there will be no alert sound, and no message added to the Window >> Alert Manager >> Log.
• No Alert Sound: This setting means that an alert sound will not be played, but there will still be a message added to the Window >> Alert Manager >> Log when the formula at row 3 in the formula column becomes TRUE.
• Alert 1-150: Using one of these settings means that an alert sound will be played, and there will be a message added to the Window >> Alert Manager >> Log when the formula at row 3 in the formula column becomes TRUE. Additional configuration can be done for the selected Alert Number. Refer to Alert Sound Settings.

Note: The Alert Number setting in the Simple Alert settings in the Study Settings window is not relevant when using the Spreadsheet System/Alert and the Spreadsheet System for Trading studies.

If the Alert Sound does not play when expected, then refer to the Troubleshooting for Alert Sounds That Do Not Play section about this.

Use Chase Orders

This Input only applies to the Spreadsheet System for Trading study. When this Input is set to Yes and you have enabled limit orders for a particular Buy/Sell column, then the limit order will be submitted as a Limit Chase Order. The chase amount is set through the J46 cell labelled Maximum Chase Amount for Limit Chase Orders.

Strict Signal Only On Bar Close Evaluation

The Strict Signal Only On Bar Close Evaluation Input applies a stricter method of evaluation when you have either of the Signal Only on Bar Close Inputs set to Yes.

When this Input is set to Yes, then only at the moment when a bar closes when one or more new bars are added to the chart, will an evaluation of your Spreadsheet formulas from the corresponding Spreadsheet columns (K, M, L, N-Z) be evaluated for TRUE conditions.

When this Input is set to Yes, row 4 will not be continuously monitored as usual, only exactly at the moment when a bar closes and the chart is updated.

Enhanced Auto Trade Signal Related Logging

When the Enhanced Auto Trade Signal Related Logging Input is set to Yes, this will add additional messages to the Trade >> Trade Service Log indicating the reason why an order signal is ignored from formula columns K, L, M, N at rows 3 and 4 when using the Spreadsheet System for Trading study.

This is helpful to troubleshoot problems with ignored signals. This should provide a definitive indication as to why signal is getting ignored.

Set this Input to Yes only for a short period of time and only when necessary because it generates a large amount of messages and can create a very heavy and unnecessary CPU load when doing back testing to the point where a tick by tick back test becomes next to impossible to complete and extremely burdensome.

Copy J Column from Formula Source Sheet

When this Input is set to Yes, then this will cause the following rows from column J from the Formula Source Sheet to be copied to the Sheet used by the Spreadsheet System for Trading study.

This includes the cells that support writing or entry by the user. If they are read-only, they are not copied from the Formula Source Sheet.

• Limit or Stop Price - Buy Entry (K)
• Limit or Stop Price - Buy Exit (L)
• Limit or Stop Price - Sell Entry (M)
• Limit or Stop Price - Sell Exit (N)
• Order Quantity
• Cancel Working Orders
• Disable Auto Trading
• Flatten and Cancel Working Orders
• Maximum Chase Amount for Limit Chase Orders
• Buy Entry: Order Type. M=Market,L=Limit,S=Stop,SL=Stop-Limit,MIT=MarketIfTouched
• Buy Exit: Order Type. M=Market,L=Limit,S=Stop,SL=Stop-Limit,MIT=MarketIfTouched
• Sell Entry: Order Type. M=Market,L=Limit,S=Stop,SL=Stop-Limit,MIT=MarketIfTouched
• Sell Exit: Order Type. M=Market,L=Limit,S=Stop,SL=Stop-Limit,MIT=MarketIfTouched
• Cancel All Orders except working Attached Orders
• Attached Order Target Offset
• Attached Order Stop Offset
• Use Separate Exit Order Quantity
• Exit Order Quantity
• Allow Trading Only During Time Range
• Start Time For Allowed Time Range
• End Time For Allowed Time Range
• Flatten Positions And Cancel Orders At Set Time
• Time for Flatten Positions and Cancel Orders
• Trade Window Configuration File

Minimum Chart Bars Required for Entry Exit Cell Evaluation

This Input when set to Yes, will prevent evaluating the Buy/Sell Entry/Exit cells for order signals until the specified number of bars are loaded in the chart.

Support Order Text Tag Cell

When this Spreadsheet System for Trading study Input is set to Yes, then the Order Text Tag cell in the I/J columns will set the Text Tag on the Trade Window associated with the chart the Spreadsheet System for Trading study is on. That cell can contain a formula to programmatically display any text that you want.

This feature is useful to add additional text with an order that is displayed in the Trade Activity Log.

Buy Entry (column K)

When the Condition Formula in row 3 of column K on the Spreadsheet returns a nonzero value or TRUE, then a Buy order will be given if certain conditions are met. For complete details about these conditions, refer to the Auto Trade Management page.

The actual order is not submitted until the cell becomes nonzero or TRUE assuming the conditions for sending the order have been met.

Buy Exit (column L)

When the Condition Formula in row 3 of column L on the Spreadsheet returns a nonzero value or TRUE, then a Sell order will be given if certain conditions are met. For complete details about these conditions, refer to the Auto Trade Management page.

You need to code into your Exit formulas by using the AND() Spreadsheet function, a check that there is a Trade Position Quantity. An example would be: =OR(AND($J$8 > 0, ID0.SG4@$3 <= $J$9-1), AND($J$8 > 0, ID0.SG4@$3 >= $J$9+2)).

The reference to cell J8 refers to the current Trade Position Quantity. The dollar signs in $J$8 mean it is an absolute Spreadsheet reference.

In the case of a Buy Exit formula, you must to check to make certain there is a long Trade Position by making certain that J8 is > 0. The purpose of this is so that the formula remains FALSE until such time that a Trade Position can be exited.

Otherwise, the formula could become TRUE on a bar and give a signal even before there is a Trade Position. In this case it will be ignored as it should be. However, if it remains TRUE and then later on that very same bar or a different bar, when there finally is a Trade Position, there will no longer be an Exit signal because the prior TRUE state was already ignored and the formula remains TRUE and not giving a new signal.

The actual order is not submitted until the cell becomes nonzero or TRUE assuming the conditions for sending the order have been met.

Sell Entry (column M)

When the Condition Formula in row 3 of column M on the Spreadsheet returns a nonzero value or TRUE, then a Sell order will be given if certain conditions are met. For complete details about these conditions, refer to the Auto Trade Management page.

The actual order is not submitted until the cell becomes nonzero or TRUE assuming the conditions for sending the order have been met.

Sell Exit (column N)

When the Condition Formula in row 3 of column N on the Spreadsheet returns a nonzero value or TRUE, then a Buy order will be given if certain conditions are met. For complete details about these conditions, refer to the Auto Trade Management page.

You need to code into your Exit formulas by using the AND() Spreadsheet function, a check that there is a Trade Position Quantity. An example would be: =OR(AND($J$8 < 0, ID0.SG4@$3 >= $J$9+1), AND($J$8 < 0, ID0.SG4@$3 <= $J$9-2)).

The reference to cell J8 refers to the current Trade Position Quantity. The dollar signs in $J$8 mean it is an absolute Spreadsheet reference.

In the case of a Sell Exit formula, you must to check to make certain there is a short Trade Position by making certain that J8 is < 0. The purpose of this is so that the formula remains FALSE until such time that a Trade Position can be exited.

Otherwise, the formula could become TRUE on a bar and give a signal even before there is a Trade Position. In this case it will be ignored as it should be. However, if it remains TRUE and then later on that very same bar or a different bar, when there finally is a Trade Position, there will no longer be an Exit signal because the prior TRUE state was already ignored and the formula remains TRUE and not giving a new signal.

The actual order is not submitted until the cell becomes nonzero or TRUE assuming the conditions for sending the order have been met.

Introduction

There are many cases in which a Buy or Sell signal from the Entry and Exit formula columns (K, L, M, N) 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 played, Alert Message logged, or a Trade Order sent. This section explains all of this.

Enabling Auto Trading

If Trade >> Auto Trading Enabled - Global or Trade >> Auto Trading Enabled - Chart are unchecked, then Buy and Sell trade order signals from the Spreadsheet System for Trading study will be ignored. In this case you will see messages added to the Trade >> Trade Service Log indicating this.

The Send Orders to Trade Service study Input setting needs to be set consistently with the Trade >> Trade Simulation Mode On setting on the menu. If it is not, then Buy and Sell trade order signals from the Spreadsheet System for Trading study will be ignored. In this case you will see messages added to the Trade >> Trade Service Log indicating this. For complete details, refer to SendOrdersToTradeService.

Ignored Signals During Historical Data Downloading

With the Spreadsheet System for Trading study and the Spreadsheet System/Alert study, System and Alert signals from the Sheet formula columns are ignored on historical data and when historical data is being downloaded. They are monitored for when the chart is actually updating with new data.

A chart will be updating with new data during real-time chart updating with live data, during replays, or during a Back Test. If you want to see the Back Test results from historical Buy and Sell signals from the Buy/Sell formula columns in the case of when using the Spreadsheet System for Trading study, then refer to the Back Testing section.

Buy/Sell Arrows Do Not Mean Trade Signals

When using the Spreadsheet System for Trading study and a formula returns TRUE/1 in the Buy Entry (K3), Buy Exit (L3), Sell Entry (M3), or Sell Exit (N3) cells, a trade signal is not necessarily going to be given.

You will see an arrow above or below the chart bar corresponding to the particular formula column cell indicating a buy or sell signal based upon that TRUE/1 value, assuming the corresponding Subgraph Draw Style is set to a visible style. For example, if cell K5 is 1 in the Sheet being used by the Spreadsheet Study and the Buy Entry Subgraph >> Draw Style is set to Arrow Up, then you would see an up arrow displayed below the third chart bar from the last bar in the chart.

However, this does not necessarily mean that the formula TRUE/1 result will be followed and a trade signal will be given. The BuyEntry (K), BuyExit (L), SellEntry (M), SellExit (N) Spreadsheet 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 the trade signal is ignored, then a message will be added to the Trade >> Trade Service Log indicating the reason why.

Review the Trade Service Log for the reason. To see on the chart the actual order fills from trades made, you need to display the order fills. For instructions to do this, refer to Displaying Order Fills.

Reset Condition on New Bar

When using either the Spreadsheet System for Trading or Spreadsheet 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 added to the chart 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.

Signal Only Once per Bar

If a TRUE signal has been given by the formula in a particular cell and then it goes back to FALSE and back to TRUE on the same bar, you will not get a signal if the study Input Signal Only Once Per Bar is set to Yes.

One scenario to be aware of, is that a Buy/Sell Exit signal is given when there is not currently an existing Trade Position, and then there is an Entry on the same bar and then a Buy/Sell Exit on that same bar again. In the case of when Signal Only Once Per Bar is set to Yes, that second Buy/Sell Exit is going to get ignored. The solution to this is to check for a nonzero Position Quantity in the Buy/Sell Exit formula.

Signal Only on Bar Close

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 the case of when either Signal Only Once per Bar and Signal Only on Bar Close study Inputs have been set to Yes, when a signal is ignored by these restrictions, there is no message added to the Trade Service Log or the Alerts Log, except in the case of the Spreadsheet System for Trading study when the signal has been ignored as the result of Signal Only Once Per Bar being set to Yes.

Troubleshooting

In order to troubleshoot a problem with ignored signals, we recommend setting both of the Signal Only On Bar Close Inputs to No.

Detailed logging can also be enabled which will add reasons for ignored signals in all cases, to the Trade >> Trade Service Log.

When using the Spreadsheet System for Trading study, set the Enhanced Auto Trade Signal Related Logging Input to Yes.

When using the Spreadsheet System/Alert study, set the Enhanced System Signal Related Logging Input to Yes.

Only set these Inputs to Yes when absolutely necessary because they will add a very large number of messages to the Trade Service Log and can cause Sierra Chart to be nonresponsive at times.

System/Alerts and Draw Style

In the case of the Spreadsheet System/Alert study, if the Draw Style for a Spreadsheet column is set to Ignore, then System or Alert signals in that column will be ignored.

Nonzero Trade Position Quantity

In the case of the Buy Exit or Sell Exit Order Action columns when using the Spreadsheet System for Trading study, if a Buy/Sell Exit formula is TRUE and remains TRUE before there is a nonzero Trade Position Quantity and then a nonzero Trade Position Quantity later exists, then the Exit will be ignored it until it goes back to FALSE and then to TRUE.

Therefore, you need to program the formula to remain FALSE until there is a Trade Position Quantity. This can be done by using the AND function and checking that cell J5 is nonzero.

Delayed TRUE Result from Formulas

Another thing to also consider is that you may see an arrow on the chart from one of the Order Action formula column cells indicating TRUE, 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 corresponding Signal Only On Bar Close Input is set to Yes, then that TRUE result going to be ignored as well.

You cannot make an assumption that when you see a 1/TRUE in a cell, that at the moment the cell is evaluated for an order/alert signal that the 1/TRUE was present at that time. It may have been 0 and could have went to 1 quickly after.

Spreadsheet System for Trading Test Procedure

To get an understanding of how the TRUE signals in K3, L3, M3, N3 are processed follow the test procedures on the Spreadsheet System for Trading Test Procedure page.

After you go through this test procedure, and you are still having difficulty troubleshooting your Spreadsheet System for Trading study implementation, we recommend changing the formulas to simple TRUE/1 values temporarily during a replay just like explained in the test procedure, and then gradually add to the complexity of them until you can determine the source of the problem.

The Effect of the Number of Rows Input

The Number of Rows Input with the Spreadsheet studies, affects the standard Up and Down arrows displayed on a chart by the Spreadsheet studies. You will need to increase this Input value to see arrows going back more bars. Arrows that were placed on bars further back than the Number of Rows Input during real-time updating and chart replays, can disappear, or if they have not been removed because there has not been a full recalculation of the study, they may no longer be valid.

Special Spreadsheet System for Trading Values

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

Current Quote Values [Cells J12 through J20]

Cells J12 through J20 display the Current Quote Values for the chart symbol. These are always displayed on the Spreadsheet used by the Spreadsheet System for Trading study, and will only be displayed if the Output Current Quote Data Input is set to Yes for the other Spreadsheet studies.

When Sierra Chart is connected to a real-time data feed (File >> Connect to Data Feed), the Daily Open, Daily High, Daily Low, Daily Price Change, and Daily Volume come from this data feed.

When a chart is replaying, these values are set based upon the prices which have occurred during the replay or back test. And they do not reset daily. So therefore they are not necessarily accurate at a given point in time during a trading day.

When connected to the real-time data feed, these values are the same as the data displayed in Window >> Current Quote Window. The Current Quote Window contains the daily quote data for the symbol of the active chart.

Tick Size (Read-only) [J21]

This cell contains the Tick Size of the symbol. This is the minimum increment that the symbol trades in. This data is outputted for all of the Spreadsheet studies.

The Tick Size is useful to use in formulas where you want to specify a number of ticks as an actual price value. For example =4 * J21.

Limit or Stop Prices [J22, J23, J24, J25]

Cells J22 through J25 allow you to set the Limit or Stop order prices for each of the 4 different Spreadsheet Order Actions (Buy Entry, BuyExit, Sell Entry, Sell Exit). These apply when you have set to use a Limit or Stop order for a particular Order Action in cells J71 through J74. Refer to Buy Entry: Order Type [J71].

You are able to set a Limit or Stop price 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. This is only an example. You can use whatever formula you require to set whatever price that you want.

You can use the Tick Size in cell J21 to use in these Limit or Stop Price cell formulas to specify a certain number of ticks as an actual price value. For example = E3 + 4 * J21.

If this cell is set to 0, and the Order Action is a BUY_ENTRY or SELL_EXIT than the current Ask price if available is used. If this cell is set to 0, and the Order Action is a SELL_ENTRY or BUY_EXIT, then the current Bid price if available is used.

Order Quantity [J26]

Cell J26 controls the Order Quantity for orders that are submitted from the Spreadsheet System for Trading study. This quantity can be dynamically controlled by a formula.

If the cell is empty or 0, the Order Quantity will be set to 1 for Buy Entry and Sell Entry.

If the cell is empty or 0 during a Buy Exit or Sell Exit, then the quantity is determined by the automated trading logic and the end result will be that the Trade Position for the Symbol and Trade Account is flattened.

Negative order quantities are converted to positive numbers.

Cancel Working Orders [J27]

Cell J27 allows you to cancel all working orders for the Symbol and Trade Account of the chart that the Spreadsheet System for Trading study is currently applied to.

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 into this cell 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. However, these cancellations in the case of non-simulated trading do take time to occur and they will be pending during the evaluation of the Entry and Exit signal cells.

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.

If you wish to cancel working orders at a specific time, then you can use a formula like this: =(J41-INT(J41)) > TIMEVALUE("16:15:00"). This example will return TRUE when the time is 16:15 or greater. Cell J41 is the latest time in the last bar in the chart. It is best to use that cell, since NOW() always refers to the current time, and that will not work during back testing.

Disable Auto Trading [J28]

When Cell J28 is set to TRUE it disables all trading from the Spreadsheet. When this cell is set to TRUE, all Spreadsheet Buy/Sell entries and exits, flatten Position and cancel order commands are ignored.

However, if this cell is set to TRUE at the same time that a cell like Flatten Position and Cancel Working Orders is set to TRUE, then the Flatten Position and Cancel Working Orders action will still occur and then automated trading will be disabled after that. This paragraph applies to versions 1463 and higher.

You can enter a formula in this cell to disable trading when certain conditions are met or even when the latest Date-Time of the last bar in the chart meets some specified time condition. Manually setting this Input to TRUE or any non-zero number will simply disable all auto trading until the cell is set back to FALSE.

If you want to disable automated trading at a specific time, then you can use a formula like this: =(J41-INT(J41)) > TIMEVALUE("16:15:00"). This example returns TRUE when the time is 16:15 or greater. Cell J41 is the latest time in the last bar in the chart. It is best to use that cell, since NOW() always refers to the current time, and that will not work during back testing.

For documentation for the particular Spreadsheet functions used, refer to Spreadsheet Functions.

To limit trading to the day session only, use this formula: =OR(J41-INT(J41) < TIMEVALUE("09:30:00"), J41-INT(J41) > TIMEVALUE("16:00:00") ). This formula assumes your time zone in Sierra Chart is set to New York, and the symbol of the chart is a stock or stock index.

Flatten Position and Cancel Working Orders [J29]

Cell J29 flattens the existing Trade Position that the Spreadsheet System for Trading study is reporting on the Sheet. So this will be for the Symbol and Trade Account that the chart is set to.

All Working orders will also be canceled for the symbol and Trade Account.

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.

If you want to flatten the Trade Position and cancel working orders at a specific time, then you can use a formula like this: =(J41-INT(J41)) > TIMEVALUE("16:15:00"). This example will return TRUE when the time is 16:15 or greater. Cell J41 is the latest time in the last bar in the chart. It is best to use that cell, since NOW() always refers to the current time, and that will not work during back testing.

Maximum Chase Amount for Limit Chase Orders [J46]

This cell is used to specify the maximum chase amount for limit chase orders. For more information, refer to the Use Chase Orders Input.

Buy Entry: Order Type [J71]

This Spreadsheet Input is for specifying the order type for the Buy Entry Order Action column.

It can be:

• M = Market
• L = Limit
• S = Stop
• SL = Stop-Limit

The corresponding Limit or Stop price is set in cell J22. If the Limit or Stop price cell is set to 0 or is blank, then the Limit or Stop price will be set to the current Ask price.

If the Order Type is blank, it will be set to M (Market).

The order type can be programmatically set with a formula. Here is an example: =IF($J$5 > 0, "L", "M").

Buy Exit: Order Type [J72]

This Spreadsheet Input is for specifying the order type for the Buy Exit Order Action column.

It can be:

• M = Market
• L = Limit
• S = Stop
• SL = Stop-Limit

The corresponding Limit or Stop price is set in cell J23. If the Limit or Stop price cell is set to 0 or is blank, then the Limit or Stop price will be set to the current Bid price.

If the Order Type is blank, it will be set to M (Market).

The order type can be programmatically set with a formula. Here is an example: =IF($J$5 > 0, "L", "M").

Sell Entry: Order Type [J73]

This Spreadsheet Input is for specifying the order type for the Sell Entry Order Action column.

It can be:

• M = Market
• L = Limit
• S = Stop
• SL = Stop-Limit

The corresponding Limit or Stop price is set in cell J24. If the Limit or Stop price cell is set to 0 or is blank, then the Limit or Stop price will be set to the current Bid price.

If the Order Type is blank, it will be set to M (Market).

The order type can be programmatically set with a formula. Here is an example: =IF($J$5 > 0, "L", "M").

Sell Exit: Order Type [J74]

This Spreadsheet Input is for specifying the order type for the Sell Exit Order Action column.

It can be:

• M = Market
• L = Limit
• S = Stop
• SL = Stop-Limit

The corresponding Limit or Stop price is set in cell J25. If the Limit or Stop price cell is set to 0 or is blank, then the Limit or Stop price will be set to the current Ask price.

If the Order Type is blank, it will be set to M (Market).

The order type can be programmatically set with a formula. Here is an example: =IF($J$5 > 0, "L", "M").

Cancel All Orders except working Attached Orders [J78]

Cell J78 allows you to cancel all working orders except for working Attached Orders for the Symbol and Trade Account of the chart that the Spreadsheet System for Trading study is currently applied to.

Attached Orders that have a Pending Child - Client/Server status will still be canceled. Only Attached Orders that have an Open/Pending Open/Pending Modify status will not be canceled.

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 into this cell 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. However, these cancellations in the case of non-simulated trading do take time to occur and they will be pending during the evaluation of the Entry and Exit signal cells.

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.

Attached Order Target Offset (write only) [J80]

When this is set to a nonzero value, this will replace any existing defined Attached Orders on the Trade Window for the chart with a Target Limit order with the Offset specified in this cell.

The purpose of using a Spreadsheet cell to set the Target Attached Order Offset is so that it can be set according to a formula rather than hardcoded at a particular value on the Trade Window for the chart.

The Offset is relative to the parent order price. This Offset needs to be specified as an actual price value.

If a Stop order is also set through cell J81, then this will be added to the Attached Orders at the same time as well. For more information, refer to Attached Orders.

For the Attached Orders to actually be used when submitting a Buy/Sell Entry order, the Use Attached Orders option needs to be enabled on the Trade Window for the chart.

For additional information about using Attached Orders, refer to Initiating Trades with Auto-Trading and Using Manual or Attached Orders Exits.

Attached Order Stop Offset (write only) [J81]

When this is set to a nonzero value, this will replace any existing defined Attached Orders on the Trade Window for the chart with a Stop order with the Offset specified in this cell.

The purpose of using a Spreadsheet cell to set the Stop Attached Order Offset is so that it can be set according to a formula rather than hardcoded at a particular value on the Trade Window for the chart.

The Offset is relative to the parent order price. This Offset needs to be specified as an actual price value.

If a Target order is also set through cell J80, then this will be added to the Attached Orders at the same time as well. For more information, refer to Attached Orders.

For the Attached Orders to actually be used when submitting a Buy/Sell Entry order, the Use Attached Orders option needs to be enabled on the Trade Window for the chart.

For additional information about using Attached Orders, refer to Initiating Trades with Auto-Trading and Using Manual or Attached Orders Exits.

Use Separate Exit Order Quantity [J82]

When this cell is set to TRUE or a nonzero value, then a Buy Exit or Sell Exit Order Action will use the quantity specified by the Exit Order Quantity [J83] cell.

Exit Order Quantity [J83]

This cell is only used when Use Separate Exit Order Quantity [J82] is set to TRUE or a nonzero value.

This cell controls the Order Quantity for Buy Exit or Sell Exit Order Actions from the Spreadsheet System for Trading study. This quantity can be dynamically controlled by a formula.

If the cell is empty or 0 during a Buy Exit or Sell Exit, then the quantity is determined by the automated trading logic and the end result is that the Trade Position for the Symbol and Trade Account the automated trading system is for will be flattened.

Negative order quantities are converted to positive numbers.

Working Orders Exist (read-only) [J84]

This is set to TRUE when there are orders which currently have a Status indicating they are working and can be filled. This also includes Pending Child orders. Otherwise, this is set to FALSE. This cell is read-only.

Allow Trading Only During Time Range (read/write) [J85]

This can be set to TRUE to only allow automated trading by the Spreadsheet System for Trading study during the time range specified in the Start Time For Allowed Time Range and End Time For Allowed Time Range cells.

The current time used in the comparison is the Date-Time of the most recent trade/quote in the last bar in the chart. This either can be accurate to the second and based upon the most recent trade date-time for the symbol from the data feed, or will be based upon the Intraday chart data file record in the case of an Intraday chart and will also be accurate to the second in the case of 1 Tick or 1 Second Intraday data.

Otherwise, the time comparison accuracy is dependent upon the Intraday Data Storage Time Unit.

Start Time For Allowed Time Range (read/write) [J86]

When the Allow Trading Only During Time Range cell has been set to TRUE, then this cell specifies the starting time in the charts time zone that automated trading is allowed to begin at.

This time can be entered in the format: HH:MM:SS or using a formula that returns a Serial DateTime Value.

Effective with version 2120, it is possible for Start Time For Allowed Time Range to have a time which is greater than End Time For Allowed Time Range in which case Start Time For Allowed Time Range will be considered to start in the prior trading day.

For example, if Start Time For Allowed Time Rangeis set to 18:00:00 and End Time For Allowed Time Range is set to 12:00:00, then the allowed time range is from 18:00:00 to 23:59:59.999 in the prior day, to 23:59:59.999 to 12:00:00 in the following day.

End Time For Allowed Time Range (read/write) [J87]

When the Allow Trading Only During Time Range cell has been set to TRUE, then this cell specifies the ending time in the charts time zone that automated trading will no longer be allowed after.

This time can be entered in the format: HH:MM:SS or using a formula that returns a Serial DateTime Value.

Effective with version 2120, it is possible for Start Time For Allowed Time Range to have a time which is greater than End Time For Allowed Time Range in which case Start Time For Allowed Time Range will be considered to start in the prior trading day.

For example, if Start Time For Allowed Time Rangeis set to 18:00:00 and End Time For Allowed Time Range is set to 12:00:00, then the allowed time range is from 18:00:00 to 23:59:59.999 in the prior day, to 23:59:59.999 to 12:00:00 in the following day.

Flatten Positions And Cancel Orders At Set Time (read/write) [J88]

When this cell is set to TRUE, then the current Trade Position will be flattened and any working Orders will be canceled for the Symbol and Trade Account of the chart the Spreadsheet System for Trading study is applied to, at the time specified by the Time For Flatten Positions And Cancel Orders cell.

This cell will not perform a Flatten Positions or Cancel Orders operation when the Disable Auto Trading cell is true, or when using Allow Trading Only During Time Range and the current time based on the current chart bar time is outside of the specified time range.

Time For Flatten Positions And Cancel Orders (read/write) [J89]

When the Flatten Positions And Cancel Orders At Set Time cell is set to TRUE, then this cell specifies the time to flatten the Trade Position and cancel the Orders at.

This cell will not perform a Flatten Positions or Cancel Orders operation when the Disable Auto Trading cell is true, or when using Allow Trading Only During Time Range and the current time based on the current chart bar time is outside of the specified time range.

However, if the prior chart bar update time was within the time range used with Allow Trading Only During Time Range and then at the next chart update, the flatten time condition occurred at a time which is outside this time range, the flatten operation will still occur.

Chart Number (read-only) [J91]

The number of the chart for which this spreadsheet is associated.

Trade Account (read-only) [J92]

The selected trade account that will be used to place a trade. This is a "Read-Only" cell, which only displays the information, you can not change this value in this cell.

Last Working Entry Order Date-Time (read-only) [J93]

The Date-Time in the charts time zone, of the last entry order which was submitted whether it has filled or not.

This is useful to use in formulas to determine the amount of time elapsed since an order was submitted and it has not yet filled.

An Entry order is simply an order that will create a new Position or increase the existing Position without any consideration at all of any other working orders. And Exit order is simply an order that will exit an existing Position without any consideration at all of any other working orders that may be exiting the Position.

This data comes from the Trade >> Trade Orders Window. Since the orders in that list are automatically cleared if they are not working, this field is not persistent and can clear at any time if the order is no longer working.

Last Working Entry Order Price (read-only) [J94]

The Price of the last entry order which was submitted whether it has filled or not.

An Entry order is simply an order that will create a new Position or increase the existing Position without any consideration at all of any other working orders. And Exit order is simply an order that will exit an existing Position without any consideration at all of any other working orders that may be exiting the Position.

This data comes from the Trade >> Trade Orders Window. Since the orders in that list are automatically cleared if they are not working, this field is not persistent and can clear at any time if the order is no longer working.

Last Working Exit Order Date-Time (read-only) [J95]

The Date-Time in the charts time zone, of the last exit order which was submitted whether it has filled or not.

This is useful to use in formulas to determine the amount of time elapsed since an order was submitted and it has not yet filled.

An Entry order is simply an order that will create a new Position or increase the existing Position without any consideration at all of any other working orders. And Exit order is simply an order that will exit an existing Position without any consideration at all of any other working orders that may be exiting the Position.

This data comes from the Trade >> Trade Orders Window. Since the orders in that list are automatically cleared if they are not working, this field is not persistent and can clear at any time if the order is no longer working.

Last Working Exit Order Price (read-only) [J96]

The Price of the last exit order which was submitted whether it has filled or not.

An Entry order is simply an order that will create a new Position or increase the existing Position without any consideration at all of any other working orders. And Exit order is simply an order that will exit an existing Position without any consideration at all of any other working orders that may be exiting the Position.

This data comes from the Trade >> Trade Orders Window. Since the orders in that list are automatically cleared if they are not working, this field is not persistent and can clear at any time if the order is no longer working.

Bar Period Type (read-only) [J97]

The Bar Period Type defined as a number where the Days-Minutes-Seconds-Milliseconds is defined as 0 and the types go up in number in the order in which they appear in the Chart Settings.

Bar Parameter 1 (read-only) [J98]

The first parameter for the given Bar Period Type.

Bar Parameter 2 (read-only) [J99]

The second parameter for the given Bar Period Type. This will be zero if the Bar Period Type does not support more than 1 parameter.

Bar Parameter 3 (read-only) [J100]

The third parameter for the given Bar Period Type. This will be zero if the Bar Period Type only supports 2 or fewer parameters.

Bar Parameter 4 (read-only) [J101]

The fourth parameter for the given Bar Period Type. This will be zero if the Bar Period Type only supports 3 or fewer parameters.

Order Text Tag (write-only) [J102]

For the documentation for this cell, refer to the description for the Support Order Text Tag Cell Input.

The Text Tag only applies to orders which are submitted at the time it is set. If this cell is empty during the time when an order is submitted, the Text Tag is not going to be set with that order.

All Symbols Daily Net Profit/Loss (read-only) [J103]

Displays the Daily Net Profit or Loss for the selected Trade Account for all symbols.

All Symbols Cumulative Net Profit/Loss (read-only) [J104]

Displays the Cumulative Net Profit or Loss for the selected Trade Account for all symbols.

All Symbols Cumulative Profit/Loss (read-only) [J105]

Displays the Cumulative Profit or Loss for the selected Trade Account for all symbols.

Use Order Fill Calculated Position (read-only) [J106]

Defines whether the option for User Order Fill Calculated Position is set to Yes (1) or No (0).

Pause Chart Replay (write-only) [J111]

Entering any non-zero number in this cell will cause a replay that is running in the chart associated with the spreadsheet, to pause. It is necessary to clear this cell in order to continue the replay by selecting the Play button on the Replay Controls.

Trade Window Configuration File (write-only) [J112]

Entering the filename of a Trade Window Configuration (including the file extension of .twconfig) will load that configuration for the trade window for the chart associated with this spreadsheet.

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 Sheet row 3 is checked for TRUE/FALSE values that are returned by formulas in the Spreadsheet signal columns (K-Z).

If there are new bars added to the chart during a chart update, then the first row in the Sheet that is examined for TRUE/FALSE values that are returned by the formulas, is the previous topmost row during the prior update (the data previously at row 3).

The reason the previous topmost row is examined, is because there could be changes with the price data and studies at that row during the chart update. In other words, the examination continues where it left off.

Next, the Spreadsheet study processing function moves up the rows all the way up to 3, examining for TRUE/FALSE conditions row by row. For example, if there is one new bar added, then first row 4 is examined and then row 3 is examined for TRUE/FALSE conditions.

One potential issue to be aware of with this processing logic is that if a formula in a particular column is TRUE at row 3 and then a new bar is added and it becomes FALSE at row 4, then the internal state maintained by Sierra Chart for the Spreadsheet 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:

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

If there are new bars added to the chart during a chart update, then the first row in the Sheet that is examined for TRUE/FALSE values that are returned by the formulas, is the previous second topmost row during the prior update (the data previously at row 4).

The reason the previous second topmost row is examined, is because there could be changes with the price data and studies at that row during the chart update. Although, usually not in this case since the data was previously at row 4. In other words, the examination continues where it left off.

Next, the Spreadsheet study processing function moves up the rows all the way up to 4, examining for TRUE/FALSE conditions row by row. For example, if there is one new bar added, then first row 5 is examined and then row 4 is examined for TRUE/FALSE conditions.

One potential issue to be aware of with this processing logic is that if a formula in a particular column is TRUE at row 4 and then a new bar is added and it becomes FALSE at row 5, then the internal state maintained by Sierra Chart for the Spreadsheet column goes to FALSE and can then go back to TRUE if the formula is TRUE at row 4 during that update.

The potential issue with this is an unexpected signal due to a state change. Although, this kind of scenario is highly unlikely with Signal Only on Bar Close set to Yes because data other than at the last bar in the chart is not likely to change.

Order of Formula Column Processing and Internal Calculation Order

When the formula results in columns K through Z (last formula column by default) are processed for Trade Order or Alert signals by the Spreadsheet System for Trading or Spreadsheet System/Alert studies, column K is evaluated first, then L, then M, and so on through and including the last formula column (by default column Z). The last formula column is set by the Number of Formula Columns Input.

Therefore in the case of the Spreadsheet System for Trading study, the following columns are evaluated in the order in which they are listed left to right: Buy Entry, Buy Exit, Sell Entry, Sell Exit.

The order of calculation for the formulas in cells within a Spreadsheet is based upon the dependency they have to each other. A cell dependent on another cell is always going to be calculated after the cell it is dependent on.

In the case where 2 cells refer to each other, this is known as a circular reference. In the version of Sierra Chart with New Spreadsheets, there is no special handling of a circular reference, the result can be unpredictable, and an error may be given. Circular references are not supported as they simply make no mathematical sense and have an absolute impossibility to resolve accurately.