Bank Loan Analysis Dashboard

Bank Loan Analysis Dashboard: This project combines rigorous SQL data validation with advanced Tableau visualization techniques to deliver a detailed analysis of a financial institution’s lending operations.

Data Import & Organization:

• Source: Financial_Loan_Data.csv, approximately 15MB with 38,577 rows and 21 fields.
• Data was imported into an MSSQL Server database (BankLoanDB) using the “Import Flat File” wizard.
• Files are organized into dedicated folders: /Data, /SQL_Scripts, and /Dashboard_Exports.

Data Cleaning & Transformation:

• Corrected data type mismatches (e.g., changing total_payment to INT).
• Expanded the emp_title field from varchar(50) to varchar(100) to accommodate longer values.
• Managed over 1,200 null entries in emp_title and addressed blank next_payment_date values for fully paid loans.

Robust SQL queries were executed to validate data quality and extract key performance metrics. Examples include:

Total Loan Applications:

SELECT COUNT(id) AS Total_Loan_Applications
FROM [BankLoanDB]..bank_loan_data;

MTD Loan Applications (December 2021):

SELECT COUNT(id) AS MTD_Total_Loan_Applications
FROM [BankLoanDB]..bank_loan_data
WHERE MONTH(issue_date) = 12 AND YEAR(issue_date) = 2021;

Loan Status Breakdown (Good vs. Bad Loans):

SELECT 
  (COUNT(CASE WHEN loan_status IN ('Fully Paid', 'Current') THEN id END) * 100.0) / COUNT(id) AS Good_Loan_Percentage,
  COUNT(CASE WHEN loan_status = 'Charged Off' THEN id END) AS Bad_Loan_Applications
FROM [BankLoanDB]..bank_loan_data;

Average Interest Rate & DTI:

SELECT 
  ROUND(AVG(int_rate), 4) * 100 AS Avg_Interest_Rate,
  ROUND(AVG(dti), 4) * 100 AS Avg_DTI
FROM [BankLoanDB]..bank_loan_data;

Each of these queries was rigorously cross-validated with Tableau visualizations to ensure an accuracy margin within ±0.01%.

All visualizations were built exclusively in Tableau to deliver a highly interactive dashboard experience:

• Summary Dashboard: Displays aggregated KPIs (loan applications, funded amounts, repayments, average interest rates, DTI) along with a segmentation of “good” vs. “bad” loans.
• Overview Dashboard: Features:
  • A line chart for month-to-month trend analysis.
  • A filled map that visualizes state-wise lending activity.
  • Bar and donut charts for a detailed breakdown (loan purpose, home ownership, etc.).
• Details Dashboard: Provides an interactive grid view for record-level analysis and cross-filtering capabilities.

Example Tableau Calculation:

// Parameter-driven measure for dynamic KPI display in Tableau
CASE [Select Measure]
  WHEN "Total Applications" THEN COUNT([id])
  WHEN "Funded Amount" THEN SUM([loan_amount])
  WHEN "Total Received" THEN SUM([total_payment])
  ELSE SUM([loan_amount])
END

Advanced interactive features such as filter actions and drill-downs empower stakeholders to examine data by loan grade, term, and employment length, transforming complex data into actionable insights.

Validation Protocols:

• Cross-validated every SQL query result with Tableau aggregates ensuring consistency within ±0.01%.
• Tested edge conditions (e.g., "n/a" employment values; remapped ambiguous fields).
• Applied descriptive statistical methods to track data distribution and integrity during transformation.

Dataset Overview:

• 21 fields including Loan ID, Issue Date, Loan Amount, Interest Rate, DTI, etc.
• Data originally sourced from Financial_Loan_Data.csv and refined using comprehensive SQL cleaning protocols.

Quality Metrics:

• Completeness: Full record import, with null values addressed.
• Consistency: Reconciled SQL metrics with Tableau outputs.
• Accuracy: Statistical validation to maintain data integrity.