Question about currency conversion transaction

[Colin Fleming]

Hi all,

On a recent trip from New Zealand to the US, I incurred some expenses that I'm trying to import into beancount. Here's an example transaction:

   2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver / POS W/D 77.00USD @ 0.7907 conversion rate ;(INC. $2.43 CURRENCY CONVERSION COMMISSION) CHECKERAIRPORT TAXI STERLING"
     Assets:NZ:KiwiBank:Current  -99.81 NZD @ 0.7907 USD
     Expenses:Other:Bank:Fees      2.43 NZD            
     Expenses:Other:Travel:Taxi   77.00 USD

This way of expressing the transaction doesn't work. I have the full amount charged to my account (99.81 NZD), I have the exchange rate in USD that they used to calculate it, I have the original USD charge amount and I have the fee they charged me for the currency conversion. Deducting the fee from the full amount and then multiplying by the exchange rate gives the original USD amount, but I can't find a way to import these transactions without doing some manual calculation for each one. I had hoped that I could leave the exchange rate off and that beancount would work it out for me, but it refuses to balance. I can't put the conversion rate on the full amount because it doesn't balance either, I need to subtract the fee first. The only way I can seem to make it work is to calculate the inverse of the quoted exchange rate and add that to the USD entry, but that's tedious - I have quite a few of these transactions. Is there an easier way to achieve this?

Thanks,

Colin

[Martin Blais]

On Wed, Jun 10, 2015 at 7:02 AM, Colin Fleming <colin.ma...@gmail.com> wrote:

Hi all,

On a recent trip from New Zealand to the US, I incurred some expenses that I'm trying to import into beancount. Here's an example transaction:

   2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver / POS W/D 77.00USD @ 0.7907 conversion rate ;(INC. $2.43 CURRENCY CONVERSION COMMISSION) CHECKERAIRPORT TAXI STERLING"
     Assets:NZ:KiwiBank:Current  -99.81 NZD @ 0.7907 USD
     Expenses:Other:Bank:Fees      2.43 NZD            
     Expenses:Other:Travel:Taxi   77.00 USD

This way of expressing the transaction doesn't work.

That's right. It's attempting to balance some NZD's against some USD:

2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver / POS W/D 77.00USD @ 0.7907 con$

  Assets:NZ:KiwiBank:Current   -99.81 NZD @ 0.7907 USD  ; -78.919767 USD

  Expenses:Other:Bank:Fees       2.43 NZD               ;       2.43 NZD

  Expenses:Other:Travel:Taxi  77.0000 USD               ;      77.00 USD

;;; Residual: (-1.919767 USD, 2.43 NZD)

 

I have the full amount charged to my account (99.81 NZD), I have the exchange rate in USD that they used to calculate it, I have the original USD charge amount and I have the fee they charged me for the currency conversion. Deducting the fee from the full amount and then multiplying by the exchange rate gives the original USD amount, but I can't find a way to import these transactions without doing some manual calculation for each one.

You can either convert your NZD fee to USD using the same rate:

2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver"

  Assets:NZ:KiwiBank:Current  -99.81 NZD @ 0.7907 USD

  Expenses:Other:Bank:Fees      2.43 NZD @ 0.7907 USD

  Expenses:Other:Travel:Taxi   77.00 USD

Or do the reverse conversion:

2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver"

  Assets:NZ:KiwiBank:Current  -99.81 NZD

  Expenses:Other:Bank:Fees      2.43 NZD

  Expenses:Other:Travel:Taxi   77.00 USD @ 1/0.7907 NZD

Either of these work out.

Beancount doesn't yet have full arithmetic expressions, but I've implemented division just for this purpose.

(It is worthy of note that using the reverse exchange rate in this order has no impact on the price database; the prices are calculated both ways. Maybe that's more of an internal implementation detail, but my point is you shouldn't have to care so much which.)

 

I had hoped that I could leave the exchange rate off and that beancount would work it out for me, but it refuses to balance. I can't put the conversion rate on the full amount because it doesn't balance either, I need to subtract the fee first. The only way I can seem to make it work is to calculate the inverse of the quoted exchange rate and add that to the USD entry, but that's tedious - I have quite a few of these transactions. Is there an easier way to achieve this?

Using the examples I allude to above.

One of the few remaining "big ticket" items to do for Beancount is dealing with inventory booking better, which will allow all sorts of elision in many places. You should eventually be able to leave out the rate IMO, I want to make this possible. However, this is entangled with inventory booking concerns, so it'll all be done at once in the rework of the inventory booking logic (I have to make the parser support the output of incomplete transactions in order to make this work and interpolate and book inventories in a separate step, it's not trivial). See what I have in mind here (more details than you probably want to know):

http://furius.ca/beancount/doc/proposal-booking

 

 

 

Thanks,

Colin

