totalling with a variable on what row to use

[hedge15]

I cant find a template that does what I need done so here is my question. Thank you for your time. I'm setting up a spread sheet for trades that has gotten pretty complicated. I buy in multiples of 3 contracts and exit in lots of either lots of 2-1 or all 3.so the columns look like this -

A B C D E F G H I J
Date Time long/short Size price exit/stop size price points net/points


Problem comes when I exit. I have the 2 statements written to give the net per trade but when you paste them down it messes up the columns. If I exit selling 2/3rds then 1/3rd, the second 1/3 will be on the next row and the profit for that part of the trade would have to be figured using the entry from the line above. I need a statement that somehow lets the other statements know if the exit should be calculated from the entry on that line or the line above.

Maybe somehow it would count the number of contracts exited then reset, like use statement 1 if total is 2 or less, if total equals 3 reset. Or calculating versus the row its in unless that row has no time vale in column b then go to the column 1 above. I am not sure. Any ideas would be helpful if you cant get the solution.

Here are the 2 statements I wrote.

1st statement is for line 1 - calculates net for that portion of the trade using the entry on the same line as the exit.

=IF(B133>0,(IF((C133="L"),((H133-E133)*G133),IF((C133="SS"),((E133-H133)*G133)))),(IF((C133="L"),((H134-E133)*G134),IF((C133="SS"),((E133-H134)*G134)))))

Statement 2 calculates net for this portion of the trade using the entry from the line above because it is the last 1/3 of the trade.

=(IF((C149="L"),((H150-E149)*G150),IF((C149="SS"),((E149-H150)*G150))))

Thanks a lot.

[mdmackillop]

Hi Hedge,
Welcome to VBAX
You can post a sample workbook using Manage Attachments in the Go Advanced section, which should make your problem clearer.
Regards
MD

[hedge15]

Thanks MD,

Here is a sample section. The problem occurs in the statement in column I. Column I totals that portion of each trade by the exit.

Row 3 and 4 is a trade with a 2 part exit. I pasted in i3 the entire statement and it works fine. In cell i4 I posted just the 2nd part of that statement by itself from cell I3 that calculates the profit from the line above. It works also when posted seperatly.

Row 5 has a 1 line trade where the entire exit is in the same row as the entry and the statement in column I5 works fine.

The trade in row 6 and 7 is where the problem is shown. The I6 cell works fine since it is the 1st line of the exit, but when I paste the formula into cell I7 it gives a false because it changes the cells - instead of I7 subtracting from the entry on the row above, (I7 from row 6 entry data) it shifts to rows 7 and 8. the same for the next trade on rows 8 and 9 where the error is in cell I9.

Thanks.

[Shazam]

This formula might help.


Input formula in cell J2 and copied down.


=(LOOKUP(10^307,E$3:E3)-H3)*G3

[hedge15]

Shazam,

Thank you very much. It does find the entry correctly but it doesn't seperate the statements for shorts "SS" or Longs "L". In the origonal statement I posted I used an IF statment (IF((C3="L"), or (IF((C3="SS"), then reversed which cell was subtracted from which, H-E or E-H.

I tried to incorporate your formula with an IF statement and 2 options but I keep getting an error message.

Thank you for your help and I do really appreciate your time. I was surprised I was able to get so far with teh IF staement on my 1st time using Excel like this.

[Shazam]

Will this work?


=IF(LOOKUP(REPT("z",255),C$3:C3)="SS",(LOOKUP(10^307,E$3:E3)-H3)*G3,IF(LOOKUP(REPT("z",255),C$3:C3)="L",(H3-LOOKUP(10^307,E$3:E3)*G3)))

[hedge15]

The results should be the same as you see in th I column. cell I6 and I8 had values of -1281 and -2559. They should have been -7.50 and 9 respectively. The statement just has to look in the c column. If it sees a "SS" then its H-E. If the c column has "L" its E-H using my initial formula. Thanks

[Shazam]

How about...


=IF(SUMPRODUCT(--(C$3:C3="SS"))>0,(LOOKUP(10^307,E$3:E3)-H3)*G3,(H3-LOOKUP(10^307,E$3:E3)*G3))

[hedge15]

The answers in row 6 and 8 are incorrect.

I think the c$3 keeps the statement always checking c3. It must check for the last time L or SS in the c column, not always check c3. Its either in the current line or the line 1 above, no more or less.

The other thing is I dont see it saying if C is "L", only for SS.

If C column for that trade is long the formula is (H3-E3)*G3). If the C colum for the trade is "SS" the formula is (E3-H3)*G3) - assuming the exit is on the same line as the entry.

If its the 2nd exit in the trade and the entry for this exit is in the line above, the formulas are as follows - c3 is "L" then (H4-E3)*G3). if C3 is "SS" then the formula is (E3-H4)*G3)

Once again thanks.

[Shazam]

Can you provide a sample workbook with the desire results? It does not have to be a formula just input the results manually.

[hedge15]

I did in my 2nd post but it is my pleasure to attach a 2nd one. In this copy Column I has the correct results I am looking for. Each cell has the formula I wrote that works, but I need 2 versions so I have to paste it in 1 at a time. I made it so you have 1 long and 1 short for each of 1 line and 2 line trades.

Thanks a lot and if you need anything else please ask.

[Shazam]

Hope I got this right.



Input formula in cell I3 and copied down.


=LOOKUP(10^307,CHOOSE({1,2,3,4},0,IF(C3="SS",""),(LOOKUP(10^307,E$3:E3)-H3)*G3,IF(C3="L",(H3-LOOKUP(10^307,E$3:E3))*G3)))

[hedge15]

That works great exceot for 1 thing that isn't really major but I do have a question as to why it acts like this.

If I change the L or SS on a 2 line trade only the 1st exits p/l changes, not the second. For example, on the spread sheet I sent you, row 9 cell c9 says SS and gives values of 6.00 in I9 and 78 in I10. If you make C9 L, the values should change to -6.00 in I9 and -78 in I10. But only the 6 changes, I10 stays as +78. Any idea why?

[Shazam]

Test this out and let me know.


=IF(C3="SS",(LOOKUP(10^307,E$3:E3)-H3)*G3,IF(C3="L",(H3-LOOKUP(10^307,E$3:E3))*G3,(IF((C2="L"),((H3-E2)*G3),IF((C2="SS"),((E2-H3)*G3))))))


Hopefully that works.

[hedge15]

Nailed it. Thanks. I never heard of a few of the commands you used like LOOKUP, CHOOSE or REPT and I never saw (10^307, e$3:E3) before. I have some research to do.

I need to figure out how you did that so I can get an automatically updating record for shorts and longs seperatly. I got the automatically updating record for all trades together but that was easy (If total column >0,1,0) then next column was just a total of the 1s.

I do appreciate the time and effort you put in. I would never have figured that out. Thank you.

[Shazam]

Your Welcome. :friends: