Prospect for Gold with Excel

There are dozens of ways to analyze stocks to find the best potential investments. But many of the most solid traditional methods have gone out the window in the dizzying dot-com boom and bust. Although you may not be able to tell when investors will tire of waiting for Amazon.com to turn a profit, you can make intelligent guesses about individual stock directions.

One way to predict stock movements is to look for correlations between the prices of different stocks. A correlation is a mathematical construct that shows whether stocks move up and down together. If you find a positive correlation, it tends to mean that good news for company A makes the share price of company B go up. This article illustrates the correlation process by tracking two stocks on the same day, but as you'll see in step 5, you can also compare price changes at different times. Although nothing is certain in stocks, knowing what stock A did today may let you predict what stock B will do tomorrow.

A new feature in Excel 2001, the Calculator, makes it easier to create stock-comparison formulas. Formulas are a problem area for many Excel users because it's easy to enter incorrect cell addresses. The Calculator makes formula construction easier: now when you click on a cell, the Calculator notes its address for you.

CHARLES SEITER has been writing for Macworld for 16 years. He specializes in scientific software and applications of statistics.

1: Get Stock Quotes Online
Plenty of excellent Internet services provide stock quotes-every online broker lists them, for example. I used Yahoo Finance because it offers free historical quotes in a format Microsoft Excel can read.

Go to http://finance.yahoo.com and enter a stock symbol, such as AMZN, for Amazon.com (as I did), in the Get Quotes space. Pull down the menu to the right of the Get Quotes button, select Chart, and then click on the Get Quotes button. This brings up a chart of stock quotes. Underneath this chart, click on the Historical Quotes link to bring up a table that shows how the stock has performed over the past three months (A).

Choose the Download Spreadsheet Format link, which is below the table, and save the resulting CSV file. Open the file in Excel 2001, as I've done here (B), so you can begin working with it.

2: Add a Second Set of Quotes
Next you need to get a set of quotes for another stock and put it next to the first set.

Simplify the historical data by deleting everything but the Date and Close columns in the worksheet (A).

Now follow the procedures in step 1 to download quotes for a second stock, such as Yahoo (YHOO). Put those values in another worksheet. Because you've downloaded the stock tables on the same day, their Date columns will match. Therefore, copy only your second stock's Close column, and paste it next to the set of values in the first worksheet.

You might want to label the Close columns with their stock-ticker symbols to avoid confusion (B). This gives you a worksheet with closing prices for the same days lined up, so you can calculate whether the stocks' day-to-day price changes are correlated.

3: Calculate Percent Change
Use the Calculator to convert the columns of closing prices to corresponding columns of day-to-day percent changes in prices.

Click on cell D2, and call up the Calculator (Tools: Calculator). Enter the formula for Amazon.com's column: (today's price - the previous day's price)/the previous day's price. Click on the opening parenthesis in the Calculator keypad and then on cell B2 (today's price) in the spreadsheet. You'll see the cell name appear in the Calculator window (A). Click on the Calculator's minus sign, then on cell B3 (the previous day's price), the Calculator's closing parenthesis, and the Calculator's division sign. Click on B3 again. The entire formula in the Calculator window should read =(B2-B3)/B3 . Click on the Calculator's OK button.

Press the enter key, copy cell D2, and click and drag from cell D3 to the bottom of column D. Choose Paste (command-V). Format the values as percentages by selecting the column, going to Format: Cells, and choosing Percentage from the list that appears. Now do the same for column C, =(C2-C3)/C3 , pasting into column E. You'll have two columns reporting percent changes (B).

4: Find the Correlation
To find the correlation between the stocks' price movements, multiply each day's changes for the two stocks, and then add these products for all the days.

For the first date, use the Calculator to multiply the percent difference in the two columns and place the result, =D2*E2 , in cell F2 (A).

Copy that formula down the column. Sum column F and multiply by 100 to simplify the number (B).

The result tells whether the price changes are correlated. If the result is near zero, there's no connection between price movements. If the result is positive, as it is in this case, the two prices tend to move together. If the result is negative, the stock prices tend to move in opposite directions.

Amazon.com and Yahoo are very different businesses, but they show some positive correlation-probably because investors see them both simply as "Internet stocks."

5: Prediction in Time
To make predictions, calculate the correlation between the price of your first stock at one date and the price of your second stock at an earlier date.

To look for predictive values, offset one column by its date.

Move one column of closing prices down one row (Insert: Cells: Shift Cells Down) (A). Now the stock prices that are next to each other refer to different days.

Following the procedures outlined in steps 3 and 4, I found the correlation between Monday's price change in Yahoo's stock and Friday's price change in Amazon.com's stock. Now any correlation is a prediction of tomorrow's change in Yahoo, based on today's change in Amazon.com. A positive correlation means that if AMZN goes up one day, YHOO will tend to go up the next. You can use this procedure to look for weekly or monthly correlation, too. Those data downloads are also available on Yahoo Finance when you click on the Historical Quotes link.

1 2 Page 1
Page 1 of 2
  
Shop Tech Products at Amazon