Objective
Create Trial_Balance_Validation_Report.csv and confirm that the synthetic trial balance is ready for financial statements and ratios.
Production / Cybersecurity Warning: This exercise uses synthetic data only. Do not run scripts in production, real folders, corporate databases, or accounting environments without authorization, backups, testing, least-privilege permissions, change-control approval, and compliance with cybersecurity protocols.
Business Scenario
Before building the income statement, balance sheet, cash flow indicators, and ratios, we must validate the foundation. In finance BI, bad accounting data creates bad ratios.
Dim_CompanyDim_PeriodDim_AccountFact_Trial_BalanceValidation Report
Step-by-Step Practice
Step 1 — Import, load, validate, and export
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 4 - Validate the Trial Balance
# ============================================================
# ------------------------------------------------------------
# Step 1 - Set project folder
# ------------------------------------------------------------
base_path = Path("financial_ratios_bi_training")
if not base_path.exists():
raise FileNotFoundError(
"The project folder does not exist. Please run Topic 1 first."
)
trial_balance_path = base_path / "Fact_Trial_Balance.csv"
if not trial_balance_path.exists():
raise FileNotFoundError(
"Fact_Trial_Balance.csv was not found. Please run Topic 3 first."
)
print(f"Project folder found: {base_path.resolve()}")
# ------------------------------------------------------------
# Step 2 - Load trial balance
# ------------------------------------------------------------
trial_balance = pd.read_csv(trial_balance_path)
print("Fact_Trial_Balance loaded:", len(trial_balance), "rows")
# ------------------------------------------------------------
# Step 3 - Overall debit / credit validation
# ------------------------------------------------------------
total_debit = round(trial_balance["Debit"].sum(), 2)
total_credit = round(trial_balance["Credit"].sum(), 2)
overall_difference = round(total_debit - total_credit, 2)
if overall_difference == 0:
overall_status = "PASSED"
else:
overall_status = "FAILED"
overall_validation = pd.DataFrame([
{
"ValidationLevel": "Overall Trial Balance",
"CompanyID": "ALL",
"PeriodID": "ALL",
"TotalDebit": total_debit,
"TotalCredit": total_credit,
"Difference": overall_difference,
"Status": overall_status,
"ValidationMessage": "Total debits equal total credits."
if overall_status == "PASSED"
else "Total debits do not equal total credits."
}
])
# ------------------------------------------------------------
# Step 4 - Company / Period validation
# ------------------------------------------------------------
company_period_validation = (
trial_balance
.groupby(["CompanyID", "PeriodID"])
.agg(
TotalDebit=("Debit", "sum"),
TotalCredit=("Credit", "sum")
)
.reset_index()
)
company_period_validation["TotalDebit"] = company_period_validation["TotalDebit"].round(2)
company_period_validation["TotalCredit"] = company_period_validation["TotalCredit"].round(2)
company_period_validation["Difference"] = (
company_period_validation["TotalDebit"]
- company_period_validation["TotalCredit"]
).round(2)
company_period_validation["Status"] = company_period_validation["Difference"].apply(
lambda x: "PASSED" if x == 0 else "FAILED"
)
company_period_validation["ValidationLevel"] = "Company / Period"
company_period_validation["ValidationMessage"] = company_period_validation["Status"].apply(
lambda x: "Company-period trial balance is balanced."
if x == "PASSED"
else "Company-period trial balance is not balanced."
)
company_period_validation = company_period_validation[
[
"ValidationLevel",
"CompanyID",
"PeriodID",
"TotalDebit",
"TotalCredit",
"Difference",
"Status",
"ValidationMessage"
]
]
# ------------------------------------------------------------
# Step 5 - Financial Statement validation
# ------------------------------------------------------------
statement_validation = (
trial_balance
.groupby("FinancialStatement")
.agg(
TotalDebit=("Debit", "sum"),
TotalCredit=("Credit", "sum"),
NumberOfRows=("AccountNumber", "count")
)
.reset_index()
)
statement_validation["TotalDebit"] = statement_validation["TotalDebit"].round(2)
statement_validation["TotalCredit"] = statement_validation["TotalCredit"].round(2)
statement_validation["Difference"] = (
statement_validation["TotalDebit"]
- statement_validation["TotalCredit"]
).round(2)
# This is informational only. Individual statements do not always balance by themselves.
statement_validation["Status"] = "INFO"
statement_validation["ValidationMessage"] = (
"Informational check by financial statement. "
"Individual statements are not required to balance independently."
)
# ------------------------------------------------------------
# Step 6 - Account-level validation
# ------------------------------------------------------------
account_validation = (
trial_balance
.groupby(
[
"AccountNumber",
"AccountName",
"AccountType",
"FinancialStatement",
"NormalBalance"
]
)
.agg(
TotalDebit=("Debit", "sum"),
TotalCredit=("Credit", "sum"),
NumberOfRows=("AccountNumber", "count")
)
.reset_index()
)
account_validation["TotalDebit"] = account_validation["TotalDebit"].round(2)
account_validation["TotalCredit"] = account_validation["TotalCredit"].round(2)
account_validation["NetBalance"] = (
account_validation["TotalDebit"]
- account_validation["TotalCredit"]
).round(2)
account_validation["ValidationMessage"] = "Account-level summary for review."
# ------------------------------------------------------------
# Step 7 - Detect possible data quality issues
# ------------------------------------------------------------
issues = []
# Missing values
missing_values = trial_balance.isna().sum()
for column_name, missing_count in missing_values.items():
if missing_count > 0:
issues.append({
"IssueType": "Missing Values",
"ColumnName": column_name,
"IssueCount": int(missing_count),
"Severity": "High",
"Recommendation": "Review missing values before using the trial balance."
})
# Negative debit or credit values
negative_debits = trial_balance[trial_balance["Debit"] < 0]
negative_credits = trial_balance[trial_balance["Credit"] < 0]
if len(negative_debits) > 0:
issues.append({
"IssueType": "Negative Debit",
"ColumnName": "Debit",
"IssueCount": len(negative_debits),
"Severity": "High",
"Recommendation": "Debit values should not be negative in this training format."
})
if len(negative_credits) > 0:
issues.append({
"IssueType": "Negative Credit",
"ColumnName": "Credit",
"IssueCount": len(negative_credits),
"Severity": "High",
"Recommendation": "Credit values should not be negative in this training format."
})
# Duplicate account-period rows
duplicate_check = trial_balance.duplicated(
subset=["CompanyID", "PeriodID", "AccountNumber"],
keep=False
)
duplicate_rows = trial_balance[duplicate_check]
if len(duplicate_rows) > 0:
issues.append({
"IssueType": "Duplicate Company-Period-Account",
"ColumnName": "CompanyID / PeriodID / AccountNumber",
"IssueCount": len(duplicate_rows),
"Severity": "High",
"Recommendation": "Each company-period-account combination should appear once."
})
# Invalid normal balance values
valid_normal_balances = ["Debit", "Credit"]
invalid_normal_balance = trial_balance[
~trial_balance["NormalBalance"].isin(valid_normal_balances)
]
if len(invalid_normal_balance) > 0:
issues.append({
"IssueType": "Invalid Normal Balance",
"ColumnName": "NormalBalance",
"IssueCount": len(invalid_normal_balance),
"Severity": "Medium",
"Recommendation": "NormalBalance should be Debit or Credit for trial balance accounts."
})
if issues:
data_quality_issues = pd.DataFrame(issues)
else:
data_quality_issues = pd.DataFrame([
{
"IssueType": "No Issues Found",
"ColumnName": "All",
"IssueCount": 0,
"Severity": "None",
"Recommendation": "No major trial balance data quality issues detected."
}
])
# ------------------------------------------------------------
# Step 8 - Create final validation report
# ------------------------------------------------------------
validation_report = pd.concat(
[
overall_validation,
company_period_validation
],
ignore_index=True
)
validation_report.to_csv(
base_path / "Trial_Balance_Validation_Report.csv",
index=False
)
statement_validation.to_csv(
base_path / "Trial_Balance_Statement_Summary.csv",
index=False
)
account_validation.to_csv(
base_path / "Trial_Balance_Account_Summary.csv",
index=False
)
data_quality_issues.to_csv(
base_path / "Trial_Balance_Data_Quality_Issues.csv",
index=False
)
# ------------------------------------------------------------
# Step 9 - Print validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Total Debit :", total_debit)
print("Total Credit:", total_credit)
print("Difference :", overall_difference)
print("Overall Status:", overall_status)
print()
print("==============================")
print("COMPANY / PERIOD SUMMARY")
print("==============================")
total_company_periods = len(company_period_validation)
passed_company_periods = len(
company_period_validation[company_period_validation["Status"] == "PASSED"]
)
failed_company_periods = len(
company_period_validation[company_period_validation["Status"] == "FAILED"]
)
print("Total company-periods :", total_company_periods)
print("Passed company-periods:", passed_company_periods)
print("Failed company-periods:", failed_company_periods)
print()
print("Maximum absolute difference:")
print(company_period_validation["Difference"].abs().max())
print()
print("==============================")
print("FINANCIAL STATEMENT SUMMARY")
print("==============================")
print(statement_validation)
print()
print("==============================")
print("DATA QUALITY ISSUES")
print("==============================")
print(data_quality_issues)
print()
print("==============================")
print("FILES CREATED")
print("==============================")
created_files = [
"Trial_Balance_Validation_Report.csv",
"Trial_Balance_Statement_Summary.csv",
"Trial_Balance_Account_Summary.csv",
"Trial_Balance_Data_Quality_Issues.csv"
]
for file_name in created_files:
file_path = base_path / file_name
if file_path.exists():
print("-", file_name)
print()
print("Topic 4 completed successfully.")Expected Output
The script creates:
financial_ratios_bi_training/Trial_Balance_Validation_Report.csvValidation
The report checks: Total Debits = Total Credits and Assets = Liabilities + Equity.
Business Interpretation
This topic protects the BI model. If the trial balance is not balanced, liquidity, profitability, debt, cash flow, and valuation ratios should not be trusted.
Final Result
After Topic 4, the synthetic financial dataset has a formal validation layer. Topic 5 will map accounts to financial statements.