Working with Templates

Topics

Standard templates
udi-Magic Software comes with a number of pre-designed Excel formats for uploading data into Tally ERP. These formats are available in the c:\udi-magic folder. In addition, a mapping file is provided for each Excel format. The mapping file has the same name as that of the Excel file.
Note

A template is a special format meant to be used as a starting point for creating new formats.

A standard udi-Magic template comprises of 2 files viz:
Excel file Example: Vouchers-Sales-with-StockItems.xls
Mapping file Example:Vouchers-Sales-with-StockItems-xml-tags.xml
Import data into Tally [Standard template]
Follow the underneath steps to upload data from a udi-Magic Standard format into Tally ERP.

Steps:
#1. Start the udi-Magic software
#2. Select option Excel to Tally
#3. Select option Import data into Tally
#4. Select Excel File <Any-Standard-udiMagic-template>
#5. Next, click the Start button to import data into Tally

On completion, you can view the data in Tally using option Gateway of Tally >> Display >> Daybook.

What is a Custom template ?
A Custom template is a user-defined template.

Common uses are to create custom templates for Flipkart or Amazon or Snapdeal sales so that you can directly import data from the Excel or CSV file downloaded from the E-Commerce web-site into Tally ERP.

Creating a Custom template
Custom templates are created based on udi-Magic Standard templates. You don't have to alter your existing excel sheet, just map your excel sheet columns with one of the udi-Magic Standard templates. Mapping is done using Excel formulas.

Steps:
#1. Start the udi-Magic software
#2. Select option Excel to Tally
#3. Select option Create Custom Template
#4. Select files
1st File: <Standard-udiMagic-template> Example: Vouchers-V7-Sales-Purchase-Advanced.xls
2nd File: <Your-Excel-File>
#5. Map using Excel formulas
#6. Click the save button and <name> your Custom template file

On completion, a mapping file is generated.

Note A mapping file contains code / instructions to upload data from Excel into Tally. It is also called as XML file.
Import data into Tally [Custom template]
Follow the underneath steps to upload data from a Custom Excel format into Tally ERP.

Steps:
#1. Start the udi-Magic software
#2. Select option Excel to Tally
#3. Select option Import data into Tally
#4. Select files
Excel File: <Your-Excel-file>
Mapping File: <Custom-Mapping-file>
#5. Next, click the Start button to import data into Tally
Note While uploading data using a Custom Excel template, you must enable the option "Use separate file for XML tags" and select the relavant mapping/xml file.
How to map my Excel sheet with udi-Magic templates ?
The mapping process requires some preliminary work to be done.

Assuming, you want to map your Sales Excel format to one of the udi-Magic Standard templates, you will have to :

(1) List down all important and mandatory fields required for a Sales entry in Tally ERP.
Example: Voucher-type; Invoice-Date; Invoice-Number; Party-Name; Item-Name; Qty; Rate; Item-Amount; Tax-type; Tax-Rate; Tax-Amount etc.
(2) You must also note down your Excel-file Column-Name against each field.

Let's take an example of mapping Snapdeal Sales format. The relavant files are :

udi-Magic Standard template : c:\udi-MagicV70\Vouchers-V7-Sales-Purchase-Advanced.xls
Excel File to be mapped : c:\udi-MagicV70\MyTemplates\Snapdeal-Sales.xls.

A table is given underneath wherein Mapping details are given in the 2nd column named MAP (your-excel-sheet)

udi-Magic template
Column		 Map with
(Your-Excel-sheet)		 Remarks
UniqueID C Specify the Mapping as given for InvoiceNo.
InvoiceNo C

Q: In which column of your Excel sheet does the Invoice Number appear?

