shinchan
unread,Aug 23, 2025, 10:24:37 AMAug 23Sign in to reply to author
Sign in to forward
You do not have permission to delete messages in this group
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to openpyxl-users
import pandas as pd
# Journal entries data from user in structured form
entries = [
(1, "Cash", "Dr", 22200), (1, "Capital", "Cr", 22200),
(2, "Cash", "Dr", 1500), (2, "Bank", "Cr", 1500),
(3, "Cash", "Dr", 2000), (3, "Income Tax Refund", "Cr", 2000),
(4, "Simla", "Dr", 2200), (4, "Cash", "Cr", 2200),
(5, "Cash", "Dr", 2000), (5, "Income Tax Refund", "Cr", 2000),
(6, "Rent", "Dr", 400), (6, "Cash", "Cr", 400),
(7, "Wealth Tax", "Dr", 1000), (7, "Cash", "Cr", 1000),
(8, "Rent", "Dr", 300), (8, "Cash", "Cr", 300),
(9, "Electricity Bill", "Dr", 400), (9, "Cash", "Cr", 400),
(10, "Repairing Exp.", "Dr", 100), (10, "Bank", "Cr", 100),
(11, "Rent", "Dr", 200), (11, "Cash", "Cr", 200),
(12, "Rent", "Dr", 1000), (12, "Cash", "Cr", 1000),
(13, "Rent", "Dr", 700), (13, "Cash", "Cr", 700),
(14, "Patna", "Dr", 2000), (14, "Cash", "Cr", 2000),
(15, "Wealth Tax", "Dr", 100), (15, "Cash", "Cr", 100),
(16, "Railways Rent", "Dr", 1000), (16, "Cash", "Cr", 1000),
(17, "Rent", "Dr", 300), (17, "Cash", "Cr", 300),
(18, "Madras", "Dr", 700), (18, "Cash", "Cr", 700),
(19, "Bombay", "Dr", 850), (19, "Cash", "Cr", 850),
(20, "Income Tax", "Dr", 300), (20, "Cash", "Cr", 300),
(21, "Cash", "Dr", 1200), (21, "Rent Received", "Cr", 1200),
(22, "Electricity", "Dr", 350), (22, "Cash", "Cr", 350),
(23, "Repairs", "Dr", 400), (23, "Cash", "Cr", 400),
(24, "Rent", "Dr", 800), (24, "Cash", "Cr", 800),
(25, "Water Tax", "Dr", 100), (25, "Cash", "Cr", 100),
(26, "Cash", "Dr", 1200), (26, "Rent", "Cr", 1200),
(27, "Water Works", "Dr", 500), (27, "Cash", "Cr", 500),
(28, "Rent", "Dr", 300), (28, "Cash", "Cr", 300),
(29, "Rent", "Dr", 700), (29, "Cash", "Cr", 700),
(30, "Tent Rent", "Dr", 600), (30, "Cash", "Cr", 600),
(31, "Rent", "Dr", 200), (31, "Cash", "Cr", 200),
(32, "Railway Rent", "Dr", 100), (32, "Cash", "Cr", 100),
(33, "Rent", "Dr", 700), (33, "Cash", "Cr", 700),
(34, "Cash", "Dr", 2000), (34, "Income Tax Refund", "Cr", 2000),
(35, "Rent", "Dr", 1500), (35, "Cash", "Cr", 1500),
(36, "Rent", "Dr", 400), (36, "Cash", "Cr", 400),
(37, "Rent", "Dr", 700), (37, "Cash", "Cr", 700),
(38, "Rent", "Dr", 200), (38, "Cash", "Cr", 200),
(39, "Cash", "Dr", 200), (39, "Rent", "Cr", 200),
(40, "Cash", "Dr", 1200), (40, "Rent", "Cr", 1200),
(41, "Rent", "Dr", 300), (41, "Cash", "Cr", 300),
(42, "Rent", "Dr", 1500), (42, "Cash", "Cr", 1500),
(43, "Rent", "Dr", 800), (43, "Cash", "Cr", 800),
(44, "Rent", "Dr", 200), (44, "Cash", "Cr", 200),
(45, "Cash", "Dr", 900), (45, "Rent", "Cr", 900),
(46, "Water Works", "Dr", 1000), (46, "Cash", "Cr", 1000),
(47, "Rent", "Dr", 100), (47, "Cash", "Cr", 100),
(48, "Electricity", "Dr", 400), (48, "Cash", "Cr", 400),
(49, "Rent", "Dr", 300), (49, "Cash", "Cr", 300),
(50, "Cash", "Dr", 1200), (50, "Rent Received", "Cr", 1200),
]
# Convert into DataFrame
df = pd.DataFrame(entries, columns=["Entry No", "Account", "Type", "Amount"])
# Ledger preparation: group by Account and Dr/Cr
ledger = df.groupby(["Account", "Type"])["Amount"].sum().unstack(fill_value=0)
ledger["Balance"] = ledger["Dr"] - ledger["Cr"]
ledger = ledger.reset_index()
# Trial Balance preparation
trial_balance = ledger[["Account", "Dr", "Cr"]]
import caas_jupyter_tools
caas_jupyter_tools.display_dataframe_to_user("Ledger Accounts", ledger)
caas_jupyter_tools.display_dataframe_to_user("Trial Balance", trial_balance)