Google Sheets Using Results from one query to return results in another (Nest or Join)

1
votes

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
1
arraysdatesortinggoogle-sheetsgoogle-query-language
what is your desired output? - player0
@player0 the last table in the question....Sorry I'll update that so it's more clear - user1015196

1 Answers

2
votes

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

enter image description here

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)