hledger
hledger copied to clipboard
simonmichael
On spending in other currencies, costs and csv conversion
Ways to record amounts and prices in other currency
Let say you have a bank account in a particular currency. Most of the bank accounts I saw are in a single particular currency (and I am treating "bank" here in a widest possible sense - PayPal is also a bank for the purposes of this text).
Let's call that account "bank" and it will be a USD account.
If you spend in other currencies, it probably makes sense to record the cost in dollars as well:
2019-01-01 Spending in VND
bank
expenses 32 VND @@ $10
This way you can run hledger bal and see that you spent 32 VND, or hledger bal -B, which will tell you that you spent $10.
You also have an alternative way to record that transaction:
2019-01-01 Spending in VND
expenses 32 VND
bank -$10
Hledger will use the commodity of the last posting to form prices, so the first posting will effectively have the amount of 32 VND @@ $10.
This transaction will not do what you want:
2019-01-01 Spending in VND
bank -$10
expenses 32 VND
It will be equivalent to:
2019-01-01 Spending in VND
bank -$10 @@ 32 VND
expenses 32 VND
and that's not what you want, as you will only be able to see how much you spent in VND out of your bank account, for all your expenses in total. Less obvious problem with this approach is that if you do open/close equity transactions at year-end, all of these priced amounts will end up in the close-of-year transaction and will probably occupy hundreds upon hundreds of lines.
Last remaining form where you omit the amount of expenses is obviously equivalent to the transaction above:
2019-01-01 Spending in VND
bank -$10 @@ 32 VND
expenses
What if your transactions come from CSV file?
To recap, we have two useful ways to record spendings in other currencies. We either do:
Form 1:
2019-01-01 Spending in VND
bank
expenses 32 VND @@ $10
Or we do Form 2:
2019-01-01 Spending in VND
expenses 32 VND
bank -$10
But what if we don't enter them manually, but rather convert them from CSV file?
Let's assume that our banking institution is nice, and supplies us with both dollars and VND amounts. In my experience, for amount in other currency (in this case, VND) you realistically have two options:
- Either amount in VND comes with some sort of indication whether this is expense or income (in the form of a sign, or a pair of debit-credit columns, or separate descriptive column
- Or VND amount is always positive, usually denoted as "amount in the currency of transaction" or something like that
If amount-in-other-currency is signed, we can write rules to convert to Form 2 above. Here we would encounter first problem it might seem natural to use bank for account1 . But in order for the costs to work the right way, bank should be account2! If the user is not familiar with how --cost works and assigns bank to account1, he is in for a surprise. Second problem is that there is no easy way to do Form 1 as you can't say "use the absolute value of this field". You can try and get creative with regular expression matches here, but that way lies madness...
What about the other alternative, when the amount in the other currencies is always positive? Well, you can't do Form 2 anymore, as you can't properly flip the sign of the amount in other currency (unless of course you are willing to get even crazier with regular expressions, but we already decided that we are not going there). You have to do Form 1, but herein lies third problem: contrary to what you might naively expect, you have to put all your number in amount2 and leave amount1 empty, which is again counterintuitive.
To summarize, you have to write CSV rules in a certain somewhat counterintuitive way for expenses in other currencies to be recorded in a useful way. All obvious approaches will not get you what you want.
What could be done?
Honestly, I am not sure yet. Ideas include:
- Changing docs to suggest that account for which you got the report should be
account2(after PR #1095 lands) - Adding support for "abs value of" to CSV rules language (what is the syntax though? and this reeks of a full-blown expression language ....)
- Adding support for designating certain currency or certain account as a
cost basis-related, so that CSV converter will automatically put the proper posting in the last position to ensure that price derivation works as required. - Adding support for designating the posting as
cost basis-forming to the journal syntax (no ideas on how this could look like and honestly this is my least favorite option, as I think it could be a massive can of worms) - Extending account directive to indicate that this account should never have priced transactions in it.
Do you feel that this is a problem that affects you? Any other thoughts or ideas?
Great analysis. I must say haven't knowingly encountered this with any of my CSVs yet, or I have unconsciously worked around it somehow. But now I feel very well forewarned and informed about it.
The fact that -B converts to the last posting's commodity has been sort of folk lore for a long time. I switch postings around by habit when needed. It should probably be made more prominent in docs.
Post #1095, I suspect it might be best to drop the advice to use a particular posting for the csv's account, and just let people put things where they want.
I don't yet have a clear idea on a best solution for what you're describing. Designating certain commodities as preferred valuation targets seems interesting. You could probably get decent results from a simple heuristic: -B always converts to the shorter commodity symbol.
Post #1095, I suspect it might be best to drop the advice to use a particular posting for the csv's account, and just let people put things where they want.
Docs in #1095 already de-emphasize the importance of account1. I will update this issue according
I dont like the idea of yet another heuristic for -B, cause it is highly likely that folks that keep everything neat and tidy would have all their symbols 1-char long or all of them 3-char long
This is a somewhat common scenario for travellers and international free-lancers. Have been anything decided on this problem in the past few years?
I don't think so @XVilka. Did it cause you pain ? Now that you know about it have you been able to work around by reordering postings or tweaking csv rules ? Making this clearer in docs is the easiest next step, do you have any ideas on that ?