Multi-Currency Reporting

Graham Holford's Avatar

Graham Holford

06 Dec, 2019 01:29 PM

Moneydance does not seem to handle currency translations in reporting correctly? I have some asset (and bank) accounts in a non-base currency. When a report is prepared, the non-base-currency balances are reflected as balances in the base-currency without being correctly translated. This gives a meaningless total as balances are nearly added. This also happens when reporting transaction items. Please help. How do I get MD to translate the balances back to my base currency in a report?

  1. Support Staff 1 Posted by Maddy on 06 Dec, 2019 02:57 PM

    Maddy's Avatar

    Hi Graham,
    Thank you for contacting Moneydance support.

    When you created your Moneydance data file, you will have picked a 'base currency'. This is the default currency used for your accounts and categories.
    When you create an account or category, you can specify which currency should be used.

    If you have an account in one currency and enter a transaction, which includes an account or category in a different currency - Moneydance will default to the current exchange rate.
    I'd recommend you take a look at this article that includes further information and a screenshot of a multi currency transaction.

    E.g.: You can setup an account in GBP and then create specific categories via Tools --> Categories, with the currency set to EUR. When you enter a transaction in the GBP account and specify the EUR category, the exchange rates will be applied.

    When you're reporting against the account, the reports will be generated using the base currency. If you wanted to report in EUR, you can navigate to Tools --> Currencies and set EUR as the base currency. This won't make any changes to the account currencies or any other currency information within the file. You can report in EUR and then change the base currency back to GBP before continuing to work within the file.

    I hope this information is helpful. Please let me know if you have further questions or need any assistance.

    --
    Maddy, Infinite Kind Support

  2. 2 Posted by Graham Holford on 06 Dec, 2019 04:12 PM

    Graham Holford's Avatar

    Hello Maddy

    I am not sure this helps. Let me explain more ...

    I have set up MD exactly as you suggest - with a base currency of GBP. This makes sense as most of my assets are in GBP. However, I have some assets in a foreign country - property in Europe reflected in EUR and also, a related bank account, the currency of which is EUR. In setting up these accounts in MD, I specified the "currency type" as EUR. This all seems to make sense.

    My problem comes when I want to report a list of balances (my investments and assets) to reflect my total wealth, MD adds the GBP amounts to the EUR amounts and gives a total in GBP which is wrong. Whilst the sidebar listing shows EUR for those accounts, MD generates a report showing the EUR balances as GBP (prefixed as "£") and then adds them all together to give a (wrong) total.

    This seems to happen with transactions as well. An expenditure report that pulls transactions from GBP-based and EUR-based categories does not translate the EUR amounts (although it shows a € prefix) but merely adds the numbers together.

    Maybe MD does not like reporting mixed-currency items in one report?

  3. Support Staff 3 Posted by Maddy on 06 Dec, 2019 04:23 PM

    Maddy's Avatar

    Thank you for confirming that Graham.

    I’m sorry to keep you waiting, but I’d like to run your query past the development team who may be able to provide further information.

    For this purpose, it might be helpful to see your error logs. Would you be able to send us a copy of your Moneydance error logs, these should include information on what might be causing an issue.

    The logs are saved on your computer as an errlog.txt file. Using Mac OS X, the logs should be located at one of these locations within your Home folder:

    ~/Library/Containers/com.infinitekind.MoneydanceOSX/Data/Library/Application Support/Moneydance/errlog.txt

    or:

    ~/Library/Application Support/Moneydance/errlog.txt

    If you have any trouble finding your Home Library folder, open Finder and select Go --> Go to Folder, then copy and paste in exactly the following:

    ~/Library

    This will pull up your local Library folder and you can then navigate to the locations above to find the logs.

    Thank you

    --
    Maddy, Infinite Kind Support

  4. 4 Posted by Graham Holford on 06 Dec, 2019 04:59 PM

    Graham Holford's Avatar

    Finally found that file - could not find the "Containers" link but then somehow got there.

  5. 5 Posted by derekkent23 on 06 Dec, 2019 06:03 PM

    derekkent23's Avatar

    I am not support staff, just a user.
    Hi Graham

    Just a thought.

    For things to work when you have accounts and categories in non-base currencies the relevant exchange rates must be present. For example, if you click on TOOLS – CURRENCIES and double click on EURO you need to have an exchange rate history. This history is used to do the currency conversion from Euros to Pounds.

    The screen shot shows this window although you would probably need an exchange rate for each day, not just for some dates, as in my screen shot.

    There a number of ways to update exchange rate history.

    Hope this helps.

  6. 6 Posted by Graham Holford on 07 Dec, 2019 10:04 AM

    Graham Holford's Avatar

    Thank you Derek (?) - that seemed to have helped, or it's made the "net worth" report correct. I went into the currency history and removed some obviously wrong rates (not sure how they got in there) and some that were shown as a rate of 1? The list still is made up of GBP and EUR line items but the "Total Assets" line is now a translated one totalled in GBP.

    I still seem to have a problem if "posting" a EUR-based bank account line item to a GBP-based category. I think, in these circumstances, I have to put in the correct GBP/EUR exchange rate for these entries. I'll do some more testing and then respond.

  7. 7 Posted by dwg on 07 Dec, 2019 10:40 AM

    dwg's Avatar

    I'm a fellow user.

    As I understand it the tables of exchange rates that are in Moneydance are indicative rates, generally automatically maintained using one of the extensions that are available.

    These rates are used whenever a report is requested in order to convert non base currency totals to the base currency.

    If you enter a transaction in an account and assign it to a category that is in a different currency the software seems to pick up the most recent rate and use this if none is specified, this is what a test I did in the latest Moneydance preview release resulted in.

    Another approach that could be taken it to have a set of categories in the foreign currency so that the totals are converted at report run time.

  8. 8 Posted by derekkent23 on 07 Dec, 2019 10:52 AM

    derekkent23's Avatar

    I am not support staff just a user.
    Hi Graham

    It sound like you are on top of this, but just for the record this is my take.

    Assuming you want to enter foreign currency transactions into a base currency account.

    You need first to set up categories in the foreign currency. TOOL – CATEGORIES – NEW.
    Now when you want to enter a transaction for a category in that foreign currency select the appropriate category. You will now notice that the Rate and Foreign Amt fields are open for you to use.
    Assuming that the exchange rate for the foreign currency is available under TOOLS – CURRENCIES the RATE value will file in. Enter the Payment or Deposit amount (for credit cards Charge or Payment) in the base currency and the Foreign amount will fill in. You can change the Rate if required. Instead you can enter the Payment or Deposit amount (for credit cards Charge or Payment) in the base currency and the Foreign amount and the Rate will change accordingly. You can’t enter the Foreign Amt as there is only one field so you are not able to define it as a Payment or Deposit amount (for credit cards Charge or Payment).

    You can also set up foreign currency accounts.

    Hope this helps

  9. 9 Posted by Graham Holford on 09 Dec, 2019 06:17 PM

    Graham Holford's Avatar

    Thanks everyone - I think I have resolved this. Trick is to be aware of what is being pulled into the Currency records and be fastidious about entering rates where a non-base currency transaction is entered.

  10. System closed this discussion on 09 Mar, 2020 06:20 PM.

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