I've created a spreadsheet to track my trades using data exported from my brokerage. my spreadsheet has the following simplified format:
| Date | Time | Symbol | Quantity | Amount | Starting Balance | Ending Balance | Trade No. |
|---|---|---|---|---|---|---|---|
| 07/1/2022 | 07:00 | ABCD | 1 | -$50 | $100 | $50 | 1 |
| 07/15/2022 | 11:00 | EFGH | 1 | -$25 | $50 | $25 | 2 |
| 07/15/2022 | 13:00 | EFGH | -1 | $50 | $25 | $75 | 2 |
| 07/15/2022 | 11:00 | ABCD | -1 | $75 | $75 | $150 | 1 |
| 08/1/2022 | 06:00 | EFGH | 1 | -$25 | $150 | $125 | 3 |
| 08/1/2022 | 11:00 | EFGH | -1 | $50 | $125 | $175 | 3 |
What I'm trying to do is get the starting and ending balance for each month/year. This might not be the correct way to do it but I've resorted to using the QUERY method since my pivot table didn't work. I have an initial query that gets the MIN and MAX ROW for each month/year combination:
=QUERY({ARRAYFORMULA(CONCAT(ARRAYFORMULA(YEAR(Daily_Trades!A5:A))&"-", ARRAYFORMULA(MONTH(Daily_Trades!A5:A)))), ARRAYFORMULA(ROW(Daily_Trades!A5:A)), Daily_Trades!Q5:Q}, "SELECT Col1, MIN(Col2), MAX(Col2) WHERE Col1 <> '1899-12' GROUP BY Col1 LABEL Col1 'Year-Month', MIN(Col2) 'Min Row Index', MAX(Col2) 'Max Row Index'" )
Now I'm trying to take the results from the query above which look like this:
| Year-Month | Min Row Index | Max Row Index |
|---|---|---|
| 2022-7 | 1 | 4 |
| 2022-8 | 4 | 5 |
My desired output is the table below with the Starting and Ending Balances for the month/year based on the row indexes. However, since I can't use INDEX in an ARRAYFORMULA and I can't use VLOOKUP because that's based on a cell value I'm not sure how to do this. I was hoping for nested or joined queries but not sure if that's possible either.
| Year-Month | Starting Balance | Ending Balance |
|---|---|---|
| 2022-7 | 100 | 150 |
| 2022-8 | 150 | 175 |
CodePudding user response：
try:
=QUERY({SORTN({TEXT(A2:A, "e-m"), F2:F}, 9^9, 2, 1, 1),
INDEX(SORTN(SORT({TEXT(A2:A, "e-m"), G2:G}, 1, 1, 2, 0), 9^9, 2, 1, 1),,2)},
"where Col3 is not null", )
update:
=QUERY({"Year-Month", "Starting Balance", "Ending Balance";
SORTN({TEXT(A2:A, "e-m"), F2:F}, 9^9, 2, 1, 1),
INDEX(SORTN(SORT({TEXT(A2:A, "e-m"), F2:F}, ROW(F2:F), 0), 9^9, 2, 1, 1),,2)},
"where Col3 is not null", 1)