Using Intuit Mint with Microsoft Excel for Creating Tax Return Financial Filing Reports

Summary

This document describes how to use the Mint financial service to create a spreadsheet for determining total income and expenses for the year. For a small business owner, this is necessary for tax purposes. For those wanting to keep a budget for personal expenses, the process can be helpful. At the bottom of this document are some suggestions for DIY small business accounting systems.

About Mint

Mint is a free online financial service from Intuit, the providers of QuickBooks. With Mint, your financial transactions are automatically gathered into a unified system. Credit card purchases, loan payments, bank transactions, interest earnings, and more can be viewed in a single stream of transactions or separately by account. The magic of Mint is that many of your transactions can be automatically assigned to expense categories. Over time, Mint learns your normal spending trends and can alert you when spending exceeds expected limits.

These steps assume you already have a Mint account and have been using it for a while. To setup a Mint account, go to the mint.inuit.com website and follow the instructions to connect your various bank accounts.

For a simpler process you may find that a product like Quicken or QuickBooks can do much of what is described on this page, but those are paid products and they lack some of the customization described below.

PHASE #1 – Regular Upkeep

In brief, you’ll want to quickly skim your list of transactions regularly to make sure the entries are as expected and desired.

Some of the automated features in Mint require a bit of correction and upkeep. Below are some examples of tasks that could be done weekly or monthly to avoid excessive year-end financial efforts. Also, the more recent a transaction is, the easier it is to remember the pertinent information or identify an unfamiliar charge and get a refund or correction if needed.

If you are interested in tracking your personal spending and maintaining a budget, you will want to spend time on the details of your personal expenses. Otherwise, if you’re only interested in keeping track of your business income and expenses for tax purposes, you can spend minimal time reviewing your personal expenses.

• Account View. If you use a particular credit card for all of your business expenses, then you can view transactions for that credit card to see all your business expenses in a single list.
• Amazon Purchases. When you purchase from vendors like Amazon, Target, or similar sources of many product categories, your transactions may show up as something generic like “Shopping.” You may want to assign each process to an expense category. If you have a business, some purchases will be personal and some will be business expenses. Consider having a dedicated credit card for business purchases so they can be quickly identified. For example, have all business purchases made on a MasterCard while Personal expenses can be made using VISA, Discover, or American Express. When you review your Amazon transactions (for example), most transaction amounts will match the orders shown in your order history. By reviewing your order history, you can see which expenses are business related and which ones are personal. It’s important to keep your business purchases and personal purchases as separate transactions. So, when purchasing from Amazon or similar vendors, don’t combine personal and business items in a single order.
• Amount Adjustment. Mint doesn’t allow you to change the dollar value of a transaction. Let’s say you buy something using PayPal from a Canadian company. The transaction amount in Mint will reflect Canadian dollars. You’ll need to login to PayPal and view the specific transaction to see how many U.S. dollars the transaction was for after the currency conversion. While you can’t correct the entry in Mint, you can use the +TRANSACTION button in mint (at the top of the transactions page listing). Create a cash transaction for the difference between the Canadian dollar amount and the U.S. dollar amount. You will want to choose Income instead of Expense as the cash entry type — this refers to positive or negative amount and not the category called “Income.” The category will be the same as the original purchase. Use the same date as the original transaction. Assign the new cash offset transaction to have the same expense category as the original PayPal purchase. This way Mint reports will reflect the correct values for expense categories. Also, when you export your transactions to a spreadsheet, the two transactions will offset each other and the total amount of the expenditure will equal the U.S. dollar amount.
• Bookmark Pages. Mint provides navigation links to 10 pages with each page showing 25, 50, or 100 transactions in rows. The first page of transactions will be the most recent. When you go to page 10, you can go forward 4 more pages or back 5 pages. This will make more sense once you start using the navigation links at the bottom of the Transactions page. If you are working on transactions spanning several months, or working through an entire year, you will probably want to bookmark pages to return later where you left off last. Keep in mind that new transactions are received into Mint periodically throughout the day as Mint refreshes with your institutions. So, if you go back to page 15 the following day, make sure new transactions haven’t fallen off to page 16 which you have already completed. Keeping a journal of your work (described below) can be helpful.
• Bulk / Group Editing. Mint lets you list and select groups of transactions based on the expense or income category, the transaction amount, or the payee. For example, if your rent is the same every month, and if it is a unique amount, you can just search on that dollar amount and all your rent payments will show up. You’ll notice the dates of those transactions will all be sometime near the first of the month. You can select them as a group and assign them to a certain expense category, or update the payee name if needed. Using this bulk edit feature to update multiple transactions can save time.
• Categories. Mint has an initial set of income and expense categories. It helps to use the default categories if possible rather than creating your own. For grouping transactions into categories you may wish to use a top-level category like Business Services and then create sub-categories such as Business Phone or Business Software. This makes all business expense categories easy to find. If you use the word Business in the category name, then they will be listed alphabetically together when exported later as described in Phase 2.
  • Adding Categories. You can add sub-categories under the existing top-level category names. See Managing Categories below.
  • Deleting Categories. You can only delete categories you have created. See Managing Categories below.
  • Managing Categories. You can manage categories by starting from a transaction, clicking the category, and choosing “Add/Edit Categories.”
  • Moving Categories. There is no easy way to move a sub-category you have created. Instead you will want to rename the category you want to move and put OLD on the end of the name. Then create a new sub-category in the new location. Finally, search all transactions entries using your OLD category. Use the global/group edit feature to reassign the category to the new one. Then delete your old unused category. This will result in the category and entries being relocated.
  • Protected Categories. You can’t delete, rename, or move the default category names.
  • Renaming Categories. You can only rename categories you have created. See Managing Categories above.
  • Tags. In addition to Categories, you can assign tags like keywords to transactions. The Tags feature is available from within the Edit Details window. Alternatively, if you’d like to easily group and search your exported data, you can use a keyword in the memo line prior to any additional notes. So, if you sort on the memo/notes column, you will see those transactions grouped. You could use a tag of “Tax Related” to identify all relevant entries, but if you forget to use that tag, a transaction could slip between the cracks.
