Financial Ratios Analysis in BI

Step-by-Step Practice

Step 1 — Prepare the folder and load previous files

Step 2 — Build topic-specific calculations

Step 3 — Export the output CSV or documentation file

Step 4 — Validate rows, keys, and financial logic

Step 5 — Interpret the result as a BI analyst

Script / Instructions

Copyfrom pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 17 - SQL Server Financial Model
# ============================================================

# ------------------------------------------------------------
# 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."
    )

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

# ------------------------------------------------------------
# Step 2 - Define CSV files to be loaded into SQL Server
# ------------------------------------------------------------

csv_files = [
    "Dim_Company.csv",
    "Dim_Period.csv",
    "Dim_Account.csv",
    "Fact_Trial_Balance.csv",
    "Fact_Income_Statement.csv",
    "Fact_Balance_Sheet.csv",
    "Fact_Cash_Flow.csv",
    "Fact_Market_Data.csv",
    "Fact_Employees.csv",
    "Fact_Liquidity_Ratios.csv",
    "Fact_Profitability_Ratios.csv",
    "Fact_Debt_Ratios.csv",
    "Fact_Operating_Performance_Ratios.csv",
    "Fact_Cash_Flow_Ratios.csv",
    "Fact_Investment_Valuation_Ratios.csv",
    "Mapping_Financial_Statements.csv",
    "Data_Quality_Issues.csv",
    "Data_Quality_Issues_Detail.csv"
]

missing_files = []

for file_name in csv_files:
    if not (base_path / file_name).exists():
        missing_files.append(file_name)

if missing_files:
    print()
    print("WARNING: Missing files:")
    for file_name in missing_files:
        print("-", file_name)
    print()
    print("Some SQL scripts will still be created, but please run previous topics first.")
else:
    print("All expected CSV files were found.")

# ------------------------------------------------------------
# Step 3 - Helper functions
# ------------------------------------------------------------

def sql_type_from_pandas(dtype, column_name):
    """
    Maps pandas data types to SQL Server data types.
    Uses column names to improve numeric precision choices.
    """

    column_lower = column_name.lower()

    if "date" in column_lower:
        return "DATE"

    if pd.api.types.is_integer_dtype(dtype):
        return "INT"

    if pd.api.types.is_float_dtype(dtype):
        if (
            "ratio" in column_lower
            or "margin" in column_lower
            or "yield" in column_lower
            or "rate" in column_lower
            or "eps" in column_lower
            or "price" in column_lower
            or "value" in column_lower
            or "amount" in column_lower
            or "debit" in column_lower
            or "credit" in column_lower
            or "balance" in column_lower
            or "debt" in column_lower
            or "assets" in column_lower
            or "liabilities" in column_lower
            or "equity" in column_lower
            or "income" in column_lower
            or "revenue" in column_lower
            or "cash" in column_lower
            or "capitalization" in column_lower
        ):
            return "DECIMAL(18,4)"
        return "FLOAT"

    return "NVARCHAR(255)"


def create_table_sql(table_name, df):
    """
    Creates a SQL Server CREATE TABLE statement based on a DataFrame.
    """

    lines = []
    lines.append(f"IF OBJECT_ID('dbo.{table_name}', 'U') IS NOT NULL")
    lines.append(f"    DROP TABLE dbo.{table_name};")
    lines.append("GO")
    lines.append("")
    lines.append(f"CREATE TABLE dbo.{table_name} (")
    
    column_lines = []

    for column in df.columns:
        sql_type = sql_type_from_pandas(df[column].dtype, column)
        clean_column = column.replace(" ", "_").replace("/", "_").replace("-", "_")
        column_lines.append(f"    [{clean_column}] {sql_type} NULL")

    lines.append(",\n".join(column_lines))
    lines.append(");")
    lines.append("GO")
    lines.append("")

    return "\n".join(lines)


def bulk_insert_sql(table_name, file_name):
    """
    Creates a SQL Server BULK INSERT template.
    The user must update @BasePath to match the local CSV folder.
    """

    return f"""
PRINT 'Loading {file_name} into dbo.{table_name}';

BULK INSERT dbo.{table_name}
FROM @BasePath + '{file_name}'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    TABLOCK,
    CODEPAGE = '65001'
);
GO
"""


def table_name_from_file(file_name):
    return Path(file_name).stem


