Code: Select all
Function CalcProfit()
'This Function Will Calculate The Profit
Dim db As Database
Dim Rs As 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, Fld8 As Field, Fld9 As Field
Dim Fld10 As Field, Fld11 As Field, Fld12 As Field, Fld13 As Field
Dim I As Long, intcurQty As Integer, intnextQty As Integer, intbackRec As Integer
Dim dblNextAmt As Double, dblCurAmt As Double
Dim strCurSymbol As String, strNextSymbol As String, strCurCon As String, strNextCon As String
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select * From Trades Order By Index")
Set Fld1 = Rs!ID
Set Fld2 = Rs!TradeDay
Set Fld3 = Rs!Symbol
Set Fld4 = Rs!Contract
Set Fld5 = Rs!Quantity
Set Fld6 = Rs!ActionID
Set Fld7 = Rs!ActionName
Set Fld8 = Rs!Price
Set Fld9 = Rs!Commission
Set Fld10 = Rs!Fees
Set Fld11 = Rs!Amount
Set Fld12 = Rs!Profit
Set Fld13 = Rs!OrderNum
Rs.MoveFirst
Rs.MoveLast
Rs.MoveFirst
Rs.MoveLast
For I = Rs.RecordCount To 2 Step -1
Rs.Edit
If Fld6 = 47 Or fld = 49 Then
'Closing trade find prev opening trades
strCurSymbol = Fld3
strCurCon = Fld4
intcurQty = Fld5
dblCurAmt = Fld11
If Fld3 = strCurSymbol And Fld4 = strCurCon Then
'Found prev opening symbol and contract
If Fld5 + intcurQty = 0 Then
'closing quantity equals prev closing quan
Rs.MoveLast ' move to the closing trade
dblNextAmt = Fld11
Else
intbackRec = Fld5 + intcurQty ' how many quan left
End If
End If
'Exit For
Rs.Update
Next I
Rs.Update
Set Rs = Nothing
Set db = Nothing
End Function