Price Distribution Analysis in Excel | Forex Factory

Options

Go to
Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

Performance History Analysis Excel Spreadsheet 15 replies

Excel and VBA analysis 48 replies

Today's Price Distribution Analysis 19 replies

Request: Price distribution indicator 0 replies

Attachments: Price Distribution Analysis in Excel
Exit Attachments

Price Distribution Analysis in Excel

  • #1
  • First Post: Edited 7:04pm May 13, 2008 5:10pm | Edited 7:04pm
I'd like to thank DownRiverTrader for this idea.Unfortunately his thread is limited only to people that have TradeStation and are interested only in the EURUSD pair.

He also was unable (or unwilling?) to explain how the TradeStation study works exactly so I tried to figure it out on my own so that I can replicate it in Excel.

I decided to share my findings with all of you, in the hope that some of you who know more than me about maths and statistics can contribute.

I have limited knowledge of statistics, all of it due to a 4 month college course and I'm pretty sure I forgot most of it.However, with the things I still remember and with some freshing up by flipping through some statistics books, I was able to come up with this:

Price Distribution Analysis is about determining support and resistance levels in price.In it's scope, it is somewhat similar to MarketProfile, but does not account for volume.

If you know at least some basic data about MarketProfile, you know it's based on the normal distribution curve also known as the Bell Curve, because, well, it looks like the profile of a bell.

For the bell curve, approximatively 70% (I believe 68% in reality) of the values fall within 1 standard deviation away from the average ( mean - 1 standard deviation for the left half of the curve area, mean + 1 standard deviation for the right half of the curve area).

However, in real trading, normal distributions of prices rarely occur.Most of the times, price distributions are skewed to the left or right, which is actually a very good thing because we can see where most of the trading has taken place.

This is effectively because of what the price distributions represent: they are probability distributions.

Here's how to put all of this together:

Let's assume we have a specific price interval we want to analyze.For the purpose of this example, like DownRightTrader I will also use the current month and the EURUSD pair.

If we know the peak of the distribution curve, the value at which most of the trading has taken place, we can then accurately predict where 68% and 95% of the future closing prices for the EURUSD pair will be:

by calculating first the standard deviation of the whole price range, the mean of the price range, then subtracting 1 standard deviation from the mean and adding 1 std dev to the mean to get the 2 values delimiting the 68% probability area, and subtracting 2 std dev from the mean and adding 2 std dev to the mean to get the 2 values delimiting the 95% area.

This can be applied to any time interval, not just for a whole month.

I've attached the Excel spreadhsheet I used and you will find more specific explanations inside of it.

I also have to mention that you will need 1 minute data for the whole month.I got mine from the Alpari databank.

Hopefully I managed to explain things correctly.Please correct me if necessary or contribute to the spreadsheet if you have any ideas.

I'm sure there are a lot of things that can be improved.It does not come with all the bells and whistles of Tradestation but it very usable, even in it's current state.

edit: forgot to say that in this case, in the spreadsheet i used, the peak value of the curve represents the mode and is different than the mean(average), why exactly the 68% and 95% intervals are calculated in relation to the mean and not the actual peak - the mode, is something i haven't managed to understand yet; maybe the mean is used only for a normal distribution and in this case, since we do not have a normal distribution we should use the mode instead for our probabilistic calculations

hopefully someone with more knowledge than me can confirm or infirm this.
Attached File(s)
File Type: xls pricedistribution.xls   828 KB | 3,961 downloads
  • #2
  • May 13, 2008 6:51pm May 13, 2008 6:51pm
Wow, good work tenantro. I am actually reading Market in Profile right now to try to get a better understanding of this method of view the market. Hopefully i can contribute soon.
  • #3
  • May 14, 2008 3:29am May 14, 2008 3:29am
Looks like you have the calculations worked out........http://www.forexfactory.com/images/icons/icon14.gif

I attached the MT4 indicator in the other thread......

So now we have all the info........

Attached the graphic for today Euro USD
Attached Image
  • #4
  • May 14, 2008 3:50am May 14, 2008 3:50am
I'm not sure how the indicator you posted works, but I think it's probably more complex that what I did so far in Excel.

I think the indicator you posted does some sort of smoothing or at least taking different bin intervals to calculate the frequencies, because the frequency histogram in my Excel spreadsheet looks very different from what the histograms plotted by the indicator you posted do.
  • #5
  • May 14, 2008 8:27am May 14, 2008 8:27am
Part of the difference in your Excel version is the amount of steps. If you show more prices,you'll get something similar to the mt4 market profile indicator.