# ------------------------------------------------------------
# Step 4 - Generate SQL CREATE TABLE script
# ------------------------------------------------------------

create_tables_sections = []

create_tables_sections.append("""
/*
============================================================
Financial Ratios Analysis in BI
Topic 17 - SQL Server Financial Model
Script 1 - Create Tables
============================================================

Cybersecurity / Production Warning:
Do not run this script in production or against real financial data
without authorization, backups, testing, least-privilege permissions,
change-control approval, and compliance with organizational
cybersecurity protocols.
============================================================
*/

IF DB_ID('FinancialRatiosBI') IS NULL
BEGIN
    CREATE DATABASE FinancialRatiosBI;
END
GO

USE FinancialRatiosBI;
GO
""")

table_metadata = []

for file_name in csv_files:

    file_path = base_path / file_name

    if file_path.exists():
        df = pd.read_csv(file_path, nrows=100)
        table_name = table_name_from_file(file_name)

        create_tables_sections.append(create_table_sql(table_name, df))

        table_metadata.append({
            "FileName": file_name,
            "TableName": table_name,
            "ColumnCount": len(df.columns),
            "SampleRowsRead": len(df)
        })

create_tables_sql = "\n".join(create_tables_sections)

(base_path / "SQL_Create_Tables.sql").write_text(
    create_tables_sql,
    encoding="utf-8"
)

print("SQL_Create_Tables.sql created successfully.")

# ------------------------------------------------------------
# Step 5 - Generate SQL load data template
# ------------------------------------------------------------

load_sections = []

load_sections.append(r"""
/*
============================================================
Financial Ratios Analysis in BI
Topic 17 - SQL Server Financial Model
Script 2 - Load Data Template
============================================================

Before running:
1. Update @BasePath to the folder where your CSV files are located.
2. Make sure SQL Server has permission to read that folder.
3. Run SQL_Create_Tables.sql first.
============================================================
*/

USE FinancialRatiosBI;
GO

DECLARE @BasePath NVARCHAR(500);

-- IMPORTANT:
-- Change this path to your real local folder.
-- Example:
-- SET @BasePath = 'C:\Users\jcarb\Downloads\financial_ratios_bi_training\';

SET @BasePath = 'C:\Users\jcarb\Downloads\financial_ratios_bi_training\';
""")

for file_name in csv_files:

    file_path = base_path / file_name

    if file_path.exists():
        table_name = table_name_from_file(file_name)
        load_sections.append(bulk_insert_sql(table_name, file_name))

load_data_sql = "\n".join(load_sections)

(base_path / "SQL_Load_Data_Template.sql").write_text(
    load_data_sql,
    encoding="utf-8"
)

print("SQL_Load_Data_Template.sql created successfully.")

# ------------------------------------------------------------
# Step 6 - Generate SQL validation queries
# ------------------------------------------------------------

