Spreadsheet Study Inputs

Chart Data Output Specification Type

This Input controls whether the Sheet the chart data is outputted to, is specified by number or by a specific name.

The choices are By Number or By Name.

In the case of By Number, the name of the Sheet name will be Sheet# where # equals the specified number through the Chart Data Output Sheet Number Input.

In the case of By Name, the name of the Sheet will be the exact name specified by the Chart Data Output Sheet Name Input.

Chart Data Output Sheet Number

This Input sets the specific Sheet in the Spreadsheet specified by the Spreadsheet Name setting on the Study Settings window, to output the chart data to. The default setting is 0 which means that the chart data will be outputted to the same Sheet number as the Chart Number that contains the Spreadsheet study.

Therefore, if the Chart Number is #3 (this is displayed at the top of the chart), the data will be outputted to a Sheet in the Spreadsheet named Sheet3.

The default names for Sheets within Spreadsheets, follow the format Sheet#. Where the # is equal to the Chart Number. When sheets are added and removed, references to existing Sheets by the Spreadsheet Study continue to remain valid.

Do not use more than one instance of the Spreadsheet study on a chart which outputs chart data to both the same Spreadsheet and the same Sheet number. This will cause a problem where when new bars are added to the chart, each Spreadsheet study will shift the rows down causing what appears to be duplicate data in the Sheet.

When using the same Spreadsheet from multiple Chartbooks, make sure that each chart with the same Chart Number which has a Spreadsheet Study on it, does not output to the same Sheet used by other Spreadsheet studies in different Chartbooks. Each instance of the Spreadsheet Study must use its own separate Sheet.

For example, if Spreadsheet ABC is used by Chart #1 in Chartbook1 and Chart #1 in Chartbook2, and they both are outputting data to Sheet1, this is going to be inefficient and cause conflicts. What happens in this case is that every time each of these Spreadsheet Studies outputs data to the same Sheet, they will fully output all rows at once which is inefficient. Make sure one of these charts is outputting data to a different Sheet by changing the Chart Data Output Sheet Number Input.

Chart Data Output Sheet Name

When the Input Chart Data Output Specification Type is set to By Name, then the Chart Data Output Sheet Name text string Input specifies the name of the Sheet within the Spreadsheet the chart data will be outputted to.

Number of Formula Columns

The default number of formula columns that can be used on a Sheet used by the Spreadsheet studies is 16 (column K through Z). This can be increased up to 60. The additional columns are after column Z on the Sheet.

Number of Rows

The Number of Rows Spreadsheet Study Input sets the number of rows to output to the Sheet in the Spreadsheet window used by the Spreadsheet Study. Each row in a Sheet corresponds to a chart bar / column.

The more rows outputted, the more CPU time is required for output to and Input from a Sheet every time a new bar is added to the chart or during a full recalculation of studies. The first row on the Spreadsheet is the last (most recent) bar / column in the chart.

Not all of the data loaded into the chart window is necessarily outputted to a Sheet. Therefore, the Days to Load setting in Chart >> Chart Settings , does not itself control the amount of data outputted to a Spreadsheet. The Number of Rows Input does.

Therefore, the maximum amount of data outputted to the Sheet is going to be the minimum of Days to Load and the Number of Rows Input.

During real-time updating of a chart or during a replay, as new bars are being added to the chart, the previous values at the end of the Formula Columns K and Z (can be a higher column number if the Number of Formula Columns Input is greater than 16) which are no longer displaying on the Sheet, will still be displayed by the Spreadsheet Study Subgraphs until the chart is fully recalculated. So therefore the number of data elements displayed by the Spreadsheet Study on a chart can exceed the Number of Rows setting.

If you are using multiple instances of the Spreadsheet Study, all or some of them are using the same Spreadsheet Name set in the Study Settings window for the Spreadsheet Study, meaning they are using the very same Spreadsheet file, these Spreadsheet studies are being used in different Chartbooks, the Spreadsheet studies are used on the same Chart Number in these different Chartbooks, then this will cause each Spreadsheet Study to be fully recalculated at each chart update.

For example, this will happen if a Spreadsheet Study is on chart #1 in one Chartbook and chart #1 in another Chartbook and both are using the very same Spreadsheet Name. In this case if you have the Number of Rows Input set to 5000, then all 5000 rows will be processed at each chart update for each chart. This can consume a lot of CPU time and make Sierra Chart less responsive and run slower.

