excel add-in user guide

104 Pages·2016·3.82 MB·English

EXCEL ADD-IN
USER GUIDE
VERSION 12.0
Copyright © 2017 Thomson Reuters. All Rights Reserved.
Thomson Reuters disclaims any and all liability arising from the use of this document and does not guarantee that any
information contained herein is accurate or complete. This document contains information proprietary to Thomson Reuters
and may not be reproduced, transmitted, or distributed in whole or part without the express written permission of Thomson
Reuters.
Acknowledgement is made to all other brand or product names referred to in the text that are registered trademarks,
trademarks, or trade names of their respective owners.
EXCEL ADD-IN FOR ONESOURCE INCOME TAX. . . . . . . . . . . . . . . 1
GETTING STARTED. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
ACCESS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
S DOWNLOADING THE EXCEL ADD-IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
REGISTERING THE EXCEL ADD-IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
ACCESSING DATA IMPORT FROM A SPREADSHEET . . . . . . . . . . . . . . . 11
DATA IMPORT FUNCTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
ADD-IN INFO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
T SETTINGS..............................................................................................................12
PROXY...................................................................................................................13
CLIENT MANAGER SETUP (USER WITH MULTI-CLIENT ACCESS)..................14
DEBUG...................................................................................................................15
BINDER GROUPS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
BINDER GROUPS REQUIRED FOR DATA IMPORT FUNCTIONS. . . . . . . 16
N
USING PREVIOUSLY MAPPED IMPORT FILES DATA IMPORT. . . . . . . . . 16
CREATING A NEW BINDER GROUP TO USE WITH DATA IMPORT. . . . . 17
NEW MAPPING IMPORT FILE TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
NEW MAPPING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18
MAPPING DATA ON AN EXISTING WORKSHEET . . . . . . . . . . . . . . . . . . . 18
NAMING NEW MAPPING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
E
MAPPING A NEW WORKSHEET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
DELETING AN EXISTING XML MAP IN A SPREADSHEET. . . . . . . . . . . . . 26
USING THE DEPTH ELEMENT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
MORE ABOUT ELEMENT HEADINGS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
ADDING HEADINGS..............................................................................................27
RETAINING EXISTING HEADINGS.......................................................................30
T
INDENTIFYING MAPPED ELEMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
GLOBAL IDENTIFIERS MAP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
COPYING MAPPING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
ROLLING OVER EXCEL ADD-IN MAPPING. . . . . . . . . . . . . . . . . . . 36
N
COPYING MAPPED ELEMENTS TO WORKBOOKS . . . . . . . . . . . . 39
TEMPLATES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
DOWNLOADING THE TEMPLATES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
VALIDATING WORKSHEETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
VERIFYING WORKSHEET INPUT FOR EXPORT . . . . . . . . . . . . . . . . . . . . 43
O
SAMPLE EXPORT ERRORS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
EXPORTING MAPPED DATA TO ONESOURCE INCOME TAX. . . .45
TRANSFERING DATA TO ONESOURCE INCOME TAX . . . . . . . . . . . . . . . 45
UNSUCCESSFUL IMPORTS.................................................................................47
SUCCESSFUL IMPORTS......................................................................................47
VERIFYING TRANSFERS IN ONESOURCE INCOME TAX. . . . . . . . . . . . . 47
C IMPORT LOG STATUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
TIPS FOR MAPPING DATA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
IDENTIFIERS.........................................................................................................49
JOURNAL ENTRIES..............................................................................................49
DATA FORMAT......................................................................................................49
EXPAND NUMERICAL COLUMNS........................................................................50
1
IMPORT LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
FILTER CRITERIA FOR IMPORT REPORTS . . . . . . . . . . . . . . . . . . . . . . . . 52
DOWNLOADING THE IMPORT LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
DELETING THE IMPORT LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
S
AUTO UPDATE FEATURE FOR EXISTING MAPS. . . . . . . . . . . . . . 55
ADDITIONAL ONESOURCE MENU OPTIONS . . . . . . . . . . . . . . . . . 57
SAVE AS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
VIEWING THE SOURCE PANE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
T CHECKING FOR UPDATES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
ABOUT ONESOURCE EXCEL ADD-IN / ACCESS REGISTRATION. . . . . . 59
UPDATING REGISTRATION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
TIPS AND TRICKS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
GROUP DATA FORMAT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
N MULTI-LEVEL GROUP DATA FORMAT. . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
MULTI-COMPANY/GROUP DATA FORMAT FOR TAX 
ACCOUNTING SYSTEM TARGETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
MULTI-COMPANY/GROUP DATA FORMAT FOR TAX RETURN 
TARGETS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
TAX ACCOUNTING SYSTEM (TAS) INTERCOMPANY TARGETS. . . . . . . 64
1065/1120 TAX RETURN TARGETS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
E
APPENDIX 1 - AVAILABLE IMPORT TARGETS. . . . . . . . . . . . . . . .66
APPENDIX 2 - DATA SPECIFICATIONS. . . . . . . . . . . . . . . . . . . . . .76
APPENDIX 3 - COUNTRY CODES. . . . . . . . . . . . . . . . . . . . . . . . . . .78
T APPENDIX 4 - OFFICER TITLE CODES . . . . . . . . . . . . . . . . . . . . . .94
APPENDIX 5 - LATE FILING REASON CODES . . . . . . . . . . . . . . . .95
APPENDIX 6 - TROUBLESHOOTING EXCEL ADD-IN 
INSTALLATION ISSUES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
OFFICE 2007: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
N
APPENDIX 7 - FAQS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
O
C
2
1

