CSV output if using more than 1 currency not very useful

[MarkusTeufelberger]

Issues with how to encode the various levels of accounts aside, there are all kinds of spaces, newlines etc. when querying for a CSV formatted balance sheet.

Also I'd like to have "currency" and "amount" as seperate filed, it is not easily possible to add the strings "5.00 USD + 5.00 USD" in Excel...

Thanks for the feature though, I hope to be able to use it in the future to be one day draw a line graph of my daily net worth - this is a step in this direction.

[simonmichael]

Could you paste an example or two ?

I made such graphs not too long ago, pasting balance csv output into a spreadsheet. I probably used cur: to limit the report to one currency at a time.

(You can also see this graph in hledger-web's register view).

[simonmichael]

Closing, reopen with more details if needed.

[MatthiasKauer]

I came here from Google and I'm not sure if I should open another issue for this.
The problem I am facing is that currency and amount are not separated. This makes further processing harder than it should be.

R:\ledger-2016\sgd>ldg register -p "last year" income:dbs:interest -O csv
"txnidx","date","description","account","amount","total"
"21","2016/01/31","","income:dbs:interest","SGD -0.31","SGD -0.31"
...

I'd prefer columns "amount", "currency" separately, or something like that.

[simonmichael]

Hi @MatthiasKauer, I guess continuing on this issue is good. Can you come up with a specification for how we should handle multi-commodity balances, which can arise with the register command ?

[MatthiasKauer]

I don't have a good idea for the multi-currency situation right now. There could be several running totals, I suppose. I am not sure if that's what I would expect, however. If time permits I should run into that issue in practice within this month. Let's see.

[trevorld]

@simonmichael For a change that won't break many existing hledger csv importers I would suggest keeping the amount and total columns as is but add new number and commodity columns "splitting" the amount column into two columns (for register output does that column ever have multiple currencies?). Don't bother trying to add new columns to handle the multi-currency situation in the total column (users can easily re-create "cumulative sums" themselves if needed).

If you are adding new columns to the csv output it would be also be very nice to add a mark column and tags column. Currently my ledger package needs to call hledger register 9 times in order to infer the mark column as well as appropriate historical cost and market value columns when importing hledger files and adding a mark column would cut that down to 3 times. A tags column would allow users to create new grouping variables according to their reporting needs.

@MarkusTeufelberger , @MatthiasKauer If you install r-ledger (disclaimer an R package I wrote) it might be able to give you your desired csv output for your hledger file:

$ hledger register -f example.hledger -o example.csv 
$ cat example.csv | head | csvlook
|---------+------------+-----------------------------+-----------------------+--------------+---------------------|
|  txnidx | date       | description                 | account               | amount       | total               |
|---------+------------+-----------------------------+-----------------------+--------------+---------------------|
|  1      | 2015/12/31 | Opening Balances            | Assets:JT-Checking    | 5000.00 USD  | 5000.00 USD         |
|  1      | 2015/12/31 | Opening Balances            | Equity:Opening        | -5000.00 USD | 0                   |
|  2      | 2016/01/01 | Landlord | Rent             | Assets:JT-Checking    | -1500.00 USD | -1500.00 USD        |
|  2      | 2016/01/01 | Landlord | Rent             | Expenses:Shelter:Rent | 1500.00 USD  | 0                   |
|  3      | 2016/01/01 | Brokerage | Buy Stock       | Assets:JT-Checking    | -1000.00 USD | -1000.00 USD        |
|  3      | 2016/01/01 | Brokerage | Buy Stock       | Equity:Transfer       | 1000.00 USD  | 0                   |
|  4      | 2016/01/01 | Brokerage | Buy Stock       | Assets:JT-Brokerage   | 4 SP         | 4 SP                |
|  4      | 2016/01/01 | Brokerage | Buy Stock       | Equity:Transfer       | -1000.00 USD | 4 SP, -1000.00 USD  |
|  5      | 2016/01/01 | Supermarket | Grocery store | Expenses:Food:Grocery | 500.54 USD   | 4 SP, -499.46 USD   |
|---------+------------+-----------------------------+-----------------------+--------------+---------------------|
$ Rscript --default-packages=ledger,rio -e 'convert("example.hledger", "example2.csv")'
$ cat example2.csv | head | csvlook
|-------------+------+-------------+------------------+-----------------------+--------+-----------+-----------------+--------------+--------------+---------------|
|  date       | mark | payee       | description      | account               | amount | commodity | historical_cost | hc_commodity | market_value | mv_commodity  |
|-------------+------+-------------+------------------+-----------------------+--------+-----------+-----------------+--------------+--------------+---------------|
|  2015-12-31 | *    |             | Opening Balances | Assets:JT-Checking    | 5000   | USD       | 5000            | USD          | 5000         | USD           |
|  2015-12-31 | *    |             | Opening Balances | Equity:Opening        | -5000  | USD       | -5000           | USD          | -5000        | USD           |
|  2016-01-01 | *    | Landlord    | Rent             | Assets:JT-Checking    | -1500  | USD       | -1500           | USD          | -1500        | USD           |
|  2016-01-01 | *    | Landlord    | Rent             | Expenses:Shelter:Rent | 1500   | USD       | 1500            | USD          | 1500         | USD           |
|  2016-01-01 | *    | Brokerage   | Buy Stock        | Assets:JT-Checking    | -1000  | USD       | -1000           | USD          | -1000        | USD           |
|  2016-01-01 | *    | Brokerage   | Buy Stock        | Equity:Transfer       | 1000   | USD       | 1000            | USD          | 1000         | USD           |
|  2016-01-01 | *    | Brokerage   | Buy Stock        | Assets:JT-Brokerage   | 4      | SP        | 1000            | USD          | 2000         | USD           |
|  2016-01-01 | *    | Brokerage   | Buy Stock        | Equity:Transfer       | -1000  | USD       | -1000           | USD          | -1000        | USD           |
|  2016-01-01 | *    | Supermarket | Grocery store    | Expenses:Food:Grocery | 500.54 | USD       | 500.54          | USD          | 500.54       | USD           |
|-------------+------+-------------+------------------+-----------------------+--------+-----------+-----------------+--------------+--------------+---------------|

[simonmichael]

Nice @trevorld. I'd welcome pull requests adding mark, payee, and separated amount/commodity columns (but I think we should probably keep the combined commodity & amount column as well). The other columns might be useful later, not sure.

As for the posting amount being a multi-commodity amount, it's currently not possible but @ag-eitilt is working on changing that, which will complicate all our lives.. #934

[trevorld]

I agree that the additional market value and historical cost columns my R package produces should be out of scope for the csv output of hledger register (hledger register users should instead generate additional csv output files for those use cases by throwing in the appropriate --costor --value flags).

[trevorld]

Even though #934 would allow multiple-commodity amounts in a single line for hledger input if that feature is implemented it would be convenient if those entries would be broken up to into multiple single-currency entries for hledger register output (not sure of hledger internals but maybe easiest if internally "broken and stored as multiple single-commodity entries" at input time?).

[ag-eitilt]

@trevorld It's already doing that with the terminal output, though I the CSV output still has them combined (comma-separated within the quoted string, like the "total" column). I can probably change that in the PR if y'all agree it would be best, though it might take a bit for me to get the time.

[trevorld]

@ag-eitilt Not putting those "separate-currency" transactions as separate lines in the CSV output (even after "conversion" to the same currency) will break the current CSV import of hledger's register in my R package (and probably other people's code that imports the hledger's csv register output) and make it a real pain to cleanly import. Right now all you have to do is a basic string manipulations on the amount column which is straightforward to do (in R at least) and when I "re-import" the hledger file converting to historical cost or market value I just need to "concatenate" values to add those columns. This would also block the ability to add new separate number and currency columns which would remove the need to do any string manipulations at all which would be a big help for people who want to work with hledger register csv output with more limited tools like Microsoft Excel.

In particular it isn't clear to me how I'd ever be able to accurately split such a row's market value and historical cost between its implicit "sub-transactions" even if I go through all the toil of splitting the original amount into different commodities as separate lines in the "spreadsheet".

However I suspect the number of users who will mix commodities in a single line to be a really small fraction of total hledger users so probably not a big deal if their hledger files aren't supported by my (or other people's) hledger register csv output importers. I probably just won't support them or suggest they first try something like ledger2beancount to see if they can convert it into a format that provides me a way to get clean csv output of the register contents.