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 -
*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?
