How can I use Excel to calculate the avarage price of some BUY\SELL operation including the sell op-CodePudding

I am not so into Excel and I have the following problem.

I have a table like this:

Basiscally it is an example of sheet to keep track of buyed and selled cryptocoin. It does some calculation. The column title are in italian so following some details:

B column: it is the operation: BUY or SELL.
D column: the amount of money in EUR (€) related this buy or sell operation.
E column: the price of a single coin in EUR.
F column: the number of coin buyed\selled in this operation.

Then Into the I4 cell I have put the average purchase price calculated by this formula:

=SUM(FILTER(D1:D150;B1:B150="BUY"))/SUM(FILTER(F1:F150;B1:B150="BUY"))

Basically it calculate the avarange purchase price in a simplified way (and not totally correct): it calculate the sum of the total purchase price in EUR (only the BUY operation) dividing it for the sum of the purchased coins (also here only the buyed operation).

I think that this formula is not totally correct because for example the line 4 represents an operation where all my coins was tottally sold, so after the next buy operation (line 5) the new average purchase price (in EUR) should be the eur price of this operation (that after a complete sale can be considered the only operation) for the number of coin related this operation. Basically in this specific case (but this is not a general case) the D5 / F5 that infact have the value of the E5 cell.

Basically I think that the previous formula (defined into I4) should take in consideration also how much does the average purchase price weigh after each sale operation (this because after that a specific number of coins were sold --> the purchase price untill now is the same but when I have another buy operation it must be considered that are less coins).

So basically I was thinking to use this G column containing a value representing the amount of coins updated after each sale, so basically I can have 2 possibility:

There are no previous sell operation (not previous row related a SELL operation in the B column) --> the value will be the sum of the current F value and of the F value of all the previous rows.
There are at least one row before the current one representing a SELL operation --> the value will be the summ of all the F values relatd to all the BUY operation - the sum of all the F values related to all the SELL operation.

I am not sure that this is the best way to procede. Basically what I need in the G cells is the quantity of coin untill this operation (including this operation).

How can I implement this behavior? Or what could be a nice and elegang operation to solve my original problem related my original formula?

CodePudding user response：

I don't have enough reputation to comment, but I don't think it's quite clear what you're trying to accomplish... if each line item has a quantity and a cost, why would you want the sum of all previous quantities? The cost per item should only be related to those items that were transacted in this line item?

I think, based on your formula in Column I, you are attempting to do an AVERAGEIF function. This would look like this:

I2=AVERAGEIF(B:B,B2,E:E)

This would make every row where B = "Buy" match each other, and every row where B = "Sell" match each other, which I think I understood you don't want. If you're wanting to weight the results based on the quantity sold, you can do that with a SUMPRODUCT:

I2=SUMPRODUCT(--(B:B=B2),E:E,F:F)/F2

Lastly, if I misunderstood this altogether, it sounds like you'd like column G to be the cumulative sum of quantity owned to date where BUYs are added and SELLs are subtracted. You could do that like this:

G2=SUMPRODUCT(--($B$2:$B2="Buy"),$F$2:$F2)-SUMPRODUCT(--($B$2:$B2="Sell"),$F$2:$F2)

CodePudding user response：

This is actually quite a complicated problem…

If you would like to show the running balance of coins, you could do it this way:

=SUMIFS(F:F,B:B,"BUY",C:C,"SHIBA",A:A,"<="&A2:A5)
-SUMIFS(F:F,B:B,"SELL",C:C,"SHIBA",A:A,"<="&A2:A5)

This will calculate your total coin position based on the sum of all purchases prior to today's date, minus the sum of all sales prior to today's date.

However, it's not so straight forward to calculate the average purchase price. When a sale is made, are you looking to deduct it from the oldest purchase first?

Sorry that it's such a messy formula, but here's the formula I came up with:

=LET(
     Dates, A2:A5,
     DatesRanked, RANK.EQ(Dates,Dates,1),
     CumulativePurchases, SUMIFS(F:F,B:B,"BUY",C:C,"SHIBA",A:A,"<="&Dates),
     CumulativeSales, TRANSPOSE(SUMIFS(F:F,B:B,"SELL",C:C,"SHIBA",A:A,"<="&Dates)),
     RowCount, ROWS(Dates),
     Matrix_NetOfSales, CumulativePurchases-CumulativeSales,
     Matrix_PositiveOnly, IF(Matrix_NetOfSales<0,0,Matrix_NetOfSales),
     Matrix_AmountsStillHeldFromEachPurchase, Matrix_PositiveOnly-IF(DatesRanked=1,0,INDEX(Matrix_PositiveOnly,XMATCH(DatesRanked-1,DatesRanked,0),SEQUENCE(,RowCount))),
     Matrix_EliminateByDate, Matrix_AmountsStillHeldFromEachPurchase*(Dates<=TRANSPOSE(Dates)),
     TRANSPOSE(MMULT(TRANSPOSE(E2:E5),Matrix_EliminateByDate))
)

Here, I'm using the LET function to break down the above into manageable parts. Here is what each of the above does:

Variable	How it works
Dates	Input date range from the spreadsheet
DatesRanked	In case your data isn't sorted, it ranks the dates
CumulativePurchases	This is a dynamic range that adds up all purchases that occurred before this purchase
CumulativeSales	Same thing, but for sales
RowCount	As the name suggests, the number of rows
Matrix_NetOfSales	This subtracts the sales from the purchases, as a matrix. This is important, because different dates draw upon different transactions
Matrix_PositiveOnly	Since cumulative sales may result in an entire transaction being eliminated from the calculation, we exclude any transaction that has a negative value still applied
Matrix_AmountStillHeldFromEachPurchase	This 'undoes' the cumulative operation, leaving just the portion of each transaction that still applies
Matrix_EliminateByDate	This ensures that we only apply transactions that occur on or before the date of the current transaction
Result	Finally, we use a matrix product between the cost per share per transaction, with the matrix of units from each transaction, to get the total value spent

Phew… That was a lot of work. To get the average price per coin, simply divide the total amount spent by the total number of coins (i.e. the first equation in this answer), and it should be done!

In finance (in common law countries), this solution is similar to Clayton's Rule: Which is that the newest credit(debit) is offset against the oldest debit(credit) when calculating interest… not a coincidence.