Import Failure from Quicken

rkbalan's Avatar

rkbalan

29 Jul, 2019 10:14 PM

Hi;

I installed the trail period of Moneydance and it pulled up in my Quicken data file.

I primarily have accounts that relate to Banking, Investment and Assets in two currencies- USD and INR. All the transactions that involved transfers between accounts of different currencies completely failed to get the exchange rates that were used. This resulted in the imported numbers completely incorrect in all the involved accounts. Please let me know if I need to be following a different import process. Thanks.

  1. 1 Posted by derekkent23 on 30 Jul, 2019 06:55 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    I think you are the first user who has posted that has tried to import a QIF file that contains accounts in more than one currency.

    Two thoughts.
    If you open the QIF file in a text editor can you see exchange rates included so that Moneydance knows what exchange rate to use? Also see under TOOLS - CURRENCIES and double click on the other currency, the currency you have not set Primary Currency (base currency) to see if dated exchange rates are listed.
    Did you follow a process similar to that below were on import you set the currency for each account?

    The following process assumes that your QIF file contains all your accounts.

    Make sure you are running this build of Moneydance https://infinitekind.com/preview

    You need to use your current program to export your data in a QIF file type. Ensure any closed account that have transfers to any open accounts are included in the QIF file, re-open these accounts if necessary.

    Open the QIF file in a text editor. You are looking for the date format used in the file e.g. D25/03'2019 Day Month Year or D5/ 17'19 or D05/31/2017 Month Day Year etc. You will need this information later. If you get this wrong it will be apparent as the dates in Moneydance will be all over the place and you will need to start again creating a new data set.

    QIF files were never meant for transferring data from one program to another, but it’s all that is available, you will need to spend time cleaning thing up when import is complete.

    When you first start Moneydance it opens in the Welcome window click on “Create a new account set”. If you have opened Moneydance before create a new data set under FILE (MONEYDANCE on Mac) – NEW.

    Select “Create New File” (Data Set) and click NEXT. Type in an Account Set Name (Data Set) and click NEXT. Change Primary Currency (base currency), if required. File Type, leave at “Standard Account set”, click FINISH.

    In the side bar (list on left) under BANK deleted the two default Bank accounts, highlighting them in turn and using ACCOUNT – DELETE ACCOUNT.

    If you have accounts with currencies that are not in your base currency make sure they are displayed on the Summary Page. Ensure box “Show on summary page” is ticked under TOOLS – CURRENCIES – highlight subject currency and double click – EDIT.

    Drag and dropped the QIF file into the side bar (list on left).
    Waited for import window to appear, this can take a few minutes.

    IMPORTANT: Checked all listed accounts are of the correct type and currency and changed if required.

    The “Date Format” should be set to the date format used in the QIF file not the date format you want to use in Moneydance. Select the correct Decimal Character. Do not tick “File was downloaded”.

    Click import and waited to account values to file in, again this can take some time.
    Some accounts maybe hidden under the “+” at the bottom of the side bar, added them back.

    Some QIF files don’t include initial account balances. If you think this is the case you can add initial balance to affected accounts as follows.
    Highlighting an account, then click ACCOUNT – EDIT ACCOUNT and add an Initial Balance.

    For investment accounts QIF file don’t always carry security prices other than buy sell prices, so investment account balances maybe wrong until you update with current security prices.

    Reminder are not included in QIF files.

    Any accounts that are inactive (closed) should be made inactive by highlighting the account in the side bar and clicking ACCOUNT – EDIT ACCOUNT and ticking the inactive box. To remove them from the side bar right click and select REMOVE FROM SIDE BAR.

    Now the real work starts to check balances and tidy thing up. This is especially true of investment accounts as not all action types are supported. But worth it in the end.

    For information on how to use Moneydance see https://infinitekind.tenderapp.com/kb

    Hope this helps.

  2. 2 Posted by dwg on 30 Jul, 2019 08:54 AM

    dwg's Avatar

    I'm a fellow user.

    QIF file do not include exchange rate information, values are just numbers. You generally will need to have separate QIF files for each account that is not in your base currency, set the account to be the correct currency before the import then do the import. alas for any inter account transfers involving different currencies you will need to manually correct the entry to use the exchange rate.

  3. 3 Posted by derekkent23 on 30 Jul, 2019 09:26 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi dwg

    It’s my understanding with the new QIF import process in Moneydance you don’t need to import separate QIF files for each account in a non-base currency. The import process lets you check and if required change each account currency.

    Hope this helps.

  4. 4 Posted by dwg on 30 Jul, 2019 10:03 AM

    dwg's Avatar

    Hi Derek,

    Quite possibly, alas it is something I cannot test, I just do not have any data to do so and the lack of information from TiK doesn't help.

  5. 5 Posted by derekkent23 on 30 Jul, 2019 08:50 PM

    derekkent23's Avatar

    I am not support staff, just a user.

    Hi rkbalan

    Did some testing using 2019.4(1886) following the procedure I posted.
    As dwg has commented it unlikely that your QIF contains exchange rates, so before importing a test QIF file into a new data set I added a set of dated exchange rates under TOOL – CURRENCIES – SUBJECT NON-BASE CURRENCY. In the table you can also import an exchange rate CSV file that can be obtained from a number of sources on the web. Then I imported my test QIF file.
    This worked well without any issues matching the data in the QIF file.

    Hope this helps.

  6. 6 Posted by rkbalan on 03 Aug, 2019 01:01 AM

    rkbalan's Avatar

    I appreciate the responses I have been getting.

    I have verified that I am on version 2019.4 (1886).

    I see the currencies that I need are in MoneyDance (Tools -> Currencies). The base currency is USD. The other currency I use is INR

    MoneyDance imports all the accounts into USD by default. When I try to Edit one of the INR accounts (Account -> Edit), the Currency Type is set to USD. The field is not editable.

    So it seems MoneyDance does not allow us to change the currency of the account once set or imported.

  7. 7 Posted by dwg on 03 Aug, 2019 02:37 AM

    dwg's Avatar

    I'm a fellow user.

    You have to set the currency of an account before you import transactions into it, you cannot change the currency after the import.

    The software assumes that if an account has transactions in it those transactions are in the currency that the account was created in, if this is not explicitly set then it will be the base currency, it will not allow that to be change as to do so the software would need to known the exchange rate of each transactions so that it could potentially be converted when the change is made and as it is more than likely that the software would not have the correct rate it is not allowed.

  8. 8 Posted by derekkent23 on 03 Aug, 2019 06:37 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    Dwg is correct, once you set the currency for each account during QIF import they can’t be changed. Make sure you set account currency during QIF import. Also, as the QIF file does not contain exchange rates you must separately manually enter or import the relevant exchange rate history covering all the dates used by your transactions into the subject account. Currency history is entered/imported under TOOLS – CURRENCIES subject currency. See my earlier posts.

    Hope this helps.

  9. 9 Posted by rkbalan on 06 Aug, 2019 12:39 AM

    rkbalan's Avatar

    Thank everyone for the responses.

    I was able to import the data in both currencies into my account but there were too many inconsistencies / errors in the accounts that had inter-currency transfers to go in an figure out where the errors occurred. To give it some context, I have been using Quicken since 2000 and so I have a large number of these transactions. I am going to put a hold on the evaluation MoneyDance. I do like the UI easy of use, which is far far better than what I have now. I will switch to MoneyDance at the start of the next year (so I can close out the tax year) and just start with a fresh slate without any imports and just creating accounts with current balances.

    Thanks again to everyone who responded and took the time to address the issue.

  10. 10 Posted by derekkent23 on 06 Aug, 2019 06:24 AM

    derekkent23's Avatar

    I am not support staff, just a user.

    I guess as your data goes right back to 2000 then it was impossible to obtain a CSV file with USD/INR exchange rates for each day going back that far. Without importing that before importing the QIF file then things were never going to work.
    Good call to start a new data set next year.

    Have Fun

  11. 11 Posted by dwg on 06 Aug, 2019 08:56 AM

    dwg's Avatar

    I'm a fellow user.

    Having a downloaded file with exchange rates is probably not going to give accurate results.

    Many countries use floating exchange rates and what you see published are indicative rates only, each institution will have its own rates and while many will set daily rates for transactions under a certain amount rates will vary from institution to institution, so a published rate will likely be a little different to the rate used for you transaction.

    Many moons ago I worked for a bank and this is how we worked it. There were also different rates for buy vs sell transactions and within sell transactions for the form it took e.g. Cash vs travelers cheques vs ....

  12. System closed this discussion on 05 Nov, 2019 09:00 AM.

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