Foreign currencies not being converted in reports
(Moneydance 2010r3 (751) under MacOS)
I'm trying to create a budget report based on the expenses I had during my recent trip to the UK. My base currency is the euro and I have an account "Cash Br Pds" in the appropriate currency.
The behavior I'm seeing is that the transactions that I entered in the pounds account are not converted to euros in the budget report, or in any other report that I've tried, either. Let's say I paid £10 for lunch, the amount will show up as €10 in the reports.
I sure hope somebody can tell me what's going on here because I don't look forward to having to work around this behavior, say in a spreadsheet, in order to get meaningful information.
Second question: Assuming that I can get the pounds converted to euros, I'm curious as to what exchange rate will be used. The rate at the time off exchange? The most recent rate of exchange in the currency list?
Comments are currently closed for this discussion. You can start a new one.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
1 Posted by Ben Spencer on 05 Oct, 2010 07:00 PM
Hi
What is the currency of the category?
The currency in the reports depends ont he report you are running. The detailed income and expense report will display the transactions in the currency of the expense or income category. The final total of the report is converted to the base currency using the "Current Price". Where the "Current Price" is the value in the "Current Price" field that you see when you select Tools->Edit Currencies/Exchange Rate.... Typically (unless you have manually changed it) the "Current Price" is equal to the most recently downloaded price.
Ben Spencer
2 Posted by mawyn on 06 Oct, 2010 12:35 PM
Thanks for the explanation, Ben. What you said about "Current Price" was exactly what I was wondering about the exchange rate used in calculations. Your whole explanation sounds exactly like the behavior I'm looking for.
My problem is that the items in pounds are not converted. Let me give you an example. I just made some screen shots of a budget report for the 26th of August.
I have a budget category Leisure: Eat Out. The budget report shows total expense for this particular day of 59.38. The individual expenses were:
44.03 in my VISA account, which is in euros
11.35 which was a snack, recorded in my Pounds Cash account
4 which was a tip, also recorded in my Pounds Cash account.
The amounts specified in the Pounds Cash account are the actual pound amounts I paid. However, 44.03 + 11.35 + 4 = 59.38, which is the total expenditures shown in the budget report. Instead of converting £11.35 and £4 to euros, it just added the pound amounts.
Confusing, isn't it?
Matthew
3 Posted by mawyn on 06 Oct, 2010 12:37 PM
Oh, I forgot to answer you question about category. All of my categories are in euros. Or are you maybe talking about budget categories?
Matthew
4 Posted by Ben Spencer on 08 Oct, 2010 07:20 PM
I have not been able to reproduce the result you are getting.
What is your base currency? I m assuming euros perhaps I am incorrect.
I created an asset account called cash with a currency GBP.
My base currency was euros.
All categories are in euros.
Transactions I enter in the cash account are entered in GBP and are shown in the budget report converted to euros.
Would you be willing to show me some screenshots. Or better yet a cut down copy of your data file that demonstrates the problem.
Ben Spencer
Moneydance Support
5 Posted by mawyn on 08 Oct, 2010 08:47 PM
You are correct, my base currency is euro. As far as I know, all my categories are in euros because there's no reason for that not to be the case. Besides, I didn't even realize that categories can be assigned a currency.
Let me start by just attaching some reports.
First I have a Search Report that shows all of the expenses in the £ account for Sep 1. Here you can see that the amounts have been converted to euros for the total given of €32.38. The -21 and -5.48 are the actual £ amounts. That gives us an exchange rate of €1.219 per £, which was realistic at the time although I'm not quite sure where this comes from.
Then there's the Detailed Income and Expenses Report for the Freizeit:Essen category on Sep 1. Translated that's Leisure:Eating Out. Here you see the same amounts, 21 and 5.48, added without conversion to the other amounts from the Bargeld ("Cash") account kept in euros. The total for this category is €57.33, although it should be more since the two £ transactions should be converted to a larger amount.
Finally there is the Budget Report for Sep 1. Looking at the Freizeit:Essen line you see the same unconverted amount of €57.33
Three more remarks:
1) I'm using the MacOS version.
2) I've noticed that it makes a difference how the Currency/Security History window is set. The first line shows the currency name. The second line, entitled "Currency", shows that currency being compared to, which can be changed. When I enter new exchange rates in this window I have to set this second line to Euros. When I leave it like that, no conversion occurs in the reports. However, if I change this second line to British Pound, conversion does occur, at least in some of the reports.
What makes it very difficult to track this problem down is that there are so many corners of the picture to keep track of: the accounts, the various reports, the settings in this currency history window.
3) Above you mention the "Current Price". I assume you're referring to the third line in the Currency/Security History window I was just talking about. In my window this line is called "Rate".
Now I have the problem that this line can't be changed when the second line ("Currency") is set to British Pounds. Any changes I enter are forgotten when I close the window and all the values in the window are, logically, "1", since the exchange rate from £ to £ is always one. However this is the window setting that provides me with conversion in at least some of my reports.
When I change the second line ("Currency") to Euros, I can make changes to the third line ("Rate") and they are remembered. Unfortunately, as far as I can tell at the moment, with the window set like this, I get no conversions in my reports.
This is all very complicated and confusing. I hope you can follow me. My guess is that I will need to provide you with an excerpt of my file so that you can try all of this stuff out on your own. It's getting late now, though, so I can't do it right now. Besides, I'll first need to figure out how to create this excerpt.
Thanks a lot for your help!
Matthew
6 Posted by mawyn on 10 Oct, 2010 04:27 PM
OK, that last report was quite long and possibly confusing. I did some more research today but this time I tried to make things simple.
I added two fictitious transactions on today's date, one in Danish crowns and one in £. I used a category that I don't normally use so no other transactions got in the way of the comparison.
Then I performed a search report on the current day and a budget report limited to the current day. These used the exchange rates that were already in the currency lists. Both reports worked as expected: The search report converted the two transactions to euros in the right column and the total was in euros - and the budget report showed the same total as was in the search report.
I then altered the current rate to .1 for crowns and to 1 for £ - to make the math easy. The results in the search report were exactly as expected. The budget report showed no change. Thus it looks like the budget report does not honor the exchange rate that I enter by hand as the current rate. As a matter of fact I was unable to get the value reported in the budget report to change at all no matter what I did.
Maybe you can reproduce this behavior?
7 Posted by Ben Spencer on 12 Oct, 2010 07:12 PM
Do you think you would be willing to create an example .md data file that shows the problem. There are so many potential things that I could be doing differently when I try to recreate what you are decribing and I am not certain that I am getting the same results. To create a new data file run Moneydance and select File->New. To get back to you old data file select File->Open.
Ben Spencer
Moneydance Support
8 Posted by mawyn on 17 Oct, 2010 03:10 PM
Thanks for suggesting to set up a small sample file, Ben. I did that and did all sorts of tests. I found out an awful by doing that.
In particular, I think I discovered what I need to do in order to get the budget report working the way I need it to work. Budget reports seem to use the exchange rate entered along with the actual transaction in the account. (I was thinking that they would use the current rate in the history window, but that's not the case.) I had only entered exchange rates for transfers between accounts but not for individual cash payments. Because of this, non of the cash payments were translated from £ to euros in my budget report. I have now gone through my £ cash account and added the desired exchange rate to each transaction so that now the budget report seems to work the way I want it to.
In this context, I was wondering if there's a way to batch edit several transactions at once to change the exchange rate?
I am attaching the test file in case you want to look at it because I still have some comments and questions.
Here's a summary of what I believe to have discovered in the course of my tests.
First I think I should probably define the terms I use to denote the three kinds of exchange rate in MD so that we hopefully all know what I'm referring to:
From my tests I believe the reports I tested can be grouped inro three different kinds, based on how they deal with currency exchange rates:
Conversion is based on entry rates:
Cash Flow, and Detailed Cash Flow
Budget Income and Expenses, and Detailed Income and Expenses
Conversion based on history rates. If there is an history rate for the date of a transaction entry, the value is converted using this history rate. If there is no history rate for the date of the entry, it looks like the history rate for the last entry date in the report is used. This is still a bit confusing for me!
Search Report Account Balances Asset Allocation Net Worth
Conversion based on current rate:
Cash Flow, Detailed Cash Flow
Is my summary correct for all the details? Is this behavior documented somewhere? It certainly would have been a big help and would have saved me a lot of time if it existed and if I had had access to it.
I'm still a bit confused about the behavior for my second group of reports above, the ones that are based on history rates. As I've written above, it looks like entries in these reports whose date has no associated history rate are converted using the history date for the latest entry in the report. I don't understand why that should be the case. Why isn't the most recent history rate or the current rate used? -- Maybe the easiest way to "de-confuse" me would be if you could send me a description of what the actual behavior is.
Anyway, thanks for your patience, Ben, and for your advice. I believe I now know what I need to know to solve my concrete problem. I'm still curious to find out the complete picture of how conversion works in all of the reports, though.
Matthew
9 Posted by Jon Hoover on 28 Nov, 2010 12:28 AM
Matthew,
I don't have too much new information for you on this but I did recall that we've seen this bug happen around a few other reports. I've attached the bug ticket for those reports to this, I imagine they are all from the same cause and hopefully we can get them all sorted out together. Thanks for looking into this issue so thoroughly with us.
Jon Hoover, Moneydance Support
System closed this discussion on 31 Mar, 2015 03:29 PM.