Conversion from Beancount to SQLite3

[Martin Blais]

Hi,

Tonight I implemented a new command to convert from Beancount to an SQL database (SQLite3 implementation).

I did this because I'm iterating on an implementation for the native query syntax for generating reports and I want to also play with another SQL client on a similar corpus of transactions to witness what the pain points are, and to test out how far one can get with what SQL provides. (Note that this is not meant to replace the Beancount query syntax, I don't think you could do, e.g., operations on inventories, closing for balance sheets, and more operations that the native syntax will provide.)

The script is here:

https://bitbucket.org/blais/beancount/src/20d209db668a803ae8c55187e6faefeb5df7c25e/src/python/beancount/scripts/sql.py?at=default

Invoke it with 

  bean-sql   filename.beancount  filename.db

You can test this out on the tutorial example located in "beancount/examples/tutorial/example.beancount".

If somebody finds this useful, let me know.

Cheers,

[Gour]

On Wed, 15 Oct 2014 00:20:23 -0400
Martin Blais <bl...@furius.ca> wrote:

Hello Martin,

> Tonight I implemented a new command to convert from Beancount to an
> SQL database (SQLite3 implementation).

I still did not convert from Gnucash to ledger-based accounting and
wonder what is your priority in regard to some tool to provide ledger
<--> Beancount conversion?


Of course, my inquiry is in the context of looking your general
development of Beancount.

Sincerely,
Gour

--
The intricacies of action are very hard to understand.
Therefore one should know properly what action is,
what forbidden action is, and what inaction is.

[Martin Blais]

On Wed, Oct 15, 2014 at 4:29 AM, Gour <go...@atmarama.net> wrote:

On Wed, 15 Oct 2014 00:20:23 -0400
Martin Blais <bl...@furius.ca> wrote:

Hello Martin,

> Tonight I implemented a new command to convert from Beancount to an
> SQL database (SQLite3 implementation).

I still did not convert from Gnucash to ledger-based accounting and
wonder what is your priority in regard to some tool to provide ledger
<--> Beancount conversion?

I'm interested in writing a conversion script from and to GNUcash to Beancount and/or Ledger (I don't see why the script could not support both output types, that should be easy). I had a quick look a while back and GNUcash exports an XML file that should be easily parseable--I think this would be a moderate to easy task. The only hurdle to doing this so far is that I don't have a decent input corpus to work from and I haven't cared enough to fiddle with GNUcash myself to produce something realistic.

If you want to help expedite this, you can create a GNUcash ledger that has at least one of every kind of transaction (especially with units held at cost, investments, etc.) that you use and share it with the list, either I or someone will surely pick it up and make a little conversion script that you can then try it on your real GNUcash ledger (note that this will take iterations, we might not get it right the first time and without access to your own file it would take cooperation and debug support to iron out all the issues that come up converting your file).

If we could build such a script, you could keep working with GNUcash for a while as you test out getting data out of Ledger or Beancount or whatever, until you're satisfied enough you'd be willing to move your source file over. I'm happy starting to hash this out and include it in the LedgerHub implementation.

Of course, my inquiry is in the context of looking your general
development of Beancount.

On Wed, Oct 15, 2014 at 6:33 AM, Gour <go...@atmarama.net> wrote:

On Wed, 15 Oct 2014 10:29:07 +0200
Gour <go...@atmarama.net> wrote:

> I still did not convert from Gnucash to ledger-based accounting and
> wonder what is your priority in regard to some tool to provide ledger
> <--> Beancount conversion?

I built one recently:

https://bitbucket.org/blais/beancount/src/539d531607f17f6f90599fbb73ea9b41b4dde66d/src/python/beancount/reports/convert_reports.py?at=default

It's certainly imperfect at this point.

 

Excuse me for making noice...now I'm looking at tutorial and see:

"It should be possible to convert example files generated from the
script to Ledger syntax, like this:

bean-query example.beancount ledger > example.lgr "

which really makes me enthusiastic to try Beancount. ;)

The conversion is imperfect; the feature sets between the two softwares do not overlap 100%. There are things I can do in Beancount that I cannot convert to Ledger, and Ledger also has features that have no equivalent in Beancount. But it works on the example file for Beancount which has a moderate amount of complexity.

Now, I'm going to check whether there is some helper for creating
budgets...both (h)ledger require some extra calc to be done to estimate
correct monthly amounts.

I have so far only thoughts about how to handle budgets, and it's not clear how to best carry this out. You would have to write a plugin yourself if what you need is a budget feature. It's pretty low down the list of things I care about.

[Gour]

