Strategy backtesting: automating signals and calculating returns

[oex]

Hey guys,

I am currently backtesting a volatility strategy in Excel 2007 and have stumbled upon some problems automating things. Seems hard to post a snapshot so I'm just gonna describe my sheet and problem...

Column 1: 20 day moving average of volatility

Column 2: 50 day moving average of volatility

Column 3: MA20/MA50 cross over or cross under, i.e. what generates buy/sell signal

Here I used =IF(G255>H255, "MA20/MA50 cross over", "MA20/MA50 cross under".

Column 4: MA20/MA50 signal

Here I used =IF(K256=K255,"","BUY") and THE FIRST PROBLEM here is that I obviously only get BUY SIGNALS even if it's a cross under. How do I get buy OR sell signal only for the instances where it changes from cross over to cross under or vice versa?

SECOND PROBLEM:

Column 5: Volatility index returns

When the 20 day MA crosses the 50 day MA (i.e. BUY/SELL signal), I want to automatically go LONG/SHORT the VIX-index (volatility) and measure the returns on these trades. Ideas on how to generate this without doing it manually?

Appreciate any help on this guys! Thanks in advance.

 

[oex]

[TABLE="width: 540"]
<TBODY>[TR]
[TD]MA20 SD[/TD]
[TD]MA50 SD[/TD]
[TD]Cross over/Cross under[/TD]
[TD]MA20/MA50 signal[/TD]
[TD]VIX price[/TD]
[TD]VIX signal[/TD]
[/TR]
[TR]
[TD="align: right"]1.52%[/TD]
[TD="align: right"]1.53%[/TD]
[TD]MA20/MA50 cross under[/TD]
[TD][/TD]
[TD="align: right"]27.17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1.62%[/TD]
[TD="align: right"]1.58%[/TD]
[TD]MA20/MA50 cross over[/TD]
[TD]BUY[/TD]
[TD="align: right"]31.74[/TD]
[TD]LONG[/TD]
[/TR]
[TR]
[TD="align: right"]1.64%[/TD]
[TD="align: right"]1.54%[/TD]
[TD]MA20/MA50 cross over[/TD]
[TD][/TD]
[TD="align: right"]29.66[/TD]
[TD]LONG[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL><COL span=2></COLGROUP>[/TABLE]

 

[tmdoherty]

Hi Folks,

I have a problem to solve.

I have a spreadsheet that selects two funds based upon an algorithm that is applied to a basket of funds. At the close of the last trading day of the month, the new fund selections are made (sometimes they change, sometimes they remain the same depending upon the quantities evaluated by the algorithms). I set up the spreadsheet in a way that assumes that at the end of the day of each month, both funds are sold, and immediately after that, the proceeds of these sales are used to buy the two funds for the next month in a 50/50 ratio (that is what Cells D3 and F3 specify). I will try to post a sample of the spreadsheet. Here is the description:

Column A: Dates
Column B: Stock Symbol of First Fund Selected
Column C: Stock Symbol of Second Fund Selected
Columns D and E: Daily adjusted closing prices for First Fund [Column B]
Columns F and G: Daily adjusted closing prices for Second Fund [Column C]
Column H: # of Shares of First Fund
Column I: # of Shares of Second Fund
Column J: Total value of shares in First Fund
Column K: Total value of shares in Second Fund
Column L: Sum of total values of shares in First and Second Fund

Each month, the fund prices shift from one column to the adjacent column (e.g., Column D to Column E). This was to allow me to calculate the closing prices for the month for the two funds, then to calculate the opening prices for the next month for the next two funds selected. You will see that there are calculations in Columns H thru L, and that these are intended to keep track of the daily values of each fund (Columns J and K) and the sum of the two funds (Column L).

Here's what I would like to do:

1. Create a way to automatically calculate all values in Columns H through L. That's the most important issue, since this is the step that is the most labor intensive right now.
2. Create a way to direct Excel to post the correct prices in Columns D thru G based on what fund is specified in Columns B and C.
3. Perform these for the each entire month, then when the new month arrives, repeat these calculations for the new funds selected.

Again, I have a small sample of the spreadsheet showing the above described columns, and will try to post that. But right now, I am not sure how to post attachments. Hopefully I will figure that out in a bit.

Thanks so much,

Terence