Exercise 6: Stock Item Masters with Opening Stock details

This is an extension to the previous Exercise 5. Herein, we have added three new columns in the Excel sheet which are as follows:-
1) Opening-Qty (Column E in the Excel Sheet)
2) Rate (Column F in the Excel Sheet)
3) Value (Column G in the Excel Sheet)

Download the Excel sheet and XML tags file.

The XML tags for this Exercise will be similar to what we used in Exercise 5, except for the additional tags that are required for OPENING STOCK . The same are given below:-

XML tags for Opening Stock
<OPENINGBALANCE COLUMNREFERENCE="E"/>
<OPENINGRATE COLUMNREFERENCE="F"/>
<OPENINGVALUE FORMULA="=+G# * -1"/>

Remarks:-
1) The first 2 tags are quite simple. We use the COLUMNREFERENCE attribute to specify the Excel-sheet Column from which UDIMagic must read the data.
2) The third tag i.e. OPENINGVALUE uses a new attribute called FORMULA. This is required as the Column G in the Excel sheet contains Stock-Value (positive values) whereas Tally Software requires the Opening Stock Value to be Negative. This may appear to be absurd at first but well Tally uses NEGATIVE values for DEBIT and POSITIVE values for CREDIT effect.

The FORMULA attribute

The FORMULA attribute is a UDIMagic-supported attribute that is used to perform calculations. It allows us to use any valid Excel-formulas. Now, let try to understand the FORMULA that we have used above.

<OPENINGVALUE FORMULA="=+G# * -1"/>

a) As mentioned earlier, Tally requires the Stock-values to be NEGATIVE (Debit-value) and hence we need to multiply the Column G values with -1 (minus 1).

b) In the aforesaid FORMULA, the # (hash symbol) is replaced by the Row numbers by UDIMagic at run-time. For example, when UDIMagic reads the Row no. 2, it interprets the FORMULA as follows:-
=+G2 * -1

c) Formula's in MS-Excel must start with the "=" (equal-to sign). You can manually try out entering this formula "=+G2 * -1" (without the quotes) into Cell H2.

d) It is advised that you first try out writing/testing your Formula in MS-Excel and when found OK, you can modify it as per UDIMagic format, if required. Example =+G2 *-1 becomes =+G# *1

The FORMULA attribute is of wide import as it allows us to use hundreds of Formula's that are supported by MS-Excel. It is adviced that you spend some-time playing with MS-Excel and try out different-different formula's that are commonly used in Excel.