EXCEL ADD-IN FOR ONESOURCE INCOME TAX
EXCEL ADD-IN FOR ONESOURCE INCOME TAX
With the Excel Add-in, you can open an existing or new Excel worksheet, map the data, and then
export the mapped data to tax form fields in Organizer or data entry screens in the Tax Accounting
System.
The Excel Add-in enhances the efficiency of data transfer by:
• Decreasing data transfer time.
• Eliminating transcription errors that can occur when manually transferring data.
• Incorporating tools for copying data and mapping schema from one worksheet to another in
preparation for import.
• Enhancing the control and flexibility of data transfer and management.
• Identifying worksheet errors such as duplications, incorrect codes, and miscalculations,
before data is loaded into the Tax Accounting System or Organizer, preventing time-
consuming edits and corrections.
The Excel Add-in is currently available for 2014 forward for Organizer import. The Tax Accounting
System import for adjustments is available for 2003 forward.
2

GETTING STARTED
GETTING STARTED
ACCESS
Access to the Excel Add-in data import feature is located within ONESOURCE Income Tax.
Navigate to Tools | Download Excel Add-in.
The Excel Add-in page contains options for Downloads and Templates.
DOWNLOADING THE EXCEL ADD-IN
TO DOWNLOAD THE EXCEL ADD-IN:
1. On the Download Excel Add-in menu, select Office 2007, 2010, and 2013 32bit Add-in or
Office 2010 and 2013 64bit Add-in.
2. Click Download.
3

DOWNLOADING THE EXCEL ADD-IN
3. In the File Download dialog box, click Open or Save.
IF YOU CLICK OPEN:
a.The WinZip Wizard dialog box appears.
b. Follow the Wizard instructions to complete the WinZip download.
4

DOWNLOADING THE EXCEL ADD-IN
c. When you come to the Unzip/Install page, you can select to Unzip only or Unzip and
install.
If you select Unzip only, the files are downloaded and the folder which holds the files opens.
Click the setup.exe file to continue.
5

DOWNLOADING THE EXCEL ADD-IN
If you click Unzip and install, in the next dialog box click Install Now.
IF YOU CLICK SAVE:
a.In the Save As dialog box, browse to a save location and click Save.
b. Browse to the save location, unzip the file, and click the setup.exe file to continue.
5. Continue with the next part of the download which installs the Excel Add-in on your
workstation.
6

DOWNLOADING THE EXCEL ADD-IN
Note: If you installed a prior version of the Excel Add-in, you may be prompted to remove that
version before you can continue. If the Excel Add-in program does not allow you to remove the
previous Add-in during the download, open your Control Panel, click Add or Remove
Programs, and manually delete the old Excel Add-in for Microsoft Excel program.
6. In the Welcome to the Data Import Excel Add-in Setup Wizard dialog box, click Next.