Excel profit/loss summary of items from another detailed log table

[sagooo]

Guys,

I have a trading log something like this in excel workbook -


[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Stock/ETF[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Buy/Sell/Deposit/Withdrawn/RH[/TD]
[TD="align: center"]Adjustment[/TD]
[TD="align: center"]Fees[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]9/1/2016[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Deposit[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]$100[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]9/29/2016[/TD]
[TD="align: center"]MSTX[/TD]
[TD="align: center"]$0.1006[/TD]
[TD="align: center"]900[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$90.54[/TD]
[TD="align: center"]$9.46[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]9/30/2016[/TD]
[TD="align: center"]MSTX[/TD]
[TD="align: center"]$0.1016[/TD]
[TD="align: center"]900[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.11[/TD]
[TD="align: center"]$91.44[/TD]
[TD="align: center"]$100.79[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]9/30/2016[/TD]
[TD="align: center"]BVX[/TD]
[TD="align: center"]$4.9[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$98.00[/TD]
[TD="align: center"]$2.79[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]10/3/2016[/TD]
[TD="align: center"]AMRS[/TD]
[TD="align: center"]$0.5921[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$2.37[/TD]
[TD="align: center"]$0.42[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]10/3/2016[/TD]
[TD="align: center"]BVX[/TD]
[TD="align: center"]$5.37[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]$53.70[/TD]
[TD="align: center"]$54.12[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]10/3/2016[/TD]
[TD="align: center"]NTNX[/TD]
[TD="align: center"]$40.15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$40.15[/TD]
[TD="align: center"]$13.97[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]10/3/2016[/TD]
[TD="align: center"]AMRS[/TD]
[TD="align: center"]$0.5973[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$12.54[/TD]
[TD="align: center"]$1.43[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]10/3/2016[/TD]
[TD="align: center"]AMRS[/TD]
[TD="align: center"]$0.5859[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$1.17[/TD]
[TD="align: center"]$0.26[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]10/4/2016[/TD]
[TD="align: center"]NTNX[/TD]
[TD="align: center"]$40.26[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]$40.26[/TD]
[TD="align: center"]$40.51[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]10/4/2016[/TD]
[TD="align: center"]AMRS[/TD]
[TD="align: center"]$0.5828[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]$15.74[/TD]
[TD="align: center"]$56.25[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]10/4/2016[/TD]
[TD="align: center"]AUPH[/TD]
[TD="align: center"]$3.48[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: center"]-$0.08[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$55.6[/TD]
[TD="align: center"]$0.65[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]10/6/2016[/TD]
[TD="align: center"]AUPH[/TD]
[TD="align: center"]$5.15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]$82.4[/TD]
[TD="align: center"]$83.04[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]10/6/2016[/TD]
[TD="align: center"]BVX[/TD]
[TD="align: center"]$5.25[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: center"][/TD]
[TD="align: center"]$0.00[/TD]
[TD="align: center"]$52.5[/TD]
[TD="align: center"]$135.54[/TD]
[/TR]
</tbody>[/TABLE]



Here are formulas -

H3 `=IF(((C3*D3)+F3)<>0,((C3*D3)+F3),"")`


I3 `=IF(E3="Buy",I2-H3,IF(E3="Sell",I2+H3-G3,IF(E3="Deposit",I2+H3,IF(E3="Withdrawn",I2-H3,IF(E3="Robin Hood",I2-G3,"")))))`


G4 `=IF(E4="Sell",(D4*C4*0.0000218)+(D4*0.000119),"")`

*Date, Adjustment, Stock/ETF, Price, Qty, Buy/Sell/Deposit/Withdrawn/RH, Adjustment* columns are all entered manually!


I am trying to achieve something like this in excel, where each row refers to the "Sells" in primary table and calculates profit/loss depending on how many shares sold that day and the price at which they were sold. It should look up the table to see if i have any outstanding shares bought in my account and still not sold and then calculate profit loss depending upon the avg. sell price and comparing it with avg. bought price -


[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Stock/ETF[/TD]
[TD]Profit/Loss[/TD]
[/TR]
[TR]
[TD]9/30/2016[/TD]
[TD]MSTX[/TD]
[TD]$0.90[/TD]
[/TR]
[TR]
[TD]10/3/2016[/TD]
[TD]BVX[/TD]
[TD]$4.70[/TD]
[/TR]
[TR]
[TD]10/4/2016[/TD]
[TD]NTNX[/TD]
[TD]$0.11[/TD]
[/TR]
[TR]
[TD]10/4/2016[/TD]
[TD]AMRS[/TD]
[TD]-$0.34[/TD]
[/TR]
[TR]
[TD]10/6/2016[/TD]
[TD]AUPH[/TD]
[TD]$26.80[/TD]
[/TR]
[TR]
[TD]10/6/2016[/TD]
[TD]BVX[/TD]
[TD]$3.50[/TD]
[/TR]
</tbody>[/TABLE]



So if you observe above table, MSTX, NTNX and AUPH were straight buy and sells. But BVX I bought 20 and sold half for $4.70 profit and second half for $3.50 profit. With AMRS, I did multiple buys but only one sell, so i averaged the buy price for each share and then figured the profit.


How do I get this working in excel?