Working with per-bank-account files

[Tobias Pfeiffer]

Hi,

I wanted to ask the mailing list for a kind of "best practice" approach
when working with multiple "physical" (that word doesn't fit at all,
though) accounts, like bank, credit card, PayPal etc.

To import data from the past into ledger, I have written scripts that
read my account statements from multiple accounts (CSV in all possible
formats, PDFs etc.) and write them to ledger files, such as
"credit-card.ledger", "savings.ledger", "paypal.ledger" etc. Now with
that approach, I can do stuff like
$ ledger -f credit-card.ledger -f savings.ledger -f paypal.ledger bal
and that works correctly, but there are several issues I was thinking
about:

- The entries are in correct date order within each file, but when I
combine them (either using multiple `-f` parameters or one file with
`include` directive) then they are not, so without extra sorting
parameters, `ledger reg` gives an unordered output. Also, balance
assertions for transactions from one of those account to another
account do not work if the transaction is not in the correct
position. Therefore I considered to write a script that merges these
files in the correct date order into some big "all.ledger" file.

- For future updates, of course I want to use the exact same scripts,
rather than adding transactions by hand. In fact, since I add my cash
expenses to a mobile app right away and can import into ledger from
there, it should hardly be necessary to add transactions manually.
That means that every month or so (whenever a new account statement
arrives), I would re-run my scripts and then merge them with
all.ledger. However, I would have to deal with transactions that
appear in *two* account statements (like, when sending money from my
savings account to PayPal).

- Often it will become necessary to edit transactions by hand, for
example, change the clearing state, add or remove tags or edit the
payee. I wonder in which files to do this and if it will work
correctly with merging.

So, all of this is not exactly rocket science and surely doable, but I
thought that probably everyone that maintains multiple files and/or does
not enter all transactions by hand must have run into similar issues, so
maybe there is a set of recommendations or best practices, or even
helper scripts available? Looking forward to hearing any advice!

Thanks,
Tobias

[Craig Earls]

I use one big file.

My account tree looks like this:

Assets:BankA:Checking

Assets:BankA:Savings

Assets:BankB:Savings

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Craig, Corona De Tucson, AZ
enderw88.wordpress.com

[Tobias Pfeiffer]

Hi Craig,

On Thu, 17 Mar 2016 08:08:25 -0700 Craig Earls wrote:
> I use one big file.
>
> My account tree looks like this:
>
> Assets:BankA:Checking
> Assets:BankA:Savings
> Assets:BankB:Savings

So how do you get your data into that big file? Do you input the data
by hand?

Thanks
Tobias

[Martin Michlmayr]

* Tobias Pfeiffer <tgpfe...@web.de> [2016-03-17 23:55]:

> - The entries are in correct date order within each file, but when I
> combine them (either using multiple `-f` parameters or one file with
> `include` directive) then they are not, so without extra sorting
> parameters, `ledger reg` gives an unordered output. Also, balance
> assertions for transactions from one of those account to another
> account do not work if the transaction is not in the correct
> position. Therefore I considered to write a script that merges these
> files in the correct date order into some big "all.ledger" file.

I use something similar at the moment instead of include directives
because the latter doesn't show line numbers of the included files
properly when there are errors.

I do something like:

