Spreadsheet Studies Special Tasks

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

On This Page

• Reversing The Order Of Chart Data In The Spreadsheet
• Locking the State of a One Time Condition
  • Limiting Auto Trading
• Accessing Tick by Tick Data
• Exporting Sheet Data Containing Price and Study Data to Text File
• Displaying Result of Spreadsheet Formula as Text on Chart
• Applying Configured Spreadsheet Study on Other Charts
• Using Different Formulas or Data at Each Row of the Formula Columns (K-Z)
• Using the I and J Columns

Reversing The Order Of Chart Data In The Spreadsheet

It is possible to reverse the order of chart data through cell references when using the Spreadsheet studies. For example, you can use an unused column, like column CA, and fill it with references to column E (Closing Prices) or whatever column you need. If you have the Number of Rows input set to 500, then in cell CA3 you would enter =E502, cell CA4 would be =E501 and so on. Your formulas can then refer to this new CA column.

Locking the State of a One Time Condition

You may have a condition which occurs once and you want to continue to return a TRUE value in your System/Alert formula even if that condition no longer is present.

This can be accomplished by checking the return value from a formula within that same formula. Here is an example: =OR(E3=10,K3>0). This formula is located in cell K3. We check to see if a cell E3 equals 10 and if it does at least once, we will continue to return TRUE.

This formula uses a reference back to itself. This is properly supported with New Spreadsheets.

The method by which you can reset the locked state is by using this formula: =AND(OR(E3=10,K3>0),H3=FALSE) which references a cell (H3) which can be manually set to TRUE (1) or FALSE (0) . To perform a reset set H3 to TRUE, and then back to FALSE.

Accessing Tick by Tick Data

To access 1 Tick data in a Spreadsheet or what also is known as Time and Sales data, then this is accomplished by setting the chart and 1 trade per bar. To do this, select Chart >> Chart Settings. Set Intraday Bar Period >> Bar Period Type to Number of Trades Per Bar and the corresponding setting to 1.

Each row in the Spreadsheet will be a single trade. In order to be certain you have tick by tick data, refer to Tick by Tick Data Configuration.

You can also add the Bid Volume and Ask Volume studies to the chart to access that data as well. This will give you an indication of whether the trade occurred at the bid or the ask. The data for those studies will appear at column AA on the Spreadsheet, by default.

Exporting Sheet Data Containing Price and Study Data to Text File

When using one of the Spreadsheet Studies, the Spreadsheet Sheet the chart data is outputted to will contain the main price graph and study data from the chart.

This data can be saved to a tab delimited text file and used in other programs. Follow the instructions below to do this.

1. Follow the instructions to use the Spreadsheet Study so that the chart data is outputted to a Sheet in the Spreadsheet window.
2. Using the list box at the top left of the Spreadsheet window, select the particular Sheet the chart data is outputted to. This will be the same as the Chart Number. For example, Chart #3 will be outputted to Sheet3.
3. Select File >> Save As on the menu.
4. In the File Type list box at the bottom of the File Save window, select Text Files.
5. In the File Name box, type a name that you want to save the text file as.
6. Press the Save button to save the file.
7. The File will be located in the Sierra Chart Data Files Folder which is specified in Global Settings >> General Settings.
8. You can then do with the text file what you require in any other program. All of the main price graph data and study data will be located in that file. The columns of data in the text file from the Sheet are separated by tab characters.

Displaying Result of Spreadsheet Formula as Text on Chart

The following files demonstrate using a Spreadsheet to display the result of a formula on the chart as text. In this case, the formula is in cell K3.

• ValueAsTextOnChart.StdyCollct
• ValueAsTextOnChart.scss

Applying Configured Spreadsheet Study on Other Charts

Follow the instructions below to apply a Spreadsheet Study and any related studies you have already set up on one chart, to another chart.

This way, the same Spreadsheet Study configuration which also specifies a particular Spreadsheet and the configuration of other studies on the chart, can be applied in one step to another chart.

These instructions should only be followed to apply a Spreadsheet Study, Spreadsheet System/Alert, Spreadsheet System for Trading study and related studies to a chart within the same Chartbook. If the chart is in a different Chartbook, then this would be very inefficient if chart data from two different charts in different Chartbooks is being outputted to the same Sheet within the same Spreadsheet.

