Spreadsheet import - account balances from Quickbooks

Recommended account setup in QuickBooks

Steps in QuickBooks

From within your QuickBooks application, select
Reports
Accountant & Taxes
Trial Balance
.
In the
Dates
field, select the date range for the data that you will import into
Accounting CS
.
Select
Excel
on the toolbar and choose
Create New Worksheet
.
Choose the
Create new worksheet
option (
new
or
existing
workbook) in the
Send Report to Excel
window, then select
Export
.
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's not password protected.
note
Be sure that the spreadsheet is closed and remains closed during the import process.

Select the source file in

Accounting CS

Select
File
Import
Spreadsheet
.
In the
Source Data
screen, select the appropriate client from the
Client name
field.
Select
Account Balances from QuickBooks
from the dropdown in the
Data type
field.
In the Import File section, enter the path and filename of the spreadsheet file to import, or select
Browse
to go to the file.
Select the worksheet within the spreadsheet file to import then select
Next
.

Map spreadsheet columns

If you saved mapping information from a prior import as a mapping template, that template will be included in the dropdown in the
Template
field. If applicable, select the appropriate template.
If the spreadsheet includes column headings or other rows of data that shouldn't be imported, mark the checkbox in the
Omit row
column for that row. The application won't validate or import data in that row.
For each column, select the column heading in the grid, then select the applicable mapping item from the dropdown in the
Column <x>
field above the grid.
Mapping item	Additional info	Additional info 2	Required?
Account Number	None	None	At least Account Number or Account Description must be mapped.
Account Description	None	None	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)	None	No
Tax Information	Tax Code Tax Code Unit M-3 Tax Code	None	No
Beginning Balance	<Year>	Dr/Cr, Debit, Credit	No
Unadjusted Balance	<Period end date>	Dr/Cr, Debit, Credit	No
Budget	<Period end date>	Dr/Cr, Debit, Credit	No
Adjusted Budget	<Period end date>	Dr/Cr, Debit, Credit	No
Budget 3	<Period end date>	Dr/Cr, Debit, Credit	No
Budget 4	<Period end date>	Dr/Cr, Debit, Credit	No
Budget 5	<Period end date>	Dr/Cr, Debit, Credit	No
note
In most cases, even if some or all of the QuickBooks accounts don't 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.
After you've mapped all applicable columns, select
Next
.
The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data then select
Next
.

Map additional data types

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 next to each account that doesn't conform to the mask, and it won't 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
options to jump to each error in the grid and correct the data.
Do not import.
Accounting CS
doesn't import data for that account.

Choose import options

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.
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:

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:

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
Select
Import
to begin the data import.

Review import diagnostics

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.
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

Assign tax codes

Select
Actions
then
Enter Trial Balance
.
Select
View Maintenance
in the upper-right corner of the screen.
In the
View Maintenance
window, select the appropriate view description, then select
Edit
.
Highlight a blank row (denoted with an asterisk *) and select
Tax Code
from the dropdown in the Column Type section.
Select
Enter
then
Done
to return to the
Enter Trial Balance
screen.
For each account in the
Enter Trial Balance
screen, select the applicable classification code, classification subcode, and tax code.
note
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 doesn't automatically enter a tax code.

Sample spreadsheets

Account balances from QuickBooks (with account and description)
Account balances from QuickBooks (with descriptions only)