Login Page - Create Account

Support Board


Date/Time: Tue, 23 Jan 2018 13:48:51 +0000



[User Discussion] - Study help needed please

Support Request:
[2013-04-05 22:33:57]
User22396 - Posts: 186
On a daily chart (or intraday chart in day mode) I want to create a study that references the High(Low) of the first day of the month and then adds(subtracts)the last day of previous months 30-day SMA of ATR and plots as a dash those values for the rest of the month then resets when the new month starts.

I can do a weekly version using SpreadsheetStudy and InitialBalance and then type in by hand the ATR from previous weeks period but wasn't sure if it can be set to reference the previous weeks ATR automatically so I wouldn't have to type by hand everyweek?

thanks for the help
b
[2013-04-06 13:42:34]
tomgilb - Posts: 1753
Using the Spreadsheet Study study on a daily chart, try this:

To grab the ATR from the previous Friday and repeat it each day of the following week, put this in K3:
=IF(AND(WEEKDAY(A4)=6,A3-A4>1),AA4,K4)

To grab the ATR from the last day of the previous month and repeat it each day of the following month, put this in L3:
=IF(DAY(A3)=1,AA4,L4)

These formulas assume the ATR study is in column AA.

To grab the High of the first day of the current month and repeat it each day of the current month, put this in M3:
=IF(MONTH(A3)>MONTH(A4),C3,M4)

To grab the Low of the first day of the current month and repeat it each day of the current month, put this in N3:
=IF(MONTH(A3)>MONTH(A4),D3,N4)

With these values, you should be able to draw the lines you want.
Date Time Of Last Edit: 2013-04-06 13:42:59
[2013-04-07 01:10:22]
User22396 - Posts: 186
hey tom...gonna try it out...thanks again!
[2013-04-07 17:07:03]
User22396 - Posts: 186
tested and works great...changed to =IF(OR(MONTH(A3)>MONTH(A4),YEAR(A3)>YEAR(A4)),C3,M4) to capture the new year rollover...seems to work fine

is there a function for Quarter...like Q1, Q2, etc...I tried Quarter() but returned '#name?'


[2013-04-07 19:44:29]
tomgilb - Posts: 1753
Here is a list of Excel functions with descriptions and examples:
http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx

You might be able to use the EDATE function in a formula to derive quarters.

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

Login


Login Page - Create Account