Implicitly related transactions from Mint, CC payments

[Daniel Vainsencher]

I am considering a combination of Mint (for short term management) and hledger (for in depth looks, long term record keeping, and stuff that is not a Mint supported account). I want to feed hledger from the Mint csv export function, and insert manually only the information that Mint cannot provide. This seems to avoid setting up imports from many different sources. Do other people use something like this?

While exploring this possibility, I've found a problem that perhaps others have solutions for: both the Mint CSV, but also my banks' (Wells Fargo) OFX export give no aid in recognizing some transactions that are related.

Example 1: automatic payments of CC balances, reduce debt on one account, reduce assets on another, usually on different days.
Example 2: an automatic transfer from checking to savings account (usually delayed by a day) has no common identifier. The amount is the same, but it is also often 1$, so it is not obvious which transaction on one account belongs to which transaction on the other account if one happens every day for a while. Even though both are in WF, the OFX file has no hints.

I think what we want to end up with is a single transaction with posting date comments summarizing both the outgoing and incoming transactions in the downloaded data.

ledger-autosync as is cannot help, as its CSVConverter maps each row to a single transaction, independent of the other rows.

Any other existing solutions/workarounds?

I've started thinking about how I would solve the problem, but I'll leave that to another post if it makes sense at all.

Thanks for hledger,
Daniel Vainsencher

[Erik Hetzner]

Hi Daniel,

On Sat, 08 Oct 2016 12:09:01 -0700,
Daniel Vainsencher <daniel.va...@gmail.com> wrote:
>
> I am considering a combination of Mint (for short term management) and
> hledger (for in depth looks, long term record keeping, and stuff that is
> not a Mint supported account). I want to feed hledger from the Mint csv
> export function, and insert manually only the information that Mint cannot
> provide. This seems to avoid setting up imports from many different
> sources. Do other people use something like this?
>
> While exploring this possibility, I've found a problem that perhaps others
> have solutions for: both the Mint CSV, but also my banks' (Wells Fargo) OFX
> export give no aid in recognizing some transactions that are related.
>

> […]

I use transfer accounts. You have to manage it semi-manually, but
ledger-autosync (and probably other importers) will often guess the transfer
account properly after some time.

2016/03/25 * ACH DEBIT- CARDMEMBER SERV DESC:WEB PYMT
; ofxid: 2000.XXX
Assets:Checking -$11.00
Transfer:AC->L_USBank $11.00

2016/03/25 * PAYMENT THANK YOU WEB AUTOMTC; 00000; ;
; ofxid: 1402.XXX
Liabilities:USBank $11.00
Transfer:AC->L_USBank -$11.00

I don’t think there is a much better solution - your bank and your credit card
company don’t know (or at least don’t provide information) that two transactions
are related to one another. It would be possible when importing, perhaps, to
“collapse” transactions with differently signed but otherwise identical amounts
within a few days of one another, but I’m not sure this would be a good idea.

best, Erik
--
Sent from my free software system <http://fsf.org/>.

[Daniel Vainsencher]

Thanks, the transfer accounts solution looks like an immediately feasible and pretty reasonable one!
I didn't see any support in ledger-autosync's CSVConverter for identifying such accounts, but hledger's CSV.rules should work ok, adding two entries once for each kind of transfer. This does require some kind of check to ensure that transfer accounts are generally empty.

My solution would be to create a specialized kind of rule that is perhaps easier to maintain, whose implementation verifies that the transfer accounts balance (except perhaps for any very recent ones). But I think I'll start with yours for now!

Daniel

[Simon Michael]

Hi Daniel,

> On Oct 8, 2016, at 9:09 AM, Daniel Vainsencher <daniel.va...@gmail.com> wrote:
> I am considering a combination of Mint (for short term management) and hledger (for in depth looks, long term record keeping, and stuff that is not a Mint supported account). I want to feed hledger from the Mint csv export function, and insert manually only the information that Mint cannot provide. This seems to avoid setting up imports from many different sources. Do other people use something like this?

I think it's a good plan, using Mint as a first pass to get clean CSV, if you don't mind giving them access to all your accounts. I started down this road but drifted away from Mint and have not given it a real try.

> While exploring this possibility, I've found a problem that perhaps others have solutions for: both the Mint CSV, but also my banks' (Wells Fargo) OFX export give no aid in recognizing some transactions that are related.
>
> Example 1: automatic payments of CC balances, reduce debt on one account, reduce assets on another, usually on different days.
> Example 2: an automatic transfer from checking to savings account (usually delayed by a day) has no common identifier. The amount is the same, but it is also often 1$, so it is not obvious which transaction on one account belongs to which transaction on the other account if one happens every day for a while. Even though both are in WF, the OFX file has no hints.

