"register_hledge()" fails with comma as currency separator and/or if the commodity is prefixed

StefanBRas commented 4 years ago

Hi

As the title states, register_hledge fails if the currency is formatted with a comma as the decimal separator and/or the commodity is prefixed. It will import the transactions, but all amounts will be NA. All of these formats are the official format somewhere.

I found possible solutions (see below) such that the user doesn't need to change his original hledger files and where the internals of register_hledge stays roughly the same, with a few additions.

I have not yet implemented the solutions, but if you agree with them i can make a pull request.

Either way, it took me some time to find error so i believe a more descriptive error message would be good.

Reproducible code

Note that this will create a file called "temp.journal" in the current directory)

> writeLines(c("2016/01/01 Rent", "    assets    100.00 EUR","    expenses"), "temp.journal")
> register_hledger("temp.journal") # works
# A tibble: 2 x 11
  date       mark  payee description account amount commodity historical_cost
  <date>     <chr> <lgl> <chr>       <chr>    <dbl> <chr>               <dbl>
1 2016-01-01 ""    NA    Rent        assets     100 EUR                   100
2 2016-01-01 ""    NA    Rent        expens…   -100 EUR                  -100
...
> writeLines(c("2016/01/01 Rent", "    assets    100,00 EUR","    expenses"), "temp.journal")
> register_hledger("temp.journal") # will import NAs
# A tibble: 2 x 11
  date       mark  payee description account amount commodity historical_cost
  <date>     <chr> <lgl> <chr>       <chr>    <dbl> <chr>               <dbl>
1 2016-01-01 ""    NA    Rent        assets      NA EUR                    NA
2 2016-01-01 ""    NA    Rent        expens…     NA EUR                    NA
# … with 3 more variables: hc_commodity <chr>, market_value <dbl>,
#   mv_commodity <chr>
Warning messages:
1: NAs introduced by coercion 
2: NAs introduced by coercion 
3: NAs introduced by coercion 
> writeLines(c("2016/01/01 Rent", "    assets    EUR 100.00","    expenses"), "temp.journal")
> register_hledger("temp.journal") # Will import NAs
# A tibble: 2 x 11
  date       mark  payee description account amount commodity historical_cost
  <date>     <chr> <lgl> <chr>       <chr>    <dbl> <chr>               <dbl>
1 2016-01-01 ""    NA    Rent        assets      NA 100.00                 NA
2 2016-01-01 ""    NA    Rent        expens…     NA -100.00                NA
# … with 3 more variables: hc_commodity <chr>, market_value <dbl>,
#   mv_commodity <chr>
Warning messages:
1: NAs introduced by coercion 
2: NAs introduced by coercion 
3: NAs introduced by coercion 
> writeLines(c("2016/01/01 Rent", "    assets    EUR 100,00","    expenses"), "temp.journal")
> register_hledger("temp.journal") "will imports NAs"
# A tibble: 2 x 11
  date       mark  payee description account amount commodity historical_cost
  <date>     <chr> <lgl> <chr>       <chr>    <dbl> <chr>               <dbl>
1 2016-01-01 ""    NA    Rent        assets      NA 100,00                 NA
2 2016-01-01 ""    NA    Rent        expens…     NA -100,00                NA
# … with 3 more variables: hc_commodity <chr>, market_value <dbl>,
#   mv_commodity <chr>
Warning messages:
1: NAs introduced by coercion 
2: NAs introduced by coercion 
3: NAs introduced by coercion

Solutions

Commodity placement

Using hledge print -o file.csv instead of hledge register -o file.csv yields a file with all the information from using register but where the amount is split into an "amount" and "commodity" field. Hence the commodity could be extracted from that column.

For example: (using the same temp.journal from above)

$ hledger register -f temp.journal -o tempReg.csv
$ hledger print -f temp.journal -o tempPrint.csv
$ cat tempReg.csv
"txnidx","date","code","description","account","amount","total"
"1","2016/01/01","","Rent","assets","EUR 100,00","EUR 100,00"
"1","2016/01/01","","Rent","expenses","EUR -100,00","0"
$ cat tempPrint.csv
"txnidx","date","date2","status","code","description","comment","account","amount","commodity","credit","debit","posting-status","posting-comment"
"1","2016/01/01","","","","Rent","","assets","100,00","EUR","","100,00","",""
"1","2016/01/01","","","","Rent","","expenses","-100,00","EUR","100,00","","",""

The columns are named in the same way, so it wouldn't change much.

Decimal separator

This solution is a bit more "hacky" but I believe it's pretty robust.

hledger allows one to specify commodity directives with commodity 100.00 USD - but it has to be a part of the journal file. So a possible solution could work like

Use hledger print -o file.csv to find all commodities in use, like above
create a copy of the journal file
for each commodity, insert the line "commodity 100.00 \<commodity>" at the end of the file
use hledger register exactly as in the original implementation. I have tried it with a single commodity and it worked.

trevorld commented 4 years ago

I'd accept a pull request if it doesn't break hledger import for earlier versions of hledger that this package currently support. An ideal target would be whichever version of hledger introduced the --unmarked flag which I currently use (seems to be after hledger 1.2 which is the version that comes with Ubuntu 18.04 LTS).

potential challenges with hledger print

I'm not sure how painless switching to hledger print will be. I'm seeing with the package's built-in example in inst/extdata/example.hledger for some rows I get amount column like 4 @ 250 USD and commodity SP with hledger print whereas hledger register gives me a much more consistent to parse 4 SP. It seems between hledger 1.2 and hledger 1.15.2 the amount column for those rows with the print command with the -V flag (which we also use) went from 2000.00 @ 250 USD to just 2000.00.

In those cases it seems though you'd just need to detect and discard those @ blah values that only appear sometimes. So maybe it isn't that hard.

decimal support

I think adding decimal separator support might be possible within R using regular expressions and gsub. Write an to_numeric function that does:

Convert last . or , to a ;
Remove all other . and ,
Convert ; to .
Cast to numeric with as.numeric

trevorld commented 4 years ago

Development version should now have decimal separator support for hledger via a more robust to_numeric function. Still no support for commodity prefixes.

StefanBRas commented 4 years ago

Ah, i didn't think to check with earlier versions.

My idea with using "commodity 100.00 \\" for decimal support was to off-load the work to hledger - i wouldn't be surprised if somewhere they're using some completely different symbol(s).

But doing the trick with adding "commodity ..." to the bottom of my own .journal file solves both the problems for me, and i probably won't get around to make a pull request.

Thanks for the quick replies

trevorld / r-ledger