Logic to solve a problem

[bknight]

I have a Db that contains futures trading. I had a scheme to calculate profits but that has failed recently with more complex trading. I'm looking for a method to calculate profit. I've enclosed a PARTIAL record of the Db nothing more is needed to calculate. h method is needed. The code doing most of the heavy lifting. The spreadsheet contains a number, instrument, whether the trade was a buy or sell, quantity traded, whether the trade was an entry or exit, and most crucial what the trade produced. The calculation is performed on the record that contains exit and -. Now from glancing at the spreadsheet the entries/exits seem trivial but more combinations exist but not shown.
In the code that cons the calculation, which is in error and needs recoded, I have a GetRows function should help in this task. So here is my new scheme the quantity traded between the first entry and the last exit must equal zero. One way to calculate is to identify the record that contains both exit and -cycle back through the records to find the record that sums all the quantities to zero.

But I don't know how to perform the procedure summing quantity and another field NOT SHOWN named amount for profit.

[HansV]

Did you intend to attach something?

[bknight]

Sorry I thought I did.
While looking at that another small question.
I currently use a sub to number the transactions. I have a variable in that sub can this variable be used in another sub?

[HansV]

Can you provide an example of the desired output?

[bknight]

Only conceptually the sum of the quantities between the first entry and the last exit in each of the trades will be equal to zero and the respective amounts (not shown but in the Db as another field) will be summed over those same records. In the spreadsheet one can see that the first 4 records meet those criteria. Using that same criteria records 5 and six meet the criteria. I highlighted all the last exits for each Block to be considered. This demonstrates a full entry and exit of the records shown. Now I need a procedure to identify the records in code and then sum the amounts not shown and enter that sum into another field (that I know ow to code. The numbers in the first field are assigned by another sub no changes to that code piece, just the calculation part. In addition I asked whether a variable in this numbering sub could be used in another code the one I'm asking about I think the term was dynamic, but it has been too many years since my classes.

[HansV]

I'll have to leave this to someone smarter than me.

[bknight]

This is the scheme that will and does work for the given subset. As time goes by I'll determine if there are any issues, but for now it works. This was developed in Excel, and I'll be required to modify into the Db.

Code: Select all

Sub CalcPft()
Dim I As Long, lngQuan As Long, lngStartNum As Long, lngQuanOpen As Long
Dim strCont As String, strAction As String, EntEx As String, strPos As String
Dim dblAmt As Double, dblPft As Double, dblCurAmt As Double
For I = 2 To 154
If Range("E" & I) = "Entry" Then
lngQuanOpen = Range("D" & I).Value + lngQuanOpen
dblCurAmt = Range("G" & I).Value + dblCurAmt
ElseIf Range("E" & I).Value = "Exit" And Range("F" & I).Value <> "-" Then
lngQuanOpen = Range("D" & I).Value + lngQuanOpen
dblCurAmt = Range("G" & I).Value + dblCurAmt
ElseIf Range("E" & I).Value = "Exit" And Range("F" & I).Value = "-" Then
lngQuanOpen = Range("D" & I).Value + lngQuanOpen
dblCurAmt = Range("G" & I).Value + dblCurAmt
Range("H" & I).Value = dblCurAmt
dblCurAmt = 0
lngQuanOpen = 0
End If
Next I
End Sub