|
In Willow Tip 2002-11 we used Excel’s IF and AND functions to place Buy in the cell if the closing price crossed above the moving
average if and Sell if the closing price crossed below the moving average. The buy signals were displayed in column J and the sell
signals were displayed in column K on the Analysis worksheet. In this Willow Tip we combined the separate buy and sell formulas into a
single formula. The buy and sell signals are now contained in a single column (column J). Approximately 9 months of historical pricing
data showing the Date, Open, High, Low, Close and Volume is displayed in columns B – G. The formula to calculate the moving average is
in column I. The historical data is in reverse chronological order with latest date being at the top of the table and the oldest at
the bottom.
The macro code which lists the buy, sell and sell short transaction on the Returns worksheet is contained in the CalculateReturns
procedure and is attached to the Calculate Returns button. After you refreshed the historical pricing information or changed the moving
average, press the Calculate Returns button to run the macro to calculate the returns based on the new buy and sell signals.
Since the historical data is displayed in reverse chronological order, in order to calculate the results of the buy and sell
transactions, we need to work from the bottom to the top in column J. A technique we use to find the bottom row in a table of data is
to go down to cell well below the range of the data and to come up to the first cell containing data. We stored the value of the
row number of the bottom row of data in a variable lRow.
| lRow = .Range("$I$16000").End(xlUp).Row |
We then used a FOR loop to go through each cell of the signal column from the bottom to the top. VBA IF statements were used to
control what the macro would do if it encountered a buy or sell value. If there was a “Buy” value in the cell, the macro copied the
date and opening price of the following session to a table on the Returns worksheet. If the buy signal closed out an existing short
position then we added the text “Close Short “in the adjoining cell next to the date and price and also added an additional
transaction using the date and opening price and the text “Go Long” in the row below. If the buy signal did not close out an existing
short position, it was an opening position and only a single entry was made. A similar process was followed when there was a “Sell”
value in the cell.
We created two Boolean variables, to keep track of the long and short positions. Boolean data types display either a true or
false value. When the macro begins, both of these variables are set to false.
If a buy signal is generated, bLong is set to true while bShort remains false. When sell signal is encountered and bLong is true,
then we will need to first sell the long position and then go short. After the short position is created bShort is set to true.
The macro is well documented and for further details you can review the macro code. After you have downloaded and opened the
workbook, press Alt F11 to activate the Visual Basic Editor and view the macro code.
In order to accurately account for the current status of your position we added some code which adds the most recent last price to
the bottom of the list. Although the existing position may still be open, it could have an impact on your profit and loss and should
be included in you results. It uses the current closing price to calculate your profit or loss for your last open position.
Calculating the Profit or Loss
We placed the formula shown below in column F, next to the cells displaying the buy, sell and sell
short transactions which were generated by the buy and sell signals.
| =IF(D4="Go Long",C5-C4,IF(D4="Go Short", C4-C5,"")) |
Once the dates, prices and transaction descriptions for all the buy and sell signals were listed in
the cells of a worksheet, we could subtract the opening price from the closing price to determine the
profit or loss for a long position or subtract the closing price from the opening price to calculate
the profit or loss for a short position. If the transaction description did not contain “Go Long”
or “Go Short” then the cell was left empty.
After the profit or loss was calculated we used Excel’s SUM function to sum the results to get the total return
for all the transactions.
Excel is a Powerful Financial Modeling Tool
In this model we used a simple trend following trading system to calculate profits and losses. After you have used this
model to analyze the trading results of several instruments, you will see that it works best if there is a significant move
up or down. Sideward price action results in losses even with longer moving averages.
|