validation_sql = r"""
/*
============================================================
Financial Ratios Analysis in BI
Topic 17 - SQL Server Financial Model
Script 3 - Validation Queries
============================================================
*/

USE FinancialRatiosBI;
GO

-- ==========================================================
-- 1. Row counts by table
-- ==========================================================

SELECT 'Dim_Company' AS TableName, COUNT(*) AS NumberOfRows FROM dbo.Dim_Company
UNION ALL
SELECT 'Dim_Period', COUNT(*) FROM dbo.Dim_Period
UNION ALL
SELECT 'Dim_Account', COUNT(*) FROM dbo.Dim_Account
UNION ALL
SELECT 'Fact_Trial_Balance', COUNT(*) FROM dbo.Fact_Trial_Balance
UNION ALL
SELECT 'Fact_Income_Statement', COUNT(*) FROM dbo.Fact_Income_Statement
UNION ALL
SELECT 'Fact_Balance_Sheet', COUNT(*) FROM dbo.Fact_Balance_Sheet
UNION ALL
SELECT 'Fact_Cash_Flow', COUNT(*) FROM dbo.Fact_Cash_Flow
UNION ALL
SELECT 'Fact_Market_Data', COUNT(*) FROM dbo.Fact_Market_Data
UNION ALL
SELECT 'Fact_Liquidity_Ratios', COUNT(*) FROM dbo.Fact_Liquidity_Ratios
UNION ALL
SELECT 'Fact_Profitability_Ratios', COUNT(*) FROM dbo.Fact_Profitability_Ratios
UNION ALL
SELECT 'Fact_Debt_Ratios', COUNT(*) FROM dbo.Fact_Debt_Ratios
UNION ALL
SELECT 'Fact_Operating_Performance_Ratios', COUNT(*) FROM dbo.Fact_Operating_Performance_Ratios
UNION ALL
SELECT 'Fact_Cash_Flow_Ratios', COUNT(*) FROM dbo.Fact_Cash_Flow_Ratios
UNION ALL
SELECT 'Fact_Investment_Valuation_Ratios', COUNT(*) FROM dbo.Fact_Investment_Valuation_Ratios;
GO

-- ==========================================================
-- 2. Trial Balance validation
-- ==========================================================

SELECT
    CompanyID,
    PeriodID,
    ROUND(SUM(Debit), 2) AS TotalDebit,
    ROUND(SUM(Credit), 2) AS TotalCredit,
    ROUND(SUM(Debit) - SUM(Credit), 2) AS Difference
FROM dbo.Fact_Trial_Balance
GROUP BY CompanyID, PeriodID
HAVING ROUND(SUM(Debit) - SUM(Credit), 2) <> 0
ORDER BY CompanyID, PeriodID;
GO

-- Expected result: no rows.

-- ==========================================================
-- 3. Balance Sheet validation
-- ==========================================================

WITH BS AS
(
    SELECT
        CompanyID,
        PeriodID,
        SUM(CASE WHEN RatioInput = 'Total Assets' THEN Amount ELSE 0 END) AS TotalAssets,
        SUM(CASE WHEN RatioInput = 'Total Liabilities and Equity' THEN Amount ELSE 0 END) AS TotalLiabilitiesAndEquity
    FROM dbo.Fact_Balance_Sheet
    GROUP BY CompanyID, PeriodID
)
SELECT
    CompanyID,
    PeriodID,
    TotalAssets,
    TotalLiabilitiesAndEquity,
    ROUND(TotalAssets - TotalLiabilitiesAndEquity, 2) AS Difference
FROM BS
WHERE ROUND(TotalAssets - TotalLiabilitiesAndEquity, 2) <> 0
ORDER BY CompanyID, PeriodID;
GO

-- Expected result: no rows.

-- ==========================================================
-- 4. Ratio coverage by category
-- ==========================================================

SELECT
    RatioCategory,
    COUNT(*) AS NumberOfRows,
    COUNT(DISTINCT RatioName) AS NumberOfRatios
FROM
(
    SELECT RatioCategory, RatioName FROM dbo.Fact_Liquidity_Ratios
    UNION ALL
    SELECT RatioCategory, RatioName FROM dbo.Fact_Profitability_Ratios
    UNION ALL
    SELECT RatioCategory, RatioName FROM dbo.Fact_Debt_Ratios
    UNION ALL
    SELECT RatioCategory, RatioName FROM dbo.Fact_Operating_Performance_Ratios
    UNION ALL
    SELECT RatioCategory, RatioName FROM dbo.Fact_Cash_Flow_Ratios
    UNION ALL
    SELECT RatioCategory, RatioName FROM dbo.Fact_Investment_Valuation_Ratios
) R
GROUP BY RatioCategory
ORDER BY RatioCategory;
GO

-- ==========================================================
-- 5. Market data validation
-- ==========================================================

SELECT
    CompanyID,
    PeriodID,
    SharePrice,
    SharesOutstanding,
    MarketCapitalization,
    ROUND(SharePrice * SharesOutstanding, 2) AS ExpectedMarketCap,
    ROUND(MarketCapitalization - (SharePrice * SharesOutstanding), 2) AS Difference
FROM dbo.Fact_Market_Data
WHERE ABS(ROUND(MarketCapitalization - (SharePrice * SharesOutstanding), 2)) > 1
ORDER BY CompanyID, PeriodID;
GO

-- Expected result: no rows.
"""

(base_path / "SQL_Validation_Queries.sql").write_text(
    validation_sql,
    encoding="utf-8"
)

print("SQL_Validation_Queries.sql created successfully.")

# ------------------------------------------------------------
# Step 7 - Generate SQL financial views
# ------------------------------------------------------------

