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.

Steps to follow

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.

Create your Excel template and formulas

  • 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. 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 with in NetSuite. It should match the name of the account from your Vendor List Download.

      5. Like (3) this is a formula field to lookup 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.

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

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

    3. Review Journal Entry - 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 formular 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

Use your template to upload new credit card transactions.

  1. Open Excel and choose file->new from template and select the template you just created.

  2. Download your credit card statement from the bank

  3. Paste the statement into the worksheet named "1. Download CC Stmt"

  4. Copy the lines from the credit card statement you just pasted and paste them into the worksheet named "2. Paste CC Stmt Here"

  5. Navigate to the worksheet named "3. Review Journal Entry"

  6. Load CloudExtend, review, and upload your Journal Entry.

    1. If you encounter any rows with #N/A you should update your Lookup table (ideally the template creator will do the same on the template file so it will be available next time it is used)

💡 NOTE: once you have gone through the trouble of building your related lookup tables and formulas the total time to create this Journal Entry should be less than one minute.

Did this answer your question?