Learning by Doing Series

Financial Ratios Analysis in BI

Topic 2 — Create the Chart of Accounts. Build Dim_Account.csv with account classifications for trial balance, financial statements, ratios, and Power BI.

Topic 2 — Create the Chart of Accounts

This topic creates Dim_Account.csv, the accounting language of the entire training. It defines assets, liabilities, equity, revenue, expenses, cash flow items, and market data items.

Dim_CompanyDim_PeriodDim_AccountFact_Trial_Balance

Production / Cybersecurity Warning

This exercise uses synthetic data and must be executed only in a practice environment. Do not run scripts in production, real folders, corporate databases, or accounting systems without formal authorization.

Before automating any real financial or accounting process, validate backups, permissions, least-privilege access, change-control approval, sandbox testing, audit requirements, and organizational cybersecurity protocols.

Business Scenario

We already have companies and fiscal periods from Topic 1. Now we need a serious Chart of Accounts so every company follows the same financial structure.

Each account is classified by Account Type, Financial Statement, Financial Category, Normal Balance, and Ratio Category.

Step-by-Step Practice

Step 1 — Import Libraries

Step 1 — Import Libraries
import pandas as pd
from pathlib import Path

Step 2 — Set the Project Folder

Step 2 — Set the Project Folder
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

print(f"Project folder ready: {base_path.resolve()}")

Step 3 — Create the Chart of Accounts

Step 3 — Create the Chart of Accounts
import pandas as pd
from pathlib import Path

base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

accounts_data = [
    # Assets
    {"AccountID":1000,"AccountNumber":"1000","AccountName":"Cash and Cash Equivalents","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Cash available for short-term obligations"},
    {"AccountID":1100,"AccountNumber":"1100","AccountName":"Accounts Receivable","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Amounts owed by customers"},
    {"AccountID":1200,"AccountNumber":"1200","AccountName":"Inventory","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Goods available for sale or production"},
    {"AccountID":1300,"AccountNumber":"1300","AccountName":"Prepaid Expenses","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Expenses paid in advance"},
    {"AccountID":1500,"AccountNumber":"1500","AccountName":"Property Plant and Equipment","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Long-term productive assets"},
    {"AccountID":1600,"AccountNumber":"1600","AccountName":"Accumulated Depreciation","AccountType":"Contra Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Credit","RatioCategory":"Operating Performance","Description":"Cumulative depreciation of fixed assets"},
    {"AccountID":1700,"AccountNumber":"1700","AccountName":"Intangible Assets","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Valuation","Description":"Non-physical assets such as software or patents"},
    # Liabilities
    {"AccountID":2000,"AccountNumber":"2000","AccountName":"Accounts Payable","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Amounts owed to suppliers"},
    {"AccountID":2100,"AccountNumber":"2100","AccountName":"Short-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due within one year"},
    {"AccountID":2200,"AccountNumber":"2200","AccountName":"Accrued Expenses","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Expenses incurred but not yet paid"},
    {"AccountID":2500,"AccountNumber":"2500","AccountName":"Long-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due after one year"},
    {"AccountID":2600,"AccountNumber":"2600","AccountName":"Deferred Tax Liability","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Future tax obligations"},
    # Equity
    {"AccountID":3000,"AccountNumber":"3000","AccountName":"Common Stock","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital contributed by shareholders"},
    {"AccountID":3100,"AccountNumber":"3100","AccountName":"Additional Paid-In Capital","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital paid above par value"},
    {"AccountID":3200,"AccountNumber":"3200","AccountName":"Retained Earnings","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Accumulated profits retained in the business"},
    # Revenue and Expenses
    {"AccountID":4000,"AccountNumber":"4000","AccountName":"Revenue","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Revenue","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Total sales or service income"},
    {"AccountID":4100,"AccountNumber":"4100","AccountName":"Other Income","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Other Income","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Income from non-core activities"},
    {"AccountID":5000,"AccountNumber":"5000","AccountName":"Cost of Goods Sold","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Cost of Revenue","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Direct cost of goods or services sold"},
    {"AccountID":6000,"AccountNumber":"6000","AccountName":"Salaries and Wages Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Employee compensation expense"},
    {"AccountID":6100,"AccountNumber":"6100","AccountName":"Rent Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Facility rental expense"},
    {"AccountID":6200,"AccountNumber":"6200","AccountName":"Utilities Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Utility costs"},
    {"AccountID":6300,"AccountNumber":"6300","AccountName":"Marketing Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Advertising and promotion costs"},
    {"AccountID":6400,"AccountNumber":"6400","AccountName":"Depreciation Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Periodic depreciation expense"},
    {"AccountID":7000,"AccountNumber":"7000","AccountName":"Interest Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Non-Operating Expenses","NormalBalance":"Debit","RatioCategory":"Debt","Description":"Cost of borrowed funds"},
    {"AccountID":7100,"AccountNumber":"7100","AccountName":"Tax Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Tax Expense","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Income tax expense"},
    # Cash Flow and Market Data
    {"AccountID":8000,"AccountNumber":"8000","AccountName":"Operating Cash Flow","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Operating Cash Flow","NormalBalance":"Debit","RatioCategory":"Cash Flow","Description":"Cash generated from operations"},
    {"AccountID":8100,"AccountNumber":"8100","AccountName":"Capital Expenditures","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Investing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash spent on long-term assets"},
    {"AccountID":8200,"AccountNumber":"8200","AccountName":"Dividends Paid","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Financing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash distributed to shareholders"},
    {"AccountID":9000,"AccountNumber":"9000","AccountName":"Share Price","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market price per share"},
    {"AccountID":9100,"AccountNumber":"9100","AccountName":"Shares Outstanding","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Number of shares outstanding"},
    {"AccountID":9200,"AccountNumber":"9200","AccountName":"Market Capitalization","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Share price times shares outstanding"},
    {"AccountID":9300,"AccountNumber":"9300","AccountName":"Enterprise Value","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market cap plus debt minus cash"}
]

