Google Sheets Query

Associate
Joined
25 Aug 2008
Posts
947
  • 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.
 
Soldato
Joined
21 Jul 2005
Posts
16,713
Location
N.Ireland
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.
 
Associate
OP
Joined
25 Aug 2008
Posts
947
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!
 
Last edited:
Soldato
Joined
21 Jul 2005
Posts
16,713
Location
N.Ireland
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.
 
Top Bottom