--
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 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/CACK23AfJ%2BMicfNHS5WzHryjkNZayxcBP6YtauB_xGHWGrAXVKA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Colin Fleming]

Thanks Martin. After playing around with it for a while I ended up with something similar:

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve HYATT HOTELS GRAND WA. WASHINGTON"
  Assets:NZ:KiwiBank:Current  -700.30 NZD @ 0.7765 USD
  Assets:NZ:KiwiBank:Current  -17.50 NZD
  Expenses:Other:Bank:Fees    17.50 NZD
  Expenses:Other:Travel:Food

But any of the solutions you suggest look good too. I think the division solution is probably the cleanest, that's good to know.

Thanks very much for the excellent documentation for beancount, too - I haven't made my way through it all yet, but I run a project myself and I'm acutely aware of how much effort it is. It's greatly appreciated! One very nice feature would be to compile it into a Kindle book - I'm not sure how much effort that would be, though.

I was excited to see some Clojure code in the repo too, I'll play around with that and see if I can brush it up - Clojure is my main programming language and I'd love to use it for import scripts.

Cheers,

Colin

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhMCbLXGYSYKLz_mYbBsALkap48kP-A2Uvz6Km83U6SX-A%40mail.gmail.com.

[Colin Fleming]

Another question related to this - in the bean-web interface I can get the Income Statement which shows the totals for all the categories. I'm using this for the accounts for a small company I'm running, and I'd like to transcribe those values to the financial statements summary for my company tax return. However to do that I need the NZD values, and now that I have the conversion correctly accounted for it shows me a combination of currencies. There doesn't seem to be a way to achieve this either in the web UI or using bean-report - do I need to dig into the query language for this?

[Martin Blais]

On Wed, Jun 10, 2015 at 6:20 PM, Colin Fleming <colin.ma...@gmail.com> wrote:

Thanks Martin. After playing around with it for a while I ended up with something similar:

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve HYATT HOTELS GRAND WA. WASHINGTON"
  Assets:NZ:KiwiBank:Current  -700.30 NZD @ 0.7765 USD
  Assets:NZ:KiwiBank:Current  -17.50 NZD
  Expenses:Other:Bank:Fees    17.50 NZD
  Expenses:Other:Travel:Food

But any of the solutions you suggest look good too. I think the division solution is probably the cleanest, that's good to know.

Thanks very much for the excellent documentation for beancount, too - I haven't made my way through it all yet, but I run a project myself and I'm acutely aware of how much effort it is. It's greatly appreciated! One very nice feature would be to compile it into a Kindle book - I'm not sure how much effort that would be, though.

Thanks Colin, it is indeed very time-consuming, many many entire weekends have been sunk hacking and writing on this. I don't really know why I do it. I think I just don't like not finishing things.

About compiling it to a Kindle book: almost there, I can do PDF now. 

I wrote a script that downloads my index file and that fetches all the documents linked from it, and can download all the docs to PDF and put them together in a single file:

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/docs/download_docs.py?at=default

Here, I just ran it, here's what it looks like:

https://drive.google.com/file/d/0Bxq3M95KLnV5N3VYNHBNLXItZUE/view?usp=sharing

I should find a more permanent place to share this eventually.

I was excited to see some Clojure code in the repo too, I'll play around with that and see if I can brush it up - Clojure is my main programming language and I'd love to use it for import scripts.

Originally I thought I might use Clojure for this project - I love Clojure, have been dabbling for a while, I go to the Conj every couple of years - but in practice it's a bit of a moving target (that community has somewhat of a disregard for software dependencies and things break a lot) and when I need to actually _use_ Beancount (that is, without tinkering with it) having to fiddle with setup is just not an option for me, I just need it to work, I really do. With Python it'll always "just work", the environment is much more stable.

That being said, I encourage you to build Clojurethings for Beancount - or even reimplement the whole thing in Clojure! - that could be fun indeed.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CACK23AeRa6bRsAQVMm%3DmGeY_NVwwDzS6XxsjTHBbhMdDXPou4Q%40mail.gmail.com.

[Colin Fleming]

Sorry for the late reply, I've been travelling.

Thanks Colin, it is indeed very time-consuming, many many entire weekends have been sunk hacking and writing on this. I don't really know why I do it. I think I just don't like not finishing things.

But you know these things are never finished, right? :-)

 

About compiling it to a Kindle book: almost there, I can do PDF now. 

I wrote a script that downloads my index file and that fetches all the documents linked from it, and can download all the docs to PDF and put them together in a single file:

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/docs/download_docs.py?at=default

Here, I just ran it, here's what it looks like:

https://drive.google.com/file/d/0Bxq3M95KLnV5N3VYNHBNLXItZUE/view?usp=sharing

