What is Bulk Excel Data Import
The Bulk Excel data import allows you to create import templates that map Access APS Tax fields to their own Excel spreadsheet tax templates. These templates can then be used to import the spreadsheet data directly into the Access APS Tax form in bulk.
This new function is featured in the Tax Services console and will be enabled for the following main return types in APS Tax:
Individual
Company
Trust
Partnership
Self managed superfund
Superfund
Most of the data on the main form and schedules can be imported (i.e. all the ATO label items), however the bulk import in its first iteration is meant for more basic means of bulk data import.
The 2021 tax year will be the first year this feature is catered for. The Excel Data Import will support the following Excel file formats:
Excel Workbook (.xlsx)
Excel Macro-Enabled Workbook (.xlsm)
Excel Binary Workbook (.xlsb)
Excel 97-2003 Workbook files (.xls)
Sample Excel Tax Template
Before being able to use this feature, you must have created your own bulk tax templates in Excel.
Download Sample Bulk Tax Template
Note: each row in the template equates to one return.
JOE Rights
To access this feature, Tax Services needs to be enabled in JOE Admin for the relevant users.
Once JOE Admin is loaded, select the user or group, then click on View, JOE Rights, Tax Management, Tax Services.
Turning off Allow Access removes the Tax Services icon from the Advance Applications main menu.
How it works
There are a couple of pre-requisites before a bulk Excel data import can be run.
All the returns that are going to be bulk updated via the Excel import will need to be created and be editable (e.g. the Started status).
Before opening Tax Services, Tax Manager needs to be opened. If this is not done, the following message will occur:
In order for returns to be bulk updated, the software requires Access APS Tax to be running.
When Tax Services is opened, it will default to the Data Import, Import selection screen.
Creating Templates
If you are using this for the first time, creating templates to link to the source Excel file will need to be completed before any returns can be imported.
Select Template from the Data Import navigator to bring up the options for template creation.
Click on Template to select a template. This will provide a dropdown list of all the available stored templates. If there are no templates available, select <New Template>.
Select the Tax Year. This will show the years 2021 and onwards.
Select the Return Type. Once this is selected, all the return type related forms, worksheets and schedule sections will now be made available under the Field (destination) column.
A Source File must be selected and loaded. Click on the ellipsis button to load a screen to select a source file.
Locate the Excel file and select open. A spreadsheet will preload to the template creation screen. The source file will display a directory path showing the location of where the template is stored.
Data Mapping
Following the sample bulk tax template, all the returns data to be imported will need to exist within the one Excel spreadsheet. In order for that to work, each row in the spreadsheet will represent one return.
As the Tax Year and Return Type has already been selected, the final unique identifying field for both the return and Practice Management is the Tax File Number (TFN). Map the first row of the TFN to the template creator as the Bulk Import will validate the source file being imported is for the correct TFN.
Note: An editable return must already exist in Tax Manager so the Bulk Import can identify if the TFN, Tax Year and Return Type is correct.
To map a section, select the Worksheet Name. This will be the different Tabs in the excel spreadsheet. If you select the worksheet name for the main heading (e.g. Personal Details), all the subfields will also populate with the same worksheet name. Each row can also have a worksheet name selected specifically for it if required.
To see all the fields that can be mapped, select the Expand All button. If you wish to not show these fields, untick the Expand All button.
To map a cell, entering a cell reference as "C:5" or "c:5" will work, there is no restriction on whether it is case sensitive or not. However, if there is a "defined name" set for the cell field, the defined name needs to be used instead of the cell reference.
Once a row is mapped, a green tick () will be shown for that row. That entire section will also be Bolded to identify that there is a mapped field.
Note: Editing of a template is automatically available for all users who have sufficient template access rights.
Where the source file (Excel spreadsheet) contains VLOOKUP formulas and/or fields containing decimal places, the Bulk Import will import formula and decimal fields from Excel into non-decimal fields in APS Tax.
Note: Following ATO rules, decimal fields will be truncated and not rounded. As such, you may need to review any totals in the return as they may be slightly different to totals in Excel.
Map Repeating Items
For the first iteration of Bulk Excel Data Import, mapping of repeating fields to the return is available but with some exceptions.
Only the innermost repeating section can be mapped. For instance, a return could have more than one partnership distribution. This is termed as a repeat.
Within each partnership distribution, there are grids which are also termed as repeats within a repeat. If you wanted to map repeating grids within the partnership distribution, only one partnership distribution can be imported and the innermost repeating section can only be mapped.
For example, to map repeating grids in the Partnership Distributions > Other non primary production - Other deductions against other income grid:
Open the Partnership distributions received worksheet > Other non-primary production section in the Field (destination) column.
Referring to the sample spreadsheet, notice that item 13Y has repeating data (interest paid and salary continuance insurance).
To map repeating columns, the cells need to be mapped with cell reference then comma then cell reference etc. in the same row (e.g. enter in cell column J:5,L:5 for description and K:5,M:5 for amount)
Once mapped, the imported records will be recorded to the Partnership distribution received for A & B Partners:
Only Show Mapped Fields
Once the worksheet name and cells have been entered and saved to the template, there is an option to only show those fields that have been mapped instead of seeing all the available fields.
Select the check box "Only show the mapped fields" to display all the mapped items.
Click on Expand All to expand every mapped row under each heading.
Save and Deleting Templates
There are three options of storing and removing templates from the Template screen:
Save is used to save the file to the database. This saves the template for that particular return type for that selected year.
Save As is used to save a copy of another template to the same form type. This can be used to save a prior years template into the next year. Where there is a field removed in a current year, that field is striked-out in the new template saved for the following year.
Delete will delete the existing template and all it's mapped fields.
Bulk Import Excel Data
Click on Import in the navigator to bring up the Import button to transfer the data from the spreadsheet to the mapped fields in APS Tax.
A Source File must be selected and loaded. Click on the ellipsis button to load a screen to select a source file. Once you have found the Excel file, select open. This will now preload that spreadsheet to the template creation screen. The source file will display a directory path showing the location of where the template is stored.
Select the Template. Once the template is loaded, the grid will display the year and return type to import (e.g. 2022 Individual Returns to import).
The Import screen will display the Return ID, Name and TFN of the matched data that exists in the spreadsheet. Those matched returns will automatically be selected for import.
If the return has not been created in Tax Manager, it will show as Doesn't Exist. If the TFN cannot be found in the database, it will state it as Not Found.
Click on the Import button to begin the process. A progress bar will inform the user which return is being imported from Excel.
Once the data is imported successfully into the return, a 'Successfully imported data from Excel' message will occur and in-turn display the Report screen.
Note: Review the return information to determine that all the mapped fields have been imported from the spreadsheet.
Report
Once the import has completed, the Report screen will allow the user to find the report for that particular batch.
After selecting the report from the report selection list, all the information from that bulk import session will display. Review the results column to see whether the returns imported successfully or failed to import.
Selecting the Print button will provide a more detailed report to show where error messages occurred so that it is easier to find the items in error.