Importing Credit Card Statements into NetSuite as Journal Entries

XLNS NextGen | Learn how to download your credit card statement from the bank and create a corresponding Journal Entry

Updated over a week ago

The problem

You need to close your books and it's time-consuming to enter credit card transactions one by one. Leverage Excel formulas and CloudExtend to make this a 30-second process every month. See it in action in the video below and refer to the steps below for the formulas used.

The video above shows this in more detail. We recommend that you create a re-usable Excel template that has all the data necessary to validate your Journal Entry prior to upload.


Template Layout

πŸ’‘ Tip: It is best to check out the forms and fields and identify the header, column, and required fields in NetSuite that you may need to build the template.

  • Custom Form

  • Tran ID

  • Tran Date

  • Credit Card Type (This is a custom field, permission is needed for your NetSuite Role)

  • Memo

  • Line List: Account

  • Line List: Entity (Name in NetSuite)

  • Line List: Class (Optional)

  • Line List: Debit

  • Line List: Credit

  • Line List: Memo


Configure Excel Workbook

Create a new Excel workbook and save it as an Excel template so it can be re-used by yourself or your users.

πŸ’‘Note:

The formulas below may refer to table names that are different than yours. Adjust accordingly.

  • Create your lookup tables as individual worksheets in the Excel template file

    • Create a worksheet named GL Accounts Download and use CloudExtend to download a list of your chart of accounts

    • Create a worksheet named Vendor List Download and use CloudExtend to download a list of your vendors

    • Create a worksheet named Lookup Table with the following 5 columns. This will be used to populate the G/L accounts and entity names to assign to each line of your Journal Entry. You'll want to keep the master template up to date with all the merchants that you receive charges for.

      1. Doing Business As - This should be a name that appears on each line of your credit card statement, for example with American Express it is typically called Doing Business As.

      2. Associated G/L account - This is the name of the G/L account that you would typically assign to this merchant. It should match the name of the account from your GL Accounts Download.

      3. GL Internal ID - This is a formula field that will append the NetSuite internal ID to your account name. When CloudExtend sees [[ ]] it ignores everything but the values in [[ ]] and treats those values as the NetSuite internal ID. This allows for faster uploads.
        ​=[@[Associated G/L Account]]&" "&"[["&XLOOKUP([@[Associated G/L Account]],GLAccounts[[#All],[Acct Name]],GLAccounts[[#All],[Internal Id]],"No Match")&"]]"

      4. Vendor Name - For those transactions that you want to track by entity you should enter the name of the vendor to associate them within NetSuite. It should match the name of the account from your Vendor List Download.

      5. Vendor Internal ID - Like (3) this is a formula field to look up the NetSuite internal ID.
        ​=IFERROR([@[Vendor Name]]&" "&"[["&XLOOKUP([@[Vendor Name]],Table5[[#All],[Company Name]],Table5[[#All],[Internal Id]],)&"]]","")

  • Add three new worksheets. We recommend naming them with numbers to help guide your users.
    ​

Step 1: Download Credit Card Statement

Download CC Stmt - this will be where you copy your credit card statement details that were previously downloaded to

Step 2: Paste the data from the credit card statement

Paste CC Stmt Here - this will be where you copy the lines from your credit card statement that you wish to upload

Step 3: Review CloudExtend Journal Entry Template

This will contain formulas that will perform the lookups from the downloaded credit card statement, this is your CloudExtend template.

  • Now, load your CloudExtend template to the worksheet named 3. Review Journal Entry and use formulas to refer back to the 2. Paste CC Stmt Here worksheet. These formulas will populate your CloudExtend template with the desired data from your credit card statement.

    1. In order to upload as a single Journal entry the date must be the same on every row. You'll probably want to manually enter the date for your Journal entry, however, consider the formula below which will return the max date from all the transactions.
      ​=IF(MAX('2. Paste CC Stmt Here'!$A$8:$A$62)=0,"",MAX('2. Paste CC Stmt Here'!$A$8:$A$62))

    2. This is optional, however, we recommend creating a custom transaction body (list) field called Credit Card Type and applying it to Journal Entries. This will allow you to generate reports based on the type of credit card used.

    3. For the 1st row, you would manually enter the account used for credit cards payable. Then, for subsequent rows, you will enter a formula that looks up the merchant and assigns the proper account.
      ​=XLOOKUP([@[Line List: Memo]],Table3[[#All],[Doing Business As]],Table3[[#All],[GL InternalID]])

    4. For the first row, you would add the credit card vendor manually. Doing so will allow you to later use the write check functionality in NetSuite to make a payment to the credit card company as well as track the transactions. Then, for subsequent rows, use the formula below that looks up the entity in NetSuite to assign the line-level transaction to.
      ​=XLOOKUP([@[Line List: Memo]],Table3[[#All],[Doing Business As]],Table3[[#All],[Vendor InternalID]])

    5. Leave the debit empty in row 1 and in all subsequent rows use a formula that looks up the charge from the credit card company.
      ​='2. Paste CC Stmt Here'!E8

    6. For the Line List Credit field you only need a formula in row 1 which will sum all of the debits for you to help ensure that your entry is in balance.
      ​=SUM(Table4[[#All],[Line List: Debit]])

    7. For the line list memo field on row 1 manually enter the desired text such as "Amex Bill". Then, in subsequent rows use a formula that looks up the name of the payee on each line of the credit card statement.
      ​='2. Paste CC Stmt Here'!G8

  • Now, save your Excel file as an Excel template

Step 4: Add missing Vendors to the Look-Up Table

If you have any missing vendors, make sure to add this to the vendor list download and associate it with the proper Chart of Account. Download the company name with the internal ID associated with it. This will be used in the Journal Entry upload.

Step 5: (Optional) Upload missing vendors to NetSuite (if desired)

You can create a CloudExtend Vendor Upload template. With the following layout:

  • Company Name

  • Entity ID (automatically generated by NetSuite)

  • Is Person (TRUE or FALSE)

Step 6: Upload the New Journal Entry

Click Upload to NetSuite then hit the Upload Records button.

πŸ’‘ Tip! Header values are repeated to let the system know this is one record upload. Different header values will create separate records.


Any Feedbacks?

If you wish to provide feedback on the NextGen app, click the button below! We love to hear from you!

Did this answer your question?