Home → Switching to Moneydance →

Importing

• Edit

paul.blake.1005

24 Dec, 2023 06:56 AM

Hi. I'm trying to determine whether Moneydance is able to import data from the app I currently use. our importing process is a bit different to others I've tested. I managed to get an import to work once but did not give the results I wanted to deleted all the transactions and attempted it again, but now the import process does not progress past setting up the fields. I assume it may be something to do with me using the trial version.
The Finances 2 app I currently use is a true accounting app, so outputs a csv that contains both the debit and credit accounts in each record. Your import process doesn't handle this, so I want to know how Moneydance will ultimately "join" the transactions together from multiple single account imports.
Do you know why the import process would no longer work? Thanks

1. 1 Posted by dwg on 24 Dec, 2023 07:28 AM

   

   Any importing issues have nothing to do with trial mode.

   With Moneydance the other side of the transaction is handled through the category field, so this field can contain either a category or an account that the funds are going to/from. In Moneydance each category is really an account.

   We cannot really say anything about the import of the data without seeing a sample CSV file, we normally need a file that contains the header plus one deposit and one withdrawal transaction.

   • Edit

2. 2 Posted by paul.blake.1005 on 24 Dec, 2023 08:51 AM

   

   Hi

   Thanks for your quick reply.

   I’’m a little confused with this, so perhaps you could guide me as to the mappings required.

   I’ve attached a CSV from one of the accounts. It appears your import can’t handle all accounts at once ? (Some others can, some can’t).

   How would you suggest I map this, and can I in fact map all transactions for all accounts at once?

   Is the Description or the Memo field the Payee?

   Thanks
   Paul

   • HB_Money_Manager.csv 50.1 KB
   • Edit

3. 3 Posted by paul.blake.1005 on 24 Dec, 2023 09:05 AM

   

   I also get this error frequently. It seems to come and go as soon as I select File->Import (from the menu) and select a csv file. Do you know what it means?

   Thanks

   Paul

   

   • Screenshot_2023-12-24_at_5.00.53 pm.png 149 KB
   • Edit

4. 4 Posted by -Kevin N. on 24 Dec, 2023 05:55 PM

   

   Hi Paul,

   I can see a few issues with the supplied CSV file that would need to be addressed before you could even attempt to import it.

   You'll need to use a spreadsheet to manipulate the data into a useable format.

   The first issue is the addition of the timestamp to the Date format. That can be easily addressed with a formula in a spreadsheet to eliminate the timestamp.

   The second is the separate 'category' and 'counter account' columns. I would tend to want to combine them into a single 'category' column. That too can be easily addressed using Concatenate in a spreadsheet.

   You will also need to create the account structure in Moneydance prior to importing the CSV file so as to first accept the initial import and second to accept the transfers to other accounts. e.g. HB Online Saver, HB Pension Plus etc. These transfer transactions will initially be imported incorrectly as Categories but can be easily corrected using Batch Change post import.

   If you're interested in taking this on, let me know and I'll try to lend a hand.

   I've provided a copy of the manipulated CSV file that I used as well as a screenshot of the importer field settings and the results in the register.

   -Kevin N. (not a member of Moneydance Support)

   • HB_Money_Manager.csv 63.6 KB
   • Importer_Fields.jpg 39 KB
   • HB_Money_Manager.jpg 299 KB
   • Edit

5. 5 Posted by -Kevin N. on 24 Dec, 2023 06:10 PM

   

   attached

   • HB_Money_Manager.csv 63.6 KB
   • Edit

6. 6 Posted by paul.blake.1005 on 25 Dec, 2023 03:22 PM

   

   Thanks Kevin, that was very helpful. I'm OK with spreadsheets so will set up a file with the rules you've suggested. There is no way of distinguishing a transfer at import time then?

   • Edit

7. 7 Posted by dwg on 25 Dec, 2023 09:28 PM

   

   Categories are a special type of accounts, you can think of them as Income and Expense account types rather than Bank, Credit Card etc types of accounts and given in accounting all transactions are reflected as a transfer between accounts so it is one and the same thing whether a transaction is assigned to a category or as a account to account transfer. There is no real differentiation in an accounting program or in the old paper based accounting books.

   The use of the term Categories is more for our benefit as a differentiation term. If you want to highlight a transaction as a transfer you would be more likely to do this through the Description or the Memo.

   • Edit

8. 8 Posted by paul.blake.1005 on 26 Dec, 2023 12:46 AM

   
   Thanks again. 

   
   

   I managed to do a sample import of some data. What I discovered is that an expense or income category is always created for the asset account to which funds are transferred, using the name of that account. 

   I must then manually locate and edit the transaction account by selecting the actual transfer account then re-save it, in order to convert it to a transfer!!

   
   

   Surely I do’t have to do this for every transfer? I’ve not had this issue with other apps. 

   

   // Please reply above this line ================================================== From: dwg Categories are a special type of accounts, you can think of them as Income and Expense account types rather than Bank, Credit Card etc types of accounts and given in accounting all transactions are reflected as a transfer between accounts so it is one and the same thing whether a transaction is assigned to a category or as a account to account transfer. There is no real differentiation in an accounting program or in the old paper based accounting books. The use of the term Categories is more for our benefit as a differentiation term. If you want to highlight a transaction as a transfer you would be more likely to do this through the Description or the Memo. On Mon, Dec 25 at 07:22 AM PST, Paul wrote: Thanks Kevin, that was very helpful. I'm OK with spreadsheets so will set up a file with the rules you've suggested. There is no way of distinguishing a transfer at import time then?

   Having trouble reading this? View this discussion online: Importing. Reply with #ignore to stop receiving notifications for this discussion.

   • Edit

9. 9 Posted by dwg on 26 Dec, 2023 01:50 AM

   

   Using CSV files to try and perform a migration is not ideal, mostly migrations are performed using QIF files which are better, but still not perfect for the job. CSV files are sometimes used to import data from banks when there is nothing better available. Most of us here have used QIF files to do our migrations so have limited experience in trying to use CSV for the purpose. It is always recommended to use QIF files with any software that can generate them

   I would not be surprised that Moneydance cannot pick the structure that is needed from the CSV file, these files are too general with an unlimited variety of structures, so I expect mistakes.

   There are approaches and tools that can be used. The Toolbox Extension has the ability to force change account types, this includes categories.

   Often the best approach is to create the structure before any data is imported, so Moneydance does not have to try and work it out.

   If you an get a category structure into CSV file, a user has written a import tool to process it and create the categories see:

   https://yogi1967.github.io/MoneydancePythonScripts/

   and the script, import_categories.py

   No matter what you have migration to another finance program invariable means work, I spent many hours getting data imported and into shape. I came from Reckon over a decade ago, and had to write some custom VBA to help, as at that stage Reckon was only providing some so called "Excel compatible" CSV files, which lack consistency.

   I did not try to import the CSV data I used an Excel add in to create QIF files.

   • Edit

