can u convert this python data inti excel sheet

45 views
Skip to first unread message

shinchan

unread,
Aug 23, 2025, 10:24:37 AMAug 23
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)
Reply all
Reply to author
Forward
0 new messages