Credit Default Risk Scoring Pipeline

Build an ML pipeline that scores 30,000 bank customers by default risk.

Introduction

30 Second Summary

Every month, banks decide which customers are about to stop paying their credit card bills. Getting it wrong means millions in losses or harassing good customers with collection calls they don't deserve.

In this project, you will build a complete credit risk scoring pipeline that loads 30,000 real bank customer records into a local database, trains a machine learning model to predict who will default next month, and delivers results through a professional Excel report and interactive Power BI dashboard.

What You'll Build

When you run your finished pipeline, it scores every customer with a probability of defaulting, flags the highest-risk accounts, and outputs a formatted report that any stakeholder can open and act on immediately.

By the end of this project, you'll have:

  • A SQLite analytics layer you can query to see default rates sliced by credit tier, delinquency level, and customer segment.
  • A Python pipeline that scores all 30,000 customers with a calibrated probability of default and saves results to a formatted Excel workbook with summary sheets, high-risk flags, and segment breakdowns.
  • A 2-page Power BI dashboard connected to your pipeline output where you can filter by risk tier and drill into the accounts most likely to need intervention.
  • Secret Mission: Build a single automation script that chains the entire pipeline end-to-end with one command.

Are there any prerequisites?

This project assumes familiarity with Python, SQL fundamentals, and a basic understanding of machine learning concepts. You do not need prior experience with Power BI or credit risk analysis.

Before We Start

Before building anything, you need a workspace ready to go. This project processes 30,000 real bank customer records through a machine learning pipeline, so the right Python packages and dataset need to be in place first.

In this step, you will create your project folder, install the data science packages, and download the dataset you will be scoring throughout this project.

In this step, get ready to:
  • Create a project folder with subfolders for data, output, and scripts.
  • Install the Python packages needed for data analysis, machine learning, and Excel report generation.
  • Download the UCI credit card default dataset into your project.
Create your project folder

Every file in this project lives inside one folder. You will create it on your Desktop so it is easy to find.

  • Press the Windows key to open your search bar.
  • Type PowerShell and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir credit-risk-pipeline
cd credit-risk-pipeline
  • Create the three subfolders your pipeline needs by running:
mkdir data, output, scripts

What are these folders for?

The data/ folder holds the raw dataset. The scripts/ folder holds your Python pipeline scripts. The output/ folder is where the pipeline writes its results (the database, scored CSV, and Excel report).

  • Confirm the folders exist by running:
ls

You should see data, output, and scripts listed in the output.

Don't see the three folders?

Make sure you ran cd credit-risk-pipeline before running ls. If you are still in ~/Desktop, you will see all your Desktop files instead of the project subfolders.

Help me find my project folder in PowerShell.

Open the project in VS Code
  • Open the credit-risk-pipeline folder in VS Code by running this command from inside the project folder:
code .

VS Code opens with your project folder in the Explorer sidebar, showing the data, output, and scripts subfolders.

VS Code did not open?

If you see a "command not found" error, open VS Code manually. Press the Windows key, type VS Code, and press Enter. Then click File in the top menu, select Open Folder, and navigate to Desktop > credit-risk-pipeline.

Help me open my project folder in VS Code.

Install the Python packages

