Pattern Recognition & Trading Decisions

Preparation of Returns Data for Portfolio Program

The file Returns.txt in the CD-ROM that accompanies the book is derived from a trading system. For such systems, particularly with futures, capital to be allocated can be quite difficult to calculate and the return on that capital equally so. Table 1 is derived from file returns.txt where highly volatile returns are shown. These would not be typical of returns data for more conventional exploitation strategies.

Table 1 Returns Data from a Highly Volatile Trading System.

For stocks, with a trading system that just uses a “buy and hold” strategy, the situation is much less complex and the returns data is simply the percentage change in price over whatever incremental time period is being used for the calculation. For example, if the portfolio calculations were being done on a weekly basis, then the returns data would be given by:

Return =	100 x	(Closing Price|This Week - Closing Price|Last Week)
		Closing Price|Last Week

It is fairly easy to import a column of closing prices from a .cmx (or other) format of market data and do this calculation in an Excel spreadsheet.

Typically, the column of closing prices would be imported well to the right of an area where returns data was being aggregated. Figure 1 shows the situation after a column of closing prices has been imported into Excel. In this case, these are daily closing prices and taken from file SPX.cmx on the CD-ROM accompanying the book.

A (daily) version of the formula is now applied and can been seen in the formula bar in Figure 2. Note the position of the active cell (D4) in Figure 2 and the position of the other cells relative to it.

The cursor is now positioned at the bottom right of the active cell of Figure 2 and the symbol changes to a “+”, at which point it is dragged down the column to apply a corresponding formula to the other cells in the column.

The returns data can now be copied and pasted (using Edit >Paste Special > Values; to avoid pasting unwanted formulae) to the correct position on a spreadsheet of returns data.

General comments made in the article on preparing .cmx data about how to avoid spurious lines at the end of the file and unwanted formulae in a text file of returns data are also applicable here and should be digested if such problems are to be avoided.

Figure1 – A Column of Closing Prices. Note that these are daily. If a weekly interval was required then a corresponding column of adjacent weekly closing prices would be needed.

Figure 2 - Demonstration of Use of the Formula bar. Note the position of the active cell. When the cursor is moved to the bottom right of this cell, its symbol changes to a “+”, at which point the formula can be dragged down the column to calculate returns data for other cells, as shown in Figure 3.

Figure 3 - The completed returns data needs to be copied to clipboard and then put in the right place in a returns file using Edit > Paste Special > Values. If this is not done, then unwanted (hidden) formulae will appear in the returns file, which the portfolio program cannot cope with. Note the comments in file “DataPrep.pdf” about the dangers of hidden formulae and unwanted lines at the end of the file, and how to use Excel to prepare data to avoid these problems.