Login Page - Create Account

Support Board


Date/Time: Fri, 21 Sep 2018 05:09:29 +0000



References Between Spreadsheets

Support Request:
[2013-06-05 22:06:46]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
Date Time Of Last Edit: 2013-06-05 22:10:43
[2013-06-05 23:25:38]
tomgilb - Posts: 1945
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]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
Date Time Of Last Edit: 2013-06-06 00:03:15
[2013-06-06 00:01:13]
Sierra Chart Engineering - Posts: 63920
Prior post has been updated.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2013-06-06 00:41:40]
tomgilb - Posts: 1945
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]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2013-06-06 11:15:32]
Trader_TD - Posts: 1
"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."

Actually, the way I use the SS, there is no way to do this.

I'm not currently a direct subscriber to SC, but over the last three years I have subscribed to either package 3 or 5 more time than I have not, so I hope you'll consider my input. Currently I am only trading futures on Transact and find their package adequate for now, but have always expected to re-subscribe for the Forex feed at some point (like others I find the FXCM pip spreads to be ridiculous at this point, but that's another topic).

Removing this particular SS capability may keep me from re-subscribing. I find the ability to create what I call a "Master Spreadsheet" that displays data from numerous other SS's in different chartbooks one of the core capabilities that I haven't seen in any other package.

I like to trade using certain confluences of timeframe and indicator data the gives me a basis to know which currencies or futures are worth looking at for a possible trade. For example: Is the current price above or below the Daily Pivot? Has the Average Daily Range been met? What are the trends on several timeframes?

To do this I set up a chartbook for each currency with a spreadsheet for each chartbook, and use SS formulas to display conditions like "ADR Not Reached", "Trend is Up", "Above the Pivot", "Today's Range", etc. I will then create a final sheet, not connected to a chart, that copies the final output from each individual chart into a readable table, or "dashboard". Final logic on this sheet may then read something like: "Look for a Long Entry", or "No Trade Setup".

I then create a "Master Spreadsheet" that is NOT connected to any chart or chartbook. Each currency gets row/columns that displays a summary from its source as well as additional logic that tells me whether I want to consider looking for a trade in that currency. All final summaries are displayed on this sheet, and I can bring it up regardless of which chartbook I'm looking at. Everything is color coded as well, i.e Green if Bullish, Red if Bearish.

The result is that, at a glance, I can tell which currencies I may want to trade, as well as those I don't. Takes a lot of work to set up, but once done, saves massive amounts of time. I no longer have to scan several charts on every currency just to get started. And if the conditions change, there they are in real-time.

And I accomplished all this without programming. Just SS language, much of which I knew already and built upon.

Needless to say, losing this capability would be a huge negative in my book, not an improvement. I hope you'll reconsider. I don't consider "not upgrading" to be much of an option as it would only be temporary - eventually there would be something that would require it, no doubt.

Perhaps this is not what you intended the SSs to be used for, but that's the thing about tools - they tend not to have just a single use.

Any way, thats my input.

Regardless of the direction you go, thank you for soliciting feedback.
[2013-06-08 00:55:51]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2013-06-12 05:05:56]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2013-06-12 12:53:42]
tomgilb - Posts: 1945
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]
Sierra Chart Engineering - Posts: 63920
Yes, this is the idea.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2013-06-12 19:08:57]
Marmany - Posts: 174
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]
tomgilb - Posts: 1945
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]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
Date Time Of Last Edit: 2013-09-21 21:40:00
[2013-09-22 02:41:47]
enemyspy - Posts: 262
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
imageScreenshotShowingConnections.jpg / V - Attached On 2014-06-05 15:21:03 UTC - Size: 821.61 KB - 496 views
[2014-06-05 17:40:04]
Sierra Chart Engineering - Posts: 63920
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. If possible please keep your questions brief and to the point. Please be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
Date Time Of Last Edit: 2014-06-05 17:40:31
[2015-03-21 08:18:16]
Acro - Posts: 207
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: 174
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: 207
Thanks Marmany

Forgot we could install both old and new versions of SC

Looks like I'll be onto four installations now !

To post a message in this thread, you need to login with your Sierra Chart account:

Login


Login Page - Create Account