Are you sure there's no corresponding ID ? In Wells Fargo CSV (and OFX) I see a "REF" in the descriptions:

checking.csv:
"10/03/2016","-200.00","*","","RECURRING TRANSFER TO ... SAVINGS REF #OPER79Y293 ..."

savings.csv:
"10/03/2016","200.00","*","","RECURRING TRANSFER FROM ... CHECKING REF #OPER79Y293 ..."

> I think what we want to end up with is a single transaction with posting date comments summarizing both the outgoing and incoming transactions in the downloaded data.
>
> ledger-autosync as is cannot help, as its CSVConverter maps each row to a single transaction, independent of the other rows.
>
> Any other existing solutions/workarounds?

But my workaround is low-tech; I import such transfers from one account only (checking), and selectively import (eg savings interest txns) from the other.

Some kind of helpful smart import wizard should certainly be possible, even if only semi-automated.

[Erik Hetzner]

Hi Daniel,

Great, I’m glad the transfer account will work for you!

ledger-autosync uses a pretty simple algorithm to determine the account - it
will search ledger for the last transaction with the same payee and use that.

At some point I’d like to implement something like that described here:

http://tomszilagyi.github.io/payment-matching/

but that is a future project.

best, Erik

On Sat, 08 Oct 2016 23:55:54 -0700,
Daniel Vainsencher <daniel.va...@gmail.com> wrote:
>
> Thanks, the transfer accounts solution looks like an immediately feasible
> and pretty reasonable one!
> I didn't see any support in ledger-autosync's CSVConverter for identifying
> such accounts, but hledger's CSV.rules should work ok, adding two entries
> once for each kind of transfer. This does require some kind of check to
> ensure that transfer accounts are generally empty.
>
> My solution would be to create a specialized kind of rule that is perhaps
> easier to maintain, whose implementation verifies that the transfer
> accounts balance (except perhaps for any very recent ones). But I think
> I'll start with yours for now!

[Daniel Vainsencher]

On Sun, Oct 9, 2016 at 8:43 PM, Simon Michael <si...@joyful.com> wrote:

I think it's a good plan, using Mint as a first pass to get clean CSV, if you don't mind giving them access to all your accounts. I started down this road but drifted away from Mint and have not given it a real try.

First, it seems to fit my SO pretty well, so its already there. Second, I hope using Mint as a standalone process for it will work well, as I've had really bad luck getting information out of my (too many) different financial institutions. I need to connect to Wells Fargo (probably not for long), Chase and Capital One credit cards, anyone have success stories from any of those to hledger? I mean either fully automated, or with manual downloading.

Are you sure there's no corresponding ID ? In Wells Fargo CSV (and OFX) I see a "REF" in the descriptions:
checking.csv:
"10/03/2016","-200.00","*","","RECURRING TRANSFER TO ... SAVINGS REF #OPER79Y293 ..."
savings.csv:
"10/03/2016","200.00","*","","RECURRING TRANSFER FROM ... CHECKING REF #OPER79Y293 ..."

A guess: that same reference number appears in all transfers stemming from the recurring order? if so, they do not help link two particular transactions as being the beginning and end of the same transfer, right? Erik's transfer accts idea has me near convinced that consolidating transactions as I'd planned is not so important, but that is what I was aiming for.

 

> Any other existing solutions/workarounds?

But my workaround is low-tech; I import such transfers from one account only (checking), and selectively import (eg savings interest txns) from the other.

Some kind of helpful smart import wizard should certainly be possible, even if only semi-automated.

I would very much like to focus the little and endangered attention I have for finances on higher level issues, hence the focus on automation.

Daniel

[Daniel Vainsencher]

On Sun, Oct 9, 2016 at 9:11 PM, Erik Hetzner <ehet...@gmail.com> wrote:

Great, I’m glad the transfer account will work for you!

Haven't yet found a nice way to automate it, though, the rules mechanism is not quite up to it yet (will post separately on this).

 

ledger-autosync uses a pretty simple algorithm to determine the account - it
will search ledger for the last transaction with the same payee and use that.

That sounds like a very reasonable heuristic, but I'm not sure its for me.

Don't know if it applies well here, but I like the generated file dogma: generate xor edit.

In particular, I will never edit the .hledger generated from Mint CSV files, so that I can regenerate them at will (for example, if I improve categorization inside Mint).

Daniel