Exercise 3

Import Stock Item Masters with Opening Stock

Published

This exercise extends the basic Stock Item master import by also bringing in Opening Quantity, Rate and Value for each item, along with its Unit, Stock Group and Part Number. It introduces three new tags — OPENINGBALANCE, OPENINGRATE and OPENINGVALUE — the last of which uses a FORMULA instead of a plain COLUMNREFERENCE.

The Excel sheet

A B C D E F G
1 NAME PARENT UNIT PARTNO OPENING-QTY RATE VALUE
2 Washer Spares Nos S100 5 100 500
3 Shim Spares Nos S101 4 80 320
4 Shaft PTO Rear CPTE Spares Nos S102 3 60 180
5 Engine Oil Oil Ltrs V100 2 40 80
6 Gear Oil Oil Ltrs V101 1 20 20

Step 1 — Create the Unit, Stock Group and Stock Item masters

The Stock Item master needs a Unit (Column C) and a Stock Group (Column B) to exist before it can be created — udiMagic processes MASTER tags in the order they're written, so the UNIT and STOCKGROUP tags come first, followed by the STOCKITEM tag. 

<XMLTAGS xmlns:UDF="TallyUDF" CELLREFERENCE="A1">
<!--  Create UNIT Masters  -->
<MASTER TYPE="UNIT">
<!--  The UNIT Master does not support Aliases.  -->
<!--  As a result, we must not use the NAME.LIST tag, but directly use the NAME tag  -->
<NAME COLUMNREFERENCE="C"/>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
<!--  Create StockGroup Masters  -->
<MASTER TYPE="STOCKGROUP">
<NAME.LIST>
<!--  Fetch the value for NAME tag from Column B of Excel Sheet  -->
<NAME COLUMNREFERENCE="B"/>
</NAME.LIST>
</MASTER>
<!--  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>
<!--  StockGroup Name to be taken from Column B  -->
<PARENT COLUMNREFERENCE="B"/>
<!--  BaseUnits to be taken from Column C  -->
<BASEUNITS COLUMNREFERENCE="C"/>
<!--  Part Number to be taken from Column D  -->
<ADDITIONALNAME COLUMNREFERENCE="D"/>
<!--  Stock-item Opening Qty to be taken from Column E  -->
<OPENINGBALANCE COLUMNREFERENCE="E"/>
<!--  Stock-item Rate to be taken from Column F  -->
<OPENINGRATE COLUMNREFERENCE="F"/>
<!--  Stock-item Value is a formula that is computed as G#*-1  -->
<OPENINGVALUE FORMULA="=+G# * -1"/>
</MASTER>
</XMLTAGS>
Note:
  1. OPENINGBALANCE and OPENINGRATE use a plain COLUMNREFERENCE, just like NAME or PARENT — they simply pull the Opening Qty and Rate straight from Columns E and F.
  2. OPENINGVALUE instead uses a FORMULA. The # is a placeholder that udiMagic replaces with the current row number at runtime — for row 2 the formula becomes =+G2 * -1.
  3. The * -1 is needed because Tally stores a Stock Item's opening value as a negative (debit) figure, while the sample Excel sheet has it as a positive number. It's a good habit to test a FORMULA directly in Excel first, before using it in the XML tags — see udiMagic XML tags for the full tag/attribute reference.

Step 2 — Import into Tally

  1. Start Tally Prime and open the company you want to import into.
  2. Minimize Tally (leave it running).
  3. Start udiMagic and select Excel to Tally.
  4. Select option Masters.
  5. Browse to select the Excel sheet and the XML tags file.
  6. Follow the wizard to complete the import — Units, Stock Groups and Stock Items (with their opening stock) are all created in a single run.
Sample files: The sample Excel sheet and matching XML tags file for this exercise will be linked here shortly.

Ready to import your Excel data into Tally?

Download udiMagic and start a 14-day free trial — no credit card required.

Download udiMagic View Pricing