• Credit Card Transactions. Many of your credit card transactions will be properly assigned to expense categories based on the vendor. Purchases from Staples will be considered Office Supplies. Purchases from Best Buy will be considered Electronics & Software. Some transactions may have an incorrect or different spending category than desired. These will need to be corrected. For example, a New York Times subscription may get assigned to the Entertainment category. An annual AAA motor club renewal may be assigned to Auto Insurance. A return of merchandise to Costco may show up as Income. These automatic expense category assignments are presumably based on the cumulative choices made by a majority of Mint users. Mint also attempts to learn from your recurring transactions.
• Edit Details. For each Mint transaction there is the option to Edit Details. Be sure to click on the “I’m Done” button when you are finished editing or you may lose your changes to that transaction.
• Default Categories. You may find that some default spending categories are only sometimes correct. For example, purchases from Apple will likely be classified as Electronics & Software. This is considered a personal expense category. That’s fine as long as the purchases are music, movies, books, and other non-business items. However, a business expenditure could inadvertently be a classified as a personal expense. This is actually a desirable situation. You don’t want Apple or Amazon purchases to automatically be assigned as a business expense because a personal expense might mistakenly slip through. If an error exists due to an oversight, let that error be a missed deduction rather than an incorrectly assigned personal expense.
• Deposits. For any deposited checks, you will need to manually enter the payee in Mint. As with other transactions, check deposits made electronically using your bank app may take some time to show up in Mint, so plan to update them a day or two later. Even though the deposits will show up in your bank account immediately as available or pending, the transfer to Mint is what may be delayed a bit. You will want to avoid making deposits of multiple checks at the bank or at an ATM because that will result in complicated splits of deposits. Ideally, use the bank smartphone app to make individual check deposits. Then each transaction will have its own entry in your bank account and in Mint. If you have a business that has hundreds of transactions and check payments, then you may need a different approach. Larger businesses will have a single deposit for the day that may contain a mix of cash and multiple checks. The bank statement will show only the day’s deposits. Those would be time consuming to track individually. The point of sale software will provide that administrative accounting function. Mint is more ideally suited for people who have a smaller number of daily transactions.
• Duplicate Entries. You may on occasion end up with duplicate entries in Mint. This could happen if your credit card company issues a new card. The new card may appear to Mint as a different account. If the credit card company moves some transactions from the old card to the new card, they could show up twice in Mint. In the Edit Details area of the transaction, you can place a check mark next to “This is a duplicate.” However, before marking the transaction as a duplicate, be sure to assign the Category as something like Duplicate Entry first. This is needed because when you export your data, duplicate entries are still retained and show up there. They aren’t deleted. Once exported, you otherwise won’t be able to identify duplicate entries, and could end up with incorrect category totals. So, by creating a new category called Duplicate Entry under the Misc Expense category, those duplicate entries will be grouped into a separate category for easy identification. After you choose the Duplicate Entry category, and save the change, then edit the transaction again and check the box next to “This is a duplicate” and click the “I’m Done” button. In Mint, the transaction will be gray and it will appear as if it is assigned to a category of Duplicate. That assignment is only a functional label in Mint. The transaction still retains the category assignment that you would have saved it under prior to marking it as a duplicate. Within the Mint charts and reporting, duplicates will be ignored.
• Gas Stations. The Mint system will probably assign all gas station purchases to Gas & Fuel, a sub-category under Auto & Transport. Food and snacks or other items purchased at gas stations may need to be assigned to their proper categories.
• Income. Money received through PayPal will automatically show up with the correct payee name, but the purpose of the money received may need to be assigned to an income category. If you are a consultant, you could assign most PayPal money received as income. If you want to keep track of income by category or business source you can do that. Keep in mind that some income will be reimbursements, gifts, or product returns, so they should not be marked as income for tax purposes. If you use the PayPal credit card processing app, PayPal Here, the transactions will show up with the word Here before the payee name.
• Journal. If you are catching up on several months of transactions, or going through the entire year, it helps to keep a journal of notes to yourself about what page, date, and transaction you last worked on so you can easily return to it later. Also, making a note of any modifications you are making to the categories or rules will help as a reference in the future.
• Manual Transaction Entries. The strength of Mint is to automatically enter and categorize your transactions. However, some financial services like Venmo may not fully work with Mint. The Apple credit card initially was not accessible by Mint, and currently the features are limited. So, you may need to enter some transactions manually. In the Transactions view, you can click on the +TRANSACTION button. For transaction type choose Cash. You can use the memo area and tags to mark the transactions as from a particular service. You may have an Apple card for purchasing expensive Apple products with the 3% cash back benefit. There won’t be many of those transactions. You can enter them manually. This way they will show up in your exported data file for tax reporting.
• Memo / Notes. Mint allows you to enter a memo or notes for each transaction. This is a good place to put cross-reference information. For example, if a payment is received via PayPal or a check deposit, enter the invoice number in the memo area.
• Outcomes Mindfulness. With all of the tips, tools, and suggestions offered here, it is important to maintain outcomes mindfulness. Stay focused on the ultimate goal which is to produce a spreadsheet for tax purposes. Don’t give into the temptation of excessive organization. For example, you may see many transactions with a description in the payee / merchant area, such as “PAYMENT – THANK YOU.” When you scroll through your Mint transactions, you won’t know what the payment was for. You will be tempted to rename every ambiguous transaction. Keep in mind that when your data is exported, it will be clear what these ambiguous transactions are for based on the account for the transaction. If you see that these transactions are automatically being recognized by Mint as credit card payments and assigned to the proper category, don’t waste time trying to rename them all.
• Payee / Recipient. For some transactions the name of the payee or recipient may be correctly displayed as you would want. However, some transactions will have a long complex name. A book purchase from Lulu may show up as “SP * LULU PRESS 8442120689 NC” so you may want to rename the transaction to Lulu as the merchant name. This is not just a problem with Mint, but with credit card statements that can list an unfamiliar payee. The information is based on the settings used by the merchant for their credit card processing.
• PayPal. You will want to keep a browser tab open to view your PayPal transactions. If you’ve not done so already, you may wish to save PDF files for every income and expense transaction. Having them saved locally lets you more easily organize and find items. When you mark invoices as paid (described further down this page), you can use the date and time of the PayPal transaction when applicable.
• Receipts. Business-class multi-function printers generally come with OCR capabilities for optical character recognition that can turn scanned receipts into readable searchable documents. Scan all receipts and invoices, and save them using a file name that is year month day merchant name. All the receipts will then be listed in chronological order on your computer. This way it will be easy to look up an expenditure. For money received, if you are paid by credit card, you will have a record of the payee with your credit card processor, such as PayPal. For check deposits, you will probably use a bank app to deposit checks. Make a record of the payee, amount, date, invoice number, and deposit date for all checks so you can cross-reference later if needed. You may want to copy and paste the receipt filename into the memo of Mint as a cross-reference for the purchase. That will contain the purchase date which may be different than the date the payment was processed.
• Reconcile. The monthly task of reconciling a statement has traditionally been quite common. A bank statement arrives in the mail. You compare your checkbook or receipts with the statement and make sure all the transactions match what you think they should be. The reconciliation process is a feature built-in on programs like Quicken and QuickBooks. With Mint, there is just an ongoing list of transactions with no running account balance shown. Just the current balance shows. So, it will initially be an unfamiliar format to adjust to. However, the goals of reconciliation can still be achieved — discovering discrepancies between your records and the bank records. If you wish, you could download the transactions and perform a traditional reconciliation using a spreadsheet.
• Routine Payments. If you choose a regular time each month to pay off credit cards and bills, these will show up in Mint as a cluster of familiar transactions you can more easily review. It’s also a good practice to maintain.
• Rules. The rules feature allows Mint to automatically identify a payee or vendor name, and change that name to something standard and simple. You can assign all transactions with that name to a certain expense or income category. This feature is helpful for ensuring that transactions are automatically assigned properly.
  • Using Rules. To use rules, view the details of a transaction, rename the transaction, and you’ll see Mint is suggesting the option to rename automatically. Or, change the category for a transaction and mint will offer to automatically do this in the future. If you view details of any transaction impacted by a rule, the MANAGE RULES button will be available.
  • Managing Rules. To review and manage rules, you must first view the details of a transaction and change the name or category. Then you will see the MANAGE RULES button. Click the button to view your existing rules and edit them. You will want to periodically review your rules, at least annually, to ensure they are still relevant and desired. Rules based on a payee or merchant name may stop working if the name description changes slightly later in the year due to a change in credit card processing service.
  • How Rules Work. When a rule is created, it will apply to future transactions that meet the criteria. It will not go back and rename or recategorize your old transactions. Using the bulk/group editing feature (described above) is a good way to change existing transactions.
  • Rules Example. If your auto insurance is with Progressive, you may see a recurring transaction “Web Authorized Prog” show up in Mint for your bank account. With a rule, you can automatically rename these transactions to “Progressive Auto Insurance” and assign them to the category Auto Insurance.
  • Rules Overuse. You may be tempted to create rules for just about every transaction to make them as neat and tidy as possible. For example, a payment on your Discover card may show up as “payment Discover” and you may want to rename that to “Discover Card Payment.” There will be a lot of different transactions where you would like to do that. Your resulting data and reports would look nicer, but it requires a lot of rules. So, keeping rules to a minimum of only what is required can be a benefit. You may see a group of similar transactions and be tempted to use a rule to correct a category assignment. Consider instead using the Bulk/Group Edit feature for one time changes or changes to older transactions.
