Exercise 21

Import Stock Item Masters with GST Tax Rates

Published 22 Oct 2026

---

This exercise imports Unit and Stock Item masters together with their HSN/SAC code and GST tax rates from Excel into Tally Prime. It builds on the Stock Item Masters with Opening Stock exercise. Every value is read from the sheet through COLUMNREFERENCE or FORMULA, and SKIP leaves out the opening value, the GST details and the Cess rates that are not needed for an item.

The Excel sheet

Each row is one stock item: Name (column B), Unit (C), Opening Value (D), GST applicable (E) and its applicable-from date (F), HSN/SAC code and description (G, H), Taxability (M), the flags in columns I, N and O, the tax percentages (columns P to W), and the type of supply (X). Diesel (row 21) is a non-GST good, so its GST details are left empty.

Step 1 — Create the Unit master

The Unit is created from column C. ORIGINALNAME is not used because the same unit repeats on many rows. The GST Unit Quantity Code (UQC) is chosen from the unit name with nested If functions, and any unit that is not listed (such as Strip) falls back to OTH-OTHERS.

<!-- Unit Master from Column C (units must exist before stock items) -->
  <MASTER TYPE="UNIT">
    <NAME COLUMNREFERENCE="C"/>
    <!-- REMARK [Duplicate Original Name]: ORIGINALNAME is not used. The same unit repeats on many CSV rows, and a repeated ORIGINALNAME is rejected as a duplicate, so the unit was never created and stock items failed with "unit does not exist". -->
    <ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
    <ISGSTEXCLUDED>No</ISGSTEXCLUDED>
    <!-- REMARK [Unit Quantity Code]: UQC is mapped per unit name from column C (GST portal UQC list). Strip has no UQC of its own, so it maps to OTH-OTHERS; any unit not listed here also falls back to OTH-OTHERS. -->
    <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

• Basics — the name (column B) and unit (column C). OPENINGVALUE uses SKIP when column D is 0 or blank, and the value is multiplied by -1 because stock value is negative in Tally.
• GST applicable — GSTAPPLICABLE adds the ~[~ prefix to column E to get the Tally system value Applicable / Not Applicable. SKIP on GSTDETAILS.LIST leaves out all GST details when the item is not GST applicable.
• Applicable from — udiMagic reads column F as an Excel date serial, so Text(F#,"yyyymmdd") converts it to the date format Tally expects.
• Rates — Central Tax (column Q), State Tax (column R) and Integrated Tax (column P) are taken directly. The UT % column is not imported because Tally has no UT head.
• Cess and State Cess — the valuation type is Based on Value when the percentage is given, Based on Quantity when only the quantity rate is given, and Not Applicable when both are 0. SKIP removes the rate in the last case.
• HSN details — HSNDETAILS.LIST holds the HSN/SAC code (column G) and its description (column H) at the item level.

<!-- 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: Opening value (column D) is skipped when blank or 0. Stock value is negative in Tally. -->
    <OPENINGVALUE SKIP="=Round(D#,2)=0" FORMULA="=Round(D#,2)*-1"/>

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

    <!-- REMARK: GST details are skipped for items where GST is Not Applicable (e.g. non-GST goods like Diesel). -->
    <GSTDETAILS.LIST SKIP="=E#&lt;&gt;&quot;Applicable&quot;">
      <!-- REMARK [Applicable From Date]: udiMagic reads column F as an Excel date serial (e.g. 46113), not as dd-mm-yyyy text. The earlier Right/Mid/Left formula built an invalid date (28262642) and Tally ignored the whole GST block, so no rates showed. Text() converts the date to yyyymmdd. If Text() is not accepted, use =Year(F#)*10000+Month(F#)*100+Day(F#). -->
      <APPLICABLEFROM FORMULA="=Text(F#,&quot;yyyymmdd&quot;)"/>
      <!-- REMARK: On Value / On Item Rate (column J); blank defaults to On Value. -->
      <CALCULATIONTYPE FORMULA="=If(Len(trim(J#))=0,&quot;On Value&quot;,J#)"/>
      <!-- REMARK [Tally Prime 3.0+]: "Specify Details Here" = GST rate details are set on the item itself. -->
      <SRCOFGSTDETAILS>Specify Details Here</SRCOFGSTDETAILS>
      <HSNCODE COLUMNREFERENCE="G"/>
      <!-- REMARK: HSN/SAC description from column H. -->
      <HSN COLUMNREFERENCE="H"/>
      <!-- REMARK: Taxability from column M: Taxable / Exempt / Nil Rated / Non-GST. Must match Tally spelling. -->
      <TAXABILITY COLUMNREFERENCE="M"/>
      <!-- REMARK [Tally Prime 3.0+]: GST flags on the stock item (reverse charge, non-GST goods, ineligible ITC). -->
      <ISREVERSECHARGEAPPLICABLE COLUMNREFERENCE="N"/>
      <ISNONGSTGOODS COLUMNREFERENCE="I"/>
      <GSTINELIGIBLEITC COLUMNREFERENCE="O"/>

      <!-- 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 Q) -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>Central Tax</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE>Based on Value</GSTRATEVALUATIONTYPE>
          <GSTRATE FORMULA="=Round(Q#,2)"/>
        </RATEDETAILS.LIST>

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

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

        <!-- REMARK [Cess Valuation Type]: Cess uses "Not Applicable" (~[~ prefix = Tally system value) when columns T and U are both 0/blank. "Based on Value" with no rate caused the invalid/not specified error. GSTRATE is skipped in that case. -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>Cess</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE FORMULA="=If(Round(T#,2)&gt;0,&quot;Based on Value&quot;,If(Round(U#,2)&gt;0,&quot;Based on Quantity&quot;,&quot;~[~ Not Applicable&quot;))"/>
          <GSTRATE SKIP="=Round(T#,2)+Round(U#,2)=0" FORMULA="=If(Round(T#,2)&gt;0,Round(T#,2),Round(U#,2))"/>
        </RATEDETAILS.LIST>

        <!-- REMARK [Cess Valuation Type]: State Cess follows the same rule using columns V and W. -->
        <RATEDETAILS.LIST>
          <GSTRATEDUTYHEAD>State Cess</GSTRATEDUTYHEAD>
          <GSTRATEVALUATIONTYPE FORMULA="=If(Round(V#,2)&gt;0,&quot;Based on Value&quot;,If(Round(W#,2)&gt;0,&quot;Based on Quantity&quot;,&quot;~[~ Not Applicable&quot;))"/>
          <GSTRATE SKIP="=Round(V#,2)+Round(W#,2)=0" FORMULA="=If(Round(V#,2)&gt;0,Round(V#,2),Round(W#,2))"/>
        </RATEDETAILS.LIST>

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

    <!-- REMARK [Tally Prime 3.0+]: HSN/SAC at item level, kept after GSTDETAILS.LIST. The applicable-from date is fixed at 01-04-2023 and the HSN code comes from column G. The HSN description comes from column H. -->
    <HSNDETAILS.LIST>
      <APPLICABLEFROM>20230401</APPLICABLEFROM>
      <HSNCODE COLUMNREFERENCE="G"/>
      <HSN COLUMNREFERENCE="H"/>
      <SRCOFHSNDETAILS>Specify Details Here</SRCOFHSNDETAILS>
    </HSNDETAILS.LIST>
  </MASTER>

---