Displaying a Table of Study Values on a Spreadsheet
This help topic describes the procedure to create a table view on a Spreadsheet showing the current values for one or more studies for multiple symbols. Refer to the image to the right.
The information on this page applies to New Spreadsheets only.
This is accomplished through a chart for each symbol that you want study values for, adding the studies to those charts that you want a table a study values for, applying a Spreadsheet study to each of those charts, and through formulas which reference the study data on a common Sheet within the Spreadsheet.
When following through the instructions below, it is a good idea to be familiar with the Spreadsheet Study.
You also need to have an understanding of Spreadsheet Cell References.
- Select File >> New Spreadsheet on the menu.
- Select File >> Save As. Save this using the filename TableOfStudyValues. You can choose any file name you want but make a note of it for another step.
- This Spreadsheet will contain one Sheet. The name will be Sheet1. Select Spreadsheet >> Rename Sheet. Enter the name Table and press OK. The name can be anything other than Sheet followed by a number.
- Open a chart through File >> Find Symbol for one of the symbols that you want to have a table of study values for.
- Add the studies that you want to get a table of study values for, to this chart. Refer to Adding/Modifying Chart Studies.
- Add the Spreadsheet Study to this chart. Open the Study Settings window for this study and in the Spreadsheet Name box, enter the name of the Spreadsheet that you saved in one of the above steps. By default this would be TableOfStudyValues.
- If you do not need to look at much historical data in this chart, then it is recommended that you set the chart to load the minimum amount of data by setting Days to Load in Chart >> Chart Settings to a small number.
- Use Chart >> Duplicate Chart to duplicate the chart the number of times that is equal to the number of symbols that you want to get study values for, minus 1. All of the charts need to be within the same Chartbook. The chart windows can also be minimized if they do not need to be viewed.
- Go to each of the duplicated charts and change the symbol to one of the symbols that you want to have in the table of study values. Refer to Changing the Symbol of a Chart for instructions.
- Go to the Spreadsheet through the CW menu. At the top left of the Spreadsheet select Table.
- In Row 1 in columns A, B, C, enter the following names in each of these columns respectively: Symbol, MACD, MACD Difference. Refer to the image at the top right for an example.
- In Row 2 in columns A, B, C, enter the following formulas in each of these columns respectively =Sheet2!$A$1, =Sheet2!$AA$3, =Sheet2!$AC$3. Refer to the image at the top right for an example. This step will need to be repeated for each row for each chart. The part of the formula you will need to change is the Sheet reference (Sheet#).
- In the next two steps there are links to example files. These files need to be saved to the Sierra Chart Data Files Folder. You can determine where this folder is located by selecting Global Settings >> General Settings. The Data Files Folder box displays the folder.
- TableOfStudyValues.cht: This is an example Chartbook file based upon the steps above. It contains charts with the studies we want to create a table of study values for. The study data is outputted to the Spreadsheet by using the Spreadsheet Study. Each chart has the MACD study on it. You will notice that the values for MACD study begin at column AA in the Spreadsheet.
- TableOfStudyValues.scss: To open this Spreadsheet select File >> Open Spreadsheet and select it from the list. This is the Spreadsheet based upon the steps above.
Why Direct Support for Study Values Is Not Supported on the Quote Board
The concept of study values on the Quote Board without an associated chart has no reasonable method to the implementation.
Some questions about this are:
- Historical data is required but Quote Boards do not have historical data. Therefore, at least one chart object which loads historical data, is required in the background for each symbol.
- How can studies be calculated without a chart object? They cannot be. A chart is required.
- What are the Study Settings for the study values? It is necessary to set Study Settings and this requires a chart object which supports Chart Studies and Study Settings windows.
- What are the Chart Settings for the chart? How much historical data is needed? This requires a chart and also requires the Chart Settings window.
- Is there any missing data in the historical data the calculations are performed on? This requires a chart to visualize this.
- In the case of a futures contract near rollover, what about the historical futures data for the prior contract? Does this data even exist? What is the rollover date? Is it back adjusted? This requires a chart to configure the settings and to ensure there is no missing data.
- If a Sierra Chart user says there is something wrong with a study value on a Quote Board, first how would they obviously know this to begin with just by looking at it in isolation without seeing it on the chart and seeing the underlying price bars? The answer is they cannot. The next question is what is the historical data it is based on? Is it complete and does it have any data errors? What about back adjustment for futures data? All of this is exceptionally difficult to even answer to the literal point of insanity without having a chart!
A chart object with Chart Settings and Study Settings windows is absolutely is required for studies on Quote Boards. There is no way around this. Therefore, the implementation of this feature does potentially use a lot of memory, it is very complex to implement, has numerous problems and is insane to support.
It is not considered a legitimate feature to develop or offer.
In consideration of all of these things, there is no possibility this would ever be developed. Therefore, please do not contact Sierra Chart Support about this.
What we do plan to implement, is a Study Summary window which will show in a table view the current values of a selected set of studies among the studies in a Chartbook. This is expected to be completed before the end of 2017.
*Last modified Monday, 09th October, 2017.