views_sql = r"""
/*
============================================================
Financial Ratios Analysis in BI
Topic 17 - SQL Server Financial Model
Script 4 - Financial Views
============================================================
*/

USE FinancialRatiosBI;
GO

-- ==========================================================
-- View 1: All financial ratios combined
-- ==========================================================

CREATE OR ALTER VIEW dbo.vw_All_Financial_Ratios AS

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Liquidity_Ratios

UNION ALL

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Profitability_Ratios

UNION ALL

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Debt_Ratios

UNION ALL

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Operating_Performance_Ratios

UNION ALL

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Cash_Flow_Ratios

UNION ALL

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,
    PeriodEndDate,
    RatioCategory,
    RatioName,
    RatioValue,
    RatioFormat,
    Interpretation
FROM dbo.Fact_Investment_Valuation_Ratios;
GO

-- ==========================================================
-- View 2: Executive company-period summary
-- ==========================================================

CREATE OR ALTER VIEW dbo.vw_Executive_Financial_Summary AS

WITH IncomeData AS
(
    SELECT
        CompanyID,
        CompanyName,
        Industry,
        PeriodID,
        FiscalYear,
        FiscalQuarter,
        YearQuarter,
        MAX(CASE WHEN RatioInput = 'Revenue' THEN Amount END) AS Revenue,
        MAX(CASE WHEN RatioInput = 'Gross Profit' THEN Amount END) AS GrossProfit,
        MAX(CASE WHEN RatioInput = 'Operating Income' THEN Amount END) AS OperatingIncome,
        MAX(CASE WHEN RatioInput = 'Net Income' THEN Amount END) AS NetIncome
    FROM dbo.Fact_Income_Statement
    GROUP BY
        CompanyID,
        CompanyName,
        Industry,
        PeriodID,
        FiscalYear,
        FiscalQuarter,
        YearQuarter
),
BalanceData AS
(
    SELECT
        CompanyID,
        PeriodID,
        MAX(CASE WHEN RatioInput = 'Total Assets' THEN Amount END) AS TotalAssets,
        MAX(CASE WHEN RatioInput = 'Total Liabilities' THEN Amount END) AS TotalLiabilities,
        MAX(CASE WHEN RatioInput = 'Total Equity' THEN Amount END) AS TotalEquity,
        MAX(CASE WHEN RatioInput = 'Working Capital' THEN Amount END) AS WorkingCapital
    FROM dbo.Fact_Balance_Sheet
    GROUP BY CompanyID, PeriodID
),
CashFlowData AS
(
    SELECT
        CompanyID,
        PeriodID,
        MAX(CASE WHEN RatioInput = 'Operating Cash Flow' THEN Amount END) AS OperatingCashFlow,
        MAX(CASE WHEN RatioInput = 'Free Cash Flow' THEN Amount END) AS FreeCashFlow
    FROM dbo.Fact_Cash_Flow
    GROUP BY CompanyID, PeriodID
),
MarketData AS
(
    SELECT
        CompanyID,
        PeriodID,
        SharePrice,
        MarketCapitalization,
        EnterpriseValue,
        EPS,
        BookValuePerShare,
        DividendYield,
        PriceToEarnings,
        PriceToBook
    FROM dbo.Fact_Market_Data
)
SELECT
    I.CompanyID,
    I.CompanyName,
    I.Industry,
    I.PeriodID,
    I.FiscalYear,
    I.FiscalQuarter,
    I.YearQuarter,
    I.Revenue,
    I.GrossProfit,
    I.OperatingIncome,
    I.NetIncome,
    B.TotalAssets,
    B.TotalLiabilities,
    B.TotalEquity,
    B.WorkingCapital,
    C.OperatingCashFlow,
    C.FreeCashFlow,
    M.SharePrice,
    M.MarketCapitalization,
    M.EnterpriseValue,
    M.EPS,
    M.BookValuePerShare,
    M.DividendYield,
    M.PriceToEarnings,
    M.PriceToBook
FROM IncomeData I
LEFT JOIN BalanceData B
    ON I.CompanyID = B.CompanyID
    AND I.PeriodID = B.PeriodID
LEFT JOIN CashFlowData C
    ON I.CompanyID = C.CompanyID
    AND I.PeriodID = C.PeriodID
LEFT JOIN MarketData M
    ON I.CompanyID = M.CompanyID
    AND I.PeriodID = M.PeriodID;
GO

-- ==========================================================
-- View 3: Latest period ratio summary
-- ==========================================================

CREATE OR ALTER VIEW dbo.vw_Latest_Period_Ratios AS

WITH LatestPeriod AS
(
    SELECT MAX(PeriodID) AS LatestPeriodID
    FROM dbo.Dim_Period
)
SELECT
    R.*
FROM dbo.vw_All_Financial_Ratios R
INNER JOIN LatestPeriod L
    ON R.PeriodID = L.LatestPeriodID;
GO

-- ==========================================================
-- View 4: Financial health score inputs
-- ==========================================================

CREATE OR ALTER VIEW dbo.vw_Financial_Health_Inputs AS

SELECT
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter,

    MAX(CASE WHEN RatioName = 'Current Ratio' THEN RatioValue END) AS CurrentRatio,
    MAX(CASE WHEN RatioName = 'Quick Ratio' THEN RatioValue END) AS QuickRatio,
    MAX(CASE WHEN RatioName = 'Net Profit Margin' THEN RatioValue END) AS NetProfitMargin,
    MAX(CASE WHEN RatioName = 'Return on Assets' THEN RatioValue END) AS ReturnOnAssets,
    MAX(CASE WHEN RatioName = 'Return on Equity' THEN RatioValue END) AS ReturnOnEquity,
    MAX(CASE WHEN RatioName = 'Debt Ratio' THEN RatioValue END) AS DebtRatio,
    MAX(CASE WHEN RatioName = 'Debt-to-Equity Ratio' THEN RatioValue END) AS DebtToEquityRatio,
    MAX(CASE WHEN RatioName = 'Operating Cash Flow to Sales' THEN RatioValue END) AS OperatingCashFlowToSales,
    MAX(CASE WHEN RatioName = 'Cash Flow Coverage Ratio' THEN RatioValue END) AS CashFlowCoverageRatio,
    MAX(CASE WHEN RatioName = 'Price / Earnings Ratio' THEN RatioValue END) AS PriceToEarningsRatio

FROM dbo.vw_All_Financial_Ratios
GROUP BY
    CompanyID,
    CompanyName,
    Industry,
    PeriodID,
    FiscalYear,
    FiscalQuarter,
    YearQuarter;
GO
"""

