Financial Analysis Dashboard Documentation

Overview

This dashboard provides comprehensive financial analysis with multi-level drill-down capabilities from groups to individual accounts, with full accounting transparency where everything must balance.

Database Architecture

Core Tables

• roll_history: Ownership records tracking who owned which property when
  • Contains: lot_account, owner name, acquisition date, owner_group mapping
  • Tracks historical ownership changes for accurate debt attribution
• financial_transactions: All financial records
  • Types: I (Invoice), R (Receipt), J (Journal), M (Miscellaneous), B (Bank), F (Fees)
  • Each transaction linked to an account_code
• owner_groups_mapping: Categorizes owners into analysis groups
  • Groups: Onterran, Napoli, Associate, Quinn, Molloy
  • Excludes CC (body corporate) entities from analysis

Critical Views

For Historical Analysis

• historical_group_debt: Aggregates debt by group using all historical ownership
  • Accounts for properties that changed hands between groups
  • Ensures no double-counting when properties transfer within same group
  • This is why Onterran shows ~$36M correctly
• historical_owner_debt: Tracks debt by owner across all their historical properties
  • Includes properties they no longer own
  • Essential for complete accountability
• current_ownership: Latest owner for each property
  • Used for understanding current portfolio
  • DISTINCT ON (lot_account) to get most recent owner

For Financial Calculations

• account_balances: Aggregates account_debt_summary to eliminate duplicates
  • Critical fix: account_debt_summary has duplicates from ownership changes
  • SUM() grouped by account_code ensures single row per account
• account_with_current_owner: Joins aggregated balances with current ownership
  • Links financial data to current property owners
  • Calculates payment ratios

Data Integrity Issues Resolved

System Components

1. Data Generation (generate_data.py)

Generates hierarchical JSON data files:

python3 generate_data.py

Creates:

• data/groups.json - Top level summary
• data/owners/{group}.json - Owner lists per group
• data/accounts/{owner}.json - Account details per owner
• data/summary.json - Overall statistics

2. Chart Generation (generate_charts.py)

Creates comprehensive 4-panel analysis charts:

python3 generate_charts.py

Generates charts for:

• ALL 5 groups - Overview analysis
• ALL owners - Every owner gets full analysis (45+ charts)
• No filtering by balance - complete transparency

3. Web Dashboard (index.html)

Interactive three-level navigation:

• Level 1: Group cards showing total debt, payment rates
• Level 2: Owner cards within selected group
• Level 3: Account detail table for selected owner

Running the System

# Generate JSON data files
python3 generate_data.py

# Generate all charts (takes ~2-3 minutes for 45+ charts)
python3 generate_charts.py

# Start web server
python3 -m http.server 8000

# View dashboard
# Open http://localhost:8000 in web browser

Key Design Decisions

Why Historical Ownership?

• Properties change hands between groups
• Debt must be attributed to group that incurred it
• Current owner may not be responsible for historical debt
• Ensures accounting integrity

Why ALL Owners Get Charts?

• Accounting requires complete transparency
• Small balances today may have significant history
• Patterns in small accounts can reveal systemic issues
• Everything must balance - no exclusions

Real vs Adjusted Debt

• Real Debt: Invoiced - Paid (actual cash position)
• Adjusted Debt: After journal entries and adjustments
• Shows ~$16M in adjustments for transparency
• Both views presented side-by-side

Neutral Language

• "Payment Performance" not "Payment Compliance"
• "Financial Analysis" not "Debt Recovery"
• "Outstanding Balance" not "Debt Owed"
• Suitable for formal/legal presentation

File Structure

Troubleshooting

Duplicate Data

• Check account_debt_summary aggregation
• Verify DISTINCT ON clauses in ownership queries
• Ensure GROUP BY in aggregation queries

Missing Owners

• Check owner_group mapping in roll_history
• Verify entity != 'CC' filtering
• Check name normalization rules

Totals Don't Balance

• Compare historical vs current ownership views
• Check for accounts transferred between groups
• Verify date range filters in queries

Important Notes