Guys,
I have a trading log something like this in excel workbook -
<tbody>
</tbody>
Here are formulas -
*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 -
<tbody>
</tbody>
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?
I have a trading log something like this in excel workbook -
A | B | C | D | E | F | G | H | I | |
1 | Date | Stock/ETF | Price | Qty | Buy/Sell/Deposit/Withdrawn/RH | Adjustment | Fees | Total | Balance |
2 | 9/1/2016 | Deposit | $100 | $100 | |||||
3 | 9/29/2016 | MSTX | $0.1006 | 900 | Buy | $90.54 | $9.46 | ||
4 | 9/30/2016 | MSTX | $0.1016 | 900 | Sell | $0.11 | $91.44 | $100.79 | |
5 | 9/30/2016 | BVX | $4.9 | 20 | Buy | $98.00 | $2.79 | ||
6 | 10/3/2016 | AMRS | $0.5921 | 4 | Buy | $2.37 | $0.42 | ||
7 | 10/3/2016 | BVX | $5.37 | 10 | Sell | $0.00 | $53.70 | $54.12 | |
8 | 10/3/2016 | NTNX | $40.15 | 1 | Buy | $40.15 | $13.97 | ||
9 | 10/3/2016 | AMRS | $0.5973 | 21 | Buy | $12.54 | $1.43 | ||
10 | 10/3/2016 | AMRS | $0.5859 | 2 | Buy | $1.17 | $0.26 | ||
11 | 10/4/2016 | NTNX | $40.26 | 1 | Sell | $0.00 | $40.26 | $40.51 | |
12 | 10/4/2016 | AMRS | $0.5828 | 27 | Sell | $0.00 | $15.74 | $56.25 | |
13 | 10/4/2016 | AUPH | $3.48 | 16 | Buy | -$0.08 | $55.6 | $0.65 | |
14 | 10/6/2016 | AUPH | $5.15 | 16 | Sell | $0.00 | $82.4 | $83.04 | |
15 | 10/6/2016 | BVX | $5.25 | 10 | Sell | $0.00 | $52.5 | $135.54 |
<tbody>
</tbody>
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 -
Date | Stock/ETF | Profit/Loss |
9/30/2016 | MSTX | $0.90 |
10/3/2016 | BVX | $4.70 |
10/4/2016 | NTNX | $0.11 |
10/4/2016 | AMRS | -$0.34 |
10/6/2016 | AUPH | $26.80 |
10/6/2016 | BVX | $3.50 |
<tbody>
</tbody>
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?