Step 4 — Export Dim_Account to CSV

Step 4 — Export Dim_Account to CSV
dim_account = pd.DataFrame(accounts_data)
dim_account.to_csv(base_path / "Dim_Account.csv", index=False)

print("Dim_Account.csv created successfully.")

Step 5 — Validate the Number of Accounts

Step 5 — Validate the Number of Accounts
print("Total accounts:", len(dim_account))

dim_account.groupby("AccountType")["AccountID"].count().reset_index(name="NumberOfAccounts")

Step 6 — Validate Financial Statement Mapping

Step 6 — Validate Financial Statement Mapping
statement_summary = (
    dim_account
    .groupby("FinancialStatement")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
)

statement_summary

Step 7 — Review Accounts by Ratio Category

Step 7 — Review Accounts by Ratio Category
ratio_summary = (
    dim_account
    .groupby("RatioCategory")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
    .sort_values("RatioCategory")
)

ratio_summary

Step 8 — Preview the Final Chart of Accounts

Step 8 — Preview the Final Chart of Accounts
dim_account[["AccountNumber","AccountName","AccountType","FinancialStatement","FinancialCategory","NormalBalance","RatioCategory"]].head(15)

Step 9 — Check Created Files

Step 9 — Check Created Files
for file in base_path.glob("*.csv"):
    print(file.name)

Business Interpretation

This topic creates the accounting vocabulary of the BI model. Cash supports liquidity analysis. Debt supports debt analysis. Revenue supports profitability. PP&E supports operating performance. Share price supports valuation.

After this topic, the project folder should contain Dim_Company.csv, Dim_Period.csv, and Dim_Account.csv.

Topic 2 — Crear el Catálogo Contable

Este tópico crea Dim_Account.csv, el lenguaje contable de todo el training. Define activos, pasivos, patrimonio, ingresos, gastos, cash flow y datos de mercado.

Dim_CompanyDim_PeriodDim_AccountFact_Trial_Balance

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética y debe ejecutarse solamente en un ambiente de práctica. No ejecutes scripts en producción, carpetas reales, bases de datos corporativas o sistemas contables sin autorización formal.

Escenario de Negocio

Ya tenemos compañías y períodos fiscales del Topic 1. Ahora necesitamos un Chart of Accounts serio para que cada compañía use la misma estructura financiera.

Práctica Paso a Paso

Step 1 — Import Libraries

Step 1 — Import Libraries
import pandas as pd
from pathlib import Path

Step 2 — Set the Project Folder

Step 2 — Set the Project Folder
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

print(f"Project folder ready: {base_path.resolve()}")

Step 3 — Create the Chart of Accounts

Step 3 — Create the Chart of Accounts
import pandas as pd
from pathlib import Path

