Skip to content
BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
LUMINA_MIS_Dashboard.xlsx
LUMINA_MIS_Dashboard.xlsx
 
 
Lumina.png
Lumina.png
 
 
README.md
README.md
 
 

Repository files navigation

LUMINA: Personal Capital Intelligence & MIS Engine

Sophisticated Financial Command & Control Through Integrated Data Architecture

Executive Summary

LUMINA represents a paradigm shift in personal financial management, architected as an enterprise-grade Management Information System (MIS) that transforms transactional data into strategic intelligence. This hybrid platform synthesizes robust relational database management with dynamic executive reporting, delivering real-time fiscal oversight and data-driven capital allocation optimization.

The system addresses critical inefficiencies in traditional personal finance tracking by implementing a dual-tier architecture: a normalized MS Access backend ensures data integrity and scalability, while an Excel-based analytical frontend provides executive-level dashboards with interactive KPI monitoring. Through systematic ETL processes and AI-augmented narrative generation, LUMINA enables proactive financial decision-making, anomaly detection, and long-term wealth trajectory optimization.

Key Strategic Outcomes:

  • Enhanced Fiscal Transparency: 360° visibility into cash flows, asset positioning, and expenditure patterns
  • Predictive Capital Management: Liquidity forecasting and burn rate analysis for sustainable fiscal health
  • Data-Driven Optimization: Identification of capital redeployment opportunities and cost reduction vectors
  • Executive Intelligence: AI-generated insights transforming raw metrics into actionable strategic recommendations

System Architecture

Architecture Overview

┌─────────────────────────────────────────────────────────────────┐
│                      LUMINA SYSTEM ARCHITECTURE                  │
└─────────────────────────────────────────────────────────────────┘

┌──────────────────┐         ┌──────────────────┐         ┌──────────────────┐
│                  │         │                  │         │                  │
│  DATA INGESTION  │────────▶│  NORMALIZATION   │────────▶│  INTELLIGENCE    │
│     LAYER        │         │  & PERSISTENCE   │         │     LAYER        │
│                  │         │                  │         │                  │
│  • Manual Entry  │         │  • MS Access DB  │         │  • Excel MIS     │
│  • CSV Import    │         │  • Relational    │         │  • Dynamic KPIs  │
│  • Bank Feeds*   │         │    Schema        │         │  • Dashboards    │
│                  │         │  • ACID          │         │  • Visualizations│
└──────────────────┘         │    Compliance    │         │                  │
                             │                  │         │  • AI Analytics  │
                             └──────────────────┘         │  • Insights Gen  │
                                      │                   │                  │
                                      │                   └──────────────────┘
                                      ▼                            ▲
                             ┌──────────────────┐                 │
                             │                  │                 │
                             │  ETL MIDDLEWARE  │─────────────────┘
                             │                  │
                             │  • SQL Queries   │
                             │  • Data Cleansing│
                             │  • Aggregation   │
                             │  • Export to CSV │
                             │                  │
                             └──────────────────┘

* Future enhancement capability

Component Descriptions

1. Data Ingestion Layer

  • Primary Input Method: Manual transaction entry via Access forms with validation rules
  • Supplementary: Batch CSV imports for historical data migration
  • Future State: Automated bank feed integration via API connectivity

2. Normalization & Persistence (MS Access Backend)

  • Database Engine: Microsoft Access 2016+ (.accdb format)
  • Data Model: Third Normal Form (3NF) relational structure
  • Integrity Controls: Primary/Foreign key constraints, referential integrity enforcement
  • Transaction Management: ACID-compliant operations ensuring data consistency

3. ETL Middleware

  • Query Engine: Parameterized SQL SELECT statements for dimensional aggregation
  • Data Transformation: Category mapping, temporal grouping, metric calculation
  • Export Mechanism: CSV generation for Excel consumption

4. Intelligence Layer (Excel MIS Frontend)

  • Computation Engine: Advanced Excel formulas, array functions, dynamic calculations
  • Visualization Suite: Executive dashboard with 8+ interactive charts
  • Automation: VBA macros for data refresh, report generation, and PDF export
  • AI Integration: LLM-based narrative insights from structured data summaries

Database Schema (MS Access Backend)

Entity Relationship Diagram (Text-Based)

┌─────────────────────────┐
│   Tbl_IncomeSources     │
│─────────────────────────│
│ • SourceID (PK)         │
│   SourceName            │
│   SourceType            │
│   IsActive              │
└─────────────────────────┘
             │
             │ 1
             │
             │ ∞
             ▼
┌─────────────────────────┐         ┌─────────────────────────┐
│   Tbl_Transactions      │ ∞    1  │   Tbl_Categories        │
│─────────────────────────│◄────────│─────────────────────────│
│ • TransactionID (PK)    │         │ • CategoryID (PK)       │
│   Date                  │         │   CategoryName          │
│   Description           │         │   CategoryGroup         │
│   Amount                │         │   BudgetLimit           │
│   Type (Income/Expense) │         │   IsDiscretionary       │
│   CategoryID (FK)       │         └─────────────────────────┘
│   PaymentMethodID (FK)  │
│   SourceID (FK)         │         ┌─────────────────────────┐
│   Notes                 │ ∞    1  │  Tbl_PaymentMethods     │
│   CreatedDate           │◄────────│─────────────────────────│
└─────────────────────────┘         │ • MethodID (PK)         │
                                    │   MethodName            │
                                    │   AccountNumber         │
                                    │   Institution           │
                                    │   IsActive              │
                                    └─────────────────────────┘

Table Definitions

Tbl_Transactions

