Exercise 18

Import Ledgers with GST Tax Rates

Published


This exercise teaches you how to import Ledger masters with their GST details and tax rates from Excel into Tally Prime. It creates Sales, Purchase, and Tax (IGST/CGST/SGST) ledgers.

Step 1 — Prepare the Excel sheet

Create an Excel sheet with one Sales ledger, one Purchase ledger and three Tax ledgers. For Sales and Purchase ledgers, fill up the GST details (columns D to J) and for duty ledgers (CGST, SGST, IGST) fill up the details in columns K to M.

A B C D E F G H I J K L M
1 sr-no ledger-name ledger-group is-gst-applicable applicable-from-date gst-rate hsn-code taxability nature-of-transaction type-of-supply type-of-duty tax-type tax-rate
2 1 Inter-State Sales @ 18% [GST Test] Sales Accounts Applicable 01/07/2025 18 84713010 Taxable Interstate Sales - Taxable Goods
3 2 Local Purchase @ 12% [GST Test] Purchase Accounts Applicable 01/07/2025 12 84713010 Taxable Local Purchase-Taxable Goods
4 3 CGST @ 9% [GST Test] Duties & Taxes GST Central Tax 9
5 4 SGST @ 9% [GST Test] Duties & Taxes GST State Tax 9
6 5 IGST @ 18% [GST Test] Duties & Taxes GST Integrated Tax 18

Step 2 — Understand the XML tags

The XML tags file is explained below in two parts, after the skeleton. The two parts explain the tags that go inside the <MASTER> element. The complete file is available to download in Step 3.

The SKELETON

<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
    <MASTER TYPE="LEDGER">
        <NAME.LIST>
            <!-- Ledger name -->
            <NAME COLUMNREFERENCE="B"/>
        </NAME.LIST>
        <!-- Group: Sales Accounts / Purchase Accounts / Duties & Taxes -->
        <PARENT COLUMNREFERENCE="C"/>

        <!-- Part 1: Duties & Taxes ledger tags -->

        <!-- Part 2: GST and HSN details -->
        <GSTDETAILS.LIST>
            <!-- GST Tax rate details go here. Refer Part 2a -->
        </GSTDETAILS.LIST>

        <HSNDETAILS.LIST>
            <!-- HSN Code details go here. Refer Part 2b -->
        </HSNDETAILS.LIST>
    </MASTER>
</XMLTAGS>

Part 1 — Tags for Duties & Taxes ledgers (CGST, SGST, IGST)

        <!-- GST or Others -->
        <TAXTYPE COLUMNREFERENCE="K"/>
        <!-- Central Tax or State Tax or Integrated Tax -->
        <GSTDUTYHEAD COLUMNREFERENCE="L"/>
        <!-- GST Tax Rate -->
        <RATEOFTAXCALCULATION COLUMNREFERENCE="M"/>

Part 2 — Tags for Sales and Purchase ledgers

2a. GST details:

<GSTDETAILS.LIST SKIP="...">
    <APPLICABLEFROM COLUMNREFERENCE="E"/>
    <TAXABILITY COLUMNREFERENCE="H"/>
    <GSTNATUREOFTRANSACTION COLUMNREFERENCE="I"/>
    <STATEWISEDETAILS.LIST>
        <RATEDETAILS.LIST>  <!-- Central Tax: =Round(F#/2,2) -->
        <RATEDETAILS.LIST>  <!-- State Tax: =Round(F#/2,2) -->
        <RATEDETAILS.LIST>  <!-- Integrated Tax: Column F -->
    </STATEWISEDETAILS.LIST>
</GSTDETAILS.LIST>

2b. HSN details:

Step 3 — Download the CSV and XML file

Download the sample CSV file (ledger-with-gst-tax-rates.csv) and the matching XML tags file (ledger-with-gst-tax-rates-xml-tags.xml) from the Sample files box at the bottom of this page. The XML file contains the complete tags, including the full contents of the GSTDETAILS.LIST and HSNDETAILS.LIST blocks, which are shown above in short form.

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. One XML tags file serves both kinds of ledger. The blank cells and the two SKIP formulas decide which tags apply to each row.
  2. Every tag refers to a column by its letter, so if you add, remove or reorder columns in your Excel sheet, update the letters in the XML (including the ones inside formulas, such as D# and F#).
Sample files: Download the sample Excel sheet and matching XML tags file for this exercise. Download ledger-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