Import Sales Vouchers data from Excel into Tally Software
This article illustrates how to import Sales Vouchers having Cost-Centres and Cost-Categories
from Excel into Tally.
To begin with, let's have a look at the Excel file "Vouchers - Sales Transactions
with CostCentres.xls" which contains the Sales Vouchers data as given
below:-
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
|
1 |
INV-NO |
DATE |
PARTY |
ITEM NAME |
QTY |
RATE |
AMOUNT |
INV-AMOUNT |
NARRATION |
Agent |
|
2 |
50000 |
1-Apr-2007 |
Jayant Metals Pvt. Ltd |
ITEM 1 |
15 |
2600 |
39000 |
49000 |
Black Large |
Mahesh |
|
3 |
50000 |
|
|
ITEM 3 |
5 |
2000 |
10000 |
|
|
Ramesh |
|
4 |
50001 |
1-Apr-2007 |
Crystal Metals Ltd |
ITEM 2 |
2 |
3500 |
7000 |
28000 |
Black small |
Shekar |
|
5 |
50001 |
|
|
ITEM 1 |
5 |
2600 |
13000 |
|
|
Abhijit |
|
6 |
50001 |
|
|
ITEM 3 |
4 |
2000 |
8000 |
|
|
Ramesh |
Remarks:-
- The above table/sheet contains "Sales" data.
- Cells shown in yellow color contain Comments
DOWNLOADS
Download the Excel file
Vouchers - Sales Transactions with CostCentres.xls
CAUTION
You need to enable the Cost Centre / Cost Category features in your Tally Company,
before using this Excel file with udiMagic. To enable Cost Centre / Cost Cetegory
1) In the Gateway of Tally, press F11 to select Features
2) Next, select Accounting Features (for Tally 9 users). For Tally 7.2 users, go
to point 3
3) Set the following features to Yes:-
Maintain Cost Centres
= Yes
More than ONE Cost Category = Yes
Explanation of XML tags used in the aforesaid Excel file
The XML tags specified in cell A1
(comments section) are given below:-
|
XML Tags
|
Remarks
|
<COLUMNNAME.LIST>
<COLUMNNAME>ID</COLUMNNAME>
</COLUMNNAME.LIST>
|
Specifies the key-field
Explanation:-
- COLUMNNAME.LIST XML tag is used to specify the Name for the Excel column.
Herein, we specify the Column-name as ID
- udiMagic uses the ID field/Column as the key-field
- ID field/column is mandatory for Vouchers
|
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Sales of FG<NAME>
</NAME.LIST>
<PARENT>Sales Account</PARENT>
<ISCOSTCENTRESON>Yes<ISCOSTCENTRESON>
<AFFECTSSTOCK>Yes<AFFECTSSTOCK>
</MASTER>
|
Create LEDGER master
Explanation:-
The attribute SINGLERECORD="Yes" specifies that this is to be processed
once only. Hence, udiMagic creates only one master-record.
- NAME.LIST specifies a static Ledger-Name "Sales of FG"
- PARENT is set to "Sales Account"
- AFFECTSSTOCK is set to Yes
In brief, these XML tags instructs udiMagic to create a single Ledger-master record
viz "Sales of FG" under Group "Sales Account" |
<MASTER TYPE="UNIT" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Nos</NAME>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</NAME.LIST>
|
Create UNIT master
These XML tags instruct udiMagic to create a single UNIT-master record viz "Nos" |
<MASTER TYPE="COSTCATEGORY" SINGLERECORD="Yes">
<NAME.LIST>
<NAME>Local Sales Agents</NAME>
</NAME.LIST>
<AFFECTSSTOCK>No</AFFECTSSTOCK>
<ALLOCATEREVENUE>Yes</ALLOCATEREVENUE>
<ALLOCATENONREVENUE>Yes</ALLOCATENONREVENUE>
</MASTER>
|
Create COSTCATEGORY master
These XML tags instruct udi-Magic to create a single COSTCATEGORY-master record
viz "Local Sales Agents" |
<MASTER TYPE="COSTCATEGORY" SINGLERECORD="Yes">
<NAME.LIST>
<NAME>Export Sales Agents</NAME>
</NAME.LIST>
<AFFECTSSTOCK>No</AFFECTSSTOCK>
<ALLOCATEREVENUE>Yes</ALLOCATEREVENUE>
<ALLOCATENONREVENUE>Yes</ALLOCATENONREVENUE>
</MASTER>
|
Create COSTCATEGORY master
These XML tags instruct udi-Magic to create a single COSTCATEGORY-master record
viz "Export Sales Agents" |
<VOUCHER>
<GUID FORMULA="=+"32a3d2ge-6472-4hk2-27bb-ed" & IF(AND(MONTH(B#)>=4,MONTH(B#)<=12),YEAR(B#)
& "nn" & YEAR(B#)+1,YEAR(B#)-1 & "-" &YEAR(B#)) & "-" &
IF(LEN(A#)>=8,"",REPT("0",8-LEN(A#))) & RIGHT(A#,8)"/>
<DATE COLUMNREFERENCE="B"/>
<EFFECTIVEDATE COLUMNREFERENCE="B"/>
<VOUCHERTYPENAME>Sales</VOUCHERTYPENAME>
<REFERENCE COLUMNREFERENCE="A"/>
<NARRATION COLUMNREFERENCE="I"/>
<ISINVOICE>Yes</ISINVOICE>
<LEDGERENTRIES.LIST>
<LEDGERNAME COLUMNREFERENCE="C"/>
<ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>
<AMOUNT FORMULA="=+H#*-1"/>
</LEDGERENTRIES.LIST>
<ALLINVENTORYENTRIES.LIST SCROLL='YES'>
<STOCKITEMNAME COLUMNREFERENCE="D"/>
<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
<RATE COLUMNREFERENCE="F"/>
<AMOUNT COLUMNREFERENCE="G"/>
<ACTUALQTY COLUMNREFERENCE="E"/>
<BILLEDQTY COLUMNREFERENCE="E"/>
<ACCOUNTINGALLOCATIONS.LIST>
<LEDGERNAME>Sales of FG</LEDGERNAME>
<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
<AMOUNT FORMULA="=+G#*1"/>
<CATEGORYALLOCATIONS.LIST>
<CATEGORY>Local Sales Agents</CATEGORY>
<COSTCENTREALLOCATIONS.LIST>
<NAME COLUMNREFERENCE="J"/>
<ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
<AMOUNT FORMULA="=+G#*1"/>
</COSTCENTREALLOCATIONS.LIST>
</CATEGORYALLOCATIONS.LIST>
</ACCOUNTINGALLOCATIONS.LIST>
<BATCHALLOCATIONS.LIST>
<GODOWNNAME>Main Location</GODOWNNAME>
<BATCHNAME>Primary Batch<BATCHNAME>
<DESTINATIONGODOWNNAME>Main Location<DESTINATIONGODOWNNAME>
<AMOUNT FORMULA="=+G#*1"/>
<ACTUALQTY COLUMNREFERENCE="E"/>
<BILLEDQTY COLUMNREFERENCE="E"/>
</BATCHALLOCATIONS.LIST>
</ALLINVENTORYENTRIES.LIST>
</VOUCHER>
|
Specifies data for the Voucher records
- GUID is a mandatory field. GUID is a unique value which is used by Tally to identify
each Voucher record uniquely. As this formula is bit difficult, we suggest that
you skip it for the time-being. Only note that the GUID is based in Column A which
is specified by A#
- DATE is to be taken from Column B
- EFFECTIVEDATE is generally same as DATE and is set to Column B
- VOUCHERTYPENAME is set to static value "Sales"
- REFERENCE is to be taken from Column A
- NARRATION is to be taken from Column I
- ISINVOICE is set to static value "Yes"
LEDGERENTRIES.LIST contains tags for the Party Ledger
- LEGERNAME is to be taken from Column C
- As the Party Ledger is to be Credited in Sales voucher, we set the ISDEEMEDPOSITIVE
tag to Yes
- AMOUNT is a formula. Herein, value of Column H is multiplied by -1
ALLINVENTORYENTRIES.LIST contains tags for the StockItems that appear in the
Voucher
- STOCKITEMNAME is to be taken from Column D
- ISDEEMEDPOSITIVE must be set to "No" for StockItems in Sales Vouchers
- RATE is to be taken from Column F
- AMOUNT is to be taken from Column G
- ACTUALQTY/BILLEDQTY are to be take from Column E
ACCOUNTINGALLOCATIONS.LIST contains tags for Ledger allocations with respect
to each StockItem
- LEDGERNAME is set to static value "Sales of FG"
- ISDEEMEDPOSITIVE is set to "No"
- AMOUNT is a formula and is calculated as
Column G * 1
- CATEGORYALLOCATIONS.LIST specifies the Cost Category and Cost Centre
allocation for the specified Ledger
- CostCentre NAME is to be taken from Column J
- CostCentre AMOUNT is a formula.
BATCHALLOCATIONS.LIST contains tags for StockItem-wise batch allocations.
Herein, default values are used for the GODOWNNAME and BATCHNAME. |
The XML tags specified in cell C1
(comments section) are given below:-
|
XML Tags
|
Remarks
|
<MASTER TYPE="LEDGER">
<NAME.LIST>
<NAME COLUMNREFERENCE="C"/>
</NAME.LIST>
<PARENT>SUNDRY DEBTORS</PARENT>
<ISCOSTCENTRESON>No</ISCOSTCENTRESON>
</MASTER>
|
Create LEDGER masters
The <MASTER TYPE="LEDGER"> tag instructs udiMagic to create LedgerMasters
- NAME is to be taken from Column C
- PARENT is set to static value "SUNDRY DEBTORS"
- ISCOSTCENTRESON is set to "No"
In brief, udiMagic processes all rows in the Excel sheet and creates LedgerMasters
based on the Column C values.
|
The XML tags specified in cell D1
(comments section) are given below:-
|
XML Tags
|
Remarks
|
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<NAME COLUMNREFERENCE="D"/>
</NAME.LIST>
<PARENT/>
<BASEUNITS>Nos</BASEUNITS>
</MASTER>
|
Create STOCKITEM masters
The <MASTER TYPE="STOCKITEM"> tag instructs udiMagic to create StockItemMasters
- NAME is to be taken from Column D
- PARENT is set to Null. Hence, Tally will create the StockItem under Group PRIMARY
- BASEUNITS is set to "Nos"
In brief, udiMagic processes all rows in the Excel sheet and creates STOCKITEM Masters
based on the Column D values |
The XML tags specified in cell J1
(comments section) are given below:-
|
XML Tags
|
Remarks
|
<MASTER TYPE="COSTCENTRE">
<NAME.LIST>
<NAME COLUMNREFERENCE="J"/>
</NAME.LIST>
<CATEGORY>Local Sales Agents</CATEGORY>
</MASTER>
|
Create COSTCENTRE masters
The <MASTER TYPE="COSTCENTRE"> tag instructs udiMagic to create CostCentre
Masters
- NAME is to be taken from Column J
- CATEGORY is set to static value "Local Sales Agents"
In brief, udiMagic processes all rows in the Excel sheet and creates COSTCENTRE
Masters based on the Column J values |
Remarks:-
udiMagic first processes the MASTER XML tags; creates the MASTER records and then
processes VOUCHERs.
|
