Spreadsheet Study Inputs

Spreadsheet Study Inputs

This section documents the available Study Inputs for the Spreadsheet Study, Spreadsheet System/Alert, and the Spreadsheet System for Trading studies.

To access the Study Inputs for one of the Spreadsheet studies, refer to Adding/Modifying Studies. The Inputs are located on the Settings and Inputs tab of the Study Settings window.

This page does not document the Inputs that are specific to the Spreadsheet System/Alert and Spreadsheet System for Trading studies.

Inputs specific to those particular studies, can be found in the Study Settings Inputs that are Specific to Systems, Alerts, and Automated Trading section.

Chart Data Output Sheet Number

Sets the specific Sheet in the Spreadsheet 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.

New Spreadsheets: The default names for Sheets within the New 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.

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.

Use Excel

NOTICE: This Excel interface feature is subject to removal sometime in 2017. Therefore, do not put reliance on this feature.

Set this input to Yes to use an Excel Spreadsheet instead of the built-in Spreadsheet. Excel version 97 or higher is required.

We provide no technical support when using Excel. If you encounter any problems with Excel it is strongly recommended you set this input to No. Sierra Chart Spreadsheets provide all of the calculation functions available in Excel.

While the interface we provide to Excel is implemented properly and in general works well, because Excel is an independent application and due to the nature of the interface software architecture that Microsoft provides, the interface is not of the most highest reliability and you could experience issues. We highly recommend using the built-in Sierra Chart Spreadsheets instead. The interface is provided as is without any support.

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 Number

This input specifies the Sheet number (Sheet#) in the Spreadsheet where your formulas are or need to be located. 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.

One thing to be aware of is that the the Sheet number is the absolute position of the Sheet within the Spreadsheet. Therefore, Sheet number 4 which is labeled Sheet4 by default, is the fourth Sheet from the left, in the Spreadsheet. If this Sheet happens to be labeled differently, then it will still be used.

Sheets are numbered from left to right. The 1st one is Sheet1. The next one is Sheet2, and so on. The formulas in columns K through Z from the specified source Sheet in a Spreadsheet are copied to the corresponding Sheet columns on the Sheet where the chart data is outputted to. For example, if your formulas are located on Sheet1, the Formula Source Sheet Number input is set to 1 and you have the Spreadsheet Study on chart number 2, 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 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).

The reason for this input is if you were to 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 without having to manually copy them to every Spreadsheet 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 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 Spreadsheet, 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.

Formulas Getting Erased

If you enter Formulas in cells K3 through Z3 on a Sheet in a Spreadsheet that has formulas copied from another Sheet because the Formula Source Sheet Number input is set to a different Sheet number, then the formulas you enter will be erased when the Spreadsheet is updated. They will be either blank or replaced by the formulas from the source Sheet.

Point and Figure Inputs on the Spreadsheet

Every Sheet in a Spreadsheet that is used with the Spreadsheet Study will have special inputs in cells J80 and J81. These are used if the chart is a Point and Figure chart. They can be set to dynamically modify the Box and Reversal Size using your own formulas based on chart data and studies.

Support All Subgraphs

When this input is set to Yes (the default for newly added instances of the Spreadsheet studies), then studies that are outputted beginning at column AA will have all of their subgraphs outputted. Previously there was a limitation of 4 subgraphs per study. Now all available subgraphs can be outputted. There will be a blank column between studies. And if this is Yes, all formula columns up to column Z can contain formulas. These columns will be filled down automatically, and can be graphed on the chart if the corresponding subgraph draw style is changed to a visible style. It is recommended that this input always be set to Yes.

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 send email messages. 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, no email alert message, and no message added to the Window >> Alerts Manager >> Alerts Log.
  • No Alert Sound: This setting means that an alert sound will not be played and an alert email message will not be sent, but there will still be a message added to the Window >> Alerts Manager >> Alerts Log when the formula at row 3 in the formula column becomes TRUE.
  • Alert 1-150: This setting means that an alert sound will be played, an alert email message can be sent and there will be a message added to the Window >> Alerts Manager >> Alerts Log when the formula at row 3 in the formula column becomes TRUE.

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.

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.

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 outputed to the Spreadsheet.

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.

Draw Zeros

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 first row of the Volume at Price data at Row 2, are the prices which have volume data. 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.

*Last modified Monday, 19th September, 2016.