Field Name Data Type Constraints Description
TransactionID AutoNumber Primary Key Unique transaction identifier
Date Date/Time Not Null, Indexed Transaction occurrence date
Description Short Text Not Null, Max 255 Transaction descriptor/merchant name
Amount Currency Not Null, >0 Monetary value (absolute)
Type Short Text Not Null, IN('Income','Expense') Transaction classification
CategoryID Number (Long) Foreign Key, Not Null Links to Tbl_Categories.CategoryID
PaymentMethodID Number (Long) Foreign Key, Not Null Links to Tbl_PaymentMethods.MethodID
SourceID Number (Long) Foreign Key, Null Links to Tbl_IncomeSources.SourceID (Income only)
Notes Long Text Null Additional context/annotations
CreatedDate Date/Time Not Null, Default=Now() Audit timestamp for record creation

Tbl_Categories

Field Name Data Type Constraints Description
CategoryID AutoNumber Primary Key Unique category identifier
CategoryName Short Text Not Null, Unique Display name (e.g., "Groceries", "Utilities")
CategoryGroup Short Text Not Null Hierarchical grouping (e.g., "Living Expenses")
BudgetLimit Currency Null Monthly budget allocation for category
IsDiscretionary Yes/No Not Null, Default=No Flag for discretionary vs. essential spending

Tbl_PaymentMethods

Field Name Data Type Constraints Description
MethodID AutoNumber Primary Key Unique payment method identifier
MethodName Short Text Not Null Display name (e.g., "Chase Sapphire", "Cash")
AccountNumber Short Text Null Last 4 digits or identifier
Institution Short Text Null Financial institution name
IsActive Yes/No Not Null, Default=Yes Current operational status

Tbl_IncomeSources

Field Name Data Type Constraints Description
SourceID AutoNumber Primary Key Unique income source identifier
SourceName Short Text Not Null Display name (e.g., "Salary - Employer X")
SourceType Short Text Not Null Classification (e.g., "Employment", "Freelance")
IsActive Yes/No Not Null, Default=Yes Current operational status

SQL Query Examples

Complex Aggregation Query for Excel Export

SELECT 
    FORMAT(t.Date, 'yyyy-mm') AS MonthYear,
    c.CategoryGroup,
    c.CategoryName,
    t.Type,
    pm.MethodName,
    COUNT(t.TransactionID) AS TransactionCount,
    SUM(IIF(t.Type = 'Expense', t.Amount, 0)) AS TotalExpenses,
    SUM(IIF(t.Type = 'Income', t.Amount, 0)) AS TotalIncome,
    AVG(IIF(t.Type = 'Expense', t.Amount, NULL)) AS AvgExpenseAmount,
    MAX(t.Amount) AS LargestTransaction,
    c.BudgetLimit,
    (SUM(IIF(t.Type = 'Expense', t.Amount, 0)) - c.BudgetLimit) AS BudgetVariance
FROM 
    ((Tbl_Transactions AS t
    INNER JOIN Tbl_Categories AS c ON t.CategoryID = c.CategoryID)
    INNER JOIN Tbl_PaymentMethods AS pm ON t.PaymentMethodID = pm.MethodID)
WHERE 
    t.Date >= #01/01/2024#
    AND t.Date < #01/01/2025#
GROUP BY 
    FORMAT(t.Date, 'yyyy-mm'),
    c.CategoryGroup,
    c.CategoryName,
    t.Type,
    pm.MethodName,
    c.BudgetLimit
ORDER BY 
    FORMAT(t.Date, 'yyyy-mm') DESC,
    TotalExpenses DESC;

Liquidity Analysis Query

SELECT 
    t.Date,
    SUM(IIF(t.Type = 'Income', t.Amount, -t.Amount)) 
        OVER (ORDER BY t.Date ROWS UNBOUNDED PRECEDING) AS RunningCashBalance,
    SUM(IIF(t.Type = 'Income', t.Amount, 0)) 
        OVER (ORDER BY t.Date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS Last30DaysIncome,
    SUM(IIF(t.Type = 'Expense', t.Amount, 0)) 
        OVER (ORDER BY t.Date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS Last30DaysExpenses
FROM 
    Tbl_Transactions AS t
ORDER BY 
    t.Date DESC;

Excel MIS Frontend (Dashboard & Calculations)

Workbook Structure

Sheet Name Purpose
Dashboard Executive overview with KPI cards, charts, and interactive controls
Raw_Data_Export Import target for CSV data from Access; source for all calculations
Calculations_Engine Pivot tables, helper columns, intermediate aggregations
Monthly_Summary Detailed month-over-month comparative analysis tables
Category_Analysis Deep-dive into spending patterns by category with trend analysis
AI_Insights AI-generated narrative summaries and anomaly detection reports
Config Configuration parameters, color schemes, budget targets, assumptions

Key Performance Indicators (KPIs) & Advanced Formulas

1. Dynamic Net Cash Flow (Period-over-Period)

Formula (Cell: Dashboard!B4)

=SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Income", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3) 
- SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Expense", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3)

Description: Calculates net cash position for selected date range, dynamically adjusting based on Config sheet parameters (StartDate: Config!B2, EndDate: Config!B3).

2. Liquidity Runway (Days of Cash Available)

Formula (Cell: Dashboard!B5)

=IFERROR(
    Config!$B$10 / 
    (SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Expense", Raw_Data_Export!$B:$B, ">="&EDATE(TODAY(),-30), Raw_Data_Export!$B:$B, "<="&TODAY()) / 30),
    "N/A"
)

Where: Config!B10 = Current Liquid Cash Balance
Description: Divides current cash by average daily burn rate (30-day trailing average) to determine sustainability in days.

3. Budget Adherence Variance (by Category)

Formula (Cell: Category_Analysis!E5, array formula)

=LET(
    CategoryName, Category_Analysis!$A5,
    ActualSpend, SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$E:$E, CategoryName, Raw_Data_Export!$D:$D, "Expense", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3),
    BudgetLimit, XLOOKUP(CategoryName, Config!$A$15:$A$30, Config!$B$15:$B$30, 0),
    Variance, (ActualSpend - BudgetLimit) / BudgetLimit,
    IF(BudgetLimit=0, "No Budget", TEXT(Variance, "0.0%"))
)

Description: Uses LET function for readable calculation; returns percentage over/under budget with error handling for uncategorized spending.

4. Savings Rate

Formula (Cell: Dashboard!B6)

=LET(
    TotalIncome, SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Income", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3),
    TotalExpenses, SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Expense", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3),
    NetIncome, TotalIncome - TotalExpenses,
    IF(TotalIncome=0, 0, NetIncome / TotalIncome)
)

Format: Percentage (0.0%)
Description: Measures capital retention efficiency; critical metric for wealth accumulation velocity.

5. Debt Servicing Ratio

Formula (Cell: Dashboard!B7)

=SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$E:$E, "Debt Service", Raw_Data_Export!$D:$D, "Expense", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3) 
/ SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$D:$D, "Income", Raw_Data_Export!$B:$B, ">="&Config!$B$2, Raw_Data_Export!$B:$B, "<="&Config!$B$3)

