Sync with Quickbooks or Import and Read Bank & Credit Card Statements

We just released an update to SimpleRent that will allow you to import statements from your bank or credit card provider, load those transactions and reconcile them as accounting entries (expense, income records) in the SimpleRent system.  You can also use these features to read transactions from Quickbooks.  In either case, SimpleRent requires that your files are in CSV format, which most online banking websites and credit card vendors can be configured to export their data as.  Quickbooks also supports this format.

The import, load and reconciliation processes are very easy to set up and will save you a lot of data entry time.

Here's how to get started:

1. Under the Accounting menu, choose the "Bank & Credit Card Statements" item

2. The "Statements" screen appears.  This is where you add each statement you want to import and load.  Click "Add New Statement" to add your first one:

3. If this is the first time you've added a statement you won't see anything in the "Statement Type" drop-down list, so you'll need to add a new statement type - this is what defines the file format/template for the statement you're loading.

Click "New Statement Type" - specify the kind of statement you're loading - for example, the name of the bank or credit card.  Don't put statement-specific labels in the statement type field.  In other words, don't use something like "July Statement" here - just use "First Credit Bank of Springfield", for example.

Select whether the statement is a bank statement or a credit card statement - this choice impacts how debits and credits are interpreted.

4. Specify the file containing the statement's transactions - click the Browse button and navigate to where you've previously downloaded the file from your online banking or credit card vendor's website.  Note that the file must in CSV format (comma-separated-value) with a .csv extension.

CSV is a a text file format that can be opened in a text editor (Notepad), or Excel.

(You might have to explore your online banking portal or credit card online statement website to find the appropriate options to export your account statement data as CSV format. It might also simply be called "Excel" format as well.)

Every file is different, and sometimes a vendor might not adhere perfectly to a clean data export standard - there might be garbled data, mismatched columns, or header rows or extraneous data in the file. In most cases it is wise to first open the file in a text editor (i.e. Notepad) first to make sure your file looks good. It should simply contain a row/line of data per transaction, and each transaction field is separated by commas. If you open it in Excel, the file should contain one transaction per row, and each field should be in a separate column. Remove any other lines or blank rows that aren't actual data.

SimpleRent will give you the option when you add a new statement to "Ignore the Header Row" - check this box to ignore the first row of data if it contains useless non-data or header titles.

Finally, give the statement file a name or description (here you can be more specific, like "June-July Bank Statement") and click "Add Statement" - the file will be uploaded to your SimpleRent account.

5. You'll now see that your statement is shown, but that no transactions have been loaded yet.  Click the Load Transactions button to map the fields/columns in your file to the fields that SimpleRent can read:

On this screen you should what field type each column in your file represents - for example, one column is probably the Date of the transaction, while another column is the Amount.  Others are most likely descriptions of the item and additional/memo information.

Each column must be its own type (no duplicates) but you can choose between Date, Amount, Name (description), Memo, and you can ignore a column as well. Some files will contain data that SimpleRent can't import, so you are limited to reading only these types of data. Make sure you specify the date in the correct format for your file (m/d/yyyy, yyyymmdd, etc).

Once you have configured the field types, click "Save Configuration & Load Transactions". Note: once you have defined a statement type, you will not have to do it again for that bank or credit card, unless the format from the vendor changes. You should create a separate statement type for each bank or credit card that you want to import.

6. The Statement Transactions screen loads, showing your imported transaction data:


Reconciling Transactions

Each transaction that can be loaded from your file is displayed on the Statement Transactions page. Your job is to reconcile each of these transactions by selecting what account code to allocate the transaction to. Income and expense account codes are shown appropriately for each transaction type (debit, credit).

Click a row to display the account code drop-down list. Choose the account code the transaction should be filed under, and click the checkmark to save it. If you want to ignore the transaction and not create an accounting entry for it, click the X button.



As you go, each transaction is categorized and accounted for, and this screen saves your progress. You can leave this page and come back to it later, breaking the data entry effort up if you want. The screen keeps track of your reconciliation progress (percentage) as you make changes:

Reconciliation Tips

You should most likely ignore payments on credit cards that show as Credits on the statement. This isn't truly income, and it isn't an expense - it's funds that you have moved from one account (bank) to another (credit card) to make payments on the card. SimpleRent is a simplified ledger system that doesn't track these types of events like a true double-entry accounting system like Quickbooks might.

Similarly, you should ignore amounts transferred out of your bank account that are not expenses (i.e. things like credit card payments or transfers from one bank account to another), unless you can truly call them expenses. Transaction items on credit card statements where you have used the card to purchase something (and are expenses) should be reconciled with the appropriate expense code.

Finalizing the Reconcilation

Once you have categorized ALL transactions (either by specifying account category codes for them, or ignoring them), you're reading to finalize/complete the reconcilation. Once you have completed the categorization of the last transaction, a popup window will appear to prompt you to "Confirm Statement Reconcilation". This screen will also appear if you click the "Finalize Reconcilation" button as well (only enabled once you have categorized all transactions).



Click the Complete button on this popup screen to finalize the process. Accounting entries (expenses and other income) will be created for the Amount, Date, Description, and Account Code you have selected. No accounting entries will be created for transactions that are set to be ignored.

 

You can jump to the details of each accounting entry here by clicking the link for each item. These transactions are also accessible as regular accounting entries in the accounting areas (ledger, expenses, income, and associated reports):


Once this process has completed, congratulations you are done importing your first bank or credit card statement!

Comments are closed