Exercise 17

Import Ledger Masters with Opening Balances

Published 20 Oct 2026

---

This exercise teaches you how to import Ledger masters with their opening balances and bill-by-bill details from Excel into Tally Prime. You will use the FORMULA attribute to turn the Debit / Credit columns into a signed opening balance, the SCROLL attribute to add one bill for every row that shares the same Sr No, and the SKIP attribute to ignore ledgers that have no bills. You will also see the OLDNAME tag, which renames existing ledgers.

Step 1 — Prepare the Excel sheet

Create an Excel sheet with one row per ledger. Column A (Sr No) is the key field: when a ledger has more than one bill, add one more row for every extra bill, repeat the same Sr No in Column A and leave the ledger columns (B to H) blank. Ledgers that are not maintained bill-by-bill need only one row, with Columns I to M blank.

Step 2 — Write the XML tags in a separate file

Open Notepad (or any plain-text editor) and write the following XML tags:

<!--
  Column A = Sr No (key field)       Column B = Ledger Name
  Column C = Old Name                Column D = Ledger Alias
  Column E = Parent Group            Column F = Opening Balance Debit
  Column G = Opening Balance Credit  Column H = Maintain Bill By Bill
  Column I = Bill Name               Column J = Bill Date
  Column K = Bill Due Date           Column L = Bill Debit Amt
  Column M = Bill Credit Amt
-->
<!-- CELLREFERENCE="A1" declares Column A (Sr No) as the key field. Rows with the same Sr No belong to one ledger. -->
<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
    <!-- Create / alter LEDGER Masters -->
    <MASTER TYPE="LEDGER">
        <!--
          Old Name (Column C): if specified, Tally looks up the Old Name
          and replaces it with the Name value (Column B).

          Note:
          Old Name is udiMagic supported tag. It is used to update existing masters, and is ignored when creating new ledgers.
        -->
        <OLDNAME COLUMNREFERENCE="C"/>
        <NAME.LIST>
            <!-- Ledger name -->
            <NAME COLUMNREFERENCE="B"/>
            <!-- Ledger alias -->
            <NAME COLUMNREFERENCE="D"/>
        </NAME.LIST>
        <!-- Group under which the ledger is created -->
        <PARENT COLUMNREFERENCE="E"/>
        <!-- Maintain balances bill-by-bill: Yes / No -->
        <ISBILLWISEON COLUMNREFERENCE="H"/>
        <AFFECTSSTOCK>No</AFFECTSSTOCK>
        <ISCOSTCENTRESON>No</ISCOSTCENTRESON>

        <!--
          Ledger opening balance.
          Tally stores a Debit balance as a negative amount and a Credit
          balance as a positive amount. So: if Debit (Column F) is greater
          than 0, it is multiplied by -1, else the Credit (Column G) is used.
        -->
        <OPENINGBALANCE FORMULA="=If(Round(F#,2)>0,Round(F#,2)*-1,Round(G#,2))"/>

        <!--
          Bill-by-bill opening balances.
          SCROLL="YES" keeps adding one bill for every following row that
          carries the same Sr No (Column A).
          SKIP ignores the bill when the Bill Name (Column I) is blank,
          e.g. ledgers that are not maintained bill-by-bill.
        -->
        <BILLALLOCATIONS.LIST SCROLL="YES" SKIP="=Len(trim(I#))=0">
            <!-- Bill name (reference) -->
            <NAME COLUMNREFERENCE="I"/>
            <!-- Bill date -->
            <BILLDATE COLUMNREFERENCE="J"/>
            <!-- Bill due date -->
            <BILLCREDITPERIOD COLUMNREFERENCE="K"/>
            <ISADVANCE>No</ISADVANCE>
            <!-- Bill amount: Debit (Column L) is negative, Credit (Column M) is positive -->
            <OPENINGBALANCE FORMULA="=If(Round(L#,2)>0,Round(L#,2)*-1,Round(M#,2))"/>
        </BILLALLOCATIONS.LIST>
    </MASTER>
</XMLTAGS>

Step 3 — Save the file

Save it as ledger-master-with-opening-balances-xml-tags.xml. In the Save as type box, choose All Files — otherwise Notepad appends .txt and you'll end up with ledger-master-with-opening-balances-xml-tags.xml.txt instead.

Step 4 — 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 Masters.
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.

---