Import/Export Employee Opening Balances (Initial Values)

Exporting opening balances

To export the employee's opening balances, go to Payroll settings and select the Opening Balances option. Next, click the Export button, located on the top right-hand side of the page. From the data type drop-down, you can choose from three options:

  1. Template with employee data: This template will list only active/current employees in the payroll platform;
  2. Template with employee data (including terminated employees): This template will list all active and terminated employees in the payroll platform
  3. Empty template: This template does not list any employees; only the relevant opening balance fields.

Once you have selected the appropriate Data type and file format, click the Download button.

What are each of the columns in the Opening Balances template?

This list explains what each column represents. You​ will find further clarification of the information provided in your employee records. The following fields are constant regardless of your payroll setup:

  • ExternalID: This relates to an employee ID you may be using that comes from another external platform. This is captured from the "External Id" field in the employee's Details screen.
  • Income Tax Number.
  • Employee name: This is captured from the employee's 'Preferred name', 'Middle name' and 'Surname' fields in the employee's Details screen.
  • Total hours: Only required if you want to add Total Hours worked in the initial basis period, up to the day before the period start date of the first pay run in this payroll platform. (Refer to the Earnings tab of the opening balances page of the employee's record).
  • Gross earnings: The total of gross earnings paid in the initial basis period, up to the day before the period start date of the first pay run in this payroll platform. (This is displayed at the bottom of the 'Earnings' tab on the opening balances page of the employee's record). You can just enter a total gross earnings figure in this column if you wish, but if you choose to enter amounts for specific pay categories (see other columns in the spreadsheet labelled PCxxx, explained below), do not enter a total here. The platform will calculate the total of all of the Pay Category columns in the spreadsheet once imported and so will automatically calculate the total for this column. Earnings are added with an effective date of 1st January.
  • PCB.
  • CP38 Mandatory Employee EPF.
  • Mandatory Employer EPF.
  • Additional Employee EPF.
  • Employee EIS.
  • Employer EIS.
  • Employee SOCSO.
  • Employer SOCSO.
  • HRDF.
  • TP1 Zakat.
  • TP1 Relief.
  • LC321919_Annual Leave.
  • PC1231282_Bonus.
  • PC1178152_Full Time Staff.
  • PC1216692_Test.

The following column numbers will vary for each business and depends on the business setup, but the category orders are the same for everyone. They are as follows:

  • Leave Categories: The next group of columns represents all the leave categories set up in your Leave Categories' payroll settings. In the template they are labelled as LC, then a number which is the platform generated ID code for the leave category, then the name of the leave category, eg. "LC262968_Annual Leave". For these fields you will enter the employee's leave balance in the relevant unit type that the leave category has been configured to, for example, if the leave category has been set up with the unit type =  'days' then the balance entered must be in days. If the employee does not have a leave balance or the leave category is not set to accrue then leave the figure as "0". Negative leave balances need to be entered as "-12.657" for example.
  • Deduction Categories: The next group of columns represents each of the deduction categories set up in your Deduction Categories' payroll settings. In the template they are labelled as DC, then a number which is the platform generated ID code for the deduction category; then the name of the deduction category, e.g. "DC141225_Community Chest".
  • Employer Liability Categories: The next group of columns represents each of the employer liability categories set up in your Employer Liability Categories payroll settings. In the template they are labelled as EL, then a number which is the platform generated ID code for the employer liability category; then the name of the employer liability category, e.g. "ELC628_WorkCover". In these fields you will enter the employee's liability as a dollar amount. If there are no EL columns in your template, this means that no Employer Liability categories are set up in your business.
  • Pay Categories: The next group of columns represents each of the pay categories set up in your Pay Categories' payroll settings. In the template they are labelled as PC, then a number which is the platform generated ID code for the pay category; then the name of the pay category, e.g. "PC959199_Ordinary Hours". In these fields you will enter the employee's pay category breakdown as a dollar amount. You only need to enter amounts in these columns if you want the employee to see a breakdown of their YTD pay by pay category or if you want to report total wages broken down by pay category. Otherwise, entering an employee's total gross wage in "GrossEarnings" is enough.

Detailed information on each of these fields can be found in this article.

Importing opening balances

To import the employee's opening balances, go to Payroll settings Opening balances page. Next, click the Import button to the right of the page heading. Select the file that you wish to upload and then click the Upload button. You will then be asked to Confirm the Upload. Once the file has been imported, a status summary will display on your screen

Matching criteria

When importing opening balances, employee records are matched according to the following criteria (in order of priority):

  • If there is an NRIC number specified, then we​ will use the NRIC number to match the employee
  • If there is an external ID specified, then we​ will use the external ID to match the employee
  • Otherwise, we will use the combination of NRIC number and name to match the employee.

Deleting data

If you want to use this import file to remove data from the opening balances, in bulk, you will need to use the value "(clear)" without the quotes in the appropriate field in your import file to remove it from the matching field on the opening balance page of the employee record. 

If you have any questions or feedback, please let us know via support@yourpayroll.io

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Article is closed for comments.