January 21, 2025
Headlines

Excel VBA Code For False Breakout Strategy

The.Data.Scientist04 mins 
Sub FalseBreakoutStrategy()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim window As Integer
    window = 14

    ' Create columns for rolling high and low
    ws.Cells(1, 8).Value = "High_Rolling"
    ws.Cells(1, 9).Value = "Low_Rolling"

    Dim i As Long
    For i = window + 1 To lastRow
        ws.Cells(i, 8).Value = WorksheetFunction.Max(ws.Range(ws.Cells(i - window, 3), ws.Cells(i - 1, 3)))
        ws.Cells(i, 9).Value = WorksheetFunction.Min(ws.Range(ws.Cells(i - window, 4), ws.Cells(i - 1, 4)))
    Next i

    ' Create columns for breakout detection
    ws.Cells(1, 10).Value = "Breakout_High"
    ws.Cells(1, 11).Value = "Breakout_Low"

    For i = window + 2 To lastRow
        If ws.Cells(i, 3).Value > ws.Cells(i - 1, 8).Value Then
            ws.Cells(i, 10).Value = 1
        Else
            ws.Cells(i, 10).Value = 0
        End If
        If ws.Cells(i, 4).Value < ws.Cells(i - 1, 9).Value Then
            ws.Cells(i, 11).Value = 1
        Else
            ws.Cells(i, 11).Value = 0
        End If
    Next i

    ' Create columns for false breakout detection
    ws.Cells(1, 12).Value = "False_Breakout_High"
    ws.Cells(1, 13).Value = "False_Breakout_Low"
    Dim reversalPeriod As Integer
    reversalPeriod = 3

    For i = window + 2 + reversalPeriod To lastRow
        If ws.Cells(i, 10).Value = 1 And _
           ws.Cells(i, 6).Value < ws.Cells(i - 1, 8).Value And _
           ws.Cells(i, 6).Value > WorksheetFunction.Min(ws.Range(ws.Cells(i - reversalPeriod, 6), ws.Cells(i - 1, 6))) Then
            ws.Cells(i, 12).Value = 1
        Else
            ws.Cells(i, 12).Value = 0
        End If
        
        If ws.Cells(i, 11).Value = 1 And _
           ws.Cells(i, 6).Value > ws.Cells(i - 1, 9).Value And _
           ws.Cells(i, 6).Value < WorksheetFunction.Max(ws.Range(ws.Cells(i - reversalPeriod, 6), ws.Cells(i - 1, 6))) Then
            ws.Cells(i, 13).Value = 1
        Else
            ws.Cells(i, 13).Value = 0
        End If
    Next i

    MsgBox "False Breakout Strategy Calculation Complete"
End Sub

Explanation:

1. Rolling High/Low Calculation:

    Columns H (`High_Rolling`) and I (`Low_Rolling`) store the rolling high and low values over a 14-day window.

2. Breakout Detection:

    Columns J (`Breakout_High`) and K (`Breakout_Low`) store 1 if there is a breakout above the rolling high or below the rolling low, respectively.

3. False Breakout Detection:

    – Columns L (`False_Breakout_High`) and M (`False_Breakout_Low`) store 1 if a false breakout is detected based on the reversal conditions.

5. Run the VBA Script: Close the VBA editor, go back to Excel, and run the script by pressing `Alt + F8`, selecting `FalseBreakoutStrategy`, and clicking `Run`.

This VBA script will mark false breakouts in your data. Adjust the column references and sheet names as needed to match your specific setup.

Related News