Buys and sells on one sheet, calculate average price

[krypto]

Hello,
I am trying to build a "stock purchase transaction log" in Excel where I use a new sheet for every stock to log the buys and sells, e.g.:



This can either be a "long" trade (i.e. first trade in line 40 is a "buy"), or a short trade where the first trade would be a "sell".
-> the formulas in a short trade are probably inverse or *-1 to the ones in a buy trade.

There can be several transactions of type "buy" /"sell" and I would like to keep track of the average price.
If the "roundtrip" is over (the amount of shares is 0), the trade is over and I would use a new sheet for the next trade.

I haven't completely figured out the formulas:
- I probably need nested ifs to treat long/short trades differently, this is not implemented yet.
- The sell in line 42 does not yet affect the average price in H42.

And am a bit confused now. Maybe someone could give me a hint re how I could fix the "average price" in H42...?

Here are my formulas so far:


Total Costs:
=IF($B$40="Buy", IF(B41="Buy",G40+F41,H41*I41), IF(B41="Sell",G40+F41,H41*I41*-1))

Avg Price:
=IF($B$40 = "Buy", IF(B41="Buy",G41/I41,H40), IF(B41="Sell",-1*G41/I41, H40))

Hold:
=IF($B$40="Buy", IF(B41="Buy",I40+D41,I40-D41), IF(B41="Sell",I40-D41,I40+D41))

 

[pecoflyer]

Hello
pictures are not very helpful. Please post a sample sheet. Thx

 

[krypto]

Hi pecoflyer,
Thanks for getting back to me - sorry I didn't post a sample right away!
I guess I would have to use a nested if to take of the long/short (first transaction is a buy or sell) situation, hmmm.