Random numbers

raza

New Member
#1
Generally, I use NORMDIST() function to generate random numbers giving Mean and SD of my choice (based on the data set I have).

Is there any way to generate random numbers in Excel (or VBA) giving 4 criterias including Mean, SD, Skewness and Kurtosis. Obviously the data will not be assumed normally distributed.

Will appreciate if someone can help me with this..........

Regards
Raza
 

vinux

Dark Knight
#2
It must be NORMINV() function. Because normdist is for pdf/cdf.

If you know the distribution then you can find out the random number generation logic.

Or Are you looking something like PEARSON DISTRIBUTION?

Regards
Richie
 

raza

New Member
#3
Actually, the idea is to use Skewness and Kurtosis to generate random number in estimating future market prices. For example Kurtosis is 3 for normal distribution......but if there are negative sentiments about the market then Kurtosis estimate will be varying i.e from person to person. Any shape of distribution can be assumed.

Again, the question is if some one wants to include Skewness and Kurtosis in generating the random numbers, what's the best option (or functions) he has.

My apologies if I am not too clear in my question.

Thanks
Raza
 

Dragan

Super Moderator
#4
Actually, the idea is to use Skewness and Kurtosis to generate random number in estimating future market prices. For example Kurtosis is 3 for normal distribution......but if there are negative sentiments about the market then Kurtosis estimate will be varying i.e from person to person. Any shape of distribution can be assumed.

Again, the question is if some one wants to include Skewness and Kurtosis in generating the random numbers, what's the best option (or functions) he has.

My apologies if I am not too clear in my question.

Thanks
Raza
Hi Raza: You have several options available. Do you know the values of skew and kurtosis that you're interested in specifying.

For example, suppose you wanted to generate a non-normal distribution with specified values of skew and kurtosis of 2 and 7 respectively.

You could use a Generalized Lambda Distribution; a (Tukey) g-and-h distribution; or power method polynomials. These are all simple transformations on either uniform or standard normal deviates.

For example, to obtain the distribution above I could a polynomial of order 3 as:

Y = -0.260023 + 0.761585*Z + 0.260023*Z^2 + 0.0530723*Z^3

where Z ~N(0,1).

The variable Y will have a mean of zero, variance of 1, skew=2, and kurtosis=7.

If your interested I can explain further on how to solve for the coefficients, or use the other techniques. I've done quite a bit of research in this area i.e. generating non-normal distributions with specified moments and correlation matrices.

Note: When I say kurtosis of 7 I am implying that normal kurtosis is scaled to 0 and not 3.
 

vinux

Dark Knight
#5
Actually, the idea is to use Skewness and Kurtosis to generate random number in estimating future market prices. For example Kurtosis is 3 for normal distribution......but if there are negative sentiments about the market then Kurtosis estimate will be varying i.e from person to person. Any shape of distribution can be assumed.

Again, the question is if some one wants to include Skewness and Kurtosis in generating the random numbers, what's the best option (or functions) he has.

My apologies if I am not too clear in my question.

Thanks
Raza

I would suggest you to look at Pearson distribution.

http://en.wikipedia.org/wiki/Pearson_distribution

Because most of the known distributions are special case of this.

And there are way to estimate the parameters from the first four moments or ( mean variance skewness kurtosis).
But I don't think there is an easy way to to generate random numbers.
 

raza

New Member
#6
Hi Vinux,

Thanks alot for all the detail.

The issue is................my background is more in Finance and financial modeling. I always have to struggle to model Statistics in work. Therefore, I will appreciate if you can explain a bit more to help me to get it properly.

Presenlty I have the following formuals in an existing spreadsheet which uses Skewness and Kurtosis to generate random numbers. Kindly look into these 4 lines and see if you agree with this logic:

=Sqr((3 * Kurtosis - 4 * Skewness ^ 2 - 9) / (variance * (Kurtosis - 5 / 3 * Skewness ^ 2 - 3) ^ 2))

= Skewness / (Sqr(variance) * (Kurtosis - 5 / 3 * Skewness ^ 2 - 3))

= mean - 3 * Skewness * Sqr(variance) / (3 * Kurtosis - 4 * Skewness ^ 2 - 9)
= 3 ^ (3 / 2) * Sqr(variance * (Kurtosis - 5 / 3 * Skewness ^ 2 - 3)) / (3 * Kurtosis - 4 * Skewness ^ 2 - 9)

Honestly speaking, I absolutely have no idea what is happening here, expecially why we are subtracting different numbers from Kurtosis and Skewness.
 

vinux

Dark Knight
#7
It seems to be the estimate of one of the Pearson family distribution.

I think it must be Pearson Type IV Distribution.
I can't help you more. :( ( I haven't touched my books for years! )
But still I try my best.

See this pdf..
http://www-cdf.fnal.gov/publications/cdf6820_pearson4.pdf

page 10 ( these are moment estimators. so it may not be the best estimates). kurtosis = beat2 and skewness = sqrt(beta1).
 

Dragan

Super Moderator
#8
Is there any way to generate random numbers in Excel (or VBA) giving 4 criterias including Mean, SD, Skewness and Kurtosis.
Raza
Yes, there are a few computationally efficient methods that will do precisely what you are asking (above) - and the associated algorithms are very "easy" to implement.

Note: I put "easy" in parentheses because I know the algorithms.

Again, I'll explain the methods to you if you're interested.
 
Last edited:
#9
non-normal RNG

Hi Dragan. I am interested in finding out how to solve for the coefficients
or what other techniques can be used. Also to clear my understanding
when someone says non-normal do they mean non-standard-normal?

