Home >> Support Board

Support Board

Date/Time: Mon, 29 Apr 2024 09:11:52 +0000

How to Post a Message for Support

[User Discussion] - tomgilb

View Count: 2699

[2014-02-17 17:10:38] #1 [Go To First Post]
User83762 - Posts: 61
TomGilb, on the old support board, in the post entitled "DRAWING RECTANGLE BY SPREADSHEET? ", you posted a spreadsheet file, spreadsheet study and collection file. The input spreadsheet file no longer has any data in it. Can you post a screenshot of it, so I can see where you placed the data? Also, can you post a copy of the study file in Notepad so I can learn how you coded it? Thanks.

[2014-02-17 18:46:32] #2 [Go To First Post]
Sawtooth - Posts: 3982
This thread mentions drawing rectangles with a spreadsheet, and drawing zones with a spreadsheet. Which are you interested in?: http://www.sierrachart.com/supportboard/showthread.php?t=40825 Here's a re-post of the rectangles example: Put the attached in your Sierra Chart \Data folder, click on Rectangle- The formulas are in cells K3-P3 on the spreadsheet. The relevant Draw Styles are on the study's Subgraphs tab, SG1-SG6. If you want to draw zones, the spreadsheet example at the above link is user-specific. Zones can also be drawn using the Horizontal Lines study, with Fill Top/Fill Bottom draw styles of paired lines.


Rectangle.scwbf - Attached On 2014-02-17 18:36:14 UTC - Size: 294.2 KB - 534 views Rectangle-.StdyCollct - Attached On 2014-02-17 18:38:14 UTC - Size: 7.67 KB - 401 views

[2014-02-17 19:03:00] #3 [Go To First Post]
User83762 - Posts: 61
I was interested in the zones example-where the data was entered in the SC spreadsheet, and the code for the study. Ultimately, I'm interested in programmatically reading dated, comma delimited text files, and displaying date specific zones for each day, but your code will be a good starting point. (I don't see any way to open a study file's code, so I guess I would need the code.). Thanks again.

[2014-02-17 19:53:25] #4 [Go To First Post]
Sawtooth - Posts: 3982
OK, here's a re-post of the zones study example, on the ES when it was in the 1400s. The Zones input.scwbf is the standalone spreadsheet where you enter your values. This spreadsheet is referenced by the Zones output.scwbf spreadsheet study that draws the zones. The Zones- study collection will add the Zones output.scwbf spreadsheet study to the chart, but the Zones input.scwbf needs to be loaded separately at File >> Open Spreadsheet. Now that the Number Of Formula Columns has been increased to a possible 60, you could draw up to 30 zones with one spreadsheet study.


Zones-.StdyCollct - Attached On 2014-02-17 19:38:13 UTC - Size: 7.67 KB - 405 views Zones output.scwbf - Attached On 2014-02-17 19:38:34 UTC - Size: 39.85 KB - 416 views Zones input.scwbf - Attached On 2014-02-17 19:38:48 UTC - Size: 6 KB - 416 views

[2014-02-17 20:16:31] #5 [Go To First Post]
User83762 - Posts: 61
How can I see the code in the study?

[2014-02-17 20:45:55] #6 [Go To First Post]
Sawtooth - Posts: 3982
The zone-drawing formulas are in cells K3 to Z3 on the Zones output spreadsheet, referencing the Zones input spreadsheet cells. There is no code on the Zones input spreadsheet; it's just user data. This example is semi-automatic and requires manual or copy/paste input from the user. The automatic part is the drawing of the zones.

[2014-02-17 23:02:15] #7 [Go To First Post]
User83762 - Posts: 61
A few follow up questions: Can the writing of data back to the output file be prevented; is it necessary, it must create overhead?? How can I add more formulas in the columns beyond Z to use 30 zones; they fill up with data from other studies? I see the formulas in the spreadsheet to indicate which input file cells to reference, but there must be code somewhere to draw the zones; where? Each time I change the zone output settings & inputs and subgraph colors and save them as default, reloading the zones indicator from the collection, sets everything back to the original. Thanks.

