I am new to excel VBAs, please bear with me.
I have created a function to :
- take (price as currency, start time as a string, stop loss as currency) as input values.
- take the price of a particular symbol and calculate 15 min high, low, open, close prices.
- return an entry strategy based on the calculated values based on the fluctuation in entry prices.
- 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