Hello all!
I've been trying to set up my own crypto portfolio google sheet and it's been going well so far. I would like some advice on the formulas to calculate the following:
Average price
Realised profit
Unrealised Profit/Loss
My first sheet contains all of my transactions, which are only spot trading transactions columns are:
||
||
|Coin Name|Transaction Type|Date|Quantity|Price per Unit (€)|Total Value (€)|
Ticker - Transaction Type(Buy/sell) - Purchase date - Qty - Price - Total price
The second sheet contains
||
||
|Coin name|Current price|Cumulative investment|Cumulative revenue|Total Qty bought|Total Qty Sold|Average cost|Realized PnL|Unrealized PnL|Pnl %|
Basically, the second sheet looks for all the transactions for each of the coins, and calculates the values in columns 3-10.
The current price is taken via the CoinMarketCap API which works great.
The cumulative revenue & investment as well as the total qty bought/sold are easy as it's just the sum of the amount/qty depending on the transaction type.
Now the challenge is calculating the rest, as for several coins I have bought at multiple instances, and sold some at other instances.
So I'd like to be able to find out for each coin how much I got in realised profit, and in unrealised PnL for whatever is left.
Any help on the formulas?
Thanks!