Simon Benninga. Financial Modelling 3-rd edition

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

408 Chapter 15

15.5.1 How Did We Produce the Bootstrapped Data?

Bootstrapping basically consists of reshufﬂ ing the data randomly, and

then viewing each reshufﬂ e as a point in a distribution. In the preceding

spreadsheet, columns C, E, and G contain random numbers. The VBA

program that follows (which is operated on the spreadsheet fm3_

chapter15.xls by pressing [Ctrl] + a) contains three For loops that insert

three columns of random numbers into the spreadsheet.

Having inserted the random numbers, the spreadsheet then uses

Excel’s Sort function to sort the index prices (column B), the foreign

interest rates (column D), and the exchange rate (column F). This pro-

cedure produces random combinations of the three portfolio pricing

factors, which give the resulting portfolio values in column H and the

portfolio return in cell F3).

‘My thanks to Marek Jochec for cleaning up

this code!

Sub randomizeit()

Range(“starttime”) = Time

Range(“J7:J15000”).ClearContents

Application.ScreenUpdating = False

For Iteration = 1 To Range(“iterations”)

For Row = 1 To 40

Range(“IndexRand”).Cells(Row, 1) = Rnd

Next Row

For Row = 1 To 40

Range(“InterestRand”).Cells(Row, 1) = Rnd

Next Row

For Row = 1 To 40

Range(“ExchangeRand”).Cells(Row, 1) = Rnd

Next Row

409 Value at Risk

Having produced the bootstrapped data, we use the array function

Frequency (see Chapter 35) to produce a distribution of the simulated

data.

Notice that this simulation takes a very long time! On the author’s

laptop 5,000 simulations took almost 17 minutes.

Appendix: How to Bootstrap: Making a Bingo Card in Excel

Bootstrapping refers to a technique of random shufﬂ ing of data to create

more “data.” This appendix gives a simple illustration of bootstrapping.

It is based on the “birthday bingo” game created for Helena Benninga’s

85th birthday. The game goes like this:

•

Everyone gets a “Helen Bingo Card,” which has ﬁ ve columns of ﬁ ve

numbers each. The ﬁ rst column has ﬁ ve numbers from 1 to 17, the second

column ﬁ ve numbers between 18 and 34, and so on. A typical card looks

like this:

Range(“B7:C46”).Sort Key1:=Range(“C6”),

Order1:=xlAscending, _

Header:=xlNo

Range(“D7:E46”).Sort Key1:=Range(“E6”),

Order1:=xlAscending, _

Header:=xlNo

Range(“F7:G46”).Sort Key1:=Range(“G6”),

Order1:=xlAscending, _

Header:=xlNo

Range(“returndata”).Cells(Iteration, 1) =

Range(“meanreturn”)

Next Iteration

Range(“elapsed”) = Time - Range(“starttime”)

End Sub

410 Chapter 15

•

We made up 85 questions with answers from 1 to 85. When a card with

a question was drawn, someone had to give the correct answer, and then

everyone who had the number on his or her card could cross it out. For

example, if we asked, “How many grandchildren does Helen have?” and

someone answered, “Thirteen,” then everyone with a 13 in the ﬁ rst

column could cross it out.

•

The ﬁ rst person with ﬁ ve numbers in a line (a column, a row, or a

diagonal) won the prize. (Note that it didn’t take any talent to win—all

you had to do was hear the right answers.)

We wanted to use Excel to create the cards, but it wasn’t initially clear

how to go about this task. Finally, the requisite trick, which is that we

want to model the selection of balls from an urn without replacement,

was discovered. (We will discuss this topic in greater detail later.)

The Trick

The trick is very simple. As an illustration, suppose we want to make a

random draw of ﬁ ve numbers between 1 and 17. (These will be the ﬁ ve

numbers that will appear in the ﬁ rst column of a particular Helen Bingo

card.) Here’s how we go about it:

•

First create a list of numbers from 1 to 17 and an adjoining column of

random numbers. This step will give something that looks like the

following:

HELEN

3 23515275

15 26 40 57 70

9 21506882

7 22495671

8 20455569

411 Value at Risk

The list of numbers was itself created in two stages: In the ﬁ rst stage

=Rand() was entered into each of the cells B2 : B18. In the second stage

B2 : B18 were copied and were then pasted special back into their loca-

tions using Edit|Paste Special|Values. This procedure gets rid of the for-

mulas behind the numbers (else Rand() will change its values every time

we hit [Enter] ).

•

Next, sort both columns using the second column as a sorting key. First

mark off the relevant data, and then use the Excel command Data|Sort.

This will bring up the following screen, in which I’ve chosen to sort the

data by Column B.

1 0.653152

2 0.425876

3 0.743173

4 0.911709

5 0.104356

6 0.09228

7 0.49608

8 0.210725

9 0.740506

10 0.724376

11 0.310175

12 0.437225

13 0.197224

14 0.145462

15 0.797405

16 0.52166

17 0.438188

EXPLAINING

THE TRICK

412 Chapter 15

•

In this case the sort command will give

1 0.041996

16 0.057147

7 0.089137

17 0.18458

9 0.185816

4 0.201975

3 0.231535

11 0.493658

10 0.58846

6 0.60949

2 0.638563

14 0.667014

5 0.678208

13 0.683465

8 0.762878

15 0.815158

12 0.924981

EXPLAINING

THE TRICK

413 Value at Risk

•

Finally, pick the ﬁ rst ﬁ ve numbers from the ﬁ rst column (in this example,

1, 16, 7, 17, 9). You could, of course, equally well pick the last ﬁ ve, the

