Advanced Macros : Lookup Masters from a different Sheet

Here's a sample Excel file that contains "Journal" Vouchers which are to be imported into Tally.

In this sample, macros have been used to lookup the Masters data. The sample Excel-file contains two sheets as shown below:-
1) Vouchers
2) Masters

Here's how the "Vouchers" sheet looks like:-

Here's how the "Masters" sheet looks like:-

Remarks:-
1) The "Vouchers" sheet contains a Column "Bank Name". The values in this Column are replaced at run-time using VBA (macros). Example :- "Axis" is replaced with "Axis bank Ltd" at runtime.
2) In the "Masters" sheet, we have specified the Group-Name under which the Account is to be created.
3) In case, if a Master is not specified in the "Masters" sheet, UDI-Magic automatically creates that Ledger under a Group called "New Ledger Items".

Follow these steps to Import the Excel sheet data into Tally Software.

1) As macro file is used, you must enable the feature "Trust Access to Visual basic Project". Refer the FAQ: Trust Access to Visual basic Project

2) Download and unzip the files into "c:\udi-magic" folder.
Though you can keep these files in any folder, the Macro-file must always be kept in the "c:\udi-magic" folder.

3) Start the Tally software and create/open a Dummy Company.
4) Minimize Tally Software
5) Run UDIMagic utility, select option "Excel to Tally -> Voucher".
6) Next, select the Excel sheet and the XML-tags file when prompted.
Note that the XML-tags file is to be selected in the same screen in which you are prompted to select the Excel-sheet.

7) Next, follow the instructions given in the wizard-screen to import data into Tally.

ZIP file contents
1) journal-vouchers-with-service-tax.xls (Excel sheet)
2) journal-vouchers-with-service-tax-xml-tags.xml (XML tags file)
3) journal-vouchers-with-service-tax-macro.txt (contains macros)

In Brief
UDIMagic supports VBA (Visual Basic for Applications), a powerful feature that allows you to automate and perform almost all tasks that can be done manually in MS-Excel. In this sample, we use VBA to lookup the Master's Full-Name and the Group-Name from the "Masters" sheet.

---

Sample Excel Sheets : Correction in Voucher Date

When you Download and install UDIMagic Demo version, few sample Excel sheets get created when you run it for the first time.

We have noticed that the Excel sheets containing Voucher records (Sales; Purchase; Payment; Receipt etc) had Voucher Date as 01-04-2007 (dd-mm-yyyy). This generated error if you imported the Vouchers data into your Tally Company with Books beginning date as 01-04-2008 (dd-mm-yyyy). The error message is given below for your reference :-

"The date 1-4-2007 is Out of Range"

Remarks:-
The Voucher Date in the Excel sheets is 1-4-2007 whereas the Books Beginning-Date in the Tally (Company) is specified as 1-4-2008.

Now, the Voucher Date in the sample Excel Sheets has been corrected and changed to 01-04-2008 in UDIMagic v3.0 Release1.45. Alternatively, you can manually change the Date in the Excel Sheets which get created in the "C:\udiMagic" folder.

---

Using UDIMagic to Import Price List into Tally 9

Here's a sample Excel sheet that contains StockItem Master records with multiple Price Levels.

For those who are not familiar with the Price List (Multiple Price Level) feature of Tally, here's a brief introduction:-

Price List in Tally 9

Tally Software allows you to maintain different Price Levels for a single Stock-Item. For example, a stock-item may be sold at different rates to :-

a) Whole-sale Dealers
b) Retailers
c) End-users

Tally Software provides "Multiple Price Levels" feature that allows you to accomplish this task.

Enabling the Multiple Price Level feature

To import Stock-Item Masters with multiple Price Level from the sample Excel sheet, you must first enable the Multiple Price-level feature in Tally using the underneath option :-

Gateway of Tally -> F11 -> Inventory features -> Use Multiple Price Levels=Yes

Follow these steps to Import the Excel sheet data into Tally Software.

1) Download and unzip the files into "c:\udi-magic" folder.
2) Start the Tally software and create/open a Dummy Company.
3) Enable the Multiple Price Level feature using F11 -> Inventory Features option. Next, add two Price Levels viz a) MRP b) ListPrice
4) Minimize Tally Software
5) Run UDIMagic utility, select option "Excel to Tally -> Master".
6) Next, select the Excel sheet prompted.
7) Next, follow the instructions in the wizard to import data into Tally.


Notes:-
1) XML tags are written in the comments section of Cell c1 of the Excel Sheet. These XML tags are used by UDIMagic to identify and read data from the Excel sheet and send it to Tally Software.
2) XML tags for using Price-List feature are given below:-

<FULLPRICELIST.LIST>
<DATE>20080401</DATE>
<!-- Specify the Price Level here -->
<PRICELEVEL>MRP</PRICELEVEL>
<PRICELEVELLIST.LIST>
<!-- Specify the Rate here. Ex: <RATE>50.00 / Nos</RATE> -->
<RATE FORMULA="=+F# &amp; &quot;/&quot; &amp; E#"/>
</PRICELEVELLIST.LIST>
</FULLPRICELIST.LIST>

