How to import open bank transactions to NetSuite

Importing open bank transactions is one of the last data migration tasks. Users must load two sets of transactions - (1) uncleared bank transactions from the previous bank reconciliation and (2) new cash transactions dated in the current month. Loading open bank transactions is required to complete the first bank reconciliation. This blog post will teach us how to prepare and upload open bank transactions from QuickBooks Online. My example is using QuickBooks Online. However, the theory is the same regardless of your legacy system.

This post is relevant for companies that have migrated data using a net change approach. If you upload detailed transactions, this step won’t be necessary. To learn more about uploading detailed transactions, check out this article.

Here are items to address before starting:

• Complete the last month’s bank reconciliation in QuickBooks Online. This step will reduce the number of imported transactions.

• Review your previous bank reconciliation and address any old, uncleared items that must be reissued or voided. It will be easier to clean up these transactions in QuickBooks Online.

• Create a suspense account for the offsetting general ledger (GL) impact of the open bank transactions in NetSuite. This account can be named OPENBANK and configured as an equity account type.

After addressing the above items, we are ready to prepare the data load for uncleared transactions from the last bank reconciliation.

Start by navigating to last month’s bank reconciliation in QuickBooks Online. Check out this Intuit help article for assistance navigating there. Once you are on the Reconciliation Report for the selected bank account, scroll to the ‘Uncleared checks and payments’ and ‘Uncleared deposits and other credits’ sections. Unfortunately, QuickBooks Online won’t allow you to export this report to Excel. However, if you select the date and all the following fields, you can copy and paste the transactions into Excel. See the screenshot below.

I’d recommend copying and pasting both sections into a single spreadsheet tab. Once complete, your spreadsheet should look something like this:

Next, we need to add any bank transactions posted in the current month. Navigate to the balance sheet, drill into the bank account’s transaction report, and export this list to Excel. The out-of-the-box list will include the columns we need. Now, consolidate the new bank transactions with the uncleared bank transactions on the spreadsheet. Users should only select transactions dated AFTER the previous month’s reconciliation date. QuickBooks will include the transactions dated before in the outstanding transaction list. Of course, this step isn't necessary if you are going live with NetSuite at the end of a month.

Once we have a single spreadsheet with all the open bank transactions, we need to map the activity to NetSuite. Users will need to:

• Map the transaction type - users cannot import certain transaction types into NetSuite. In those cases, I usually defer to journal entries. See the sample transaction type map below. Each NetSuite transaction type will need a separate CSV upload template.

  

• Map the payee/customer - Certain transactions, such as invoices and checks, require the name to be populated. Journal entries do not need a name. The payee and customer entity should exist in QuickBooks and NetSuite. See my article on preparing a segment map file for more details on preparing this information.

• Map the account - The open bank transactions will only involve two general ledger accounts, the OPENBANK suspense account, and the bank account. Be sure to include both lines in the journal entry import file.    

Now comes the heavy lifting. Users need to prepare the CSV upload template for each NetSuite transaction type. Your NetSuite implementation team should provide these templates for you. If you or your team do not have the capacity, the team at intheBlk consulting can assist.

After successfully uploading the files, the bank balance will be incorrect. For example, if the bank balance was $50 on 4/30/2022 and we load $15 of open transactions, the new balance should be $65, and the OPENBANK expense account should have a $-15 balance. The final step is to post a journal entry reversing the impact of the open bank transactions. In the example above, we would debit OPENBANK and credit the bank account for $15.

When preparing the first bank reconciliation in NetSuite, select all monthly financial journal entries plus the journal entry reversing the OPENBANK GL activity. The cleared balance should match the last month’s bank balance on the reconciliation in QuickBooks.

Here are a few final tips and tricks:

• Set the posting period for all open transactions to the current period. This trick will ensure that the GL impact of the OPENBANK account is always zero and only impacts a single accounting period. If you don’t do this and the suspense account is an income statement account type, older bank transactions will get rolled into retained earnings.

• If the OPENBANK account is not zero, something is off. In my experience, this usually happens when someone voids a check from the open bank import and uses the old GL account to post the new transaction. So, if you need to cancel a bank transaction, always post the new transaction to the OPENBANK account.

• Deactivate the OPENBANK account after tying out this process. This trick will prevent future account changes that need to be reconciled.

• If you need to complete this process for multiple bank accounts, include all transactions on the consolidated transaction file. In this case, add an extra column for the bank account and map this account into NetSuite.

Do you or your organization need an extra hand with the data migration component of your NetSuite implementation? OptimalData Consulting can prepare and execute your customized data migration strategy. Contact us to find out more, or review our implementation resource page for more helpful articles.