Account balance - Google Spreadsheet SQL Query -

a        b        c       d       e       f    g date              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 resulting balance in column f?

and what's performant option?

i know how sum , rest values, don't know how put them cleanly in f column.

what tried

i made this:

in h1 =query(a1:d99,"select b, sum(d) d > 0 group b")  in j1 =query(a1:d99,"select c, sum(d) d > 0 group c") 

getting:

h                   j       k    sum amount         sum amount bank    600           bank    2400 work    2400          food    200                       rent    400 

now, if rest k - i, balance. but, how can correspondent elements? mean bank bank , on.

if have accounts listed in e2 down, enter 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 have best performance of alternative in answer. relies on account names being populated.

this formula return 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 col1 label col1 'account', sum(col2) 'balance'";0))

but aside being horribly unreadable, these type of "concatenate split" formulae can have poor performance large data sets. prefer use custom function in situation:

function accountbalance(fromaccount, toaccount, amount) {   var result = [], output = [['account', 'balance']], from, to, value;   (var = 0; < amount.length; ++) {     = fromaccount[i][0];     = 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;     }   }   (var j in result) {     output.push([j, result[j]]);   }   return output; } 

and in spreadsheet cell, invoke:

=accountbalance(b2:b;c2:c;d2:d)