Google Sheets Query

[DireEmblem]

• Morning all.

I have a google sheet with lots of SUMIF formula across the page in cells, and I getting fed up having to expand the range of these so trying to look at converting to using the QUERY function instead.

I have one sheet with trade data. Columns are

• Trade date/time stamp
• Trade direction
• Security id
• Units

The data in the trade date/time stamp column is not always sequential, basically depends when I trade.

the second tab i am trying to convert to a query needs to have the security ID along the top row, and each day down the left side without time stamp. So for this week, even if so only traded on the 18/20th January 2021, there would be a data row for the 19th as well.

then for the data in the table, this is a sum of all the Units bought less sold up to the end of that date.

Any ideas how I do this?

=TRANSPOSE(QUERY(Trades!A:B,”Select C, SUM(D) where B = ‘Buy’ group by A”))

So that formula if I have typed it in right would put all the Security ID’s along the top row, all the trade dates with time stamp down the left, and only fill in the data fields on the days I have a buy. It wouldn’t sum up the buys prior to that day.

 

[DireEmblem]

https://docs.google.com/spreadsheets/d/1nNT3XR-jBkp3MqR6mpNl9nrZ4M1Nt6bYZTaAqhsf1lI/edit?usp=sharing

Thats my google sheets - its tabs 5/6 I would like to convert into an array, like tab 7 so it auto expands when needed and doesn't perform calculations on nil data.

A query sort of works, I can get the total units or cost bought/sold on a day, but cant seem to get a running total up to date, so I've done Sumifs all the way across the sheet which I don't think is very efficient.

Actually, I might just be having a brain fart. If my query pulls back the required data for the day, then I just need to have a + balance from prior day - duh!