CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaaveragestock
Guzz
Guzz

Reputation: 5

Google Sheets - Finding the average cost of current holding in stocks

I've been at this for i don't know how long, and can't seem to find a formula that can automatically calculate the average cost for my current holding in a stock. I currently have a setup where i insert the data, like seen in the first screenshot below, and then press the arrow for it to be moved by some JavaScript, as a new row, to another sheet, seen in the second screenshot. As i insert it, i have two main actions. Buy and Sell. (The screenshots are just exampels)

Screenshot 1

Screenshot 2

I need one, (Or more if needed), formula that can calculate the average price cost for the shares i have left, and for it to update itself as i insert more rows with either buying or selling.

Hope this makes sense. Otherwise feel free to ask if i am missing any information.

I have already tried some formulas and asking some AI, but none of it has seemed to work.

Upvotes: -1

Views: 283

Answers (2)

Babanana
Babanana

Reputation: 1476

Getting Average Price based on Amount Paid and Action Buy/Sell

I would like to start this answer by setting your expectation that this is what I have understood for your question. You can comment in case that you need adjustment or I misunderstood your expected results.

Try this:

={{"Average Price",(MINUS(sumif(C2:C,"Buy",E2:E),(sumif(C2:C,"SELL",E2:E))))/(MINUS(sumif(C2:C,"BUY",D2:D),(sumif(C2:C,"SELL",D2:D))))};{"Current Stock",MINUS(sumif(C2:C,"BUY",D2:D),(sumif(C2:C,"SELL",D2:D)))}}

Note: Please paste this formula on the part where it is not going to be affected by your dataset. I tried to simplify the data as possible for my example to be readable

Sample Result:

Formula Result

Reference:

Sumif

Minus

Upvotes: 1

Guzz
Guzz

Reputation: 5

I think i found a solution, that seems to work:

=((SUMIFS(F:F, B:B, "NKE", C:C, "Buy"))/(SUMIFS(D:D, B:B, "NKE", C:C, "Buy")))*(SUMIFS(D:D, B:B, "NKE", C:C, "Buy") - SUMIFS(D:D, B:B, "NKE", C:C, "Sell"))

I might have made it harder than it really was, but this seems to get the correct average price for all the shares i have of a stock like NKE. If you want the individual average price per share, you have to simply remove the end of the formula, so it only says:

=((SUMIFS(F:F, B:B, "NKE", C:C, "Buy"))/(SUMIFS(D:D, B:B, "NKE", C:C, "Buy")))

Upvotes: -1

Related Questions