Support Board
Date/Time: Mon, 25 Nov 2024 13:14:31 +0000
standardize spreadsheet
View Count: 338
| [2024-03-04 19:20:03] |
| User411320 - Posts: 289 |
|
so I'm just trying to standardize an ema based off LN previous bar change. My question is shouldn't the value be at 0.00 whenever price is at the EMA? please see attached photo any help is appreciated
|
 Screenshot 2024-03-04 141551.png / V - Attached On 2024-03-04 19:19:31 UTC - Size: 42.95 KB - 60 views Attachment Deleted. |
| [2024-03-05 17:15:25] |
| User411320 - Posts: 289 |
|
Update: I've played with the scaling and still have the issue, I also plotted just the L column which is the LN of the ema and it aligns perfectly but loses it standardization
|
| [2024-03-05 18:59:56] |
| John - SC Support - Posts: 36238 |
|
What do you mean by "LN"? And what do you mean by "...but loses it standardization"? As you can tell, we know nothing about the calculation you are doing, so we can not answer your questions. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-05 19:09:26] |
| User411320 - Posts: 289 |
|
so I used the spreadsheet function (LN) on the 1 bar change of the EMA, my understanding is that 0.00 is when price on the chart is at the EMA but it's not doing that as shown in the previous pic. when I say loses its 'standardization" is because I used the result of just the LN function. attached are the formulas |
| Screenshot 2024-03-05 140802.png / V - Attached On 2024-03-05 19:09:10 UTC - Size: 899 B - 52 views Screenshot 2024-03-05 140838.png / V - Attached On 2024-03-05 19:09:17 UTC - Size: 1.61 KB - 55 views |
| [2024-03-05 21:13:08] |
| John - SC Support - Posts: 36238 |
|
Thank you for the clarifications. so I used the spreadsheet function (LN) on the 1 bar change of the EMA, my understanding is that 0.00 is when price on the chart is at the EMA but it's not doing that as shown in the previous pic.
This will not be the case, as you are taking the ratio of the current bar's EMA divided by the previous bar's EMA, therefore the only time the natural log of this will be 0 is if the EMA is the same at both bars, giving you a ratio of 1.00 (LN(1) = 0). We do not know how you setup the EMA (i.e. what Length you are using), but even if it is set to 1, then you would need to have the same price at both bars for the chosen bar value to get natural log to be 0. We are not really sure how you are using the "STANDARDIZE" function as we do not know what you have in M3 and N3, but it looks like the best this would be is a smoothing function to the natural log data. What exactly are you wanting to accomplish from this? For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-05 21:22:31] |
| User411320 - Posts: 289 |
|
I'm trying to create a z-score like the native z-score study but instead of the SMA with the EMA and other moving averages. The EMA length is set to 100. M3 is the AVERAGE function of the L column N3 is STDEV.S function of the L column The L column is the LN function of the ema (I should have mentioned that) |
| [2024-03-05 21:47:24] |
| John - SC Support - Posts: 36238 |
|
Z-Score is calculated as follows: z = (x - mu) / sigma Where mu is the Mean of the population and sigma is the standard deviation of the population. Therefore, the STANDARDIZE() function is not giving you the z-score. Rather, you should use the following formula instead: = (L3 - $M$3) / $N$3 For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-05 22:40:42] |
| User411320 - Posts: 289 |
|
Thanks for explaining that, I had no idea. I thought it was the same thing. I used the formula you suggested and it's still the same
|
| Screenshot 2024-03-05 173957.png / V - Attached On 2024-03-05 22:40:36 UTC - Size: 47.11 KB - 50 views |
| [2024-03-06 15:33:59] |
| John - SC Support - Posts: 36238 |
|
What exactly is the problem? If you are referring to when the data should be 0, then the probabilities of this are very small, as the value in the L column would have to match the Mean of the population (cell M3). It will not be when the Close is the same price as the EMA (as noted above).
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-06 17:28:15] |
| User411320 - Posts: 289 |
|
Is that because I'm using an EMA? Because the native z-score lines up perfectly when price is at SMA and the z-score line being at the 0. I can be totally wrong
|
| [2024-03-06 19:05:16] |
| John - SC Support - Posts: 36238 |
|
When you use the Z-Score study, what are you calculating the Z-Score of? What is the "Based On" and "Input Data" set to?
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-06 20:45:30] |
| User411320 - Posts: 289 |
|
When I Use the Z-score study the "Based On" = <Main Price Graph> and "Input Data" = Last.
|
| [2024-03-06 22:48:31] |
| John - SC Support - Posts: 36238 |
|
In this case, what you are seeing makes sense, as the numerator of the Z-Score is (X - SMA(X)), so when X is the current price and the SMA is at the same price, then the result is 0.
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-07 13:05:31] |
| User411320 - Posts: 289 |
|
so how can I get that done with the EMA? what am I doing wrong? I'm not trying to be difficult just trying to teach myself and learn
|
| [2024-03-07 16:08:31] |
| John - SC Support - Posts: 36238 |
|
You are taking the natural log of the EMA from the current bar divided by the EMA of the previous bar, and then using that to determine the Slope and Intercept for an input into the Z-Score. The formula we use in our Z-Score study is the following Z(index) = ((X(index) - SMA(X)) / SD(X) Where X is the Input Data, SMA is the Simple Moving Average of the data, and SD is the Standard Deviation of the data. Therefore, for what you are doing in a spreadsheet, your formula would be the following = (ID0.SG4@3 - ID1.SG1) / $N$3 Where ID0.SG4 is the Close, ID1 is the EMA study with SG1 being the EMA subgraph, N3 is the cell that holds the Standard Deviation of the EMA (not the LN(EMA/EMA(-1)). For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-07 18:49:22] |
| User411320 - Posts: 289 |
|
That worked. Thank you so much for your help and time
|
To post a message in this thread, you need to log in with your Sierra Chart account: