Verify account balance from bank statement

[Ben Finney]

Howdy all,

My bank's statements of account show opening and closing balance. How
can I enter those at a specific point in time in the Ledger input, such
that Ledger can verify them when processing the data?

The documentation speaks of the ‘assert’ and ‘check’ directives, but
doesn't give examples of how to use them to make a verifiable assertion
about the present balance of an account. Are they a good tool to use for
this?

There is some mention of asserting a balance on a specific transaction.
That's not quite what I need; I would rather have the balance verified
separate from an existing transaction, but rather as its own point in
time, between transactions.

What conventions are there in Ledger practice for doing this?

--
\ “Under democracy one party always devotes its chief energies to |
`\ trying to prove that the other party is unfit to rule — and |
_o__) both commonly succeed, and are right.” —Henry L. Mencken |
Ben Finney

[John Wiegley]

>>>>> Ben Finney <ben+l...@benfinney.id.au> writes:

> My bank's statements of account show opening and closing balance. How
> can I enter those at a specific point in time in the Ledger input, such
> that Ledger can verify them when processing the data?

You can use a single line balance assertion:

2006/01/17 Confirm balance at this point
[Liabilities:MasterCard] = $-1,731.72

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

[Ben Finney]

"John Wiegley" <jo...@newartisans.com> writes:

> >>>>> Ben Finney <ben+l...@benfinney.id.au> writes:
>
> > How can I enter [the account balance] at a specific point in time in

> > the Ledger input, such that Ledger can verify them when processing
> > the data?
>
> You can use a single line balance assertion:
>
> 2006/01/17 Confirm balance at this point
> [Liabilities:MasterCard] = $-1,731.72

Thank you. That works in this simple case:

=====
$ cat ./lorem.ledger

2015-01-01 * Opening balance
Assets:Bank:Savings $ 1000
Equity:Balances

2015-01-04 * Balance brought forward
[Assets:Bank:Savings] = $ 999999

2015-01-04 * Cheese
Expenses:Groceries $ 17
Assets:Bank:Savings

$ ledger -f ./lorem.ledger bal

While parsing file "./lorem.ledger", line 10:
While balancing transaction from "./lorem.ledger", lines 9-10:
> 2015-01-04 * Balance brought forward
> [Assets:Bank:Savings] = $ 999999
Unbalanced remainder is:
$ 998999
Amount to balance against:
$ 998999
Error: Transaction does not balance

=====


But the assertion doesn't work when I have a “default” account set:

=====
$ cat ./lorem.ledger

account Assets:Bank:Lorem Ipsum:Everyday Savings
alias Assets:Bank:Savings
default

2015-01-01 * Opening balance
Assets:Bank:Savings $ 1000
Equity:Balances

2015-01-04 * Balance brought forward
[Assets:Bank:Savings] = $ 999999

2015-01-04 * Cheese
Expenses:Groceries $ 17
Assets:Bank:Savings

$ ledger -f ./lorem.ledger bal

$ 983 Assets:Bank:Lorem Ipsum:Everyday Savings
$ -1000 Equity:Balances
$ 17 Expenses:Groceries
--------------------
0
=====

With the default account set, Ledger fills in what is needed to make the
assertion pass. This results in nonsense transactions:

=====
$ ledger ./lorem.ledger reg

2015-01-01 Opening balance ..:Lo:Everyday Savings $ 1000 $ 1000
Equity:Balances $ -1000 0
2015-01-04 Balance brought fo.. ..Lo:Everyday Savings] $ 998999 $ 998999
..:Lo:Everyday Savings $ -998999 0
2015-01-04 Cheese Expenses:Groceries $ 17 $ 17
..:Lo:Everyday Savings $ -17 0
=====

So, how can I make Ledger both create the needed postings on a default
account (as normal for the “default”); *and* make falsifiable assertions
about the balance at a specific timestamop?

--
\ “Any fool can write code that a computer can understand. Good |
`\ programmers write code that humans can understand.” —Martin |
_o__) Fowler, _Refactoring_, 2000 |
Ben Finney

[Simon Michael]

How about writing a explicit 0 posting amount.

[John Wiegley]

>>>>> Simon Michael <si...@joyful.com> writes:

> How about writing a explicit 0 posting amount.

Right, you can balance the assertion amount against 0.

[Ben Finney]

"John Wiegley" <jo...@newartisans.com> writes:

> Right, you can balance the assertion amount against 0.

Can you show how you'd change the example I gave, to do that?