Threshold Alert: Conditional formatting triggers red if >0.40 (40%)
Description: Monitors debt burden relative to income; essential for creditworthiness and financial stress assessment.

6. Asset Growth Rate (Liquid Assets)

Formula (Cell: Dashboard!B8)

=(Config!$B$10 - Config!$B$11) / Config!$B$11

Where: Config!B10 = Current Period Liquid Assets, Config!B11 = Prior Period Liquid Assets
Format: Percentage (0.0%)
Description: Period-over-period asset accumulation rate; indicator of wealth trajectory.

7. Compound Annual Growth Rate (CAGR) - Long-Term Savings

Formula (Cell: Dashboard!B9)

=((Config!$B$12 / Config!$B$13)^(1/(Config!$B$14/12)) - 1)

Where:

  • Config!B12 = Current Total Savings/Investments
  • Config!B13 = Initial Savings/Investment Value
  • Config!B14 = Number of Months Tracking

Description: Annualized return rate on savings/investment portfolio; enables multi-year performance benchmarking.

8. Top 5 Expense Categories (Dynamic Ranking)

Formula (Cell: Dashboard!F5, spills to F5:G9)

=LET(
    ExpenseData, FILTER(Raw_Data_Export!$E:$G, (Raw_Data_Export!$D:$D="Expense") * (Raw_Data_Export!$B:$B>=Config!$B$2) * (Raw_Data_Export!$B:$B<=Config!$B$3)),
    CategoryColumn, INDEX(ExpenseData, , 1),
    AmountColumn, INDEX(ExpenseData, , 3),
    UniqueCats, UNIQUE(CategoryColumn),
    SummedAmounts, SUMIF(CategoryColumn, UniqueCats, AmountColumn),
    RankedData, SORT(HSTACK(UniqueCats, SummedAmounts), 2, -1),
    TAKE(RankedData, 5)
)

Description: Dynamic array formula leveraging FILTER, UNIQUE, SUMIF, and SORT to automatically identify and rank top spending categories without manual intervention.

9. Month-over-Month Expense Growth Rate

Formula (Cell: Monthly_Summary!D5)

=IFERROR(
    (C5 - C6) / C6,
    "N/A"
)

Where: C5 = Current Month Total Expenses, C6 = Prior Month Total Expenses
Conditional Format: Green if <0 (decreased), Red if >0.10 (>10% increase)
Description: Detects spending acceleration; early warning system for budget overruns.

10. Expense Concentration Index (Herfindahl-Hirschman)

Formula (Cell: Category_Analysis!B2)

=SUMPRODUCT((Category_Analysis!$C$5:$C$20 / SUM(Category_Analysis!$C$5:$C$20))^2)

Where: Column C contains category-wise expense totals
Interpretation: Values closer to 1 indicate high concentration (few dominant categories); closer to 0 indicates diversification
Description: Measures expense portfolio diversification; identifies dependency risks on specific spending areas.

VBA Automation Module

Data Refresh Macro

Sub RefreshDataFromAccess()
    '═══════════════════════════════════════════════════════════════
    ' LUMINA MIS - Automated Data Refresh from MS Access Backend
    ' Author: Financial Systems Architecture Team
    ' Version: 2.1.0
    ' Last Modified: 2024-01-15
    '═══════════════════════════════════════════════════════════════
    
    Dim dbPath As String
    Dim conn As Object
    Dim rs As Object
    Dim sqlQuery As String
    Dim wsData As Worksheet
    Dim startRow As Long
    
    On Error GoTo ErrorHandler
    
    ' Configuration
    dbPath = ThisWorkbook.Path & "\LUMINA_Database.accdb"
    Set wsData = ThisWorkbook.Sheets("Raw_Data_Export")
    startRow = 2 ' Header row = 1
    
    ' Clear existing data (preserve headers)
    wsData.Range("A2:K" & wsData.Rows.Count).ClearContents
    
    ' SQL Query for comprehensive data extraction
    sqlQuery = "SELECT t.TransactionID, t.Date, t.Description, t.Type, " & _
               "c.CategoryName, c.CategoryGroup, t.Amount, " & _
               "pm.MethodName, is.SourceName, t.Notes, t.CreatedDate " & _
               "FROM ((Tbl_Transactions AS t " & _
               "INNER JOIN Tbl_Categories AS c ON t.CategoryID = c.CategoryID) " & _
               "INNER JOIN Tbl_PaymentMethods AS pm ON t.PaymentMethodID = pm.MethodID) " & _
               "LEFT JOIN Tbl_IncomeSources AS is ON t.SourceID = is.SourceID " & _
               "ORDER BY t.Date DESC;"
    
    ' Establish ADO connection
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    
    ' Execute query
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sqlQuery, conn, 1, 3 ' adOpenKeyset, adLockOptimistic
    
    ' Transfer data to Excel
    If Not rs.EOF Then
        wsData.Range("A" & startRow).CopyFromRecordset rs
        
        ' Apply formatting
        With wsData.Range("B:B") ' Date column
            .NumberFormat = "yyyy-mm-dd"
        End With
        
        With wsData.Range("G:G") ' Amount column
            .NumberFormat = "$#,##0.00"
        End With
        
        ' Auto-fit columns
        wsData.Columns("A:K").AutoFit
    End If
    
    ' Cleanup
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    ' Refresh pivot tables
    Dim pt As PivotTable
    For Each pt In ThisWorkbook.Sheets("Calculations_Engine").PivotTables
        pt.RefreshTable
    Next pt
    
    ' Update timestamp
    ThisWorkbook.Sheets("Dashboard").Range("A1").Value = "Last Refreshed: " & Now()
    
    MsgBox "Data refresh completed successfully. " & wsData.Rows.Count - 1 & " transactions loaded.", vbInformation, "LUMINA MIS"
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error occurred during data refresh: " & Err.Description, vbCritical, "LUMINA MIS Error"
    If Not conn Is Nothing Then
        If conn.State = 1 Then conn.Close
    End If
