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>
CELLREFERENCE="A1"makes Sr No (Column A) the key field. Here each row is imported as one ledger.MASTER TYPE="LEDGER"tells udiMagic to create Ledger masters in Tally.NAMEis the ledger name (Column B).PARENTis the group the ledger is created under (Column C).- The downloaded file also has a few fixed flags (such as
ISBILLWISEON) and theGSTAPPLICABLEtag. They are not shown here; leave them as they are.
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"/>
TAXTYPE,GSTDUTYHEADandRATEOFTAXCALCULATIONare to be set for Duties and Taxes ledgers.- These tags sit directly under the ledger, not inside
GSTDETAILS.LIST.
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>
- The
GSTDETAILS.LISTblock carries the GST details of the Sales and Purchase ledgers: the date from which they apply (Column E), the taxability (Column H), the nature of transaction (Column I) and the GST rate (Column F). - The GST rate is set on three
RATEDETAILS.LISTblocks inside it. The Central Tax and State Tax rates are half of Column F, calculated with the formula=Round(F#/2,2)(for example, 18 becomes 9). The Integrated Tax rate is Column F as it is, set withCOLUMNREFERENCE="F". Tally uses the Central and State rates for intra-state and the Integrated rate for inter-state transactions. - The block has a
SKIPformula that leaves it out when Columns D, E, F, H and I are all blank. That is the case for the duty ledgers, so they get no empty GST block.
2b. HSN details:
- The
HSNDETAILS.LISTblock carries the HSN code (Column G). - Like the GST details, it has a
SKIPformula that leaves it out when Column G is blank, so the duty ledgers get no empty HSN block.
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
- Start Tally Prime and open the company you want to import into.
- Minimize Tally (leave it running).
- Start udiMagic and select
Excel to Tally. - Select option
Import data into Tally. - 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.
- Follow the wizard to complete the import.
- One XML tags file serves both kinds of ledger. The blank cells and the two
SKIPformulas decide which tags apply to each row. - 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#andF#).
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