base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

accounts_data = [
    # Assets
    {"AccountID":1000,"AccountNumber":"1000","AccountName":"Cash and Cash Equivalents","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Cash available for short-term obligations"},
    {"AccountID":1100,"AccountNumber":"1100","AccountName":"Accounts Receivable","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Amounts owed by customers"},
    {"AccountID":1200,"AccountNumber":"1200","AccountName":"Inventory","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Goods available for sale or production"},
    {"AccountID":1300,"AccountNumber":"1300","AccountName":"Prepaid Expenses","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Expenses paid in advance"},
    {"AccountID":1500,"AccountNumber":"1500","AccountName":"Property Plant and Equipment","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Long-term productive assets"},
    {"AccountID":1600,"AccountNumber":"1600","AccountName":"Accumulated Depreciation","AccountType":"Contra Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Credit","RatioCategory":"Operating Performance","Description":"Cumulative depreciation of fixed assets"},
    {"AccountID":1700,"AccountNumber":"1700","AccountName":"Intangible Assets","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Valuation","Description":"Non-physical assets such as software or patents"},
    # Liabilities
    {"AccountID":2000,"AccountNumber":"2000","AccountName":"Accounts Payable","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Amounts owed to suppliers"},
    {"AccountID":2100,"AccountNumber":"2100","AccountName":"Short-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due within one year"},
    {"AccountID":2200,"AccountNumber":"2200","AccountName":"Accrued Expenses","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Expenses incurred but not yet paid"},
    {"AccountID":2500,"AccountNumber":"2500","AccountName":"Long-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due after one year"},
    {"AccountID":2600,"AccountNumber":"2600","AccountName":"Deferred Tax Liability","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Future tax obligations"},
    # Equity
    {"AccountID":3000,"AccountNumber":"3000","AccountName":"Common Stock","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital contributed by shareholders"},
    {"AccountID":3100,"AccountNumber":"3100","AccountName":"Additional Paid-In Capital","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital paid above par value"},
    {"AccountID":3200,"AccountNumber":"3200","AccountName":"Retained Earnings","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Accumulated profits retained in the business"},
    # Revenue and Expenses
    {"AccountID":4000,"AccountNumber":"4000","AccountName":"Revenue","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Revenue","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Total sales or service income"},
    {"AccountID":4100,"AccountNumber":"4100","AccountName":"Other Income","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Other Income","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Income from non-core activities"},
    {"AccountID":5000,"AccountNumber":"5000","AccountName":"Cost of Goods Sold","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Cost of Revenue","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Direct cost of goods or services sold"},
    {"AccountID":6000,"AccountNumber":"6000","AccountName":"Salaries and Wages Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Employee compensation expense"},
    {"AccountID":6100,"AccountNumber":"6100","AccountName":"Rent Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Facility rental expense"},
    {"AccountID":6200,"AccountNumber":"6200","AccountName":"Utilities Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Utility costs"},
    {"AccountID":6300,"AccountNumber":"6300","AccountName":"Marketing Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Advertising and promotion costs"},
    {"AccountID":6400,"AccountNumber":"6400","AccountName":"Depreciation Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Periodic depreciation expense"},
    {"AccountID":7000,"AccountNumber":"7000","AccountName":"Interest Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Non-Operating Expenses","NormalBalance":"Debit","RatioCategory":"Debt","Description":"Cost of borrowed funds"},
    {"AccountID":7100,"AccountNumber":"7100","AccountName":"Tax Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Tax Expense","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Income tax expense"},
    # Cash Flow and Market Data
    {"AccountID":8000,"AccountNumber":"8000","AccountName":"Operating Cash Flow","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Operating Cash Flow","NormalBalance":"Debit","RatioCategory":"Cash Flow","Description":"Cash generated from operations"},
    {"AccountID":8100,"AccountNumber":"8100","AccountName":"Capital Expenditures","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Investing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash spent on long-term assets"},
    {"AccountID":8200,"AccountNumber":"8200","AccountName":"Dividends Paid","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Financing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash distributed to shareholders"},
    {"AccountID":9000,"AccountNumber":"9000","AccountName":"Share Price","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market price per share"},
    {"AccountID":9100,"AccountNumber":"9100","AccountName":"Shares Outstanding","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Number of shares outstanding"},
    {"AccountID":9200,"AccountNumber":"9200","AccountName":"Market Capitalization","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Share price times shares outstanding"},
    {"AccountID":9300,"AccountNumber":"9300","AccountName":"Enterprise Value","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market cap plus debt minus cash"}
]

