Sierra Chart - Worksheet Studies, Systems and Alerts

Worksheet Studies, Systems and Alerts

Related Documentation

On This Page

General Information

The Worksheet Study/System/Alert Study will output chart data to the built-in worksheets or to an Excel worksheet. This lets you work with the data using worksheet formulas. Worksheet columns K through Z are graphable on the chart to show the results of your own formulas that use the chart data. This will let you create your own custom chart Studies, Systems and Alerts. Doing color bar studies is also possible. Any time the chart is updated, the worksheet will also be updated and the worksheet graph on the chart.

The results of all the studies on the chart are also outputted to the worksheet. This lets you use the built-in studies of the program in your own formulas. If the Worksheet Study/System/Alert is set up as a system/alert, then the results of all the other Worksheet studies on the chart are also outputted so you can use them in your System or Alert formula.

[Top]

Step-by-step Instructions

  1. Go to an open chart or open a chart. Select Analysis >> Studies on the menu. Select .Worksheet Study/System/Alert in the list on the left. Press the Add button.

  2. Highlight .Worksheet Study/System/Alert in the Studies to Graph list. Press the Settings button. In the Technical Study Settings window on the Settings And Inputs tab there is a text box for the Workbook Name. Enter a name in the text box without an extension. This is the name of the workbook that will be opened or created to contain the data and formulas. The name should be related to what your custom study will do. For example, you can enter SMA to indicate its for a simple moving average. If there is no workbook with this name already, then a new one will be created. The workbook will be automatically opened or a new one will be created.

    If you want to use a workbook you created before, then you can see all of your saved workbooks by selecting File >> Open Workbook on the main menu.

  3. On the Settings and Inputs tab, set the the Inputs and other settings as you require. For a description of the available Inputs for the .Worksheet Study/System/Alert study, see Worksheet Inputs for general Inputs, and Study Settings Inputs that are Specific to Systems, Alerts, and Automated Trading for Inputs specific to systems and alerts.

    If you are creating a System or Alert, then set the System/Alert input to 2. For more information see Worksheet Systems and Alerts.

  4. Select the Subgraphs tab. Adjust the settings for each Subgraph as you require. Each subgraph corresponds to a formula column on the Worksheet. If you want a formula column to be visible on the chart, then set it's Draw Style to something other than Ignore or Do Not Draw. Otherwise, set the Draw Style to Ignore.

    Coloring Bars Based on Certain Conditions: If you wish to create a study which colors bars based upon a certain condition, then you will want to set the Draw Style for the Subgraphs you will be using to Color Bar. If you wish to color bars just one color, then you will just need only one Subgraph and the Draw Style for the others should be set to Ignore. If you wish to color the bars 2 colors, then you will need 2 formula columns and you will want to set the Draw Style for 2 subgraphs to Color Bar and set the colors you want. Keep in mind for a Color Bar worksheet study, the input Study or System/Alert should be set to 1.

  5. For new Worksheet studies the Input, Support All Subgraphs, should be set to Yes and it is by default.

  6. Press the OK button to close the Settings window for the Worksheet study.

  7. If there are other studies you want to add to the chart that have not already been added or studies you want to reference in the Worksheet formulas, then add them now and adjust the settings as you require them for each study.

    These studies will be outputted beginning at column AA. If you wish to have access to Bid Volume and Ask Volume, then add the Bid Volume and Ask Volume studies to the chart as well.

    If you added a study to the chart for the purpose of only referencing it in your Worksheet formulas and do not want to make it visible on the chart, then in the Study Settings for the study enable Hide Study.

  8. Type a name in the Study Collections text box. You can also select an existing name from the drop-down list. This name can be the same as the Workbook Name you entered earlier, or a similar name. It does not have to match. Press the Save Single button or Save All button. For more information refer to Study Collections. This will let you apply your Worksheet Study or System and all your settings to the chart or any other chart with one step by selecting the name from the programs Analysis menu. If your worksheet formulas will use the results of other studies on the chart, then you must press the Save All button. If you apply the Study Collection to multiple charts, they will use the same Workbook file. Each chart will use a separate worksheet within the workbook. Chart #1 will use Sheet1, chart #2 will use Sheet2 and so on.

  9. Press the OK button on the Chart Studies window.

  10. The workbook will be opened in the background. Go to it by selecting Window >> [Workbook Name] on the menu. The name will be what you've entered in a prior step plus a .scwbf extension. A workbook contains multiple worksheets. The worksheet number the data is outputted to is the same worksheet number as the chart number. For example, chart #2 will be outputted to Sheet2 in the workbook. The worksheets can be selected with the tabs at the bottom of the workbook. All of the individual studies are also outputted to the same worksheet beginning at column AA.

    If the workbook has been previously used by the Worksheet study, then whatever data that was previously outputted and not overwritten will still be there. You will have to manually delete that data if you want.

  11. The date and time are placed in column A. The rest of the data items are placed in the subsequent columns.

  12. Enter your formulas in columns K through Z on the worksheet. See further down for a formula example. By default, only the first 4 columns are graphed on the chart. The remaining columns can also be used for worksheet formulas and optionally graphed. To graph these columns, go to the Technical Study Settings window and set Draw Style to the desired type for the corresponding Subgraph. They can also be used for any background calculations required for your analysis method.

  13. The worksheet you should enter your formulas on might be different than the worksheet the chart data is copied to. Please read the Formula Source Sheet Number input description for information about this. When you add the Worksheet Study to a chart, then the Sheet # the chart data is copied to are where your formulas should go unless the Worksheet Study was applied to the chart as part of an existing study collection. In this case the worksheet for your formulas may be different.

  14. The chart data is outputted to the worksheet beginning at row 3. Your formulas must be entered in row 3 when entering the formulas in the formula columns. They will automatically be copied down the column when the chart is recalculated. To recalculate the chart: go to the chart window and select Chart >> Reload and Recalculate on the menu. The Number of Rows input in the Technical Study Settings window sets the number of rows the formula is copied down to. For example: if you enter =E3+2 in K3 and the Number of Rows study input is set to 1000, then it will be copied all the way down to K1002. The reference to E3 will automatically be adjusted for each row.

    If you need more formula columns, then you can use any available column in the worksheet. If you're using a column other than columns K through Z, then you'll need to manually copy your formulas down the column.

  15. Example: To create a five bar moving average of closing prices on the chart: Enter: =AVERAGE(E3:E7) in cell K3. The range E3:E7 is the last 5 closing prices from the chart. To update the chart immediately: Go to the chart and select Chart >> Reload and Recalculate on the menu. The average will be drawn in region number 2. To put it in region 1 over the price bars: Go to the chart window. Select Analysis >> Studies on the menu. Select the name of your workbook in the Studies to Graph list box (the Worksheet Study/System is renamed to the workbook name). Press the Settings button. Set the Chart Region to number 1. Press OK. Press OK.

    This is a very simple example. You can use formulas to perform any calculations you need. See the Working with Worksheets and Workbooks documentation page for complete information on using workbooks and worksheets, creating and entering formulas, and the available functions to use in formulas.

  16. Color Bar Formula Example: If you are creating a worksheet study to color bars based on certain conditions, then your formula should return True or False. For example if you wish to color bars that have a Last Price above 100, then you would use this formula, =E3>100. In the case of coloring bars with one color based on a single formula, you probably would just simply want to use our Color Bar Based on Alert Condition study which will color bars based on the specified Alert Condition which also supports Excel formulas.

  17. If your formula returns a value of zero, then the zero value will not be drawn in the study graph on the chart unless you set the Draw Zeros Input to Yes.

  18. When entering numbers and formulas, keep in mind to use the proper delimiters in your numbers and formulas, according to your Regional Setting. For more information, see the Regional Setting section.

  19. The workbook will need to be saved when you close the workbook or the program so you don't lose any of your formulas. First, go to the workbook window. Select File >> Save on the menu to save the workbook now. You will also be asked to save changes when you close the program or the workbook.

  20. Enter a custom name for any of the available formula columns that you use. To name column K, type the name in cell K2. These names will be displayed as the subgraph names for the study graph in the chart.

  21. If you make changes to worksheet formulas and want to see the results in the chart before the chart is updated, switch to the chart and select Chart >> Reload and Recalculate on the menu to immediately update the chart.

  22. Click on the links to download the Study Collection and Worksheet files.

[Top]

Sharing Your Worksheet Study With Another User

To share your Worksheet studies, please follow the steps below:

When you have your Worksheet study ready, save it as a study collection as explained in the Study Collection section. You may have already done this in one of the steps above.

Now all you have to do is share the study collection file which has a .StdyCollct extension and the Workbook file which has a .scwbf or a .vts extension. Both of these files are located in your Sierra Chart Data folder, you can find the location of this folder under Global Settings >> General Settings. You may also want to link to the "How to Use an Advanced Custom Study or a Study Collection" documentation page for the steps to install your Worksheet study.

[Top]