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
Workpapers 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
Workpapers CS
and be sure that it's not password protected.

Select the source file in

Workpapers 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
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.
Workpapers CS
adds the account as it is entered in the spreadsheet. Select a valid class code to add the account.
Do not import.
Workpapers 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,

Workpapers 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,

Workpapers 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
Workpapers CS
read in the spreadsheet.
Number of new accounts added.
This is the number of QuickBooks accounts that were added to
Workpapers 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
Workpapers 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.

Sample spreadsheets

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