A: In the Snapdeal excel file, it appears in Column C. Hence, we have specified C in the Mapping Column.
InvoiceDate left(L#,10)

Q: In which column of your Excel sheet does the Invoice Date appear?

A: In the Snapdeal excel file, it appears in Column L. However, as the DATE is in long format (dd/mm/yyyy HH:MM:SS), we use the excel function left() to fetch the first 10 characters.
Voucher-type "SD-Sales"

Q: In which column of your Excel sheet does the Voucher-type appear?

A: No, it is not there in the Snapdeal excel file. Hence, we have taken fixed value as "SD-Sales".
Customer or Party-Name "Jasper"

Q: In which column of your Excel sheet does the Customer-Name appear?

A: In case of e-tailers, instead of maintaining separate ledgers for each buyer, it is generally preferred to have a single Party Ledger A/c and then use the bill-by-bill feature to track receipts. Hence, we have taken fixed value as "Jasper".
StockItem-Name D

Q: In which column of your Excel sheet does the StockItem-Name appear?

A: In the Snapdeal excel file, it appears in Column D. Hence, we have specified D in the Mapping Column.
Units "Pcs"

Q: In which column of your Excel sheet does the Unit of measurement appear?

A: No, it is not there in the Snapdeal excel file. Hence, we have taken fixed value as "Pcs".
Qty 1

Q: In which column of your Excel sheet does the Item-Qty appear?

A: No, it is not there in the Snapdeal excel file. Hence, we have taken fixed value as 1.
Rate Round(AP#*100/(100+AR#),2)

Q: In which column of your Excel sheet does the Rate appear?

A: In the Snapdeal excel file, it appears in Column AP. However, the rate is inclusive of Tax, and the Tax-rate is given in Column AR.

Now we have to reverse calculate the rate-excluding-tax.
It will be ItemRate * 100 / (100+TaxRate)
i.e. AP# * 100 / (100+AR#)
i.e. Round(AP# * 100 / (100+AR#),2) rounded to 2 decimals
Amount Round(AP#*100/(100+AR#),2)

Q: In which column of your Excel sheet does the StockItem-Amount appear?

A: As the Qty is always 1, we take the Formula as used for Rate.
TaxType AQ# & " " & AR# & "%"

Q: In which column of your Excel sheet does the Tax-Type appear?

A: Tax-Rate is not specified in Snapdeal Excel sheet. We have added two columns at the end viz AQ and AR which contain Tax-type and Tax-Rate.
Example: VAT 5% or VAT 15%
Tax-Amount Round(AP#*AR#/(100+AR#),2)

Q: In which column of your Excel sheet does the Tax-Amount appear?

A: It is quite similar to the Item-Rate formula. Here, we need the Tax-amount which is computed as follows :
It will be ItemRate * TaxRate / (100+TaxRate)
i.e. AP# * AR# / (100+AR#)
i.e. Round(AP# * AR# / (100+AR#),2) rounded to 2 decimals
Note The mapped Snapdeal Sales format is provided with udi-Magic Software. Use these steps to view/modify it:
#1. Start the udi-Magic software
#2. Select option Excel to Tally
#3. Select option Modify Custom template
#4. Select file Snapdeal-Sales
#5. Click the Next button to view/modify the Mapping
#6. Click the Save button to save changes or the Exit button to exit
Understanding the Excel formulas used for MAPPING
The underneath table shows how formulas can be used for mapping.

Description Excel Formula udi-Magic Mapping Formula
Formula with single Column =A2*5/100 A#*5/100
Formula with multiple Columns =(A2+B2+C2)*5/100 (A#+B#+C#)*5/100
Static text with Cell-references ="Order Id: " & B2 "Order Id: " & B#
Rounding off to 2 decimals =Round(A2,2) Round(A#,2)
Offset Formula to get cell-value from next row =Offset(A2,0,1) Offset(A#,0,1)
Sum value for a specific Column for multiple rows =Sum(A2:A5) {Round(A#,2)}
Fixed-value ex. VoucherType ="Local Sales" "Local Sales"
Absolute and Relative Cell-references =A2+B$1 A#+B$1
Note Use "#" for relative row numbers like A#,B# etc.