On Wed, 15 Oct 2014 11:26:15 -0400
Martin Blais <bl...@furius.ca> wrote:

> If you want to help expedite this, you can create a GNUcash ledger
> that has at least one of every kind of transaction (especially with
> units held at cost, investments, etc.) that you use and share it with
> the list, either I or someone will surely pick it up and make a
> little conversion script that you can then try it on your real
> GNUcash ledger (note that this will take iterations, we might not get
> it right the first time and without access to your own file it would
> take cooperation and debug support to iron out all the issues that
> come up converting your file).

I do not use all these stuff, but have you seen:

https://github.com/MatzeB/pygnucash which converts from Gnucash's
SQLite3 db?

Considering your recent SQL-related work, it might be easier than to
convert from XML?


Sincerely,
Gour

--
For him who has conquered the mind, the mind is the best of
friends; but for one who has failed to do so, his mind will
remain the greatest enemy.

[Martin Blais]

On Wed, Oct 15, 2014 at 12:15 PM, Gour <go...@atmarama.net> wrote:

On Wed, 15 Oct 2014 11:26:15 -0400
Martin Blais <bl...@furius.ca> wrote:

> If you want to help expedite this, you can create a GNUcash ledger
> that has at least one of every kind of transaction (especially with
> units held at cost, investments, etc.) that you use and share it with
> the list, either I or someone will surely pick it up and make a
> little conversion script that you can then try it on your real
> GNUcash ledger (note that this will take iterations, we might not get
> it right the first time and without access to your own file it would
> take cooperation and debug support to iron out all the issues that
> come up converting your file).

I do not use all these stuff, but have you seen:

https://github.com/MatzeB/pygnucash which converts from Gnucash's
SQLite3 db?

Considering your recent SQL-related work, it might be easier than to
convert from XML?

6 and a half-dozen of the other, same to me.

This looks like there's much of the information needed to do this.

But no tests.

You could adapt this to spit out a Beancount file if you needed to, with relatively minor changes.

[Wm...]

Wed, 15 Oct 2014 19:16:58
<CAK21+hOJYjx=jNMzrvPZGwoK+U+0KFr...@mail.gmail.com>
Martin Blais <bl...@furius.ca> wrote...

I did that just the other day. Not the prettiest code / conversion but
the balance sheets agree afterwards not counting cosmetic presentational
differences.

--
Wm...

[Martin Blais]

On Fri, Oct 17, 2014 at 5:14 PM, Wm... <tcn...@tarrcity.demon.co.uk> wrote:

Wed, 15 Oct 2014 19:16:58 <CAK21+hOJYjx=jNMzrvPZGwoK+U+0KFrfYxZJKHRecva_FQ78sg@mail.gmail.com>

That's great.

Can you fork the project and submit your Beancount converter somewhere?

Also, if you can identify the distinct of transactions you're using that would also help me create a unit test.

Thanks,

 

[Wm...]

Fri, 17 Oct 2014 18:20:47
<CAK21+hOUvzxe66R9jJzq0PtZJRVc=4oTFkjVyLo...@mail.gmail.com>
Martin Blais <bl...@furius.ca> wrote...

[GnuCash SQLite3 to Beancount]

MartinB:

>>> You could adapt this to spit out a Beancount file if you needed to,
>>> with relatively minor changes.

Wm:

>> I did that just the other day. Not the prettiest code / conversion but
>> the balance sheets agree afterwards not counting cosmetic presentational
>> differences.

>That's great.
>Can you fork the project and submit your Beancount converter somewhere?

Prefer not to as I'm sticking with GnuCash long term, I happened to be
writing some GnuCash+SQL reports for myself when I saw pygnucash
mentioned and wondered if beancount looked much different to ledger-cli
with my data.

I've no interest in maintaining it (beancount seems to be changing file
format more actively than gnucash) and my code changes are extremely
rough, e.g. I didn't bother preserving account names nicely where
beancount is fussier than ledger-cli.

It would make much more sense for MatzeB to have gnucash2beancount.py
alongside his gnucash2ledger.py if he is interested (although it was a
proof of concept for him AIUI) or for you to pick the working bits up.

The changes from MatzeB's original are:

massage account names due to differences between ledger-cli and
beancount; you have almost certainly got a better bit of code to
do that than my effort, I just got rid of stuff in account names
as bean-check complained about it

adjust for differences in account structure permissiveness [2]

present commodity / currency transactions to match beancount

I am happy to share the script with you and answer any queries you have
about the GnuCash side.

Some notes for you to consider:

1. bean-check makes no distinction between a warning and an error
1.1 "Duplicate Entry" drawing 10 cash twice in one day is not an error
1.2 "Unused account" is not an error

