Import transactions with exchange rate

Bob B's Avatar

Bob B

21 May, 2020 06:31 PM

I'm trying to switch from Quicken. I h ave over 20 years worth of data I'm trying to preserve. My Quicken data has a number of accounts that are not in the base currency. Of course when I try to import the QIF file directly into Moneydance, I get a big mess. So I wrote a VB program to impport the QIF into a table in MS Access and I can match the from and to accounts and calculate the exchange rate present at the time of the transaction.

Now I can create a transaction that has both side of the to/from with exchange rate.
  
Is I there a way to import a 'full register transaction' into Moneydance? By that I mean a transaction that has all the fields that I would find if I were manually entering the data into the register - the 'to account', the 'to amount', the 'exchange rate' and any split categories?

If I can do that, my problems are solved and I can get a nice lean starting Mondeydance file with all my historical data.

By the way, I already have a nice clean Moneydance file with all the accounts, categories, securities etc in the correct currencies ready to accept data.

  1. 1 Posted by dwg on 21 May, 2020 09:37 PM

    dwg's Avatar

    I'm a fellow user.

    The short answer is that you cannot take the approach you describe.

    There is no field for exchange rate in Moneydance. There no field for it in the download formats. The main ones. OFX and QIF do not have any provision for exchange rates or foreign amounts and the CSV importers follow this direction.

    Hence all imports are considered to be in the currency that the account has been defined in.

    Giving the omission from the standard formats the only way that Moneydance will work with foreign amounts is if the exchange rate used has already been loaded into Moneydance for the specific date prior to the import of the transaction. If there is a rate there and an amount is added to a foreign currency account where the transfer account or category is in another currency then Moneydance performs the conversion when adding the transaction to the other account/category.

  2. 2 Posted by Bob B on 22 May, 2020 01:25 AM

    Bob B's Avatar

    Thanks. When I enter a transaction manually in a register (see attached), I can enter both sides of the transaction and an exchange is calculated or, if i enter the rate, the amount is recalculated. I'd like to import transactions where I provide 'both sides' of the transaction.

    As I stated in my original message, I've done the work to match both side of the transaction in an Access database. Now I want to import them. I was hoping someone might have written an add on that allows an import with both sides to be entered in one transaction.
     I know it's possible to write my own but I was hoping someone might have already done it :) I can provide the data in any format required.

  3. 3 Posted by dwg on 22 May, 2020 05:13 AM

    dwg's Avatar

    Apologies I had forgotten that Moneydance stores the rate for individual transactions and was think how it uses the overall rates it keeps.

    I've been around Moneydance for a while now, alas I cannot recall any extensions that work with foreign currency transactions with exchange rates. The main user created extension for loading transactions is the CSV importer, I did look at it and there is no provision for exchange rates or foreign amounts that I can see. QIF has no provision and as OFX has no provision for categories it is of limited use in migrations in any case even though it does have provision for rates.

  4. 4 Posted by derekkent23 on 22 May, 2020 10:33 AM

    derekkent23's Avatar

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

    You have probable worked this out but just in case.

    As part of the QIF import process Moneydance allows you to select the currency for non-base currency accounts. For these currencies to be listed on import ensure “Show on summary page” box is ticked under TOOLS – CURRENCIES – highlight subject currency and double click – EDIT. Note: QIF file do not contain exchange rates. To overcome this QIF file shortcoming the Currency/Security History window must have current and dated exchange rates filled in which cover the date range of transactions in non-base currency account. As well as manually adding exchange rates you can import CSV exchange rate files downloaded from sites on the web. Use IMPORT button in the Currency/Security History window.

    Some notes on entering foreign currency transactions into a base currency account.

    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. Remember to change the CURRENCY TYPE using the dropdown when you set up a non-base category to the required currency. As you can see you can end up with categories with similar names which need to be differentiated by currency.
    Now when you want to enter a transaction for a category in that foreign currency select the appropriate category.
    e.g.
    Holiday:Travel -------- base currency
    Holiday:Travel UK -------------- foreign currency GBP
    Holiday:Travel Europe ---------foreign currency EURO

    When a non-base currency category is selected you will now notice that the Rate and Foreign Amt fields are open for you to use when you select a category created for use with a foreign currency.
    Assuming that the exchange rate for the foreign currency is available under TOOLS – CURRENCIES the RATE value will fill 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).

    Splits will also handle a mix of base and foreign currency transactions.

    I don’t know if Quicken works in a similar way or uses a different approach when handling foreign currency transaction. Maybe you can adapt the following.

    Create a new data set. Populate the relevant exchange rate history window. Manually or CSV import.

    Let’s assume you have a category Holiday:Travel which has imported from Quicken. As the QIF file does not define currencies all associated transactions appear in the base currency. SEE FIG 1. Set up a new category Holiday:Travel UK with the currency set to British Pound as FIG 2.

    Under EXTENSIONS – MANAGE EXTENSION install/run Find and Replace. See FIG 3 for next set of steps. In the FIND section, top left, select category to Travel and click FIND. Tick all the transactions you want to move from the category Travel to Travel UK. See FIG 4 for next set of steps. In the REPLACE section set the category to Holiday:Travel UK click REPLACE ALL. If all looks OK click RECORD – DONE otherwise click RESET.

    Fig 5 show that the subject transaction has been changed to a foreign currency transaction including exchange rate and foreign amount.

    Maybe you can adapt the above to meet your situation.

    Hope this helps.

  5. 5 Posted by Bob B on 22 May, 2020 05:37 PM

    Bob B's Avatar

    Thank you - I am aware of this but I appreciate your help. My issue is not with using Moneydance or entering foreign currency transactions or cross currency transfers. The problem is that QIF import creates duplicate transactions for almost every dual currency transaction that is imported. I can manage this pre-import if i could find an extension that allowed me to specify both the from and to amounts in one entry. I'll probably have to write one myself but I was hoping someone else had done so. Thanks for your help.

  6. 6 Posted by dwg on 22 May, 2020 09:18 PM

    dwg's Avatar

    I'm a fellow user.

    The source code for the CSV importer is available, it might be an idea to see if it can be extended to suit your needs.

    https://github.com/stant/mdcsvimporter2015/releases

  7. 7 Posted by Bob B on 25 May, 2020 03:14 AM

    Bob B's Avatar

    I've figured this out and I can load these transactions . I used makeSplitTxn to enter the amount and exchange rate. It all works well so for.

  8. 8 Posted by derekkent23 on 25 May, 2020 07:03 AM

    derekkent23's Avatar

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

    Well done.

    Have Fun, Stay Safe.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

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