Clean a Tally day book export into one row per ledger line

What the raw file looks like

Exports differ with version, the options you tick, and whether you save through Excel or straight to CSV, so treat the layout below as an example and check your own file line by line. The problems, though, show up in almost every day book export:

• three or four lines of company name, report name and period above the real header;
• a header row repeated after each page break;
• voucher details only on the first line of a voucher, with blank cells beneath;
• amounts stored as text with lakh-style commas;
• a grand total at the bottom that looks like a ledger line.

A pivot table built on this raw sheet will show a large "(blank)" date and count the grand total as a transaction. Clean the sheet first.

The cleaning script

The sample below is synthetic: an invented trader, invented parties and amounts. Save your export as CSV, then point read_csv at it with the same skiprows value your title block needs.

import io
import pandas as pd

# Synthetic export in the shape many day book exports take once saved as CSV.
raw = """Sharma Traders (Synthetic)
Day Book
1-Aug-2026 to 3-Aug-2026
Date,Particulars,Vch Type,Vch No.,Debit,Credit
1-Aug-2026,Cash,Receipt,R-101,"25,000.00",
,Gupta Enterprises,,,,"25,000.00"
1-Aug-2026,Office Rent,Payment,P-044,"40,000.00",
,TDS Payable,,,,"4,000.00"
,HDFC Bank,,,,"36,000.00"
Date,Particulars,Vch Type,Vch No.,Debit,Credit
2-Aug-2026,Verma & Sons,Sales,S-2201,"1,18,000.00",
,Sales @18%,,,,"1,00,000.00"
,Output IGST,,,,"18,000.00"
3-Aug-2026,Stationery,Journal,J-012,"1,200.00",
,Petty Cash,,,,"1,000.00"
,Grand Total,,,"1,84,200.00","1,84,000.00"
"""

df = pd.read_csv(io.StringIO(raw), skiprows=3, dtype=str)
df.columns = ["date", "ledger", "vch_type", "vch_no", "debit", "credit"]

# 1. Drop repeated headers and the total line.
df = df[df["date"].ne("Date") & df["ledger"].ne("Grand Total")].copy()

# 2. Carry voucher-level fields down to the ledger lines beneath them.
df[["date", "vch_type", "vch_no"]] = df[["date", "vch_type", "vch_no"]].ffill()

# 3. Indian-grouped amounts to numbers; blanks become 0.
for col in ("debit", "credit"):
    df[col] = pd.to_numeric(df[col].str.replace(",", "", regex=False)).fillna(0.0)
df["date"] = pd.to_datetime(df["date"], format="%d-%b-%Y")
df["amount"] = df["debit"] - df["credit"]   # signed: Dr positive, Cr negative

print(df[["date", "vch_type", "vch_no", "ledger", "amount"]].to_string(index=False))

# 4. Every voucher must balance. Key on type AND number.
balance = df.groupby(["vch_type", "vch_no"])["amount"].sum().round(2)
print("\nUnbalanced vouchers:")
print(balance[balance != 0])

The output is ten ledger lines with a date and voucher on every row, and one flagged voucher: journal J-012 is out by ₹200. In the synthetic file that is deliberate. It stands in for the real-world case where a line did not come through the export, often a round-off or a ledger filtered out by the report settings.

Three decisions hidden in those twenty lines

Why dtype=str on read. If pandas guesses types, 1,18,000.00 stays text anyway, but voucher numbers like 0045 lose their zeros and dates may be half-parsed. Reading everything as text and converting deliberately keeps you in control.

Why a single signed amount column. Two columns, debit and credit, force every later formula to handle both. One column where debit is positive and credit negative lets you sum by ledger, by voucher or by month with one groupby, and a balanced voucher sums to exactly zero. If your team prefers the opposite sign, pick it once and write it at the top of the output sheet.

Why group by voucher type and number together. Many businesses number each voucher type separately, so Receipt 12 and Payment 12 are different documents. Grouping on the number alone would merge them and could hide one imbalance behind another.

When forward-fill gives the wrong answer

Forward-fill assumes a blank cell means "same as above". That holds for ledger continuation lines, but not if the export has a genuine voucher with a missing number, or if you sorted the sheet before saving it. Two protections:

1. 1Never sort or filter the raw export before cleaning. Row order carries the voucher grouping.
2. 2After cleaning, count vouchers per type and compare with the counts shown in the accounting software for the same period. If the counts differ, something was merged.

Doing the same in Excel or Power Query

If the file has to stay in Excel, Power Query covers the same steps without code: Remove Top Rows for the title block, Use First Row as Headers, a filter removing rows where Date equals "Date" or Particulars equals "Grand Total", then select Date, Vch Type and Vch No. and choose Fill Down. For the amounts, replace "," with nothing before changing type to Decimal Number. Add a custom column [Debit] - [Credit] and a Group By on voucher type and number to get the balance check.

Whichever tool you use, keep the raw export untouched in a separate sheet or folder. When someone asks why a figure changed, the raw file is your evidence.

Where this table goes next

Once the day book is flat, you can pivot expenses by ledger and month, find the largest cash payments, or join party ledgers to a customer master. If the next file you get is a ledger or trial balance with "Dr" and "Cr" written after each balance, convert those Dr/Cr strings into signed numbers before you add anything up. Turning messy accounting exports into dependable tables is everyday work for the analysts our Data Analyst programme prepares, and a good way to test yourself is to try this script on last month's real export.