Exercise 19

Import Stock Item Masters with GST Tax Rates

Published


This exercise shows how to import Unit and Stock Item masters into Tally Prime, along with each item's HSN/SAC code and GST tax rates (IGST, CGST and SGST), directly from your Excel sheet. It builds on the Stock Item Masters with Opening Stock exercise. Items that are not subject to GST are imported without any GST details, so one sheet can hold your entire stock list.

The Excel sheet

Each row is one stock item: Name (column B), Unit (C), GST applicable (D), the GST applicable date (E, dd/mm/yyyy), HSN/SAC code and description (F, G), the type of supply (H), Taxability (I), and the tax percentages IGST, CGST and SGST (J, K, L). Keep the HSN/SAC Code column (F) formatted as Text so codes such as 0401 keep their leading zero.

ABCDEFGHIJKL
1sr-nonameunitsgst-applicablegst-applicable-datehsn-codehsn-descriptionType-of-Supplytaxabilityigstcgstsgst
21Basmati Rice (Packed)KgApplicable01/04/20231006RiceGoodsTaxable52.52.5
32SugarKgApplicable01/04/20231701Cane or beet sugarGoodsTaxable52.52.5
43Fresh MilkLtrApplicable01/04/20230401Milk and creamGoodsNil Rated000
54Packaged Drinking WaterLtrApplicable01/04/20232201Waters including mineral watersGoodsTaxable1899
65Aerated Soft DrinkLtrApplicable01/04/20232202Sweetened or flavoured watersGoodsTaxable402020

Step 1 — Create the Unit master

The Unit is created from column C. Every unit also needs a GST Unit Quantity Code (UQC), the standard unit code from the GST portal. It is picked from the unit name: Kg, Ltr, Nos, Bag and Pkt each get their own code, and any other unit (for example Strip) gets OTH-OTHERS.

<!-- Unit Master from Column C (units must exist before stock items) -->
  <MASTER TYPE="UNIT">
    <NAME COLUMNREFERENCE="C"/>
    <ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
    <ISGSTEXCLUDED>No</ISGSTEXCLUDED>
    <GSTREPUOM FORMULA="=If(C#=&quot;Kg&quot;,&quot;KGS-KILOGRAMS&quot;,If(C#=&quot;Ltr&quot;,&quot;LTR-LITRES&quot;,If(C#=&quot;Nos&quot;,&quot;NOS-NUMBERS&quot;,If(C#=&quot;Bag&quot;,&quot;BAG-BAGS&quot;,If(C#=&quot;Pkt&quot;,&quot;PAC-PACKS&quot;,&quot;OTH-OTHERS&quot;)))))"/>
  </MASTER>

Step 2 — Create the Stock Item master with GST details

  <!-- Stock Item Master from Column B; HSN and GST rates come from the same CSV row -->
  <MASTER TYPE="STOCKITEM">
    <NAME.LIST>
      <NAME COLUMNREFERENCE="B"/>
    </NAME.LIST>
    <!-- REMARK: No PARENT tag, so items are created under Primary. -->
    <BASEUNITS COLUMNREFERENCE="C"/>

    <!-- REMARK: Applicable / Not Applicable from column D; ~[~ prefix = Tally system value. -->
    <GSTAPPLICABLE FORMULA="=&quot;~[~ &quot; &amp; D#"/>
    <GSTTYPEOFSUPPLY COLUMNREFERENCE="H"/>

    <!-- REMARK: GST details are skipped for items where GST is Not Applicable. -->
    <GSTDETAILS.LIST SKIP="=D#&lt;&gt;&quot;Applicable&quot;">
      <!-- REMARK: Applicable-from date is mapped directly from column E. -->
      <APPLICABLEFROM COLUMNREFERENCE="E"/>
      <CALCULATIONTYPE>On Value</CALCULATIONTYPE>
      <!-- REMARK [Tally Prime 3.0+]: "Specify Details Here" = GST rate details are set on the item itself. -->
      <SRCOFGSTDETAILS>Specify Details Here</SRCOFGSTDETAILS>
      <HSNCODE COLUMNREFERENCE="F"/>
      <HSN COLUMNREFERENCE="G"/>
      <!-- REMARK: Taxability from column I: Taxable / Exempt / Nil Rated / Non-GST. Must match Tally spelling. -->
      <TAXABILITY COLUMNREFERENCE="I"/>
      <ISREVERSECHARGEAPPLICABLE>No</ISREVERSECHARGEAPPLICABLE>
      <TYPEOFSUPPLY COLUMNREFERENCE="H"/>
      <GSTINELIGIBLEITC>No</GSTINELIGIBLEITC>

      <!-- REMARK: Rates apply to all states (~[~ Any). Duty head names must be Central Tax / State Tax / Integrated Tax, not CGST / SGST / IGST. -->
      <STATEWISEDETAILS.LIST>
        <STATENAME>~[~ Any</STATENAME>

        <!-- Central Tax = CGST % (column K) -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>Central Tax</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>Based on Value</GSTRATEVALUATIONTYPE>
          <GSTRATE FORMULA="=Round(K#,2)"/>
        </RATEDETAILS.LIST>

        <!-- State Tax = SGST % (column L) -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>State Tax</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>Based on Value</GSTRATEVALUATIONTYPE>
          <GSTRATE FORMULA="=Round(L#,2)"/>
        </RATEDETAILS.LIST>

        <!-- Integrated Tax = IGST % (column J) -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>Integrated Tax</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>Based on Value</GSTRATEVALUATIONTYPE>
          <GSTRATE FORMULA="=Round(J#,2)"/>
        </RATEDETAILS.LIST>

        <!-- REMARK: Cess is not applicable for these items. -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>Cess</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>~[~ Not Applicable</GSTRATEVALUATIONTYPE>
        </RATEDETAILS.LIST>

        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>State Cess</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>~[~ Not Applicable</GSTRATEVALUATIONTYPE>
        </RATEDETAILS.LIST>

      </STATEWISEDETAILS.LIST>
    </GSTDETAILS.LIST>

    <!-- REMARK [Tally Prime 3.0+]: HSN/SAC at item level, kept after GSTDETAILS.LIST. -->
    <HSNDETAILS.LIST>
      <APPLICABLEFROM COLUMNREFERENCE="E"/>
      <HSNCODE COLUMNREFERENCE="F"/>
      <HSN COLUMNREFERENCE="G"/>
      <SRCOFHSNDETAILS>Specify Details Here</SRCOFHSNDETAILS>
    </HSNDETAILS.LIST>
  </MASTER>

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 Import data into Tally.
  5. Browse to select both the Excel sheet and the XML tags file — you're prompted for the XML file on the same screen as the Excel sheet.
  6. Follow the wizard to complete the import.
Remarks:
  1. The Unit master must be created before the Stock Item master, so it comes first in the XML tags.
  2. No PARENT tag is used, so the items are created under Primary.
  3. Column A (serial number) is not imported. The calculation type (On Value), reverse charge and ineligible input credit (No) are fixed values in the XML tags.
  4. The rate head names must be Central Tax, State Tax and Integrated Tax, not CGST, SGST and IGST. Taxability must match Tally's spelling: Taxable, Exempt, Nil Rated or Non-GST.
  5. Tags marked Tally Prime 3.0+ in the comments are needed only for Tally Prime 3.0 and later.
Sample files: Download the sample Excel sheet and matching XML tags file for this exercise. Download stock-item-master-with-gst-tax-rates.zip

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