Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

748 Chapter 29

Assigning a Macro to a Button or to a Control Sequence

In the preceding spreadsheet, we have assigned the macro RandomList to

the button marked “RandomList Macro.” As explained in Chapter 38, any

drawing shape in Excel can be assigned a VBA program. In this case we have

created a rectangle; right-clicking on this rectangle, we have assigned it a

macro:

749 Generating Random Numbers

29.3 Testing Random-Number Generators

Producing lists of random numbers is interesting, though a bit uninfor-

mative. Is the list of numbers thus produced really uniformly distributed?

A simple test is to generate each number and determine whether it falls

into the interval [0, 0.1), [0.1, 0.2), . . . , [0.9, 1). The notation [a, b) denotes

the half-open interval between a and b; a number x is in this interval if

a ≤ x < b. If the list of numbers is really uniformly distributed, we would

expect roughly an even number of the “random” numbers to be in each

of the ten intervals.

One way to test uniformity is to generate a list of random numbers on

the spreadsheet by copying Rand( ) to many cells and then using the

Excel array function Frequency(data_array,bins_array).

This procedure

is illustrated in the following spreadsheet picture:

Elsewhere in this book, we usually assign macros a control sequence. After

clicking on Tools|Macro|Macros, this particular macro RandomList has been

assigned the sequence [Ctrl]+a:

5. Array functions are explained in Chapter 34.

750 Chapter 29

This method is obviously not efﬁ cient (or even feasible) when we want

to test the random-number generator for large numbers of random

draws. The following program uses VBA to generate many random

numbers and puts them into the bins in range A3:A12:

ABCDE F

Random

numbers

Bin Frequency

0.8978 <

=RAND( ) 0.1 0

0.8354 0.2 1

0.5188 0.3 1

0.7317 0.4 0

0.5067 0.5 0 <

=FREQUENCY(A3:A12,D3:D12)

0.2418 0.6 4

0.6406 0.7 1

0.1228 0.8 1

0.5611 0.9 2

0.5543 1 0

USING EXCEL'S FREQUENCY FUNCTION

TO TEST THE DISTRIBUTION OF RAND( )

Each cell in the range A3:A12 contains the formula Rand( ). Pressing F9 will produce a new set of

random numbers and frequencies.

Sub UniformRandom()

‘Puts random numbers into bins

Range(“E3”) = Time

N = Range(“B2”).Value ‘the number of

random draws

Dim distribution(10) As Long ‘bins

For k = 1 To N

d4raw = Rnd

distribution(Int(draw * 10) + 1) = _

distribution(Int(draw * 10) + 1) + 1

Next k

751 Generating Random Numbers

The output from this program produces the following spreadsheet:

For Index = 1 To 10

Range(“B5”).Cells(Index, 1) = _

distribution(Index)

Next Index

Range(“E4”) = Time

End Sub

Here are some things to note about UniformRandom:

•

The program has a “clock” to measure the amount of time it takes to

run. At the start of the program, we use Range(“E3”)=Time to put the

current time into cell E3. At the end of the program, Range(“E4”)=Time

puts in the ending time. The cell elapsed contains the formula =stoptime-

starttime. Note that in order for the cells to read correctly, you have to

use the command Format|Cells|Number|Time on the relevant cells.

752 Chapter 29

•

The heart of the program uses the function Int(draw * 10) + 1.

Multiplying the random draw by 10 produces a number whose ﬁ rst digit

is 0, 1, . . . , or 9. The VBA function Int gives this integer. Distribution is

a VBA array numbered 1 to 10, with Distribution(1) being the number

of random numbers in [0, 0.1), Distribution(2) the number of random

numbers in [0.1, 0.2), etc. Thus Int(draw * 10) + 1 is the proper

place in Distribution to which the current random draw belongs.

29.3.1 Using Randomize to Produce the Same List (or Not) of Random

Numbers

Most random-number generators use the last generated “random”

number to produce the next.

The ﬁ rst number used in a particular

sequence is controlled by the “seed,” which is typically taken from the

computer’s clock. VBA’s Rnd is no exception, but it allows you to control

the seed by using the command Randomize. The two small programs that

follow illustrate two uses of this command.

•

Using Randomize without any numeric argument resets the seed

(meaning that it breaks the connection between the next random number

and the current random number). This approach is illustrated in the

macro Random_EachDifferent, though it is difﬁ cult to see the effect.

Sub Random_EachDifferent()

‘Produces a list of random numbers

Randomize

‘Initializes the VBA random-number generator

For Index = 1 To 10

Range(“A5”).Cells(Index, 1) = Rnd()

Next Index

End Sub

•

Using Randomize(seed) uses a particular number as the seed.

•

Using the sequence of commands Rnd(negative number) and

Randomize(seed) guarantees the same sequence of random numbers.

This approach is illustrated in the macro Random_Same.

6. There are more examples in the exercises at the end of the chapter.

753 Generating Random Numbers

In the following spreadsheet, pushing the top button produces a

