Another CSV parsing question

[Oliver Payne]

Hi all,

I'm just starting to get into hledger having been a long time Gnucash user.

The first thing to do is to try and parse some bank downloads.  I'm getting a bit stuck with my credit card CSV file.  The rules file I've come up with is:

account1 liabilities:creditcard

skip 1

fields date, description, name, category, blank, amount

date-format %d %b %y

currency £

I think I have the fields correct, as the majority of the transactions are parsed fine.  This is where the amount is positive (i.e. I spend something and the liability increases).  However, as soon as the amount is negative (when I get a refund, or I pay the monthly bill), I get the error:

hledger: amount has no value

the CSV record is:       "{DATE}", "{DESCRIPTION}", "{NAME}", "{CATEGORY}", "-24.99", ""

with the obvious substitutions to remove personal data.

Have I made an obvious error?  I really hope I can get this to work, as hledger looks like a really nice way of keeping on top of the finances.

Many thanks,

Oliver

[Simon Michael]

Hi Oliver, welcome!

 

At a guess, this bank puts positive and negative amounts in different CSV fields. For this situation, use amount-in and amount-out instead of amount (they are mentioned briefly at http://hledger.org/MANUAL.html#csv-files ).

 

If that's not the situation, maybe paste an exact sample CSV line.

--

-- You received this message because you are subscribed to the Google Groups hledger group. To post to this group, send email to hle...@googlegroups.com. To unsubscribe from this group, send email to hledger+u...@googlegroups.com. For more options, visit this group at https://groups.google.com/d/forum/hledger?hl=en

---

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.

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

 

[Oliver Payne]

Thanks Simon for your quick response.

You were quite right; the positive and negative amounts are in different columns (quite difficult to spot in CSV!).  Here are a couple of representative lines:

03 Oct 13," Supermarket ",O PAYNE,Groceries,,10.00

01 Oct 13, Payment By Direct Debit ,O PAYNE,,"-100.00",

with a corresponding rules file:

account1 liabilities:creditcard

fields date, description, name, category, amount-in, amount-out

date-format %d %b %y

currency £

Now, when I try hledger -f ccard.csv print, I get the following:

2013/10/01  Payment By Direct Debit

    expenses:unknown             £100.00

    liabilities:creditcard      £-100.00

2013/10/03  Supermarket

    expenses:unknown              £10.00

    liabilities:creditcard       £-10.00

So I think the sign should be reversed for the first transaction (paying in).  Does the parser assume that the entries in amount-in and amount-out have the same sign and use the position to determine whether to add or subtract?  That being the case, I guess I need to remove the minus signs.

Thanks again,

Oliver

[Simon Michael]

I see, your bank uses different fields AND changes the sign in one of them. The hledger CSV rules can't quite handle this... or can they ?

 

fields date, description, name, category, in, out

amount (%in%out)

 

This was the only hack I could find that worked. Parentheses are used to negate the amount since the rules parser currently does not allow minus before a % field interpolation.

 

Every new CSV file seems brings new quirks.. I wonder when will the CSV rules/parser be "smart enough" ? And, what's an elegant way to accommodate this latest case ?

[Oliver Payne]

Hi Simon,

That's done the trick. I suspect banks will keep finding new ways of making parsing difficult!  Incidentally, I also tried their ofx download, but that caused other problems, so I guess they may be playing fast and loose with the rules!

Many thanks,

Oliver