Turn Tally "Dr" and "Cr" balances into signed numbers you can sum
In this article (6 sections)
Related programme: the Data Analytics with Generative AI course (3–4 months; ₹90,000 (inclusive of GST) · EMI from ₹4,000/month).
To make Tally balances usable in formulas, convert each closing balance into one signed number: debit balances positive, credit balances negative. Strip the Indian thousands separators, read the Dr or Cr suffix, and apply the sign. Once every ledger is signed, the trial balance must net to exactly zero, which is the first test to run before anything goes into a report. Show revenue and liabilities as positive only at the presentation step, using a mapping table.
Why "4,35,600.00 Dr" is not a number
When a trial balance or group summary is exported to Excel, or copied from the screen, the closing balance often lands as text: an amount with Indian comma grouping, some spaces, and a two-letter side marker. Excel will not sum it, and a naive clean-up that deletes the letters leaves every balance positive, so the capital account and the debtors add together instead of offsetting. Reports built this way are wrong in a way that looks plausible.
One rule, applied in code
The parser below accepts one format only: digits and commas, an optional decimal part, then Dr or Cr. Anything else raises an error with the offending text in the message. Refusing to guess matters more here than handling every layout, because a balance read with the wrong sign is worse than a balance not read at all. Every ledger, group and amount is synthetic.
import re
from decimal import Decimal
import pandas as pd
# Synthetic trial balance as it often arrives from a Tally export pasted into Excel:
# one text column holding amount and side together.
tb = pd.DataFrame({
"ledger": ["Capital Account", "Sundry Debtors", "Sundry Creditors", "HDFC Bank A/c",
"Cash-in-hand", "Sales Accounts", "Purchase Accounts", "Output CGST",
"Output SGST", "Rent", "Office Equipment"],
"group": ["Capital Account", "Current Assets", "Current Liabilities", "Bank Accounts",
"Cash-in-Hand", "Sales Accounts", "Purchase Accounts", "Duties & Taxes",
"Duties & Taxes", "Indirect Expenses", "Fixed Assets"],
"closing": ["10,00,000.00 Cr", "4,35,600.00 Dr", "2,18,400.00 Cr", "3,12,750.00 Dr",
"18,250.00 Dr", "24,60,000.00 Cr", "17,85,000.00 Dr", "2,21,400.00 Cr",
"2,21,400.00 Cr", " 3,60,000.00 Dr", "12,09,600.00 Dr"],
})
PATTERN = re.compile(r"^\s*([\d,]+(?:\.\d+)?)\s*(Dr|Cr)?\s*$", re.I)
def signed(text: str) -> Decimal:
"""'4,35,600.00 Dr' -> 435600.00 ; '2,18,400.00 Cr' -> -218400.00 (Dr positive)."""
m = PATTERN.match(text)
if not m:
raise ValueError(f"cannot read balance: {text!r}")
number, side = m.groups()
value = Decimal(number.replace(",", ""))
if side is None:
raise ValueError(f"no Dr/Cr side on {text!r}; do not guess")
return -value if side.lower() == "cr" else value
tb["amount"] = tb["closing"].map(signed)
tb["side"] = tb["amount"].map(lambda v: "Dr" if v > 0 else "Cr" if v < 0 else "")
print(tb[["ledger", "closing", "amount"]].to_string(index=False))
total = tb["amount"].sum()
print("\nnet of all balances:", total)
assert total == 0, "trial balance does not tie: find the difference before reporting"
# The same numbers, split back into the two-column layout a reviewer expects.
dr = tb.loc[tb["amount"] > 0, "amount"].sum()
cr = -tb.loc[tb["amount"] < 0, "amount"].sum()
print("debit total:", dr, "| credit total:", cr)
assert dr == cr == Decimal("4121200.00")Eleven ledgers are read, the net of all balances is exactly 0.00, and the debit and credit totals both come to ₹41,21,200.00. That is the same figure a trial balance printout would show at the foot of each column, and it is the number to compare with Tally before going further.
If the net is not zero, stop. The usual causes are a ledger missed in the copy, a group total pasted alongside its own ledgers (double counting), or a line whose side marker was lost. Look at the difference: a figure equal to one ledger's balance points to a missing row; a figure equal to twice a balance points to a wrong sign.
From signed balances to report sections
Tally groups ledgers under groups such as Sales Accounts, Current Assets or Duties & Taxes. A management report wants its own sections, and it wants revenue shown as a positive number even though it is a credit. Keep that translation in a small mapping table rather than in formulas scattered across the report, and record the sign to apply for display. The SQL below uses SQLite and the same synthetic balances.
-- Synthetic signed balances (Dr positive, Cr negative), as produced by the Python step.
CREATE TABLE tb (ledger TEXT, grp TEXT, amount NUMERIC);
INSERT INTO tb VALUES
('Capital Account','Capital Account',-1000000), ('Sundry Debtors','Current Assets',435600),
('Sundry Creditors','Current Liabilities',-218400), ('HDFC Bank A/c','Bank Accounts',312750),
('Cash-in-hand','Cash-in-Hand',18250), ('Sales Accounts','Sales Accounts',-2460000),
('Purchase Accounts','Purchase Accounts',1785000), ('Output CGST','Duties & Taxes',-221400),
('Output SGST','Duties & Taxes',-221400), ('Rent','Indirect Expenses',360000),
('Office Equipment','Fixed Assets',1209600);
-- Your own mapping of groups to report sections, agreed once with accounts.
CREATE TABLE grp_map (grp TEXT, section TEXT, sign_for_report INTEGER);
INSERT INTO grp_map VALUES
('Sales Accounts','Revenue',-1), ('Purchase Accounts','Direct costs',1),
('Indirect Expenses','Overheads',1), ('Capital Account','Equity',-1),
('Current Liabilities','Liabilities',-1), ('Duties & Taxes','Liabilities',-1),
('Current Assets','Assets',1), ('Bank Accounts','Assets',1),
('Cash-in-Hand','Assets',1), ('Fixed Assets','Assets',1);
SELECT m.section,
SUM(t.amount) AS signed_total,
SUM(t.amount) * m.sign_for_report AS shown_in_report,
CASE WHEN SUM(t.amount) >= 0 THEN 'Dr' ELSE 'Cr' END AS side,
(SELECT COUNT(*) FROM tb x LEFT JOIN grp_map g ON g.grp = x.grp
WHERE g.grp IS NULL) AS unmapped_ledgers
FROM tb t JOIN grp_map m ON m.grp = t.grp
GROUP BY m.section, m.sign_for_report
ORDER BY m.section;Six sections come back. Revenue is ₹24,60,000 with a signed total of −24,60,000 and a Cr side; assets are ₹19,76,200 Dr. The last column counts ledgers whose group has no row in the mapping table. It is zero here; in a live file it becomes non-zero the day someone creates a new group, and a report that silently drops that group's ledgers would still look complete. Put that count on the report's check panel.
One LET formula for the sign
With the text balance in C2, a single formula produces the signed number:
=LET(t, TRIM(C2), side, UPPER(RIGHT(t, 2)),
v, VALUE(SUBSTITUTE(TRIM(LEFT(t, LEN(t) - 2)), ",", "")),
IF(side = "DR", v, IF(side = "CR", -v, NA())))NA() for a missing side is deliberate: it shows as an error, and any total that includes it also becomes an error, so the problem cannot hide inside a sum. Below the column, =SUM(D2:D200) should be exactly zero. If your file has separate Debit and Credit columns, use =N(B2)-N(C2) instead, and add =COUNTIFS(B2:B200,"<>",C2:C200,"<>") to count rows with both filled.
Conventions worth writing down
| Decision | Recommended choice | Reason |
|---|---|---|
| Sign convention in data | Debit positive, credit negative | Balances sum to zero; one test proves completeness |
| Sign in reports | Set per section in the mapping table | Presentation changes without touching the data |
| Rounding | Keep paise in data, round at display | Avoids a trial balance that is off by a rupee |
| Unknown formats | Raise an error | A silent wrong sign is harder to find than a failed run |
Write these four lines at the top of the workbook or script. Someone else will refresh this report next quarter, and a debit-positive file handed to a colleague who assumes credit-positive is how a profit turns into a loss on a slide.
Reports that depend on this step
Signed balances are the starting point for most finance analysis: a monthly MIS pack assembled from ERP dumps and a budget-versus-actual variance for the April–March year both assume them. If the source is Tally's day book rather than a trial balance, clean the voucher-level export first as described in cleaning a Tally day book export in pandas.
Reading accounting exports correctly, and building the checks that prove a figure is complete, is practised on synthetic finance data in our Data Analyst course.
Frequently asked questions
Should debit be positive or credit be positive?
Either works if the whole team uses one convention. Debit-positive is the common choice for analysis because the balances of all ledgers then sum to zero. Flip the sign only at the last step, when showing revenue or liabilities as positive figures in a report.
What if my export has separate Debit and Credit columns instead?
Then the signed amount is simply Debit minus Credit for each row, with blanks treated as zero. Check that no row has values in both columns, which usually means an opening balance and a movement were merged.
Pankit Kumar has 10 years in Data Science & AI, building and shipping production systems in regulated pharma and clinical environments. He is a freelance trainer at Boston Institute of Analytics, AnalytixLabs and Scaler, and has taught this material to thousands of working professionals.
This article is part of our Data Analytics with Generative AI programme — 3–4 months. The full analyst stack — Excel, SQL, Power BI and Python pipelines — then a generative-AI layer you can prove is right.
Explore Data Analytics with Generative AI