Hledger: Non-empty debit and credit amounts in CSV

[lucianf]

My bank gives separate debit and credit amounts in the following format:

Sample Transaction,Money In,Money Out
A Deposit,12.34,0.00
A Withdrawal,0.00,34.56

Is there any way to parse this using amount-in / amount-out or the basic amount directive, or do I need to remove the 0.00's before submitting to hledger? Since arithmetic operations or field-level matching are not supported I can't seem to find a way to make this work with hledger only.

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[simonmichael]

Hi Lucian, this is exactly what amount-in/amount-out are for, no ?

By the way we use the issue tracker mainly for bug reports and enhancement proposals, for support questions I think the mail list or IRC channel best.

[lucianf]

Thanks Simon. I've perused all the CSV-related issues here and found quite a few discussions regarding amount formats; also I humbly thought this was an enhancement proposal, because with amount-in/amount-out I'm getting the error both amount-in and amount-out have a value, and I can't seem to get rid of the 0.00's in any other way (I don't have a transaction type field that I can run conditional blocks on).

[simonmichael]

No problem! Adding a complete reproducible example also helps me not get confused. I see now your data includes no date field - could this be the problem ?

[lucianf]

Sorry, I had removed everything else I thought had nothing to do with the case in point.

Real-life example:

Date,Reference,Transaction Type,Money In, Money Out
05/06/2017,01 JUN 17 TESCO STORES,Card Purchase,0.00,12.80
12/04/2017,Counter deposit,Cash Deposit,5300.00,0.00

I was just now looking at the Bank of Ireland sample, looks like a promising starting point.

LE: Reading this it turns out the if ~ isn't actually implemented. Back to square 1.

[simonmichael]

Ohh, I see the (a) problem now:

# a.csv.rules
skip 1
fields date, description, type, amount-in, amount-out
date-format %m/%d/%Y

$ hledger -f a.csv print
using conversion rules file /Users/simon/src/hledger/a.csv.rules
2017/05/06 01 JUN 17 TESCO STORES
hledger: both amount-in and amount-out have a value
the CSV record is:       "05/06/2017", "01 JUN 17 TESCO STORES", "Card Purchase", "0.00", "12.80"



md5-0af9aa685b5233a9b31f44f0766142f3



skip 1
fields date, description, type, in, out
date-format %m/%d/%Y

# set amount to the (sign-flipped) out field
amount  -%out
# unless that contains 0.00, in which case the in field
if ,0.00$
 amount  %in

Thanks for linking the B of I sample (which I'll update, also.)

[simonmichael]

PS I think it should allow any zero-looking amount as well as the empty string, making this workaround unnecessary.

[lucianf]

Aha, now you see why I meant to open this as an issue :) Yes, any zero-looking amount should be discarded, not just null fields.

However: apologies for missing one key thing (fat fingered deletion - don't ask why). I have one more column at the end: "balance". Hence can't test for 0.00$ - that's actually my big problem, I can't figure out where I have the 0.00 without being able to do field-level if matching. The only real workaround afaict is to sed the 0.00's out.

[simonmichael]

You can use a regexp like in the (updated) bank of ireland sample to count fields from the beginning. (Assuming no field values contain a comma.. there's always a catch).

# if 3rd field is empty:
if ^([^,]*,){2},

I would be delighted if someone worked more on the unfinished CSV conversion features, such as proper field-level matching. CSV issues