Is there a scriptable way to clear transactions past transactions with future bank statements ?

[Jimi Damon]

Hi,

I routinely will write checks to contractors who perform gardening or other tasks around the house. These aren't big ticket items and sometimes it takes a while for the contractor to cash the checks.  

I'm maintaining long ledger files and I would prefer to have a system that just lets me specify the main ledger file and a converted CSV bank statement in order to consolidate the individual check caching transactions that may take up to 6 months to show up.  

In addition, I prefer scritpability over having to hand edit past transactions to mark them as cleared.  

Is there a preferred way to do this without having to go back and mark old entries in my ledger file as finally clearing ? 

Ideally, I'd like to mark the original Check to the customer as cleared, but then have a deduction from a virtual account that only gets balanced if/ when my CSV parsed Bank statement with the correct check number of amount comes in.

I already have scripts that can parse my CSV file and generate Ledger compatible output to Standard output.  What I want is to be able to take the output and clear a transaction from a long time ago.

Thanks for any suggestions on this front.

[maktak]

Hi Jimi,

I use a regex pattern in the find & replace option of my text editor to clear transactions. This allows me to either clear all transactions ("replace all") in bulk or manually review each one before clicking "replace".

## Uncleared Header

`^(\d{4,}/\d{2,}/\d{2,}) ([^*].*)$`

## Set to cleared

`$1 * $2`

[Jimi Damon]

Thanks  Maktak,

But I don't want to do any editing in my files after my initial entry.  I'm looking for an automated way to do this without manually text editing a file.

-Jimi

[Taylor R Campbell]

I use separate accounts for every multi-step transaction like a cheque
which separately issues and clears. I enter one transaction when I
issue the cheque; then a script converts a CSV bank statement into a
transaction that clears the cheque. For example:

; hand-written ledger entry
account Liabilities:Bank:Cheque:123
2017-01-31 Landlord
Expenses:Rent $750.00
Liabilities:Bank:Cheque:123

; automatically generated by bank2ledger script
2017-02-25 Bank statement
Liabilities:Bank:Cheque:123 $750.00
Assets:Bank

Here's a more detailed description of how it works:

https://mumble.net/~campbell/2017/02/26/ledger/HOWTO-reconcile-cheques

I do the same for other transactions than cheques too. The only
hand-editing occurs when I don't have any kind of transaction id
number printed on the receipt, or when something is amiss -- more
details here:

https://mumble.net/~campbell/2017/02/26/ledger/HOWTO-reconcile-payments

I also put the date in the account names these days so it's easy to
march through pending transactions chronologically and figure out
what's up with them using just `ledger balance pending'.

