Data Tables 211

columns C to E resulting in amounts won or lost by the player. The numbers in cells

H5:H7 show, respectively, the number of times under each column from C to E, the

number called by the player showed up. The number in H5 is calculated by the formula

=COUNTIF(C5:C504,”>0”); similar formulas are used in H6 and H7.

Let us compare the simulation result with those that we can get by applying the basic

probability theory. With three dice, the total number of different face combinations

is 216 (=6*6*6). The probability that the called number will show up on one face is

75/216 (=3*(1/6)*(5/6)*(5/6)), and the probability that two faces will show up

with the called number is 15/216 (=3*(1/6)*(1/6)*(5/6)). The probability that

all three faces will show up with the called number is 1/216. Thus, out of 500 trials we

expect to win about 42% ((75+15+1)/216)) of the time. The numbers in H5:H7 are

close to this mark. These numbers would be even closer if we were to simulate 5000 or

more trials.

Example 9.2 Stock Return Simulation

In this example, we will carry out a simulation to determine what kind of return we

can expect on a particular stock. The basic assumption of our model is that the future

performance of a stock can be ascertained from its past performance. We assume that we

have historical data for the stock under consideration, say for five to ten years. To fore-

cast how the stock will behave in the first year, we randomly pick the stock’s monthly

gains/losses from a table of historical data 12 times and use those picks to calculate the

stock’s gain/loss in the first year. By repeating this process several hundreds of times,

we can calculate the average gain or loss and use it as an estimate of likely performance

in the first year. Obviously, it is a simplistic model and does not take into account many

Figure 9.8

Two-way chuck-a-luck data table.

Chapter 9 Data Analysis and Simulation212

other factors that govern the performance of a company’s stock. However, the model is

complex enough to illustrate how Excel can be used for such tasks.

To carry out this simulation, we need a data source for historical stock prices. There are

several such sources on the Internet; we will use the Yahoo! Finance site. Let us assume

that we want to look at Texas Instruments, stock symbol TXN. We can then access the

historical price data with the following steps. First, go to the Yahoo! Finance page at

http://finance.yahoo.com and then insert TXN in the stock quote window. Once the

quote page shows up, click at “5y” under the stock price chart. This will take you to the

five-year prices page where you will find a link to the historical price data). The histori-

cal data page allows us to specify the dates for which we want the data. Moreover, it

can be downloaded directly as an Excel file. We select five years of monthly data from

December 31, 2000, to December 31, 2005, and download it to our computer.

The downloaded data in the Excel file will appear as shown in Figure 9.9. It shows the

opening price, high and low prices of the day, the closing price, the daily volume, and

the adjusted closing price for every first day of the month the stock market is open. For

our simulation we need only the adjusted closing price for each month; we can then use

it to calculate monthly gains or losses. We, therefore, copy the columns showing the

month and the adjusted closing price into a new worksheet. We also insert a column

before the month column and the successive months are numbered from 1 to 60. We

add another column which shows a monthly gain or loss which is computed from the

adjusted price column. For example, the percent change in cell D2 is calculated as

=(C2-C3)*100/C3, that is by subtracting the current month’s closing price from the

closing price of the previous month and expressing it as a percentage change. The new

worksheet now appears similar to that shown in Figure 9.10. Note that the data has

Figure 9.9

Excel ﬁle of Texas Instruments’ historical stock prices downloaded from Yahoo! Finance.

Get *Computing with Excel and VBA* now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.