Step 4 — Export Dim_Account to CSV

Step 4 — Export Dim_Account to CSV
dim_account = pd.DataFrame(accounts_data)
dim_account.to_csv(base_path / "Dim_Account.csv", index=False)

print("Dim_Account.csv created successfully.")

Step 5 — Validate the Number of Accounts

Step 5 — Validate the Number of Accounts
print("Total accounts:", len(dim_account))

dim_account.groupby("AccountType")["AccountID"].count().reset_index(name="NumberOfAccounts")

Step 6 — Validate Financial Statement Mapping

Step 6 — Validate Financial Statement Mapping
statement_summary = (
    dim_account
    .groupby("FinancialStatement")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
)

statement_summary

Step 7 — Review Accounts by Ratio Category

Step 7 — Review Accounts by Ratio Category
ratio_summary = (
    dim_account
    .groupby("RatioCategory")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
    .sort_values("RatioCategory")
)

ratio_summary

Step 8 — Preview the Final Chart of Accounts

Step 8 — Preview the Final Chart of Accounts
dim_account[["AccountNumber","AccountName","AccountType","FinancialStatement","FinancialCategory","NormalBalance","RatioCategory"]].head(15)

Step 9 — Check Created Files

Step 9 — Check Created Files
for file in base_path.glob("*.csv"):
    print(file.name)

Interpretación de Negocio

Este tópico crea el vocabulario contable del modelo BI. Cash apoya liquidez. Debt apoya deuda. Revenue apoya rentabilidad. PP&E apoya desempeño operativo. Share Price apoya valoración.

Al final tendremos Dim_Company.csv, Dim_Period.csv y Dim_Account.csv.

Final Validated Script

This is the corrected and live-tested script for Topic 2: Create the Chart of Accounts.

Python - Final Validated Script
from pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 2 - Create the Chart of Accounts
# ============================================================

# ------------------------------------------------------------
# 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 to create Dim_Company.csv and Dim_Period.csv."
    )

print(f"Project folder found: {base_path.resolve()}")

# ------------------------------------------------------------
# Step 2 - Create Chart of Accounts
# ------------------------------------------------------------