Wow, nice - I've bought smaller books than that! The one thing that would be useful would be for the links to work within the documents - currently they still point to the website. This is slightly complicated by the redirect you're using from furius.ca, it might require a mapping from the redirected name to the Google doc. Can the docs be exported as HTML rather than PDF?

 

Originally I thought I might use Clojure for this project - I love Clojure, have been dabbling for a while, I go to the Conj every couple of years - but in practice it's a bit of a moving target (that community has somewhat of a disregard for software dependencies and things break a lot) and when I need to actually _use_ Beancount (that is, without tinkering with it) having to fiddle with setup is just not an option for me, I just need it to work, I really do. With Python it'll always "just work", the environment is much more stable.

Oh nice - maybe I'll see you at the conj one of these days, my main project is https://cursiveclojure.com. I think the stability of a lot of the projects has increased recently but it can be a problem depending on what you're using (CLJS always moves pretty fast). But particularly if you want outside contributions Python is probably a better choice anyway.

 

That being said, I encourage you to build Clojurethings for Beancount - or even reimplement the whole thing in Clojure! - that could be fun indeed.

Indeed, although way outside the possibilities of my paltry spare time!

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNnTGsgPQsgk2jgfDoB0KmRPTgOW%2B-QFKNsUNVDYy722w%40mail.gmail.com.

[Martin Blais]

About compiling it to a Kindle book: almost there, I can do PDF now. 

I wrote a script that downloads my index file and that fetches all the documents linked from it, and can download all the docs to PDF and put them together in a single file:

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/docs/download_docs.py?at=default

Here, I just ran it, here's what it looks like:

https://drive.google.com/file/d/0Bxq3M95KLnV5N3VYNHBNLXItZUE/view?usp=sharing

Wow, nice - I've bought smaller books than that! The one thing that would be useful would be for the links to work within the documents - currently they still point to the website. This is slightly complicated by the redirect you're using from furius.ca, it might require a mapping from the redirected name to the Google doc. Can the docs be exported as HTML rather than PDF?

Yes, but Google Docs "published" HTML does not look nearly nice enough. It doesn't look anything like the page rendered on Google. It's a shame, I would have rather exported to that. I hope one day it's improved.

I'd also like to be able to render to an e-book format.

 

Originally I thought I might use Clojure for this project - I love Clojure, have been dabbling for a while, I go to the Conj every couple of years - but in practice it's a bit of a moving target (that community has somewhat of a disregard for software dependencies and things break a lot) and when I need to actually _use_ Beancount (that is, without tinkering with it) having to fiddle with setup is just not an option for me, I just need it to work, I really do. With Python it'll always "just work", the environment is much more stable.

Oh nice - maybe I'll see you at the conj one of these days, my main project is https://cursiveclojure.com.

Awesome! 

 

I think the stability of a lot of the projects has increased recently but it can be a problem depending on what you're using (CLJS always moves pretty fast). But particularly if you want outside contributions Python is probably a better choice anyway.

Sure, please do reach out next year.

I was at the one in DC in November.

[Colin Fleming]

Whoops, I've just come back to this, and in all the excitement about Clojure I just realised I never got an answer for this question - is there any advice here?

[Colin Fleming]

And an additional question - for transactions where I have a receipt which I'd like to associate with the transaction, what's the best way to achieve this? The document directive doesn't seem to be associated with a particular transaction - should I just use metadata for this?

[Martin Blais]

On 29 June 2015 at 13:17, Colin Fleming <colin.ma...@gmail.com> wrote:

Whoops, I've just come back to this, and in all the excitement about Clojure I just realised I never got an answer for this question - is there any advice here?

On 11 June 2015 at 00:32, Colin Fleming <colin.ma...@gmail.com> wrote:

Another question related to this - in the bean-web interface I can get the Income Statement which shows the totals for all the categories. I'm using this for the accounts for a small company I'm running, and I'd like to transcribe those values to the financial statements summary for my company tax return.

That's problem number 1: In the evolution of this software the balance sheet and income statements were developed solely for the web interface, so those reports only support HTML output. I have since then moved the code that generates these to the beancount.reports layer which supports multiple output formats and bean-web has become a thin shell that serves the reports layer as formatted to HTML. The plan is to make the balance sheet and income statement reports support csv and text output formats so you can import them in excel or whatever, but I haven't done it yet. It's up there on my radar though.

 

However to do that I need the NZD values, and now that I have the conversion correctly accounted for it shows me a combination of currencies. There doesn't seem to be a way to achieve this either in the web UI or using bean-report - do I need to dig into the query language for this?

Are you using the "operating_currencies" option to let it know you're interested in breaking out NZD in its own dedicated column?

Also, can you be more specific about some line of output that does not appear the way you'd like it?  There are a number of decisions to be made about desired output for those statements, e.g. do we show the cost basis, number of units or market value, or do we try to automate price conversions to translate all number to one of the operating currencies? There should eventually be parameters as input to the production of those reports.

