Google Sheets Query

DireEmblem · 20 Jan 2021 at 08:42

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.

gpuerrilla · 23 Jan 2021 at 13:32

Do it first in the query and get that working. I have found once wrapping in other functions like iferror you can waste a lot of time 'fixing' it when its not broken just the wrapping within another function is not happy with a random reason.

DireEmblem · 23 Jan 2021 at 14:27

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!

gpuerrilla · 23 Jan 2021 at 14:33

I found the filter function better to mess with for results but I might get round to taking a look this afternoon if the kids dont go on a mad one.