Spreadsheet import - Account Balances from QuickBooks

Show expandable text

We moved!

Help articles have been migrated to the new Help and Support. You can find help for your products and accounts, discover FAQs, explore training, and contact us!

The Spreadsheet Import Wizard makes it easy to import your QuickBooks clients' account balances from a spreadsheet file created by QuickBooks Pro (including the Premier Accountant and Enterprise editions for versions currently supported by Intuit) or QuickBooks Online.

Recommended account setup in QuickBooks

Although QuickBooks does not require account numbers (only account descriptions), if you will be importing the client's balances into Accounting CS, we recommend that you instruct your client assign account numbers to all GL accounts in QuickBooks. The import will work even if an account number is not assigned to each account, but there will be some additional mapping steps involved.

Steps in QuickBooks

  1. From within your QuickBooks application, choose Reports > Accountant & Taxes > Trial Balance.
  2. In the Dates field, select the date range for the data that you will import into Accounting CS.
  3. Click the Excel button on the toolbar and choose Create New Worksheet.
  4. Choose the Create new worksheet option (new or existing workbook) in the Send Report to Excel dialog, and then click the Export button.
  5. When the spreadsheet opens in Excel, save the file in a location that you can later access to import into Accounting CS and be sure that it is not password protected.

    Note: Be sure that the spreadsheet is closed and remains closed during the import process.

To import subaccounts correctly and to avoid account duplication in Accounting CS, be sure to mark the Show lowest subaccount only checkbox in QuickBooks. The checkbox is in the Company Preferences tab of the Edit > Preferences screen when Accounting is selected in the left pane. Note that the Show lowest subaccount only checkbox is available only when an account number is assigned to every account in QuickBooks.

Selecting the source file in Accounting CS

  1. Choose File > Import > Spreadsheet.
  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select Account Balances from QuickBooks from the drop-down list in the Data type field.
  4. In the Import File section, enter the path and filename of the spreadsheet file to import, or click the Browse button to navigate to the file.
  5. Select the worksheet within the spreadsheet file to import and click Next.

Mapping spreadsheet columns

Use this screen to map the spreadsheet columns to specific data fields in Accounting CS.

  1. If you saved mapping information from a prior import as a mapping template, that template will be included in the drop-down list in the Template field. If applicable, select the appropriate template.
  2. If the spreadsheet includes column headings or other rows of data that should not be imported, mark the checkbox in the Omit row column for that row. The application will not validate or import data in that row.
  3. For each column, click the column heading in the grid, and then select the applicable mapping item from the drop-down list in the Column <x> field above the grid.
    Mapping item Additional info Additional info 2 Required?
    Account Number At least Account Number or Account Description must be mapped.
    Account Description At least Account Number or Account Description must be mapped.
    Account Grouping Account Classification Code
    Account Classification Subcode
    Leadsheet Schedule Code
    Leadsheet Schedule Subcode

    (includes Code and Subcode for
    each account grouping set up for the client)
    --
    --
    --
    --

    --
    Tax Information Tax Code
    Tax Code Unit
    M-3 Tax Code
    --
    --
    --
    Beginning Balance <Year> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Unadjusted Balance <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Budget <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Adjusted Budget <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Budget 3 <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Budget 4 <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --
    Budget 5 <Period end date> Dr/Cr
    Debit
    Credit
    --
    --
    --

    Notes

    • In most cases, even if some or all of the QuickBooks accounts do not have account numbers, you may want to map the account name/description as an account number column. The application will map any QuickBooks account numbers as is and use the account name/description as the account number for any QuickBooks accounts that do not have an account number. If needed, you can modify the account number for any new accounts in screen; however, you cannot modify the account number for any accounts that already exist in Accounting CS.
    • If you map any budget columns and select the year option (as opposed to a specific period-end date) in the second Column <x> field, the application will equally distribute the amount among all periods within the current year. (For example, if you import a balance of $1,200 for a monthly client and select 2015, the application will import a $100 balance in each period for the account.) If you select a specific period end date from the second Column <x> field, the application will import the full budget amount into the selected period only.
  4. After you have mapped all applicable columns, click Next.
  5. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Mapping additional data types

The application displays the Data Mapping - Chart of Accounts screen, where you can map the appropriate Accounting CS account to the corresponding account in the spreadsheet.

In the Accounting CS Number and Description column for each row in the grid, select the corresponding Accounting CS GL account number. The drop-down list includes all GL accounts that were set up in the Setup > Chart of Accounts screen. If a corresponding account is not listed, select one of the following options.

  • Add as is. Accounting CS adds the account as it is entered in the spreadsheet. Select a valid class code to add the account.

    Note: If you add an account as is, it must conform to the Chart of Accounts mask displayed above the grid. The application displays an error indicator import error icon next to each account that does not conform to the mask, and it will not allow you to continue with the import until all account numbers are valid. If you are importing a large number of accounts that may not conform to the mask, you may want to exit the import wizard and change the client's account mask. If the client uses long account descriptions or names that will be converted to account numbers, you may want to set up the client's account mask to use the maximum number of characters to ensure that the account numbers fit within the mask limitations.

    You can use the Error Navigation buttons below the mapping grids to jump to each error in the grid and correct the data. Show me.

    Error navigation
  • Do not import. Accounting CS does not import data for that account.

The Data Mapping screens may include the following fields and buttons.

Click this button to clear any changes made in the current Data Mapping screen.

If the application encounters a blank field in the spreadsheet, and that blank field is mapped to a required field, the application enters Add as is in that field by default. To change the default information to Do not import or to a blank field for any of these fields that you have not changed, select the desired default value from this field.