End Sub

PDF Report Generation Macro

Sub ExportMonthlyReportToPDF()
    '═══════════════════════════════════════════════════════════════
    ' LUMINA MIS - Executive Monthly Report PDF Export
    '═══════════════════════════════════════════════════════════════
    
    Dim wsReport As Worksheet
    Dim pdfPath As String
    Dim reportMonth As String
    
    On Error GoTo ErrorHandler
    
    Set wsReport = ThisWorkbook.Sheets("Dashboard")
    
    ' Generate filename with current month/year
    reportMonth = Format(Date, "yyyy-mm")
    pdfPath = ThisWorkbook.Path & "\Reports\LUMINA_Executive_Summary_" & reportMonth & ".pdf"
    
    ' Ensure Reports directory exists
    If Dir(ThisWorkbook.Path & "\Reports", vbDirectory) = "" Then
        MkDir ThisWorkbook.Path & "\Reports"
    End If
    
    ' Set print area and quality
    With wsReport.PageSetup
        .PrintArea = "$A$1:$M$40"
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
    End With
    
    ' Export to PDF
    wsReport.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    
    MsgBox "Monthly report exported successfully to:" & vbCrLf & pdfPath, vbInformation, "LUMINA MIS"
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error during PDF export: " & Err.Description, vbCritical, "LUMINA MIS Error"
End Sub

Dashboard Visualization: The "Aurelius" Standard

Visual Design Philosophy

Color Palette: "Deep Slate & Emerald"

Color Name Hex Code RGB Values Usage
Primary Slate #2F4F4F (47, 79, 79) Background panels, chart fills
Accent Emerald #3D9970 (61, 153, 112) Positive metrics, income indicators
Alert Crimson #E74C3C (231, 76, 60) Budget overruns, negative variances
Neutral Titanium #AAAAAA (170, 170, 170) Grid lines, secondary text
Highlight Gold #F39C12 (243, 156, 18) Warning thresholds, attention items
Deep Charcoal #1C1C1C (28, 28, 28) Text, borders, emphasis
Clean White #FFFFFF (255, 255, 255) Primary text on dark backgrounds

Typography:

  • Headers: Calibri Light, 18pt, #1C1C1C
  • KPI Values: Calibri Bold, 28pt, #3D9970 (positive) / #E74C3C (negative)
  • Labels: Calibri Regular, 11pt, #AAAAAA

Chart Specifications

1. Executive KPI Cards (4 Cards)

Layout: 2x2 grid, top-left quadrant of dashboard

Card Specifications:

  • Dimensions: 250px (W) × 120px (H) per card
  • Background: Gradient from #2F4F4F to #3A5A5A
  • Border: 1px solid #AAAAAA