I haven't quite figured out yet all the options that should be there and how they influence each other. Personal I'm hoping to create a little filter program that accept the output of a bean-query SQL query and render it as an income statement and balance sheet, so that these details could be done away with: you'd write the desired query and the output would be processed by this external tool to produce a nicely formatted income statement and balance sheet instead of using the special-purpose reporting code that currently generates those pages. I think it's possible (also high on my radar), and it would avoid creating a bunch of annoying options with long names and unclear semantics.

On Mon, Jun 29, 2015 at 9:28 AM, Colin Fleming <colin.ma...@gmail.com> wrote:

And an additional question - for transactions where I have a receipt which I'd like to associate with the transaction, what's the best way to achieve this? The document directive doesn't seem to be associated with a particular transaction - should I just use metadata for this?

Document directives indeed aren't designed to be associated with a particular transactions. Generally documents potentially contain multiple transactions.

If you want to associate a document to a particular transaction, use metadata. On some of my transactions I use the "doc" key with a string that is just the filename of the document. I'm not really using those yet, it's write-only so far, but you can script something if you need it, that's what metadata is for.

I hope this helps,

[Colin Fleming]

On 30 June 2015 at 00:34, Martin Blais <bl...@furius.ca> wrote:

Are you using the "operating_currencies" option to let it know you're interested in breaking out NZD in its own dedicated column?

No, I'm not - I hadn't seen that. It doesn't appear in the Language Syntax Guide, BTW. However this doesn't do what I need - it just breaks it out into a column but doesn't actually perform any conversion.

 

Also, can you be more specific about some line of output that does not appear the way you'd like it?

Sure. I actually came up with a workaround where I just commented out the currency calculation, like:

2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver / POS W/D 77.00USD @ 0.7907 conversion rate ;(INC. $2.43 CURRENCY CONVERSION COMMISSION) CHECKERAIRPORT TAXI STERLING"
  Assets:NZ:KiwiBank:Current  -99.81 NZD

  Expenses:Other:Bank:Fees    2.43 NZD
  Expenses:Other:Travel:Taxi  ;77.00 USD @ 1/0.7907 NZD

  receipt: "washington-airport-taxi.pdf"

That gives me what I need, which is all values in NZD. If I uncomment that conversion, then I get:

select date, position, balance where date >= 2014-04-01 and date < 2015-04-01 and account ~ "Expenses:Other";

   date     position     balance 
---------- ----------- -----------
2014-09-28 2653.00 NZD 2653.00 NZD
2014-09-30    0.20 NZD 2653.20 NZD

<etc etc...>
2014-11-19   77.00 USD 3358.12 NZD
                         77.00 USD
<etc etc...>
2015-03-03 2516.00 NZD 6834.43 NZD
                         77.00 USD

So this gives me the balance in a mix of USD and NZD, which is no good for me - the tax return box only accepts NZD.

There's an additional issue - if I do provide a conversion and then somehow convert the balance to NZD, rounding is a problem. What I really need is for the expense value to be the exact NZD amount calculated by taking into account the remaining transaction legs, which is exactly what I get by leaving the conversion commented out. I'm starting to think that that is really what I want, and that maybe I don't care about currencies.

 

Personal I'm hoping to create a little filter program that accept the output of a bean-query SQL query and render it as an income statement and balance sheet, so that these details could be done away with: you'd write the desired query and the output would be processed by this external tool to produce a nicely formatted income statement and balance sheet instead of using the special-purpose reporting code that currently generates those pages.

This would be really great. I actually looked into how to produce a custom report which I could just copy to the tax return fields, but it looks trickier than I have time for right now. For now I have various queries saved for things like GST periods and the various fields in the financial statements summary, it would be nice to be able to automate the extraction of those values somehow.

 

If you want to associate a document to a particular transaction, use metadata. On some of my transactions I use the "doc" key with a string that is just the filename of the document. I'm not really using those yet, it's write-only so far, but you can script something if you need it, that's what metadata is for.

Right, that's what I've started doing - example above.

Thanks for the advice!

[Martin Blais]

On Tue, Jun 30, 2015 at 7:01 AM, Colin Fleming <colin.ma...@gmail.com> wrote:

On 30 June 2015 at 00:34, Martin Blais <bl...@furius.ca> wrote:

Are you using the "operating_currencies" option to let it know you're interested in breaking out NZD in its own dedicated column?

No, I'm not - I hadn't seen that. It doesn't appear in the Language Syntax Guide, BTW.

Thanks for letting me know. It appears in the options reference but it's true I haven't dedicated a section to this topic and it deserves it. I'll do this.

https://bitbucket.org/blais/beancount/issue/62/document-operating_currencies-in-language

 

However this doesn't do what I need - it just breaks it out into a column but doesn't actually perform any conversion.

