Foreign currency

Associated directory: 10-foreign-currency

Handling transactions in foreign currency: a simple case

Let's tackle the most common scenario: most of your transactions (both earnings and spendings) are in a single currency (henceforth called "native" currency), but sometimes you are being charged amounts in foreign currency, but this does not happen often. Examples in this repository that you've seen so far have all been in GBP (£), and this would be our "native" currency, and I will use USD ($) as an example of the foreign currency.

In a situation like this, you will probably want to think in the native currency whenever you are looking at long-term figures (like total annual expenditure), but at the same time, you would like to know the exact amount spent in foreign currency when you are looking at a particular transaction.

To achieve this, you would have to record the cost of your foreign currency transaction in native currency, like this:

2020-03-09 Foreign currency expense example
  assets:bank    -£10
  expenses:whatever    $11.65 @@ £10

Note that amount in foreign currency (with native cost) goes on the expense account posting - as you will be deducting native currency from your bank account.

Now hledger will report your expenses as $11.65, but if you will use --cost flag, it will use the cost instead and report this item of expenses as £10.

When looking at the annual income/expenses report, you will probably want to use --cost most of the time. This way you would immediately see the cost of foreign-currency expenses in the units most familiar to you. It is easy to add "--cost" flag to the appropriate hledger invocation in export.hs (see diffs/09-to-10.diff)

Handling foreign currency transactions in bank statements

Now, if we were recording foreign currency transactions, this would be all that you need to do and remember.

But if you want to handle foreign currency transactions as they are reported by your bank, then (in my experience), you will need to do a bit of work first. More often than not banks report foreign currency transactions in a way that does not lend itself to easy handling by hledger's CSV conversion rules. Foreign currency amount (or FX rate) might be tucked away in some sort of free-form description field, and the transaction amount will always be reported in the native currency.

You might need to do a bit of preprocessing. I found that it is ofter easy to "sacrifice" one of the existing columns to extract foreign currency amount/rate out of the description field. In 10-foreign-currency I provided an example of how "inconvenient" bank statement might look like:

$ cat import/lloyds/in/99966633_20171224_2043.csv

Transaction Date,Transaction Type,Sort Code,Account Number,Transaction Description,Debit Amount,Credit Amount,Balance,
01/05/2016,BP,'12-34-56,99966633,AVIVA,100,,100.00
09/04/2016,DEB,'12-34-56,99966633,TRANSFER TO 12345678,1000,,200.00
07/04/2016,BP,'12-34-56,99966633,OASIS COFFEE ,3.72,,1200.00
05/04/2016,FOREIGN CCY $6.40,'12-34-56,99966633,WIKIMEDIA,5,,1203.72
02/04/2016,FOREIGN CCY $7.68,'12-34-56,99966633,HLEDGER,6,,1208.72
31/03/2016,BGC,'12-34-56,99966633,HSBC,100,,1214.72
30/03/2016,BGC,'12-34-56,99966633,EMPLOYER INC,,664.72,1314.72

Here the foreign currency amount is embedded into Transaction code field. We will sacrifice "sort code" column to put foreign currency amount there with the help of in2csv script:

$ cat import/lloyds/in2csv

#!/bin/bash
sed -e 's/  +/ /g; s/,99966633,/,assets:Lloyds:current,/; s/,12345678,/,assets:Lloyds:savings,/; s/FOREIGN CCY \([^,]*\),[^,]*/FOREIGN CCY,\1/' < "$1"

After cleanup, csv file is more parseable:

$ cat import/lloyds/csv/99966633_20171224_2043.csv

Transaction Date,Transaction Type,Sort Code,Account Number,Transaction Description,Debit Amount,Credit Amount,Balance,
01/05/2016,BP,'12-34-56,assets:Lloyds:current,AVIVA,100,,100.00
09/04/2016,DEB,'12-34-56,assets:Lloyds:current,TRANSFER TO 12345678,1000,,200.00
07/04/2016,BP,'12-34-56,assets:Lloyds:current,OASIS COFFEE ,3.72,,1200.00
05/04/2016,FOREIGN CCY,$6.40,assets:Lloyds:current,WIKIMEDIA,5,,1203.72
02/04/2016,FOREIGN CCY,$7.68,assets:Lloyds:current,HLEDGER,6,,1208.72
31/03/2016,BGC,'12-34-56,assets:Lloyds:current,HSBC,100,,1214.72
30/03/2016,BGC,'12-34-56,assets:Lloyds:current,EMPLOYER INC,,664.72,1314.72

Now it is simply the matter of producing suitable CSV conversion rules so that foreign currency amount will be used as the transaction amount, and transaction amount will become the price:

if
FOREIGN CCY
  amount2  %sortcode @@ £%amount1-out

With this rule, converted transactions will have proper prices:

$ hledger -f all.journal print code:FOREIGN

2016-04-02 (FOREIGN CCY) HLEDGER
    assets:Lloyds:current             £-6 = £1208.72
    expenses:donations        $7.68 @@ £6

2016-04-05 (FOREIGN CCY) WIKIMEDIA
    assets:Lloyds:current             £-5 = £1203.72
    expenses:donations        $6.40 @@ £5

And now we can see our expenses in the currencies that were spent:

$ hledger -f 2016.journal balance expenses

               £3.72  expenses:coffee
              $14.08  expenses:donations
              £11.01  expenses:mortgage interest
--------------------
              $14.08
              £14.73  

... or in the native currency:

$ hledger -f 2016.journal balance expenses --cost

               £3.72  expenses:coffee
              £11.00  expenses:donations
              £11.01  expenses:mortgage interest
--------------------
              £25.73  

And that's it!

Next steps

Let's deal with those pesky expenses:unknown transaction quicky and efficiently. In the next chapter, we are sorting "unknowns".