I also use a similar idea to track outstanding orders, so that `ledger
balance orders' will show all orders I made that have not been shipped
or otherwise fulfilled: enter a receipt when I make an order as one
transaction with a date and order number in the account name, and then
enter the packing list as another transaction clearing the order.

[Jimi Damon]

This is exactly what I was looking for. 

Many thanks for the LIFE hack approach in addition to the technical solution.

 -Jimi

[o1bigtenor]

On Wed, Aug 14, 2019 at 4:04 PM Taylor R Campbell
<campbell+...@mumble.net> wrote:
>
> I use separate accounts for every multi-step transaction like a cheque
> which separately issues and clears. I enter one transaction when I
> issue the cheque; then a script converts a CSV bank statement into a
> transaction that clears the cheque. For example:
>
>

Very interesting - - - - after just a quick look I'm thinking similar systems
could be used in a business application for invoices.
This means that AR and AP could be both covered.
Now for someone who has already written something that could be
used to monitor things so that aging would be just as simple.

(Singing a goofy tune to the words of 'Ain't ledger so wonderful . . . ' )

[Martin Michlmayr]

* Taylor R Campbell <campbell+...@mumble.net> [2019-08-14 21:04]:

> ; hand-written ledger entry
> account Liabilities:Bank:Cheque:123
> 2017-01-31 Landlord
> Expenses:Rent $750.00
> Liabilities:Bank:Cheque:123
>
> ; automatically generated by bank2ledger script
> 2017-02-25 Bank statement
> Liabilities:Bank:Cheque:123 $750.00
> Assets:Bank

This works fine for personal accounts where you can do whatever works
best for you instead of following official accounting practices.

This won't work for a business, however. Cheques are treated as
immediate cash expenses: when you write a check, the money immediately
gets deducted (in your books), even though in reality it only gets
deducted from your bank account when the person deposits the check.

That's (one reason) why businesses do bank reconciliations at the end
of the month where they compare their books with their actual bank
balances and account for the differences (undeposited checks get added
to the balance from your book to match the bank balance).

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

[Jimi Damon]

So according to your commentary Martin , would you just change it to 

; file: check_writing.ledger

2000/01/01 * Initial Balance

    Assets:Cash      $1000.00

    Equity:Initial

2019/09/12 * Landlord

    Expenses:Rent    $500.00

    Assets:Cash:Cheque:124

2019/10/11 * Landlord Cashed the check

    Assets:Cash:Checque:124   $500.00

    Assets:Cash

I'm not sure how what you describe is different than these two reports

One is at the end of September

ledger -f check_writing.ledger -e 2019/09/30 bal ^assets:cash$

           $1000.00  Assets:Cash

The other is at the end of next month

ledger -f check_writing.ledger  -e 2019/10/31 bal ^assets:cash$

             $500.00  Assets:Cash

Thanks for any clarifications you can give.

On Saturday, August 31, 2019 at 11:59:20 AM UTC-7, Martin Michlmayr wrote:

* Taylor R Campbell <camp...@mumble.net> [2019-08-14 21:04]:

[psionl0]

This is what the "cleared" field is mean to do. I would initially write the entries as follows:

; file: check_writing.ledger

2000/01/01 * Initial Balance

    Bank:Checking Account      $1000.00

    Equity:Initial

2019/09/12  Landlord

    Expenses:Rent                    $500.00

    Bank:Checking Account:Cheque:124

When the cheque clears, you mark the entry with an asterisk.

If you don't want to alter the initial file then you could do the following:

2000/01/01 * Initial Balance

    Bank:Checking Account      $1000.00

    Equity:Initial

2019/09/12  Landlord

    Expenses:Rent                    $500.00

    Bank:Checking Account:Cheque:124

2019/10/11  Landlord

    Expenses:Rent                   -$500.00

    Bank:Checking Account:Cheque:124

2019/10/11 *  Landlord ; Cheque cleared

    Expenses:Rent                    $500.00

    Bank:Checking Account:Cheque:124

Using the -C or -U option (or neither) in your reports will give you an accurate picture of the state of your finances at all times.

[Jimi Damon]

Clearing that way is really cumbersome, isn't nearly as scriptable as the method mentioned and really doesn't jive well with modern day Cryptocurrency / block chain solutions.

[Craig Earls]

One thing to keep in mind is that, by design, ledger-cli NEVER alters the input file. 

--

---
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/ledger-cli/5dcf74ac-4798-4693-932c-e8fd428b8147%40googlegroups.com.

--

Craig, Corona De Tucson, AZ

enderw88.wordpress.com

[psionl0]

On Saturday, September 14, 2019 at 1:29:45 AM UTC+8, Jimi Damon wrote:

Clearing that way is really cumbersome, isn't nearly as scriptable as the method mentioned and really doesn't jive well with modern day Cryptocurrency / block chain solutions.

Just adding the asterisk would be much simpler but you wish to keep the original source file immutable. This way is at least consistent with business practices.

I don't understand the cryptocurrency reference. If you are paying for something using a crypto then the payment goes pretty much instantaneously (unless you fail to pay a transaction fee) and the entry can be cleared at the time you make it. If you are buying or selling cryptocurrencies then you would account for it in the same way that you would account for any other good or service.

[Taylor R Campbell]

> Date: Sat, 31 Aug 2019 20:59:16 +0200
> From: Martin Michlmayr <t...@cyrius.com>

>
> * Taylor R Campbell <campbell+...@mumble.net> [2019-08-14 21:04]:
> > ; hand-written ledger entry
> > account Liabilities:Bank:Cheque:123
> > 2017-01-31 Landlord
> > Expenses:Rent $750.00
> > Liabilities:Bank:Cheque:123
> >
> > ; automatically generated by bank2ledger script
> > 2017-02-25 Bank statement
> > Liabilities:Bank:Cheque:123 $750.00
> > Assets:Bank
>
> This works fine for personal accounts where you can do whatever works
> best for you instead of following official accounting practices.
>
> This won't work for a business, however. Cheques are treated as
> immediate cash expenses: when you write a check, the money immediately
> gets deducted (in your books), even though in reality it only gets
> deducted from your bank account when the person deposits the check.

Can you expand on the distinction you're drawing here?

If you mean that you need the account tree Assets:Bank to reflect your
current bank balance, you could name it Assets:Bank:Cheque:123
instead, so that `ledger balance --depth 2 Assets:Bank' will report
the balance with outstanding cheques deducted.

