Code: Select all

```
Sub CalcTUProfit(Profit)
'This Function Will Calculate The Profit for TU Only
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, J As Long, CountTicks As Long, intCurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim intCurWhle As Integer, intPrevWhle As Integer, intWhleMult As Integer
Dim dblPrevAmt As Double, dblCurAmt As Double, dblPrevFrc As Double, dblCurFrc As Double, dblBuyP As Double, dblSellP As Double
Dim dblCurCom As Double, dblCurFee As Double, dblPrevCom As Double, dblPrevFee As Double
Dim strCurSymbol As String, strCurCon As String, strCurRawP As String, strPrevRawP As String
Dim varStart As Variant, varEnd As Variant
Dim p As Double, p1 As Double
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By ID")
Set Fld1 = Rs!ID
Set Fld2 = Rs!Tradedate
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!ContractMonth
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!RawP
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Rs.MoveFirst
Rs.MoveLast
strCurSymbol = Fld3
strCurCon = Fld4
intCurQty = Fld5
strCurRawP = Fld7 'Closing Price of TU
dblCurCom = -Abs(Fld5 * 1.5)
dblCurFee = -Abs(Fld5 * 0.67)
p = InStr(strCurRawP, "'")
intCurWhle = Left(strCurRawP, p - 1)
dblCurFrc = Mid(strCurRawP, p + 1)
If Fld6 = 46 Or Fld6 = 47 Then
dblBuyP = intCurWhle + dblCurFrc / 32
Else
dblSellP = intCurWhle + dblCurFrc / 32
End If 'If Fld6 = 46 Or Fld6 = 47 Then
For I = Rs.RecordCount To 2 Step -1
If I < Rs.RecordCount Then
If Fld6 = 46 Or Fld6 = 48 Then
'Find prev opening trades
If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
'Found prev opening symbol and contract
dblPrevCom = -Abs(Fld5 * 1.5)
dblPrevFee = -Abs(Fld5 * 0.67)
varEnd = dblCurFrc
strPrevRawP = Fld7
p = InStr(strPrevRawP, "'")
intPrevWhle = Left(strPrevRawP, p - 1)
dblPrevFrc = Mid(strPrevRawP, p + 1)
If Fld6 = 46 Or Fld6 = 47 Then
dblBuyP = intPrevWhle + dblPrevFrc / 32
Else
dblSellP = intPrevWhle + dblPrevFrc / 32
End If 'If Fld6 = 46 Or Fld6 = 47 Then
varStart = dblPrevFrc 'Example 102+02.1
'Count the number of ticks between Start and End
'For each whole number there are 256 ticks
varEnd = Abs(intPrevWhle - intCurWhle) * 256 + dblCurFrc
If varStart <= varEnd Then
For J = 10 * varStart + 1 To 10 * varEnd
If J Mod 10 <> 4 And J Mod 10 <> 9 Then
CountTicks = CountTicks + 1
End If 'If J Mod 10 <> 4 And J Mod 10 <> 9 Then
Next J
Else
For J = 10 * varStart - 1 To 10 * varEnd Step -1
If J Mod 10 <> 4 And J Mod 10 <> 9 Then
CountTicks = CountTicks + 1
End If
Next J
End If 'If varStart <= varEnd Then
Rs.MoveLast ' move to the closing trade
Rs.Edit
If dblSellP > dblBuyP Then
Fld12 = Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5 * 1.5) - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
Else
Fld12 = -Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5) * 1.5 - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
End If 'If dblSellP > dblBuyP Then
Rs.Update
Exit For
End 'If Fld3 = strCurSymbol And Fld4 = strCurCon And IsNull(Fld12) Then 'If multiple openings, then place a zero in profit of closed opening trade
End 'Fld6 = 46 Or Fld6 = 48 Then
End If 'If I < Rs.RecordCount Then
Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Sub
```