Login Page - Create Account

Support Board


Date/Time: Fri, 29 Mar 2024 16:03:55 +0000



[User Discussion] - how to add a list of vertical lines to a chart?

View Count: 1834

[2018-09-13 22:35:56]
whats1thingnow - Posts: 407
Hi support,

As per of my daily review, I want to chart vertical lines for entries and exits from my other platform.

From my other platform, I can export the entry date/time and exit date/time.

If I want to import a list of vertical time lines everyday, what is the easiest and fastest way to do this?

Time Line doesn't work I don't think because I have different dates/times everyday.

Would the Overview of Spreadsheet Studies be able to do this?

If so, is there an example to follow?

Thank you!
[2018-09-16 22:08:27]
whats1thingnow - Posts: 407
bump - anyone have experience with this?
[2018-09-19 21:30:11]
whats1thingnow - Posts: 407
this would also be useful for adding news events in my opinion

as the INTRADAY chart scrolls forward, you see a red line with the event name appearing will remind you of the event you care about is coming up in a few minutes
Date Time Of Last Edit: 2018-09-19 21:30:31
[2018-10-16 02:23:57]
whats1thingnow - Posts: 407
any ideas?
[2018-10-16 05:35:48]
Sawtooth - Posts: 3953
This could be done using the Spreadsheet Study study. There is no known example to follow.
Limitations:
-The spreadsheet study cannot place a text label at the vertical line.
-Maximum of 60 time windows.
-Times would need to be updated daily.
-There is no vertical line Draw Style. You'd have to use the Background Draw Style.
-For exact alignment, this will work best on time-based price bars.
-Some spreadsheet experience is recommended.

This is the basic setup:
-Each sheet is associated with a chart#, and on this sheet would be formulas to draw the vertical background between the start and end time when TRUE.
-A blank sheet would be added to the spreadsheet that is not associated with a chart#, and on this sheet one column would be for start times and another column for end times. The formulas on the chart's sheet would reference these times and compare them to the bar timestamp. You could manually enter the start and end times or paste them from Excel or a text doc.

If you are interested in this solution, post again and I'll give specific instructions.
[2018-10-18 02:54:10]
whats1thingnow - Posts: 407
thank you @tomgilb

i'm very interested in this solution!

too bad there is no vertical line Draw Style... seems almost silly we don't have this option
[2018-10-18 15:36:25]
Sawtooth - Posts: 3953
Add the Spreadsheet Study study.
-Set the Chart Region to 1.
-Give it a name in the Spreadsheet Name field.
-Set the Number of Formula Columns to 60.
On the Subgraphs tab:
-Set the Draw Styles of every subgraph to Background (or Background Transparent), with a width of 2.
-Set the colors of every subgraph.
-Click OK and OK.

When the spreadsheet appears, go to Spreadsheet >> Add Sheet and give the added sheet a unique name, e.g. VertTimes
-Use the dropdown in the upper left corner of the spreadsheet and select the chart's Sheet#.
-Go to Spreadsheet >> Delete Sheet.
-Focus the chart and go to Chart >> Recalculate. The chart's Sheet# will reappear.
(The above substeps will put the added sheet always at the top of the dropdown list).

Use the dropdown in the upper left corner of the spreadsheet and select the added sheet.
-Put today's date in cell A1, e.g. 2018-10-18
-Cells B1 and C1 are column titles: Start time and End time, respectively.
-Enter 1 in cell A2.
-Put this formula in A3: =A2+1
-Focus cell A3 and go to Spreadsheet >> Copy Formula.
-Highlight cells A4:A61 and go to Spreadsheet >> Paste.
-Enter or paste your start and end times in columns B and C, respectively. Use the hh:mm:ss format. They do not need to be in any order. They, and cell A1, will need to be updated each day.

Use the dropdown in the upper left corner of the spreadsheet and select the chart's Sheet#.
-Put this formula in cell K3:
=AND(INT(A3)=VertTimes!$A$1,FRACTIME(A3)>=VertTimes!$B$2,FRACTIME(A3)<VertTimes!$C$2)
-Replicate this formula in columns L3:BR3, incrementing the row references of columns B and C of the added sheet, e.g. in L3:
=AND(INT(A3)=VertTimes!$A$1,FRACTIME(A3)>=VertTimes!$B$3,FRACTIME(A3)<VertTimes!$C$3)

