What is Excel Data Import
The Excel data import allows a user to create import templates that map APS Tax fields to their own Excel spreadsheet tax templates. These templates can then be used to import the spreadsheet data directly into the APS Tax form.
This new feature will be enabled for all available return types in APS Tax.
Almost all the data on the main form and schedules can be imported (i.e. all the ATO label items).
There are exceptions where data can not be imported from repeating fields within a repeating group.
For example, in an Individual return, there can be multiple Salary and Wages payment summaries (this is a repeating group). The Lump sum E payments within this section is a repeating field within this repeating group. The lump sum E amount will not be imported, however all the other items within the repeating group will.
The 2021 tax year will be the first year this feature is catered for. All on going years will retain this new functionality.
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 tax templates in Excel.
Download Sample Excel Template
JOE Rights
A security feature has been included to allow which user(s) have access to the Excel Data Import. The rights for this can be located in JOE Admin.
Once JOE Admin is loaded, select the user or group, then click on View, JOE Rights, Tax Management, Advance Tax, Excel Import.
Turning off Allow Access removes the Excel Data Import button from the ribbon inside APS Tax.
Turning off Allow Template restricts the user from creating, editing, deleting, and saving templates. The user can only import what has already been set.
How it works
With access to the Excel import and templates, a new Excel button in the APS tax forms menu ribbon (residing under Imports) is now enabled for 2021 forms and onwards.
This button will display a Data Import screen that can store new templates in APS Tax. This template can then be used for multiple returns in the same tax year to import Excel data.
Source File
To begin the process, 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 Data Import screen.
The source file will display a directory path showing the location of where the template is stored.
Note:
For each return that has already set a source file, the file and template location is saved for that return so that each time the importer is opened, the source file location is shown and the spreadsheet is linked to the specific return.
When re-selecting the source file, the system will be directed back to the last known directory where the Excel file was selected.
In addition to this when exporting a return to pdf, the default directory for the file creation can now be set to the location of the Excel Data Import. This option can be set to a specific print template by selecting the option Use Excel import directory for Print Return file output or can be used for a single return by clicking Options from the Print Return window. This option will work if using the Export button from the Print window, using the Save As button from the print preview window or using a pdf driver when printing. This option is only available from Print Return and is not available if using Print Documents.
Importing Fields with Formulas and Decimals
Where the source file (Excel spreadsheet) contains VLOOKUP formulas and/or fields containing decimal places, the Excel Data Import will now 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 the totals in Excel.
Template
Once the source file is selected, click on Template. This will provide a dropdown list of all the available stored templates.
If there are no templates available, select <New Template>.
All the return type related forms, worksheets, and schedule sections will now be made available under the Field (destination) column.
Data Mapping
The Excel Data Import needs two fields to be mapped first to check the file being imported is for the correct client and for the correct year.
The TFN/ABN and also the Tax Year are the two fields that will be checked. This information can be mapped in the Return Identification section.
Both these fields are not mandatory, however, if these fields are ignored, a warning occurs but the import process can be continued.
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. Return Identification), 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, select the Collapse All button.
To map a cell, entering a cell reference as "D:4" or "d:4" 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 a user who has sufficient template access rights.
Mapping Grids
Mapping of repeating fields to the return can also be achieved via the Excel Data Import.
For example, to map data to the interest grid, open the Form > Gross Interest section.
For any fields that are to be repeated or mapped into a grid, it will show a grid () icon.
To map the repeating data, locate the first row from the Interest table in the spreadsheet and enter the cell references from that row to their respective "field destination" in the data import screen.
Once the first row is mapped, no further rows from the spreadsheet need to be linked to that section as the Data Import will locate the rest of the grid items.
Note: For repeating rows to end, have a blank row after the repeated rows in the Excel spreadsheet so the system can stop the repeating process.
Only View 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.
Save and Deleting Templates
There are three options for storing and removing templates from the Data Import screen:
Save is used to save the file to the database. This saves the template for that particular return type for that year. Opening any other return type will contain different saved templates.
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 its mapped fields.
Import Excel Data
Click on the Import button to transfer the data from the spreadsheet to the mapped fields in APS Tax.
Note: Where error messages occur, there will be more detailed information provided in the error message so that it is easier to find the items in error.
Once the data is imported successfully into the return, the Data Import window will close.
Review the return information to determine that all the mapped fields have been imported from the spreadsheet.