gratipay / finances Goto Github PK

Reticketed from gratipay/inside.gratipay.com#350.

Retained earnings (or accumulated deficit, for a loss) is for past years' activity. We should be posting individual income and expense transactions against Equity:Current Activity, which gets settled to retained earnings at the close of the year.

Most important follow-on from #28 ... what domain should we use? :-)

We don't want to use a subdomain of gratipay.com, because that exposes us to security issues related to cookies. These are available:

@nobodxbodon @JessaWitzel Picking up from gratipay/inside.gratipay.com#867 (comment), gratipay/inside.gratipay.com#878 (comment), and gratipay/inside.gratipay.com#871 (comment) ...

When @nobodxbodon and I met last week, we proposed writing a Django application to help us visualize and manage our budget and financials. Our thought was to start by porting over our budget, which is currently in a Google Sheet.

I propose that we add the Django app to this repo, and deploy it to Digital Ocean. What I envision is that we can use Ledger CLI under the hood to run the reports, and we can use Django to read and eventually write the data that Ledger processes. Waddya think? :-)

If you're open to the basic idea, then let's discuss requirements and priorities ...

As noted at #22 (comment) we only have good formats for the past two months, before that we only have PDFs. 😞

@kaguillera and I contacted PayPal a week or two ago to ask for an export of our old account data. I just called back and it turns out they dropped the ball! They escalated me and I now have an export from the web interface (I had been getting an error previously) that goes back to 2014-04-27, and the L2 rep filed a request for an export from there back through 2012-05-01. That should give us what we need to reconcile PayPal. Supposedly we should see a mail from them inside of 24 hours with a link for an encrypted download of the old data. 👍