[2014-02-17 23:33:14] #8 [Go To First Post]
Sawtooth - Posts: 3982
Can the writing of data back to the output file be prevented; is it necessary, it must create overhead?? You could conditionally prevent a value with a more complex formula, but if you keep the Number of Rows to the default 1000, the overhead will remain minimal. If you don't want to draw a zone, blank or zero the cell pair on the Zones input spreadsheet. How can I add more formulas in the columns beyond Z to use 30 zones; they fill up with data from other studies? Set the Number Of Formula Columns to 60, then set the Draw Style pairs of the remaining subgraphs. The other study outputs will move to column BS and above. To remove the old study output data in columns AA and above, highlight the columns and press DEL (before you put formulas there). I see the formulas in the spreadsheet to indicate which input file cells to reference, but there must be code somewhere to draw the zones; where? Using the values returned by the spreadsheet formulas, the zones are drawn by the Draw Styles on the spreadsheet study's Subgraphs tab. Columns K-Z...BR are SG1-SG16...SG60 respectively. Each time I change the zone output settings & inputs and subgraph colors and save them as default, reloading the zones indicator from the collection, sets everything back to the original. Applying a study collection will override a saved default. Instead of saving a default, 'Save Single' the spreadsheet study as a study collection. This is done when viewing the Studies to Graphs list by highlighting only the spreadsheet study.

[2014-02-18 02:20:07] #9 [Go To First Post]
User83762 - Posts: 61
Thanks. I notice that you entered A1 and B1 in the last columns Y and Z, instead of placing the highest levels in the first two columns, K and L; any reason for that order? Is there a way to enter the formulas other than pasting a current cell's formula (for example, ='[Zones input.scwbf]Sheet1'!$B$1) and manually changing the numbers (for instance in Excel, you can drag cells and the formulas will increment and populate at the same time)? Thanks again.

[2014-02-18 04:58:15] #10 [Go To First Post]
Sawtooth - Posts: 3982
I notice that you entered A1 and B1 in the last columns Y and Z, instead of placing the highest levels in the first two columns, K and L; any reason for that order? No reason. In hindsight, I'd do it as you suggest, especially since you are going to use all 60 columns. Is there a way to enter the formulas other than pasting a current cell's formula (for example, ='[Zones input.scwbf]Sheet1'!$B$1) and manually changing the numbers (for instance in Excel, you can drag cells and the formulas will increment and populate at the same time)? The only thing you change is the value in cells in the Zones input spreadsheet. You could just enter values in row 3 of the Zones output spreadsheet. The only reason this example uses the Zones input spreadsheet is because that user was pasting from another file. The absolute value in the formula ($B$1) is necessary because the spreadsheet study will advance the row# in rows below, and the value needs to be the same in all rows so that a horizontal zone is drawn.

[2014-02-18 20:08:55] #11 [Go To First Post]
User83762 - Posts: 61
You've been very helpful, learned a bunch. Not sure why SC doesn't have a study to read a spreadsheet for just drawing, without outputting any data, but it works. Much obliged.

[2014-02-18 23:28:34] #12 [Go To First Post]
emp0804 - Posts: 31
Tomgilb, This is FANTANSTIC!!!!! I opened a ticket last week and got the usual read the documentation to input zones from a spreadsheet. Is there a way to flag the zones as a drawing? The reason I would like to attach the spreadsheet to one chart and use the option to Copy Chart Drawings in chart settings for multiple charts in my chartbook? Or should I not worry about the overhead attaching the spreadsheet to multiple charts (6), as I would be drawing about 8-12 zones? THANKS ONCE AGAIN!!!!!!!

[2014-02-19 02:09:34] #13 [Go To First Post]
Sawtooth - Posts: 3982
Is there a way to flag the zones as a drawing? The reason I would like to attach the spreadsheet to one chart and use the option to Copy Chart Drawings in chart settings for multiple charts in my chartbook? Or should I not worry about the overhead attaching the spreadsheet to multiple charts (6), as I would be drawing about 8-12 zones? A study cannot be also be a drawing. If you want the same zones on other charts in the chartbook, use the Study/Price Overlay study on the other charts. You don't need a spreadsheet study on each chart, only on the source chart.

