Assigning Distributions in Excel

[jimenef]

Does anybody know how to (without using add-ins or other softwares) assign the best statistical distribution to a set of data that is given to you (ex rentabilities)?... My idea was generating auxiliary columns with the distribution:

example: a uniform with the parameters correponding to the ones of the data i have
a normal one with the mean and variance of the data i have

and then measure the squared differences and based on that decide which one is the best, however im guessing its a very inefficient way and probably an expert who reads this is laughing haha...

any thoughts?

 

[jimenef]

Anyone any ideas???

 

[tobias elbert]

The best way to get a feel for the data and its underlying distribution would be

a) to plot a histogram. Excel has a built in functionality for that.
b) test for normality using for instances the Jacque-Bera (I think it's called) test.
c) a Q-Plot to visualise the extent your data differs from a given distribution (eg a normal one). Google for "Excel Q-plot" and you'll find heaps of results and step by step guides.

It also depends on what you wanna do with the data? Do you want to model it? You need to decide whether you want to model the levels or (log) first differences, for instances.

 

[jimenef]

The actual use will be for the user to put historical changes in percentage... to get a distribution... and from that distribution (after having done cholesky) generate the random scenarios of the montecarlo)... i have seen that usually people use normal variables for montecarlo but i have to use also try with the logistic, weibull, lognormal, exponential.

 

[tobias elbert]

So you'll be working with log returns? Then look at which distribution fits best your sample of log returns data (after removing any deterministic components) and use that one to draw realisations.

Not sure whether Weibull, lognormal, or exponential distributions are a good idea to use for modelling anything to do with changes - their support is only defined from 0 to inf.