accounts_data = [
    # Assets
    {
        "AccountID": 1000,
        "AccountNumber": "1000",
        "AccountName": "Cash and Cash Equivalents",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Liquidity",
        "Description": "Cash available for short-term obligations"
    },
    {
        "AccountID": 1100,
        "AccountNumber": "1100",
        "AccountName": "Accounts Receivable",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Liquidity",
        "Description": "Amounts owed by customers"
    },
    {
        "AccountID": 1200,
        "AccountNumber": "1200",
        "AccountName": "Inventory",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Liquidity",
        "Description": "Goods available for sale or production"
    },
    {
        "AccountID": 1300,
        "AccountNumber": "1300",
        "AccountName": "Prepaid Expenses",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Liquidity",
        "Description": "Expenses paid in advance"
    },
    {
        "AccountID": 1500,
        "AccountNumber": "1500",
        "AccountName": "Property Plant and Equipment",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Non-Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Long-term productive assets"
    },
    {
        "AccountID": 1600,
        "AccountNumber": "1600",
        "AccountName": "Accumulated Depreciation",
        "AccountType": "Contra Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Non-Current Assets",
        "NormalBalance": "Credit",
        "RatioCategory": "Operating Performance",
        "Description": "Cumulative depreciation of fixed assets"
    },
    {
        "AccountID": 1700,
        "AccountNumber": "1700",
        "AccountName": "Intangible Assets",
        "AccountType": "Asset",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Non-Current Assets",
        "NormalBalance": "Debit",
        "RatioCategory": "Valuation",
        "Description": "Non-physical assets such as software or patents"
    },

    # Liabilities
    {
        "AccountID": 2000,
        "AccountNumber": "2000",
        "AccountName": "Accounts Payable",
        "AccountType": "Liability",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Liabilities",
        "NormalBalance": "Credit",
        "RatioCategory": "Liquidity",
        "Description": "Amounts owed to suppliers"
    },
    {
        "AccountID": 2100,
        "AccountNumber": "2100",
        "AccountName": "Short-Term Debt",
        "AccountType": "Liability",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Liabilities",
        "NormalBalance": "Credit",
        "RatioCategory": "Debt",
        "Description": "Debt due within one year"
    },
    {
        "AccountID": 2200,
        "AccountNumber": "2200",
        "AccountName": "Accrued Expenses",
        "AccountType": "Liability",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Current Liabilities",
        "NormalBalance": "Credit",
        "RatioCategory": "Liquidity",
        "Description": "Expenses incurred but not yet paid"
    },
    {
        "AccountID": 2500,
        "AccountNumber": "2500",
        "AccountName": "Long-Term Debt",
        "AccountType": "Liability",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Non-Current Liabilities",
        "NormalBalance": "Credit",
        "RatioCategory": "Debt",
        "Description": "Debt due after one year"
    },
    {
        "AccountID": 2600,
        "AccountNumber": "2600",
        "AccountName": "Deferred Tax Liability",
        "AccountType": "Liability",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Non-Current Liabilities",
        "NormalBalance": "Credit",
        "RatioCategory": "Debt",
        "Description": "Future tax obligations"
    },

    # Equity
    {
        "AccountID": 3000,
        "AccountNumber": "3000",
        "AccountName": "Common Stock",
        "AccountType": "Equity",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Equity",
        "NormalBalance": "Credit",
        "RatioCategory": "Valuation",
        "Description": "Capital contributed by shareholders"
    },
    {
        "AccountID": 3100,
        "AccountNumber": "3100",
        "AccountName": "Additional Paid-In Capital",
        "AccountType": "Equity",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Equity",
        "NormalBalance": "Credit",
        "RatioCategory": "Valuation",
        "Description": "Capital paid above par value"
    },
    {
        "AccountID": 3200,
        "AccountNumber": "3200",
        "AccountName": "Retained Earnings",
        "AccountType": "Equity",
        "FinancialStatement": "Balance Sheet",
        "FinancialCategory": "Equity",
        "NormalBalance": "Credit",
        "RatioCategory": "Profitability",
        "Description": "Accumulated profits retained in the business"
    },

    # Revenue
    {
        "AccountID": 4000,
        "AccountNumber": "4000",
        "AccountName": "Revenue",
        "AccountType": "Revenue",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Revenue",
        "NormalBalance": "Credit",
        "RatioCategory": "Profitability",
        "Description": "Total sales or service income"
    },
    {
        "AccountID": 4100,
        "AccountNumber": "4100",
        "AccountName": "Other Income",
        "AccountType": "Revenue",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Other Income",
        "NormalBalance": "Credit",
        "RatioCategory": "Profitability",
        "Description": "Income from non-core activities"
    },

    # Expenses
    {
        "AccountID": 5000,
        "AccountNumber": "5000",
        "AccountName": "Cost of Goods Sold",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Cost of Revenue",
        "NormalBalance": "Debit",
        "RatioCategory": "Profitability",
        "Description": "Direct cost of goods or services sold"
    },
    {
        "AccountID": 6000,
        "AccountNumber": "6000",
        "AccountName": "Salaries and Wages Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Employee compensation expense"
    },
    {
        "AccountID": 6100,
        "AccountNumber": "6100",
        "AccountName": "Rent Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Facility rental expense"
    },
    {
        "AccountID": 6200,
        "AccountNumber": "6200",
        "AccountName": "Utilities Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Electricity, water, internet, and utility costs"
    },
    {
        "AccountID": 6300,
        "AccountNumber": "6300",
        "AccountName": "Marketing Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Advertising and promotion costs"
    },
    {
        "AccountID": 6400,
        "AccountNumber": "6400",
        "AccountName": "Depreciation Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Operating Performance",
        "Description": "Periodic depreciation expense"
    },
    {
        "AccountID": 7000,
        "AccountNumber": "7000",
        "AccountName": "Interest Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Non-Operating Expenses",
        "NormalBalance": "Debit",
        "RatioCategory": "Debt",
        "Description": "Cost of borrowed funds"
    },
    {
        "AccountID": 7100,
        "AccountNumber": "7100",
        "AccountName": "Tax Expense",
        "AccountType": "Expense",
        "FinancialStatement": "Income Statement",
        "FinancialCategory": "Tax Expense",
        "NormalBalance": "Debit",
        "RatioCategory": "Profitability",
        "Description": "Income tax expense"
    },

    # Cash Flow Items
    {
        "AccountID": 8000,
        "AccountNumber": "8000",
        "AccountName": "Operating Cash Flow",
        "AccountType": "Cash Flow Item",
        "FinancialStatement": "Cash Flow Statement",
        "FinancialCategory": "Operating Cash Flow",
        "NormalBalance": "Debit",
        "RatioCategory": "Cash Flow",
        "Description": "Cash generated from operating activities"
    },
    {
        "AccountID": 8100,
        "AccountNumber": "8100",
        "AccountName": "Capital Expenditures",
        "AccountType": "Cash Flow Item",
        "FinancialStatement": "Cash Flow Statement",
        "FinancialCategory": "Investing Cash Flow",
        "NormalBalance": "Credit",
        "RatioCategory": "Cash Flow",
        "Description": "Cash spent on long-term assets"
    },
    {
        "AccountID": 8200,
        "AccountNumber": "8200",
        "AccountName": "Dividends Paid",
        "AccountType": "Cash Flow Item",
        "FinancialStatement": "Cash Flow Statement",
        "FinancialCategory": "Financing Cash Flow",
        "NormalBalance": "Credit",
        "RatioCategory": "Cash Flow",
        "Description": "Cash distributed to shareholders"
    },

    # Market Data Items
    {
        "AccountID": 9000,
        "AccountNumber": "9000",
        "AccountName": "Share Price",
        "AccountType": "Market Data",
        "FinancialStatement": "Market Data",
        "FinancialCategory": "Market Valuation",
        "NormalBalance": "N/A",
        "RatioCategory": "Valuation",
        "Description": "Market price per share"
    },
    {
        "AccountID": 9100,
        "AccountNumber": "9100",
        "AccountName": "Shares Outstanding",
        "AccountType": "Market Data",
        "FinancialStatement": "Market Data",
        "FinancialCategory": "Market Valuation",
        "NormalBalance": "N/A",
        "RatioCategory": "Valuation",
        "Description": "Number of shares issued and outstanding"
    },
    {
        "AccountID": 9200,
        "AccountNumber": "9200",
        "AccountName": "Market Capitalization",
        "AccountType": "Market Data",
        "FinancialStatement": "Market Data",
        "FinancialCategory": "Market Valuation",
        "NormalBalance": "N/A",
        "RatioCategory": "Valuation",
        "Description": "Share price multiplied by shares outstanding"
    },
    {
        "AccountID": 9300,
        "AccountNumber": "9300",
        "AccountName": "Enterprise Value",
        "AccountType": "Market Data",
        "FinancialStatement": "Market Data",
        "FinancialCategory": "Market Valuation",
        "NormalBalance": "N/A",
        "RatioCategory": "Valuation",
        "Description": "Market capitalization plus debt minus cash"
    }
]

