Excel - fitting distribution actually, exactly, someone, excel

atate2007

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.

tango_gs

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.

brainsX

How many data points do you have? Just use a formula for the curve that has one less variable. It almost always fits well.

ninja1024

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

dirk01

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?

sabaruch

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.

Valores

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.

RepoToronto

Inner Evil Voice Wrote:
-------------------------------------------------------
> Remember to delete those pieces of data preventing
> a smooth distribution fit.


I now understand your username much better. ;-)

cjs238

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."

RMontgomery

Remember to delete those pieces of data preventing a smooth distribution fit.