Quartz 4

06 - Wealthfolio & Financial Automation Pipelines

4 min read

Wealthfolio & Financial Automation Pipelines

Overview

Wealthfolio (https://wealth.ganeshr.me) is our self-hosted personal wealth management platform. Rather than paying for third-party subscription apps or manually keying in transactions, a series of automated Python workers continuously sync household investment accounts, cash interest, workplace pensions, foreign currency loans, and real estate valuations directly into Wealthfolio.

1. Financial Architecture & Data Ingestion

flowchart TD
    subgraph DataSources ["Financial Sources"]
        T212_Ganesh["Trading 212 API (Ganesh ISA)"]
        T212_Rekha["Trading 212 API (Rekha ISA)"]
        Chip_App["Chip Cash ISA (4.42% AER)"]
        LG_Pension["L&G Pension (MYA CSV)"]
        LIC_Loan["LIC Home Loan (PDF Statement)"]
        Mumbai_RE["Mumbai Flats 208 & 209"]
    end

    subgraph AutomationEngine ["Server Workers (systemd timers)"]
        W1["sync_trading212.py (Every 4 Hours)"]
        W2["sync_chip_interest.py (Daily / 14th Monthly)"]
        W3["import_landg.py (Manual / Quarterly)"]
        W4["import_lic_loan.py (Annual / Ad-hoc)"]
        W5["update_properties.py (Local Helper)"]
    end

    subgraph CoreEngine ["Wealthfolio Engine"]
        DB["wealthfolio.db (SQLite Database)"]
        FX["FX:INR/GBP Daily Exchange Rates"]
        App["Wealthfolio UI (:8088 / wealth.ganeshr.me)"]
    end

    T212_Ganesh --> W1
    T212_Rekha --> W1
    Chip_App --> W2
    LG_Pension --> W3
    LIC_Loan --> W4
    Mumbai_RE --> W5

    W1 -->|Idempotent SQL Injection| DB
    W2 -->|Monthly Interest Posting| DB
    W3 -->|Fund Unit Accounting| DB
    W4 -->|Amortisation & FX Pairing| DB
    W5 -->|Asset Valuations| DB
    FX --> DB
    DB --> App

2. The 5 Financial Automation Pipelines

Pipeline 1: Trading 212 Multi-Account Sync

  • Script: /usr/local/bin/sync_trading212.py
  • Schedule: Every 4 hours via trading212-sync.timer (00:00, 04:00, 08:00, 12:00, 16:00, 20:00 UTC).
  • Supported Household Accounts:
    1. Ganesh ISA (23093683): Stocks & Shares ISA holding global equities and ETFs (VUAG.L, XNIF.L, RR.L, cash).
    2. Rekha ISA (38734606): Stocks & Shares ISA holding physical gold ETCs and equities (RMAP.L, GLDW.L, SGLP.L, SGLN.L, LHAD.DE, cash).
  • Key Mechanics:
    • Connects to Trading 212’s official REST API using HTTP Basic Authentication (API_KEY:API_SECRET).
    • Fetches real-time cash balances, deposits, withdrawals, and interest on uninvested cash.
    • Queries historical buy/sell orders and dividend payouts using cursor-based pagination.
    • Resolves Yahoo Finance tickers and automatically handles London Stock Exchange (LSE) pence (GBp) to pounds (GBP) unit price conversions.
    • Fully idempotent: Can be triggered repeatedly without creating duplicate transactions.

Pipeline 2: Chip Smart Cash ISA Compounding Worker

  • Script: /usr/local/bin/sync_chip_interest.py
  • Schedule: Daily at 06:00 UTC via chip-interest.timer (executes interest crediting on the 14th of each month).
  • Account: Rekha Rajput Smart Cash ISA (ClearBank FSCS-protected).
  • Key Mechanics:
    • Manages the native CASH account in Wealthfolio.
    • Calculates daily compound interest at 4.42% AER (Base 3.75% + 0.67% boost active until 11 July 2027).
    • Automatically creates an INTEREST transaction on the 14th of each month (e.g. verified £52.58 payout).
    • Includes a built-in projection calculator (--project) showing expected 12-month forward cashflows.

Pipeline 3: Legal & General Workplace Pension Importer

  • Script: /usr/local/bin/import_landg.py
  • Source: L&G Manage Your Account (MYA) CSV transaction statement.
  • Key Mechanics:
    • Manages dedicated “Legal & General Pension” retirement account.
    • Registers the unlisted workplace pension asset EY Growth Fund (A) (EY-GROWTH).
    • Parses payroll contributions, unit purchases, and unit deductions for annual management charges.
    • Converts pence NAV to GBP base currency and triggers full portfolio recalculation.

Pipeline 4: LIC Housing Finance Loan & Liability Tracker

  • Script: /usr/local/bin/import_lic_loan.py
  • Source: LICHFL Statement of Account PDF files.
  • Key Mechanics:
    • Manages Indian Home Loan (611100005416, Flat 209) as a native Wealthfolio LIABILITY asset.
    • Pairs the liability with the FX:INR/GBP currency pair, fetching historical daily exchange rates (INRGBP=X) from Yahoo Finance.
    • Reconstructs 5-year historical amortisation trajectory from initial disbursal (31/03/2021) to present.
    • Automatically reduces total net worth by the outstanding GBP-converted debt balance (Assets - Liabilities).

Pipeline 5: Mumbai Real Estate Properties (Flats 208 & 209)

  • Script: scripts/update_properties.py (Local offline helper, zero background VM overhead).
  • Tracked Properties (Bhayandar East, Mumbai MMR 401105):
    1. Flat 208 (380 sq ft Carpet): Acquired 2021 for ₹24,00,000. Current FMV: ₹27,36,000 (+14.0%).
    2. Flat 209 (595 sq ft Carpet): Acquired 2021 for ₹36,00,000. Current FMV: ₹42,84,000 (+19.0%).
  • Key Mechanics:
    • Stored natively in INR and converted dynamically using the live FX:INR/GBP rate.
    • Direct linkage to the LIC Housing Loan, yielding Net Real Estate Equity (~₹63.76 Lakhs / £49,295 debt-free).

3. Operations Runbook & Cheatsheet

Check Active Financial Timers

Verify that automated sync schedules are active on the server:

systemctl list-timers | grep -E 'trading212|chip'

Run Trading 212 Manual Sync Immediately

ssh -i ~/.ssh/oracle_vm.key ubuntu@144.21.49.153 "python3 /usr/local/bin/sync_trading212.py"

View 12-Month Chip Forward Cash Flow Projection

ssh -i ~/.ssh/oracle_vm.key ubuntu@144.21.49.153 "python3 /usr/local/bin/sync_chip_interest.py --project"

Check Mumbai Properties & Net Equity Status

python3 scripts/update_properties.py --status

Import Updated L&G Pension Statement

ssh -i ~/.ssh/oracle_vm.key ubuntu@144.21.49.153 "python3 /usr/local/bin/import_landg.py /path/to/statement.csv"

View Live Wealthfolio Server Logs

sudo docker logs -f wealthfolio

4. Next Steps & Related Links

Graph View

Backlinks