Import Ledger Master with BYB into Tally Software
UDIMagic allows you to import both Masters as well as Vouchers from MS-Excel into
Tally Software. Herein, we shall focus on Ledger Master having bill-by-bill details.
To begin with, let's have a look at the Excel file "Masters - LedgerMaster with
BYB details.xls" which contains the data as given below:-
|
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
|
1 |
ID |
NAME |
DEBIT |
CREDIT |
BYB-DATE |
BYB-NAME |
BYB-
DUEDATE |
BYB-
AMOUNT |
BYB-
DRCR |
|
2 |
10000001 |
Ankur Salt Pvt. Ltd |
|
260808 |
31-Mar-2007 |
Inv/06-07/51 |
23-Apr-2007 |
100000 |
CR |
|
3 |
10000001 |
|
|
|
31-Mar-2007 |
Inv/06-07/52 |
10-May-2007 |
160808 |
CR |
|
4 |
10000002 |
Velji & Co. |
47000 |
|
|
|
|
|
|
|
5 |
10000003 |
Vibhuti Shipping Pvt Ltd |
251640 |
|
31-Mar-2007 |
Inv/06-07/53 |
15-May-2007 |
62910 |
DR |
|
6 |
10000003 |
|
|
|
31-Mar-2007 |
Inv/06-07/54 |
15-May-2007 |
88074 |
DR |
|
7 |
10000003 |
|
|
|
31-Mar-2007 |
Inv/06-07/55 |
15-May-2007 |
70460 |
DR |
|
8 |
10000003 |
|
|
|
31-Mar-2007 |
Inv/06-07/56 |
15-May-2007 |
30196 |
DR |
Remarks:-
- The above table/sheet contains "LedgerMaster" data.
- Cells shown in yellow color contain Comments
DOWNLOADS
Download the Excel file
Masters - Ledger Master with BYB details.xls
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:-
- Though ID field/column is optional for Masters, it is used in this
case as the Ledger Master records span over multiple rows.
- If ID field/column is not used in this case, then udi-Magic would treat
each row a a separate Ledger Master record.
|
The XML tags specified in cell B1
(comments section) are given below:-
|
XML Tags
|
Remarks
|
<MASTER TYPE="LEDGER">
<NAME.LIST>
<NAME COLUMNREFERENCE="B"/>
</NAME.LIST>
<PARENT FORMULA="=IF(C#=0,"Sundry Creditors","Sundry
Debtors")"/>
<OPENINGBALANCE FORMULA="=IF(C#=0,+D#,C#*-1)"/>
<BILLALLOCATIONS.LIST SCROLL="YES"/>
<NAME COLUMNREFERENCE="F"/>
<BILLDATE COLUMNREFERENCE="E"/>
<BILLCREDITPERIOD COLUMNREFERENCE="G"/>
<OPENINGBALANCE FORMULA="=IF(+I#="CR",+H#,+H#*-1)"/>
</BILLALLOCATIONS.LIST>
</MASTER>
|
Create LEDGER masters
The <MASTER TYPE="LEDGER"> tag instructs udiMagic to create LedgerMasters
- NAME is to be taken from Column B
- PARENT is a Formula as given below:-
=IF(C#=0,"Sundry Creditors","Sundry Debtors")
Remarks:- Specifies that PARENT must be set to
"SUNDRY CREDITORS" if Column C contains 0; otherwise "SUNDRY DEBTORS"
- OPENINGBALANCE is a Formula as given below:-
=IF(C#=0,+D#,+C*-1)
Remarks:-
- If Column C is zero, then return Column D value
- Otherwise, Column C multiplied by -1
- BILLALLOCATIONS.LIST has attribute SCROLL=YES which means that it spans to multiple
rows.
- NAME (i.e. Byb-Name) is to be taken from Column F
- BILLDATE (i.e. Byb-date) is to be taken from Column E
- BILLCREDITPERIOD is to be taken from Column G
- OPENINGBALANCE (for byb) is a Formula as given below:-
=IF(I#="CR",+H#,+H*-1)
Remarks:-
- If Column I contains "CR", then return Column H value
- Otherwise, Column H multiplied by -1
|
In brief, udiMagic processes all rows in the Excel sheet and creates LedgerMasters
based on the Column B values.
|