middle ﬁ ve, or any other ﬁ ve numbers from the column.

The Probabilistic Model

What we’re doing here is just like picking random numbers out of an urn

without replacement. This model, standard in all introductory probability

books, imagines an urn ﬁ lled with balls. Each ball has a different

number—in our case, there are 17 balls with numbers between 1 and 17.

The urn is shaken to mix up the balls, and then ﬁ ve balls are drawn out.

Each ball, once drawn, is not placed back in the urn.

This model is somewhat different from the standard random-number

generators, which pick random numbers with replacement (i.e., once the

ball’s number is recorded, it is placed back in the urn, so that it could

possibly be drawn again).

Writing a VBA Program

The next obvious step was to write a program in VBA to automate the

procedure. Here is the spreadsheet:

4. Excel has a function Randbetween(low,high) which lets you create random integers

between low and high. Thus, to create ﬁ ve numbers between 1 and 17, you just copy

=Randbetween(1,17) into ﬁ ve adjacent cells. However, this is like drawing numbers

from the urn with replacement and hence can give you multiple draws of the same

number—a bingo no-no!

414 Chapter 15

BC D E F G H I

HELEN

2 20476373

15 18 39 68 78

5 31435880

11 19 44 65 69

17 26 51 64 76

Ctrl + b runs the

macro

The code that produced this spreadsheet is as follows:

5. I thank Paul Legerer for vastly improving the code for this program from the previous

edition of Financial Modeling. An astute reader will note that Paul’s program internal-

izes the sorting of the random numbers in the VBA code, so that only the printing of

the card is done in the spreadsheet.

415 Value at Risk

Public Const NperR = 17

Public Const BingoRows = 5

Public Const BingoColumns = 5

Public Const BingoCard = “C6:G10”

Option Base 1

Sub DoIt()

‘loop 5 time (1 loop for each

‘column on the bingo card)

For iii = 1 To BingoColumns

Dim ArraySort(NperR, 2)

For i = 1 To NperR

‘ﬁ rst dimension of the array:

‘random number between 0 and 1

ArraySort(i, 1) = Rnd

‘second dimension of the array:

‘position in the array (1-17 in the ﬁ rst loop,

‘18 to 34 in the second loop, etc...)

ArraySort(i, 2) = i + (iii - 1) * NperR

Next i

For ii = 1 To NperR

‘look for the minimum value in the array

‘and keep also the value of the position (1

to 17)

MinNum = ArraySort(ii, 1)

MinIndex = ArraySort(ii, 2)

RealIndex = ii

For i = ii To NperR

If ArraySort(i, 1) < MinNum Then

MinNum = ArraySort(i, 1)

MinIndex = ArraySort(i, 2)

RealIndex = i

End If

416 Chapter 15

Next i

‘Replace the ﬁ rst number in the array by the

‘minimum value and...

TempNum = ArraySort(ii, 1)

TempIndex = ArraySort(ii, 2)

ArraySort(ii, 1) = MinNum

ArraySort(ii, 2) = MinIndex

ArraySort(RealIndex, 1) = TempNum

ArraySort(RealIndex, 2) = TempIndex

‘start again with the remaining numbers: once

the

‘the last loop is completed, all numbers are

sorted

Next ii

‘write the ﬁ rst 5 numbers (number of rows on

the

‘bingo card) of the results into the

spreadsheet

With ActiveSheet.Range(BingoCard)

For ii = 1 To BingoRows

.Cells(ii, iii) = ArraySort(ii, 2)

Next ii

End With

Next iii

End Sub

Another Way to Do the Bingo Cards

There’s another way to design the bingo cards, using the Excel Rank

function:

6. I thank A. C. M. de Bakker for the suggestion in this subsection.

417 Value at Risk

AB C D E F G

HELEN

1 31366383

=(F$9-1)*17+RANK(F10,F$10:F$26)

3 27385884

=(F$9-1)*17+RANK(F11,F$10:F$26)

11 32 44 55 78

2 23356875

6 19456176

12345

0.9562 0.2730 0.8788 0.2574 0.0735 <

=RAND()

0.8333 0.5145 0.7366 0.5556 0.0702 <

=RAND()

0.4827 0.2727 0.4318 0.8825 0.5355 <

=RAND()

0.8475 0.6533 0.9783 0.0103 0.6092

0.7706 0.9582 0.3832 0.3485 0.6019

0.4284 0.6652 0.2587 0.6039 0.9998

0.2066 0.3386 0.0672 0.0924 0.5632

0.6735 0.1244 0.3091 0.8998 0.8532

0.2589 0.6032 0.0847 0.4665 0.4054

0.6858 0.9602 0.4834 0.6650 0.8638

0.3187 0.6513 0.2867 0.8896 0.0098

0.3953 0.1872 0.3402 0.1919 0.7563

0.8029 0.3386 0.5020 0.2635 0.8696

0.8241 0.5994 0.7240 0.0538 0.9613

0.5427 0.9184 0.7793 0.4888 0.2180

0.7136 0.4916 0.4389 0.9486 0.1391

0.0453 0.8367 0.5422 0.2381 0.5272

The entries in rows 10–26 are created with Rand(). The entries of the

bingo card are computed with the formula:

=∗

↑

(F$9-1) 17

Guarantees that all

numbers in column

F aremorethan688

(and likewise for other

columns)

RANK(F10,F$10:F$26)



↑

aat is the rank of F10 among F10:F26?

When copied down one cell:

What is tthe rank of F11among F10:F26?

etc.

 

The only disadvantage of this very clever implementation of bootstrap-

ping is that the entries on the card change with each recomputation of

the spreadsheet.