Get average stock price in Google Sheets from a list

Question

I'm trying to get average stock prices from a dynamic list.

In my example, I've made a bought stocks in a few different days. And I've sold some of them (not all). So, I have some stocks available that I want to find out the average price:

How to get an average price from stocks not sold, from the earlier to the latest date without changing manually the list?

Manually, the result must be: $19,82

I've built an example sheet.

Thanks! Any help will be appreciated!

Subspecies

Answer 1

use:

=AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); FILTER(D3:D6; B3:B6="buy"))

---

update 1:

=INDEX(AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); FILTER(D3:D6; B3:B6="buy")-
 {SEQUENCE(COUNTIF(B3:B6; "buy")-1; 1; 0; 0); FILTER(D3:D6; B3:B6="sell")}))

---

update 2:

=ARRAYFORMULA(AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); IF(0<FILTER(D3:D6; B3:B6="buy")+
 IF(0>MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 
 MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 0); FILTER(D3:D6; B3:B6="buy")+
 IF(0>MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 
 MMULT(TRANSPOSE((SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))>=
 TRANSPOSE(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy")))))*
 QUERY({FILTER(D3:D6; B3:B6="buy");-SUM(FILTER(D3:D6; B3:B6="sell"))}; "offset 1"; 0)); 
 SIGN(SEQUENCE(COUNTA(FILTER(D3:D6; B3:B6="buy"))))); 0); 0)))

Junk

Thanks, but the goal is to get that result without manually changing the 12 to 2. Do you know if this is possible? Automatically recover 10 units from the last one purchased. – Subspecies

Thanks! This is great. How it would be if it were a number minor than 10 in the 12 cell? For example an 8 and you have to take that 8 and 2 more frome the above row. – Subspecies

@AntorCha wrap the last FILTER into SUM – Junk

Like that it doesn't give a value. I've updated the google sheet. =INDEX(AVERAGE.WEIGHTED(FILTER(E3:E6; B3:B6="buy"); FILTER(D3:D6; B3:B6="buy")- {SEQUENCE(COUNTIF(B3:B6; "buy")-1; 1; 0; 0); sum(FILTER(D3:D6; B3:B6="sell"))})) – Subspecies