Get average stock price in Google Sheets from a list
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!
Solution
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)))
- Appscript Google sheets returns null
- Conditional formatting if a cell is a duplicate value
- Google Sheets Image() function now requires users to hit Allow Access
- Can I use Google Apps Script instead of Formulas for doing calculations on a spreadsheet?
- Is it possible to display an iframe or webview in google sheets
- In the same Google Sheet Workbook, can you extract the info from a cell in Sheet A to a cell in Sheet B, and link it back to the same cell in Sheet A?
- GSPREAD bulk export of dataframes on separate sheets without triggering API write request limits
- How do I configure Apps Script time-based Triggers to execute successfully?
- Write the count files in folder to a spreadsheet
- Adding message to email when using addEditor()
- Spreadsheet formulas calculation control
- Is there a way to keep two sheets synchronized?
- How to generate an uuid in google sheet?
- Google Sheets calculate percentage of the values in the rightmost column of total values in that column
- Struggling to set an option in a google Sheet's dropdown menu with JS
- How to perform calculations on numbers stored as text in google sheets or excel?
- Can't use getValues() with IMPORTRANGE
- How can turn this sumif into an arrayformula in Google Sheets?
- How can I retrieve the hyperlink from a data cell in google apps script?
- Email Google Sheets table and preserve link formatting
- Sheets countifs function returning 0 value
- Countif count Sunday to Friday correctly until it reach Saturday in google sheets
- How to get a bill to populate with the amount, only if the particular payday falls exactly on or 6 days before the due date?
- Google Sheets - Import Range issue and building a formula that both Import Ranges but based on corresponding Data Validation in another column
- How can I match a smart chip using SUMIF?
- Google Sheets - Bypassing the restriction of 50k characters for TEXTJOIN
- Customized FOrmel in google sheets to mark the current calendar week of the current year
- How to auto repeat ImportHTML with multiple URLS in Google Sheets
- How do I use IFS to conditionally apply a percentage?
- Import of a Large Quantity of API Data