0

I am new to excel VBAs, please bear with me.

I have created a function to :

  1. take (price as currency, start time as a string, stop loss as currency) as input values.
  2. take the price of a particular symbol and calculate 15 min high, low, open, close prices.
  3. return an entry strategy based on the calculated values based on the fluctuation in entry prices.
  4. return an exit strategy based on entry strategy and trail stop loss(calculated wrt entry and stop-loss mentioned.

Update: Thanks for the help. I found that the function was resetting the min-max values on each call. How do i overcome this? I tried using do-while loops but it overloaded the process.

Here is the code I used. Please suggest any improvements

Option Explicit
Function St(P As Currency, start As String, SL As Currency, Mclose As String) As String
On Error GoTo eh
Dim EntExt As String, op_15 As Currency, cl_15 As Currency, Tmax As Currency, Tmin As Currency, TSL As Currency
Dim Response As Variant

Application.Volatile

        If TimeValue(Now) <= TimeValue(start) Then
            min = P
            max = P
            op_15 = P
            Response = MsgBox("max=" & max & "-----min=" & min, vbOKCancel)
            If Response = vbCancel Then
                Exit Function
            End If
    
        ElseIf TimeValue(Now) <= TimeValue(start) + TimeValue("00:02:00") Then
            cl_15 = P
            If P > max Then
                max = P
            ElseIf P < min Then
                min = P
            End If
            Tmax = max
            Tmin = min
       
    
        ElseIf TimeValue(Now) <= TimeValue(start) + TimeValue("00:04:00") Then
            If P > Tmax Then
                Tmax = P
                Response = MsgBox("Tmax=" & Tmax, vbOKCancel)
                If Response = vbCancel Then
                    Exit Function
                End If
            ElseIf P < Tmin Then
                Tmin = P
                Response = MsgBox("Tmin=" & Tmin, vbOKCancel)
                If Response = vbCancel Then
                    Exit Function
                End If

            End If
            EntExt = EntF(P, start, max, min)

                    
        ElseIf TimeValue(Now) > TimeValue(start) + TimeValue("00:04:00") And TimeValue(Now) < TimeValue(Mclose) Then
            If EntExt = "LE" Then
                'If long exit then exits after price drops below stop loss
                    TSL = Tmax - SL
            ElseIf EntExt = "SE" Then
                'If short exit then exits after price rises above stop loss
                    TSL = Tmin + SL
            Else:
                ' if price does not go above or below intial high low, then no entry is done
                    TSL = 0
            End If
        
            EntExt = ExtF(P, Tmax, Tmin, EntExt, TSL)
           
            If EntExt = "LX" Or EntExt = "SX" Then
                Exit Function
            End If
        
        End If
    
    St = EntExt
    
    St = St(P, start, SL, Mclose)
    
    If TimeValue(Now) >= TimeValue(Mclose) Then
            Exit Function
        
Exit Function
eh:
On Error GoTo 0
   'MsgBox "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description _
            , , "Error", Err.HelpFile, Err.HelpContext
End Function

Function EntF(P As Currency, start As String, max As Currency, min As Currency) As String
On Error GoTo eh
Dim Response As Variant
    If TimeValue(Now) <= TimeValue(start) + TimeValue("00:04:00") Then
   'devising an entry strategy for the next 15 min
        If P > max Then
            EntF = "LE"
            Response = MsgBox("price = " & P & "  max = " & max)
            If Response = vbCancel Then
                Exit Function
            End If
        ElseIf P < min Then
            EntF = "SE"
            Response = MsgBox("price = " & P & "  min = " & min)
            If Response = vbCancel Then
                Exit Function
            End If
        Else:
            EntF = "dont"
        End If
        
    End If
    
Exit Function
eh:
On Error GoTo 0
   'MsgBox "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description _
            , , "Error", Err.HelpFile, Err.HelpContext
   End Function
Function ExtF(P As Currency, Tmax As Currency, Tmin As Currency, Entry As String, TSL As Currency) As String
On Error GoTo eh
    'MsgBox "EXIT"
    'devising an exit strategy for the given entry
    '
        If Entry = "LE" Then
            'If long exit then exits after price drops below stop loss

                If P < TSL Then
                    ExtF = "SX"
                Else:
                ' else exit at the end of market, hence profit
                    ExtF = "Profit"
                End If
        ElseIf Entry = "SE" Then
            'If short exit then exits after price rises above stop loss

                If P > TSL Then
                    ExtF = "LX"
                Else:
                'else exit at the end of market adn gains profit
                'provided quantity of stocks sold at entry = quantity of stocks bought by the end of market
                    ExtF = "Profit"
                End If
        Else:
            ' if price does not go above or below intial high low, then no entry is done

            ExtF = "NO entry done"
        End If
Exit Function
eh:
On Error GoTo 0
   'MsgBox "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description _
            , , "Error", Err.HelpFile, Err.HelpContext
   
End Function
Akarsh
  • 1
  • 1
  • did you actually debug the code - step through line-by-line and compare it's behavoir for different scenarios. that will likely solve your problem. – Scott Holtzman Aug 21 '20 at 15:44
  • Your second and third `ElseIf` statements are identical. Is that by design? if so, your third `ElseIf` will never get trriggerd. Also, you are setting error handling at the start of your UDF (`On Error GoTo eh`). It is good practice to always reset this at the end or your UDF (`On Error Goto 0`) – Zac Aug 21 '20 at 15:52
  • Show usage of `EntExt` . For sure wrong `tm` parameter. – ComputerVersteher Aug 21 '20 at 17:01
  • As Scott advised, step through your code to see what it does http://www.cpearson.com/Excel/DebuggingVBA.aspx – ComputerVersteher Aug 21 '20 at 18:10

0 Answers0