2017-01-01 Opening Balances
Assets:Bank:Balance $10,000.00
Equity:Opening Balances

; hand-written when you write the cheque
account Assets:Bank:Cheque:123

2017-01-31 Landlord
Expenses:Rent $750.00

Assets:Bank:Cheque:123

; Show the available balance:
;
; % ledger balance --depth 2 Assets:Bank
; $9,250.00 Assets:Bank
;
; Show the balance of the last bank statement together with the
; outstanding cheques:
;
; % ledger balance Assets:Bank
; $9,250.00 Assets:Bank
; $10,100.00 Balance
; $-750.00 Cheque:123
; --------------------
; $9,250.00


; automatically generated by bank2ledger
2017-02-25 Bank statement
Assets:Bank:Cheque:123 $750.00
Assets:Bank:Balance

; % ledger balance --depth 2 Assets:Bank
; $9,250.00 Assets:Bank
; % ledger balance Assets:Bank
; $9,250.00 Assets:Bank

But then you have an `asset' account with negative balance, until the
cheque clears. It's not obvious to me whether it's better to label
this as an asset so it stays in `Assets:Bank', or whether to label it
as a liabilitiy since it's negative. Either way `ledger balance bank'
reports the available balance in this system.

But maybe I misunderstood what your objection to this system is.

> That's (one reason) why businesses do bank reconciliations at the end
> of the month where they compare their books with their actual bank
> balances and account for the differences (undeposited checks get added
> to the balance from your book to match the bank balance).

That seems to be what I was describing with automatically ingesting
the bank's statement using a bank2ledger script? After ingesting the
bank statement, running `ledger balance bank' will show all of the
outstanding cheques as line items and the total will sum to what you
have available in the bank.

[o1bigtenor]

On Fri, Sep 13, 2019 at 10:33 PM Taylor R Campbell
<campbell+...@mumble.net> wrote:
>
> > Date: Sat, 31 Aug 2019 20:59:16 +0200
> > From: Martin Michlmayr <t...@cyrius.com>
> >
> > * Taylor R Campbell <campbell+...@mumble.net> [2019-08-14 21:04]:
> > > ; hand-written ledger entry
> > > account Liabilities:Bank:Cheque:123
> > > 2017-01-31 Landlord
> > > Expenses:Rent $750.00
> > > Liabilities:Bank:Cheque:123
> > >
> > > ; automatically generated by bank2ledger script
> > > 2017-02-25 Bank statement
> > > Liabilities:Bank:Cheque:123 $750.00
> > > Assets:Bank
> >
> > This works fine for personal accounts where you can do whatever works
> > best for you instead of following official accounting practices.
> >
> > This won't work for a business, however. Cheques are treated as
> > immediate cash expenses: when you write a check, the money immediately
> > gets deducted (in your books), even though in reality it only gets
> > deducted from your bank account when the person deposits the check.
>
> Can you expand on the distinction you're drawing here?
>

I can give some clues here.
Almost all businesses are required to use an accrual accounting system.
Only individuals are allowed to use a cash system.
The distinction can be subtle but is crucial to entities like your taxation
authorities.

Some clarification using examples.

You take possession of an item but pay for it 2 weeks later and that happens
to be in another calendar year.
In a cash based accounting system that item is deemed to have been
purchased in that other year. This means that the expense is logged in the
other year for taxation purposes. Accrual accounting would have that purchase
logged into the year that the item was taken possession in.
A business can 'sell' an item to customer x where there is a legal
invoice (bill of
sale or whatever document is used) but the customer is going to take possession
of the item at some later date (maybe even 5 years later (extreme example!!))
where the customer pays upon receipt.
The business logs the sale of the item and the resulting income - - - - even
though no money has changed hands. (Its a great technique for making a
business look really profitable.)

The distinctions as to when something happens is important in an accrual
accounting system (almost all businesses are required to use this!!!) but in
a cash accounting system - - - -well - - - - everything is booked as to when
the 'cash' changed hands.

HTH

[Taylor R Campbell]

> Date: Sat, 14 Sep 2019 07:07:36 -0500
> From: o1bigtenor <o1big...@gmail.com>

>
> On Fri, Sep 13, 2019 at 10:33 PM Taylor R Campbell
> <campbell+...@mumble.net> wrote:
> >
> > > Date: Sat, 31 Aug 2019 20:59:16 +0200
> > > From: Martin Michlmayr <t...@cyrius.com>
> > >

> > > This won't work for a business, however. Cheques are treated as
> > > immediate cash expenses: when you write a check, the money immediately
> > > gets deducted (in your books), even though in reality it only gets
> > > deducted from your bank account when the person deposits the check.
> >
> > Can you expand on the distinction you're drawing here?
>
> I can give some clues here.
> Almost all businesses are required to use an accrual accounting system.
> Only individuals are allowed to use a cash system.
> The distinction can be subtle but is crucial to entities like your taxation
> authorities.
>
> Some clarification using examples.
>
> You take possession of an item but pay for it 2 weeks later and that happens
> to be in another calendar year.
> In a cash based accounting system that item is deemed to have been
> purchased in that other year. This means that the expense is logged in the
> other year for taxation purposes. Accrual accounting would have that purchase
> logged into the year that the item was taken possession in.

I don't follow how this is related to the distinction Martin was
drawing. Here's an accrual-based example where taking possession of
an item (`expense') happens in one year, the payment for the item
happens in another year, and the cheque clears separately from the
payment when you get your bank statement a month later:

; Order a batch of widgets.
2018-12-30 Acme Widgets
Expenses:Widgets 100 EUR
Liabilities:Acme Widgets:Order #12837

; Write a cheque for the batch of widgets.
2019-01-05 Acme Widgets
Liabilities:Acme Widgets:Order #12837 100 EUR
Assets:Bank:Cheque:123 ;or Liabilities:Bank:Cheque:123

; Reconcile the bank statement.
2019-02-01 Bank statement
Assets:Bank:Cheque:123 100 EUR
Assets:Bank:Balance

Can you expand on how _notating each cheque in its own ledger account_
so that you can have separate dated records for writing the cheque and
for clearing it gets in the way of business accounting?

[o1bigtenor]

On Sat, Sep 14, 2019 at 8:42 AM Taylor R Campbell

Sorry - - - I never said that anything got in the way of anything else in the
business accounting.

I can understand why someone, a business owner, would want ALL THREE
pieces of information.
Part of the challenge is that your example is showing the distinction between
three different viewpoints.
The bank doesn't care when anything is happening. What is important to the
bank is when the 'instrument' is drawn (check is cashed) and that's
it. (The bank
does sorta care as its a right royal pita dealing with checks after a
stale date of
over 3 months today.)
Acme Widgets only cares that it gets its money.
Bookkeeper cares that the cleared check matches an expenditure.

Reading back to the beginning of the thread - - - the desire was to
have some way
of tracking all points of reference (previously mentioned 3 + that of
the check writer)
easily. I don't really know of an 'easy' way - - - just know I need to.

Sorry for any confusion or misunderstanding caused!!

Regards

[Jimi Damon]