I canceled Xero since we went with Ledger (#1). I read somewhere on their community forum that their cancelation process is not the smoothest, and their latest bill makes me nervous, because at the bottom it says:

Unless advice to the contrary is received from you by 04 Feb 2016, the amount due will be debited from your credit card on or after 07 Feb 2016.

I've replied:

We canceled our Xero account recently. Please confirm that we won't be charged again. Thanks!

Here are Ledger's budget docs:

http://www.ledger-cli.org/3.0/doc/ledger3.html#Budgeting

Looks like we should be able to make a file that includes weekly, monthly, quarterly, and annual expenses.

Reticketed from gratipay/inside.gratipay.com#350.

cf. #8

The fee buffer shouldn't be on the income statement, because then we would need to "bury it in retained earnings" at the close of the year. We want to keep the fee buffer active in the balance sheet from year to year. It's basically an "allowance liability account." We'll see the fee buffer net on the balance sheet, and if we need change over time we'll run a separate report.

What we want is the master branch and the current month PR, though at this stage we have so many PRs open it's a mess. One possibility would be to compile this to static pages (beancount might even give us facilities for that), in which case Netlify could be an option (cf. gratipay/grtp.co#171), or some other simple SFTP or Nginx hosting situation (same droplet as grtp.co?).

This is a meta-ticket for all the months we need to catch up on, going back to the beginning.

• 2012-06—#1
• 2012-07—#2
• 2012-08—#6
• 2012-09
• 2012-10
• 2012-11
• 2012-12
• 2013-01
• 2013-02
• 2013-03
• 2013-04
• 2013-05
• FY2013

• 2013-06
• 2013-07
• 2013-08
• 2013-09
• 2013-10
• 2013-11
• 2013-12
• 2014-01
• 2014-02
• 2014-03
• 2014-04
• 2014-05
• FY2014

• 2014-06
• 2014-07
• 2014-08
• 2014-09
• 2014-10
• 2014-11
• 2014-12
• 2015-01
• 2015-02
• 2015-03
• 2015-04
• 2015-05
• FY2015

• 2015-06
• 2015-07
• 2015-08
• 2015-09
• 2015-10
• 2015-11
• 2015-12
• 2016-01—#14
• 2016-02
• 2016-03
• 2016-04
• 2016-05
• FY2016

• 2016-06
• 2016-07
• 2016-08
• 2016-09
• 2016-10
• 2016-11—#30
• 2016-12—#31
• 2017-01—#44
• 2017-02
• 2017-03
• 2017-04
• 2017-05
• FY2017

From @tlevine at https://github.com/gratipay/logs/issues/12 (reticketing here so it's public):

I request records from the following banks/processors if they are available.

• Braintree
• Balanced
• Coinbase
• Dwolla
• Paypal (We only have 2015-9-28 right now.)

If paper/PDF statements are available, I want those regardless of whether the records are available in other forms. Other forms are nice too, but I want the paper for checking stuff.

If the processor doesn't provide documents intended for archival but does provide records on a webpage, you can save the webpage in MHTML format.

In https://github.com/gratipay/finances#readme it's June to May, in http://inside.gratipay.com/big-picture/operating-agreement it's January to December.

I assume the latter is correct now.

Afaict we only have PDFs and we should at least have that data in CSV.

https://www.odoo.com/page/accounting

(h/t)

cf. #22

So we are doing this in order to be able to quickly and easily visually check if the information that we are producing in the Financial reports reconcile with the entries in the Gratipay database. Please note that this is a work in progess. I am not submitting a PR as yet because I am getting some trouble with the SQL. It has been a while.
To the point of this post since I digress...I have come up with the following SQL query and would like verification as to if it is correct as well as give some starting point for some one who would like to try. Please note that it has been a while since I have done (years) SQL and it is not my first language. But again I digress.

SELECT  t1.month,
        balanced_payins,
        balanced_payouts,
        balanced_income,
        paypal_payins,
        paypal_payouts,
        paypal_income,
        balanced_payins + paypal_payins AS Payins,
        balanced_payouts + paypal_payouts AS Payouts,
        balanced_income + paypal_income AS Income
FROM (  SELECT date_trunc('month', "timestamp") AS month
            , sum(case when amount > 0 then amount end) as balanced_payins
            , -(sum(case when amount < 0 then amount end)) as balanced_payouts
            , sum(fee) as balanced_income
            , network
       FROM exchanges, exchange_routes
       WHERE exchanges.route = exchange_routes.id
       AND exchange_routes.network = 'balanced-cc'
       GROUP BY month, exchange_routes.network
       ORDER BY month ASC
     ) AS t1,
    (  SELECT date_trunc('month', "timestamp") AS month
            , sum(case when amount > 0 then amount end) as paypal_payins
            , -(sum(case when amount < 0 then amount end)) as paypal_payouts
            , sum(fee) as paypal_income
            , network
       FROM exchanges, exchange_routes
       WHERE exchanges.route = exchange_routes.id
       AND exchange_routes.network = 'paypal'
       GROUP BY month, exchange_routes.network
       ORDER BY month ASC
    ) AS t2
WHERE t1.month = t2.month;

The query above should produce a table of Payins, Payouts, and income broken down by months and network (i.e. routes/payment vendors). It work...up to a point. It does what it is supposed to but the routes are hard coded and I personally don't like this because it makes the Dashboard Reconciliation Report inflexible. If you have any advice as to how to make it more dynamic please share. I will continue to try and work on it

Reticketing from gratipay/inside.gratipay.com#448 (comment).

@tlevine proposes GnuCash ... if we can get OFX or QIF files as input.

We need professional help setting up our books and answering questions about how to structure certain transactions (like Mass Pay). If we continue trying to do this ourselves we will take lots of time and do it wrong.

IRL me and @whit537 are trying to figure out the account for masspay
Here are some notes I made that @whit537 suggested I post from my evernote

Note:

• The new structure of the assets accounts are such that
  • ::<Category/Logic>
  • e.g. Assets:New-Alliance:Escrow
• All expenses should fall under one of these accounts
  • Expenses:Other:Fees - Fees for transferring money in and out of the system.
  • Expenses:Other:Hosting - Fees for hosting the system e.g. Travis, Heruko, etc
  • Expenses:Other:Misc - Fees that don’t fall under any other category.
  • Expenses:Advertisings - e.g. Business Cards, internet, radio, and TV ads
  • Expenses:Equipment - Equipment Rental e.g. Van or Truck rental or office equipment
  • Expenses:Services - Legal or Professional e.g. Fees paid to an attorney or other professional
  • Expenses:Transport - Local Transportation e.g. Parking, tolls, or taxis
  • Expenses:Meals - Meals and Entertainment e.g. Meals while traveling, lunch with client.
  • Expenses:Travel - Overnight Travel Expense e.g. Transportation costs, internet, phone or fax charges, and tips
  • Expenses:Supplies - Supplies e.g. printer ink and printer paper

We should store reports in the repo, maybe on a dedicated branch with an automated creation process from Travis? I'm thinking top-level reports: current balance sheet, month-to-date income statement, year-to-date income statement.

Reticketed from gratipay/inside.gratipay.com#350.

The income statement should only show operational accounts. It should not show escrow. Peter suggested going half-way towards a non-profit income statement, which has three columns: operations, temporarily restricted funds, and permanently restricted funds. We don't have permanently restricted funds, escrow would count for temporarily restricted. Basically we want a separate income statement for escrow.

Stems from discussion around #6 (comment), and actually we are already doing that manually when we validate the beancount files.

Basically, the goal is to keep balance consistent with every balance in every bank account.

For example, pick 1/31/2017, the sum of all the assets of PNC according to beancount should be equal to the balance on that day in the bank statement. The same should apply to every day, and other bank accounts as well.

To achieve that, we'll add sanitized bank statements in the repository, so that they can be compared with through the tests.

Does it sound reasonable? I've not looked into beancount whether it has special support for this kind of task, but I imagine writing query from scratch is still feasible. The test could be a little slow, if we want to run a query for each day with transaction, but that's low priority to optimize IMO.