---

XML tags with Formula's

UDIMagic || Excel to Tally

This post illustrates how to write XML tags that include formula's. For those who are new, here's a brief introduction to UDIMagic.

"UDIMagic is a software utility that allows you to Import Excel data into Tally Software. It can process any type of data (Masters or Vouchers) irrespective of the format in which it is arranged in the Excel sheet. All you need to do is to specify what data you have in the Excel sheet and where is it stored. This is done using XML tags."

In simple words, UDIMagic uses the XML tags to read the Excel sheet data and then Import it into the Tally Software.

Now, let's us try to understand how to write the XML tags. For illustrative purpose, we have taken up a single tag i.e. the NARRATION tag which is used to specify the Narration in Voucher-entries.

Format 1
<NARRATION> This is a Static Narration </NARRATION>

Remarks:-
The above XML tag instruct UDIMagic that NARRATION tag has a static / fixed value.

Format 2
<NARRATION COLUMNREFERENCE="B"/>

Remarks:-
In the above XML tag, we have used an attribute named COLUMNREFERENCE which instructs UDIMagic to fetch the value of NARRATION tag from Column B of the Excel sheet.

Format 3
<NARRATION FORMULA="=+B# &amp; &quot; &quot; &amp; C#"/>

Remarks:-
In the above XML tag, we have used an attribute named FORMULA which instructs UDIMagic that NARRATION tag value is to be taken from TWO columns viz Column B and Column C.

Writing FORMULA's

Before you write any formula in XML, it is advised that you test the same in MS-Excel. Here are the steps for writing / testing the Formula that we require :-

1) Start Excel and open a new workbook
2) Enter "Shweta" in Cell B2
3) Enter "Computers" in Cell C2
4) Enter a formula in Cell D2 as shown below:-
=B2 & " " & C2

If you correctly enter the aforesaid formula, then you will get "Shweta Computers" in Cell D2. After you get the correct result, you can then write the formula in your XML tags with some modifications.

The aforesaid formula in XML format is as follows:-

Formula in XML
=B# &amp; &quot; &quot; &amp; C#

Formula in Excel
=B2 & " " & C2

Remarks:-
1) The ampersand sign (&) and quote (") are special characters and must be written differently in XML. The ampersand sign (&) is written as &amp; and the quote (") is written as &quot; in XML.

2) For more details on XML Escape sequences, please refer http://www,rtslink.com/introductionxmlsoap.html

---

UDIMagic Multi-user version

Multi-user version of UDIMagic is now available. For cost details, please refer http://www.rtslink.com/buy.html

---

Stock Item Masters with Batch expiry

Here's a sample Excel-sheet that contains StockItem Masters with Batch expiry and Multiple Locations.

As we are using Multiple-Godown and Batch-wise expiry features, the StockItem Master records may span to multiple rows, as seen in the sample Excel-sheet. The NAME field (i.e. StockItem Name) is used as the Key-field.

We are also using a Macro file which is required to do some calculations at run-time. Due to this, you may get an error message, if you are using Excel 2003 or higher. To solve this issue, refer FAQ: "Programmatic access to Visual basic project is not trusted".

Follow these steps to Import the Excel sheet data into Tally Software.

1) Download and unzip the files into "c:\udi-magic" folder.
Though you can keep these files in any folder, the Macro-file must always be kept in the "c:\udi-magic" folder.

2) Start the Tally software and create/open a Dummy Company.

3) Enable the following features using F11 key :-
a) Maintain Multiple Godowns
b) Maintain Batch-wise details

4) Minimize Tally Software
5) Run UDIMagic utility, select option "Excel to Tally -> Master".

6) Next, select the Excel sheet and the XML-tags file when prompted.
Note that the XML-tags file is to be selected in the same screen in which you are prompted to select the Excel-sheet.

7) Next, follow the instructions given in the wizard-screen to import data into Tally.

ZIP file contents
1) stockitem-with-batch-expiry.xls (Excel sheet)
2) stockitem-with-batch-expiry-xml-tags.xml (XML tags file)
3) stockitem-with-batch-expiry-macro.txt (contains macros)

---

Set / Modify default Ledger for Stock Items in Tally 9

Here's a case wherein the Customer already had few thousand Stock-Item Master records in Tally 9. The requirement was to set the Default Ledger for these Stock-Items.

As all the Stock-Items were of similar type (i.e. spares parts), a common ledger was to be assigned to all the Stock-Items. Example:-

Stock-Item Name : Bush
Ledger Name : Sales @ 12.5%
VAT : Sales @ 12.5%
Posting : 100%

Though this could be easily done if we could directly access the Tally database, like using REPLACE or UPDATE (SQL) in other programming languages to update any database/table.

Herein, we had follow these steps:-
1) Export the Stock-Item Masters from Tally 9 into Excel sheet
2) Add the Ledger Name; VAT; Posting details in Excel sheet
3) Copy/paste the Ledger-name;VAT; Posting details to all Stock-Items in the Excel-sheet.
4) Next, import the data into Tally 9 using UDIMagic utility.

Download the Excel sheet and XML tags for Set-default-Ledger-for-StockItems.zip

---