That's right.

 

Also, can you be more specific about some line of output that does not appear the way you'd like it?

Sure. I actually came up with a workaround where I just commented out the currency calculation, like:

2014-11-19 * "POS W/D 77.00USD @ 0.7907 conver / POS W/D 77.00USD @ 0.7907 conversion rate ;(INC. $2.43 CURRENCY CONVERSION COMMISSION) CHECKERAIRPORT TAXI STERLING"
  Assets:NZ:KiwiBank:Current  -99.81 NZD
  Expenses:Other:Bank:Fees    2.43 NZD
  Expenses:Other:Travel:Taxi  ;77.00 USD @ 1/0.7907 NZD
  receipt: "washington-airport-taxi.pdf"

You should _definitely_ never have to hack your input file in order to get the results you want.

 

That gives me what I need, which is all values in NZD. If I uncomment that conversion, then I get:

select date, position, balance where date >= 2014-04-01 and date < 2015-04-01 and account ~ "Expenses:Other";

   date     position     balance 
---------- ----------- -----------
2014-09-28 2653.00 NZD 2653.00 NZD
2014-09-30    0.20 NZD 2653.20 NZD

<etc etc...>
2014-11-19   77.00 USD 3358.12 NZD
                         77.00 USD
<etc etc...>
2015-03-03 2516.00 NZD 6834.43 NZD
                         77.00 USD

So this gives me the balance in a mix of USD and NZD, which is no good for me - the tax return box only accepts NZD.

Have you tried this?

  select date, convert(position, "NZD"), convert(balance, "NZD") where date >= 2014-04-01 and date < 2015-04-01 and account ~ "Expenses:Other";

I think this does more or less what you want (minus the ugly rendering of too many digits).

I'm aware the query isn't super well documented yet.

I'm also not quite sure yet of all the use cases and needs for currency conversion; your reporting this use case is useful.

(I plan to revisit the language and make it better and better documented.)

There's an additional issue - if I do provide a conversion and then somehow convert the balance to NZD, rounding is a problem. 

What I really need is for the expense value to be the exact NZD amount calculated by taking into account the remaining transaction legs, which is exactly what I get by leaving the conversion commented out. I'm starting to think that that is really what I want, and that maybe I don't care about currencies.

Ah I think I see what you mean. You don't want that third leg to "compute to 77.00 * 1/0.7907", but rather "compute to -(-99.81 + 2.43)". Is that right?

But you also want to record & deposit USD expenses of 77.00 USD to that account.

It's not obvious to me how I would generalize that.

This is a quirky request indeed.

I think you could easily write a plugin that goes through your transactions and converts the amounts for postings to the subset of accounts you want, e.g. Expenses:Other:* + posting in USD. That would do it.

 

Personal I'm hoping to create a little filter program that accept the output of a bean-query SQL query and render it as an income statement and balance sheet, so that these details could be done away with: you'd write the desired query and the output would be processed by this external tool to produce a nicely formatted income statement and balance sheet instead of using the special-purpose reporting code that currently generates those pages.

This would be really great. I actually looked into how to produce a custom report which I could just copy to the tax return fields, but it looks trickier than I have time for right now. For now I have various queries saved for things like GST periods and the various fields in the financial statements summary, it would be nice to be able to automate the extraction of those values somehow.

You may want to look at the realization example I just wrote in the design doc this weekend:

https://docs.google.com/document/d/1N7HDXuNWgLG2PqFS4Kkgv5LzAAtU6c97UVNT7tdTIjA/edit#heading=h.318taqny5rpg

 

I think you can get balances for a particular account directly like this, it's a one-liner:

  realization.get(real_root, 'Expenses:Other:Bank:Fees').balance

[Colin Fleming]

On 30 June 2015 at 17:18, Martin Blais <bl...@furius.ca> wrote:

It appears in the options reference but it's true I haven't dedicated a section to this topic and it deserves it.

Where is the options reference? I can't find it in the doc TOC and it's not in the Language Syntax.

Related to this, a bit of feedback on the Google Docs documentation - as a user, it's quite difficult to navigate. It insists on opening linked pages in a new tab which makes going back unintuitive, and I can't search it - the lack of a directory structure means that I can't even use a site: search in Google. I'd really like to see a standard website, even if you edit in Docs and then export somehow - I totally understand that WYSIWYG is nice for the editing.

Have you tried this?

  select date, convert(position, "NZD"), convert(balance, "NZD") where date >= 2014-04-01 and date < 2015-04-01 and account ~ "Expenses:Other";

I think this does more or less what you want (minus the ugly rendering of too many digits).

That does indeed do what I want, thanks!

 

Ah I think I see what you mean. You don't want that third leg to "compute to 77.00 * 1/0.7907", but rather "compute to -(-99.81 + 2.43)". Is that right?

