Download the Excel sheet and XML tags for Cash Receipt Vouchers
Download the Instructions for writing XML tags for Importing Excel data (Cash Receipts) into Tally Software using UDI-Magic Software (PDF-file).
Tuesday, January 6, 2009
Exercise 17 : Cash Receipt Vouchers
Here's a Excel sheet that contains "Cash Receipt Vouchers"
Download the Excel sheet and XML tags for Cash Receipt Vouchers
Download the Instructions for writing XML tags for Importing Excel data (Cash Receipts) into Tally Software using UDI-Magic Software (PDF-file).
Download the Excel sheet and XML tags for Cash Receipt Vouchers
Download the Instructions for writing XML tags for Importing Excel data (Cash Receipts) into Tally Software using UDI-Magic Software (PDF-file).
Wednesday, July 2, 2008
Exercise 16: Sales Vouchers without Items
Here's a sample Excel sheet that contains Sales Vouchers (without Items). As this is the first exercise (for Vouchers), we have purposely taken up a simple Excel sheet.
Here's what the sample Excel sheet looks like.
Column A - Invoice No
Column B - Invoice Date
Column C - Party Name
Column D - Sales Ledger
Column E - Narration
Column F - Invoice Amount
Download the Excel sheet with the XML tags.
View the XML tags for Exercise 16
XML tags Explanation
1) First, you must write XML tags to create Masters (i.e. Party Ledgers and Sales Ledgers) . As you can see in the Excel sheet snapshot, the Column C contains Party Names whereas the Column D contains the Sales Ledgers. The XML tags to create Ledger Masters look like this :-
<MASTER TYPE="LEDGER">
...... tags......
</MASTER>
Remarks:-
a. Please refer the XML tags file for complete tags. You can also view it online by clicking on the link given above.
b. UDIMagic first processes the MASTER tags and then the VOUCHER tags.
2. You can get the Tally supported tags for Vouchers from Tally Software itself. All you need to do is to manually enter the sample data (only 1 voucher) as given in the Excel sheet into the Tally Software. Next, export it in XML format using option "Gateway of Tally >> Display >> Daybook >> Alt+E". This will generate the tags in Daybook.xml file. You can open/view these tags in IE. In the Daybook.xml file, refer to the tags that appear within the <TALLYMESSAGE> tag and try relating the same with the tags as given in the XML tags for this exercise.
Here's what the sample Excel sheet looks like.
Column A - Invoice No
Column B - Invoice Date
Column C - Party Name
Column D - Sales Ledger
Column E - Narration
Column F - Invoice Amount
Download the Excel sheet with the XML tags.
View the XML tags for Exercise 16
XML tags Explanation
1) First, you must write XML tags to create Masters (i.e. Party Ledgers and Sales Ledgers) . As you can see in the Excel sheet snapshot, the Column C contains Party Names whereas the Column D contains the Sales Ledgers. The XML tags to create Ledger Masters look like this :-
<MASTER TYPE="LEDGER">
...... tags......
</MASTER>
Remarks:-
a. Please refer the XML tags file for complete tags. You can also view it online by clicking on the link given above.
b. UDIMagic first processes the MASTER tags and then the VOUCHER tags.
2. You can get the Tally supported tags for Vouchers from Tally Software itself. All you need to do is to manually enter the sample data (only 1 voucher) as given in the Excel sheet into the Tally Software. Next, export it in XML format using option "Gateway of Tally >> Display >> Daybook >> Alt+E". This will generate the tags in Daybook.xml file. You can open/view these tags in IE. In the Daybook.xml file, refer to the tags that appear within the <TALLYMESSAGE> tag and try relating the same with the tags as given in the XML tags for this exercise.
Friday, June 20, 2008
Exercise 15: Welcome to the world of VBA / Macros
VBA or Visual Basic for Applications is a light weight programming language (quite similar to Visual Basic) that allows automation of tasks in MS-Office Applications.
UDI Magic v3.0 or higher supports the use of VBA. Using VBA, you can sort data; perform calculations; insert or delete columns or rows in MS-Excel at run-time. In simple words, VBA allows you to do almost any task that you can manually do in MS-Office Applications.
In this exercise, we shall take up "How to use VBA (Visual Basic for Applications) with UDI Magic ?"
STEP 1: Creating a macro file
To begin with, we shall write a simple macro that displays a message "Hello World". You can use Notepad (or any text-editor) to create macro files. Here's a simple macro file (Exercise-15-macro.txt) which displays a message.
Sub HelloWorld
MsgBox "Hello World !!!"
End Sub
Remarks:-
1) Sub...End Sub is used to declare a procedure / routine.
2) Google for "Excel VBA" for more help on writing VBA macros.
Before we write XML tags for importing data from our Excel sheet into Tally, let's have a look at the UDI Magic supported XML tags for loading and executing macros.
XML tags for Loading / executing the Macro file
Syntax:-
<VBA LOADFILE="macro-filename-with-path" RUNMACROATSTARTUP="macro-name"/>
Example 1:-
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
Example 2:-
<VBA LOADFILE="c:\exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
Explanation:-
1) In the first example, we have not specified the full-path for the macro-file. Hence, UDIMagic searches for the macro-file in the UDI Magic startup folder. In the second exmple, we have specified the full-path for the macro-file.
2) The tag <VBA> is UDI Magic specific.
3) The attribute LOADFILE="macro-filename" is used to specify the macro-filename.
4) The attribute RUNMACROATSTARTUP is used to specify the macro name which is to be executed at startup.
5) The Macro-file is executed before the Excel sheet data is processed. As a result,the macro used in this exercise displays a message "Hello World".
STEP 2: Create an Excel Sheet and write XML tags for it
To make the task simple, we shall take up an Excel sheet with Stock Item Masters records (having only one field i.e. the NAME field).
Download the Excel sheet with the XML tags. For your reference, the XML tags given below:-
<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
<!-- This loads and processes the macro-file -->
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
<!-- Create StockItem Masters -->
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
</MASTER>
</XMLTAGS>
UDI Magic v3.0 or higher supports the use of VBA. Using VBA, you can sort data; perform calculations; insert or delete columns or rows in MS-Excel at run-time. In simple words, VBA allows you to do almost any task that you can manually do in MS-Office Applications.
In this exercise, we shall take up "How to use VBA (Visual Basic for Applications) with UDI Magic ?"
STEP 1: Creating a macro file
To begin with, we shall write a simple macro that displays a message "Hello World". You can use Notepad (or any text-editor) to create macro files. Here's a simple macro file (Exercise-15-macro.txt) which displays a message.
Sub HelloWorld
MsgBox "Hello World !!!"
End Sub
Remarks:-
1) Sub...End Sub is used to declare a procedure / routine.
2) Google for "Excel VBA" for more help on writing VBA macros.
Before we write XML tags for importing data from our Excel sheet into Tally, let's have a look at the UDI Magic supported XML tags for loading and executing macros.
XML tags for Loading / executing the Macro file
Syntax:-
<VBA LOADFILE="macro-filename-with-path" RUNMACROATSTARTUP="macro-name"/>
Example 1:-
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
Example 2:-
<VBA LOADFILE="c:\exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
Explanation:-
1) In the first example, we have not specified the full-path for the macro-file. Hence, UDIMagic searches for the macro-file in the UDI Magic startup folder. In the second exmple, we have specified the full-path for the macro-file.
2) The tag <VBA> is UDI Magic specific.
3) The attribute LOADFILE="macro-filename" is used to specify the macro-filename.
4) The attribute RUNMACROATSTARTUP is used to specify the macro name which is to be executed at startup.
5) The Macro-file is executed before the Excel sheet data is processed. As a result,the macro used in this exercise displays a message "Hello World".
STEP 2: Create an Excel Sheet and write XML tags for it
To make the task simple, we shall take up an Excel sheet with Stock Item Masters records (having only one field i.e. the NAME field).
Download the Excel sheet with the XML tags. For your reference, the XML tags given below:-
<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
<!-- This loads and processes the macro-file -->
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
<!-- Create StockItem Masters -->
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
</MASTER>
</XMLTAGS>
Tuesday, June 17, 2008
Exercise 14: Import data into Multiple Companies simultaneously
Using UDIMagic v3.0 Release 1.48 or higher, you can import data into Multiple Companies simultaneously.
Here's an Excel sheet that contains Stock Item master records. The XML tags for this Excel sheet have been written such that the data is imported into two companies simultaneously.
Download the Excel sheet with the XML tags.
Follow these steps to import data into Tally
1) Create a company named "Company A" in Tally.
2) Create another company named "Company B" in Tally.
3) Next, Minimize Tally software.
Make sure that both the companies are open in Tally.
4) Run UDI Magic software and select option "Excel to Tally".
5) Next, select option "Masters".
6) Next, select the Excel sheet and the XML tags file.
You must select the XML tags file in the same screen in which you are prompted to select the Excel file.
7) Follow the wizard instructions to import data into Tally.
Understanding the XML tags
The XML tags used in this exercise are quite simple. We have already written such tags (for Stock Item Master) in our previous exercises. Now, let's see how do we import data into multiple companies...
STEP 1
Write XML tags to create STOCKGROUP masters
<!--This will create StockGroup Masters in Company A -->
<MASTER TYPE="STOCKGROUP" COMPANYNAME="Company A"/>
<NAME.LIST>
<!--Fetch StockGroup Name from Column B of the Excel Sheet-->
<NAME COLUMNREFERENCE="B"/>
<NAME.LIST>
</MASTER>
<!--This will create StockGroup Masters in Company B -->
<MASTER TYPE="STOCKGROUP" COMPANYNAME="Company B"/>
<NAME.LIST>
<!--Fetch StockGroup Name from Column B of the Excel Sheet-->
<NAME COLUMNREFERENCE="B"/>
<NAME.LIST>
</MASTER>
Remarks:-
1) Note that COMPANYNAME attribute is used to specify in which Company the data is to be imported.
2) We used the same tags twice, only the Company Name has been changed.
STEP 2
Write XML tags to create STOCKITEM masters
<!-- Create STOCKITEM Masters in Company A-->
< MASTER TYPE="STOCKITEM" COMPANYNAME="Company A">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column B-->
<PARENT COLUMNREFERENCE="B"/>
</MASTER>
<!-- Create STOCKITEM Masters in Company B-->
< MASTER TYPE="STOCKITEM" COMPANYNAME="Company B">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column B-->
<PARENT COLUMNREFERENCE="B"/>
</MASTER>
IMPORTANT:-
1) To import data into multiple Companies, you must use UDIMagic v3.0 Release 1.48 or higher. The Demo version can be downloaded from the Downloads web-page at our site http://www.rtslink.com/downloads.html
2) Those using licensed UDIMagic are requested to write to sales @ rtslink.com (remove spaces) for more information.
Here's an Excel sheet that contains Stock Item master records. The XML tags for this Excel sheet have been written such that the data is imported into two companies simultaneously.
Download the Excel sheet with the XML tags.
Follow these steps to import data into Tally
1) Create a company named "Company A" in Tally.
2) Create another company named "Company B" in Tally.
3) Next, Minimize Tally software.
Make sure that both the companies are open in Tally.
4) Run UDI Magic software and select option "Excel to Tally".
5) Next, select option "Masters".
6) Next, select the Excel sheet and the XML tags file.
You must select the XML tags file in the same screen in which you are prompted to select the Excel file.
7) Follow the wizard instructions to import data into Tally.
Understanding the XML tags
The XML tags used in this exercise are quite simple. We have already written such tags (for Stock Item Master) in our previous exercises. Now, let's see how do we import data into multiple companies...
STEP 1
Write XML tags to create STOCKGROUP masters
<!--This will create StockGroup Masters in Company A -->
<MASTER TYPE="STOCKGROUP" COMPANYNAME="Company A"/>
<NAME.LIST>
<!--Fetch StockGroup Name from Column B of the Excel Sheet-->
<NAME COLUMNREFERENCE="B"/>
<NAME.LIST>
</MASTER>
<!--This will create StockGroup Masters in Company B -->
<MASTER TYPE="STOCKGROUP" COMPANYNAME="Company B"/>
<NAME.LIST>
<!--Fetch StockGroup Name from Column B of the Excel Sheet-->
<NAME COLUMNREFERENCE="B"/>
<NAME.LIST>
</MASTER>
Remarks:-
1) Note that COMPANYNAME attribute is used to specify in which Company the data is to be imported.
2) We used the same tags twice, only the Company Name has been changed.
STEP 2
Write XML tags to create STOCKITEM masters
<!-- Create STOCKITEM Masters in Company A-->
< MASTER TYPE="STOCKITEM" COMPANYNAME="Company A">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column B-->
<PARENT COLUMNREFERENCE="B"/>
</MASTER>
<!-- Create STOCKITEM Masters in Company B-->
< MASTER TYPE="STOCKITEM" COMPANYNAME="Company B">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column B-->
<PARENT COLUMNREFERENCE="B"/>
</MASTER>
IMPORTANT:-
1) To import data into multiple Companies, you must use UDIMagic v3.0 Release 1.48 or higher. The Demo version can be downloaded from the Downloads web-page at our site http://www.rtslink.com/downloads.html
2) Those using licensed UDIMagic are requested to write to sales @ rtslink.com (remove spaces) for more information.
Monday, June 16, 2008
Exercise 13: Using the COMPANYNAME attribute
When you import data into Tally Software using UDIMagic, it gets imported into the Current Company, by default.
To import data into a specific Company in Tally, you need to use the COMPANYNAME attribute. Generally, this is required when two or more companies are open in Tally and you wish to import data into a specific company.
Herein, we shall take a simple Excel sheet that contains Stock Item Masters. For simplicity, we have taken only the Stock Item NAME in the Excel Sheet.
Download the Excel sheet with the XML tags.
Follow these steps to import data into Tally
1) Create two Companies in Tally. viz "Company A" and "Company B"
2) Select the "Company A".
3) Minimize Tally software.
4) Run UDIMagic and follow instructions in the wizard to Import data from the sample Excel-sheet.
You must select the XML tags file in the same screen in which you are prompted to select the Excel file.
If everything goes fine, then 5 Stock Item master records will be imported into "Company B" in Tally.You can view the same using "Gateway of Tally >> Inventory Info >> Stock Item >> Display" option.
Attribute : COMPANYNAME
Applies to: MASTER and VOUCHER tags
Example 1:-
<MASTER TYPE="LEDGER" COMPANYNAME="MyCompany">
.....XML tags.....
</MASTER>
Example 2:-
<VOUCHER COMPANYNAME="MyCompany">
.....XML tags.....
</VOUCHER>
Remarks:-
1) The COMPANYNAME attribute can be used with the MASTER and VOUCHER tags only.
2) If there two or more MASTER tags (or VOUCHER tags) in a single XML file, then you need to use the COMPANYNAME attribute with each MASTER tag.
To import data into a specific Company in Tally, you need to use the COMPANYNAME attribute. Generally, this is required when two or more companies are open in Tally and you wish to import data into a specific company.
Herein, we shall take a simple Excel sheet that contains Stock Item Masters. For simplicity, we have taken only the Stock Item NAME in the Excel Sheet.
Download the Excel sheet with the XML tags.
Follow these steps to import data into Tally
1) Create two Companies in Tally. viz "Company A" and "Company B"
2) Select the "Company A".
3) Minimize Tally software.
4) Run UDIMagic and follow instructions in the wizard to Import data from the sample Excel-sheet.
You must select the XML tags file in the same screen in which you are prompted to select the Excel file.
If everything goes fine, then 5 Stock Item master records will be imported into "Company B" in Tally.You can view the same using "Gateway of Tally >> Inventory Info >> Stock Item >> Display" option.
Attribute : COMPANYNAME
Applies to: MASTER and VOUCHER tags
Example 1:-
<MASTER TYPE="LEDGER" COMPANYNAME="MyCompany">
.....XML tags.....
</MASTER>
Example 2:-
<VOUCHER COMPANYNAME="MyCompany">
.....XML tags.....
</VOUCHER>
Remarks:-
1) The COMPANYNAME attribute can be used with the MASTER and VOUCHER tags only.
2) If there two or more MASTER tags (or VOUCHER tags) in a single XML file, then you need to use the COMPANYNAME attribute with each MASTER tag.
Saturday, June 14, 2008
Exercise 12: Using the SCROLL attribute
In this exercise, we shall take up an Excel sheet that contains Stock Item Master records with Standard Cost and Standard Price details. As the data for a single MASTER record may span to multiple rows, we have used the SCROLL attribute which is described later on.
Here's a snapshot of the Excel sheet that we shall be using in this exercise. You can create a similar sheet or Download it from here.
STEP 1
Write XML tags to create UNIT masters
<MASTER TYPE="UNIT"/>
<NAME COLUMNREFERENCE="D"/>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
Remarks:-
a) The above XML tags instruct UDIMagic to create UNIT Masters based on Column D data in the Excel sheet.
STEP 2
Write XML tags to create STOCKGROUP masters
<MASTER TYPE="STOCKGROUP"/>
<NAME.LIST>
<NAME COLUMNREFERENCE="C"/>
<NAME.LIST>
</MASTER>
STEP 3
Write XML tags to create Stock Item Masters
<!-- Create STOCKITEM Masters -->
< MASTER TYPE="STOCKITEM">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
<!-- ALIAS to be taken from Column B of Excel Sheet -->
<NAME COLUMNREFERENCE="B"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column C-->
<PARENT COLUMNREFERENCE="C"/>
<!-- BaseUnits is to be taken from Column D-->
<BASEUNITS COLUMNREFERENCE="D"/>
<!-- Standard Cost details -->
<STANDARDCOSTLIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="E"/>
<RATE FORMULA="=+F# & "/" & +G#"/>
</STANDARDCOSTLIST.LIST>
<!-- Standard Price details -->
<STANDARDPRICELIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="H"/>
<RATE FORMULA="=+I# & "/" & +J#"/>
</STANDARDPRICELIST.LIST>
</MASTER>
Remarks:-
a) The SCROLL attribute is used to specify that the data spans to multiple rows.
b) The SCROLL attribute is UDI Magic specific.
c) As you can see in the Excel sheet, the Standard Cost and Standard Price data for the first Master Record "BunnyBT1" spans to 3 rows. Also note that the Column A (key-field) contains the same Name i.e. "BunnyBT1" as all these 3 rows are part of one MASTER record.
d) Example:-
<STANDARDCOSTLIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="E"/>
<RATE FORMULA="=+F# & "/" & +G#"/>
</STANDARDCOSTLIST.LIST>
The SCROLL attribute used in the above XML tags, instructs UDI Magic that the data spans to multiple rows.
e) Use option "Alter Standard Rates" to specify the Standard Cost and Price details in the Stock Item Master as shown below:-
Here's a snapshot of the Excel sheet that we shall be using in this exercise. You can create a similar sheet or Download it from here.
STEP 1
Write XML tags to create UNIT masters
<MASTER TYPE="UNIT"/>
<NAME COLUMNREFERENCE="D"/>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
Remarks:-
a) The above XML tags instruct UDIMagic to create UNIT Masters based on Column D data in the Excel sheet.
STEP 2
Write XML tags to create STOCKGROUP masters
<MASTER TYPE="STOCKGROUP"/>
<NAME.LIST>
<NAME COLUMNREFERENCE="C"/>
<NAME.LIST>
</MASTER>
STEP 3
Write XML tags to create Stock Item Masters
<!-- Create STOCKITEM Masters -->
< MASTER TYPE="STOCKITEM">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
<!-- ALIAS to be taken from Column B of Excel Sheet -->
<NAME COLUMNREFERENCE="B"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column C-->
<PARENT COLUMNREFERENCE="C"/>
<!-- BaseUnits is to be taken from Column D-->
<BASEUNITS COLUMNREFERENCE="D"/>
<!-- Standard Cost details -->
<STANDARDCOSTLIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="E"/>
<RATE FORMULA="=+F# & "/" & +G#"/>
</STANDARDCOSTLIST.LIST>
<!-- Standard Price details -->
<STANDARDPRICELIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="H"/>
<RATE FORMULA="=+I# & "/" & +J#"/>
</STANDARDPRICELIST.LIST>
</MASTER>
Remarks:-
a) The SCROLL attribute is used to specify that the data spans to multiple rows.
b) The SCROLL attribute is UDI Magic specific.
c) As you can see in the Excel sheet, the Standard Cost and Standard Price data for the first Master Record "BunnyBT1" spans to 3 rows. Also note that the Column A (key-field) contains the same Name i.e. "BunnyBT1" as all these 3 rows are part of one MASTER record.
d) Example:-
<STANDARDCOSTLIST.LIST SCROLL="Yes">
<DATE COLUMNREFERENCE="E"/>
<RATE FORMULA="=+F# & "/" & +G#"/>
</STANDARDCOSTLIST.LIST>
The SCROLL attribute used in the above XML tags, instructs UDI Magic that the data spans to multiple rows.
e) Use option "Alter Standard Rates" to specify the Standard Cost and Price details in the Stock Item Master as shown below:-
Friday, June 13, 2008
Solution to Exercise 11
Alternative 1
Change the Heading in Cell A1 to NAME
Alternative 2
Add the following tags in your XML tags file :-
<COLUMNNAME.LIST>
<COLUMNNAME>NAME</COLUMNNAME>
</COLUMNNAME.LIST>
Remarks:-
1) Tally use the Master's NAME as the key-field to uniquely identify the Master records. Hence, we must have a Column titled as NAME or specify the same using <COLUMNNAME> tag.
Change the Heading in Cell A1 to NAME
Alternative 2
Add the following tags in your XML tags file :-
<COLUMNNAME.LIST>
<COLUMNNAME>NAME</COLUMNNAME>
</COLUMNNAME.LIST>
Remarks:-
1) Tally use the Master's NAME as the key-field to uniquely identify the Master records. Hence, we must have a Column titled as NAME or specify the same using <COLUMNNAME> tag.
Subscribe to:
Posts (Atom)