Thanks.

1of4


Hi Raza: You have several options available. Do you know the values of skew and kurtosis that you're interested in specifying.

For example, suppose you wanted to generate a non-normal distribution with specified values of skew and kurtosis of 2 and 7 respectively.

You could use a Generalized Lambda Distribution; a (Tukey) g-and-h distribution; or power method polynomials. These are all simple transformations on either uniform or standard normal deviates.

For example, to obtain the distribution above I could a polynomial of order 3 as:

Y = -0.260023 + 0.761585*Z + 0.260023*Z^2 + 0.0530723*Z^3

where Z ~N(0,1).

The variable Y will have a mean of zero, variance of 1, skew=2, and kurtosis=7.

If your interested I can explain further on how to solve for the coefficients, or use the other techniques. I've done quite a bit of research in this area i.e. generating non-normal distributions with specified moments and correlation matrices.

Note: When I say kurtosis of 7 I am implying that normal kurtosis is scaled to 0 and not 3.
 

Dragan

Super Moderator
#10
Hi Dragan. I am interested in finding out how to solve for the coefficients
or what other techniques can be used. Also to clear my understanding
when someone says non-normal do they mean non-standard-normal?

Thanks.

1of4

To obtain the coefficients requires solving a system of nonlinear equations.

This is the system (below in Mathematica code) I used for third-order polynomials. There are boundary conditions on the skew (gamma1) and kurtosis (gamma2). For example, for symmetric distributions i.e. skew=0, the lower boundary of kurtosis is about -1.15 (I derived the boudary conditions in an article I published a few years ago, if you're interested I can get it for you).

Note: The mean and variance are set to 0 and 1. You can take a distribution and impose a linear tranformation on it and it will not change the values of the skew and kurtosis (or any other higher moments, for that matter).

Code:
gamma1 = 2;
gamma2 = 7;
FindRoot[{
    a + c == 0,
    b^2 + 6*b*d + 2*c^2 + 15*d^2 == 1,
    2*c*(b^2 + 24*b*d + 105*d^2 + 2) == gamma1,
    24*(b*d + c^2*(1 + b^2 + 28*b*d) + d^2*(12 + 48*b*d +
   141*c^2 + 225*d^2)) == gamma2},
  {a, -0.230}, {b, 0.88}, {c, 0.23}, {d, 0.019}, AccuracyGoal -> 24] 

Solutions:{a -> -0.260023, b -> 0.761585, c -> 0.260023, d -> 0.0530723}
Other techniques that I mentioned such as g-and-h transformations and the Generalized Lambda distributions also require the simulatanoeus solutions to a system of nonlinear equations.

These techiques that I mention are especially useful when you need multivariate non-normal distributions with a specified correlation matrix.

For example, suppose you wanted X1, X2, and X3 all with different values of skew and kurtosis and with correlations of r12=0.4; r13=0.5; r23=0.6. These techniques I mention handle this scenario very easily.

Note: when I say non-normal I mean that the values of skew and kurtosis are not equal to zero. A normal distribution has skew of 0 and kurtosis of 0.
 
#12
Hi Dragon,
I really want to know how to make"{a, -0.230}, {b, 0.88}, {c, 0.23}, {d, 0.019}, AccuracyGoal -> 24] " ? And would you please tell me the artical you mentioned "I derived the boudary conditions in an article I published a few years ago,"?

what's more, the polynomials have one more roots due to the non-homogeneous equations,which root should I choose?
 
Last edited:
#13
To obtain the coefficients requires solving a system of nonlinear equations.

This is the system (below in Mathematica code) I used for third-order polynomials. There are boundary conditions on the skew (gamma1) and kurtosis (gamma2). For example, for symmetric distributions i.e. skew=0, the lower boundary of kurtosis is about -1.15 (I derived the boudary conditions in an article I published a few years ago, if you're interested I can get it for you).

Note: The mean and variance are set to 0 and 1. You can take a distribution and impose a linear tranformation on it and it will not change the values of the skew and kurtosis (or any other higher moments, for that matter).

Code:
gamma1 = 2;
gamma2 = 7;
FindRoot[{
    a + c == 0,
    b^2 + 6*b*d + 2*c^2 + 15*d^2 == 1,
    2*c*(b^2 + 24*b*d + 105*d^2 + 2) == gamma1,
    24*(b*d + c^2*(1 + b^2 + 28*b*d) + d^2*(12 + 48*b*d +
   141*c^2 + 225*d^2)) == gamma2},
  {a, -0.230}, {b, 0.88}, {c, 0.23}, {d, 0.019}, AccuracyGoal -> 24] 

Solutions:{a -> -0.260023, b -> 0.761585, c -> 0.260023, d -> 0.0530723}
Other techniques that I mentioned such as g-and-h transformations and the Generalized Lambda distributions also require the simulatanoeus solutions to a system of nonlinear equations.

These techiques that I mention are especially useful when you need multivariate non-normal distributions with a specified correlation matrix.

For example, suppose you wanted X1, X2, and X3 all with different values of skew and kurtosis and with correlations of r12=0.4; r13=0.5; r23=0.6. These techniques I mention handle this scenario very easily.

Note: when I say non-normal I mean that the values of skew and kurtosis are not equal to zero. A normal distribution has skew of 0 and kurtosis of 0.
Hi Dragan. I am interested in generating Tukey's gh distribution in R software . could you please help me ?Do you know any paper or book including this codes?

Thanks.