cat bank/2016/*.ledger | ledger -f - --sort d print > 2016

> - For future updates, of course I want to use the exact same scripts,
> rather than adding transactions by hand. In fact, since I add my cash
> expenses to a mobile app right away and can import into ledger from
> there, it should hardly be necessary to add transactions manually.
> That means that every month or so (whenever a new account statement
> arrives), I would re-run my scripts and then merge them with
> all.ledger. However, I would have to deal with transactions that
> appear in *two* account statements (like, when sending money from my
> savings account to PayPal).

I'm not sure I understand the part about adding transactions manually
(I maintain a separate .ledger file with cash expenses that is merged
into my final ledger file.)

But regarding transactions in different accounts (e.g. paying your
credit card bill with your bank account): I use an Assets:Transfer
account, as someone suggested on this list a few years ago. (I can
look for a link if you want, but the basic idea is: one transaction
does: Assets:Bank -> Assets:Transfer; and the other Assets:Transfer ->
Liabilities:Credit Card).

The other solution would be use to an UUID tag; if two transactions
have the same UUID, ledger will ignore one.

> - Often it will become necessary to edit transactions by hand, for
> example, change the clearing state, add or remove tags or edit the
> payee. I wonder in which files to do this and if it will work
> correctly with merging.

I only add *new* transactions to my ledger files with my import scripts.
This way, I can modify old entries without any problems.

--
Martin Michlmayr
http://www.cyrius.com/

[Ben Finney]

Martin Michlmayr <t...@cyrius.com> writes:

> I use [one large ledger file] at the moment instead of include

> directives because the latter doesn't show line numbers of the
> included files properly when there are errors.

Where can we see the bug report for that behaviour?

--
\ “There are no significant bugs in our released software that |
`\ any significant number of users want fixed.” —Bill Gates, |
_o__) 1995-10-23 |
Ben Finney

[Martin Michlmayr]

* Ben Finney <ben+l...@benfinney.id.au> [2016-03-18 11:12]:

> > I use [one large ledger file] at the moment instead of include
> > directives because the latter doesn't show line numbers of the
> > included files properly when there are errors.
>
> Where can we see the bug report for that behaviour?

http://bugs.ledger-cli.org/show_bug.cgi?id=786

That is actually much more benign than I remembered. I can confirm
that correct line numbers for the included file are shown for "normal"
errors.

I believe there was another reason why I couldn't use include
directives a few years ago but unfortunately I no longer remember.

Note that my use of "cat *.ledger | ledger -f print" results in other
problems, e.g. this serious one:
http://bugs.ledger-cli.org/show_bug.cgi?id=1033

Maybe I should try to move to include files again.

[Craig Earls]

I have an emacs script that reads the CSV files in. It is smart enough to know what asset account it is reading in and has limited abilities to fuess at an expense category

I probably hand enter 5% of my transactions (~17 per day across all accounts)

I would share the enacs code but ut is highly tuned to the CSV i fet from my banks. 

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tobias Pfeiffer]

Hi,

On Thu, 17 Mar 2016 16:48:57 -0700 Martin Michlmayr wrote:
> Tobias Pfeiffer <tgpfe...@web.de> [2016-03-17 23:55]:

> > Therefore I considered to write a script that merges
> > these files in the correct date order into some big "all.ledger"
> > file.
>
> I use something similar at the moment instead of include directives
> because the latter doesn't show line numbers of the included files
> properly when there are errors.
>
> I do something like:
>
> cat bank/2016/*.ledger | ledger -f - --sort d print > 2016

Amazing!! Wow, there it is, my to-be-written "merge script"! Great,
thank you very much!

> But regarding transactions in different accounts (e.g. paying your
> credit card bill with your bank account): I use an Assets:Transfer
> account, as someone suggested on this list a few years ago. (I can
> look for a link if you want, but the basic idea is: one transaction
> does: Assets:Bank -> Assets:Transfer; and the other Assets:Transfer ->
> Liabilities:Credit Card).
>
> The other solution would be use to an UUID tag; if two transactions
> have the same UUID, ledger will ignore one.

Wow, that again is a very, very helpful advice, thank you!! Adding a
Assets:Transfer account makes it more difficult to track money flow
between accounts, I think, but that UUID tag is maybe exactly what I
was looking for.

> > - Often it will become necessary to edit transactions by hand, for
> > example, change the clearing state, add or remove tags or edit the
> > payee. I wonder in which files to do this and if it will work
> > correctly with merging.
>
> I only add *new* transactions to my ledger files with my import
> scripts. This way, I can modify old entries without any problems.

I see. That was also the flow that I had in mind, but since I am just
starting with Ledger, I was wondering if at some point I will be
thinking something like "uhm, if only transactions that have properties
A and B would have a different wording/tag/account" and thought about
re-importing the whole data from the past, *not* overwriting changes
that I have made. But then, I should probably think about that problem
at that time ;-)

Thank you very much,
Tobias

[Tobias Pfeiffer]

Hi,

On Thu, 17 Mar 2016 16:48:57 -0700 Martin Michlmayr wrote:

> The other solution would be use to an UUID tag; if two transactions
> have the same UUID, ledger will ignore one.

Uh, for me this only works if the UUID tag is set *above* the
postings... if the UUID tag is set below the postings, the second one
is not ignored.

Tobias

[Alexandre Rademaker]

Can you give an example? Not clear...

Ab.,

----
Alexandre Rademaker
http://arademaker.github.com

[Tobias Pfeiffer]

Hi,

On Mon, 21 Mar 2016 14:59:07 -0300 Alexandre Rademaker wrote:
> > On Thu, 17 Mar 2016 16:48:57 -0700 Martin Michlmayr wrote:
> >> The other solution would be use to an UUID tag; if two transactions
> >> have the same UUID, ledger will ignore one.
> >
> > Uh, for me this only works if the UUID tag is set *above* the
> > postings... if the UUID tag is set below the postings, the second
> > one is not ignored.
>

> Can you give an example? Not clear...

This one:
----
2016-03-23 Test
; uuid: foo
Expenses:A 100 EUR
Assets:B

2016-03-23 Test 2
; uuid: foo
Expenses:A 100 EUR
Assets:B
----
works and shows a balance of "100 EUR" for "Expenses:A".

This one:
----
2016-03-23 Test
Expenses:A 100 EUR
Assets:B
; uuid: foo

2016-03-23 Test 2
Expenses:A 100 EUR
Assets:B
; uuid: foo
----
shows a balance of "200 EUR" for "Expenses:A".

Note that the "uuid" tag is recognized as a tag correctly, as
ledger reg tag uuid=foo
lists the transactions (one for the first example, both for the second
one) properly.

Tobias

[John Wiegley]

>>>>> Tobias Pfeiffer <tgpfe...@web.de> writes:

> works and shows a balance of "100 EUR" for "Expenses:A".

The "uuid" tag is applied to postings, not to transactions. If you specify it
before the first posting, it is applied to all the posting, giving the sense
of "postings belonging to the same UUID, hence transaction".

If you put it after all the postings, I don't believe it has a meaning (I'm
not even entirely sure about that one).

When a "duplicate posting" (a posting with the same UUID) is encountered later
in the file, Ledger ignores it completely, as if it had never seen it. It
doesn't become part of any calculation. This is the reason for the behavior
you've been seeing.

--
John Wiegley GPG fingerprint = 4710 CF98 AF9B 327B B80F
http://newartisans.com 60E1 46C4 BD1A 7AC1 4BA2

[Alexandre Rademaker]

Ohh ! Good to know. I use to write the comments in the end of the last post of the transaction, I was doing wrong for years!! ;-)

Best,

Alexandre Rademaker

http://arademaker.github.com/

http://researcher.ibm.com/person/br-alexrad

[Simon Michael]

My understanding is that a comment (immediately) below the transaction
is considered part of the posting above it, ie a posting comment.