correct any misuse of escrowed funds

[chadwhitacre]

was move money from Zeta bank account into Balanced

We've got $2,203.89 escrowed in Balanced with a merchant balance of -$167.22. That's $2,036.67.

We owe people $5,449.41 (SELECT sum(balance) FROM participants), so I need to add $3,412.74 to my Balanced escrow.

How do I do that?

/cc @mtamizi, @jkwade, @mahmoudimus, @mjallday

[chadwhitacre]

We have plenty in Balanced to cover today's anticipated withdrawals (based on connected bank accounts). Bumping this a week.

[mahmoudimus]

Hi @whit537, do you still need help with this issue?

[chadwhitacre]

Alright, now we're at:

Liabilities
 $8,641.46  SELECT sum(balance) FROM participants;
 $  372.87  "Merchant balance" in Balanced
 ---------
 $9,014.33

Assets
 $5,805.09  "In escrow" in Balanced
 $3,406.01  Balance in New Alliance FCU
 ---------
 $9,211.10

P/L
 $  196.77

The Balanced "Merchant balance" is the sum of the fees that we owe them. They haven't quite started collecting yet, still tightening that up (release early! release often! :D).

I need to understand the New Alliance line item better. It looks from this like there is $200 to put towards hosting! Yay! :D

Is that right?

[chadwhitacre]

And to date we have paid $180 for hosting ($20/mo for June and July, $70 for August and September). We should recalculate going forward what it will take to cover $70/mo, but it looks like so far we've almost exactly covered our hosting costs with our fee structure, which is nice.

[chadwhitacre]

Here's how Balanced works:

1. Gittip charges patrons.
2. Gittip deposits to receivers.
3. Gittip deposits to Zeta's account at New Alliance FCU.
4. Balanced debits from Zeta's account at New Alliance FCU.

The amount debited in (4) is calculated from (1) at 2.9% + $0.30 per transaction, and (2) at $0.25 per transaction. (3) is free.

Right now we are doing (1) and (2) but not (3), which is okay because Balanced is not doing (4) quite yet. :-)

We need to start doing (3). At the end of each payday we need to transfer dollars to New Alliance FCU. Here's how to compute the number of dollars to transfer:

SELECT sum(fee) FROM exchanges WHERE timestamp >= %s

We also need to a) look at New Alliance and put Chad's money into it to cover old fees that were coming out until recently, and b) transfer money that Zeta is holding in escrow for Gittip participants from New Alliance to Balanced.

[chadwhitacre]

Balanced has started charging. Huzzah! :D

There's a great start to an invoice listing on the dashboard. We should go over all the invoices and make sure they're right, and then make that part of our weekly ritual.

This also means it's time to automate the transfer of funds from Balanced to Zeta each week.

[chadwhitacre]

Now we're at:

52,096.27 in escrow

and

48,037.87 in Balanced
 3,771.90 in New Alliance
----------------
51,809.77

Which means we're only short by 286.50. Phew. :-)

We have 1,200+ coming to us on Friday (cf. https://github.com/gittip/www.gittip.com/issues/68#issuecomment-25114189), so if I'm reading this right we should easily be able to correct the error then, and we'll have enough left in Ally to switch paying our vendors over to that account (#1506), which means that we'll no longer be co-mingling funds and misuse of escrow will be behind us.

[chadwhitacre]

  52,827.97  total escrow
- 49,597.72  currently in Balanced
-  3,530.06  currently in New Alliance
-----------
     299.81
-    286.50  what we owed before yesterday's payday
-----------
      13.31

Um, what?

[chadwhitacre]

Yeah, I had that wrong. Scratchpad:

[chadwhitacre]

We owe Balanced $232.90. Looks like that hasn't hit New Alliance yet but should ... soon? With a post date of Sept 26, I expect?

Now, something else: we've got 18 invoices (since launch) for failed credits (possibly representing less than 18 failed credits, if there is some retry algorithm in play?). We're not charged for these, but how do failed credits interact with our escrow numbers? I'm guessing that we decrement the amount in Gittip's participants.balance (which is where escrow on the stats page comes from) even when a credit fails. This would mean that we're low-balling escrow on the stats page.

[chadwhitacre]

(Smells like #213.)

[chadwhitacre]

This is squirrelly. These numbers are off just enough to be annoying.

[chadwhitacre]

Still slightly off.

121,627.17 New Alliance
 28,007.13 Balanced
  7,143.91 PayPal
----------
156,778.21

=> select sum(balance) from participants;
┌───────────┐
│    sum    │
├───────────┤
│ 156227.26 │
└───────────┘
(1 row)

[MikeFair]

Have you accounted for interest that would accrue in the bank accounts and/or deleted/deactivated accounts that once had a balance? On Jun 15, 2015 8:01 AM, "Chad Whitacre" notifications@github.com wrote:

Still slightly off.

121,627.17 New Alliance 28,007.13 Balanced

7,143.91 PayPal

156,778.21

=> select sum(balance) from participants; ┌───────────┐ │ sum │ ├───────────┤ │ 156227.26 │ └───────────┘ (1 row)

— Reply to this email directly or view it on GitHub https://github.com/gratipay/gratipay.com/issues/265#issuecomment-112098634 .

[chadwhitacre]

@MikeFair Good call! No, I didn't factor in interest, but I know we've accrued some.

[chadwhitacre]

We've got a solid financial accounting system coming together in https://github.com/gratipay/inside.gratipay.com/pull/390 and https://github.com/gratipay/finances/pull/1. As we get caught up reconciling past months under this new accounting system, any escrow discrepancy should get squeezed out.

[chadwhitacre]

Closing to keep this repo clean now that we have https://github.com/gratipay/finances. This should get cleaned up in course and audited under https://github.com/gratipay/finances/issues/3.