1. Go to the chart containing the Spreadsheet Study, through the CW menu if it is not already the active chart.
2. Select Analysis >> Studies.
3. Open the Study Settings window for the Spreadsheet Study on the chart by selecting that study in the Studies to Graph list and pressing the Settings button.
4. On the Settings and Inputs tab, the Chart Data Output Sheet Number Input must be set to 0.
5. Verify the Formula Source Sheet Number is set as you require. Refer to the documentation for this Input to fully understand it. Normally it should be set to the same number as the Chart Number which contains the Spreadsheet Study.
6. Set the Copy Column J from Formula Source Source Sheet Input to Yes, in the case of the Spreadsheet System for Trading study to copy the read/write cells from column J to the corresponding cells in the Sheet to be used by the chart the Study Collection will be applied to.
7. Press OK to close the Study Settings window.
8. Type a name to save the collection of studies as, in the Save Studies as Study Collection >> Name box.
9. Press the Save Studies as Study Collection >> Save All command button to save all the studies as a Study Collection. It is necessary save all of the studies.
10. Follow the instructions to apply a Study Collection to another chart.
11. When applying this Study Collection to another chart, the chart data will be outputted to a different Sheet number within the same Spreadsheet which has been specified with the Spreadsheet Name in the Study Settings window with the Spreadsheet Study saved in the Study Collection.
    
    Example of what you should see: If you apply the Study Collection to Chart #3, then the chart data will be outputted to Sheet3. If the Formula Source Sheet Number Input is set to 1, then the formulas from Sheet1 will be copied to Sheet3.
12. If you have applied the Study Collection to a different Chartbook and to a chart with the same Chart Number as the original chart the Spreadsheet Study is contained within in the other Chartbook, then after applying the Study Collection to that chart it is necessary to set the Chart Data Output Sheet Number Input setting with the Spreadsheet Study to a number which is different than the Chart Number of the original chart the Spreadsheet Study is contained within. This is so that the chart data is outputted to a different Sheet within the Spreadsheet window so there are no conflicts.

In no case should you ever directly exactly duplicate a Spreadsheet Study with identical settings, on the same chart by using the Duplicate button on the Chart Studies window. What will happen is each instance of the Spreadsheet Study will use the same Sheet and Spreadsheet and will contain an additional output of the same chart data and studies. This is not only inefficient, it is going to cause conflicts.

The duplicated Spreadsheet Study must output data to a different Sheet within the Spreadsheet by using a different Chart Data Output Sheet Number Input setting with the Spreadsheet Study.

Using Different Formulas or Data at Each Row of the Formula Columns (K-Z)

Follow the instructions below to use a different formula or data value at each row in one of the Spreadsheet Study Formula Columns. These are K through Z if the Number of Formula Columns Input with the Spreadsheet Study is set to 16. If this input is set higher, more columns will be used as the Formula Columns.

1. Go to the Spreadsheet Sheet which is being used by the Spreadsheet Study. The Spreadsheet can be accessed through the CW menu. And the particular Sheet can be selected with the list box at the top left of the Spreadsheet window.
2. After the last used Formula Column on the Sheet (By default this is Z), find an available column that is not being used by an outputted study. Consider the other studies you may add to the chart later on which will use the columns beginning at AA, assuming 16 Formula Columns.
3. Beginning at row three in the column you have determined in the prior step, enter the particular formula or data value that you want to display the result of or want to display, on the chart through the Spreadsheet Study.
4. In one of the actual Formula Columns, enter a formula which references the particular formula or data value entered in the prior step. For example, if you entered formula or data value at BB3, then and cell K3 enter the formula =BB3. This formula will automatically copy down and reference the data entered in column BB. You will see the results displayed by the Spreadsheet Study on the chart.

Using the I and J Columns

When using one of the Spreadsheet Studies, Sierra Chart outputs data to the I and J columns in the Sheet. You need to avoid using the same cells in those columns that Sierra Chart is using.

Additional cells within those columns can be used if they are not used by Sierra Chart. However, we recommend keeping any cells that you use, at least 20 rows away from any existing cells used by Sierra Chart just to allow for future expansion by Sierra Chart.

There is also the H column which can be used within the Sheet and is not used by Sierra Chart.