• Search on Amount. If you are looking for a particular transaction, try searching on the amount of the transaction if it is unique.
• Separate Accounts. Presumably it’s possible to setup a Mint user account that synchronizes only with your business checking account and credit card. You would avoid the problem of having to identify all personal and business transactions. However, this may not work for people who have a single credit card in use for services like Apple, where some purchases are personal and some are business.
• Split Transactions. Avoid having any split transactions. The split feature is available in most financial software as a way to have a single purchase transaction distributed across two or more expense categories. In a program like QuickBooks, the transactions still show up as a single line item, but you can see the separate parts also. With Mint, split transactions become multiple Mint entries.

PHASE #2 – Export to Microsoft Excel

Once you know your transactions in Mint are all properly assigned to the correct income/expense categories, and descriptions are as you wish, then you can export all the transactions to a CSV file that is readable by Microsoft Excel. Follow these steps to export your Mint data.

1. Go to your transactions page. [View Here]
2. Scroll to the bottom of the transactions page.
3. You will find the Export option in the bottom right of the page. It’s in small text below the words “Showing 25 50 100 transactions” where you will see Export all ____ transactions.
4. This will save a file called transactions.csv in your downloads folder.
5. Use Excel to open the CSV file and then save it in Excel Workbook file format.

A free online version of Excel is available, or you can subscribe to use the software for $70/year (1 user), $100/year (6 users), or $150 as a one-time purchase for one user.

