标题: Excel - fitting distribution [打印本页] 作者: atate2007 时间: 2011-10-2 18:10 标题: Excel - fitting distribution
I was asked to "fit a data series" into distribution in excel. I seriously have no idea what this actually mean. I googled it and found some shareware but none explain what exactly does it mean. Can someone help?
Thank you in advance.作者: Valores 时间: 2011-10-2 18:15
What are you trying to accomplish with this info? Do they just want you to build a histogram?作者: rohitdoshi 时间: 2011-10-2 18:20
Usually it involves trying to fit a histogram of a distribution to some kind of theoretical distribution. So, for example, if it were a normal distribution, you'd be looking for the mean and standard deviation of a normal distribution that makes for a best fit between the theoretical and the actual data. If it's another distribution, there are different parameters, and you want to try to find the ones that make for the best fit.
You may have to transform the data before you do that, by the way. If you have levels data for stocks, you might need to turn that into returns to do the fitting. Or logged returns.
How you measure the fit is another challenge. The statistically purest way would be to run up a chi squared test and check for (lack of) significance. A more quick-and-dirty method might be to compute the difference between expected frequencies and observed frequencies for each bin of the histogram and try to minimize the sum of squares of the differences (or sum of absolute values of differences). You could then have solver try to solve for the parameters of your distribution while trying to minimize the sum-of-squares.
It's all a bit challenging if you don't know what distribution you are trying to fit.作者: joehogue 时间: 2011-10-2 18:26
Bchad's approach will work nicely if this is a one time thing, if you're going to be doing this regularly, you can buy excel add-ins that will do this with the click of a button. @Risk is a great program that has this capability in addition to hundreds of others. It's wildly expensive, but very powerful. You might be able to download a demo.作者: RMontgomery 时间: 2011-10-2 18:42
Remember to delete those pieces of data preventing a smooth distribution fit.作者: cjs238 时间: 2011-10-2 18:48
Inner Evil Voice Wrote:
-------------------------------------------------------
> Remember to delete those pieces of data preventing
> a smooth distribution fit.
I like to call this removing outliars, as in "I removed the outliars because they cannot be trusted. What's that, you disagree? If you're in cahoots with the outliars you must be a liar yourself, shut up."作者: RepoToronto 时间: 2011-10-2 18:53
Inner Evil Voice Wrote:
-------------------------------------------------------
> Remember to delete those pieces of data preventing
> a smooth distribution fit.
I now understand your username much better. ;-)作者: Valores 时间: 2011-10-2 18:59
Hahaha, thanks guys for the prompt and insightful feedback.
My task is that I have a return series and I'm not sure what distribution it is so without jumping into conclusion that it's gaussian, I like to find another way to test it.
jmh530, you're correct that we don't want to pay anything. I work in a non-for-profit organisation so any cost saving is good saving. I used R briefly to do some cluster analysis but never looked into it more. I'll try that.
I'll try Bchad's approach shortly and yes...I'll "remove the outliers"...hahah
Much appreciated.作者: sabaruch 时间: 2011-10-2 19:04
Then a first step would be to ahead and look at the skew and kurtosis figures. If it's normally distributed, it should have 0 skew and a kurtosis of about 3. Depending how different these figures are from that, it may or may not be necessary to go through the trouble of figuring out another distribution. For zero skew and high kurtosis, some people use a t-distribution with a smaller number of degrees of freedom.
Part of the problem is that it's often difficult to figure out what distribution you ought to be using. Usually it's the underlying model of probability that determines what distribution you expect... just throwing in a bunch of distributions and seeing which one fits best is effectively data-mining and often just as problematic as assuming something is normally distributed. Which distribution to use generally comes out of your theoretical understanding of what's going on - the parameters are what you determine from empirical sources.
Remember that if you are doing stock returns, you'll want to be looking at ln(total return) when you are doing the fit.作者: dirk01 时间: 2011-10-2 19:09
Thanks BChad. Questions... elementary questions.
1. Why ln(total return)?
2. my first set of data is inflation and inflation bond return. Then it'll move on to something else. Both series does not have large kurtosis nor skewness so I think you're right that normal is just as good as any others?作者: ninja1024 时间: 2011-10-2 19:15
the Jarque–Bera test is a simple stats test for normality based on the first 4 moments, along the lines of Bchad's recommendation but you'll need to compute the JB test statistic and compare it against a table rather than eye-balling skewness and kurtosis. There is plenty of info about it on the web. There are also other more involved statistical tests which can also be implemented easily just with a spreadhseet, just google "tests for normality"
the reason why you want to use log-return is because it is additive, i.e. the log-return over some time interval is equal to the sum of the log-returns over a partition of that time interval. under certain set of assumptions, the sum of a large number of i.i.d. random variables will be approximately gaussian (CLT). so the assumption that the log-return is normal is grounded in theory, it is not just an empirical exercise about finding the best distribution that fits your data作者: brainsX 时间: 2011-10-2 19:20
How many data points do you have? Just use a formula for the curve that has one less variable. It almost always fits well.作者: tango_gs 时间: 2011-10-2 19:26
It's ln changes, not ln levels (in case you didn't get that).
It took a long time for me to understand why to use one or the other. In my opinion, there are two reasons beyond what Mobius says.
First, if you use natural logs and then transfer it to arithmetic, then the arithmetic will never fall below 0 (this is important for indices that never will fall below 0). Alternately, it may also be important that as the index goes to 0, then the volatility might fall.
Second, when you're doing like a mean-variance optimization, then you need to do it on arithmetic returns. If the frequency of your data matches your time horizon, this isn't an issue. However, if you have a longer time horizon, then you need to project out data and its tricky to project out arithmetic returns. B/c of what Mobius says, you can just add up the log returns and convert to arithmetic (exp(X)-1 does the trick). You can't use the log returns in the optimization b/c log returns don't add up the way arithmetic returns do.
