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.
What are you trying to accomplish with this info? Do they just want you to build a histogram?
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.
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.
http://www.lmgtfy.com/?q=curve+fitting+excel e.g. http://www.csupomona.edu/~seskandari/documents/Curve_Fitting_William_Lee.pdf
I would guess they would mean linear regression (aka fit a line). In that case, you could follow the second link from DarienHacker or use the excel Data Analysis add-in (google to install if it isn’t, pretty straightforward to use). It really depends on what they are looking for. You have tons of options. I would guess they aren’t looking for you to do the things below, but I figured I’d mention them anyway as possibilities. I think if you’re trying to fit a distribution the way that Bchad is saying, but don’t want to pay for @Risk, then after plotting the histogram and maybe checking the skewness and kurtosis, you could download R, install the sn package, fit a skew t distribution and check how the skew and degrees of freedom parameters turn out. If df is very high and skew is close to 0, then you may as well use the gaussian, but otherwise this might get a better fit. Another alternative is to use a garch model (time varying volatility may be the reason you see fat tails). You can do this in R as well. If you have access to matlab, they also have a nice package that can handle residuals with different distributions: http://www.kevinsheppard.com/wiki/MFE_Toolbox
Remember to delete those pieces of data preventing a smooth distribution fit.
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.”
Inner Evil Voice Wrote: ------------------------------------------------------- > Remember to delete those pieces of data preventing > a smooth distribution fit. I now understand your username much better. 
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.
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.
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?
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
How many data points do you have? Just use a formula for the curve that has one less variable. It almost always fits well.
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.