Card 1: Net Cash Flow (Current Month)

  • Metric: =Dashboard!B4 (formula reference)
  • Display: Large centered value with $ formatting
  • Trend Indicator: Small sparkline showing last 6 months
  • Color Logic: Green (#3D9970) if positive, Red (#E74C3C) if negative
  • Subtitle: "Current Period Performance"

Card 2: Savings Rate

  • Metric: =Dashboard!B6
  • Display: Percentage with 1 decimal place
  • Target Line: Horizontal reference at 20% (configurable)
  • Color Logic: Green if ≥20%, Yellow (#F39C12) if 10-19%, Red if <10%
  • Subtitle: "Capital Retention Efficiency"

Card 3: Liquidity Runway

  • Metric: =Dashboard!B5
  • Display: Integer value with "days" suffix
  • Alert Threshold: Visual warning if <60 days
  • Icon: Hourglass symbol (Wingdings 2, char 241)
  • Subtitle: "Projected Cash Sustainability"

Card 4: Total Liquid Assets

  • Metric: =Config!B10
  • Display: Currency format with K/M abbreviation
  • Growth Indicator: Month-over-month % change
  • Color Logic: Green for growth, Red for decline
  • Subtitle: "Current Liquid Position"

2. Dynamic Category Expense Breakdown (Donut Chart)

Type: Donut Chart (Office 365 Modern Chart Style)

Data Source: Pivot table from Calculations_Engine!B5:C20

Configuration:

  • Inner Radius: 60% (creating thick donut ring)
  • Data Labels: Category name + percentage, positioned outside
  • Color Scheme: Custom sequential palette from Emerald to Slate (7-color gradient)
  • Legend: Bottom position, horizontal layout
  • Interactive Element: Click to drill-down to Monthly_Summary sheet filtered by category

Special Features:

  • Top 6 Categories: Displayed individually in distinct colors
  • "Other" Segment: Aggregation of remaining categories in neutral gray
  • Hover Tooltip: Shows category name, amount ($), percentage, and budget comparison

3. Monthly Cash Flow Waterfall Chart

Type: Waterfall Chart (Inserted via Insert > Charts > Waterfall)

Data Structure:

Month Starting Balance Income Expenses Net Change Ending Balance
Jan 2024 $15,000 +$8,500 -$6,200 +$2,300 $17,300
Feb 2024 $17,300 +$8,700 -$6,800 +$1,900 $19,200

Visual Elements:

  • Starting Position: Dark slate (#2F4F4F) floating column
  • Income Additions: Emerald green (#3D9970) ascending segments
  • Expense Reductions: Crimson red (#E74C3C) descending segments
  • Net Position: Gold (#F39C12) cumulative total column
  • Connector Lines: Dashed gray lines showing flow between periods

Axes:

  • X-Axis: Monthly periods (last 12 months)
  • Y-Axis: Currency values with $K formatting
  • Gridlines: Light gray (#AAAAAA), 50% transparency

4. Expense Trend Line Chart (12-Month View)

Type: Line Chart with Markers and Smoothing

Series Configuration:

Series 1: Actual Monthly Expenses

  • Line Color: #E74C3C (Crimson)
  • Line Width: 2.5pt
  • Marker Style: Circle, 6pt diameter
  • Data Labels: Only for peaks/troughs (using VBA conditional labeling)

Series 2: 3-Month Moving Average

  • Line Color: #F39C12 (Gold)
  • Line Width: 1.5pt
  • Line Style: Dashed
  • No Markers

Series 3: Budget Target Line

  • Line Color: #AAAAAA (Neutral Gray)
  • Line Width: 1pt
  • Line Style: Dotted
  • Horizontal reference line

Chart Area:

  • Background: Gradient from white (top) to light slate (bottom)
  • Plot Area: White with 10% transparency
  • Title: "Monthly Expense Trajectory & Budget Compliance"

5. Budget vs. Actual Variance (Clustered Bar Chart)

Type: Clustered Bar Chart (Horizontal Orientation)

Data Layout:

  • Categories (Y-Axis): Top 10 expense categories
  • Values (X-Axis): Dollar amounts

Bar Series:

  • Series 1 (Budget): Light slate (#2F4F4F), 80% opacity
  • Series 2 (Actual): Solid emerald (#3D9970) if under budget, crimson (#E74C3C) if over budget
  • Gap Width: 50%
  • Overlap: -20% (slight separation for clarity)

Conditional Formatting:

  • Actual bars dynamically change color based on comparison to budget
  • Variance percentage displayed at end of each actual bar

Data Labels:

  • Format: "$#,##0" for currency values
  • Position: Outside end for actual, inside base for budget

6. Financial Health Radar Chart

Type: Radar Chart (filled)

Dimensions (6 axes at 60° intervals):

  1. Savings Rate (0-100% scale)
  2. Liquidity Strength (Days/100 scale, capped at 180)
  3. Budget Discipline (100% - Avg Variance %)
  4. Income Stability (StdDev inverse, normalized)
  5. Debt Health (100% - Debt Servicing Ratio)
  6. Asset Growth (CAGR × 10, normalized)

Visual Styling:

  • Fill Color: Emerald (#3D9970) with 30% transparency
  • Border: 2pt solid emerald (#3D9970)
  • Axis Lines: Gray (#AAAAAA), 1pt
  • Gridlines: Concentric circles at 20%, 40%, 60%, 80%, 100%
  • Target Overlay: Dotted line showing ideal profile (customizable in Config)

Interactivity:

  • Hover over data points shows underlying metric value and percentile rank

7. Calendar Heatmap (Daily Spending Intensity)

Type: Conditional Formatting on Date-Amount Matrix

Implementation:

  • Sheet: Dashboard, cells N5:AB35 (7 columns × 31 rows for max month)
  • Data Source: VLOOKUP pulling daily total expenses from Raw_Data_Export
  • Formula (Cell N5):
=IFERROR(SUMIFS(Raw_Data_Export!$G:$G, Raw_Data_Export!$B:$B, DATE($M$1, MONTH($M$2), ROW()-4), Raw_Data_Export!$D:$D, "Expense"), 0)

Conditional Formatting Rules:

  • Scale: 3-Color Scale
    • Minimum (0): White (#FFFFFF)
    • Midpoint (50% of max): Light gold (#FFE5A5)
    • Maximum: Deep crimson (#C0392B)
  • Cell Borders: Light gray (#D3D3D3)
  • Cell Dimensions: 40px × 40px (square cells)
  • Font: 9pt, centered, color auto-adjusts for contrast

Headers:

  • Row Headers (Left): Day numbers 1-31
  • Column Headers (Top): Month names (Jan-Dec)

8. Top Merchants/Payees (Horizontal Bar - Top 10)

Type: Horizontal Bar Chart

Data Source: Pivot table grouping by Description field, summing Amount where Type="Expense"

Specifications:

  • Bars: Gradient fill from dark slate (#2F4F4F) to medium slate (#4F6F6F)
  • Data Labels: Inside end, white text, "$#,##0" format
  • Axis: Category names on Y-axis (truncated at 25 characters)
  • Chart Title: "Largest Expense Recipients (Current Period)"
  • Sort Order: Descending by total amount

Interactive Control Elements

Date Range Slicer

  • Type: Timeline Slicer (Excel 2013+)
  • Connected To: PivotTables in Calculations_Engine
  • Default View: Last 12 months
  • Position: Top-right of Dashboard (cells M2:O4)

Category Group Filter

  • Type: Dropdown (Data Validation List)
  • Source: Config!$A$15:$A$30 (unique category groups)
  • Cell: Dashboard!B2
  • Linked Formula: All charts filter based on this selection
  • Default: "All Categories"

Report Period Toggle

  • Type: Form Control Button Group (3 options)
  • Options: "Monthly" | "Quarterly" | "Annual"
  • Action: Triggers VBA macro to recalculate aggregation periods across all sheets

AI Integration & Executive Insights Module

Data Summarization for LLM Input

Automated Summary Generation (VBA Function):

Function GenerateAISummaryPrompt() As String
    '═══════════════════════════════════════════════════════════════
    ' Constructs structured prompt for LLM analysis
    '═══════════════════════════════════════════════════════════════
    
    Dim prompt As String
    Dim wsCalc As Worksheet
    Dim currentMonth As String
    
    Set wsCalc = ThisWorkbook.Sheets("Monthly_Summary")
    currentMonth = Format(Date, "mmmm yyyy")
    
    prompt = "# FINANCIAL DATA ANALYSIS REQUEST" & vbCrLf & vbCrLf
    prompt = prompt & "## Context" & vbCrLf
    prompt = prompt & "You are analyzing personal financial data for " & currentMonth & ". "
    prompt = prompt & "Provide executive-level insights with actionable recommendations." & vbCrLf & vbCrLf
    
    prompt = prompt & "## Key Metrics" & vbCrLf
    prompt = prompt & "- **Net Cash Flow:** $" & Format(wsCalc.Range("C5").Value, "#,##0.00") & vbCrLf
    prompt = prompt & "- **Total Income:** $" & Format(wsCalc.Range("D5").Value, "#,##0.00") & vbCrLf
    prompt = prompt & "- **Total Expenses:** $" & Format(wsCalc.Range("E5").Value, "#,##0.00") & vbCrLf
    prompt = prompt & "- **Savings Rate:** " & Format(wsCalc.Range("F5").Value, "0.0%") & vbCrLf
    prompt = prompt & "- **Liquidity Runway:** " & wsCalc.Range("G5").Value & " days" & vbCrLf & vbCrLf
    
    prompt = prompt & "## Month-over-Month Comparison" & vbCrLf
    prompt = prompt & "- **Expense Change:** " & Format(wsCalc.Range("H5").Value, "0.0%") & vbCrLf
    prompt = prompt & "- **Income Change:** " & Format(wsCalc.Range("I5").Value, "0.0%") & vbCrLf & vbCrLf
    
    prompt = prompt & "## Top 5 Expense Categories (Current Month)" & vbCrLf
    Dim i As Integer
    For i = 1 To 5
        prompt = prompt & i & ". " & wsCalc.Range("J" & (4 + i)).Value & ": $" & _
                 Format(wsCalc.Range("K" & (4 + i)).Value, "#,##0.00") & _
                 " (" & Format(wsCalc.Range("L" & (4 + i)).Value, "0.0%") & " of total)" & vbCrLf
    Next i
    
    prompt = prompt & vbCrLf & "## Budget Variances (Categories Over Budget)" & vbCrLf
    Dim lastRow As Long
    lastRow = wsCalc.Cells(wsCalc.Rows.Count, "M").End(xlUp).Row
    
    For i = 5 To lastRow
        If wsCalc.Range("O" & i).Value > 0 Then ' Variance is positive (over budget)
            prompt = prompt & "- " & wsCalc.Range("M" & i).Value & ": $" & _
                     Format(wsCalc.Range("N" & i).Value, "#,##0.00") & " actual vs $" & _
                     Format(wsCalc.Range("O" & i).Value, "#,##0.00") & " budget (" & _
                     Format(wsCalc.Range("P" & i).Value, "+0.0%;-0.0%") & ")" & vbCrLf
        End If
    Next i
    
    prompt = prompt & vbCrLf & "## Analysis Requirements" & vbCrLf
    prompt = prompt & "1. Identify 2-3 key trends or patterns in spending behavior" & vbCrLf
    prompt = prompt & "2. Flag any anomalies or concerning deviations from historical norms" & vbCrLf
    prompt = prompt & "3. Provide 3-5 specific, actionable recommendations for optimization" & vbCrLf
    prompt = prompt & "4. Assess overall financial health trajectory (improving/stable/declining)" & vbCrLf
    prompt = prompt & "5. Highlight opportunities for capital redeployment or cost reduction" & vbCrLf & vbCrLf
    
    prompt = prompt & "Please provide analysis in a professional executive summary format, approximately 250-300 words."
    
    GenerateAISummaryPrompt = prompt
End Function

Example AI Prompt (Actual LLM Input)

# FINANCIAL DATA ANALYSIS REQUEST

## Context
You are analyzing personal financial data for January 2025. Provide executive-level insights with actionable recommendations.

## Key Metrics
- **Net Cash Flow:** $2,347.82
- **Total Income:** $8,750.00
- **Total Expenses:** $6,402.18
- **Savings Rate:** 26.8%
- **Liquidity Runway:** 94 days

## Month-over-Month Comparison
- **Expense Change:** +8.3%
- **Income Change:** +2.1%

## Top 5 Expense Categories (Current Month)
1. Housing & Utilities: $2,150.00 (33.6% of total)
2. Groceries & Dining: $1,287.45 (20.1% of total)
3. Transportation: $843.20 (13.2% of total)
4. Healthcare & Insurance: $625.00 (9.8% of total)
5. Subscriptions & Entertainment: $418.75 (6.5% of total)

## Budget Variances (Categories Over Budget)
- Groceries & Dining: $1,287.45 actual vs $1,000.00 budget (+28.7%)
- Subscriptions & Entertainment: $418.75 actual vs $300.00 budget (+39.6%)
- Miscellaneous: $215.33 actual vs $150.00 budget (+43.6%)

## Analysis Requirements
1. Identify 2-3 key trends or patterns in spending behavior
2. Flag any anomalies or concerning deviations from historical norms
3. Provide 3-5 specific, actionable recommendations for optimization
4. Assess overall financial health trajectory (improving/stable/declining)
5. Highlight opportunities for capital redeployment or cost reduction

Please provide analysis in a professional executive summary format, approximately 250-300 words.

Example AI-Generated Executive Insights

Generated Output (Claude/GPT-4):

LUMINA Executive Intelligence Report - January 2025

Overall Assessment: Stable with Optimization Opportunities

The January financial position demonstrates healthy fundamentals with a 26.8% savings rate—substantially above the recommended 20% threshold—and a comfortable 94-day liquidity runway. Net positive cash flow of $2,347.82 indicates effective capital retention despite modest expense growth.

Key Trends Identified:

  1. Lifestyle Inflation Acceleration: The 8.3% month-over-month expense increase significantly outpaces the 2.1% income growth, creating compression on savings capacity. This divergence, if sustained, could erode the strong savings rate within 6-8 months.

  2. Discretionary Spending Drift: Subscriptions & Entertainment exceeded budget by 39.6%, suggesting potential "subscription creep"—accumulation of recurring micro-charges that individually seem negligible but collectively represent $118.75 in excess spending. A subscription audit is warranted.

  3. Food Expenditure Elevation: Groceries & Dining surpassed budget by 28.7% ($287.45 overage), likely driven by increased dining out frequency or premium grocery selection. This category represents the largest optimization opportunity.

Anomaly Detection:

Miscellaneous category variance of +43.6% requires investigation. Historical patterns suggest this may indicate uncategorized impulse purchases or one-time expenses that should be properly classified for accurate trend analysis.

Strategic Recommendations:

  1. Immediate: Conduct subscription inventory using bank statement parsing; cancel redundant or low-value services. Potential monthly recovery: $50-$100.

  2. Short-term: Implement a dining budget with meal-planning protocols. Target: reduce category spending by 15% ($193/month) through increased home cooking.

  3. Medium-term: Establish automatic transfer of 30% of net cash flow to a high-yield savings account (currently ~4.5% APY) to prevent lifestyle inflation from eroding savings discipline.

  4. Analytical: Review and properly categorize all "Miscellaneous" transactions to improve budget accuracy and identify hidden spending patterns.

  5. Capital Redeployment: With liquidity exceeding 90 days, consider allocating $1,500-$2,000 of excess cash reserves into short-term Treasury bills or CD ladders for superior yield while maintaining accessibility.

Trajectory: Current path projects 22-24% savings rate by Q2 if expense acceleration continues unchecked. Implementing recommendations 1-3 could stabilize at 28-30% savings rate, accelerating wealth accumulation by approximately $400-$600 monthly.

End of AI Analysis

Project Outcomes & Core Competencies Demonstrated

Technical Competencies

Skill Domain Specific Capabilities Demonstrated
Relational Database Design Normalization to 3NF; ERD modeling; referential integrity; constraint enforcement
SQL Query Development Complex multi-table JOINs; window functions; aggregation; parameterized queries; performance optimization
Advanced Excel Modeling Dynamic array formulas (LET, FILTER, XLOOKUP); nested logic; volatile function management; formula auditing
Data Visualization Dashboard composition; chart type selection; color theory application; visual hierarchy; interactive controls
VBA/Macro Programming ADO database connectivity; error handling; recordset manipulation; PDF automation; user-defined functions
ETL Process Design Data pipeline architecture; transformation logic; validation rules; automated refresh mechanisms
Financial Modeling KPI construction; variance analysis; ratio calculation; trend projection; benchmarking
Business Intelligence MIS architecture; executive reporting; drill-down capability; data storytelling; actionable insight generation
AI/ML Integration Prompt engineering for LLMs; structured data summarization; narrative intelligence; anomaly detection
System Architecture Hybrid system design; component integration; data flow optimization; separation of concerns

Analytical Competencies

  • Financial Trend Analysis: Identification of spending patterns, cyclical behaviors, and trajectory forecasting
  • Risk Assessment: Liquidity monitoring, debt burden evaluation, concentration risk measurement
  • Performance Benchmarking: Period-over-period comparison, budget adherence tracking, goal progress monitoring
  • Root Cause Analysis: Variance investigation, category drill-down, merchant-level examination
  • Strategic Planning: Capital allocation optimization, savings rate enhancement, expense rationalization

Business Outcomes

  • Decision Quality Improvement: Data-driven financial choices replacing intuition-based spending
  • Visibility Enhancement: Transformation of opaque transaction data into transparent, comprehensible metrics
  • Efficiency Gains: Automation of manual data aggregation tasks, reducing analysis time by ~85%
  • Predictive Capability: Forward-looking runway calculations and trend projections enabling proactive adjustments
  • Accountability Mechanism: Budget tracking creates structured framework for fiscal discipline

Getting Started

Prerequisites

  • Microsoft Access: 2016 or later (ACE.OLEDB.12.0 provider required)
  • Microsoft Excel: 2016 or later (preferably Office 365 for dynamic array support)
  • Operating System: Windows 10/11 (for full VBA compatibility)
  • Storage: ~50MB for database and workbook files

Installation & Setup

  1. Clone Repository:

    git clone https://github.com/yourusername/LUMINA-Personal-Capital-Intelligence.git
cd LUMINA-Personal-Capital-Intelligence

  2. Database Configuration:

    • Open LUMINA_Database.accdb in Microsoft Access
    • Review and customize Tbl_Categories and Tbl_PaymentMethods with your personal data
    • Import historical transactions (use provided CSV template) or begin manual entry

  3. Excel Workbook Setup:

    • Open LUMINA_MIS_Dashboard.xlsm
    • Enable macros when prompted (required for data refresh functionality)
    • Update Config sheet with:
      • Your current liquid asset balances (Cell B10)
      • Budget targets by category (Range B15:B30)
      • Reporting preferences (date ranges, thresholds)

  4. Establish Database Connection:

    • In Excel VBA Editor (Alt+F11), update dbPath variable in RefreshDataFromAccess() macro
    • Ensure path points to your local LUMINA_Database.accdb file location
    • Test connection by running macro (Developer tab > Macros > RefreshDataFromAccess)

  5. Initial Data Load:

    • Execute RefreshDataFromAccess macro to populate Raw_Data_Export sheet
    • Verify data transfer and formula calculations on Dashboard sheet
    • Adjust chart date ranges if necessary for your data period

Customization Guidance

  • Adding Categories: Update Tbl_Categories in Access, then refresh Excel data
  • Modifying KPIs: Edit formulas in Dashboard sheet cells B4-B12
  • Chart Adjustments: Right-click charts > Edit Data > Modify source ranges
  • Color Scheme Changes: Update hex codes in chart formatting and conditional formatting rules
  • AI Prompts: Modify GenerateAISummaryPrompt() function to adjust analysis focus areas

Project Structure

LUMINA-Personal-Capital-Intelligence/
│
├── LUMINA_Database.accdb          # MS Access backend (relational data store)
│
├── LUMINA_MIS_Dashboard.xlsm      # Excel frontend with VBA macros
│   ├── Dashboard                  # Executive overview sheet
│   ├── Raw_Data_Export            # Data import target from Access
│   ├── Calculations_Engine        # Pivot tables & intermediate calcs
│   ├── Monthly_Summary            # Period-over-period analysis
│   ├── Category_Analysis          # Deep-dive spending breakdown
│   ├── AI_Insights                # LLM-generated narrative reports
│   └── Config                     # System parameters & settings
│
├── Templates/
│   ├── Transaction_Import_Template.csv
│   └── Category_Budget_Template.xlsx
│
├── Documentation/
│   ├── Database_Schema.pdf        # ERD and table specifications
│   ├── Formula_Reference.xlsx     # Complete formula documentation
│   └── VBA_Code_Documentation.md  # Macro explanations
│
├── Reports/                       # Auto-generated PDF exports (created by macro)
│
├── README.md                      # This file
├── LICENSE                        # MIT License
└── .gitignore

Future Enhancements Roadmap

Phase 1: Data Acquisition Automation (Q2 2025)

  • Plaid API integration for automated bank feed imports
  • OCR receipt scanning for automatic transaction entry
  • Email parser for transaction confirmation emails

Phase 2: Advanced Analytics (Q3 2025)

  • Machine learning model for expense category prediction
  • Anomaly detection algorithm for fraudulent transaction flagging
  • Time-series forecasting for cash flow projection (ARIMA/Prophet)

Phase 3: Expanded Visualization (Q4 2025)

  • Power BI integration for cloud-based dashboards
  • Mobile companion app (React Native) for on-the-go viewing
  • Tableau Public portfolio showcasing (anonymized data)

Phase 4: Portfolio Management Module (2026)

  • Investment tracking integration (stocks, bonds, crypto)
  • Net worth statement generation
  • Asset allocation rebalancing recommendations
  • Tax optimization scenario modeling

Contributing

This is a personal project portfolio piece, but suggestions and feedback are welcome:

  1. Open an Issue: Describe proposed enhancement or bug report
  2. Fork the Repository: Create your feature branch (git checkout -b feature/AmazingFeature)
  3. Commit Changes: Use conventional commit messages (git commit -m 'feat: Add investment tracking')
  4. Push to Branch: (git push origin feature/AmazingFeature)
  5. Open Pull Request: Provide detailed description of changes

Please ensure any code contributions:

  • Include inline comments for complex logic
  • Follow existing naming conventions
  • Update documentation accordingly
  • Test thoroughly before submission

License

This project is licensed under the MIT License - see the LICENSE file for details.

Summary: You are free to use, modify, and distribute this project with attribution. No warranty is provided.

Contact & Professional Inquiries

Project Maintainer: [Your Name]

LinkedIn: [Your LinkedIn Profile URL]
Email: [your.email@domain.com]
Portfolio: [yourportfolio.com]

Areas of Expertise:

  • Financial Systems Architecture
  • Business Intelligence & Analytics
  • Data Engineering & ETL
  • Advanced Excel & VBA Development
  • SQL Database Design
  • AI/ML Integration for Business Applications

For collaboration opportunities, consulting inquiries, or technical questions about this project, please reach out via LinkedIn or email.

Acknowledgments

  • Microsoft Office Development Team: For robust VBA and Excel capabilities
  • OpenAI/Anthropic: For AI models enabling advanced narrative insights
  • Financial Independence Community: For inspiration on systematic wealth tracking
  • Data Visualization Experts: Edward Tufte, Stephen Few for design principles

Version: 2.1.0
Last Updated: February 14, 2025
Status: Production-Ready Portfolio Showcase

⭐ If you found this project valuable, please consider starring the repository! ⭐

About

Hybrid financial intelligence system: MS Access backend (normalized schema, complex SQL) + Excel frontend (dynamic formulas, executive dashboards). Tracks income/expenses with AI-powered narrative insights. Showcases database design, ETL automation, advanced Excel modeling, VBA, and business intelligence principles.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors