ccQuickMint – Import Mint Transactions into QuickBooks

I’ve been using mint.com for the last couple of years and I’ve found it to be an invaluable tool for personal (and business) finance management and visualization. After it was acquired by Intuit, I was a bit worried, but it does look like Mint will replace QuickBooks online and not the other way around. However, one thing that I thought surely would come out of the acquisition would be ability to seamlessly trade data between the two applications. My accountant prefers to get my financial data in QuickBooks format and I certainly prefer the ease and intuitiveness of Mint. I always knew that mint allowed exporting transactions into Excel via comma-separated values file (CSV) and I was very surprised to find out that QuickBooks only allows limited Excel import – you cannot import transactions but only lists of vendors and such.

After some poking around, I found out about ability to import transactions into mint using IIF files. Essentially, these are specially formatted text files that include information about the transaction and account involved. After reading some documentation, I decided to bite the bullet and write a tool to convert transactions exported from mint into IIF, such that they can be imported into QuickBooks.

Welcome ccQuickMint – a tool to convert Mint’s transaction export into a format that can be imported into QuickBooks.

ccQuickMint

How it works:

Step 1: Cut a hole in a box… (sorry, I’m really a 13 year old trapped in a 30 year olds body)

But seriously:

Step1: Export Transactions From Mint to CSV file

Export Transactions

Step 2: Feed the CSV File into ccQuickMint via ‘Load’ button and wait for the Razzle Dazzle

Step 3: Adjust account information (type, description, etc) and select appropriate export options

Step 4: Use ccQuickMint to output transactions into an IIF file via ‘Export to IIF’ button

Step 5: Import saved IIF file into QuickBooks

Quickbooks import

Step 6: Profit??

 

Under the Hood:
IMPORTANT: Please read this section. Here I will outline assumptions that were made and limitations of my implementation.

Account Logic

The tools maps the concept of Mint “Category” to the concept of “Expense Account” in QuickBooks. So when the tool processes a transaction , it uses the account as one side of the transactions (debit or credit) and Mint category as the other side (Debit or Credit).

For Example, a Mint Transaction like:

Date 

Description 

Amount 

Type 

Category 

Account  

12/17/2010

ExxonMobil

52.02

debit 

Gas & Fuel

Citibank Credit Card

Would result in a debit to the Citibank Credit Card account and a credit to the Gas & Fuel expense account.

The accounts will be automatically created in QuickBooks (if you check off the appropriate box in ccQuickMint). If the Category contains the word ‘income’, the account will be created as an income account. Otherwise, by default ‘Category’ will imply an Expense account. All Mint accounts will default to ‘BANK’. Once transactions are loaded into ccQuickMint, you can change the account type (to CCARD for example). Don’t forget to click ‘Apply’.

There are a couple of problems with this. First and foremost, it fails for things like credit card payments because the category ‘credit card payment’ does not represent an account but represents an action. A ‘credit card payment’ action is in fact a transfer from one account to another (i.e. debit to the checking account, and a credit to the credit card account).

Transfer/Credit Card Payment Logic

To mitigate this, ccQuickMint has custom handling of ‘credit card payment’ and ‘transfer’ categories. (If you have other transfer style categories you will have to rename them for the time being to either exactly ‘transfer’ or exactly ‘credit card payment’). However, for transfers and CC payments two transactions are needed to be matched together (as opposed to in a typical expense, where a single transaction contains all the needed info). In other words, when ccQuickMint encounters a transaction like:

Date 

Description 

Amount 

Type 

Category 

Account  

12/21/2010

Credit Card Payment

1597 

debit 

Credit Card Payment

Checking Account

It needs to see another transaction like

Date 

Description 

Amount 

Type 

Category 

Account  

12/23/2010

Credit Card Payment – Thank you

1597 

credit

Credit Card Payment

Citibank Credit Card

Since these transactions do not necessarily follow each other in Mint’s export file, this was a tiny bit of a challenge. When ccQuickMint sees a transfer/cc payment transaction, it checks if it already has a counter transaction for the same amount. If it has, all the data are available to create a QuickBooks entry. Otherwise, it stores the transaction until the 2nd part is found.

Since I am making some logical leaps with this amount matching logic, I included a feature to ignore credit card payments and transfers (disabled via checkbox), such that they can be manually entered.


Known Issues

  • Multiple IIF Imports: Despite assigning unique IDs to transactions in the IIF file, if you import an IIF file into QuickBooks more than once, all transactions will be duplicated. I feel like this is a QuickBooks issue. As a workaround, you can edit the CSV file exported by Mint and remove transactions that have been already imported.  
  • If you have more than once Credit Card for the same account (i.e. you and your spouse), Mint shows it as two separate accounts. They both would have the same balance, but different transactions. There is not really a good solution for this. I ended up manually editing Mint’s CSV file and changed the account name to be the same for cards that were attached to the same account.  
  • I’ve encountered a strange error while importing an IIF file into QuickBooks. I am fairly sure it’s an Intuit issue and it happens if you try to import an IIF file while having a report open. Closing all QuickBooks windows (i.e. account registers, reports, etc) prior to import resolves an issue.Error Message
  • There has been little testing, so hopefully you can report any issues you find and I will keep this list current, so people know what to expect. 

Support

Surely there will be issues with this tool. It worked for me, but I wrote it with me in mind. Please send any problems you encounter my way and I will process them in the order of available time.

If you find the tool useful and are interested in keeping updates going, please provide feedback and/or buy me a beer or something. Also, feel free to contact me for any customization requests.

 




 

Free Download

ccQuickMint v1.2 – requires Windows and .NET 4 framework – Download Here

  • 2011.11.29 – Fixed Mint File Format Compatibility Issue” – should now load mint export files successfully again 
  • 2011.03.02 – Added ability to ignore blank CSV entries (when rows were cleared via Excel but not deleted) 

ccQuickMint v1.2 Source Code – C#, Visual Studio 2010 – Download Source Code Here

  • 2011.04.07 – Source code is a little dirty and needs a clean up, but hopefully will still be useful. Parsing and IIF formatting logic implemented in ccQuickMint.cs. 

Disclaimer: Provided as-is with no warranty of any kind. Please BACKUP all your data before attempting any data changes.

Cheers!


  1. Aneesh

    Thank you for replay.
    Actually I want to use my own gridview to export to IIF . If i’m getting your source code it will be very helpful for me.
    Please I need your soucecode.

  2. Timur

    See the Source Code link above (under Free Download). Most notable are the ToIIF() methods but pay attention to the format stipulated in GetIIFHeader method as well – it needs to match what ToIIF() will output. The logic pertaining to determining transaction type is specific to how Mint exports data, so you may need to change it to adjust to your application.
    For more details on IIF format and sample transaction definitions check out http://support.quickbooks.intuit.com/support/Articles/HOW12778.

    Hope this helps! Let me know if you have any suggestions for improvements or if you end up with a useful tool you can share.

    Cheers!

Comments are closed.