Home >> Support Board

Support Board

Date/Time: Wed, 24 Apr 2024 18:35:38 +0000

How to Post a Message for Support

[User Discussion] - Study sub graph difference MA

View Count: 1868

[2013-11-28 08:49:29] #1 [Go To First Post]
User54027 - Posts: 128
I am plotting the difference between price and the 20ema. Above and below the 20 ema. Is there any way I can plot a moving average of price above and below the 20 ema. So when price trades above the 20ema, over x period of time what number of points above the average does it trade at. So over the last 20 periods, price has on average traded 10 points above the 20ema? Thanks

[2013-11-28 10:58:07] #2 [Go To First Post]
TastyRisk - Posts: 119
all studies are already there. It's basic maths. Date Time Of Last Edit: 2013-11-28 10:59:11

[2013-11-28 16:35:58] #3 [Go To First Post]
User54027 - Posts: 128
Tasty, I want to know the average number of points above vwap the dow trades when price is above vwap over the last 5000 periods. How the $%#@# is that basic maths????

[2013-11-28 20:18:16] #4 [Go To First Post]
TastyRisk - Posts: 119
So your main 20period average can be your "line in the sand"... If Last is above your "Line in the Sand" then record the difference that exists. If negative then you need to skip onto the next row. Add the recorded value to the next positive result you get. When you have have done this, on cells giving positive results, 5000 times; then you need to divide your running total by 5000 to get your average. So to implement this; you need either a custom study that will loop through until it's found 5000 instances, or, you can devise some logic in a spreadsheet. As SC support won't write the study for you for free; please post your code or spreadsheet formulas and I'm sure that you'll find other people willing to help some more. The maths isn't complicated but the programming / logic might be new to you ? I don't really find it easy myself but I've found it's always best practice to break things down into small chunks so you can more easily plan how to work through them. Date Time Of Last Edit: 2013-11-28 20:33:27

[2013-11-28 20:59:39] #5 [Go To First Post]
TastyRisk - Posts: 119
I think you can do this in spreadsheet using the function COUNTIF and SUMIF. see; http://office.microsoft.com/en-gb/excel-help/countif-HP005209029.aspx & http://office.microsoft.com/en-gb/excel-help/sumif-HP005209292.aspx Make a cell either a 1, or 0, depending on if last is > or < vwap. You will use COUNTIF to find how many of the above cells it takes to find 5000 instances of positive values. You will make the SUMIF range dynamic depending on the value arrived at from the COUNIF. Specifically; The COUNTIF result will be referenced by - and used to construct - your SUMIF formula. Date Time Of Last Edit: 2013-11-28 21:11:16

[2013-11-28 21:25:32] #6 [Go To First Post]
User54027 - Posts: 128
Many thanks Tasty!

[2013-11-28 21:57:58] #7 [Go To First Post]
TastyRisk - Posts: 119
If using the spreadsheet method, I suggest doing this: 1. Make a column's cell either a 1, or 0, depending on if last is > or < vwap. 2. Make a column's cell with formula like this> =IF(Last>VWAP,DIFF,0) (in english> if last above vwap then record diff from another cell else record a zero.) 3. Do the COUNTIF on the cells described in step 1. (suggest setting spreadsheet to have many more rows than needed incase bear market!). 4. Do the SUMIF on the cells described in step 2, using the single COUNTIF value to modify the SUMIF formula. 5. You only need to divide the SUMIF result by 5000 as the zeros should not add up to anything :-) note; this is all dependent on how flexible the function syntax is in SC spreadsheets. I don't know if this is doable in the new version but at least you have a path forward and perhaps a target in how you want to use the SUMIF function.

[2013-11-28 22:08:35] #8 [Go To First Post]
TastyRisk - Posts: 119
Hay, User... You know I'm learning myself here, that's why I'm helping you, actually! I think that even if the SUMIF formula can be modified by an COUNTIF external value; there still exists the problem which is that the COUNTIF range is not dynamic itself. However; the MATCH function should solve that. Here is a good example... http://www.mrexcel.com/forum/excel-questions/162666-can-excel-formula-count-until-certain-sum-reached.html Date Time Of Last Edit: 2013-11-28 22:11:46

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