Coin Tosses and Stock Price Charts

Prior to the 1960’s, most investors believed that future securities prices could be predicted (and that great riches were to be had) if only they could discover the secret. Many investors still believe this today, despite much evidence that suggests that they would be best served by simply buying and holding the entire market (investing in index funds) rather than trying to pick individual stocks.

The efficient markets hypothesis (EMH) essentially states that techniques such as fundamental and technical analysis cannot be used to consistently earn excess risk-adjusted profits in the long run.  The EMH began with the observation that changes in securities prices appear to follow a random walk (technically, geometric Brownian motion with a positive drift). The random walk hypothesis was first proposed by mathematician Louis Bachelier in his doctoral thesis in 1900, and then promptly forgotten. Further evidence of randomness was occasionally reported by others, most famously by the statistician M.G. Kendall in 1953. Many others followed in the late 1950’s and 1960’s.

One of the best-known stories regarding the randomness of changes in stock prices is told by Burton Malkiel in A Random Walk Down Wall Street (a fascinating practitioner-oriented book now in its 13th edition). In that book he tells of having fooled a friend, who was a committed technical analyst, by showing him a “stock chart” that was generated by coin tosses, rather than actual stock prices. Apparently, the friend was quite interested in learning the name of the stock.

The purpose of this post is not to debate market efficiency (so please don’t email me about that subject — it is almost a religious debate among some), or to even state that the EMH is correct. Instead, as this blog is focused on Excel, it is to demonstrate how we can simulate coin tosses and use those simulated tosses to generate “stock charts” of the kind that Malkiel discussed.

Generating Coin Tosses with Rand()

Our first task is to generate a sufficient number of coin tosses to create the chart. There are many ways that we might go about doing so, and I will first show the easy way.

Realize that a coin toss can be represented by a binary variable, where 0 is tails and 1 is heads. This is a binomially distributed variable with a probability of a “success” of 50% (p = 0.5), assuming that the coin is fair. We could use the random number generator from the Analysis ToolPak add-in to generate these tosses, but it suits my purposes better to create a formula:

=Round(Rand(),0)

The Rand() function generates a uniformly distributed random number between 0 and 1. I apply the Round() function to the result so that if the random number is 0.5 or greater it will get converted to a 1. If it is less than 0.5 it will get converted to a 0. We will treat a 1 as a head, and a 0 as a tail. Furthermore, if the result is heads the stock will go up, and if it is tails then the stock price will decline.

Now, we know that stock prices tend to rise over time so we will say that daily price changes are slightly asymmetric. Specifically, let’s say that the price will rise by 0.25% if a head is tossed, or fall by 0.20% if a tail is tossed for a given day. Also, we need to set a starting price, so let’s choose $100. Create a worksheet that looks like the one in the picture below:

Enter the data as shown in A1:B5. In B6, just link to the beginning price with the formula: =B3. In A7 we will calculate our first coin toss with the formula:

=Round(Rand(),0)

In B7 we will calculate the new stock price with an IF() statement:

=IF(A7=1,B6*(1+\$B\$1),B6*(1-\$B\$2))

Finally, copy the formulas from A7:B7 down the sheet to row 156. That will give us 150 coin tosses and 151 days of stock price “history.”

We can now create a line chart of the resulting stock prices:

Notice that I have marked a few typical technical chart patterns that make an appearance in the coin flip-generated chart. There is a double top, a resistance level, and an up trend. Press F9 to recalculate the sheet and you will get a fresh round of coin tosses and a new chart. If you do that enough times, you can see all of the chart patterns that are discussed in such books as Technical Analysis of Stock Trends by Edwards and Magee (often called the Bible of technical chart patterns).

A More Realistic Chart

The chart generated above is perfectly functional and makes the point, but it doesn’t look as realistic as it could. There are at least two improvements that could be made:

• Use a more realistic model for price evolution over time.

• Generate a High-Low-Close chart instead of a simple line chart.

Typically, stock prices are simulated using a stochastic process known as geometric Brownian motion. Using this model, we can generate returns by using the following formula:

$$\frac{\partial{S}}{S}=\mu \, \partial{t} + \sigma \, \partial{X}$$

That formula simply says that the change in the stock price is equal to the mean change per unit of time ($\mu$, known as the drift) plus a random shock. The shock is simply the standard deviation ($\sigma$) of the returns times a standard normal deviate (mean of 0, standard deviation of 1). If the standard deviation is 0, then the stock price would simply increase by the amount of the drift in each period. So, it is the second term that determines the random up and down fluctuations in the price. We can calculate the next stock price by simply multiplying the previous price by one plus the result of that formula.

I no longer need the coin toss metaphor, but I’m going to keep it. It doesn’t hurt anything, and it makes the demonstration more interesting. So, the closing price will go up or down by the absolute value of the result of the Brownian motion equation. For heads the price will go up, for tails it will go down. Note that the coin toss is still determined by generating a uniformly distributed random number as done originally.

To generate the high and low prices for the day, I use the same formula. However, the base price is the day’s closing price instead of the previous day’s price. This way, I can tie the high and low to the same day’s close. This also allows me to make sure that the high price is always greater than the close, and the low price is always less than the close. Still, the amounts by which the high and low deviate from the close are determined randomly using the same mean and standard deviation.

To do all of this requires that I generate four random numbers for each day. The first is for the coin toss. The other three are the standard normal variables that get fed into the Brownian motion formula to determine the high, low, and close for the day. I use VBA to generate these numbers and to change the scale of the Y axis in the HLC chart so that it looks reasonably good. The code is quite straightforward, so I won’t cover it here. If you are interested, you can download the example workbook (see below and note that this is a macro-enabled workbook, so you will likely get a security warning) and examine the code by opening the VBA editor (Alt-F11). Note that the random numbers are in columns A:D, which are hidden from view. Simply unhide those columns to see the output.

The picture below shows the resulting worksheet.

Note that there are two buttons on the worksheet. Clicking the “Flip Coins” button will cause new coin tosses to be regenerated and the new values for the chart to be calculated. The “Copy Chart to Clipboard” button does exactly that. The purpose of that button is to make it easy to capture a particularly interesting chart so that you can show it to your technical analyst friends just like Malkiel did!