• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Assigning Distributions in Excel

jimenef

Joined
10/9/09
Messages
12
Points
11
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?
 
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.
 
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.
 
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.
 
Back
Top