[2] gnc has a top level Trading account type, I chose to place them
under Equity in beancount, another person might choose to exclude them
from the conversion process [3]

[3] as an aside I have read your critique of GnuCash in your document
about why you got started on beancount and presume you know a lot of it
doesn't apply. No, I am not trying to change your mind, just mentioning
it as gnc Trading accounts are one of the things many people don't know
about and cover some of your issues, I also consider some to be
unnecessary, but that is my problem :)

Let me know if you want the script here or by e-mail, you are better at
python than me and will make it much tidier in minutes.

>Also, if you can identify the distinct of transactions you're using that
>would also help me create a unit test.
>Thanks,

Most, I would have thought. GnuCash doesn't have distinct transactions
types in the sense I think you mean.

units of stuff (e.g. currencies + commodities) seem to be what excite
you most and my beancount balance sheet agrees with gnucash apart from
the interpretational bits.

perhaps it would be better (considering in another post you are
producing test / sample data for beancount) that you produce a "one of
each type" of transaction distinctly recognised by beancount and someone
(probably me while I am interested) will use that as a comparative.

P.S. MatzeB if you read this will you let me know if you are interested
in having a look at my messy code? The difference really isn't worth
another project

--
Wm...

[Martin Blais]

Actually I was thinking I would just start from what you built and integrate it in my other project, LedgerHub, whose focus is on importing external data, and maintain it myself from there.

The main issue I have is that I don't have a realistic GnuCash input to work from (for unit testing).

The changes from MatzeB's original are:

        massage account names due to differences between ledger-cli and
        beancount; you have almost certainly got a better bit of code to
        do that than my effort, I just got rid of stuff in account names
        as bean-check complained about it

        adjust for differences in account structure permissiveness [2]

        present commodity / currency transactions to match beancount

I am happy to share the script with you and answer any queries you have
about the GnuCash side.

Some notes for you to consider:

1. bean-check makes no distinction between a warning and an error

That's on purpose. I try really hard to minimize the amount of concepts and options. Beancount operates only in "strict" mode; the input has to comply for now, I might relax this later.

> 1.2 "Unused account" is not an error

The way to correctly "fix" this error is by adding a plugin that will automatically remove the unused directives (I haven't done the plugin yet, so for now your list of accounts has to match, maybe I should do that). The idea is that compliant Beancount input has certain characteristics and guarantees. At the moment, one guarantee is that all accounts have a matching open directive, and that unused accounts aren't allowed. Maybe I should relax the unused accounts one though, it has been a bit annoying to me as well when doing demos.

Okay, I'll think about this, this might be a bit overkill.

 

1.1 "Duplicate Entry" drawing 10 cash twice in one day is not an error

Hmm, so here's the thing, in the implementation, each transaction has a unique signature, a "hash", which allows me to make comparisons between sets of transactions. The transactions will only be detected as duplicates if and only if _all_ of their fields are exactly the same. This rarely if ever happens in practice. You probably were cut-n-pasting the same transaction to test things out.

The reason this exists is that it has happened a few times in the past that in the course of importing a transaction was copied twice - clearly an error. This check exists to detect this.

Okay, so here's what I'm going to do:

- Make the unused account check optional; it will be available through an optional plugin.

- Make the duplicates check optional; similarly will be available through an optional plugin.

Those who want super tight checks will have to add the options. 

Something like this:

  option "plugin" "beancount.plugins.nounused"

  option "plugin" "beancount.plugins.noduplicates"

By default unused accounts and duplicates would be allowed.

How's that?

 

[2] gnc has a top level Trading account type, I chose to place them
under Equity in beancount, another person might choose to exclude them
from the conversion process [3]

Using an Equity account for GnuCash trading accounts is the correct thing to do.

I'm aware of this feature, I've read the docs in the past. Beancount already automatically implements a solution similar to the foreign currency trading account method described in this document. Unlike GnuCash, you don't even have to do anything to make it work--a special "Equity:Conversions" account is added to contain the sum total of all conversions (which, if correct, should balance each other near zero, unless you did large transfers with hugely varying exchange rates).  I've been referring to this issue as "the conversions problem" so far. I've been meaning to document my solution, but it's low on the list of priorities--it just works and as a user you don't have to worry about it much. You can peruse the source code in the meantime if you need the full detail. In short: Beancount inserts a special transaction that balances out the converted amounts so that the sum total of all transactions is precisely zero.

 

[3] as an aside I have read your critique of GnuCash in your document
about why you got started on beancount and presume you know a lot of it
doesn't apply.  No, I am not trying to change your mind, just mentioning
it as gnc Trading accounts are one of the things many people don't know
about and cover some of your issues, I also consider some to be
unnecessary, but that is my problem :)