random set of random numbers. Pushing the bottom button activates the

macro Random_Same and produces the same set of random numbers

each time—provided the Seed (cell B2) is not changed.

Sub Random_Same()

‘Produces the same list of random numbers

‘which is always the same

Rnd (-4)

Randomize (Range(“seed”)) ‘Initializes the VBA

‘random-number generator

For Index = 1 To 10

Range(“B5”).Cells(Index, 1) = Rnd()

Next Index

End Sub

754 Chapter 29

29.4 Generating Normally Distributed Random Numbers

In the preceding sections we have generated numbers that are uniformly

distributed. In this section we explore four ways to produce normally

distributed random numbers using Excel.

29.4.1 Method 1: Normally Distributed Numbers Using Tools|Data

Analysis|Random Number Generation

One way to generate normally distributed random numbers is to use

the Excel command Tools|Data Analysis|Random Number Generation.

Here’s how we get Excel to produce 1,000 random numbers that are

normally distributed (with μ = 0 and σ = 1) in column G of the

spreadsheet:

755 Generating Random Numbers

If we want to see whether the output is distributed normally, we

can have Excel do a frequency distribution (either by using the

array function Frequency or by using Tools|Data Analysis|Histogram).

As the preceding graph shows, the output appears to be normally

distributed.

29.4.2 Method 2: Normally Distributed Numbers Using NormInvS( )

Excel’s NormSInv( ) function inverts the function NormSDist. Given

a number x between 0 and 1, NormSInv(x) produces number y

such that NormSDist(y) = x. The function NormSDist(Rand( )) should

produce a set of random numbers that is distributed standard

normal:

CBA

Any number between 0 and 1 0.6

Normal number 0.2533471 <

=NORMSINV(B2)

Check: 0.6 <

=NORMSDIST(B3)

Random normal number 0.56005094 <

=NORMSDIST(RAND( ))

NORMALLY DISTRIBUTED RANDOM

NUMBERS USING NORMSINV( )

In the following spreadsheet we produce 114 iterations of

NormSInv(Rand( )) and graph the resulting frequencies. They look nor-

mally distributed:

756 Chapter 29

DCBA FE

Any number between 0 and 1 0.6

Normal number 0.2533 <

=NORMSINV(B2)

Check: 0.6 <

=NORMSDIST(B3)

Random normal number 0.8169 <

=NORMSDIST(RAND())

Random numbers

produced with Rand()

NormSInv Bin Frequency

0.6782 0.4627 <

=NORMSINV(A9) -3 0

0.8742 1.1465 <

=NORMSINV(A10) -2.8 0

0.7633 0.7169 <

=NORMSINV(A11) -2.6 0 <

{=FREQUENCY(B9:B122,D9:D39)}

0.6200 0.3055 <

=NORMSINV(A12) -2.4 0

0.1892 -0.8807 -2.2 1

0.2723 -0.6059 -2 1

0.0581 -1.5713 -1.8 1

0.3430 -0.4043 -1.6 4

0.8523 1.0463 -1.4 5

0.1417 -1.0729 -1.2 4

0.5779 0.1964 -1 9

0.7166 0.5727 -0.8 4

0.7906 0.8087 -0.6 8

0.7271 0.6042 -0.4 3

0.8165 0.9022 -0.2 4

0.0630 -1.5301 0 9

0.1385 -1.0873 0.2 8

0.6820 0.4733 0.4 9

0.5590 0.1485 0.6 11

0.5977 0.2474 0.8 9

0.6750 0.4538 1 9

0.4470 -0.1333 1.2 8

0.3598 -0.3589 1.4 4

0.6483 0.3807 1.6 1

0.6590 0.4098 1.8 1

0.2214 -0.7673 2 0

NORMALLY DISTRIBUTED RANDOM

NUMBERS USING NORMSINV( )

Plotting 114 numbers produced with NormSInv(Rand( ))

F9 produces a new series

-3 -2 -1 0 1 2 3

29.4.3 Method 3: Incorporating NormSInv( ) into VBA

We can incorporate NormSInv( ) into a VBA program. The VBA program

NormStandardRandom uses NormSInv to produce random deviates.

Here are the program, a comment, and the output it produces:

Sub NormStandardRandom()

‘Produces a list of normally distributed

‘random numbers

Randomize ‘Initializes the VBA random-number

‘generator

Application.ScreenUpdating = False

Range(“E2”) = Time

757 Generating Random Numbers

The program NormStandardRandom includes two lines that measure

the time taken for the whole simulation to run. The program is very slow,

largely because of repeated calls on the spreadsheet function. As you will

see in the sample screen that follows, 10,000 runs of the program take

almost seven minutes (the button operates the macro):

Range(“A8”).Range(Cells(1, 1), _

Cells(64000, 1)).Clear

N = Range(“B2”).Value

For Index = 1 To N

Range(“A8”).Cells(Index, 1) _

= Application.NormSInv(Rnd())

Next Index

Range(“E3”) = Time

End Sub