CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
YorgenFridman
YorgenFridman

Reputation: 31

How to get the average price I paid for the shares I own by trade history in Google sheets

I have a table of my trade history in the stock market and I want to get the average price I paid for the shares I still own of specific stock. Here's an example of the table:

Date Action Symbol Shares Price
23/06/2020 BUY GEM 1000 $100
23/06/2020 SELL GEM 1000 $5,000.00
23/06/2020 BUY GEM 1000 $50
23/06/2020 BUY GEM 1000 $40
23/06/2020 BUY GEM 1000 $30
23/06/2020 BUY AAPL 60 $140

The return of the formula should be 40 because I bought 1000 for 100 but I sold it so it shouldn't have impact on the average price. so basically it's (50 * 1000 + 40 * 1000 + 30 * 1000) / 3000 = 40

I tried using this solution Get average stock price in Google Sheets from a list by adjusting the formula to my situation like this:

=AVERAGE.WEIGHTED(FILTER(E2:E; B2:B="buy"; C2:C="GEM"); FILTER(D2:D; B2:B="buy"; C2:C="GEM"))

But the result is 55 which is wrong.

Upvotes: 1

Views: 776

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34370

Essentially you need to maintain a queue to implement this type of FIFO calculation, as I did in my answer to a previous question in Excel using VBA. This would be better done in GAS but for a modest number of shares you could try generating a row for each individual share and using a formula to maintain a sort of queue to which you either vstack more shares on the end when buying or use chooserow to remove them from the beginning when selling:

=average(reduce(,sequence(rows(tocol(A:A,1))-1),lambda(a,c,if(index(A2:A,c)="Buy",vstack(a,sequence(index(B2:B,c),1,index(C2:C,c),0)),chooserows(a,sequence(rows(a)-index(B2:B,c),1))))))

enter image description here

Then suppose you were to sell only 5 of the original shares:

enter image description here

which is the same as

=average.weighted({100,50,40,30},{5,10,10,10})

EDIT

Found an error (I should have been retaining the last n shares, not the first n, where n is the number of shares remaining after a sale) this is the updated version:

=average(reduce(,sequence(count(B:B)),
lambda(a,c,if(index(A2:A,c)="Buy",vstack(a,sequence(index(B2:B,c),1,index(C2:C,c),0))
,if(index(B2:B,c)=count(a),"",chooserows(a,1,sequence(count(a)-index(B2:B,c),1,index(B2:B,c)+2)))))))

Upvotes: 0

Related Questions