Support Board
Date/Time: Wed, 27 Nov 2024 08:42:00 +0000
References Between Spreadsheets
View Count: 14276
| [2013-06-05 22:06:46] |
| |
|
Hello, We have been developing our own new high-performance spreadsheet functionality in Sierra Chart. This is going to replace the .NET spreadsheet component we currently use. One thing that the new spreadsheet functionality we are developing does not have, is the ability to reference between spreadsheet windows. Now we know some of you are using this kind of functionality, but really there should be an alternative way for you to accomplish what you want by putting all of the data into a single spreadsheet window just using the available Sheets. When using the Spreadsheet Studies, you do have the ability to output the data to any sheet within a Spreadsheet. There is the Chart Data Output Sheet Number input for this. By doing this, you can output all of the data to a single Spreadsheet and use whatever references you need between the different sheets. This is the preferred way. We have a help topic that explains creating a table of Study Values and we are going to update it to use this new suggested method. For those of you are making references between different Spreadsheets, we want to understand why you are doing this, and suggest alternatives. Because it is a much more solid solution for you to make references within a particular Spreadsheet among the Sheets. References outside of a Spreadsheet window just inherently get complex and there is not a proper calculation order and it should be avoided. So therefore, we feel the best solution, is to work on solid alternatives that users will have to migrate to rather than relying on references between Spreadsheets. We believe alternatives are reasonable and should be implemented. And for those, who just do not want to implement alternative, or find it very very difficult, We will see how best to help you, or we would suggest that you just continue to use an older version of Sierra Chart when the new version comes out that supports the new spreadsheet functionality. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2013-06-05 22:10:43
|
| [2013-06-05 23:25:38] |
| Sawtooth - Posts: 4120 |
|
I have built a few spreadsheet systems that reference other spreadsheet Windows. Here are two examples: 1) I color bars in Region 1 with one spreadsheet study, based on formulas in another spreadsheet study that draw lines in Region 2. Even though all study outputs appear in both spreadsheets, not all user columns of each of the 3 spreadsheet studies appear in the all of the other 3 spreadsheet studies so if there are not enough spare user columns I have to reference the other spreadsheet. Expanding the user columns up to 40 has really opened up possibilities (thank you!), but I'm already pushing this limit. So, two questions: 1a) Will the new spreadsheets studies allow more than 40 user columns? 1b) Will the new spreadsheets studies output all user columns to all 3 of the spreadsheet studies? 2) I use the standalone spreadsheets to consolidate user inputs to spreadsheet studies, and sometimes to display spreadsheet study outputs, so inter-referencing spreadsheet Windows is important to me. Several of my clients have systems that use this functionality. Date Time Of Last Edit: 2013-06-06 00:04:45
|
| [2013-06-05 23:54:48] |
| |
|
1a,b . We can increase the number of Subgraphs beyond 40. How many would you need? And those would all be graphable on the chart and therefore show up in Spreadsheet System/Alert studies. If there is anything else you need beyond this or we are not fully understanding what you need, we do not see it as difficult to improve the capabilities of what we offer to easily accommodate what you require , and not add the capability to reference between Spreadsheets windows. 2. We have thought about this kind of usage . What you could do is dedicate a particular sheet within a Spreadsheet for user inputs like Sheet1. And we can always develop something like multiple views into the same spreadsheet to allow you to have separate windows, which will be detachable, to view different Sheets of the Spreadsheet at the same time. 3. With what we read here, we still do not see that references between Spreadsheets are critical. There are reasonable solutions. Would you agree? Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2013-06-06 00:03:15
|
| [2013-06-06 00:01:13] |
| |
|
Prior post has been updated.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-06-06 00:41:40] |
| Sawtooth - Posts: 4120 |
|
1a,b . We can increase the number of Subgraphs beyond 40. How many would you need? And those would all be graphable on the chart and therefore show up in Spreadsheet System/Alert studies. If there is anything else you need beyond this or we are not fully understanding what you need, we do not see it as difficult to improve the capabilities of what we offer to easily accommodate what you require , and not add the capability to reference between Spreadsheets windows.
2. We have thought about this kind of usage . What you could do is dedicate a particular sheet within a Spreadsheet for user inputs like Sheet1. And we can always develop something like multiple views into the same spreadsheet to allow you to have separate windows, which will be detachable, to view different Sheets of the Spreadsheet at the same time. 3. With what we read here, we still do not see that references between Spreadsheets are critical. There are reasonable solutions. Would you agree? 1a. So far I've used 30 columns in my most complex system, but I can see this being exceeded. Would it be better to ask for 60 now, or wait until I need them? 1b. The Spreadsheet Study study is the only one that outputs its user columns to the other 2 spreadsheet studies. This limitation has yet to be, and may never be, a problem because of other available flexibilities. For example, I use a SMA length 1 based on a spreadsheet column to draw lines in other regions, and as the source for overlay studies. 2. If the new spreadsheets have detachable sheet functionality with the ability to view more than one sheet concurrently, this should fill my need. 3. I agree, based on your response to #2. |
| [2013-06-06 01:06:07] |
| |
|
1. We will increase the number of Subgraphs to 60. If necessary we can allow a Spreadsheet Study to display other Spreadsheet studies. Although it may not always work properly due to calculation precedence. It's a very simple modification. 2, 3. OK this is good. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-06-08 00:55:51] |
| |
|
Thank you for this information. We still have to look it over. In general, if there is a need to allow references between spreadsheet windows, we eventually will get to that. It's just not something we can get to right now. And we want to evaluate if there is a critical need for it now. Just one users need is not going to be sufficient. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-06-12 05:05:56] |
| |
|
We have reviewed post #7. We can see the usefulness of being able to reference data between spreadsheets with more sophisticated applications like this. As we said, eventually we will get to the capability to reference data between Spreadsheet Windows. Mainly what we are trying to evaluate now is if there is a critical need in the initial release. So far we believe that there is not and where someone does have a need to do this, they can stay on an older version until this capability comes.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-06-12 12:53:42] |
| Sawtooth - Posts: 4120 |
|
2. We have thought about this kind of usage . What you could do is dedicate a particular sheet within a Spreadsheet for user inputs like Sheet1. And we can always develop something like multiple views into the same spreadsheet to allow you to have separate windows, which will be detachable, to view different Sheets of the Spreadsheet at the same time.
I also use standalone spreadsheets as 'master spreadsheets', and my understanding of this future functionality is that I would be able to detach an unused sheet in the same spreadsheet study to accomplish this. This is within the same chartbook with the same-named spreadsheet study applied to all charts. Trader_TD is doing the same thing but across multiple chartbooks. I, too, would like the ability to consolidate data from multiple chartbooks. |
| [2013-06-12 17:53:39] |
| |
|
Yes, this is the idea.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-06-12 19:08:57] |
| Marmany - Posts: 307 |
|
I have a Quote Spreadsheet set up with streaming data for Last, Net change and volume on a range of commodities into which data from the sheets of another spreadsheet is fed to give further columns showing: cumulative volumes vs prev day on 15min chart total volume previous day vs 2 days back trend on 15 min chart trend on daily chart Inside day proximity to high, mean or low of current day Market open or closed in relation to time bands identified by myself. This Quote Spreadsheet is used as a very effective scanner to determine the best candidates for day trading. I would have no problem integrating this into one spreadsheet provided at least the first page of that could be used in a similar manner to the current Quote Spreadsheet. Please consider this situation. Date Time Of Last Edit: 2013-06-12 19:09:25
|
| [2013-09-21 21:25:01] |
| Sawtooth - Posts: 4120 |
|
I'm sorry to read that the new spreadsheets will not allow circular references. Some of my client's systems use this feature to lock the TRUE state.
|
| [2013-09-21 21:39:44] |
| |
|
The new spreadsheets do allow a cell to reference itself. However separate cells cannot reference each other since there is no logical way to properly calculate that. Our understanding, is that what you are doing is having a cell reference itself.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2013-09-21 21:40:00
|
| [2013-09-22 02:41:47] |
| enemyspy - Posts: 306 |
|
I have a master spreadsheet referencing multiple spreadsheet studies based off of different time frames...will there be a way to place separate time frames on different sheets of a single spreadsheet?
|
| [2014-06-05 15:23:47] |
| Joe Hobart - Posts: 89 |
|
I've attached my multi spreadsheet scenario. I currently have it using a fairly annoying sequence of spreadsheet studies that waterfall to secondary charts via study overlays. I dont directly reference the other sheets within themselves, because i found that it would make the data updates get screwy. I submit for your consideration my usage scenario. Hopefully i included everything you need if you want to play with it. |
Private File  ScreenshotShowingConnections.jpg / V - Attached On 2014-06-05 15:21:03 UTC - Size: 821.61 KB - 1201 views |
| [2014-06-05 17:40:04] |
| |
|
As long as there are no cell references between different Spreadsheet windows, then this will work. It is possible to reference other Sheets within the same named Spreadsheet window, but not between completely different named Spreadsheet windows. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2014-06-05 17:40:31
|
| [2015-03-21 08:18:16] |
| Acro - Posts: 441 |
|
I would like to do what Marmany has done per post #12 above. Is this possible with the new spreadsheets and would I start by creating a normal spreadsheet and putting quotes onto that spreadsheet rather than starting with a quote spreadsheet ?? For example, for my quasi scanner I would like to include relative volume, trend on 15 min chart. |
| [2015-03-23 06:49:48] |
| Marmany - Posts: 307 |
|
Acro, the scanner works as the first page of a "New Spreadsheet" however it is not yet possible to use the sort function. The only work around I found is to manually copy and paste the first page into Excel, and even that looses the conditional formatting (it may be possible to have a VBasic macro re-set the formatting). As I use sort on the spreadsheet several times per day, I continue to run that copy of SC on "Old Spreadsheet". |
| [2015-03-23 06:51:46] |
| Acro - Posts: 441 |
|
Thanks Marmany Forgot we could install both old and new versions of SC Looks like I'll be onto four installations now ! |
| [2021-09-15 11:33:10] |
| AndreyPoroshin - Posts: 104 |
|
I am using Spreadsheets for automated trading. Please tell me if there is a Study platform, which would be able to output from the filtered by volume of Time and Sales, signals of appearance of Bellow Bid Trades and Above Ask Trades? I want to see these signals in Spreadsheets |
| [2021-09-15 15:42:54] |
| John - SC Support - Posts: 36309 |
|
WRT Post #21: Refer to the following study: Time and Sales Volume For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-09-16 06:31:39] |
| AndreyPoroshin - Posts: 104 |
|
unfortunately, this study does not filter volume by Bellow Bid Trades and Above Ask Trades. This is not at all what is required. Judging by the Time and Sales feed, the platform has an algorithm that calculates these volumes, but it is possible to follow this only visually and only in the tape itself. If I use Study T&S Vol, then in the study settings there is no form for sorting Bellow Bid Trades and Above Ask Trades Date Time Of Last Edit: 2021-09-16 06:51:00
|
| 2021-09-16_064538.png / V - Attached On 2021-09-16 06:50:52 UTC - Size: 144.54 KB - 312 views |
| [2021-09-16 14:40:24] |
| John - SC Support - Posts: 36309 |
|
That is the only built-in study that will come close to what you want. Otherwise, you would have to develop (or have developed) a custom study to do what you want.
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-11-15 17:39:46] |
| User337568 - Posts: 60 |
|
Hi: What is the correct reference information for the Master Spreadsheet? I want to create this Master spreadsheet idea, and I get a #REF error in my AGGregation spreadsheet. Here is the error: [ '[ES1439]Sheet1'!E3 ] '[ES1439]Sheet1'!E3 = #REF! (error) I have three spreadsheets: ES1439 NQ1439 AGGChart - intended Master Because the Study Summary Window does not allow text output, I am creating a Master spreadsheet with Text output. I looked on the web and found this excel Workbook reference information: [Workbook_name]Sheet_name!Cell_address I can post the chartbook if desired, but I am lost with how to reference the slave workbooks into the Master. In my case, the Master is "AGGChart." |
To post a message in this thread, you need to log in with your Sierra Chart account: