automated strategies for reconciling transactions that span multiple accounts

[Eli Flanagan]

I am trying to completely automate CSV import following Dmitry's method here: https://github.com/adept/full-fledged-hledger
I find hledger's CSV import rules file work fairly well, but there are a few cases it does not account well for:

1. transactions that span multiple accounts
1. Given the financial institutions I use, transferring 5 USD from Savings1 to Checking1 introduces 2 entries in the underlying exported csvs. 1 entry per account CSV export.
   
2. transactions that have the same description, but a different amount

how do you all try and solve #1? For 1 and 2, I am thinking that preprocessing the CSV's before importing them would be easiest. For example, removing one entry from one of the exported CSV files.
In the case of 2, you could append to the CSV line's description so hledger import rules could uniquely match.

I see this issue to make importing CSV better, perhaps some of this question overlaps:
https://github.com/simonmichael/hledger/issues/507

For the record, Nicolas just posted this project that seems to offer more advanced import rules; https://github.com/johannesgerer/buchhaltung#match-accounts
I haven't tried it out, but could address my issue.
As usual, thanks for the ideas and feedback in advance!

[Dmitry Astapov]

For 1. you write the rules to produce two transactions: one that transfers 5 USD from Savings1 to synthetic Transfers account, and a second one that transfers from Transfers to Checking1. As an added bonus, you can check that Transfers is zero each time you reconcile and you do not have unfinished transfers.

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

--

D. Astapov

[Simon Michael]

On Mar 19, 2018, at 11:51 PM, Eli Flanagan <e...@typedspace.com> wrote:

I am trying to completely automate CSV import following Dmitry's method here: https://github.com/adept/full-fledged-hledger
I find hledger's CSV import rules file work fairly well, but there are a few cases it does not account well for:

1. transactions that span multiple accounts
1. Given the financial institutions I use, transferring 5 USD from Savings1 to Checking1 introduces 2 entries in the underlying exported csvs. 1 entry per account CSV export.
   

+1 to what Dmitry said: use a temporary third account to break up the transfer into two steps, so there is just one transaction affecting Checking and one affecting Savings.

Or the semi-manual approach.Checking transactions are primary and most automated, Savings transactions (typically much fewer) are more manual. So when importing savings transactions I watch for already-recorded transfer transactions and just delete those. (I "hledger import" to new.journal, and manually cut/paste from there to 2018.journal.

2. transactions that have the same description, but a different amount

how do you all try and solve #1? For 1 and 2, I am thinking that preprocessing the CSV's before importing them would be easiest. For example, removing one entry from one of the exported CSV files.
In the case of 2, you could append to the CSV line's description so hledger import rules could uniquely match.

Could you give an example of 2 ? I'm not sure why that's a problem.

[Eli Flanagan]

Ah, this temporary account solution works perfectly; thanks folks!
For point 2, it may be a moot issue given how I can use the temporary or virtual accounts.
Given Checking1, the exported CSV may contain 2 transactions with an identical discription of the financial institution. Though each transaction applies to a different account at that institution. It seems that exporting the corresponding 2 other account histories could possibly resolve that.
Does that help clarify? I see the ambiguity in the way I phrased it.
Or I am considering preprocessing the CSV to make the description more meaningful.

[Simon Michael]

Nope, I'm still not understanding. :) Anybody else ? 

Your CSV sounds a bit different to mine. Probably a concrete example would make it crystal clear.

--
You received this message because you are subscribed to the Google Groups "hledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+u...@googlegroups.com.

[Dmitry Astapov]

Hello,

It would be interesting to hear why you describe it as "temporary solution". In my view, it is not in any way temporary and this is basically The Way To Do It in a double-entry accounting system.

Your item #2 sounds like "more of the same". Suppose that I send money from my Current account to N accounts at other financial institution, lets say I use it to fund PayPal accounts for myself and my wife. In Current account statement I will see just "PayPal" without any indication of whether this was for myself or my wife, and I definitely do not want to spend my time going over them manually and attributing them to PayPal accounts based on my vague recollection. Well, not to worry : I would import these Current account transactions as simply going from "Current" to "PayPal:funding".

Then, later on, when I would import my PayPal statements (which could happen separately and later - even several years later if I do not care about PayPal originally that much), and these PayPal statements would have pay-in transactions. Well, I would import them as coming from "PayPal:funding", and lo and behold - everything would balance out and if I done everything right my PayPal:funding is now empty and all money sent to PayPal are accounted for and distributed between PayPal accounts of myself and my wife.

Does this help you?

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "hledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

D. Astapov

[Simon Michael]

> On Mar 21, 2018, at 10:37 AM, Dmitry Astapov <dast...@gmail.com> wrote:
> It would be interesting to hear why you describe it as "temporary solution". In my view, it is not in any way temporary and this is basically The Way To Do It in a double-entry accounting system.

I think "temporary" came from me - I meant that the third/transfer account holds funds temporarily, and is not a real-world bank account.

[Dmitry Astapov]

Ah, i see. Sorry, got my wires crossed.

--
You received this message because you are subscribed to the Google Groups "hledger" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hledger+u...@googlegroups.com.

[Eli Flanagan]

Oddly, google group's web interface keeps deleting my post. Censorship from the bots??

I think I was approaching this with a lack of doublebooking knowledge.The third account solution solves the bulk of my problem. 

Indeed Dmitry it is in most cases, "more of the same". 

No problem if this is unclear Simon; I should've spelled this out earlier.

Say I have the following CSV:

#,Effective Date,Transaction Description,Check Number,Amount

1,02/09/2018,HOMBNK CR,,-20.00

2,02/14/2018,HOMBNK CR,,-40.00

Assume that the first transaction comes from an external account I can export CSVs for but the second one does not.

Setting up the following rule would handle the case of the first transaction:

if

hombnk cr

  account1  Transfers

However, it would incorrectly classify the second transaction. 

I see this is a problem with the institution's CSV export. 

Because of that, it seems a problem best resolved by a simple CSV munging tool or a users' custom scripts.

Preprocessing the second transaction's description to include meaningful text should resolve it nicely.

For example, the newly modified row and corresponding rule:

2,02/14/2018,my outlier,,-40.00

if

my outlier

  account1  Income

Thanks again for the discussion Dmitry and Simon!

[Simon Michael]

Ah yes, some CSV just doesn't provide enough distinguishing information to allow full categorisation. I sometimes flag things for manual fixup:

if hombnk cr

  comment  ; TODO:check account