I don't know what you're talking about.

In the short paragraph, I'm making the following points:

- Using a language alleviates the need for inconvenient UIs

- I can break GnuCash in about an hour (done this several times, as other people on this list have)

What's your point?

Let me know if you want the script here or by e-mail, you are better at
python than me and will make it much tidier in minutes.

If you can email the script it would save me time, thank you.

Much, much more important to me is if someone could create a representative GnuCash input file that showcases a variant of every possible kind of transaction that can be done with it. I would be happy to write and maintain code to make that automatic conversion for Beancount, Ledger and HLedger. I just need some data.

 

>Also, if you can identify the distinct of transactions you're using that
>would also help me create a unit test.
>Thanks,

Most, I would have thought.  GnuCash doesn't have distinct transactions
types in the sense I think you mean.

units of stuff (e.g. currencies + commodities) seem to be what excite
you most and my beancount balance sheet agrees with gnucash apart from
the interpretational bits.

perhaps it would be better (considering in another post you are
producing test / sample data for beancount) that you produce a "one of
each type" of transaction distinctly recognised by beancount and someone
(probably me while I am interested) will use that as a comparative.

P.S. MatzeB if you read this will you let me know if you are interested
in having a look at my messy code?  The difference really isn't worth
another project

--
Wm...

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/n4rcDlRF7pQUFwcf%40tarrcity.demon.co.uk.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

1.1 "Duplicate Entry" drawing 10 cash twice in one day is not an error

Hmm, so here's the thing, in the implementation, each transaction has a unique signature, a "hash", which allows me to make comparisons between sets of transactions. The transactions will only be detected as duplicates if and only if _all_ of their fields are exactly the same. This rarely if ever happens in practice. You probably were cut-n-pasting the same transaction to test things out.

The reason this exists is that it has happened a few times in the past that in the course of importing a transaction was copied twice - clearly an error. This check exists to detect this.

Okay, so here's what I'm going to do:

- Make the unused account check optional; it will be available through an optional plugin.

- Make the duplicates check optional; similarly will be available through an optional plugin.

Those who want super tight checks will have to add the options. 

Something like this:

  option "plugin" "beancount.plugins.nounused"

  option "plugin" "beancount.plugins.noduplicates"

By default unused accounts and duplicates would be allowed.

How's that?

Implemented. 

https://bitbucket.org/blais/beancount/commits/01ab95c5ab260fb3d6dc34861e5111c4da3ff35d?at=default

https://bitbucket.org/blais/beancount/commits/0362940c62197b2ab4b0838b5f7c754afb2ef7eb?at=default

Beancount users: if you still wants these validation checks, add this to your file:

[Wm...]

Sat, 18 Oct 2014 13:48:05
<CAK21+hMNcR-j_6skp3w+b0Fe...@mail.gmail.com>
Martin Blais <bl...@furius.ca> wrote...

>> 1.1 "Duplicate Entry" drawing 10 cash twice in one day is not an error
>>>
>>
>> Hmm, so here's the thing, in the implementation, each transaction has a
>> unique signature, a "hash", which allows me to make comparisons between
>> sets of transactions.

me, says Martin, me !

>>The transactions will only be detected as duplicates
>> if and only if _all_ of their fields are exactly the same. This rarely if
>> ever happens in practice.

In *your* life!

This is where I must stand up and say I think you are a fool.

Your life may be such that you never do something twice in one day, I am
not like that. My life is not like that.

GnuCash has the notion of a transaction ID that ledger-cli and beancount
don't.

Why do you think they have that if not for ordinary repetitive stuff? If
you've only just realised a person may withdraw the same amount of notes
from a cash machine in one day you are living a very restricted life in
my opinion.

>>You probably were cut-n-pasting the same
>> transaction to test things out.

Wrong. I, a human being, withdrew a unit note of my countries
convenient currency, unit 10, from a machine, provided by a global bank
twice in one day.

I am *NOT* going to apologize to you for doing that, OK! Sort your
stuff out.

TBH, I am finding this bit of you weird, MartinB.

>> The reason this exists is that it has happened a few times in the past
>> that in the course of importing a transaction was copied twice - clearly an
>> error. This check exists to detect this.

Why should I or anyone else have to play extra checks and padding
because you or your bank fucked up in the past? This is *not* the way
to get people on your side, Martin.

>> Okay, so here's what I'm going to do:
>> - Make the unused account check optional; it will be available through an
>> optional plugin.

