Import Payroll data into Tally Software
Looking for a software utility to import Payroll / Attendance data from MS-Excel
into Tally Software ? Try out udiMagic.
Contents
UDIMagic
UDIMagic [Standard] is a software utility that allows you to Import data from MS-Excel
into Tally Accounting Software. It works with Tally 6.3 and all higher versions
including the latest version Tally 9.
UDIMagic supports :-
- All Tally Masters and Vouchers types (including user-defined voucher-types and
UDF's created using TDL)
- All Tally features like Payroll; Cost Centres;
Cost Categories; Multiple Currencies; Multiple Godowns etc.
Electronic Attendance Systems
Today, a large number of SME's (small and medium enterprises) use
Electronic Attendance Systems to maintain their attendance records.
These Electronic Attendance Systems are useful for maintaining Attendance records;
computation of salary/wages; generating payslips; salary statements etc.
However, as majority of the SME's use Tally Accounting Software for Accounting purpose;
the Payroll data is manually re-entered into the Tally Software. It is here, that
UDIMagic utility can relieve you from the task of manually re-entering the Payroll
data into Tally.
Payroll in Tally
The Payroll module is available in Tally 9 (and higher versions).
The Payroll module is integrated with the Accounts and hence, the accounting aspect
is taken care of automatically.
UDIMagic and Payroll in Tally
UDIMagic allows you to Import Payroll data into Tally. It is particularly useful
in following situations where :-
- Electronic Attendance System
is used
Most of the Electronic Attendance Systems allow export of data in Excel format.
This can then be imported into Tally using UDIMagic utility.
- Attendance records are maintained
directly in MS-Excel
Many organisations maintain the Present ; Absent; Leave; OT and other details directly
in MS-Excel. Herein, the Excel data can be imported into Tally using UDIMagic.
Enabling the Payroll feature in Tally
Go to "Gateway of Tally >> F11: Features >> Accounting Features" and
then set the following options:-
- Set "Maintain Payroll" to Yes
- Set "More than ONE Payroll / Cost Category" to Yes
Understanding Master and Voucher types
in Tally Payroll
Before we proceed with the actual task of importing Payroll data into tally, let's
have a look at the various Masters and Voucher-types available in Tally.
Payroll Masters include :-
- Employee Master (ex: Ramesh Mehta; Paresh)
- Employee Group Master (ex: Sales Dept; Administration Dept)
- Pay Head Master (ex: Basic Salary; HRA etc)
- Attendance/Production types (ex: Present; Absent; Paid Leave; Unpaid Leave etc)
Payroll Voucher types include :-
- Attendance Voucher (for recording Present; Absent details)
- Payroll
Voucher (for computing Salary)
- Payment Voucher (for Payment)
Herein, we shall focus on the Attendance Voucher which is used
to record Present; Absent details of Employees.
Sample Excel Sheet with Attendance data
Here's a sample Excel sheet format which contains Attendance data. For simplicity,
we have taken minimum fields / columns required for posting the Attendance data
into tally.
|
A |
B |
C |
D |
E |
F |
G |
H |
|
Period |
Employee Name |
Department |
Designation |
Present |
Absent |
Paid Leave |
Unpaid Leave |
1 |
Apr08-09 |
Ramesh Mehta |
Sales |
Sales Executive |
26 |
|
4 |
|
2 |
Apr08-09 |
Pradeep Swamy |
Sales |
Sales Executive |
26 |
|
4 |
|
3 |
Apr08-09 |
Suresh Choudhary |
Administration |
General Manager |
26 |
|
4 |
|
4 |
Apr08-09 |
Kamlesh Jhani |
Accounts |
Accounts Officer |
23 |
3 |
4 |
|
In this sample Excel sheet, we have used various Attendance-types
- Present
- Absent
- Paid Leave
- Unpaid Leave
Let's now understand these Attendance-types.
Master Name
(Attendance /Production type Master) |
Under |
Type |
Whether Salary is Payable to Employee
(Yes or No) |
Remarks |
PRESENT |
Primary |
Attendance / Leave with Pay |
Yes |
Salary is to be paid for PRESENT days |
ABSENT |
Primary |
Leave without Pay |
No |
Salary is NOT to be paid for ABSENT days |
PAID LEAVE |
PRESENT |
Attendance / Leave with Pay |
Yes |
Salary is to be paid for PAID LEAVE. ex: Sundays; other paid holidays |
UNPAID LEAVE |
ABSENT |
Leave without Pay |
No |
Salary is NOT to be paid |
Computation of Salary / Wages in Tally
Tally Software computes Salary/wages based on:-
- Attendance / Leave with Pay OR;
- Leave without Pay
Hence, you can specify either of these two :-
- Present - Number of Present days (including paid holidays) for which salary is to
be paid
- Absent - Number of Absent days
Remarks:-
Though in the sample Excel sheet, we have shown both the PRESENT and ABSENT [Attendance-types],
internally only one of these is used in the XML tags for salary computation.
Steps to Import Attendance data into Tally
It is presummed that you have already downloaded and installed the UDIMagic Demo
version from the Downloads section. Next, follow these steps:-
- Download the
Attendance-sheet (zip file).
- Unzip and copy the files into C:\UDI-MAGIC
folder.
- Start Tally and create/open a Dummy Company
- Go to "Gateway of Tally >> F11 >> Accounting features" and set
the following options:-
- Set "Maintain Payroll" to Yes
- Set "More than ONE Payroll / Cost Category" to Yes
- Minimize Tally Software
- Run UDIMagic software
- Next, select option "Excel to Tally"
- Next, select "Vouchers"
- Next, select the Excel sheet and the XML tags file.
Please note that the XML tags file is to be speicifed in the same screen in which
you are prompted to select the Excel Sheet.
- Follow instructions in the wizard to import data into Tally
UDI-Magic Demo version
Attendance-sheet
(Excel sheet Template)
Links
udiMagic Home Page
udiMagic Articles and sample
Excel templates
Learn Tally XML
tags for importing data into Tally
Tags:-
udiMagic, Tally, Payroll, Excel to Tally
|