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)))
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation
- Google Sheets: Number Format option of horizontal axis labels in a histogram
- Textjoin with header values
- Specifying a time span / duration constant in Google Sheets formula over 24h
- If spreadsheet ID is not equal to and number of rows less than?
- Using a gs function with an input from a selection in html on appscript
- Generate unique random numbers in Google Sheets