Get average stock price in Google Sheets from a list

user_15140997 0
Viewed 839

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:

enter image description here

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!

weighted-average stock google-sheets
1 Answers

use:

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

enter image description here

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")}))

enter image description here

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)))

enter image description here

user_56326290
Related