--
\ “Dyslexia means never having to say that you're ysror.” |
`\ —anonymous |
_o__) |
Ben Finney

[Chris Bennett]

Hi Ben,

I do this for every single statement I reconcile. I'll include some
things I do in my workflow in case they are of interest to you.

I use a ledger file per statement and then include from a parent
ledger file.

Each statement ledger file begins with:

;=====================================================================
; 20160101-20160201
; Statement: cheque-20160101-20160201.pdf
2016/01/01 Assets:Bank:Cheque opening balance
Assets:Bank:Cheque $0.00 = $1000.00
Equity::Dummy

and ends with:

2016/02/01 Assets:Bank:Cheque closing balance
Assets:Bank:Cheque $0.00 = $2000.00
Equity::Dummy

with entries in between. I usually place these assertions in the
file before doing the import, so the beginning of my reconciling is in
an error condition (ledger will complain that things don't balance,
and will tell you how many $$ I am off by), and once entries are
accurate, ledger will be happy.

Hope that helps - I've found this style of entry management &
reconciling extremely useful for keeping data accurate & quickly
finding introduced errors if I ever make amendments.

Regards,

Chris

[Martin Blais]

Two notes:

1. AFAIK in Ledger this will only work if you keep your entries in sorted order for each account, which I think is not always possible (think of a transaction which posts to two accounts each of which has balance assertions, e.g. a transfer between checking and savings account). I think there may be a way out of this issue by declaring transactions with a single posting when such transfers occur. Ledger's assertions are computed in file order, not in date order.

2. The semantics that you describe you desire are exactly those which are defined in Beancount: a distinct, dedicated directive type exists for declaring an assertion (it's called "balance"). It is also processed in date order -- the order of declarations in an input file is kept irrelevant on purpose. It is defined to apply at the beginning of the day on the directive's date.

--

---
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.

[Ben Finney]

Martin Blais <bl...@furius.ca> writes:

> 1. AFAIK in Ledger this will only work if you keep your entries in
> sorted order for each account, which I think is not always possible
> (think of a transaction which posts to two accounts each of which has
> balance assertions, e.g. a transfer between checking and savings
> account).

The transaction will not be half-done at the point of the balance
assertion. Any other transactions are either entirely done or have not
appeared yet.

> 2. The semantics that you describe you desire are exactly those which
> are defined in Beancount

Not relevant, because I'm using Ledger.

--
\ “I bet one legend that keeps recurring throughout history, in |
`\ every culture, is the story of Popeye.” —Jack Handey |
_o__) |
Ben Finney

[Ben Finney]

Chris Bennett <ch...@ceegeebee.com> writes:

> Each statement ledger file begins with:
>
> ;=====================================================================
> ; 20160101-20160201
> ; Statement: cheque-20160101-20160201.pdf
> 2016/01/01 Assets:Bank:Cheque opening balance
> Assets:Bank:Cheque $0.00 = $1000.00
> Equity::Dummy
>
> and ends with:
>
> 2016/02/01 Assets:Bank:Cheque closing balance
> Assets:Bank:Cheque $0.00 = $2000.00
> Equity::Dummy

That looks good, I will try it.

> Hope that helps

Thanks for the suggestion.

