gratipay / finances Goto Github PK
View Code? Open in Web Editor NEWGratipay's financial accounting system
Gratipay's financial accounting system
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.
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
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.
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.
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:
domain | price |
---|---|
gratipay.accountant |
$49 (first year $9.90) |
gratipay.finance |
$69 |
gratipay.financial |
$69 |
gratipay.money |
$39 |
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.
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:
This is a meta-ticket for all the months we need to catch up on, going back to the beginning.
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.
@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. 👍
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.
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.
Afaict we only have PDFs and we should at least have that data in CSV.
@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 ...
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.
As noted at #22 (comment) we only have good formats for the past two months, before that we only have PDFs. 😞
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?).
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!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.