10. 10 Posted by paul.blake.1005 on 26 Dec, 2023 02:14 AM

    
    Thanks again for your help. 

    
    

    I also stopped using Reckon in 2019 and replaced it with Finances 2 at that time, which is good basic accounting software but no longer being developed nor supported. 

    
    

    It was too difficult trying to migrate my old Reckon data, so I just manually re-entered that financial year transactions and started from there. It’s a shame that none of these apps can transfer attachments!

    
    

    Unfortunately Finances 2 does not have a QIF export. It only has a few CSV variants, so that’s what I’m stuck with! After manipulation in Numbers I then only have CSV or TSV export options too. 

    

    I’ll investigate what you’ve suggested. Thanks for your time. 

    
    

    // Please reply above this line ================================================== From: dwg Using CSV files to try and perform a migration is not ideal, mostly migrations are performed using QIF files which are better, but still not perfect for the job. CSV files are sometimes used to import data from banks when there is nothing better available. Most of us here have used QIF files to do our migrations so have limited experience in trying to use CSV for the purpose. It is always recommended to use QIF files with any software that can generate them I would not be surprised that Moneydance cannot pick the structure that is needed from the CSV file, these files are too general with an unlimited variety of structures, so I expect mistakes. There are approaches and tools that can be used. The Toolbox Extension has the ability to force change account types, this includes categories. Often the best approach is to create the structure before any data is imported, so Moneydance does not have to try and work it out. If you an get a category structure into CSV file, a user has written a import tool to process it and create the categories see: https://yogi1967.github.io/MoneydancePythonScripts/ and the script, import_categories.py No matter what you have migration to another finance program invariable means work, I spent many hours getting data imported and into shape. I came from Reckon over a decade ago, and had to write some custom VBA to help, as at that stage Reckon was only providing some so called "Excel compatible" CSV files, which lack consistency. I did not try to import the CSV data I used an Excel add in to create QIF files. On Mon, Dec 25 at 04:46 PM PST, Paul Blake wrote: Thanks again.  I managed to do a sample import of some data. What I discovered is that an expense or income category is always created for the asset account to which funds are transferred, using the name of that account.  I must then manually locate and edit the transaction account by selecting the actual transfer account then re-save it, in order to convert it to a transfer!! Surely I do’t have to do this for every transfer? I’ve not had this issue with other apps.   // Please reply above this line ================================================== From: dwg Categories are a special type of accounts, you can think of them as Income and Expense account types rather than Bank, Credit Card etc types of accounts and given in accounting all transactions are reflected as a transfer between accounts so it is one and the same thing whether a transaction is assigned to a category or as a account to account transfer. There is no real differentiation in an accounting program or in the old paper based accounting books. The use of the term Categories is more for our benefit as a differentiation term. If you want to highlight a transaction as a transfer you would be more likely to do this through the Description or the Memo. On Mon, Dec 25 at 07:22 AM PST, Paul wrote: Thanks Kevin, that was very helpful. I'm OK with spreadsheets so will set up a file with the rules you've suggested. There is no way of distinguishing a transfer at import time then? Having trouble reading this? View this discussion online: Importing. Reply with #ignore to stop receiving notifications for this discussion.

    Having trouble reading this? View this discussion online: Importing. Reply with #ignore to stop receiving notifications for this discussion.

    • Edit

11. 11 Posted by -Kevin N. on 26 Dec, 2023 02:45 AM

    

    HI Paul,

    There is no way of distinguishing a transfer at import time then?

    None that I can think of. As I stated in my prior post, you can easily resolve the issue of transfers being incorrectly imported as Categories by using Batch Change. Make sure that you have created the account(s) that will receive the transfer(s).

    Sort the register by Category in the account delivering the transfer. Doing so should gather all of your transfers together as a contiguous group in the register. Highlight the first transfer transaction, hold down SHIFT and highlight the last transfer transaction.

    All of the transfer transactions should now be highlighted. Right-click (CTRL-click for Mac) any of the highlighted transactions and choose Batch Change > Category.

    Now select the actual receiving account from the drop-down list. Doing so will change the transaction from a Category type to a Transfer type.

    I would be remiss if I didn't mention that with the CSV file format you will only be able to import into Bank, Credit Card, Asset and Liability accounts. CSV files cannot be imported into investment account registers without first converting them to QIF or OFX formats.

    -Kevin N. (not a member of MD support)

    • Edit

12. 12 Posted by paul.blake.1005 on 01 Jan, 2024 08:27 AM

    

    I have now successfully imported all the transactions and amended all the categories.
    The next task is manually re- attaching thousands of attachments!

    • Edit

13. paul.blake.1005 closed this discussion on 26 Jan, 2024 05:45 AM.