#### Home >> (Table of Contents) Spreadsheets >> Overview of Spreadsheet Studies >> Spreadsheet Study Inputs

# Spreadsheet Study Inputs

## Related Documentation

- Overview of Spreadsheet Studies
- Using the Spreadsheet Study
- Spreadsheet Systems, Alerts and Automated Trading
**Spreadsheet Study Inputs**- Referencing Other Charts in Spreadsheet Study Formulas
- Spreadsheet Studies Special Tasks
- Sharing Your Spreadsheet Study With Another User
- Working with Spreadsheets
- Spreadsheet Functions

## 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 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.

### Use Excel

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 most of the calculation functions available in Excel.

While the interface Sierra Chart provides 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.

### 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

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 Spreadsheet to reference the formulas from. In the case where you want to use the Spreadsheet Study on multiple charts with a Chartbook 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**).

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.

#### 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. This is normal and expected. The solution is to set the **Formula Source Sheet Number** Input with the Spreadsheet Study to be the same number as the Sheet the chart data is outputted to.

### 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.

### 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.

### 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 Wednesday, 15th February, 2017.