Reporting USD Trans in CAD for CRA

[Ben Luey]

I do most of my transactions in USD. However, the Canadian Revenue Agency (CRA) requires that I submit numbers in Canadan dollars, based on the exchange rate at the time of each transaction.

So, if I have two $100 expenses, I cannot just sum them ($200) and convert that USD to CAD and report that. Instead, I have to convert each transaction to CAD with the day's exchange rate and then sum that.

The question is how best to do / handle this in beancount? I had a few thoughts on how to do this:

1) Do this all on the query side. Is there a sql query I can run that would do a convert for each transaction that matches some criteria and then sum that? Let's assume I've already input the USD/CAD exchange rate for all transaction dates into beancount.

2) Double-enter all transactions: (Maybe have a plugin do this behind the scenes) Any USD transaction would get mirrored as an additional CAD transaction with transaction date's exchange rate and I'd have both a expense:food and a expense:_food_CAD category that I could query directly in CAD. And Same for asset:bank asset:_bank_CAD (although for cash assets, I would need to convert the total balance, not sum the converted transactions).

Other thoughts on how to best handle this?

Thanks!

Ben

[Ben Blount]

There are some other Canadians on the list who have posted previously and developed some plugins to help with a variant of solution 2.

See https://groups.google.com/g/beancount/c/UcEqxDKtqB8/m/BJe_XFSlDAAJ and if you search teh mailing list you may find some additional threads.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/0cbd2e73-2bcd-49db-b7d6-5a67a84111b0n%40googlegroups.com.

[Martin Blais]

I just left Canada it was much easier to deal with.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CACGEkZvOFb4nry1-3z_8%2BiUPXnk4-WFuGNc%3DYHN8Gzt_MVarOg%40mail.gmail.com.

[Martin Michlmayr]

* Ben Luey <milehi...@gmail.com> [2021-07-19 08:14]:

> 1) Do this all on the query side. Is there a sql query I can run
> that would do a convert for each transaction that matches some
> criteria and then sum that? Let's assume I've already input the
> USD/CAD exchange rate for all transaction dates into beancount.

This query will convert from USD to CAD on the date of the
transaction:

SELECT date, narration, CONVERT(position, 'CAD', date) WHERE account ~ 'Expenses:Test'

--
Martin Michlmayr
https://www.cyrius.com/

[Alan H]

I have written a plugin that allows me to split expenses across tax years and assign fractions of each expense to a tax reporting account as needed. (For example to recover home office expenses)

As for dealing with reporting in the other (non-native) currency I do what Martin said - assuming I can write a query that shows all relevent amounts, I just convert the position to CAD while ensuring that I have appropriate pricing data to support the conversions. TBH this made tax-time this year much easier than prior years and very fragile large sheets

Alan

[Ben L]

Thanks for all the suggestions. Using https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py as a starting point, I'm trying to write a plugin that will duplicate every transaction in a second currency. So for example:

2020-01-01 * "Buy"
  Assets:Chequing    -400.00 USD
  Assets:Brokerage       4 ACME {100.00 USD}

Would turn into

2020-01-01 * "Buy"
  Assets:Chequing    -400.00 USD
  Assets:Brokerage       4 ACME {100.00 USD}

2020-01-01 * "Buy"
  Assets:Chequing_CAD    -500.00 CAD
  Assets:Brokerage_CAD       4 ACME_CAD {500.00 CAD}

(1.25 CAD = USD) This works fine for basic stuff, but the problem I'm having is I want to use the  beancount engine to calculate the values of certain lines. In the above example, if the exchange rate changes to 1.2 CAD = USD, and I sell 2 units of ACME, I would add

2020-03-01 * "Sell"
  Assets:Chequing       220.00 USD  
  Income:Capital-gains  
  Assets:Brokerage         -2 ACME {} @ 110.00 USD

(Beancount calculates cap gains as $20 since previous buy was at $100)

I want my plugin to generate symmetric lines in CAD using the 1.2 exchange rate:

2020-03-01 * "Sell"
  Assets:Chequing_CAD       264.00 CAD
  Income:Capital-gains_CAD
  Assets:Brokerage_CAD         -2 ACME_CAD {} @ 132.00 CAD

And then beancount calculate $14 CAD as cap gains since the 2 ACME_CAD were previously purchased at $125 CAD each.

The problem I'm having is in my plugin, when I loop through the entries (for entry in entries in python) then the entry figure has the USD value for the Capital-gains, so it just converts the USD to CAD with the relevant exchange rate instead of calculating it using the cost of the ACME_CAD asset. So in this example, instead of $14 CAD cap gains, it is $16.67 CAD ($20 / 1.2).

Is there a way to loop over the entries before beancount does these calculations? Or is there a better approach?

Thanks,

Ben

[James Cook]

Hi Ben,

I had my plugin compute capital gains itself, at least partly for the
reason in your email. See for example the test case "test_uses_price"
in
https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost_test.py

If you still want to do things your way, maybe you could add a metadata
field like "blank: True" to fields your plugin should treat as "blank". E.g.

2020-03-01 * "Sell"
Assets:Chequing 220.00 USD
Income:Capital-gains

blank: True

Assets:Brokerage -2 ACME {} @ 110.00 USD

and then your plugin would know to ignore the USD value it sees in the
Income:Capital-gains posting.

I don't know if there's a way to modify the entries before Beancount
fills in blanks.

--
James