Creating transaction list from google sheet data

by Quirkafleeg   Last Updated July 17, 2017 05:03 AM

I'm hoping to create an automated list of required transactions based on numerical values in a google sheet. The existing data is in the form:

A1: Name   B1: Bal $       C1: Share %

A3: Bob    B3: 5795.35     C3: 11.4
A4: Fred   B4: 2236.82     C4: 17.29
A5: Lisa   B5: 982.12      C5: 40.36
A6: Ian    B6: 12532.00    C6: 9.85
An: Dave   Bn: 1097.50     Cn: 21.1

Where Bal is the current funds held by each person and Share is the ideal balance as a percentage of the total of those funds. I'd like to create an optimal list of required payments (rounded to the nearest $10) made between these people to bring everybody's balance close to the specified percentage of funds.

I have a satisfactory algorithm, it's just the implementation that's beyond me (I have no prior experience of google sheets and have inherited the sheet as is). Using the above data as an example, I would sort the people by (rounded) excess funds held, ie

Ian +10300
Bob +3210
Fred -1680
Dave -3680
Lisa -8160

Then keep repeating first pays last the lesser of the first's excess or the last's shortfall, and removing the person who's new excess is 0 from the list, until only one person remains on the list. So producing the following list of transactions:

E1: From  F1: To    G1: Amount $

E3: Ian   F3: Lisa  G3: 8160
E4: Bob   F4: Dave  G4: 3210
E5: Ian   F5: Fred  G5: 1680
E6: Ian   F6: Dave  G6: 460

The original balances don't need updating, it's simply the advised transaction list that's required. And the whole functionality is only required on demand, say on the click of a button, rather than in real time as balances are changed. Ideally I'm hoping that a clever combination of existing google spreadsheet functions would solve this, though I'm expecting it'll need a custom function. If all else fails, the easiest method for me to get to grips with would probably be a RPC to my server, though that still leaves me figuring out how to build the outgoing query from a spreadsheet and handle the return data. I really am a beginner with these things and would appreciate any solutions or even pointers.

Related Questions

How to get the count of the current sheet?

Updated April 03, 2015 21:18 PM

ArrayFormula changes the result

Updated August 16, 2015 17:01 PM