This project uses pandas for data manipulation, scikit-learn for machine learning, openpyxl for writing Excel reports, and xlrd for reading the dataset's older Excel format.

  • Open a terminal inside VS Code by pressing Ctrl+` (backtick key, usually above Tab).
  • Install all four packages by running this command:
pip install pandas scikit-learn openpyxl xlrd

What do these packages do?

  • pandas loads and transforms tabular data (like spreadsheets and database tables).
  • scikit-learn provides the Random Forest classifier and evaluation metrics for your default prediction model.
  • openpyxl writes formatted .xlsx Excel workbooks from Python without needing Excel installed.
  • xlrd reads the .xls format that the UCI dataset uses.

You should see output ending with "Successfully installed" followed by the package names and versions.

Seeing a permission error or pip not found?

If you see "pip is not recognized", try python -m pip install pandas scikit-learn openpyxl xlrd instead. If you see a permission error, add --user to the end of the command.

Help me fix my pip install error.

  • Verify all packages installed correctly by running:
python -c "import pandas; import sklearn; import openpyxl; print('All packages ready')"

Before you run this, what do you expect to see printed if everything installed correctly?

You should see All packages ready printed with no errors.

Seeing a ModuleNotFoundError?

If a specific package fails to import, re-run the pip install command for just that package. For example: pip install scikit-learn. Make sure you are using the same Python that pip installed to.

Help me fix a ModuleNotFoundError after pip install.

Download the dataset

You will use a real dataset of 30,000 credit card customers from a Taiwanese bank. The dataset is hosted on the UCI Machine Learning Repository and is free to download.

  • Open the UCI dataset page in your browser by visiting:

https://archive.ics.uci.edu/dataset/350/default+of+credit+card+clients

  • Click the Download button on the dataset page.
  • Extract the downloaded zip file. Inside you will find a file named default of credit card clients.xls (about 5.3 MB).
  • Move (or copy) default of credit card clients.xls into the data folder inside your credit-risk-pipeline project.

What is this dataset?

This dataset contains 30,000 real credit card customer records from 2005. Each row is one customer with their credit limit, age, education, 6 months of repayment history, bill amounts, and payment amounts. The target column records whether that customer defaulted the following month.

Your pipeline will analyze these records, engineer risk features, and train a model to predict which customers are likely to default.

  • Confirm the file is in the right place. In your VS Code terminal, run:
ls data

You should see default of credit card clients.xls listed in the output.

File not showing up in the data folder?

Make sure you moved the .xls file (not the zip file) into Desktop/credit-risk-pipeline/data/. Check that the filename matches exactly, including the spaces.

Help me find and move my downloaded dataset file.

Your workspace is ready. You have a project folder with the right structure, all the Python packages installed, and 30,000 customer records waiting to be analyzed. Next up, you will load this data into a SQLite database and run your first risk queries.

Build the SQL Risk Analytics Layer

Your environment is ready with packages installed and the dataset downloaded. Now it is time to understand what is actually in that data.

Before building any predictive model, you need to explore the portfolio. Loading the data into SQLite and running business queries reveals default patterns that inform which features matter. It also gives you the SQL analytics layer that hiring managers expect in enterprise data pipelines.

In this step, get ready to:
  • Load 30,000 customer records from the UCI dataset into a SQLite database.
  • Write SQL queries that reveal default rates by credit tier, delinquency status, and education level.
  • Print formatted analytics results to the terminal.
Load the dataset into SQLite

The UCI dataset sits in an Excel file with 30,000 rows of real credit card customer data. You need to load it into a queryable database so you can slice it with SQL.

  • In VS Code, create a new file called load_and_query.py inside the scripts/ folder.
  • Add the data loading function by pasting this code:
import pandas as pd
import sqlite3
import os

def load_data_to_sqlite():
    """Load UCI credit card dataset into SQLite database."""
    # Read the Excel file (skip first row which is a header description)
    df = pd.read_excel('data/default of credit card clients.xls', header=1)

    # Rename columns for clarity
    df.columns = ['ID', 'LIMIT_BAL', 'SEX', 'EDUCATION', 'MARRIAGE', 'AGE',
                  'PAY_0', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6',
                  'BILL_AMT1', 'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6',
                  'PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',
                  'default_payment_next_month']

    # Create SQLite database
    os.makedirs('output', exist_ok=True)
    conn = sqlite3.connect('output/credit_risk.db')
    df.to_sql('customers', conn, if_exists='replace', index=False)
    print(f"Loaded {len(df)} records into SQLite database.")
    return conn

if __name__ == '__main__':
    conn = load_data_to_sqlite()

What does this code do?

  • pd.read_excel reads the .xls file. The header=1 argument tells pandas that row 2 contains the real column headers (row 1 is a description row in this dataset).
  • df.columns = [...] renames every column to a clear, readable name so your SQL queries make sense.
  • df.to_sql('customers', conn, if_exists='replace') writes all 30,000 rows into a SQLite table called customers. You can now query it with standard SQL.

Why SQLite?

SQLite ships with Python's standard library. There is no database server to install or configure. It stores everything in a single file on disk, which makes it perfect for local analytics pipelines and portfolio projects.

  • Save scripts/load_and_query.py.
  • Before you run this, what do you think the output will show?
  • Run the script by entering this command in your terminal:
python scripts/load_and_query.py

You should see: Loaded 30000 records into SQLite database.

Seeing a file not found error?

  • Make sure your terminal is in the project root folder (the one containing the data/ and scripts/ subfolders). Run ls to confirm you see both folders.
  • Confirm the dataset file is saved exactly as default of credit card clients.xls inside the data/ folder. The spaces in the filename matter.
  • If you see a xlrd error, confirm you installed it: pip install xlrd.

Help me troubleshoot this error

Write the SQL risk queries

With 30,000 records in your database, you can now ask business questions. These queries slice default rates by credit tier and payment behaviour to reveal where the portfolio risk concentrates.

  • In scripts/load_and_query.py, find the line if __name__ == '__main__': at the bottom.
  • Add the run_risk_queries function ABOVE that line. Start with the function definition and the first SQL query:
def run_risk_queries(conn):
    """Execute portfolio-level risk analytics queries."""

    # Query 1: Default rate by credit limit tier
    query_credit_tier = """
    SELECT
        CASE
            WHEN LIMIT_BAL <= 100000 THEN 'Low (<=100K)'
            WHEN LIMIT_BAL <= 250000 THEN 'Medium (100K-250K)'
            ELSE 'High (>250K)'
        END AS credit_tier,
        COUNT(*) AS total_customers,
        SUM(default_payment_next_month) AS defaults,
        ROUND(AVG(default_payment_next_month) * 100, 1) AS default_rate_pct
    FROM customers
    GROUP BY credit_tier
    ORDER BY default_rate_pct DESC;
    """

What does this query do?

This query groups customers into three credit limit tiers (Low, Medium, High) and calculates the default rate for each. Customers with lower credit limits tend to default more often. This pattern tells you which segment to focus your model on.

  • Directly below the first query (still inside the run_risk_queries function), add the remaining two queries:
    # Query 2: Default rate by worst delinquency status
    query_delinquency = """
    SELECT
        CASE
            WHEN MAX(PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6) <= 0 THEN 'No delinquency'
            WHEN MAX(PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6) = 1 THEN '1 month late'
            WHEN MAX(PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6) = 2 THEN '2 months late'
            ELSE '3+ months late'
        END AS worst_delinquency,
        COUNT(*) AS total_customers,
        ROUND(AVG(default_payment_next_month) * 100, 1) AS default_rate_pct
    FROM customers
    GROUP BY worst_delinquency
    ORDER BY default_rate_pct DESC;
    """

    # Query 3: Portfolio exposure summary
    query_exposure = """
    SELECT
        CASE EDUCATION
            WHEN 1 THEN 'Graduate School'
            WHEN 2 THEN 'University'
            WHEN 3 THEN 'High School'
            ELSE 'Other'
        END AS education_level,
        COUNT(*) AS total_customers,
        ROUND(AVG(LIMIT_BAL), 0) AS avg_credit_limit,
        ROUND(AVG(default_payment_next_month) * 100, 1) AS default_rate_pct
    FROM customers
    GROUP BY education_level
    ORDER BY default_rate_pct DESC;
    """

What do these queries reveal?

  • The delinquency query finds each customer's worst payment delay across 6 months using MAX(PAY_0, PAY_2, ...). Customers who have been 3+ months late default at dramatically higher rates.
  • The exposure query shows how default risk varies by education level and reveals which segments carry the most credit exposure.
Run the complete analytics script

The queries are defined but nothing prints them yet. You need to execute each query and display the results, then update your main block to call the new function.

  • Still inside the run_risk_queries function, add the following code below the last query to execute and print all results:
    print("\n=== DEFAULT RATE BY CREDIT LIMIT TIER ===")
    result = pd.read_sql_query(query_credit_tier, conn)
    print(result.to_string(index=False))

    print("\n=== DEFAULT RATE BY WORST DELINQUENCY ===")
    result = pd.read_sql_query(query_delinquency, conn)
    print(result.to_string(index=False))

    print("\n=== PORTFOLIO EXPOSURE BY EDUCATION ===")
    result = pd.read_sql_query(query_exposure, conn)
    print(result.to_string(index=False))

    conn.close()

What does this code do?

pd.read_sql_query sends each SQL query to the SQLite database and returns the results as a pandas DataFrame. The to_string(index=False) method prints a clean table without row numbers.

  • Now update the if __name__ block at the bottom of the file. Find this line:
if __name__ == '__main__':
    conn = load_data_to_sqlite()
  • Add run_risk_queries(conn) below conn = load_data_to_sqlite(), so the block now looks like this:
if __name__ == '__main__':
    conn = load_data_to_sqlite()
    run_risk_queries(conn)
  • Save scripts/load_and_query.py.
  • What do you think the output will show this time? Do you expect low-credit customers or high-credit customers to default more often?
  • Run the script again:
python scripts/load_and_query.py

You should see three formatted tables printed to the terminal. The credit tier table shows that lower credit limit customers default at a higher rate (around 26%) compared to high credit limit customers (around 15%).

Seeing a NameError or IndentationError?

  • Make sure the entire run_risk_queries function is placed ABOVE the if __name__ block. Python reads top to bottom.
  • Check that all code inside run_risk_queries is indented with exactly 4 spaces. The triple-quoted SQL strings must also be indented inside the function.
  • Confirm conn.close() is the last line inside run_risk_queries, not after the function.

Help me debug this issue

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/load_and_query.py and move on to the next step.

ⓧ I'd like to double check the full code

Your scripts/load_and_query.py file should contain these sections in order. Scroll up and confirm each is present:

  • Imports (pandas, sqlite3, os) at the top.
  • load_data_to_sqlite() function that reads the Excel file, renames columns, and writes to SQLite.
  • run_risk_queries(conn) function containing three query variables (query_credit_tier, query_delinquency, query_exposure), followed by three print blocks and conn.close().
  • if __name__ == '__main__': block calling load_data_to_sqlite() then run_risk_queries(conn).

You have a working SQL analytics layer that reveals exactly where default risk hides in this portfolio. Next up, you will engineer predictive features from this raw data to feed a machine learning model.

Engineer Credit Risk Features

Your SQL queries revealed clear patterns: customers with low credit limits default more often, and delinquency history is a strong signal.

Raw payment columns like PAY_0 through PAY_6 are hard for a predictive model to interpret directly. In this step, you will transform them into meaningful risk features that capture the behavioral patterns a credit analyst looks for. You will also test whether a simple rule can identify defaulters on its own.

In this step, get ready to:
  • Engineer five predictive features from raw payment history.
  • Test a rule-based threshold to flag high-risk customers.
  • Observe how static rules fall short at identifying defaulters.
Calculate risk features from payment data

Each feature captures a different dimension of credit risk that a single raw column cannot express on its own.

  • In VS Code, create a new file called feature_engineering.py inside the scripts folder.
  • Add the following code to load data from your SQLite database and calculate five risk features:
import pandas as pd
import sqlite3
import os

def engineer_features():
    """Create risk-predictive features from raw payment data."""
    conn = sqlite3.connect('output/credit_risk.db')
    df = pd.read_sql_query("SELECT * FROM customers", conn)
    conn.close()

    pay_columns = ['PAY_0', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6']
    bill_columns = ['BILL_AMT1', 'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6']
    pay_amt_columns = ['PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6']

    # Feature 1: Credit utilization ratio
    avg_bill = df[bill_columns].mean(axis=1)
    df['credit_utilization_ratio'] = avg_bill / df['LIMIT_BAL'].replace(0, 1)

    # Feature 2: Worst delinquency in 6-month window
    df['worst_delinquency'] = df[pay_columns].max(axis=1)

    # Feature 3: Payment consistency (months paid on time or early)
    df['payment_consistency'] = (df[pay_columns] <= 0).sum(axis=1)

    # Feature 4: Bill amount trend (month 1 vs month 6)
    df['bill_trend'] = df['BILL_AMT1'] - df['BILL_AMT6']

    # Feature 5: Average payment ratio (amount paid / amount billed)
    avg_pay = df[pay_amt_columns].mean(axis=1)
    df['avg_payment_ratio'] = avg_pay / avg_bill.replace(0, 1)

What do these features capture?

  • credit_utilization_ratio measures how much of their credit limit a customer actually uses. Higher utilization signals financial stress.
  • worst_delinquency captures the most severe late payment in 6 months. A single severe delinquency is a stronger signal than an average.
  • payment_consistency counts months where the customer paid on time or early (status <= 0). More consistent payers are lower risk.
  • bill_trend shows whether the customer's balance is growing or shrinking over the 6-month window.
  • avg_payment_ratio compares what the customer pays each month to what they owe. Paying less than billed is a warning sign.
Test a rule-based approach

With these features calculated, a natural first instinct is to use a simple threshold rule to flag risky customers. If someone has been 3+ months late, they must be high risk, right?

  • Add the following code directly below the previous block (still inside the engineer_features function) to print a feature summary, test the rule-based approach, and save results:
    print(f"Engineered features for {len(df)} customers.")
    print(f"\nNew feature columns: credit_utilization_ratio, worst_delinquency, "
          f"payment_consistency, bill_trend, avg_payment_ratio")
    print(f"\nFeature summary:")
    print(df[['credit_utilization_ratio', 'worst_delinquency',
              'payment_consistency', 'bill_trend', 'avg_payment_ratio']].describe().round(2))

    # Demonstrate rule-based limitation
    print("\n=== RULE-BASED APPROACH LIMITATION ===")
    rule_flagged = df['worst_delinquency'] > 2
    actual_defaults = df['default_payment_next_month'] == 1

    true_positives = (rule_flagged & actual_defaults).sum()
    total_defaults = actual_defaults.sum()
    total_flagged = rule_flagged.sum()
    total_non_defaults = (~actual_defaults).sum()
    false_positives = (rule_flagged & ~actual_defaults).sum()

    recall = true_positives / total_defaults * 100
    false_positive_rate = false_positives / total_non_defaults * 100

    print(f"Rule: Flag customers with worst_delinquency > 2")
    print(f"Recall (defaulters caught): {recall:.1f}%")
    print(f"False positive rate: {false_positive_rate:.1f}%")
    print(f"-> Static rules miss ~{100-recall:.0f}% of actual defaulters!")

What does the rule-based test do?

This code applies a simple rule: flag everyone whose worst delinquency exceeds 2 months as high risk. It then measures two things.

Recall tells you what percentage of actual defaulters the rule catches. False positive rate tells you how many safe customers get incorrectly flagged. A good system needs high recall without overwhelming the collections team with false alarms.

  • Add the final block below the rule-based test to save features and make the script runnable:
    # Save features
    os.makedirs('output', exist_ok=True)
    df.to_csv('output/features.csv', index=False)
    print(f"\nFeatures saved to output/features.csv")
    return df

if __name__ == '__main__':
    engineer_features()

What does this code do?

The save block writes all 30,000 rows (with engineered features) to a CSV file that the next step's model training script will read.

The if __name__ == '__main__' guard means the function runs when you execute the script directly from the terminal.

  • Save scripts/feature_engineering.py.
Run the feature engineering script

Before you run this, think about the rule-based test. Do you think flagging everyone with 3+ months of delinquency will catch most defaulters?

  • Run the feature engineering script by executing this command in your terminal:
python scripts/feature_engineering.py

You will see feature summary statistics for all 30,000 customers, followed by the rule-based test results.

The output reveals the problem: the simple rule catches only about 60% of actual defaulters. That means 40% of people who will default slip through undetected. This is the gap that a machine learning model will close in the next step.

Why does the rule miss so many defaulters?

A single threshold on one feature ignores everything else. Many customers who default have moderate delinquency (1-2 months) combined with high utilization or declining payments. A static rule cannot weigh multiple signals simultaneously.

A Random Forest model can consider all five features at once, finding combinations that predict default far more reliably than any single rule.

Seeing a FileNotFoundError?

  • Make sure you are running the command from your project root folder (the one containing the scripts and output subfolders).
  • Confirm that output/credit_risk.db exists. If not, run python scripts/load_and_query.py first to create the database.

Why is my feature engineering script failing?

  • Confirm that output/features.csv was created by running:
ls output/features.csv

You should see the file listed. This CSV contains all 30,000 customers with their original data plus the five new feature columns.

✔️ Awesome, I've got everything!

Great. Double check that you have saved scripts/feature_engineering.py and that the terminal output shows both the feature summary and the rule-based limitation results.

ⓧ I'd like to double check the full code

Your file has three main sections. Scroll up and confirm each one is present in this order:

  • Imports, function definition, data loading from SQLite, column definitions, and five feature calculations (credit_utilization_ratio through avg_payment_ratio).
  • Print summary statements, followed by the rule-based limitation test that computes recall and false positive rate.
  • Save block writing to output/features.csv, return statement, and the if __name__ == '__main__' entry point.

You have proven that static rules leave 40% of defaulters undetected. Next up, you will train a Random Forest classifier that weighs all five features simultaneously to produce a calibrated probability of default for every customer.

Train the ML Default Scoring Model

Your feature engineering script revealed the core limitation of static rules. Flagging everyone with worst_delinquency > 2 caught only about 60% of actual defaulters while flooding the collections team with false alarms.

A Random Forest classifier can weigh all features simultaneously to produce calibrated probability scores. Instead of a single threshold, the model learns complex interactions between utilization, delinquency, payment consistency, and bill trends to identify defaulters the rules missed.

In this step, get ready to:
  • Split the data into train/test sets and train a Random Forest classifier.
  • Evaluate model performance with AUC-ROC and feature importances.
  • Score all 30,000 customers with default probabilities and risk tiers.
Prepare the data and define the model

The model needs to learn from historical outcomes. You will split the 30,000 customers into a training set (80%) for learning patterns and a test set (20%) for honest evaluation.

  • Create a new file called train_model.py inside the scripts folder.
  • Add the imports, feature column definitions, and stratified train/test split by pasting the following code:
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import roc_auc_score, classification_report
import os

def train_default_model():
    """Train Random Forest model for credit default prediction."""
    df = pd.read_csv('output/features.csv')

    # Define feature columns
    feature_cols = ['LIMIT_BAL', 'AGE', 'PAY_0', 'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6',
                    'BILL_AMT1', 'BILL_AMT2', 'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6',
                    'PAY_AMT1', 'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',
                    'credit_utilization_ratio', 'worst_delinquency',
                    'payment_consistency', 'bill_trend', 'avg_payment_ratio']

    X = df[feature_cols]
    y = df['default_payment_next_month']

    # Stratified train/test split
    X_train, X_test, y_train, y_test = train_test_split(
        X, y, test_size=0.2, random_state=42, stratify=y
    )

    print(f"Training set: {len(X_train)} samples ({y_train.mean()*100:.1f}% default rate)")
    print(f"Test set: {len(X_test)} samples ({y_test.mean()*100:.1f}% default rate)")

What does this code do?

This first section sets up the data pipeline for training:

  • feature_cols lists the 25 input columns the model will learn from. This includes the raw payment columns plus your five engineered features.
  • stratify=y ensures both the training and test sets preserve the 22% default rate. Without stratification, the test set might randomly get more or fewer defaulters, giving unreliable results.
  • random_state=42 makes the split reproducible so you get the same results each run.
Train the model and evaluate performance

Now you will instantiate the Random Forest, train it on the 24,000-sample training set, and measure how well it identifies defaulters on the unseen test set.

  • In scripts/train_model.py, add the following code directly below the two print statements you just pasted:
    # Train Random Forest with balanced class weights
    model = RandomForestClassifier(
        n_estimators=100,
        max_depth=10,
        class_weight='balanced',
        random_state=42,
        n_jobs=-1
    )
    model.fit(X_train, y_train)

    # Evaluate
    y_pred_proba = model.predict_proba(X_test)[:, 1]
    y_pred = model.predict(X_test)
    auc_score = roc_auc_score(y_test, y_pred_proba)

    print(f"\n=== MODEL PERFORMANCE ===")
    print(f"AUC-ROC: {auc_score:.4f}")
    print(f"\nClassification Report:")
    print(classification_report(y_test, y_pred, target_names=['No Default', 'Default']))

    # Feature importance
    importance = pd.Series(model.feature_importances_, index=feature_cols)
    importance = importance.sort_values(ascending=False)
    print(f"\nTop 10 Feature Importances:")
    for feat, imp in importance.head(10).items():
        print(f"  {feat}: {imp:.4f}")

    return model, df

if __name__ == '__main__':
    train_default_model()

What does this code do?

  • class_weight='balanced' tells the model to pay more attention to the minority class (defaulters at 22%). Without this, the model would just predict "no default" for everyone and still hit 78% accuracy.
  • roc_auc_score measures how well the model separates defaulters from non-defaulters across all probability thresholds. This is the standard metric for imbalanced credit scoring problems.
  • feature_importances_ reveals which features the model relies on most. You should see worst_delinquency and credit_utilization_ratio near the top.

Why AUC-ROC instead of accuracy?

With only 22% of customers defaulting, a model that always predicts "no default" scores 78% accuracy. Accuracy is misleading for imbalanced datasets. AUC-ROC measures ranking quality: how often the model assigns a higher probability to an actual defaulter than a non-defaulter, regardless of the threshold you pick.

Before you run the script, what AUC-ROC score do you think the model will achieve? A score of 0.5 means random guessing. A score of 1.0 means perfect separation.

  • Save scripts/train_model.py and run it with this command:
python scripts/train_model.py

You should see the training and test set sizes (24,000 and 6,000 samples), an AUC-ROC score around 0.77-0.78, a full classification report, and the top 10 feature importances with worst_delinquency ranking highest.

Seeing an import error?

  • If you see a ModuleNotFoundError for sklearn, make sure you installed it with the package name scikit-learn, not sklearn.
  • If the script cannot find output/features.csv, make sure you ran python scripts/feature_engineering.py successfully in the previous step.
  • Check your indentation. All lines from model = RandomForestClassifier through the feature importance loop must be indented inside the train_default_model() function.

Why isn't my train_model.py script running?

Score all customers and assign risk tiers

The model can now generate a probability-of-default for every single customer. You will use these probabilities to assign each customer to a risk tier that the collections team can act on.

  • In scripts/train_model.py, find this line:
    return model, df
  • Replace return model, df and the if __name__ block with the following code that scores all customers before returning:
    # Score ALL customers
    df['probability_of_default'] = model.predict_proba(X[feature_cols])[:, 1]
    df['risk_tier'] = pd.cut(
        df['probability_of_default'],
        bins=[0, 0.15, 0.30, 1.0],
        labels=['Low', 'Medium', 'High']
    )

    # Save scored customers
    os.makedirs('output', exist_ok=True)
    df.to_csv('output/scored_customers.csv', index=False)
    print(f"\nScored {len(df)} customers. Saved to output/scored_customers.csv")
    print(f"\nRisk tier distribution:")
    print(df['risk_tier'].value_counts().to_string())

    return model, df

if __name__ == '__main__':
    train_default_model()

What does this code do?

  • predict_proba(X)[:, 1] gets the model's predicted probability of default for every customer, not just the test set. The [:, 1] selects the probability column for class 1 (default).
  • pd.cut assigns each customer to a tier based on their probability score: Low (below 15%), Medium (15-30%), or High (above 30%).
  • The scored dataframe is saved to output/scored_customers.csv where the next step will use it to generate the Excel report.

Do you think most customers will land in the Low, Medium, or High risk tier? Make your prediction before running the script.

  • Save scripts/train_model.py and run it again:
python scripts/train_model.py

At the bottom of the output, you should now see a message confirming 30,000 customers scored and saved to output/scored_customers.csv, plus a risk tier distribution showing most customers in the Low tier.

Not seeing the scored_customers.csv file?

  • Make sure the scoring code is inside the train_default_model() function (indented with 4 spaces), not at the top level of the file.
  • Confirm that return model, df appears only once, after the scoring code. If you have two return statements, the function exits before scoring.

Help me fix my scoring output in train_model.py

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Here is the complete scripts/train_model.py file. Scroll up and confirm each section is present in this order: imports, function definition with feature columns, train/test split, model training and evaluation, feature importances, scoring all customers, saving to CSV, and the if __name__ block at the bottom.

  • Imports and function start with feature column definitions.
  • Stratified train/test split with print statements.
  • RandomForestClassifier with balanced class weights, fit, and evaluation (AUC-ROC, classification report, feature importances).
  • Scoring all customers with predict_proba, risk tier assignment with pd.cut, and saving to output/scored_customers.csv.
  • return model, df followed by the if __name__ == '__main__': block.

You now have a trained model that scores every customer with a calibrated probability of default. The next step turns these scores into a professional Excel report and interactive Power BI dashboard that stakeholders can act on.

Generate the Excel Report and Power BI Dashboard

Your model has scored all 30,000 customers with a probability of default. That's powerful, but it lives inside a Python script that no stakeholder will ever open.

In this step, you will deliver those predictions in two formats decision-makers actually use. First, an automated Excel report that any manager can open. Second, an interactive Power BI dashboard that lets leadership drill into risk segments on demand.

In this step, get ready to:
  • Generate a formatted multi-sheet Excel risk report from the scored customer data.
  • Import the report into Power BI Desktop and build a Portfolio Overview page.
  • Build a High Risk Drill-Down page with interactive filtering.
Write the Excel report generator

The report script reads the scored customer CSV and produces a professional Excel workbook with three sheets: a summary dashboard, a high-risk customer list, and a segment analysis.

  • Create a new file called generate_report.py inside your scripts folder.
  • Start the file with the imports, function definition, data loading, and Summary sheet setup by pasting this code:
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
import os

def generate_risk_report():
    """Generate formatted Excel risk report from scored customers."""
    df = pd.read_csv('output/scored_customers.csv')

    wb = Workbook()

    # --- Sheet 1: Summary ---
    ws_summary = wb.active
    ws_summary.title = "Summary"

    # Styles
    header_font = Font(bold=True, size=14)
    subheader_font = Font(bold=True, size=11)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_text = Font(bold=True, color="FFFFFF")

    ws_summary['A1'] = "Credit Risk Portfolio Summary"
    ws_summary['A1'].font = header_font

    # KPIs
    total_customers = len(df)
    default_rate = df['default_payment_next_month'].mean() * 100
    avg_pd = df['probability_of_default'].mean() * 100
    high_risk_count = (df['risk_tier'] == 'High').sum()

What does this code do?

This sets up the workbook with openpyxl and defines reusable styles (blue header fills, bold fonts) for a professional look.

It then calculates four portfolio KPIs from the scored data: total customers, actual default rate, average predicted probability of default, and count of high-risk customers.

  • Directly below the code you just pasted, add the KPI write logic and risk tier breakdown table:
    kpis = [
        ('Total Customers', f"{total_customers:,}"),
        ('Actual Default Rate', f"{default_rate:.1f}%"),
        ('Average Predicted PD', f"{avg_pd:.1f}%"),
        ('High Risk Customers', f"{high_risk_count:,}"),
    ]

    for i, (label, value) in enumerate(kpis, start=3):
        ws_summary[f'A{i}'] = label
        ws_summary[f'A{i}'].font = subheader_font
        ws_summary[f'B{i}'] = value

    # Risk tier breakdown
    ws_summary['A8'] = "Risk Tier Breakdown"
    ws_summary['A8'].font = subheader_font

    tier_summary = df.groupby('risk_tier', observed=True).agg(
        customers=('ID', 'count'),
        avg_pd=('probability_of_default', 'mean'),
        actual_default_rate=('default_payment_next_month', 'mean')
    ).reset_index()

    for col_idx, col_name in enumerate(['Risk Tier', 'Customers', 'Avg PD', 'Actual Default Rate'], 1):
        cell = ws_summary.cell(row=9, column=col_idx, value=col_name)
        cell.font = header_text
        cell.fill = header_fill

    for row_idx, row in tier_summary.iterrows():
        ws_summary.cell(row=10 + row_idx, column=1, value=row['risk_tier'])
        ws_summary.cell(row=10 + row_idx, column=2, value=int(row['customers']))
        ws_summary.cell(row=10 + row_idx, column=3, value=f"{row['avg_pd']*100:.1f}%")
        ws_summary.cell(row=10 + row_idx, column=4, value=f"{row['actual_default_rate']*100:.1f}%")

What does the tier breakdown do?

It groups all 30,000 customers by their Low/Medium/High risk tier and calculates the average predicted PD and actual default rate for each group. The blue-header table makes it immediately scannable for stakeholders.

  • Continue below the tier breakdown loop. Add the High Risk Customers sheet:
    # --- Sheet 2: High Risk Customers ---
    ws_high_risk = wb.create_sheet("High Risk Customers")
    high_risk = df[df['risk_tier'] == 'High'].sort_values('probability_of_default', ascending=False)
    high_risk_display = high_risk[['ID', 'LIMIT_BAL', 'AGE', 'worst_delinquency',
                                    'credit_utilization_ratio', 'probability_of_default']].copy()
    high_risk_display['probability_of_default'] = (high_risk_display['probability_of_default'] * 100).round(1)
    high_risk_display.columns = ['Customer ID', 'Credit Limit', 'Age', 'Worst Delinquency',
                                  'Utilization Ratio', 'Default Probability (%)']

    # Write headers
    for col_idx, col_name in enumerate(high_risk_display.columns, 1):
        cell = ws_high_risk.cell(row=1, column=col_idx, value=col_name)
        cell.font = header_text
        cell.fill = header_fill

    # Write data
    for row_idx, row in enumerate(high_risk_display.itertuples(index=False), 2):
        for col_idx, value in enumerate(row, 1):
            ws_high_risk.cell(row=row_idx, column=col_idx, value=value)

What does the High Risk sheet contain?

It filters to only customers in the High risk tier (PD above 30%), sorts them by default probability descending, and writes their key attributes into a formatted table. This gives the collections team a ready-made action list.

  • Finally, paste the Segment Analysis sheet and the save logic below the High Risk Customers section:
    # --- Sheet 3: Segment Analysis ---
    ws_segment = wb.create_sheet("Segment Analysis")

    education_map = {1: 'Graduate School', 2: 'University', 3: 'High School'}
    df['education_label'] = df['EDUCATION'].map(education_map).fillna('Other')

    segment = df.groupby('education_label').agg(
        customers=('ID', 'count'),
        avg_credit_limit=('LIMIT_BAL', 'mean'),
        avg_pd=('probability_of_default', 'mean'),
        actual_default_rate=('default_payment_next_month', 'mean')
    ).reset_index()

    segment_cols = ['Education Level', 'Customers', 'Avg Credit Limit', 'Avg PD', 'Actual Default Rate']
    for col_idx, col_name in enumerate(segment_cols, 1):
        cell = ws_segment.cell(row=1, column=col_idx, value=col_name)
        cell.font = header_text
        cell.fill = header_fill

    for row_idx, row in segment.iterrows():
        ws_segment.cell(row=2 + row_idx, column=1, value=row['education_label'])
        ws_segment.cell(row=2 + row_idx, column=2, value=int(row['customers']))
        ws_segment.cell(row=2 + row_idx, column=3, value=f"{row['avg_credit_limit']:,.0f}")
        ws_segment.cell(row=2 + row_idx, column=4, value=f"{row['avg_pd']*100:.1f}%")
        ws_segment.cell(row=2 + row_idx, column=5, value=f"{row['actual_default_rate']*100:.1f}%")

    # Save workbook
    os.makedirs('output', exist_ok=True)
    wb.save('output/risk_report.xlsx')
    print("Risk report generated: output/risk_report.xlsx")
    print(f"  - Summary sheet: Portfolio KPIs and risk tier breakdown")
    print(f"  - High Risk Customers: {len(high_risk_display)} accounts sorted by PD")
    print(f"  - Segment Analysis: Default rates by education level")

if __name__ == '__main__':
    generate_risk_report()

What does the Segment Analysis sheet show?

It groups customers by education level (Graduate School, University, High School, Other) and shows how many customers, their average credit limit, average predicted PD, and actual default rate per segment. This reveals which demographics carry the most risk.

Before you run this, what do you expect the terminal output to show?

  • Save scripts/generate_report.py and run it:
python scripts/generate_report.py

You should see output confirming the report was generated with all three sheets listed, including the count of high-risk accounts.

  • Open output/risk_report.xlsx in Excel. Confirm you see three sheet tabs at the bottom: Summary, High Risk Customers, and Segment Analysis.

Seeing a FileNotFoundError?

Make sure you are running the command from your project root folder (the one containing the scripts and output folders). The script reads output/scored_customers.csv which was created in the previous step.

Help me debug this FileNotFoundError

✔️ Awesome, I've got everything!

Great. Double check you have saved scripts/generate_report.py and the Excel file opened with all three sheets.

ⓧ I'd like to double check the full code

Your scripts/generate_report.py file is composed of the four code chunks above in this order:

  • Chunk 1: Imports, function definition, workbook setup, styles, and KPI calculations.
  • Chunk 2: KPI list write, risk tier breakdown with header styling.
  • Chunk 3: High Risk Customers sheet creation and data write.
  • Chunk 4: Segment Analysis sheet, save workbook, print statements, and main block.

Scroll up and confirm each chunk is present in order. The function should start with def generate_risk_report(): and end with the if __name__ == '__main__': block.

Import the report into Power BI and build the Portfolio Overview

Power BI Desktop connects directly to your Excel workbook, letting you build interactive visuals on top of the same data your script produces.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • If you see a sign-in or welcome screen, close it to get to the blank canvas.
  • On the Home tab in the ribbon, click Get Data.
  • Select Excel Workbook from the list of data sources and click Connect.
  • Browse to your project's output folder, select risk_report.xlsx, and click Open.
  • In the Navigator window, check the boxes next to all three sheets: Summary, High Risk Customers, and Segment Analysis.
  • Click Load to import the data into Power BI.

After loading, you should see all three tables listed in the Fields pane on the right side of the screen.

Why Excel Workbook and not CSV?

Your report has three sheets with different structures. Power BI loads each sheet as a separate table, preserving the multi-sheet layout your script created. A CSV would only give you one flat table.

Now build the first dashboard page.

  • Right-click on Page 1 at the bottom of the canvas and select Rename Page.
  • Type Portfolio Overview and press Enter.
  • In the Visualizations pane, click the Card visual icon (a rectangle with a number).
  • From the Fields pane, expand the Summary table and drag Column2 (the value column) into the card's Fields well. Filter this card to show only the Total Customers row.
  • Create a second Card visual for the overall default rate using the same approach.
  • Click the Clustered bar chart icon in the Visualizations pane.
  • Expand the High Risk Customers table. Drag Risk Tier to the X-axis and Customer ID to the Y-axis (set to Count).
  • Click the Pie chart icon. From the Segment Analysis table, drag Education Level to Legend and Customers to Values.

Your Portfolio Overview page should now show two KPI cards at the top, a bar chart of customer count by risk tier, and a pie chart breaking down customers by education segment.

Fields pane is empty?

Make sure you checked all three sheet names in the Navigator window and clicked Load. If you clicked Transform Data by mistake, click Close & Apply in the Power Query Editor ribbon to load the data into your report.

Help me fix my empty Fields pane

Build the High Risk Drill-Down page

The second page gives leadership an interactive view of the riskiest accounts with filtering controls.

  • Click the + icon next to Portfolio Overview at the bottom to create a new page.
  • Right-click the new page tab and select Rename Page.
  • Type High Risk Drill-Down and press Enter.
  • In the Visualizations pane, click the Table visual icon.
  • From the High Risk Customers table in Fields, drag Customer ID, Credit Limit, Worst Delinquency, Default Probability (%), and Risk Tier into the table's Columns well.
  • Click the Slicer visual icon in the Visualizations pane.
  • Drag Risk Tier from the High Risk Customers table into the slicer's Field well. This adds a filter control to the page.
  • Click the Clustered column chart icon.
  • From the Summary table's tier breakdown data, drag the risk tier to the X-axis and the actual default rate percentage to the Y-axis.
  • Add the average PD as a second value to compare predicted vs actual rates side by side.

Try clicking different risk tiers in the slicer. The table and chart should update to show only the selected segment.

  • Save your Power BI file by pressing Ctrl+S.
  • Name the file credit_risk_dashboard.pbix and save it in your project folder.

Why a slicer instead of separate reports?

A slicer lets one page serve multiple audiences. The collections team filters to High risk only. Portfolio managers look at all tiers. One dashboard, many views.

Chart showing unexpected values?

Power BI may default to Sum for numeric fields. Click the field in the Values well, then select the dropdown arrow and change the aggregation from Sum to Average or Don't summarize depending on the metric.

Help me fix my chart aggregation settings

Your credit risk pipeline now delivers results in two stakeholder-ready formats. The Excel report can be emailed directly to managers. The Power BI dashboard gives interactive exploration to leadership. Next up, you will chain this entire pipeline into a single automated command.

Secret mission

End-to-End Pipeline Automation

Running 4 separate scripts manually is not production-ready. Can you build a single automation script that chains the entire pipeline with one command, complete with timing, error handling, and a skip-train flag?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources on your machine, or delete them entirely.

Resources you used:

  • Project folder on your Desktop (Python scripts, dataset, SQLite database, CSV files, Excel report, Power BI file).
  • Python packages installed via pip (pandas, scikit-learn, openpyxl, xlrd).
  • Power BI Desktop application.

Keep everything running

No action needed. Choose this if you want to keep the full pipeline ready to showcase in interviews or extend further.

  • Your project folder contains the complete pipeline, scored data, and Power BI dashboard.
  • You can re-run the pipeline at any time with python run_pipeline.py.
  • Consider uploading to GitHub as a portfolio project with a README explaining the business problem and your approach.

Pause - I'll come back to this later

All resources are local files with no running processes. There is nothing consuming memory or incurring costs while idle.

  • Close the Power BI Desktop file if it is still open.
  • Close VS Code.
  • Your project folder will be exactly as you left it when you return.

Delete - I don't want to use this again

Remove all project files and optionally uninstall the tools you installed for this project.

  • Close Power BI Desktop and VS Code if they are still open.
  • Delete your project folder from your Desktop. Right-click the folder in File Explorer and select Delete.
  • Uninstall the Python packages by running this command in your terminal:
pip uninstall pandas scikit-learn openpyxl xlrd -y
  • To uninstall Power BI Desktop, open Settings on your Windows machine.
  • Select Apps, then Installed apps.
  • Search for Power BI Desktop, click the three-dot menu, and select Uninstall.

Nice Work!

Nice Work!

You just built a complete credit risk scoring system that transforms 30,000 real bank customer records into ML-powered default predictions, delivered through both an automated Excel report and an interactive Power BI dashboard.

You've learned how to:

  • Build a SQLite analytics layer with SQL risk queries, engineer predictive features like credit utilization ratio and delinquency severity, and demonstrate why static threshold rules fall short of identifying defaulters.
  • Train a Random Forest default prediction model with class imbalance handling and AUC-ROC evaluation, scoring every customer with a calibrated probability of default.
  • Automate stakeholder-ready deliverables using openpyxl for a formatted Excel risk scorecard and Power BI Desktop for a 2-page interactive dashboard with drill-down filtering.
  • Secret Mission: Created an end-to-end pipeline automation script with per-stage timing, error handling, and a --skip-train flag for report-only regeneration.

Ready to quiz yourself?