CoA sensibility suggests this is a good idea.

>> - Make the duplicates check optional; similarly will be available through
>> an optional plugin.

I think someone else said something about apportioning and accounts not
balancing exactly. I mean, if I sell stuff I don't have *and* I didn't
have Trading accounts would you prevent the record of the transaction?

This is the issue. Do you record reality or not? I think you are, in
some circumstances worse than ledger-cli because you want to impose.
Post event things may be clear.

>> Those who want super tight checks will have to add the options.
>> Something like this:
>>
>> option "plugin" "beancount.plugins.nounused"
>> option "plugin" "beancount.plugins.noduplicates"
>>
>> By default unused accounts and duplicates would be allowed.
>> How's that?

I'm unaffected, your theoretical 3 users may speak.

>Implemented.
>https://bitbucket.org/blais/beancount/commits/01ab95c5ab260fb3d6dc34861e
>5111c4da3ff35d?at=default
>https://bitbucket.org/blais/beancount/commits/0362940c62197b2ab4b0838b5f
>7c754afb2ef7eb?at=default

>
>Beancount users: if you still wants these validation checks, add this to
>your file:
>
> option "plugin" "beancount.plugins.nounused"
> option "plugin" "beancount.plugins.noduplicates"

BTW, I think if you dropped the unnecessary precision you might get some
extra users.

--
Wm...

[Martin Blais]

On Sat, Oct 18, 2014 at 4:11 PM, Wm... <tcn...@tarrcity.demon.co.uk> wrote:

Sat, 18 Oct 2014 13:48:05 <CAK21+hMNcR-j_6skp3w+b0Ferh5DixJoxbi5oHGn76dYjvzMZQ@mail.gmail.com>

Martin Blais <bl...@furius.ca> wrote...

1.1 "Duplicate Entry" drawing 10 cash twice in one day is not an error

Hmm, so here's the thing, in the implementation, each transaction has a
unique signature, a "hash", which allows me to make comparisons between
sets of transactions.

me, says Martin, me !

The transactions will only be detected as duplicates
if and only if _all_ of their fields are exactly the same. This rarely if
ever happens in practice.

In *your* life!

This is where I must stand up and say I think you are a fool.

Your life may be such that you never do something twice in one day, I am not like that.  My life is not like that.