Therefore, it is a good idea to keep the Number of Rows Input set to a small number in this particular case, or use a different Spreadsheet Name for the Spreadsheet studies in different Chartbooks.

First Study Row to Graph

If the number of rows is 400 and you only want to graph on the chart all but the last 10 rows (the rows at the bottom of the Spreadsheet column), then set this to 10. For example if you have an 11 period moving average, then the last 10 moving average values on the Spreadsheet will not contain useful data. So you will want to set this to 10 to prevent graphing the data that is not accurate.

Formula Source Sheet Specification Type

This Input controls whether the Sheet that contains the formulas, is specified by number or by a specific name.

The choices are By Number or By Name.

In the case of By Number, the name of the Sheet name will be Sheet# where # equals the specified number through the Formula Source Sheet Number Input.

In the case of By Name, the name of the Sheet will be the exact name specified by the Formula Source Sheet Name Input.

Formula Source Sheet Number

The Formula Source Sheet Number Input specifies the Sheet number (Sheet#) in the Spreadsheet where the formulas for use by the Spreadsheet Study, are located.

The purpose of this Input is to set a different Sheet within the same Spreadsheet to reference the formulas from. In the case where you want to use the Spreadsheet Study on multiple charts within a Chartbook, use the same Spreadsheet Name, and share the same formulas, then this Input allows each instance of the Spreadsheet Study to reference the same formulas on the same Sheet so you only need to enter and update the formulas in one location.

This Input is displayed in cell D1 on the Sheet where the chart data is outputted to. Example: Formula Source Sheet #1. Enter and modify formulas for this sheet on Sheet1.

Below is an example of selecting the active sheet in the Spreadsheet.

The formulas in columns K through Z (assuming 16 formula columns) from the Sheet Number specified with this Input are copied to the corresponding Sheet columns on the Sheet where the chart data is outputted to. The Sheet where the chart data is outputted to is controlled by the Chart Data Output Sheet Number Input.

For example, if the formulas are located on Sheet1, the Formula Source Sheet Number Input is set to 1, the Spreadsheet Study is on Chart Number 2 and the chart data is outputted to Sheet2, then the formulas from Sheet1 will be copied to Sheet2 and used to perform calculations on the data from chart #2.

By default this Input is set to be the same as the Chart Number. For example, if you add a Spreadsheet Study to chart #2, then this Input will be set to 2 automatically. In this case the Spreadsheet formulas are not copied from another Sheet. They will only be filled down the columns on the specified Sheet (in this case Sheet2).

Set the Formula Source Sheet Number Input to 0 to automatically cause it to be set to the same as the Chart Number.

If you save a Spreadsheet Study as part of a Study Collection and then use that Study Collection on another chart, then the formulas from the Sheet your formulas were originally entered on, will be copied to the Sheet used by the other chart the Study Collection is added to without having to manually copy them to every Sheet you will be using. Usually you will just enter formulas on one of the Sheets and make changes only to the formulas on that Sheet.

This is the reason why a Spreadsheet should usually be used for just one specific study or trading system you create.

If you apply a Study Collection containing a Spreadsheet Study to a chart and you wish to use a different set of formulas than what are contained on the original Sheet, then you will need to change the Formula Source Sheet Number Input to the Chart Number that you applied the Study Collection to. You can then go to the Sheet in the Spreadsheet that corresponds to the Chart Number that you applied the Study Collection to, and enter your formulas on that Sheet.

Formula Source Sheet Name

When the Input Formula Source Sheet Specification Type is set to By Name, then the Formula Source Sheet Name text string Input specifies the name of the Sheet within the Spreadsheet the formulas will be located.

Always Set Formula Source Sheet to Chart Data Output Sheet

When this Input is set to Yes, the Formula Source Sheet Number Input will always be set to be the same as the Chart Data Output Sheet Number/Name.

Use Price Graph Style

When the Use Price Graph Style Input is set to Yes, then the Graph Draw Type setting on the Subgraphs tab of the Study Settings Window for the study can be set to any of the price graph type of styles.

This includes OHLC Bars, Candlestick Bars and any of the others.

When this Input is set to Yes, Sheet column K represents the Open price, Sheet column L represents the High price, Sheet column M represents the Low price, and Sheet column N represents the Close/Last price of a bar. Using all of these formula columns together, you are able to create price bars.

Use Standard Chart Header

Do Not Output Subgraphs Set To Ignore

When this Input is set to Yes, Subgraphs of studies on the chart which have a Draw Style set to Ignore will not be output to the Spreadsheet.

Output Additional Values in Columns I and J

When this Input is set to Yes, in columns I and J, the current daily quote for the symbol will be outputted, the settings for the bars in the chart, such as the number of minutes, number of ticks,..., and various other information that you may want to use in your calculations.

Draw Zero Values

When this Input is set to Yes, then values of zero outputted from your Spreadsheet formulas will be drawn on the chart. Otherwise, they will not be and zero values will be skipped when drawing. Typically this Input should be set to No.

Output Volume at Price Data

When the Output Volume at Price Data Input is set to Yes, then the trade volume for each price level for each chart bar is outputted to the corresponding Sheet in the Spreadsheet, used by the Spreadsheet study.

The data is outputted beginning at column BA. Refer to the image below.

Each row of data outputted by the Spreadsheet study to a Sheet represents one bar in the chart for the time period of the bar. The data is ordered from the Low Price of the bar to the High Price of the bar, where each column represents one price tick.

So each column represents one price. At each row in a particular column for a price, is the volume for that price for the particular chart bar the row is for.

For example, in the image above, row 9 represents one bar in the Main Price Graph. That bar has a Low Price of 2776.75 and a High Price of 2778.5, the Tick Increment for this symbol is 0.25. Therefore the cell at BC9 shows a Volume of 151 at the Low Price of 2776.75. Cell BD9 shows a Volume of 979 at the price of 2778.00 (one tick above the Low). Cell BD10 shows a volume of 1103 at the price of 2778.25 (two ticks above the low). This process continues through to cell BJ9 which shows a volume of 122 at the High Price of 2778.5.

Blank Columns Before First Study

The default for this Input is 0 which means that there will be no empty columns before the Sheet column that the studies are outputted to on the Sheet. If the Number of Formula Columns is set to 16 which is the default, then the studies will be outputted starting at Column AA on the Sheet.

If this Input is set to a nonzero value, then blank columns are inserted on the Sheet before the first study outputted to the Sheet. For example, if this is set to 1, then the first study will be outputted starting at column AB instead.

Blank Columns After Each Study

The default for this Input is 1 which means that there will be 1 blank column after each study on the chart which is outputted to the Sheet. So between each study outputted to the Sheet, there will be a blank column. Using a larger number will output additional blank columns after each study outputted to the Sheet.

Copy Column J from Formula Source Source Sheet

This Input is supported by the Spreadsheet System for Trading study. When this Input is set to Yes, the read/write cells from column J from the Formula Source Sheet are copied to the corresponding cells in the Sheet used by the Spreadsheet System for Trading study.

Periodically Save Sheet as Text in Seconds

When this is set to a nonzero number the Spreadsheet Sheet the chart data is outputted to is saved to a text file as tab delimited values every specified number of seconds.

You can then use a program like Excel or any other program to periodically read this data. Excel does support periodically reading of a CSV file.

This feature is only supported in version 2071 or higher of Sierra Chart.

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.

Use Column I and J Values Version 2

This is a new Input in recent versions of Sierra Chart, as of this writing. The default is No.

When it is set to Yes, then all of the read-only, or read/write names and values in columns I and J, are organized in a more logical and updated format. These include various fields of information related to trading and the chart, and numerous special Inputs related to primarily trading.

Clear Unused Column I Names

This Input can be set to Yes and it will clear column I names which are not used based upon how the Use Column I and J Values Version 2 Input is set. For example, if there is a particular row in column I which is unused, when Use Column I and J Values Version 2 is set to No, then that particular cell will be cleared if it contains any name from previously, which can be then misleading.

Once it executes once, this Input is set automatically back to No.

Support Order Text Tag Cell

The Order Text Tag cell in Column I/J will be supported if this Input is set to Yes. This cell is only used by the Spreadsheet System for Trading study.

It sets the Text Tag text on each order that is submitted from the Spreadsheet. For more details, refer to Text Tag.