Spreadsheet Studies Special Tasks
- 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
- 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
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 Price ) 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.
We plan to offer a feature in an upcoming version to reverse the order of the chart data outputted to a Spreadsheet.
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.
Limiting Auto Trading
By using the method of locking the state of a one-time condition, we can use this to limit auto trading.
For an example, save the OneTradeExample.scss file. This file needs to be saved to the Sierra Chart Data Files Folder. To determine the location of this folder, select Global Settings >> General Settings. Refer to the Data Files Folder box for the folder. Once the file is saved, open this file by selecting File >> Open Spreadsheet.
This example Spreadsheet demonstrates limiting auto trading to one Buy Entry and one Sell Entry. The relevant formulas can be found in cells J28, H3 through H8 on Sheet1 and are described in detail below.
- H4: This cell contains a formula which checks for a long position. When there is a long position it becomes TRUE and stays true until reset. The result of this formula needs to be referenced in your Buy Entry formula to prevent a second entry until a reset. Example: =AND (H4= FALSE, [your conditions]).
- H6: This cell contains a formula which checks for a short position. When there is a short position it becomes TRUE and stays true until reset. The result of this formula needs to be referenced in your Sell Entry formula to prevent a second entry until a reset. Example: =AND (H6= FALSE, [your conditions]).
- H8: This cell is used to reset the cells H4 and H6. It needs to be manually set to 1 and then back to zero to do a reset.
- J28: This cell is used to disable auto-trading. When cells H4 and H6 are both true, then auto-trading is disabled. The use of the cell in this particular example is not necessary but it's a good idea.
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.
- Follow the instructions to use the Spreadsheet Study so that the chart data is outputted to a Sheet in the Spreadsheet window.
- 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.
- Select File >> Save As on the menu.
- In the File Type list box at the bottom of the File Save window, select Text Files.
- In the File Name box, type a name that you want to save the text file as.
- Press the Save button to save the file.
- The File will be located in the Sierra Chart Data Files Folder which is specified in Global Settings >> General Settings.
- 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.
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.
These instructions should only be followed to apply a Spreadsheet 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.
- Go to the chart containing the Spreadsheet Study, through the CW menu if it is not already the active chart.
- Select Analysis >> Studies.
- 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.
- On the Settings and Inputs tab, the Chart Data Output Sheet Number Input must be set to 0.
- 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.
- 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.
- Press OK to close the Study Settings window.
- Type a name to save the collection of studies as, in the Save Studies as Study Collection >> Name box.
- 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.
- Follow the instructions to apply a Study Collection to another chart.
- 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.
*Last modified Monday, 22nd May, 2017.