Bank Import Mapping is the first step to being able to import bank and credit card transactions.
Remove Lines above Data / Save as .csv
You will need to set up a mapping record for each account for which you will be importing records. A mapping record is essentially the CSV file that you can normally download from the source of the account. The mapping record tells Studio which columns to import and how to format the data within those columns. You will only need to specify the mapping for each account once and it will be saved for future imports.
To set up a mapping record, you will need three things:
1. A .csv file from your bank with some activity for the account.
2. A vendor address record for the bank or credit card.
3. A cash or credit card account set up in your chart of accounts.
Download Activity
Start by downloading your account activity from your bank or credit card company's website in a .csv (Comma Separated Value, or Comma Delimited) file, which saves the data in a tabular format, like a spreadsheet.
Below is an example from Wells Fargo. Although we can't get examples from all banks, most of the time you will find a link to download the activity from the transaction view for the account.
Note: Studio Designer's bank import function is only compatible with .csv files, which is the most common, generic download available at the time this guide was written. There might be additional .csv files you can save as, the one we recommend is the CSV UTF-8
Example:
Wells Fargo
Note, the bank may change these screens or its process at any time. This is meant as one possible scenario and may or may not be updated as the bank makes changes.
- Select Transactions to view transactions for the account you would like to work with.
- Select Download Account Activity.
- Select the Account and Date Range.
- Select Comma Delimited for File Format.
- Then select Download.
Note: Watch for the download. You'll need to know where your browser saves the file.
Depending on your browser and operating system, the file may appear towards the top of the window, or you may just see an indicator that you can select to open the file. In most default instances, the file will be automatically saved to a Downloads folder or appear at the bottom of your internet browser.
Remove lines above data / Save as .csv
The file you import must be a .csv with nothing or only column headers above the actual data.
Some banks provide Excel exports, but not .csv, so you may need to modify the file before using or importing it.
In the example below I've opened my downloaded file with Microsoft Excel, and there are text lines and blank lines above the data. In this case, I need to delete all of the lines above the data.
To delete these lines, I'll select the lines and right-click within the left (numbered) column to open the submenu where I can select Delete, or you can select Delete from Edit in the menu bar at the top of the screen.
If your file does not already have .csv extension, select File and Save As, then select Comma Separated Values (.csv) for the Format.
Important Notes
1. Negative amounts must have a minus sign (-) denoting that they are negative. Parenthesis are not valid to denote a negative number and you will need to format them to remove the parenthesis and add the - minus sign. This will need to be done prior to creating the mapping record, and each time you import.
2. The Import allows for up to seven (7) columns. If your data file has more than six (6) and data that you want is in anything past the sixth column, you will need to delete un-needed columns before creating the mapping record and each time you import.
3. You can only select one column for amounts. If your bank separates out debits and credits into two different columns, you'll need to combine them into one column prior to creating the mapping record, and each time you import.
Create a Mapping Record
Step 1. Select Accounting> Bank Import Mapping
The Bank Import Mapping list shows all accounts have previously been mapped. There must be a mapping record before records can be imported.
Step 2. To create a new mapping record select New.
Step 3. Click Select File, and select the transaction activity file (.csv) that you downloaded in the previous step.
The first few transactions will be shown so that you know what information is in each column.
Step 4. Select the drop-down menu at the top of each column of data that you'd like to import. The options are listed below and are the only fields that can be imported:
- Check - This is the check number that can be imported if the activity file includes it. In the example above, the bank did not.
- Check Date - You'll also need to select a format. Either mm/dd/yy or dd/mm/yy. This way we'll know if we need to adjust the date as it is imported.
Note: Some files may contain more than one date field. In this case, choose the one that contains the date that you want to use as the transaction date in Studio.
- Amount - The amount of the transaction that you would like to be imported. ***
- Checks/Charges are Negative Amounts - *** If your check/charge amounts in the data file are shown as negative amounts, select Yes for this option.
- Description - If the activity file includes a column that you would like imported into the description field.
- Ignore - If you do not want to import the data in this column.
It's extremely important that you select the correct setting for Checks/Charges are Negative Amounts. If you do not, your transactions may increase your balance, when they should decrease it, or vice versa.
Step 5. When you are ready, select Save. Once you save, the new mapping record will appear in your mapping list and the only information that will be saved is the Account, Vendor, and column selections.
Additional Functions
First Row Contains Titles (see below) - If the first row of your activity file contains titles for the various columns, slide this to on; otherwise, select off. This will determine what information is saved in the permanent mapping record.
**First Row Contains Titles**
Some banks include column titles at the top of the transaction details. If this is the case, your first line of data may look like the file below and Studio Designer may think it is text, not a date or numerical field.
If so, you will need to check the My First Row Contains Titles box so that Studio Designer recognizes the first line as column headers as shown below.
Account - This is the cash account or credit card liability account in your Chart of Accounts into which the transactions should be imported. You will select an expense account at the time you import the data.
Vendor - This is the bank name. As you import records, you'll have the option to automatically use this vendor for all imported transactions.