Account balance - Google Spreadsheet SQL Query

Question

A        B        C       D       E       F    G
date     from     to      amount  account      
5/5/2013 bank     food    200     bank    
5/5/2013 work     bank    1200    food
5/5/2013 bank     rent    400     work
5/5/2013 work     bank    1200    rent

How can I get the resulting balance in column F?

And what's the most performant option?

I know how sum and rest values, but I don't know how to put them cleanly in F column.

What I tried

I made this:

in H1
=QUERY(A1:D99,"select B, sum(D) where D > 0 group by B")

in J1
=QUERY(A1:D99,"select C, sum(D) where D > 0 group by C")

Getting:

H       I             J       K
from    sum Amount    to      sum Amount
bank    600           bank    2400
work    2400          food    200
                      rent    400

Now, If I rest k - i, I could get the balance. But, how can I do this for correspondent elements? I mean bank with bank and so on.

Answer 1

If you have the accounts already listed in E2 down, you could enter this array formula in F2:

=ArrayFormula(IF(LEN(E2:E);SUMIF(C2:C;E2:E;D2:D)-SUMIF(B2:B;E2:E;D2:D);IFERROR(1/0)))

This would probably have the best performance of any alternative in this answer. However it relies on the account names already being populated.

This formula will return the entire table:

=ArrayFormula(QUERY(IF({1,0};TRANSPOSE(SPLIT(CONCATENATE(FILTER(B2:C;LEN(B2:B);LEN(C2:C))&CHAR(9));CHAR(9)));TRANSPOSE(SPLIT(CONCATENATE((FILTER(D2:D;LEN(B2:B);LEN(C2:C))*{-1,1})&CHAR(9));CHAR(9))));"select Col1, sum(Col2) group by Col1 label Col1 'Account', sum(Col2) 'Balance'";0))

But aside from being horribly unreadable, these type of "concatenate then split" formulae can have really poor performance for large data sets. So I would usually prefer to use a custom function in this situation:

function accountBalance(fromAccount, toAccount, amount) {
  var result = [], output = [['Account', 'Balance']], from, to, value;
  for (var i = 0; i < amount.length; i ++) {
    from = fromAccount[i][0];
    to = toAccount[i][0];
    value = amount[i][0];
    if (from && to) {
      if (!(from in result)) result[from] = 0;
      if (!(to in result)) result[to] = 0;
      result[from] -= value;
      result[to] += value;
    }
  }
  for (var j in result) {
    output.push([j, result[j]]);
  }
  return output;
}

And then in the spreadsheet cell, you would invoke:

=accountBalance(B2:B;C2:C;D2:D)