But you also want to record & deposit USD expenses of 77.00 USD to that account.

Right, which is exactly what I get if I leave off the currency conversion on the posting. In general, that's how much the cost to me actually was, but for larger transactions (of which I have a couple) rounding means that this value and the conversion via the quoted rate are frequently not equal. This is related to the rendering of too many digits in the convert() example above. I also had to fiddle with the tolerance a couple of times for several of my postings to get them to balance. For example:

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve / POS W/D 543.77USD @ 0.7765 conversion rate ;(INC. $17.50 CURRENCY CONVERSION COMMISSION) HYATT HOTELS GRAND WA. WASHINGTON"
  Assets:Cash:KiwiBank:Current  -717.8 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @ 1/0.7765 NZD
  receipt: "washington-grand-hyatt.pdf"

Here, this transaction doesn't balance if I have the KiwiBank leg as -717.80. I could probably also have used tolerance directives to work around it. But this is the same problem - the actual cost to me was 717.80 - 17.50, and the conversion is really only indicative of how that value was calculated.

 

It's not obvious to me how I would generalize that.

This is a quirky request indeed.

Really? I guess no-one thinks of their own requests as quirky :-). Isn't this a problem for anyone filling in a tax return from data including other currencies?

Again, I'm starting to think that perhaps I'm better off not worrying about the currencies at all - it seems wrong to throw away that information, but the IRD only cares about NZD which means that mostly I only care about that too.

 

You may want to look at the realization example I just wrote in the design doc this weekend:

https://docs.google.com/document/d/1N7HDXuNWgLG2PqFS4Kkgv5LzAAtU6c97UVNT7tdTIjA/edit#heading=h.318taqny5rpg

 

I think you can get balances for a particular account directly like this, it's a one-liner:

  realization.get(real_root, 'Expenses:Other:Bank:Fees').balance

Thanks, I'll study that when I get a moment and see if I can make it do what I want. Can I collapse accounts like this? For example, the tax return just has a field for "Other expenses", the groupings under that (bank fees or whatever) are just for my information. In bean-query I do that using account ~ "Expenses:Other", which works very well.

Thanks for all the help!

[Martin Blais]

On Thu, Jul 2, 2015 at 5:34 AM, Colin Fleming <colin.ma...@gmail.com> wrote:

On 30 June 2015 at 17:18, Martin Blais <bl...@furius.ca> wrote:

It appears in the options reference but it's true I haven't dedicated a section to this topic and it deserves it.

Where is the options reference? I can't find it in the doc TOC and it's not in the Language Syntax.

There's a section at the bottom of the language syntax doc called "Options" and a link to the doc.

But that's good feedback nonetheless: I've added a link to it from the top-level index.

Look for "Beancount Options Reference".

Related to this, a bit of feedback on the Google Docs documentation - as a user, it's quite difficult to navigate. It insists on opening linked pages in a new tab which makes going back unintuitive,

You can use tabs.

 

and I can't search it - the lack of a directory structure means that I can't even use a site: search in Google.

Have you tried searching from Google Drive itself?

Go to drive.google.com, search including the word "Beancount" (all the document names begin with "Beancount - ").

It would be nicer if I could somehow create a group of documents to search from; I'll look into it, there must be a way. I might be able to make a folder public or something like that.

 

I'd really like to see a standard website, even if you edit in Docs and then export somehow -

I would love to export to HTML, but so far the conversions Docs offers are really ugly. The output doesn't look anything like the super nice output you get when editing. I've written a script that can bake a single large PDF file, would that be useful?  I could probably modify it to export to the ugly HTML conversion Google Docs provides.

 

I totally understand that WYSIWYG is nice for the editing.

Actually, I don't care that much about WYSIWIG, the main thing I love about Docs is the commenting and collaboration, and no compilation (edit and you're done publishing, it's instant). The benefits I've gotten from these aspects alone far outweigh any of the numerous shortcomings of using Google Docs for such a project. I can assure you that the docs you're reading would simply not exist if not for the existence of it.

The main thing that drives me crazy is the fact I can't send readers to the "View Only" mode by default. So I get a lot of accidental "drive by edits", people who inadvertently make suggestions insert whitespace, for example.

I have some ideas for making it better, wrapping it up in an iframe and doing some redirection magic, but that's a separate project I haven't started yet.

Have you tried this?

  select date, convert(position, "NZD"), convert(balance, "NZD") where date >= 2014-04-01 and date < 2015-04-01 and account ~ "Expenses:Other";

I think this does more or less what you want (minus the ugly rendering of too many digits).

That does indeed do what I want, thanks!

 

Ah I think I see what you mean. You don't want that third leg to "compute to 77.00 * 1/0.7907", but rather "compute to -(-99.81 + 2.43)". Is that right?

But you also want to record & deposit USD expenses of 77.00 USD to that account.