You can also use free software like LibreOffice to open the CSV file if you don’t have Microsoft Excel. LibreOffice Calc is a free spreadsheet program that works like Excel

NOTE: You can actually perform an export anytime you wish as a method of backing up your Mint data.

PHASE #3 – Review Transactions in Excel

As you are reviewing your transactions in Excel, you may see some incorrect entries. You can make corrections in Excel, but remember to go back to Mint and make the corrections there so they are retained in the future. Also, remember to save your file in Excel format, not CSV format. This will preserve formatting and other layout changes.

Save your file periodically using the Save As feature and give the file a revision or version number, “My File Name v2” so you can have backups of previous versions in case you need to go back a step.

Here are some tips and tools for working in Excel.

1. Freeze Panes. The top row of your spreadsheet will have the column headings that describe each column of information in the spreadsheet. Select (click on) cell A2, the cell just below cell A1 in the top left corner of your spreadsheet. Then under the Window menu choose Freeze Panes. This will keep the column headings visible at the top of your page.
2. Sort. Under the Data menu (or ribbon) in Excel, choose the Sort option. You can choose to sort the transactions first by Category, then by Transaction Type, then by date.
3. Categories Sorted. When you use the Sort feature as described above, you will be able to easily skim through all your transactions looking for discrepancies.
4. Personal Transactions. The category sorting should allow you to easily find and remove expense and income categories that are not tax related.
5. Category Totals. As you look at your Excel file, with the entries sorted as described above, you will see the amounts for each transaction within a category. You can create a formula entry to the right that will give you a total for a particular expense or income category. To begin the formula, type the following in a cell, sum=( When you type that, you can then use your mouse to highlight the series of numbers you would like added up. Be sure to get a total for debits and credits separately if applicable (such as purchases and returns). Then subtract credits (returns) from your expenses for the category. Many categories will have only debits and no credits. For example, while you might take a product back to the store for a refund, you probably won’t be seeing credits for rent, electric bills, car payments, etc.

The above steps will help you review your transactions and then get totals for income and expense categories.

PHASE #4 – Tax Organizer or Workbook

If you are following the instructions on this page, it’s probably because you have a small business and need to keep track of your income and expenses for tax purposes. If this is the case, then you probably have a tax service or tax accountant to assist you with filing your taxes each year. Rather than giving your financial advisor a box filled with paper receipts, it’s better to provide an organized printed (or PDF) statement of your income and expenses. Most accounting firms and tax services will have you complete a basic form each year to answer necessary questions and provide the details needed to complete the tax return. If you use the same tax company each year, you will have the advantage of working with a tax return workbook that has much of your previous year’s information already provided. This saves a lot of time.

As you go through your tax return organizer, you’ll be answering questions and providing totals for income and various business-related expenses. This process will be made much easier and the answers will be very precise if you’ve completed all the steps described above.

You could probably accomplish something very similar by using Quicken or QuickBooks to generate reports. Some people find the spreadsheet approach to be appealing because it offers greater detail of control over the reporting and viewing of the data.

DIY Business Billing and Accounting

If you have a consulting business where your time and materials are charged. You can use the following three-step process to manage your business.

1. Work Notes. Every billable project you work on can have notes. Even brief notes can help keep track of all work done. Use a note keeping app like Evernote to track all your work. Or create separate text documents with notes for each job. Each invoice can have a unique serial number that is the date (as year month day) and time. This ensures that they will be listed in order. Multiple entries in an invoice for the day can be notated with the time if there are breaks or interruptions in the work. This ensures lean billing so people only pay for work done. Not everyone will want details notes of work done, but those who do with appreciate this being available. You can easily search and organize work notes by client name or search for a specific date.
2. Invoice Tracking. When you are finished with your work for a customer, you can enter the basic information for each invoice into a spreadsheet. This will create a single location for all of your invoices. Include date – time, client name, amount charged, discount if any, total due, amount paid, etc. If the payment is received on the same day then the invoice is marked as paid. Unpaid invoices can be easily reviewed and followed up on later.
3. Invoice Creation. Create a blank invoice in a word processing program and use that as the layout and format you will use for any invoices that are printed or sent out as PDF files via email. A typical invoice would have your logo, your business name, contact information, a place for the date and invoice number, the customer name, and an invoice total. Include any cost of materials as desired. Open that file and use Save As to create an invoice using the filename as the invoice number of year month day time and customer name. The resulting file can be printed or saved as a PDF and sent by email. I like to use an Apple iPad with an Apple Pencil stylus to include some handwritten notes on each invoice with a “thank you” message.

The above three step system can accommodate accounts receivable for most small businesses. Your business income and expenses can be tracked and reported using Mint as described above.