No, you don't understand, it's entirely legitimate to do multiple cash transactions of the same amount in the same day, you can just vary the narration string to make each unique. (From an implementation POV, it's a really nice property for transactions to have a unique signature, it makes it possible to do diffs on sets of transactions.) Anyhow, I've moved this check to be optional following your comment, so it does not get triggered by default, it sounds reasonable that some of the stronger checks should be optional. If you want the check you have to explicitly enable it with a plugin now.

GnuCash has the notion of a transaction ID that ledger-cli and beancount don't.

Why do you think they have that if not for ordinary repetitive stuff? If you've only just realised a person may withdraw the same amount of notes from a cash machine in one day you are living a very restricted life in my opinion.

Again, you're misinterpreting the intent of the constraint. I never claimed you could not do this in Beancount, just putting a unique narration/comment on each transaction disambiguated it in the first place.

You probably were cut-n-pasting the same
transaction to test things out.

Wrong.  I, a human being, withdrew a unit note of my countries convenient currency, unit 10, from a machine, provided by a global bank twice in one day.

I am *NOT* going to apologize to you for doing that, OK!  Sort your stuff out.

TBH, I am finding this bit of you weird, MartinB.

The reason this exists is that it has happened a few times in the past
that in the course of importing a transaction was copied twice - clearly an
error. This check exists to detect this.

Why should I or anyone else have to play extra checks and padding because you or your bank fucked up in the past?  This is *not* the way to get people on your side, Martin.

The bank never made a mistake, the user did. It's easy to make mistakes. I was trying to provide some help to the user in avoiding to make the common mistake of repeating the _very_ same transaction by accident.

(And FWIW, I'm not trying to get people "on my side", I'm just building software. If you don't like it, please don't use it.)

Okay, so here's what I'm going to do:
- Make the unused account check optional; it will be available through an
optional plugin.

CoA sensibility suggests this is a good idea.

- Make the duplicates check optional; similarly will be available through
an optional plugin.

I think someone else said something about apportioning and accounts not balancing exactly.  I mean, if I sell stuff I don't have *and* I didn't have Trading accounts would you prevent the record of the transaction?

This is the issue.  Do you record reality or not?  I think you are, in some circumstances worse than ledger-cli because you want to impose. Post event things may be clear.

I don't understand the two paragraphs above.

- Beancount specifically does not allow you to reduce units from lots you do not have. That's the key difference between the way inventory booking is modeled in Ledger and Beancount, we take two distinct approaches to this.

- Yes, the intent is to create a record of reality in all these softwares.

What do you mean by "apportioning"?

Can you detail the example of "if I sell stuff I don't have *and* I didn't have Trading accounts"?

Those who want super tight checks will have to add the options.
Something like this:

  option "plugin" "beancount.plugins.nounused"
  option "plugin" "beancount.plugins.noduplicates"

By default unused accounts and duplicates would be allowed.
How's that?

I'm unaffected, your theoretical 3 users may speak.

Implemented.
https://bitbucket.org/blais/beancount/commits/01ab95c5ab260fb3d6dc34861e
5111c4da3ff35d?at=default
https://bitbucket.org/blais/beancount/commits/0362940c62197b2ab4b0838b5f
7c754afb2ef7eb?at=default

Beancount users: if you still wants these validation checks, add this to
your file:

 option "plugin" "beancount.plugins.nounused"
 option "plugin" "beancount.plugins.noduplicates"

BTW, I think if you dropped the unnecessary precision you might get some extra users.

I'm not sure the cynical comments deserve a response but you're wholly welcome not to use the stuff I build. In fact, I would encourage you not to use it. I don't work for you AFAIK, I built this software to manage my own finances and provide it for free or others who are be interested (it works great and if I'm the only user of it I'll be very satisfied) and love to participate in discussions to solve personal financial management problems. If you don't like it, please go play with something else.

About the precision issue, I've already written a proposal doc to outline what the problems are, and what could be a great solution, which will get implemented:

https://docs.google.com/document/d/1MY2JMiiXUmcwsOT0CkiK-fCo0ZE7nbr8uTcTL50b6X4/

And thanks for the two bits of constructive feedback 2 or 3 emails prior. I'm always glad to hear of friction others may have and to make suitable adjustments.

Good luck,

[Wm...]

Sat, 18 Oct 2014 17:23:34
<CAK21+hMneyWt_NaUJt9cEKp3...@mail.gmail.com>
Martin Blais <bl...@furius.ca> wrote...

>Again, you're misinterpreting the intent of the constraint. I never claimed
>you could not do this in Beancount, just putting a unique narration/comment
>on each transaction disambiguated it in the first place.

I do not want to explain to my accounting s/w why I took some money out
of my account. I do that by saying how I spend it. If you and other
people withdraw money from a machine and *only* ever spend it on what
you decided on at that moment I feel sorry for you.

>The bank never made a mistake, the user did. It's easy to make mistakes.

How? Do you mean I booked a hiking trip in Nepal kind of mistake ?

> I
>was trying to provide some help to the user in avoiding to make the common
>mistake of repeating the _very_ same transaction by accident.

Isn't the obvious check, "do my accounts agree with my bank" ?

>(And FWIW, I'm not trying to get people "on my side", I'm just building
>software. If you don't like it, please don't use it.)

I'm going to go back to SQL reporting, the thing you say can't be done.
Ho hum, losing interest fast.

>I'm not sure the cynical comments deserve a response but you're wholly
>welcome not to use the stuff I build. In fact, I would encourage you not to
>use it.

ok

> I don't work for you AFAIK, I built this software to manage my own
>finances

it shows

>and provide it for free or others who are be interested (it works
>great and if I'm the only user of it I'll be very satisfied) and love to
>participate in discussions to solve personal financial management problems.
>If you don't like it, please go play with something else.

ok

>About the precision issue, I've already written a proposal doc to outline
>what the problems are,

as I and other people have noticed, you do that a lot, I hope it makes
you feel better.

> and what could be a great solution, which will get
>implemented:
>https://docs.google.com/document/d/1MY2JMiiXUmcwsOT0CkiK-fCo0ZE7nbr8uTcT
>L50b6X4/

oh, you're going to implement it? great!

what a pity the link doesn't work, but that might be your crap quoting

>And thanks for the two bits of constructive feedback 2 or 3 emails prior.
>I'm always glad to hear of friction others may have and to make suitable
>adjustments.

ok, I guess you'll be just the same place in a year or two's time.

Gour will be no better off and I'll have some nice SQL reports to show.

Enjoy.

--
Wm...

[Stefano Zacchiroli]

Wm...,

On Sun, Oct 19, 2014 at 03:32:49PM +0100, Wm... wrote:
> >I don't work for you AFAIK, I built this software to manage my own
> >finances
>
> it shows

[...]

> >About the precision issue, I've already written a proposal doc to outline
> >what the problems are,
>
> as I and other people have noticed, you do that a lot, I hope it makes you
> feel better.

[...]

> what a pity the link doesn't work, but that might be your crap quoting

I'm just a sporadic participants on these lists, but I'd like to say out
loud that I don't appreciate the ad-hominem style of communication
exemplified by the above passages, and that can be found in other mails
of yours in this thread.

I found those passages to be disrespectful of the work that Martin has
poured into Beancount and that, thanks to Beancount being Free Software,
he is de facto donating to the world. Regarding the papers that Martin
has written, according to your interpretation only to "feel better",
they have in fact been very useful contributions to the Ledger*
communities and I, among others, have learned a lot by reading them or
by discussing with him on these lists.

No matter what Martin has done for this community, on which you might
have a different judgement than mine, it looks like he was trying to
help you all along this thread. Considering all this, I would
appreciate if you could try to show at least some basic form of respect
when participating on these lists.

Thanks for considering,
--
Stefano Zacchiroli . . . . . . . za...@upsilon.cc . . . . o . . . o . o
Maître de conférences . . . . . http://upsilon.cc/zack . . . o . . . o o
Former Debian Project Leader . . @zack on identi.ca . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »

[Martin Blais]

On Sun, Oct 19, 2014 at 9:32 AM, Wm... <tcn...@tarrcity.demon.co.uk> wrote:

Sat, 18 Oct 2014 17:23:34 <CAK21+hMneyWt_NaUJt9cEKp3YFh9nfMa_ooUc9sM2J_AEg...@mail.gmail.com>

Martin Blais <bl...@furius.ca> wrote...

Again, you're misinterpreting the intent of the constraint. I never claimed
you could not do this in Beancount, just putting a unique narration/comment
on each transaction disambiguated it in the first place.

I do not want to explain to my accounting s/w why I took some money out of my account.  I do that by saying how I spend it.  If you and other people withdraw money from a machine and *only* ever spend it on what you decided on at that moment I feel sorry for you.

You are still not understanding what I said. There is no expectation that you would have to explain anything. The validation check was just there to make sure you don't accidentally enter the very very same transaction twice (entirely identical transactions on the same day happens to be pretty rare in practice). The check worked by hashing all the fields on a transaction object to a unique signature, and then collecting all those signatures and verifying all of them were unique. You could have easily made _any_ part of the transaction unique to disambiguate it. The most semantically applicable way to do this would have been to add a unique link to it:

  2014-02-02 * "WITHDRAWAL" ^2

    ...

In the above, "^2" is the link field. It gets hashed along with the rest of the transaction data.

By the way, did you notice that I made both of the validation checks you commented about optional, and that those checks don't trigger by default now?

The bank never made a mistake, the user did. It's easy to make mistakes.

How?  Do you mean I booked a hiking trip in Nepal kind of mistake ?

No, not so much by actually _booking_ it twice but rather by _copying_ the transaction data twice.

Surely, if you copied an expensive Nepal hiking trip twice by accident you would want some sort of error checking that reports that.

I
was trying to provide some help to the user in avoiding to make the common
mistake of repeating the _very_ same transaction by accident.

Isn't the obvious check, "do my accounts agree with my bank" ?

The double-entry method does not necessarily provide checks for all accounts. You can typically only enter balance checks for Assets and Liabilities accounts, rarely if ever for Income or Expenses accounts (their lifetime balance generally has no associated external balance counter). So in the case of small cash expenses that you enter manually, unless you add explicit balance checks on your "cash" account (e.g., by counting the dollars in your wallet and entering a corresponding balance check in your ledger to assert it), erroneous duplicated cash transactions could easily go undetected. This is the kind of mistake - a mistake in user entry, not from a bank - that the validation check was intended to prevent you from making.

[...] 

About the precision issue, I've already written a proposal doc to outline
what the problems are,

as I and other people have noticed, you do that a lot, I hope it makes you feel better.

Writing is thinking. The purpose of the documents is to describe a problem that occurs to become familiar with its various aspects, summarize the solutions that all the projects have come up with to address it so far, point out benefits and flaws in all these solutions and to try to propose a better solution, and then  begin a discussion and reach for feedback from other developers. Many of the other developers have commented on them, so it has not been a futile exercise so far. Putting the ideas down also has helped me understand them better (yes, that makes me feel good).  If you're not interested in the development aspect of these things, you don't have to read the proposals.

and what could be a great solution, which will get
implemented:
https://docs.google.com/document/d/1MY2JMiiXUmcwsOT0CkiK-fCo0ZE7nbr8uTcT
L50b6X4/

oh, you're going to implement it?  great!

what a pity the link doesn't work, but that might be your crap quoting

To "open" a link, bring up a web browser and copy-paste the text of the link (this is called a "URL") in the location bar. The location bar is the text field at the top of the window. If you're reading this email from a web browser and the text is blue, you may also be able to just click on the blue text with the mouse.

And thanks for the two bits of constructive feedback 2 or 3 emails prior.
I'm always glad to hear of friction others may have and to make suitable
adjustments.

ok, I guess you'll be just the same place in a year or two's time.

Gour will be no better off and I'll have some nice SQL reports to show.

Can't wait to see them.  Please share some great results with us (and some code!), it would be lovely to compare what the GnuCash community produces with the Ledgerverse. There's always room to grow I'm sure.

[Wm...]

Sun, 19 Oct 2014 16:57:29 <20141019145...@upsilon.cc>
Stefano Zacchiroli <za...@upsilon.cc> wrote...

>Wm...,
>
>On Sun, Oct 19, 2014 at 03:32:49PM +0100, Wm... wrote:
>> >I don't work for you AFAIK, I built this software to manage my own
>> >finances
>>
>> it shows

why are you critical of this comment? Martin agrees with me

>> >About the precision issue, I've already written a proposal doc to outline
>> >what the problems are,
>>
>> as I and other people have noticed, you do that a lot, I hope it makes you
>> feel better.

if Martin feels worse about explaining his motivations he'd stop,
surely?

>> what a pity the link doesn't work, but that might be your crap quoting
>
>I'm just a sporadic participants on these lists, but I'd like to say out
>loud that I don't appreciate the ad-hominem style of communication
>exemplified by the above passages, and that can be found in other mails
>of yours in this thread.

Isn't that ad-hominem too? Yes, I am aware of my "style"

I didn't come here to attack Martin, he chose a style of defence. Why
aren't you having a go at him for reacting badly ?

>I found those passages to be disrespectful of the work that Martin has
>poured into Beancount and that, thanks to Beancount being Free Software,
>he is de facto donating to the world.

I think you are wrong. Martin is doing beancount for *himself* not the
world and actually and specifically doesn't care about the world.

Who is the rude person now?

> Regarding the papers that Martin
>has written, according to your interpretation only to "feel better",
>they have in fact been very useful contributions to the Ledger*
>communities and I, among others, have learned a lot by reading them or
>by discussing with him on these lists.

I don't doubt that. I question the motivation.

>No matter what Martin has done for this community, on which you might
>have a different judgement than mine, it looks like he was trying to
>help you all along this thread.

I didn't ask for help. Someone else did. My opinion of that person
isn't high as I have encountered him in the GnuCash newsgroups too.

> Considering all this, I would
>appreciate if you could try to show at least some basic form of respect
>when participating on these lists.

If you want me to play stupid and lick bum the answer is NO, Martin has
a good brain, sure, but he fails in real world stuff.

>Thanks for considering,

Seriously, what were you expecting?

--
Wm...

[Wm...]

Sun, 19 Oct 2014 11:38:28
<CAK21+hMfxt2eQU2xGwUX0xzH...@mail.gmail.com>
Martin Blais <bl...@furius.ca> wrote...

Wm:

> Gour will be no better off and I'll have some nice SQL reports to
> show.
>
>Can't wait to see them.  Please share some great results with us (and
>some code!), it would be lovely to compare what the GnuCash community
>produces with the Ledgerverse. There's always room to grow I'm sure.

There is some recent SQL stuff in the gnc lists that may be of interest,
I have a weird idea of trying to make your query work with other db
based backends like those offered by GnuCash but IIRC you said SQL
couldn't do it.

Ho hum.

Are you still sure about that?

--
Wm...

[Martin Blais]

On Sat, Dec 13, 2014 at 10:06 AM, Wm... <tcn...@tarrcity.demon.co.uk> wrote:

Sun, 19 Oct 2014 11:38:28 <CAK21+hMfxt2eQU2xGwUX0xzHFGO-V6axRbeWWrEz_kL_uvML7A@mail.gmail.com>

Martin Blais <bl...@furius.ca> wrote...

Wm:

 Gour will be no better off and I'll have some nice SQL reports to
 show.

Can't wait to see them.  Please share some great results with us (and
some code!), it would be lovely to compare what the GnuCash community
produces with the Ledgerverse. There's always room to grow I'm sure.

There is some recent SQL stuff in the gnc lists that may be of interest, I have a weird idea of trying to make your query work with other db based backends like those offered by GnuCash but IIRC you said SQL couldn't do it.

Why don't you share a link with the list.