Develop Customized Trading Strategy Backtesting Tools with Excel VBA

This will include a step-by-step breakdown of setting up the Excel spreadsheet, writing the VBA code, and running the backtest.

Step 1: Set Up the Excel Spreadsheet

Before diving into the VBA code, let’s first set up the Excel spreadsheet. We’ll assume you have historical price data, which is necessary for backtesting.

Layout of the Spreadsheet:

1. Historical Data: Typically, your historical price data will be in columns like:
   • Column A: Date
   • Column B: Open Price
   • Column C: High Price
   • Column D: Low Price
   • Column E: Close Price
   • Column F: Volume (optional, but useful for some strategies)
2. Strategy Inputs: In another section of the spreadsheet, we will define the inputs for the trading strategy (like moving averages, thresholds, etc.).
   • Cell H1: Fast Moving Average Period (e.g., 50)
   • Cell H2: Slow Moving Average Period (e.g., 200)
   • Cell H3: Buy Threshold (e.g., 1.02 for 2% above the slow moving average)
3. Backtest Results:
   • Column G: Signals (Buy/Sell)
   • Column H: Trade Position (Long/Short)
   • Column I: Portfolio Value (calculated based on trading decisions)
   • Column J: P&L (Profit and Loss) per trade.

Example Spreadsheet Layout:

   A         |   B     |   C     |   D     |   E     |   F     |   G   |   H    |   I    |   J

1 Date       | Open    | High    | Low     | Close   | Volume  | Signal | Position | Portfolio | P&L

2 2020-01-01 | 100     | 105     | 98      | 102     | 10000   |        |          |           |

3 2020-01-02 | 102     | 106     | 101     | 104     | 12000   |        |          |           |

…

Step 2: Writing the VBA Code

Now, let’s write the VBA code that will implement the backtesting logic. The code will:

1. Calculate moving averages for the chosen periods.
2. Generate trading signals (buy/sell) based on the strategy.
3. Track the portfolio’s value and calculate profit/loss.

Example VBA Code:

1. Open the Visual Basic for Applications (VBA) editor in Excel (Press Alt + F11).
2. Insert a Module (Right-click in the Project Explorer > Insert > Module).
3. Copy and paste the following VBA code:

Sub BacktestStrategy()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim fastMA As Integer
    Dim slowMA As Integer
    Dim closePrice As Double
    Dim fastMAValue As Double
    Dim slowMAValue As Double
    Dim signal As String
    Dim position As String
    Dim portfolioValue As Double
    Dim tradeSize As Double
    Dim initialCapital As Double
    Dim pnl As Double
    Dim totalPnL As Double   
    ' Set up the worksheet and strategy parameters
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    fastMA = ws.Range("H1").Value  ' Fast Moving Average period
    slowMA = ws.Range("H2").Value  ' Slow Moving Average period
    initialCapital = 100000  ' Example starting capital
    tradeSize = 1000  ' Number of shares per trade   
    ' Initialize portfolio value and total PnL
    portfolioValue = initialCapital
    totalPnL = 0   
    ' Loop through the data and perform the backtest
    For i = slowMA + 1 To lastRow ' Start from the point where we have enough data for moving averages
        closePrice = ws.Cells(i, 5).Value  ' Close price column       
        ' Calculate moving averages
        fastMAValue = Application.WorksheetFunction.Average(ws.Range(ws.Cells(i - fastMA + 1, 5), ws.Cells(i, 5)))
        slowMAValue = Application.WorksheetFunction.Average(ws.Range(ws.Cells(i - slowMA + 1, 5), ws.Cells(i, 5)))
        ' Generate buy/sell signal based on strategy
        If fastMAValue > slowMAValue * 1.02 Then ' Fast MA crosses 2% above Slow MA
            signal = "Buy"
        ElseIf fastMAValue < slowMAValue * 0.98 Then ' Fast MA crosses 2% below Slow MA
            signal = "Sell"
        Else
            signal = "Hold"
        End If       
        ' Apply strategy logic: Buy, Sell, or Hold
        If signal = "Buy" And position <> "Long" Then
            position = "Long"
            ws.Cells(i, 7).Value = "Buy"
            portfolioValue = portfolioValue - closePrice * tradeSize
        ElseIf signal = "Sell" And position <> "Short" Then
            position = "Short"
            ws.Cells(i, 7).Value = "Sell"
            portfolioValue = portfolioValue + closePrice * tradeSize
        Else
            ws.Cells(i, 7).Value = "Hold"
        End If       
        ' Update portfolio value and calculate PnL
        If position = "Long" Then
            pnl = (closePrice - ws.Cells(i - 1, 5).Value) * tradeSize
        ElseIf position = "Short" Then
            pnl = (ws.Cells(i - 1, 5).Value - closePrice) * tradeSize
        Else
            pnl = 0
        End If      
        totalPnL = totalPnL + pnl
        ws.Cells(i, 8).Value = position
        ws.Cells(i, 9).Value = portfolioValue
        ws.Cells(i, 10).Value = pnl
    Next i
    MsgBox "Backtest completed. Total PnL: " & totalPnL
End Sub

Explanation of the Code:

1. Initialization:
   • The code initializes variables like fastMA, slowMA, portfolioValue, etc.
   • The lastRow variable is used to find the last row of the data.
   • The strategy uses moving averages (fastMA and slowMA) for buy/sell signals.
2. Main Backtest Logic:
   • The code loops through each row starting from the point where we have enough data to calculate both the fast and slow moving averages.
   • It then calculates the moving averages for each row.
   • It generates a signal to « Buy », « Sell », or « Hold » based on the moving averages’ relationship.
   • The portfolio value is updated with each trade, and the profit or loss (PnL) is calculated.
3. Result Output:
   • For each row, the code stores:
     • The signal (« Buy », « Sell », « Hold »)
     • The trade position (« Long », « Short »)
     • The portfolio value after the trade
     • The PnL for the specific trade
   • After the loop, the total profit/loss is displayed in a message box.

Step 3: Running the Backtest

To run the backtest:

1. Make sure your historical data is set up correctly in the spreadsheet as mentioned in Step 1.
2. Press Alt + F8, select BacktestStrategy, and click « Run ».
3. The backtest will run, and the results (signals, positions, portfolio value, and PnL) will populate in the corresponding columns of the spreadsheet.

Example Output:

Conclusion

This VBA backtesting tool allows you to test a simple trading strategy based on moving averages. The code can be expanded with additional features like transaction costs, more complex strategies, or risk management rules.