Thanks in Advance!
I have an excel file that reflects day trading in the futures market. I use it for various metrics (average profit per trade, wins/losses, etc.). There can be any number of contracts in a given single trade. For example, at one time I may be holding 1,2,3, etc. long positions (positive numbers in column E) or short positions (negative numbers in column E). There can be a LARGE numbers of possibilities within a given trade. In the attached file rows 2 - 13 are the first trade, rows 14,15 are the second trade, and rows 16-18 are the third trade). The range of each trade starts with zero and ends with zero.
I wrote IF-THEN statements based on the trades I have actually done, but I am up to 56 IF-THEN statements and I understand the max is 64. Plus while not difficult, it is very time consuming to write new IF-THEN statements for the cases that are new on a new trading day.
Excel Version: Microsoft 365 Subscription (so latest version)
EQUATION IN CELL F13 (see attached file)
=
IF(AND(N13=0,N12=-1,N11=-2,N10=-1,N9=-2,N8=-1,N7=-2,N6=-3,N5=-4,N4=-5,N3=-4,N2=-2,N1=0),SUM(A2:A13),
IF(AND(N15=0,N14=-2,N13=0),SUM(A14:A15),
IF(AND(N18=0,N17=1,N16=2,N15=0),SUM(A16:A18),
0)))
EQUATION IN CELL G13 (see attached file)
=
IF(AND(N13=0,N12=-1,N11=-2,N10=-1,N9=-2,N8=-1,N7=-2,N6=-3,N5=-4,N4=-5,N3=-4,N2=-2,N1=0),SUM(A2:C13),
IF(AND(N15=0,N14=-2,N13=0),SUM(A14:C15),
IF(AND(N18=0,N17=1,N16=2,N15=0),SUM(A16:C18),
0)))
Notes:
- The only difference between the equation in cell F13 versus the equation in cell G13 is the "sum" range at the end of each line.
- The same If/Then equation is in every cell (but of course the reference numbers of the lines changes. That is, the equation in cell F15 looks just like the equation in cell F13, but the row numbers will be different.
- You can assume that row 1 is the starting zero.
- The summing function is one row UNDER the first zero, and down to the same row as the second zero for each trade.
- For Cell F13: What the IF-THEN statements do is to finds the 0's in column E (rows 1 and 13) and then sums rows 2 - 13 in column A, and puts the total in cell F13. This is the profit or loss on a trade (not including commissions) on a single trade.
- For Cell G13: The very same thing occurs again, except the sum range is A2:C13 (profit/loss including commissions) on a single trade.
- The zero at the very bottom of the IF-THEN statement does not do anything, other than let me know that I need to update the IF-THEN statement with a new case. That is, the logic should never get to the zero if I have all cases covered for that trading day.
- GOAL: Rather than the IF-THEN statements, is it possible to equations or Visual Basic to look for the zero's and then sum accordingly?
Spreadsheet w/ Notes