dim_account = pd.DataFrame(accounts_data)

# ------------------------------------------------------------
# Step 3 - Export Dim_Account
# ------------------------------------------------------------

dim_account.to_csv(base_path / "Dim_Account.csv", index=False)

print("Dim_Account.csv created successfully.")

# ------------------------------------------------------------
# Step 4 - Validate output
# ------------------------------------------------------------

print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")

print("Total accounts:", len(dim_account))

print()
print("Accounts by AccountType:")
account_type_summary = (
    dim_account
    .groupby("AccountType")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
    .sort_values("AccountType")
)
print(account_type_summary)

print()
print("Accounts by FinancialStatement:")
statement_summary = (
    dim_account
    .groupby("FinancialStatement")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
    .sort_values("FinancialStatement")
)
print(statement_summary)

print()
print("Accounts by RatioCategory:")
ratio_summary = (
    dim_account
    .groupby("RatioCategory")["AccountID"]
    .count()
    .reset_index(name="NumberOfAccounts")
    .sort_values("RatioCategory")
)
print(ratio_summary)

print()
print("Preview of Chart of Accounts:")
print(
    dim_account[
        [
            "AccountNumber",
            "AccountName",
            "AccountType",
            "FinancialStatement",
            "FinancialCategory",
            "NormalBalance",
            "RatioCategory"
        ]
    ].head(15)
)

print()
print("Files currently in project folder:")

for file in base_path.glob("*.csv"):
    print("-", file.name)

print()
print("Topic 2 completed successfully.")