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 stocks monthly
gains/losses from a table of historical data 12 times and use those picks to calculate the
stocks 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 companys 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 months 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 file 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.