[2014-02-23 19:44:29] #15 [Go To First Post]
Sawtooth - Posts: 3982
The zones example in post #4 is semi-automatic. It will not automatically work in a backtest. It is meant for realtime use. IOW, it will not do what you want.

[2015-09-30 18:45:44] #16 [Go To First Post]
Trader Ra - Posts: 87
judging by the date of the last posting, am i correct to assume that the files here are no longer compatible with the new spreadsheet system? would love to be able to simplify drawing zones! Date Time Of Last Edit: 2015-09-30 18:46:10

[2015-09-30 20:45:46] #17 [Go To First Post]
Sawtooth - Posts: 3982
Unfortunately the New Spreadsheets version does not support referencing separate spreadsheet file names, so the spreadsheets as posted in post #4 are not directly convertible to the New Spreadsheets. You could use a blank sheet in the New Spreadsheets for the Zones input, and then edit all of the references in Zones output to point to the input sheet. IOW, it's possible with a rework. The example in post #2 of how to draw a rectangle with a spreadsheet study should convert directly to the .scss of New Spreadsheets.

[2015-10-01 01:08:38] #18 [Go To First Post]
Trader Ra - Posts: 87
thanks for that.. ill try converting directly first.. i know nothing about coding spreadsheets but am willing to spend time learning it as i really want to simplify what im doing so might start a project on this for the weekends. I want to use rectangles and extending rectangles to draw zones on my charts or as you suggested in the old thread to draw lines with fill top/bottom options. i like that idea. now with horizontal level drawing tool, that idea might work better. there's no easy way of doing all this at present other than drawing it all in manually which is really time consuming esp. with the limited drawing properties for these drawing tools. what i'd really like to do is code up a study like you did that can import automatically the latest zones file and draw them up on the charts. Also, i would want all the older files to remain on the charts so i can review my zones at a later date. - create a new zones file daily where i input all the price levels (older files not to be overwritten) - a study reads the price levels from the spreadsheet file - the study allows me to modify drawing properties like color, line style, width, names, text labels, etc - refresh charts and new zones should be there. - yesterday and previous days zones still show on the charts (they just end at the start of new session or specified time) i like the idea of using either rectangles or horizontal levels that have fill options between price levels for previous days and maybe extending rectangles or horizontal rays for the present day so that the drawing extends to the right edge of the chart to make them visible. hope that makes sense. my question is simply .. is this doable / codeable through the spreadsheet study feature in SC? if so, then im willing to learn it. Not sure is SC Support will read this post as its marked user discussion. I'd like their input as well on this. Date Time Of Last Edit: 2015-10-01 01:09:34

[2015-10-01 01:32:15] #19 [Go To First Post]
Sawtooth - Posts: 3982
What you want to do might be a little beyond the capability of a spreadsheet study. -there is no output to a spreadsheet from a manual drawing. -a spreadsheet study has a max of 60 formula columns so you could only have a max of 30 zones. -a spreadsheet study cannot draw anything into the future; only to the current bar -a spreadsheet study will only draw into the past based on the Number of Rows (chart bars) setting, and a large setting can cause lag in real time. -you can use any Draw Style but names and text labels are limited to the y axis.

[2015-10-01 01:55:50] #20 [Go To First Post]
Trader Ra - Posts: 87
thanks for getting back to me -there is no output to a spreadsheet from a manual drawing. this shouldnt be an issue as i dont intend to output drawings onto a spreadsheet. more like the other way around. spreadsheet is read by a study that in turn draws the zones -a spreadsheet study has a max of 60 formula columns so you could only have a max of 30 zones. that's more than enough on a daily basis. over time, the total number of zones on a chart would indeed exceed 30 but not on a daily basis if all i do is create one new spreadsheet file per day that is read by the study -a spreadsheet study cannot draw anything into the future; only to the current bar -a spreadsheet study will only draw into the past based on the Number of Rows (chart bars) setting, and a large setting can cause lag in real time. bummer! lol i wanted the drawings from the most current file to extend into the unfilled space to the right edge. i was also concerned about the potential lag caused by the large number of zones that build up over time on a chart. looks like i may need to think of ways around this issue or hire a programmer to code this in acsil if that's possible. thanks anyway. Date Time Of Last Edit: 2015-10-01 01:56:49

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