Right, which is exactly what I get if I leave off the currency conversion on the posting. In general, that's how much the cost to me actually was, but for larger transactions (of which I have a couple) rounding means that this value and the conversion via the quoted rate are frequently not equal. This is related to the rendering of too many digits in the convert() example above. I also had to fiddle with the tolerance a couple of times for several of my postings to get them to balance. For example:

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve / POS W/D 543.77USD @ 0.7765 conversion rate ;(INC. $17.50 CURRENCY CONVERSION COMMISSION) HYATT HOTELS GRAND WA. WASHINGTON"
  Assets:Cash:KiwiBank:Current  -717.8 NZD
  Expenses:Other:Bank:Fees    17.50 NZD
  Expenses:Other:Travel:Food  543.77 USD @ 1/0.7765 NZD
  receipt: "washington-grand-hyatt.pdf"

Here, this transaction doesn't balance if I have the KiwiBank leg as -717.80. I could probably also have used tolerance directives to work around it. But this is the same problem - the actual cost to me was 717.80 - 17.50, and the conversion is really only indicative of how that value was calculated.

I'll offer three existing solutions:

 

 

It's not obvious to me how I would generalize that.

This is a quirky request indeed.

Really? I guess no-one thinks of their own requests as quirky :-). Isn't this a problem for anyone filling in a tax return from data including other currencies?

Again, I'm starting to think that perhaps I'm better off not worrying about the currencies at all - it seems wrong to throw away that information, but the IRD only cares about NZD which means that mostly I only care about that too.

 

You may want to look at the realization example I just wrote in the design doc this weekend:

https://docs.google.com/document/d/1N7HDXuNWgLG2PqFS4Kkgv5LzAAtU6c97UVNT7tdTIjA/edit#heading=h.318taqny5rpg

 

I think you can get balances for a particular account directly like this, it's a one-liner:

  realization.get(real_root, 'Expenses:Other:Bank:Fees').balance

Thanks, I'll study that when I get a moment and see if I can make it do what I want. Can I collapse accounts like this? For example, the tax return just has a field for "Other expenses", the groupings under that (bank fees or whatever) are just for my information. In bean-query I do that using account ~ "Expenses:Other", which works very well.

Thanks for all the help!

--

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 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/CACK23Af1xM7P_V1Vwh348YT8dU%2BhwwyFKDk9aEnApvi_Xq8j3g%40mail.gmail.com.

[Martin Blais]

