I recently got e-mail asking about some spreadsheet I had written some time ago. I (finally!) found it ... and it was quite interesting.

>What did it do?
You typed in some stock symbol, clicked a button and downloaded a year's worth of prices. Then, again with the click of a button, you got a 20-day prediction of the future price evolution of the stock. Predictions were made by using a random selection of 20 daily returns ... assuming a Normal Distribution of daily returns with the Mean and Standard Deviation calculated from the downloaded data.

The spreadsheet gave a graphical display of possible 20-day futures, like this (along with some forecasted moving averages and Bollinger bands).

Anyway, I got to thinking ...

>Bad move!
... that I should see if the predictions were any good. So this is what I did:

1. A random date is generated ... some time over the past dozen years or so.
2. A year's worth of prices is downloaded, ending at that date.
3. The last 20 days of data is ignored and a 20-day forecast is made starting with the price 20 days before the end of the downloaded data (picking, at random, from all the daily returns ... except the last 20 days).
4. Step 3 is repeated a thousand times and the average price prediction is calculated
5. This average price prediction is then compared to the actual price (which, of course, is the last day of downloaded data).

>You didn't assume a Normal distribution?
No, I gave that up. Somebuddy might ask, "Why not lognormal?" ... or another distribution.
So, to avoid that, I used the actual daily returns over the past year or so.

>And?
Then I repeated steps 1 to 5 a couple of dozen times, each using some random date and ...

>And? Then I gazed in awe and amazement at the results and ... >It was that good? Uh ... no, it was that bad!

Figure 1

>You predict 822 and it ends up at 949? That is bad! So you gave up, eh?
Hardly. However, I wondered whether a simple comparison between the predicted and actual prices was a good measure of how well the prediction was. Should I calculate the percentage error? Was a 1% error "good"? Maybe I should ...

>Why not see how much money I could make ... using that forecasting scheme? How clever of you! That's exactly what I thought, so I considered two investors, Sam and Sally. Sam looked at the predicted price and, if it predicted an increase, he'd invest in the stock, selling at the end of 20 days. If it predicted an decrease he wouldn't invest, but keep his money under a pillow. Sally, on the other hand, always invested at the start of the 20 days and sold at the end. >Then you saw who made more money, eh? Exactly. >And? The dates and starting prices ... and the actual vs predicted prices (20 days after the starting date) are shown in Figure 2. >And? And, for two dozen 20-day predictions (using GE stock and random dates, as described above), Sam's gain was 114% while Sally's gain was 97%. >That was one set of two dozen predictions, eh? Yes, each starting at a different date, involving the average of a thousand 20-day forecasts.

Figure 2

>And if you tried that again?
The second time I tried it (for a different set of random starting dates), Sam got 139% and Sally 44%.

>And if you tried that again?
No, I'm tired of playing with the spreadsheet. It's fiction, you know. Anyway, if you'd like to play, you can download a ZIPd file.
Just RIGHT-clicking and here and Save Target.

I might also mention that, often (maybe very often) Sally does MUCH better than Sam

>So what good is the spreadsheet?
It's great fun, no?

>No.