(base_path / "SQL_Financial_Views.sql").write_text(
    views_sql,
    encoding="utf-8"
)

print("SQL_Financial_Views.sql created successfully.")

# ------------------------------------------------------------
# Step 8 - Create table metadata report
# ------------------------------------------------------------

metadata_df = pd.DataFrame(table_metadata)

metadata_df.to_csv(
    base_path / "SQL_Table_Metadata.csv",
    index=False
)

print("SQL_Table_Metadata.csv created successfully.")

# ------------------------------------------------------------
# Step 9 - Print validation summary
# ------------------------------------------------------------

print()
print("==============================")
print("SQL SERVER MODEL SUMMARY")
print("==============================")

print("Tables prepared:", len(metadata_df))

print()
print("Table metadata:")
print(metadata_df)

print()
print("SQL files created:")

created_files = [
    "SQL_Create_Tables.sql",
    "SQL_Load_Data_Template.sql",
    "SQL_Validation_Queries.sql",
    "SQL_Financial_Views.sql",
    "SQL_Table_Metadata.csv"
]

for file_name in created_files:
    file_path = base_path / file_name
    if file_path.exists():
        print("-", file_name)

print()
print("==============================")
print("NEXT STEPS")
print("==============================")
print("1. Open SQL Server Management Studio.")
print("2. Run SQL_Create_Tables.sql.")
print("3. Review and update the path in SQL_Load_Data_Template.sql.")
print("4. Run SQL_Load_Data_Template.sql.")
print("5. Run SQL_Validation_Queries.sql.")
print("6. Run SQL_Financial_Views.sql.")
print()
print("Topic 17 completed successfully.")

financial_ratios_bi_training/SQL_Financial_Model_Scripts.sql

Validation Checklist

• Checkpoint 1: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 2: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 3: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 4: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 5: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 6: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 7: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 8: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.

Final Recommendation

Before moving forward, open the generated file, review the columns, confirm the number of companies and periods, and make sure the numbers make financial sense. Good BI starts with clean, explainable, and validated data.