--
\ “Education is learning what you didn't even know you didn't |
`\ know.” —Daniel J. Boorstin, historian, 1914–2004 |
_o__) |
Ben Finney

[Martin Blais]

On Mon, May 30, 2016 at 6:45 PM, Ben Finney <ben+l...@benfinney.id.au> wrote:

Martin Blais <bl...@furius.ca> writes:

> 1. AFAIK in Ledger this will only work if you keep your entries in
> sorted order for each account, which I think is not always possible
> (think of a transaction which posts to two accounts each of which has
> balance assertions, e.g. a transfer between checking and savings
> account).

The transaction will not be half-done at the point of the balance
assertion. Any other transactions are either entirely done or have not
appeared yet.

I don't understand your response. 

It also sounds you didn't understand mine either.

Let's see. 

Here's what I mean:

;;----- Credit Card Section

2016-03-01 *

  Expenses:Clothing     35.46 USD

  Liabilities:CreditCard    -35.46 USD

2016-03-15 * "Payment"

  Assets:Checking     -984.32 USD

  Liabilities:CreditCard 984.32 USD

2016-03-16 * "Balance check"

  Liabilities:CreditCard       $0.00 = $0.00 USD

  Equity:Dummy

;;----- Checking Account Section

...

2016-03-05 * "Balance check"

  Assets:Checking       $0.00 = $1234.56 USD

  Equity:Dummy

...

The problem here is that if you organize your transactions by account as in the above, some of the postings will have applied before their due date. Specifically, the problem is that Payment transaction on 3/15 posts to the checking account _before_ the balance check on 3/5, because Ledger ignores the dates of transactions, processes them in file order. Thus the balance check will incorrectly fail.

If only one of the two accounts has balance checks, you could intersperse the balance checks in that account's section and you're okay. The problem is generally most accounts are connected.

Another option is to order all the transactions from related groups of accounts together. In the example, you'd order your checking account and credit card transactions in a single section.

Finally, I think it's possible to split the transactions to have only a single posting each. You have to use a clearing account, something like this:

2016-03-01 *

  Expenses:Clothing     35.46 USD

  Liabilities:CreditCard    -35.46 USD

2016-03-15 * "Payment"

  Liabilities:CreditCard   984.32 USD

  Equity:Clearing

2016-03-16 * "Balance check"

  Liabilities:CreditCard    0.00 = 0.00 USD

  Equity:Dummy

;;----- Checking Account Section                                                                                                                                                                                                  

;; ...                                                                                                                                                                                                                            

2016-03-05 * "Balance check"

  Assets:Checking       0.00 = 2000.00 USD

  Equity:Dummy

2016-03-15 * "Payment"

  Equity:Clearing

  Assets:Checking     -984.32 USD

I don't know how Ledger users generally deal with this. It would seem to me a huge annoyance that I should have to even think about the ordering of my input file.

But this can be fixed: someone could implement a second processing pass in Ledger and accumulate the balances in date order to implement dated assertions.

[Chris Bennett]

Thanks Martin

1) I manage to maintain sorted order for all transactions of bank
accounts, and my balance assertion at open is at top, balance
assertion at close is at bottom. Works well for my workflow.

2) I was aware of the beancount balance assertion and I'm very eager
to move my data across. I suppose like a lot of us on here, we are
full time doing something else, and part time bookkeepers :) So it's
just a matter of jumping across. Maybe I'll consider at end of
financial year (July 1st in Australia).

Regards,

Chris

[Chris Bennett]

> Finally, I think it's possible to split the transactions to have only a
> single posting each. You have to use a clearing account, something like
> this:

This is exactly what I do - automatically during bank import since the
transfers are detectable by my perl script and the output does the
right thing without thinking about it.

I forgot to mention to Ben that that is how I manage my accounts - it
is indeed very relevant for balance assertions within and at bottom of
a ledger file if there are other entries that manipulate the relevant
account.

I've found the clearing method very useful - particularly that you can
can reconcile a subset of your accounts and if any transfers occur
between accounts, you don't break the assertions (but you'll have
money 'sitting' in a clearing account until the related account is
reconciled).

I also use a clearing account per actual destination account, so if a
transfer is only half reconciled, it is clear what account money is
destined to be going into.

Regards,

Chris

[Martin Blais]

Another option would be to add this as an option to Ledger. I don't think it would be very difficult.

It would involve:

- Adding a new option to enable this behavior (you wouldn't change the default)

- When this option is specified, ignore balance assertions on the first pass

- During the first pass, create a list of (date, posting-ptr)

- If the option is enabled... after the first pass:

  - Sort that list by date

  - Use it to process the posting balances in date order, just for balance assertions.

I bet it wouldn't make Ledger more than 5% slower, and only when the option is specified. 

It would, however, need keep the posting objects in memory through the end of the first pass.

(Warning: I haven't looked at the source code.)

[Ben Finney]

Martin Blais <bl...@furius.ca> writes:

> The problem here is that if you organize your transactions by account
> as in the above, some of the postings will have applied before their
> due date.

Right. As was said before (by you?), Ledger processes transactions in
order from the input. So I expect to have transactions with the same
date ordered in the sequence they are to occur on that date.

I would consider the situation you describe above to be a mistake in the
input, so I don't expect Ledger to handle it correctly.

> Specifically, the problem is that Payment transaction on 3/15 posts to
> the checking account _before_ the balance check on 3/5, because Ledger
> ignores the dates of transactions, processes them in file order. Thus
> the balance check will incorrectly fail.

Yes. So it's my responsibility to get the transactions such that they
occur in the right sequence.

If I care to put a date+time on the entry, then a tool could even do
that for me by ordering the data. For now I'm fine with ordering them
manually.

> But this can be fixed: someone could implement a second processing
> pass in Ledger and accumulate the balances in date order to implement
> dated assertions.

Which wouldn't help place a balance assertion that is meant to fall
between two specific transactions with the same date stamp. If the
information isn't in the input, I don't expect Ledger to invent it :-)

--
\ “It is wrong to think that the task of physics is to find out |
`\ how nature *is*. Physics concerns what we can *say* about |
_o__) nature…” —Niels Bohr |
Ben Finney

