Create a table based on a ledger

Question

Give a ledger-like set of data with 3 columns:

Name,Type,Amount
Foo,A,-100
Bar,A,-200
Baz,A,-500
Foo,B1,200
Foo,A,-100
Bar,A,-300
Baz,B2,2000
Bar,A,-300

If I want to make a table that has these headers:

Name,X,Y,Z

where Name is a distinct list of Name in the first table,

where X is sum of the values from the first table where Names match, Type is A, and Value is negative

where Y is the sum of values from the first table where Names match, Type starts with B, and Value is positive

where Z is Y / X

How might I do this? Right now I have this implemented as 2 different queries for X and Y, but they repeat the Name column, and it's not combine-able into Z because those two queries don't return the same set of Names since some Names don't have any values that match the query for both X and Y.

Here's a concrete example in a sheet I made: https://docs.google.com/spreadsheets/d/1qbwLqRncgo8pQNU4oAeYfEHr5o4L8EGB-Skhu869fGE/edit?usp=sharing

Answer 1

This might work for you.

I added a tab to your sheet, Sheet1-GK. In it, I first calculate the amounts that are either distributed, or invested, for each row of your data - see formulas in cells D1 and E1. Then I use the following query, in G8, to produce the result that you were asking for.

=query(A1:E12,"select A, sum(D), sum(E), sum(E)/sum(D) group by A label sum(D) ' Invested', sum(E) 'Distributed', sum(E)/sum(D) 'MoM'",1)

The only issue is having the MoM value return as negative. This could be handled with another column, if necessary, to force the absolute value. I'm not sure if there is any way to incorporate that in the query statement.

Let me know if this is useful, or if you need something different.