(Continued - Sorry typing on a 11" MBA in a cafe, fat-fingered send email with an overly sensitive touchpad)

I'll offer three existing solutions:

(1) Specify a most precise rate. This is what I do (Yes, I fiddle to get the rate right, it's not ideal):

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve / POS W/D 543.77USD @ 0.7765 conversion rate ;(INC. $17.50 CURRENCY CONVERSION COMMISSION) HYATT HOTELS GRAND WA. WASHINGTON"

  Assets:Cash:KiwiBank:Current  -717.80 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @ 1/0.776485 NZD

  receipt: "washington-grand-hyatt.pdf"

(2) Use a total cost conversion with @@:

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve / POS W/D 543.77USD @ 0.7765 conversion rate ;(INC. $17.50 CURRENCY CONVERSION COMMISSION) HYATT HOTELS GRAND WA. WASHINGTON"

  Assets:Cash:KiwiBank:Current  -717.80 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @@ 700.300 NZD

  receipt: "washington-grand-hyatt.pdf"

To get the sum, I first run bean-check without the @@ and look at the NZD balance so I don't have to calculate it. Then I insert that. The problem here is that the rate is synthetic (well not sure if that's a problem, but something's got to give).

(3) Loosen up the tolerance multiplier:

option "inferred_tolerance_multiplier" "2.0"

2014-11-26 * "POS W/D 543.77USD @ 0.7765 conve / POS W/D 543.77USD @ 0.7765 conversion rate ;(INC. $17.50 CURRENCY CONVERSION COMMISSION) HYATT HOTELS GRAND WA. WASHINGTON"

  Assets:Cash:KiwiBank:Current  -717.80 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @ 1/0.7765 NZD

  receipt: "washington-grand-hyatt.pdf"

Neither of these is ideal but would you rather the transactions not balance?

Do you have ideas for making this easier? (Beyond the plugin suggestion from previously?)

I'm open to suggestions.

 

It's not obvious to me how I would generalize that.

This is a quirky request indeed.

Really? I guess no-one thinks of their own requests as quirky :-). Isn't this a problem for anyone filling in a tax return from data including other currencies?

I don't think it is.

I think you're worrying about rounding way too much. Your government probably has a note somewhere that you must use their central bank rate on the day of the transaction, or at the end of the year, instead of the exact one you incurred. You'd have to write a script to do this precisely. Fuggetabout it. If I were you, I'd just sum up the converted amounts and not worry about a <1 NZD difference over one year's worth of expenses (or even more). I'm pretty sure an auditor wouldn't get on your case about that either - despite the appearances, accounting people can afford a lot more approximation than engineers would like to think. I've learned this by spending years watching my accountant deal with the government directly, seeing him make phone calls to govt agents and what-not. They're reasonable people, they always have reasons for what they do. It should also be obvious you weren't attempting to defraud the kiwis by a piddly dollar. In my experience even in an audit there's some common sense in taxation departments.

Again, I'm starting to think that perhaps I'm better off not worrying about the currencies at all - it seems wrong to throw away that information, but the IRD only cares about NZD which means that mostly I only care about that too.

Here's yet another idea: You could write a script that for all of the eligible expenses (perhaps tag them?) you would accumulate the non-expenses legs totals in order to get the precise NZD amounts and at the end invert the sign. That would work too. Probably 30 lines of code or something.

 

 

You may want to look at the realization example I just wrote in the design doc this weekend:

https://docs.google.com/document/d/1N7HDXuNWgLG2PqFS4Kkgv5LzAAtU6c97UVNT7tdTIjA/edit#heading=h.318taqny5rpg

 

I think you can get balances for a particular account directly like this, it's a one-liner:

  realization.get(real_root, 'Expenses:Other:Bank:Fees').balance

Thanks, I'll study that when I get a moment and see if I can make it do what I want. Can I collapse accounts like this?

IIRC there's an iterator function that allows you to iterate over all postings of an account and a subaccount.

 

For example, the tax return just has a field for "Other expenses", the groupings under that (bank fees or whatever) are just for my information. In bean-query I do that using account ~ "Expenses:Other", which works very well.

Thanks for all the help!

No worries mate, glad you reported your issues.

[Colin Fleming]

Ok, thanks for the information about the site. Something like a downloadable PDF would be good, or as you say a search (perhaps "Beancount" and owned by you, or something?) would be great - that would help a lot.

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

[Colin Fleming]

On 2 July 2015 at 19:50, Martin Blais <bl...@furius.ca> wrote:

I'll offer three existing solutions:

<snip>

 

Neither of these is ideal but would you rather the transactions not balance?

Do you have ideas for making this easier? (Beyond the plugin suggestion from previously?)

I'm open to suggestions.

The only thing I could think of would be to infer the exchange rate, basically do automatically what you're doing by fiddling the rate manually. Could you automatically infer the exchange rate if I provided:

2014-11-26 * "HYATT HOTELS GRAND WA. WASHINGTON"

  Assets:Cash:KiwiBank:Current  -717.80 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @/

  receipt: "washington-grand-hyatt.pdf"

Where @/ (or whatever symbol) means I'd like the rate calculated. That seems to be the free variable here since the actual USD and NZD amounts are all known exactly.

 

I think you're worrying about rounding way too much.

I think you're right, and I must admit this has diverged a little into "I wonder how it should work" rather than "I need it to work like this" :-). Is there a query function I can use to round the convert(balance, "NZD") values to 2DP? That would make the output more readable.

IIRC there's an iterator function that allows you to iterate over all postings of an account and a subaccount.

Thanks, I'll check it out and let you know how I get on.

[Martin Blais]

On Thu, Jul 2, 2015 at 6:38 PM, Colin Fleming <colin.ma...@gmail.com> wrote:

On 2 July 2015 at 19:50, Martin Blais <bl...@furius.ca> wrote:

I'll offer three existing solutions:

<snip>

 

Neither of these is ideal but would you rather the transactions not balance?

Do you have ideas for making this easier? (Beyond the plugin suggestion from previously?)

I'm open to suggestions.

The only thing I could think of would be to infer the exchange rate, basically do automatically what you're doing by fiddling the rate manually. Could you automatically infer the exchange rate if I provided:

2014-11-26 * "HYATT HOTELS GRAND WA. WASHINGTON"

  Assets:Cash:KiwiBank:Current  -717.80 NZD

  Expenses:Other:Bank:Fees    17.50 NZD

  Expenses:Other:Travel:Food  543.77 USD @/

  receipt: "washington-grand-hyatt.pdf"

Where @/ (or whatever symbol) means I'd like the rate calculated. That seems to be the free variable here since the actual USD and NZD amounts are all known exactly

Something similar to this is already proposed in the inventory booking improvements:

http://furius.ca/beancount/doc/proposal-booking

I'm hoping to have this implemented by end-of-summer.

 

I think you're worrying about rounding way too much.

I think you're right, and I must admit this has diverged a little into "I wonder how it should work" rather than "I need it to work like this" :-). Is there a query function I can use to round the convert(balance, "NZD") values to 2DP? That would make the output more readable.

There's none at the moment, but it should be easy to hack in query_env.