New in Transaction Pro Importer 5.0! You can now can import bank statements from excel, csv, text, Quicken (.qif) or Microsoft Money (.ofx) file into QuickBooks. To make this process even easier Transaction Pro Importer includes a powerful feature called payee alias matching. Most times financial institutions use different names that those in your QuickBooks company file nor do they provide the expense or income accounts that you want to use in QuickBooks. The Payee Alias feature is available on the following import types: bank statements, bills, checks and credit card charges.
In this post we will provide an overview of how to import bank statements into QuickBooks using Transaction Pro Importer. On the first screen of Transaction Pro you will select the browse button to select your import file. I have downloaded a Quicken file (qif) from a bank as an example and have selected the browse button to select the file. We will also select an import type of bank statement. Then select the next button to advance to the next screen.
On the file contents screen you will see the data in your import file in a grid format. Select the next button.
The next screen in Transaction Pro Importer is the mapping screen. The required QuickBooks fields are indicated in red. Please note the following below:
- We are not selecting an import file column for the the QuickBooks Fields, Account, as this Quicken file does not include the account and we will create the accounts during the Payee Alias matching. You can easily select the import file column that you want to have matched to the QuickBooks Fields, by putting your cursor in the middle column, Import File Columns.
- As we want these bank transactions to post directly to the QuickBooks account, Checking, we typed this value in under the Static Value/Formula column next to the QuickBooks field, Bank Account.
Select the next button to advance to the next screen.
You will get an error that some required fields are missing. This is because the Quicken file did not have the QuickBooks accounts. We will select the accounts during the Payee Alias step which is next.
On the next screen select the Aliases button and this is where we will match the name in the Quicken file to the names in QuickBooks and assign accounts.
Now we will walk though how to set up an Alias for the first line in this import file. We will need to include certain key characters contained in the the Payee Column in the bottom half of the screen.
The first step is to select certain key characters from the payee column at the bottom of the Alias screen. In this case we are going to use “Bank of Amer.” Once you have determined the Keywords the next step is to select a name. In this example the vendor, Bank of America, does not exist in this QuickBooks company file so I am going to use Transaction Pro to create it. First I will select <Add new> from the Payee drop down box. In the Add New Payee box I am going to select Vendor and then type in the name of the vendor that I want to create and then select OK. Transaction Pro will create this new vendor in the QuickBooks company file.
Then the next step is to select an account to be associated with the keywords box. In this example I am going to choose an already existing account by using the drop down under the Account field and I am going to select Bad Debt and then select the Add/Update button. You also have the option to create a new account if you want we just have elected not to show that step here.
After selecting the Add/Update button you will see that the bottom part of the screen that has the grid titled, “Alias Definitions Applied to Import File,” now has the name and account columns completed. Review this data for accuracy and if everything looks correct then select the Apply button. This mapping will be saved your next import.
This next screen shows how the data will be mapped to your QuickBooks fields. The QuickBooks fields are listed at the top of the grid and the data follows in rows immediately below. Review this data to make sure that data is mapped correctly. Select the next button.
On the last screen of Transaction Pro you select how any new items that do not exist in your QuickBooks company file will be set up by making selections from the drop down menus. Please note this last screen needs to be completed even if you are not mapping data to the items item field. Select the finish button to start the import.
The Payee Alias function has more features. We will publish another article on how to use these features in another blog post.
You can read more about Transaction Pro Importer for QuickBooks and also download a free demo to try this out here.