It's actually pretty easy to reconcile two statements if they can both agree upon a common UUID ( or something similar like Check number) for the transaction that occurred between them both.

This is what I meant by the "Cryptocurrency / block chain" solution where each transaction has a unique identifier. 

Each transaction would be sacrosanct and uniquely named so there would be no need to go back and mark either transaction as "cleared".  

That suits my needs better than having to manually go through and hand edit my ledger files after the fact.   I know that this kind of goes against the default standard of accounting, but I feel like that is going to be replaced with more of these electronic transactions.

[psionl0]

On Monday, September 16, 2019 at 4:32:10 AM UTC+8, Jimi Damon wrote:

It's actually pretty easy to reconcile two statements if they can both agree upon a common UUID ( or something similar like Check number) for the transaction that occurred between them both.

This is what I meant by the "Cryptocurrency / block chain" solution where each transaction has a unique identifier. 

Each transaction would be sacrosanct and uniquely named so there would be no need to go back and mark either transaction as "cleared".  

That suits my needs better than having to manually go through and hand edit my ledger files after the fact.   I know that this kind of goes against the default standard of accounting, but I feel like that is going to be replaced with more of these electronic transactions.

Like I said earlier, Any bitcoin payment you make you would immediately mark as cleared at the time you make the entry (and you probably wouldn't make the entry until you have seen a couple of confirmations). Similarly, you are not going to enter any bitcoin receipts in your ledger file until you see them in your wallet so they can also be marked as cleared at the time you make the entry.

It is only when you have to wait for bank statements that there is a time delay between making the entry and clearing it.

[psionl0]

On Saturday, September 14, 2019 at 11:33:48 AM UTC+8, Taylor R Campbell wrote:

But then you have an `asset' account with negative balance, until the
cheque clears.  It's not obvious to me whether it's better to label
this as an asset so it stays in `Assets:Bank', or whether to label it
as a liabilitiy since it's negative.  Either way `ledger balance bank'
reports the available balance in this system.

There is nothing wrong with having an asset account with a negative balance - especially if it is a subcategory that is subtracted from the parent account.

[Jimi Damon]

On 9/14/19 6:54 AM, o1bigtenor wrote:
> On Sat, Sep 14, 2019 at 8:42 AM Taylor R Campbell
>
>
>
>
>

> <campbell+...@mumble.net> wrote:; Order a batch of widgets.

> 2018-12-30 Acme Widgets
> Expenses:Widgets 100 EUR
> Liabilities:Acme Widgets:Order #12837
>
> ; Write a cheque for the batch of widgets.
> 2019-01-05 Acme Widgets
> Liabilities:Acme Widgets:Order #12837 100 EUR
> Assets:Bank:Cheque:123 ;or Liabilities:Bank:Cheque:123
>
> ; Reconcile the bank statement.
> 2019-02-01 Bank statement
> Assets:Bank:Cheque:123 100 EUR
> Assets:Bank:Balance
>>
>>

>> I can understand why someone, a business owner, would want ALL THREE
>> pieces of information.
>> Part of the challenge is that your example is showing the distinction between
>> three different viewpoints.
>> The bank doesn't care when anything is happening. What is important to the
>> bank is when the 'instrument' is drawn (check is cashed) and that's
>> it. (The bank
>> does sorta care as its a right royal pita dealing with checks after a
>> stale date of
>> over 3 months today.)
>> Acme Widgets only cares that it gets its money.
>> Bookkeeper cares that the cleared check matches an expenditure.
>>
>> Reading back to the beginning of the thread - - - the desire was to
>> have some way
>> of tracking all points of reference (previously mentioned 3 + that of
>> the check writer)
>> easily. I don't really know of an 'easy' way - - - just know I need to.
>>
>> Sorry for any confusion or misunderstanding caused!!
>>
>> Regards

Taylor answered this perfectly. Using ledger plus the correct command
line filtering , you get everything that you described

1. Bank's perspective

2. When the check was written

3. When the check cleared

The original post was about having the information in a format so that
"one" didn't have to edit ledger files to put in an Asterisks (*)  to
mark transactions as cleared.