[Simon Michael]

On 6/2/16 4:17 AM, Ben Finney wrote:
> Martin Blais <bl...@furius.ca> writes:

>> But this can be fixed: someone could implement a second processing
>> pass in Ledger and accumulate the balances in date order to implement
>> dated assertions.
>
> Which wouldn't help place a balance assertion that is meant to fall
> between two specific transactions with the same date stamp. If the
> information isn't in the input, I don't expect Ledger to invent it :-)

The solution, of course, is to order transactions first by date and
second by parse order, like hledger. :-)

-Simon

But seriously.. we run through this discussion every few months.. how to
break the cycle and create a new outcome ? Would someone like to create
a FAQ ?

[Martin Blais]

On Thu, Jun 2, 2016 at 5:51 PM, Simon Michael <si...@joyful.com> wrote:

On 6/2/16 4:17 AM, Ben Finney wrote:

Martin Blais <bl...@furius.ca> writes:

But this can be fixed: someone could implement a second processing
pass in Ledger and accumulate the balances in date order to implement
dated assertions.

Which wouldn't help place a balance assertion that is meant to fall
between two specific transactions with the same date stamp. If the
information isn't in the input, I don't expect Ledger to invent it :-)

The solution, of course, is to order transactions first by date and second by parse order, like hledger. :-)

Didn't realize you were forcing sorted order as well.

(BTW Beancount also uses the line number as secondary key, mainly so that the sorting is stable.)

[Martin Blais]

On Thu, Jun 2, 2016 at 7:17 AM, Ben Finney <ben+l...@benfinney.id.au> wrote:

Martin Blais <bl...@furius.ca> writes:

> The problem here is that if you organize your transactions by account
> as in the above, some of the postings will have applied before their
> due date.

Right. As was said before (by you?), Ledger processes transactions in
order from the input. So I expect to have transactions with the same
date ordered in the sequence they are to occur on that date.

I would consider the situation you describe above to be a mistake in the
input, so I don't expect Ledger to handle it correctly.

You're missing the point. The point is that if you arrange transactions by account within a file, it is virtually impossible to use balance assertions unless you always post them to a clearing account in order to avoid out-of-order postings to another section's account. This is what the example I posted is meant to demonstrate: the transactions within each section are correctly ordered and it will fail.

And because all the accounts are at some point or other connected to each other via some transfer, ultimately you would end up having to order the entire set of transactions by date, in one big list. This would work, but I'm sure you'll grant that this would be hugely inconvenient to update, and it sort-of invalidates much of the benefits of using a text file as input.

> Specifically, the problem is that Payment transaction on 3/15 posts to
> the checking account _before_ the balance check on 3/5, because Ledger
> ignores the dates of transactions, processes them in file order. Thus
> the balance check will incorrectly fail.

Yes. So it's my responsibility to get the transactions such that they
occur in the right sequence.

If I care to put a date+time on the entry, then a tool could even do
that for me by ordering the data. For now I'm fine with ordering them
manually.

As I proved above, you just won't be able to do it.

 

> But this can be fixed: someone could implement a second processing
> pass in Ledger and accumulate the balances in date order to implement
> dated assertions.

Which wouldn't help place a balance assertion that is meant to fall
between two specific transactions with the same date stamp. If the
information isn't in the input, I don't expect Ledger to invent it :-)

For Beancount that would indeed be a problem in theory, but in practice one rarely has intra-day balance assertions, and you can fix them manually (or ignore an imported one every now and then). Ledger has date-time syntax, so it could handle intra-day balance assertions (if there was reordering of transactions before processing).

[John Wiegley]

>>>>> Martin Blais <bl...@furius.ca> writes:

> I bet it wouldn't make Ledger more than 5% slower, and only when the option
> is specified. It would, however, need keep the posting objects in memory
> through the end of the first pass. (Warning: I haven't looked at the source
> code.)

If those are the only caveats, I'd support such a change.