Finance Tracker
Multi-Team Accounting & Financial Management Platform
- Python
- Django
- MySQL
- pandas
- AWS EC2
A full-featured, production-deployed accounting and financial-management web application built with Django. It is designed for a multi-team app-development company that earns revenue in multiple currencies, runs payments across several ad networks, lends money to staff, and needs detailed per-team cost allocation and management reporting.
Role: Full-stack developer who designed the data model, built all backend business logic, server-rendered UI, import/export pipelines, role-based access control, and the CI/CD deployment pipeline.
Table of Contents
- The Problem
- What the Application Does
- Tech Stack
- Architecture Overview
- Core Features
- Data Model
- Notable Engineering Challenges & Solutions
- Security & Access Control
- DevOps & Deployment
- Project Stats
- Possible Future Improvements
The Problem
A growing software company faced several real-world accounting headaches that off-the-shelf tools could not solve cleanly:
- Multi-currency money flow: Bank and cash accounts are held in PKR, USD, and GBP. Income from ad networks arrives in USD/GBP, while salaries and local expenses are paid in PKR. Reporting needs a single, consistent base currency (PKR).
- Per-team profitability: The company runs multiple product teams, each with its own apps, revenue, headcount, and costs. Management needs to know which team is actually profitable, broken down by platform (iOS/Android) and ad network.
- Staff loans & advances: The company lends money to employees. These receivables must be tracked per borrower, with repayments (including salary deductions) reducing the outstanding balance.
- Manual bank reconciliation: Bank statements arrive as PDF and CSV files that previously had to be re-typed into spreadsheets by hand.
- Spreadsheet sprawl: Salaries, revenue sheets, per-head costs, and exchange rates lived in disconnected Excel files, making month-end consolidation slow and error-prone.
This application replaces that spreadsheet ecosystem with a single, role-controlled, auditable system.
What the Application Does
In one sentence: it is a double-aware, multi-currency bookkeeping system with team-level management accounting, receivables tracking, and bulk bank-statement import.
- Records every transaction (income, expense, transfer, owner's equity, journal entry) against an account, category, and team.
- Maintains running balances per account in both the account's currency and PKR.
- Generates bank statements, reports, and pivot analyses with rich filtering and Excel/CSV export.
- Tracks accounts receivable (loans to staff) with an automatically maintained sub-ledger.
- Consolidates team-wise revenue, salaries, per-head costs, and other expenses into a monthly management report ("Final Book").
- Imports transactions in bulk from CSV, Excel, and PDF bank statements.
- Manages fixed assets in a separate, permission-isolated module.
Tech Stack
| Layer | Technology |
|---|---|
| Language | Python 3 |
| Framework | Django 4.2 (LTS) |
| Database | MySQL 8 (mysqlclient driver, STRICT_TRANS_TABLES mode, utf8mb4) |
| Data processing | pandas, NumPy, openpyxl (Excel read/write) |
| HTTP / integrations | requests (external PDF-to-CSV API) |
| Frontend | Server-rendered Django templates, hand-written CSS (custom minimalist black-and-white theme), vanilla JavaScript, with no heavy frontend framework |
| Auth | Django authentication with a custom role-aware login view |
| CI/CD | GitHub Actions, SSH deploy to AWS EC2 |
| Server | Ubuntu EC2, systemd-managed service, WSGI |
Why these choices: Django's ORM, admin, migrations, and form layer cover most accounting CRUD with minimal boilerplate, and its strong transaction/atomicity guarantees matter for financial data. pandas and openpyxl make the heavy import/export work (the project's hardest feature area) tractable. Server-rendered templates keep the app simple to deploy and fast for a data-entry-heavy internal tool.
Architecture Overview
A classic Django project with a single feature-rich app (core):
finance_tracker/ # Project config
settings.py # MySQL, Asia/Karachi TZ, dd/mm/yyyy locale, env-driven config
urls.py # Root routing
auth_views.py # RoleAwareLoginView, redirects users by role after login
core/ # The application
models.py # ~20 models covering the financial domain
views.py # All request handling and business logic (~8,150 lines)
dashboard_view.py # Dashboard aggregation logic
dashboard_helpers.py # Period resolution, multi-currency sum helpers, sparklines
receivables.py # Receivables/loan ledger engine
receivables_views.py # Receivables UI
forms.py # Django ModelForms for every entity
admin.py # Customized Django admin
middleware.py # Role-based route restriction
decorators.py # @require_admin permission decorator
signals.py # Auto-sync receivable ledger on transaction save/delete
context_processors.py # Inject user role into every template
management/commands/ # Data-integrity CLI tools
migrations/ # 42 migrations; schema evolved iteratively
templates/ # ~55 server-rendered templates
Key design decisions
- PKR as the base currency. Every monetary value is stored both in its native currency and converted to PKR (
amount_pkr), so reports never have to convert on the fly and historical exchange rates are preserved per transaction. - Denormalized running balances. Each transaction stores the
available_balanceafter it posts (and the counter-party balance for transfers). This makes bank-statement views fast and gives an auditable point-in-time balance, at the cost of needing careful recalculation logic. - Signal-driven sub-ledgers. The receivables ledger is kept in sync automatically through Django
post_save/post_deletesignals, so business logic cannot be bypassed by any code path that creates a transaction.
Core Features
1. Accounts & Transactions
- CRUD for accounts of five types (cash, bank, credit card, savings, investment), each with a currency and opening balance.
- Five transaction types: income, expense, transfer, owner's equity, and journal entry.
- Each transaction carries both a transaction date and a posting date (the accounting-effective date), supports notes, attachments, and a borrower name for receivables.
- Automatic balance maintenance: current account balances and per-transaction running balances are recalculated as transactions are added, edited, or deleted.
- Cross-currency transfers automatically compute the counter-party amount via PKR parity and track balances on both accounts.
2. Dashboard
A configurable financial overview where each widget has its own time period (This Month, Last Month, This/Last Quarter, This Year, Year-to-Date). Widgets cover KPIs, profit & loss, cash position, drawdowns, receivables, advances, payables, currency exposure, and top movers. Includes multi-month sparkline trends for net profit, income, and expenses. All aggregation is multi-currency-safe: amounts are summed in PKR using a single ORM expression that coalesces stored or computed PKR values.
3. Bank Statement & Bulk Import
The most technically involved feature area:
- CSV / Excel bulk import of transactions, validating every row against existing accounts, categories, and teams with detailed per-row error reporting.
- Bank-statement import with automatic header detection and column mapping.
- PDF-to-CSV conversion: uploaded PDF bank statements are sent to an external conversion API and returned as import-ready CSV.
- Excel template generator: produces a formatted import template pre-loaded with a reference sheet of valid accounts, categories, and teams.
4. Receivables (Staff Loans)
- Loans disbursed to staff are detected from transactions in the Account Receivable / Current Asset category trees.
- Repayments (including salary deductions and loan recoveries posted as journal entries) reduce the outstanding balance.
- A
ReceivableLedgerEntrysub-ledger is maintained automatically via signals, so the receivables list always reflects reality. - Per-borrower outstanding balances are shown in both native currency and PKR, with a drill-down detail view per borrower.
5. Team Management Accounting
A suite of interconnected monthly reporting modules:
- Teamwise Revenue Sheet: revenue per team/sub-team split by platform (iOS/Android) and ad network.
- Income Sheet: expected vs. actual USD income, posting bank, source network, and receipt status.
- Per-Head Cost: monthly headcount and per-employee cost allocation.
- Teamwise Salaries: a three-step import workflow (select team/month, then upload, then review and confirm) that auto-detects employee-name and amount columns from messy CSV/Excel files.
- Other Expenses: ad-hoc per-team monthly costs.
- Network USD Rates: month-specific PKR/USD rates, kept separate for income vs. expense.
- Shareholders: ownership-percentage registry per team/sub-team.
- Final Book: consolidates all of the above into a single monthly management report.
6. Reports & Analytics
- Filtered reports by date, type, category, account, and team, with category summaries and CSV/Excel export.
- Pivot reports for multi-dimensional analysis across categories, teams, and time.
7. Assets Module
A permission-isolated module for tracking fixed assets, with its own asset categories and a legacy Excel importer (header-alias mapping for backward-compatible data migration). Access is restricted to the dedicated Asset Manager role.
8. Data-Integrity CLI Tools
Custom Django management commands:
find_duplicates: detects exact duplicate transactions by signature.update_opening_balance: safely changes an opening balance and recalculates every subsequent running balance (atomic, with--dry-runand--verify).validate_transactions: audits balance calculations and filter correctness across views.
Data Model
Roughly 20 models. Highlights:
| Model | Purpose |
|---|---|
UserProfile | Extends Django User with a role (Super Admin, Admin/Accountant, Asset Manager, Viewer) |
Currency | Supported currencies and their exchange rate to PKR |
Account | Bank/cash accounts; stores opening + current balance in native currency and PKR |
Transaction | The central ledger entry; stores native + PKR amounts and post-transaction running balances |
Category | Hierarchical (parent/child) income / expense / transfer / equity / journal categories |
Team / SubTeam | Organizational units used for cost and revenue allocation |
ReceivableLedgerEntry | Auto-maintained loan sub-ledger (disbursements & repayments per borrower) |
TransactionAttachment | File attachments on transactions |
RevenueSheetEntry | iOS/Android revenue per network, per team, per month |
IncomeSheetEntry | Expected vs. actual USD income per month |
PerHeadCostMonth / PerHeadCostEntry | Headcount and per-employee cost lines |
TeamwiseSalaryEntry | Imported monthly salary per employee |
NetworkIncomeExpenseMonthUsdRate | Month-specific PKR/USD rates |
OtherExpense | Ad-hoc per-team monthly expenses |
Shareholder | Ownership percentages per team/sub-team |
AssetCategory / AssetStock | Fixed-asset register |
The schema was evolved iteratively across 42 migrations, reflecting real, ongoing collaboration with the business users.
Notable Engineering Challenges & Solutions
1. Consistent multi-currency reporting
Challenge: accounts in PKR/USD/GBP need to roll up into one comparable figure, and historical rates must not change retroactively.
Solution: every transaction stores both its native amount and a frozen amount_pkr computed from the rate on its date. A reusable ORM expression coalesces stored PKR values (falling back to amount × rate for older rows), so all aggregations are correct and fast.
2. Accurate, auditable running balances
Challenge: deleting or back-dating a transaction invalidates the running balance of every transaction after it.
Solution: a recalculate_subsequent_balances() routine recomputes the affected chain, wrapped in atomic DB transactions; a dedicated management command performs opening-balance changes safely with dry-run and verification modes.
3. Loan tracking that cannot be bypassed
Challenge: a loan repayment can be entered through several UI paths (normal journal entry, salary deduction, loan recovery).
Solution: the receivable sub-ledger is rebuilt from post_save/post_delete signals rather than from view code, so any transaction touch keeps receivables correct.
4. Importing messy real-world spreadsheets
Challenge: bank statements and HR salary sheets arrive with inconsistent headers, encodings (UTF-8/UTF-16/Latin-1), and column orders.
Solution: pandas-based parsers with header normalization and fuzzy column matching to locate name/amount/date fields, plus a staged upload, review, confirm workflow so users catch mistakes before anything is written.
5. Eliminating manual PDF re-typing
Challenge: many banks only provide statements as PDFs.
Solution: integrated an external PDF-to-CSV conversion API, feeding directly into the existing bulk-import validation pipeline.
6. Role isolation for a sensitive module
Challenge: Asset Managers should see only the assets module, never financial data.
Solution: custom middleware that confines that role to asset and auth routes, complemented by a permission decorator and a role-aware login redirect.
Security & Access Control
- Four-tier role model: Super Admin, Admin/Accountant, Asset Manager, and Viewer (read-only).
@require_admindecorator guards all mutating finance operations.AssetManagerOnlyAssetsMiddlewareconfines the Asset Manager role to its module.- Role-aware login routes each user to the appropriate landing page after authentication.
- Context processor exposes the current user's role to every template for conditional UI.
- Configuration (secret key, DB credentials, timezone) is environment-variable driven for safe deployment.
- Locale is fixed to British English with dd/mm/yyyy dates and Asia/Karachi timezone to match the business context.
DevOps & Deployment
Continuous deployment via GitHub Actions. Every push to main triggers a workflow that:
- Connects to an AWS EC2 instance over SSH (key stored in GitHub Secrets).
- Pulls the latest code (
git reset --hard origin/main). - Installs dependencies, runs database migrations, and collects static files.
- Restarts the
systemd-managed application service.
This gives the team zero-touch, push-to-deploy releases on a self-managed Ubuntu server.
Project Stats
- ~12,000+ lines of application Python (views, models, forms, helpers).
- ~20 domain models, 42 migrations.
- ~55 server-rendered templates.
- ~25+ feature URLs/views spanning accounting, reporting, and management modules.
- 3 custom management commands for data integrity.
- Production-deployed with automated CI/CD.
Possible Future Improvements
- Add automated test coverage (the data-integrity commands partly substitute for this today).
- Introduce caching for heavy dashboard aggregations.
- Move the PDF-to-CSV step in-house to remove the external dependency.
- Add a REST/JSON API layer to support a future SPA or mobile client.
- Scheduled exchange-rate refresh from a live FX feed.
Built with Django · MySQL · pandas · deployed on AWS EC2 with GitHub Actions CI/CD.