Personally, I think the better option is it use regression channels. They basically give you the same bell curve on the chart. Bollinger Bands do as well as long as you use a longer period.

I had designed a whole system in excel using your same concept of mapping out the bell curve. It started out as just the value of the mean, standard deviation +-1, +-2. Eventually, I added a graphical represenation. But the numbers are what the system was about. (thumbnail should be clickable)

http://img264.imageshack.us/img264/7...ep15og2.th.gif
  • #6
  • May 14, 2008 8:32am May 14, 2008 8:32am
Regression channels..I will do some reading on that, thank you for the idea.I love your excel screenshot btw Very cool stuff...I need to do some more reading on Excel as well now that I'm stuck with the 2007 edition.
  • #7
  • May 14, 2008 8:43am May 14, 2008 8:43am
The excel thing stopped working when I upgraded to office xp and then office 2003.

I was getting live price data from the CMS VT API. I don't think the thing will even open now. And I don't use the methods anymore anyway, so I haven't bothered trying to fix it. In fact, I don't even know where it is. that was 2 or 3 pc's ago.

Anywho... regression channels, raff channels, etc. Apply to multiple time frames and when price is at or beyond a standard deviation on multiple time frames, you've got a home run setup.
  • #8
  • May 14, 2008 10:22am May 14, 2008 10:22am
I wasn't very curious to find out more about Bollinger Bands until JR97 mentioned them, but indeed it seems that Bollinger Bands are plotted using the same principles as in the spreadsheet modeled after DownRiverTrader's initial idea.

I found something on Wikipedia about Bollinger Bands:

Quote
Predictive Value

The bands cannot, as some have supposed, be used to make reliable statements regarding what fraction of an equity's prices will lie within a certain distance of the mean value. This is because an individual equity's price does not obey known distribution functions (see stochastic process). For example, if the bands for plus or minus two standard deviations (2SD) are computed, it is wrong to suppose that ~95% of an equity's closing prices will, on average, lie within the Bollinger bands. That would require, among other things, that the prices be normally distributed, which they are generally not. It would further require that the true standard deviation be known. The standard deviation calculated as above, however, is only an uncertain estimate of the true standard deviation. Furthermore, it should be realized that the "standard deviations" of stock prices for finite time periods are not fixed parameters as required to apply classical statistical theory, but instead are variables in constant flux depending on price volatility. The bands give a visual picture of a stock's price volatility. Another misconception is that Chebyshev's inequality can be used to show that 75% of the data lies between the Bollinger Bands. This is also not applicable because each point of the band is calculated on a different data set. That is the mean and the standard deviation at period 100 is calculated by the price at periods 100, 99, 98, ..., 100-N+1 and the mean and standard deviation at period 120 is calculated by the price at periods 120, 119, 118, ..., 120-N+1.
It is of interest to note that faulty interpretation of a price touching or breaching a band based on incorrect statistical assumptions has become so widespread that some traders now use these events alone as trading signals and by so doing may have unwittingly injected significance into these band-touching events that should otherwise be absent. Nevertheless, anyone can observe over time, that for a diversified group of mutual funds, say, the proportion of daily adjusted close prices that breach their 1-month 2SD Bollinger bands varies between 5% and 15% of days, with each fund having a fairly constant, characteristic long-term breach probability descriptive of its long-term, relative volatility.
When the bands lie close together a period of low volatility in stock price is indicated. When they are far apart a period of high volatility in price is indicated. When the bands have only a slight slope and lie approximately parallel for an extended time the price of a stock will be found to oscillate up and down between the bands as though in a channel.


So, considering this, the main attribute of Price Distribution Analysis still seems to be only determining at which values most of the trading has taken place...it's predictive abilities seems to be less valuable.
  • #9
  • May 14, 2008 10:34am May 14, 2008 10:34am
Bollinger Bands by themselves and at the "standard" setting do hold little value. That's why I said you have to go to a longer period and apply to multiple time frames. Nesting them is also another good technique.
  • #10
  • May 14, 2008 10:39am May 14, 2008 10:39am
Hum...sorry to bug you again, but what do you mean by longer period? I know I used 1 minute data...but it's supposed to give general directions as to what will happen for the rest of the month, at least that's what I understood DownRiverTrader was saying...
  • #11
  • May 14, 2008 11:03am May 14, 2008 11:03am
I know nothing about DownRiverTrader. I avoid anything less than 15 min. as well.

What I meant by longer period is that most programs put bollinger bands with a period of 20 or 14. That's too short and "rubbery" for me. I like the bands with a period of 55 or 60. Smooths them out and gives a better representation of expected statistical range. Then apply to multi time frames.