Use attached chartbook and spreadsheet file as an example.
Date Time Of Last Edit: 2018-10-18 19:17:31
attachmentVertical Line at Time.Cht - Attached On 2018-10-18 15:22:55 UTC - Size: 29.97 KB - 421 views
attachmentVertical Line at Time.scss - Attached On 2018-10-18 15:23:09 UTC - Size: 37.21 KB - 358 views
[2018-10-19 02:03:01]
whats1thingnow - Posts: 407
thank you so much tomgilb!

just tried your example but unfortunately i'm not seeing the vertical lines on the chart. from what i can see, the formulas added to K3 and L3 on 'Sheet1' are supposed to extract the time specified on 'VertTimes' and plot it on chart 1?

however, i don't think 'Sheet1' is successful extracting the value 'VertTimes'? please see attached

not sure what i'm doing wrong. thank you for your guidance
image2018-10-18_1856.png / V - Attached On 2018-10-19 02:00:23 UTC - Size: 116.39 KB - 379 views
[2018-10-19 04:34:34]
Sawtooth - Posts: 3953
Yes, the K3 and L3 formulas on Sheet1 are referencing the times on the VertTimes sheet.

See attached screenshot. I used the times you used in your pic.

Thoughts:
-If you change the times on the VertTimes sheet, you'll need to focus the chart and go to Chart >> Recalculate.
-You may need to increase the Number of Rows in the spreadsheet study settings to allow for enough bars for the entire day.
imageVertical Line as Time.PNG / V - Attached On 2018-10-19 04:34:06 UTC - Size: 87.03 KB - 433 views
[2018-10-19 15:51:52]
whats1thingnow - Posts: 407
ok cool, will try when i get home again.

do you mind attaching the .cht and .scss of your working example?

thank you so much!!!

You may need to increase the Number of Rows in the spreadsheet study settings to allow for enough bars for the entire day.

yes, i am using 1 minute chart right now and do see the times i specified on the chart's sheet ... i wonder if it will be better if i use a small tick chart instead
[2018-10-22 04:27:30]
whats1thingnow - Posts: 407
it's working now!!!!!!!!!!!

'Background Transparent' works great!!!!!!!!!!!!!!!

review trades working great :)

10 minute warning for econ event works great tooooooooooooooooooo

thank you so much tomgilb
[2021-07-26 23:49:09]
SierraFan - Posts: 84
tomgilb, Can u suggest the simplest way to place a vertical line at a specific date? Time isn't important. I would like to use Historical, Continuation charts and add dates(vertical lines) of every full moon for the life of contract or as far back as sierra allows. Then apply the vertical lines to whatever market I decide to chart. Hope this makes sense? Thanks in advance!
[2021-07-27 13:07:00]
Sawtooth - Posts: 3953
Assuming the chart bars are daily bars, you could:
Add the Spreadsheet Study study
- Give it a name in the Spreadsheet Name field
- Set the Chart Region to 1
- Check 'Draw Study Underneath Main Price Graph'
- Set the 'Number of Formula Columns' to 60
- Set the Draw Style to Background, and the Width/Size to 2, of all 60 subgraphs
- Use a formula like this in row 3 of any column, K to BR:
=INT(A3)=DATEVALUE("07/23/2021")

Alternatively, you could create a list of dates in column H, and use a formula like this in row 3 of any column, K to BR:
Cell K3: =INT(A3)=$H$1
where the date in H1 is in this format:
2021-07-23
Increment the referenced column H cell for each column, K to BR, e.g.:
Cell L3: =INT(A3)=$H$2
Cell BR3: =INT(A3)=$H$60

The max number of columns is 60, so the max number of dates is 60.
Dates do not need to be in order.
Date Time Of Last Edit: 2021-07-27 20:00:17
[2021-08-08 02:02:12]
SierraFan - Posts: 84
tomgilb, Thank You! Heres some full moon vert. lines applied to ZB.


http://www.sierrachart.com/image.php?Image=1628388021657.png
[2021-08-08 02:24:07]
SierraFan - Posts: 84
tomgilb, Ive added dates all the way out to 2021-12-19 but as you can see(barely) that it stops showing vert. lines at June. Am i doing something wrong or have I over looked a setting to get these vert. lines charted for the future?

https://www.sierrachart.com/Download.php?Folder=SupportBoard&download=53555
Attachment Deleted.
imagefullmoon.jpg / V - Attached On 2021-08-08 02:23:49 UTC - Size: 258.73 KB - 207 views
[2021-08-08 18:29:07]
Sawtooth - Posts: 3953
Am i doing something wrong or have I over looked a setting to get these vert. lines charted for the future?
A spreadsheet study cannot plot anything in the future.
The current bar is in row 3 and earlier bars are in rows below, so there are no rows that can plot at future bars.

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

Login

Login Page - Create Account