Spreadsheet Study Inputs
- 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 Example Formulas and Usage
Spreadsheet Study Inputs
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 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 a Sheet used by other Spreadsheet studies in different Chartbooks.
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. 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.
It is expected that sometime during 2019 that support for Excel will be removed due to internal changes in Sierra Chart which will make it difficult to support. Older versions of Sierra Chart supporting Excel will still be made available for several years. However, please take notice of this so you do not put any long-term reliance on this functionality. If this functionality is removed, it is extremely unlikely that we would ever add support for it back.
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.
Another solution is to use ACSIL (Advanced Custom Study Interface and Language) to access the bar and study data in a chart. Refer to: Advanced Custom Study Interface and Language. And then you can develop your own custom interface to Excel using this data to accomplish just what you need. This is going to be only though for those with some programming abilities.
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 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.
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.
If the Alert Sound does not play when expected, then refer to the Troubleshooting for Alert Sounds That Do Not Play section about this.
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.
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.
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.
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.
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.
*Last modified Wednesday, 27th February, 2019.