For example, if price is at the bottom of the daily bands, 4h bands, and 1h bands, that's a pretty signal to go long and target at least the mean of the 1h bands for short term, 4h bands for medium, and daily for longer or swing moves. Also take band direction into account. If the bands are sloping up, I'd stick with long trades and wouldn't look to short unless a higher frame is straight or down.
  • #12
  • May 14, 2008 11:08am May 14, 2008 11:08am
I understand now, thank you. Ok, I have more food for thought now
  • #13
  • May 14, 2008 2:15pm May 14, 2008 2:15pm
Just wanted to say thanks for your efforts tenanto!
  • #14
  • May 14, 2008 4:38pm May 14, 2008 4:38pm
This tenantro guy is a jerk. Major jerk.
  • #15
  • May 14, 2008 5:14pm May 14, 2008 5:14pm
I'm what?
  • #16
  • Edited 7:42pm May 14, 2008 6:45pm | Edited 7:42pm
Here's the updated curve in TS to help you guys along.

http://www.morrisfunds.com/storage/f...05.14.2008.PNG
  • #17
  • May 18, 2008 8:50am May 18, 2008 8:50am
Quoting tenantro
I'm what?
Bon travail Tenantro !
Il faut simplement l'ignorer ces imbecils.
  • #18
  • Edited 11:10am May 18, 2008 9:58am | Edited 11:10am
merci beaucoup aussi.

Btw, I'm doing more research at the moment which seems to confirm that there isn't as much predictive value in MarketProfile as their creators seem to advertise there is.

The single most important part of the analysis is finding out the frequency distribution of the prices and subsequently the price at which most of the trading has taken place, but it's not correct to use this info to predict where most of the prices will go next because:

the 68-95-99.7 rule applies (or should only be applied) to data samples that have a normal distribution, but as we can see from the spreadsheet, the histogram does not have a normal distribution, fact indicated also by the skewness coefficient.

so far from my research it seems that the correct thing to do in order to estimate between what intervals will prices be next is a non-linear, non-parametric regression because that can be applied successfully to data samples that do not follow a normal distribution.
  • #19
  • May 18, 2008 1:05pm May 18, 2008 1:05pm
Quoting tenantro
merci beaucoup aussi.

Btw, I'm doing more research at the moment which seems to confirm that there isn't as much predictive value in MarketProfile as their creators seem to advertise there is.

The single most important part of the analysis is finding out the frequency distribution of the prices and subsequently the price at which most of the trading has taken place, but it's not correct to use this info to predict where most of the prices will go next because:

the 68-95-99.7 rule applies (or should only be applied) to data samples that have a normal distribution, but as we can see from the spreadsheet, the histogram does not have a normal distribution, fact indicated also by the skewness coefficient.

so far from my research it seems that the correct thing to do in order to estimate between what intervals will prices be next is a non-linear, non-parametric regression because that can be applied successfully to data samples that do not follow a normal distribution.
I have been looking at this market profile thing since 2007 - since DRT started his first posting.
http://www.forexfactory.com/showthre...t=18243&page=7

That was where I first got to know the market Profile indicator. The PD Dynamics that was posted recently is slightly different from the maket profile indicator.
My conclusion too is that it cannot predict on it's own, as you said, where price will go. What it can certainly do is with other fundamental / technical indicator give us a probability of where it MIGHT go.
By using the various levels, I got some good support and resistance line. When it forms a confluence with other MA's etc, it does suggest a good entry or exit point.
When it breaks a certain level, and by looking to the equity market sentiment, one can eventually put a target at the next level. ( also a stop loss too )
  • #20
  • May 18, 2008 1:21pm May 18, 2008 1:21pm
No, what I meant to say is that the probability levels given by MarketProfile are less "accurate" than the ones that could be given by non-linear, non-parametric regression because of the fact that in theory what MarketProfile tries to achieve is correct, but the conditions for a correct application of the analysis are not met (the normal distribution) and given that, the results of the analysis will also be distorted.

Actually, let me rephrase that, maybe not MarketProfile in itself is inaccurate, but just that some people try to get more from it than it's supposed to give.

I'm quoting this from CBOT in regards to MarketProfile:

Quote
A price level that has been confirmed over time takes on added meaning. A price that is touched only briefly is just a price and little more. Confirmed by time, however, a price can reveal market value.

Given my recent research, MarketProfile should only be used for the purpose described in the quote above, finding out what the market considers a fair price and which ones are not considered so.

Anyway, back to my research...I will post more as things develop. :nerd:
0 traders viewing now