Bean-report to use FX conversion on date of transaction instead of latest FX rate

[hoongsh...@oromico.com]

Hi,

I have a beancount file with multiple FX conversion rates for different dates (same currency pair) and would like to use the FX rate on the date of transaction instead of the latest FX rate found. bean-report seems to use the latest FX rate by default (the equivalent bean-query is shown below) -

  bean-query> SELECT date, narration, account, position, getprice(currency,"SGD", date), convert(position, "SGD")

I am trying to get the bean-report and fava to query in this way instead

  bean-query> SELECT date, narration, account, position, getprice(currency,"SGD", date), convert(position, "SGD", date)

Is there an easy way to get the income report to utilize the second way of conversion rather than the first?

Regards,

hoongshen

[hoongsh...@oromico.com]

In addition I was reading up the industry practice for treatment of items on the income statement and came across the following extracted at (https://www.accountingtools.com/articles/foreign-currency-translation.html):

• Assets and liabilities. Translate using the current exchange rate at the balance sheet date for assets and liabilities.
• Income statement items. Translate revenues, expenses, gains, and losses using the exchange rate as of the dates when those items were originally recognized.

So far I find that:

- bean-report doesn't support currency conversions

- bean-query support currency conversions only on a per item basis, I can't construct a query that gives me the equivalent of the income statement entries but based on the exchange rate of the individual transaction dates

- fava conversion feature uses inventory numbers (e.g. my USD balance on my Income accounts) and the FX rate either at end of period or latest FX rate. 

It seems that the options I have are:

- major surgery on fava to change the methodology for aggregating the numbers for the income statement 

- build another program sitting on bean-query to perform the aggregation of the income and expense items based on transaction dates

Appreciate some thoughts on how I should go about to get the income statement to aggregate and convert foreign currencies correctly.

Regards,

hoongshen

[Martin Blais]

On Wed, Mar 28, 2018 at 11:33 PM, <hoongsh...@oromico.com> wrote:

In addition I was reading up the industry practice for treatment of items on the income statement and came across the following extracted at (https://www.accountingtools.com/articles/foreign-currency-translation.html):

• Assets and liabilities. Translate using the current exchange rate at the balance sheet date for assets and liabilities.
• Income statement items. Translate revenues, expenses, gains, and losses using the exchange rate as of the dates when those items were originally recognized.

That makes sense, though for tax purposes, depending on the jurisdiction, one might have to translate differently, but this sounds like a good default, recognizing income at the rate at the time of payment and valuing assets at the latest rate.

 

So far I find that:

- bean-report doesn't support currency conversions

FYI long-term the idea is to replace all those report implementations by SQL queries embellished as webpages.

 

- bean-query support currency conversions only on a per item basis, I can't construct a query that gives me the equivalent of the income statement entries but based on the exchange rate of the individual transaction dates

Doesn't something like this provide useful output: 

beancount> select position, convert(position, "USD", date), getprice(currency, "USD", date) where currency = "CAD"                                                                                                  

 

- fava conversion feature uses inventory numbers (e.g. my USD balance on my Income accounts) and the FX rate either at end of period or latest FX rate. 

It seems that the options I have are:

- major surgery on fava to change the methodology for aggregating the numbers for the income statement 

- build another program sitting on bean-query to perform the aggregation of the income and expense items based on transaction dates

I really do think the SQL query should be made to be able to do this if you find it cannot

Appreciate some thoughts on how I should go about to get the income statement to aggregate and convert foreign currencies correctly.

Regards,

hoongshen

On Wednesday, March 28, 2018 at 10:01:01 PM UTC+8, hoongsh...@oromico.com wrote:

Hi,

I have a beancount file with multiple FX conversion rates for different dates (same currency pair) and would like to use the FX rate on the date of transaction instead of the latest FX rate found. bean-report seems to use the latest FX rate by default (the equivalent bean-query is shown below) -

  bean-query> SELECT date, narration, account, position, getprice(currency,"SGD", date), convert(position, "SGD")

I am trying to get the bean-report and fava to query in this way instead

  bean-query> SELECT date, narration, account, position, getprice(currency,"SGD", date), convert(position, "SGD", date)

Is there an easy way to get the income report to utilize the second way of conversion rather than the first?

Regards,

hoongshen

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/b1e43e57-065a-4fc2-907a-ef5c8c85c359%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Hoong-Shen Wong]

On Fri, Mar 30, 2018 at 6:37 AM, Martin Blais <bl...@furius.ca> wrote:

On Wed, Mar 28, 2018 at 11:33 PM, <hoongsh...@oromico.com> wrote:

In addition I was reading up the industry practice for treatment of items on the income statement and came across the following extracted at (https://www.accountingtools.com/articles/foreign-currency-translation.html):

• Assets and liabilities. Translate using the current exchange rate at the balance sheet date for assets and liabilities.
• Income statement items. Translate revenues, expenses, gains, and losses using the exchange rate as of the dates when those items were originally recognized.

That makes sense, though for tax purposes, depending on the jurisdiction, one might have to translate differently, but this sounds like a good default, recognizing income at the rate at the time of payment and valuing assets at the latest rate.

 

So far I find that:

- bean-report doesn't support currency conversions

FYI long-term the idea is to replace all those report implementations by SQL queries embellished as webpages.

 

- bean-query support currency conversions only on a per item basis, I can't construct a query that gives me the equivalent of the income statement entries but based on the exchange rate of the individual transaction dates

Doesn't something like this provide useful output: 

beancount> select position, convert(position, "USD", date), getprice(currency, "USD", date) where currency = "CAD"                                                                                                  

 

Actually the following works very well (your previous statement still gives me individual postings but I need to sum them up to get the total income line):

beancount> select sum(convert(position, "USD", date) where accounts ~ "Income:*"

Thanks a lot!

Regards,

hoongshen

[Martin Blais]

You should confirm whether using "date" uses the date prior to that day, or on that date if set. 

I think it's the latter, but I can't quite remember exactly.

--

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+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAB1%3DY91Y5Sv_tB%3D%3DUx696RtjSqMYFgTYhu8Mxwq11xfz4ZvnUA%40mail.gmail.com.

[Hoong-Shen Wong]

On Sat, Mar 31, 2018 at 10:58 AM, Martin Blais <bl...@furius.ca> wrote:

You should confirm whether using "date" uses the date prior to that day, or on that date if set. 

I think it's the latter, but I can't quite remember exactly.

I checked the query behaviour - it takes the rate on the date itself .

e.g. Simple, 2 rate example

FX rate on 2014-01-17 

FX rate on 2014-03-17

 

Transactions up to 2014-03-16 will use the first rate, transactions after 2014-03-17 will use the second rate.

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/iAq6y0sJe_w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhP-1Br8ZWqsEZWWBDRXJoKL3G_AXGjtqacdPrE6uTJ%2BgQ%40mail.gmail.com.

[Martin Blais]

On Sat, Mar 31, 2018 at 8:52 AM, Hoong-Shen Wong <hoongsh...@oromico.com> wrote:

On Sat, Mar 31, 2018 at 10:58 AM, Martin Blais <bl...@furius.ca> wrote:

You should confirm whether using "date" uses the date prior to that day, or on that date if set. 

I think it's the latter, but I can't quite remember exactly.

I checked the query behaviour - it takes the rate on the date itself .

e.g. Simple, 2 rate example

FX rate on 2014-01-17 

FX rate on 2014-03-17

 

Transactions up to 2014-03-16 will use the first rate, transactions after 2014-03-17 will use the second rate.

Awesome, that's the desired behavior; thanks for double-checking.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAB1%3DY91U5%3DFTyHK_2FRJTNqq-ECy21Ya2-_0TCHj4hRty4U%3DUA%40mail.gmail.com.