convert to GnuCash

[chadwhitacre]

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

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

[chadwhitacre]

Here are our banks and processors, and the formats they provide (to be filled in):

Processor	Format
New Alliance	CSV
Braintree	CSV, ???
Balanced	JSON
PayPal	CSV, ???
Coinbase	???
Samurai	CSV
Stripe	CSV, ???
Citizens	???
Ally	???
PNC	QFX, QBO, OFX, CSV

[chadwhitacre]

Looks like we can get a few good formats from PNC.

[chadwhitacre]

However, PNC only supports 90 days of export history. Beyond that, we're looking at PDFs. Looks like we've got CSV exports from July through September, 2015. We don't have June 2015. I've just downloaded data in all formats since February, 2016. So we've got some gaps. :-(

[chadwhitacre]

Called PNC tech support and didn't get anywhere. I do find some data for June but nothing between September 28, 2015 and February 1.

[tlevine]

In summary, few of the available records are in a convenient format,
so I'm not going to try to script the conversion in any reusable way.
I'll start with the records that we have now and get back to you when I
have a few months ready.

On 12 Apr 09:19, Chad Whitacre wrote:

Called PNC tech support and didn't get anywhere. I do find some data for June but nothing between September 28, 2015 and February 1.

---

You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub:
#22 (comment)

[chadwhitacre]

@tlevine I'm going to install GnuCash and explore it a little bit. Basically our requirements for an accounting system are:

1. we can put data into it
2. we can get reports out of it
3. we can do both with a high degree of transparency
4. we can readily find auditors to work with it

[mattbk]

Interesting. Are you intending to use it with a database backend? That would fit my use case on another project.

[chadwhitacre]

Are you intending to use it with a database backend?

@tlevine mentioned that possibility on our private call last week (gratipay/inside.gratipay.com#448 (comment)). Not sure how else we would provide (3) with GnuCash.

[chadwhitacre]

Criterion	Ledger	GnuCash
1. We can input data into it.	★★☆☆☆	★★★★☆
2. We can get reports out of it.	★★☆☆☆	★★★★☆
3. We can do both with a high degree of transparency.	★★★★★	★☆☆☆☆
4. We can readily find auditors to work with it.	★★★☆☆	★★★☆☆

[chadwhitacre]

With Ledger we have to write code for input and reporting.

With GnuCash we have to write code for transparency.

@tlevine Which code do you wanna write? :-)

[chadwhitacre]

mentioned that possibility

More specifically, the proposal here is that we would store a gratipay.gnucash Sqlite file in this repo instead of the *.dat files we've got now. We would input and modify data using the GnuCash GUI, each on our own computer, and we would provide reporting and drill-down transparency with a read-only web app for browsing gratipay.gnucash.

[mattbk]

reporting and drill-down transparency with a read-only web app for browsing gratipay.gnucash.

That sounds like a useful project on its own...

[techtonik]

With Ledger we have to write code for input and reporting.

As long as we do this in Python, it is fine. We can do this in IPython notebook and copy it for every time period with comments. This way we also make people more educated in accounting.

With GnuCash we have to write code for transparency.

In C/C++? No way. This is buggy and exploitable. You don't want to deal with it in server/production environment unless you're Google with prizes over $1337 per vulnerability. They have nice art, but using autotools for financially backed up project in 2016 is like being a hardcore oldscoller. I am +1 for solving financial tasks in tools that are more suited for financial calculators.

If GNU Cash GUI can be used for editing the data - that's fine, but making it an essential dependency feels wrong.

Hi. =)

[chadwhitacre]

In C/C++?

No. We would access the SQLite database using whatever we wanted (i.e., probably Python).

[mattbk]

No. We would access the Sqlite database using whatever we wanted (i.e., probably Python).

Assuming it's in some meaningful format.

[chadwhitacre]

http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png

[techtonik]

Wow. That's big. I wonder who did they generate it?

[chadwhitacre]

GnuCash takes a long time to open up on my (Mac) laptop.

[chadwhitacre]

Like, a couple minutes.

[chadwhitacre]

@tlevine and I talked about this on our private call; @kaguillera and I are teasing out the details here IRL: the collaborative editing experience with GnuCash will be much worse than the collaborative editing experience with Ledger.

GnuCash

1. Edit file on local computer.
2. Commit changes to binary SQLite file to Git.
3. Push changes to repo.
4. Describe changes manually in a GitHub comment.
5. Others pull the changes, and inspect the file in GnuCash on their local computer.

Ledger

1. Edit the files on local computer (or using GitHub's web interface).
2. Commit changes to text files to Git.
3. Push changes to repo.
4. Changes are evident in diffs on GitHub. Descriptions can add context and explanation, but aren't the sole basis for comparison.
5. Others may pull the changes, but can also inspect on GitHub.

Step (4) is the big difference.

[chadwhitacre]

If a custom-built web viewer for GnuCash files (#22 (comment)) is the answer to step (4), then the custom-built web viewer needs to supporting diffing of different versions of a GnuCash file, not just browsing a snapshot.

[techtonik]

Why can't GnuCash use ledger format as final input and output?

[tlevine]

If I were doing this as a real job, I would have pretended to cared for
all of these various careful considerations and presented meaningless
responses to make you think that your thoughts are very thoughtful.

I am first going to try to reconcile the bank/processor records.
Then I'll take a guess as to what belongs in each of escrow, fee buffer,
and operations, and I can talk with Chad, probably, about the ones I
couldn't guess. Then we can think about how to store the records and
what tooling to write around that.

On 12 Apr 12:29, Chad Whitacre wrote:

http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png

---

You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub:
#22 (comment)

[chadwhitacre]

I am first going to try to reconcile the bank/processor records.

I am looking forward to seeing what you come up with, because at this point I have no idea what you mean by this. :-)

[tlevine]

Well I can explain that.

I want to itemize all movements of money among bank accounts, payment
processors, liability accounts, income accounts, and expense accounts,
without concern for whether the money moved between escrow, operations,
or fee buffer. I will compare transaction logs with each other and with
monthly statements, if those are available.

On 15 Apr 10:55, Chad Whitacre wrote:

I am first going to try to reconcile the bank/processor records.

I am looking forward to seeing what you come up with, because at this point I have no idea what you mean by this. :-)

---

You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub:
#22 (comment)

[chadwhitacre]

Alright, this fizzled.