Choosing import options

The Import Options screen opens only if you mapped at least one balance-based column. Use this screen to specify how the spreadsheet data should be imported.

  1. Choose the applicable Chart of Accounts import option.
    • Append to existing chart. The application adds new accounts and their balances to the client's Chart of Accounts. If the spreadsheet includes data for any existing accounts, the application updates data for those account with the new information.
    • Zero existing balances and import new balances. The application adds new accounts and their balances to the client's Chart of Accounts. If the spreadsheet includes data for an existing account, the application zeroes the balances for that account for the selected dates, and imports only the balances in the spreadsheet file. If the spreadsheet does not include data for an existing account, the application zeroes the balances for that account.
  2. Choose the applicable balance import option.
    • Current period balances. The application imports the balances from the spreadsheet directly into the period selected for the balance column.

      Example

      Example

      The application adds the balance from the spreadsheet to the existing balance in the client data to create year-to-date balances. During the import process, Accounting CS creates an activity journal entry to store balances that are imported from the spreadsheet.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/16
      100.00
      100.00
      100.00
      200.00
      02/28/16
      200.00
      200.00
      200.00
      400.00
      03/31/16
      400.00
      300.00
      300.00
      700.00
      04/30/16
      700.00
      400.00
      400.00
      1100.00
      05/31/16
      1100.00
      500.00
      500.00
      1600.00
      06/30/16
      1600.00
      600.00
      600.00
      2200.00
      07/31/16
      2200.00
      700.00
      700.00
      2900.00
      08/31/16
      2900.00
      800.00
      800.00
      3700.00
      09/30/16
      3700.00
      900.00
      900.00
      4600.00
      10/31/16
      4600.00
      1000.00
      1000.00
      5600.00
      11/30/16
      5600.00
      1100.00
      1100.00
      6700.00
      12/31/16
      6700.00
      1200.00
      1200.00
      7900.00
    • Year-to-date balances. The application calculates current-period activity based on existing prior-period balances and creates a journal entry with the net change for the period.

      Example

      Example

      The balance in the spreadsheet data should be the current year-to-date balance in the client data. The application calculates the difference between the current year-to-date balance in the client data and the balance in the spreadsheet data. During the import process, Accounting CS creates an activity journal entry to store balances that are imported from the spreadsheet.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/16
      100.00
      100.00
      0.00
      100.00
      02/28/16
      100.00
      200.00
      100.00
      200.00
      03/31/16
      200.00
      300.00
      100.00
      300.00
      04/30/16
      300.00
      400.00
      100.00
      400.00
      05/31/16
      400.00
      500.00
      100.00
      500.00
      06/30/16
      500.00
      600.00
      100.00
      600.00
      07/31/16
      600.00
      700.00
      100.00
      700.00
      08/31/16
      700.00
      800.00
      100.00
      800.00
      09/30/16
      800.00
      900.00
      100.00
      900.00
      10/31/16
      900.00
      1000.00
      100.00
      1000.00
      11/30/16
      1000.00
      1100.00
      100.00
      1100.00
      12/31/16
      1100.00
      1200.00
      100.00
      1200.00
  3. Click the Import button to begin the data import.

Reviewing import diagnostics

The Data Analysis screen displays a summary of the information that was imported from the spreadsheet and an explanation of the results for the imported data. Review the information. If necessary, you can click the Back button to make changes in the mapping and options screens. To print a simple report of the import results, click the Print button to open the Print Preview window, where you can view and print the report.

  • Spreadsheet import successful Successful
    • Number of accounts read. This is the total number of accounts Accounting CS read in the spreadsheet.
    • Number of new accounts added. This is the number of QuickBooks accounts that were added to Accounting CS. If the number of accounts added is lower that the number read, it could be because some of the accounts that were read already exist in Accounting CS.
    • Number of accounts not matched in the import. This usually includes the 999 account.
    • <period end date> Unadjusted balances successfully imported. This is the number of unadjusted account balances that were imported from the spreadsheet.
    • Beginning balances successfully imported. This is the number of beginning balances that were imported from the spreadsheet.
  • Spreadsheet import exception Exception
    • A journal entry's distribution amounts must sum to zero. This indicates that the distribution amounts for the journal entry do not sum to zero.

When you are satisfied with the data that will be imported, click the Finish button.

Assigning tax codes

After you import account balances from QuickBooks, you will need to manually assign tax codes to each account to use in your tax application. This is a one-time assignment for each account. In subsequent imports, you will need to do this for new accounts only.

  1. Choose Actions > Enter Trial Balance.
  2. Click the View Maintenance link in the upper-right corner of the screen.
  3. In the View Maintenance dialog, select the appropriate view description, and then click the Edit button.
  4. Highlight a blank row (denoted with an asterisk *) and select Tax Code from the drop-down list in the Column Type section.
  5. Click Enter and then Done to return to the Enter Trial Balance screen.
  6. For each account in the Enter Trial Balance screen, select the applicable classification code, classification subcode, and tax code.

    If a tax code has been assigned to a subcode, that application automatically enters the tax code when you select that subcode for new accounts only. If you select a new subcode for an existing account, the application does not automatically enter a tax code.

Sample spreadsheets

The following sample spreadsheet files are available for you to download and review. The sample spreadsheets are set up with commonly used columns and sample data. You can modify the formatting, column, and data to fit your needs.

Note: If you import the sample data into a live client record, please remember to delete the imported data when you are finished.

Click a link below to open the spreadsheet file, and then save the file to the location specified in the Spreadsheet field in the Import Data tab of the Setup > File Locations dialog.

Was this article helpful?

Thank you for the feedback!