#!/usr/bin/env python3 """ Finance Dashboard Generator Pulls live data from YNAB, Google Sheets, BTC price Outputs HTML to workspace/www/finance.html """ import json, os, time, base64, urllib.request, urllib.parse from datetime import datetime, timezone from pathlib import Path # ── helpers ────────────────────────────────────────────────────────────────── def get_sheets_token(): key_file = os.path.expanduser("~/.openclaw/secrets/google-calendar-sa.json") sa = json.load(open(key_file)) now = int(time.time()) header = base64.urlsafe_b64encode(json.dumps({"alg":"RS256","typ":"JWT"}).encode()).rstrip(b'=').decode() payload = base64.urlsafe_b64encode(json.dumps({ "iss": sa["client_email"], "scope": "https://www.googleapis.com/auth/spreadsheets.readonly", "aud": "https://oauth2.googleapis.com/token", "iat": now, "exp": now+3600 }).encode()).rstrip(b'=').decode() from cryptography.hazmat.primitives import serialization, hashes from cryptography.hazmat.primitives.asymmetric import padding from cryptography.hazmat.backends import default_backend key = serialization.load_pem_private_key(sa["private_key"].encode(), password=None, backend=default_backend()) sig = key.sign(f"{header}.{payload}".encode(), padding.PKCS1v15(), hashes.SHA256()) jwt = f"{header}.{payload}.{base64.urlsafe_b64encode(sig).rstrip(b'=').decode()}" data = f"grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion={jwt}".encode() return json.loads(urllib.request.urlopen(urllib.request.Request("https://oauth2.googleapis.com/token", data=data)).read())["access_token"] def fetch(url, headers={}): req = urllib.request.Request(url, headers=headers) return json.loads(urllib.request.urlopen(req, timeout=10).read()) def fmt_cad(n): if n is None: return "—" neg = n < 0 s = f"${abs(n):,.0f}" return f"-{s}" if neg else s def parse_currency(s): if not s or not str(s).strip(): return 0 clean = str(s).replace("$","").replace(",","").replace(" ","").strip() try: return float(clean) except: return 0 # ── data sources ────────────────────────────────────────────────────────────── def get_btc_price_cad(): try: data = fetch("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=cad") return data["bitcoin"]["cad"] except: return 91301 # fallback def get_ynab_data(): key = open(os.path.expanduser("~/.openclaw/secrets/ynab-api-key.txt")).read().strip() budget_id = "6c0a7a3f-6fa2-4729-9b88-d6ec3447fa69" biz_budget_id = "6b38a734-c3a4-4efd-bdfb-9d317f896711" h = {"Authorization": f"Bearer {key}"} accts = fetch(f"https://api.ynab.com/v1/budgets/{budget_id}/accounts", h)["data"]["accounts"] biz_accts = fetch(f"https://api.ynab.com/v1/budgets/{biz_budget_id}/accounts", h)["data"]["accounts"] def bal(accts, acct_id): for a in accts: if a["id"] == acct_id: return a["balance"] / 1000 return 0 # Personal accounts chq_5322 = bal(accts, "3deb48de-f487-4f05-b268-3bbfa8607060") chq_5421 = bal(accts, "e5ea972e-3c66-4e46-95f5-7e7d8c503b74") amex = bal(accts, "9568661e-a80e-4c13-9e49-a5b84f7427d1") td_visa = bal(accts, "072af67d-f487-4f05-b268-3bbfa8607060") rbc_loc = bal(accts, "35745d52-f487-4f05-b268-3bbfa8607060") volvo = bal(accts, "06700e9c-f487-4f05-b268-3bbfa8607060") bronco = bal(accts, "b38d40f0-f487-4f05-b268-3bbfa8607060") # Business accounts biz_chq = next((a["balance"]/1000 for a in biz_accts if "9694" in a.get("name","")), 0) biz_loc = next((a["balance"]/1000 for a in biz_accts if "8945" in a.get("name","")), 0) biz_amex = next((a["balance"]/1000 for a in biz_accts if "2006" in a.get("name","")), 0) scotialine= next((a["balance"]/1000 for a in biz_accts if "ScotiaLine" in a.get("name","")), 0) return { "chq_5322": chq_5322, "chq_5421": chq_5421, "amex": amex, "td_visa": td_visa, "rbc_loc": rbc_loc, "volvo": volvo, "bronco": bronco, "biz_chq": biz_chq, "biz_loc": biz_loc, "biz_amex": biz_amex, "scotialine": scotialine, } def get_sheet_data(): token = get_sheets_token() sheet_id = "190gcG6GtyPRBFRPNZoy9sysjTnnfKrdeqjPDZSS5rZQ" h = {"Authorization": f"Bearer {token}"} rows = fetch(f"https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/Networth!A1:Z60", h).get("values", []) def last_val(row): """Get last non-empty value from a data row (skip col 0 label, col 1 sublabel)""" vals = [parse_currency(c) for c in row[2:] if str(c).strip() and parse_currency(c) != 0] return vals[-1] if vals else 0 data = {} for row in rows: if len(row) < 2: continue label = row[1] if len(row) > 1 else row[0] v = last_val(row) data[label] = v return data # ── build HTML ──────────────────────────────────────────────────────────────── def build_dashboard(ynab, sheet, btc_price): ts = datetime.now(timezone.utc).astimezone().strftime("%B %d, %Y at %I:%M %p %Z") # BTC holdings btc_personal = 0 # update when Mike provides btc_business = 0.30224196 btc_total_cad = (btc_personal + btc_business) * btc_price # Assets cash_personal = max(0, ynab["chq_5322"]) + max(0, ynab["chq_5421"]) cash_biz = max(0, ynab["biz_chq"]) investments_mike = ( sheet.get("Stocks - Mike Wealthsimple Personal", 0) + sheet.get("Stocks - Mike Wealthsimple RRSP", 0) + sheet.get("Stocks - Mike Wealthsimple TFSA", 0) + sheet.get("Mike Wealthsimple TFSA", 0) + sheet.get("Mike Wealthsimple TFSA Emergency Fund", 0) + sheet.get("Mike IBM Lira (WealthSimple)", 0) + sheet.get("Mike RRSP (WealthSimple)", 0) ) investments_nicola = ( sheet.get("Nicola Wealthsimple RRSP", 0) + sheet.get("Nicola IBM Pension (LIRA Wealthsimple)", 0) + sheet.get("Nicola Wealthsimple TFSA", 0) ) resp = sheet.get("Kids RESP (WealthSimple)", 0) nicola_pension = sheet.get("Nicola Sunlife KC Pension ", 0) or sheet.get("Nicola Sunlife KC Pension", 0) kc_shares = sheet.get("Kimberly Clark Shares", 0) home_value = sheet.get("Personal residence", 0) or 2000000 vehicles = sheet.get("Vehicles", 0) or 20000 crypto = btc_total_cad total_assets = (cash_personal + cash_biz + investments_mike + investments_nicola + resp + nicola_pension + kc_shares + crypto + home_value + vehicles) # Debts mortgage = sheet.get("Home mortgage", 0) or 1375321 cc_amex = abs(min(0, ynab["amex"])) cc_td = abs(min(0, ynab["td_visa"])) rbc_loc = abs(min(0, ynab["rbc_loc"])) car_volvo = abs(min(0, ynab["volvo"])) car_bronco = abs(min(0, ynab["bronco"])) biz_loc = abs(min(0, ynab["biz_loc"])) biz_amex = abs(min(0, ynab["biz_amex"])) scotialine = abs(min(0, ynab["scotialine"])) total_debt = mortgage + cc_amex + cc_td + rbc_loc + car_volvo + car_bronco + biz_loc + biz_amex + scotialine net_worth = total_assets - total_debt # Debt payoff tracker (avalanche order) cc_total = cc_amex + cc_td cc_target = 42000 # approx peak cc_pct = max(0, min(100, (1 - cc_total/cc_target)*100)) html = f""" Mike & Nicola — Financial Dashboard

Mike & Nicola — Net Worth

{fmt_cad(net_worth)}
Last updated {ts}

💵 Cash & Banking

Joint Chequing 5322{fmt_cad(ynab['chq_5322'])}
Joint Chequing 5421{fmt_cad(ynab['chq_5421'])}
NMC Business Chequing{fmt_cad(ynab['biz_chq'])}
Total Cash{fmt_cad(cash_personal + cash_biz)}

📈 Mike's Investments

Wealthsimple RRSP{fmt_cad(sheet.get("Mike RRSP (WealthSimple)", 0))}
Wealthsimple TFSA{fmt_cad(sheet.get("Mike Wealthsimple TFSA", 0) + sheet.get("Mike Wealthsimple TFSA Emergency Fund", 0))}
IBM LIRA (Wealthsimple){fmt_cad(sheet.get("Mike IBM Lira (WealthSimple)", 0))}
Personal (Wealthsimple){fmt_cad(sheet.get("Stocks - Mike Wealthsimple Personal", 0))}
Total{fmt_cad(investments_mike)}

⚠️ Values from Dec 2025 sheet — update Wealthsimple balances

📈 Nicola's Investments

Sun Life KC Pension{fmt_cad(nicola_pension)}
Wealthsimple RRSP{fmt_cad(sheet.get("Nicola Wealthsimple RRSP", 0))}
IBM LIRA (Wealthsimple){fmt_cad(sheet.get("Nicola IBM Pension (LIRA Wealthsimple)", 0))}
Kimberly Clark Shares{fmt_cad(kc_shares)}
Total{fmt_cad(investments_nicola + nicola_pension + kc_shares)}

⚠️ Values from Dec 2025 sheet — update Wealthsimple balances

👨‍👩‍👧‍👦 Kids & Crypto

RESP (Ben & Ellie){fmt_cad(resp)}
BTC Business (0.302 BTC){fmt_cad(btc_total_cad)}
BTC Price (CAD){fmt_cad(btc_price)}

⚠️ RESP from Dec 2025 sheet — update monthly. Personal BTC unknown.

🏠 Real Estate & Vehicles

Primary Residence{fmt_cad(home_value)}
Vehicles (est.){fmt_cad(vehicles)}
Total{fmt_cad(home_value + vehicles)}

⚠️ Home value last updated Dec 2025

🎯 Debt Payoff — Avalanche Order

🔴 STAGE 1 — Credit Cards (19%) ACTIVE
Amex Personal{fmt_cad(cc_amex)}
TD Visa{fmt_cad(cc_td)}
Amex Business{fmt_cad(biz_amex)}
Total CC Debt{fmt_cad(cc_amex + cc_td + biz_amex)}
{cc_pct:.0f}% paid off
🟡 STAGE 2 — Unsecured LOCs (9-10%) NEXT
Unsecured LOC 8945{fmt_cad(biz_loc)}
RBC Credit Line{fmt_cad(rbc_loc)}
ScotiaLine{fmt_cad(scotialine)}
🚗 Car Loans (Volvo 9.99%, Bronco 3.49%) LATER
Volvo XC90{fmt_cad(car_volvo)}
Ford Bronco{fmt_cad(car_bronco)}
🏠 Mortgage LAST
Home Mortgage{fmt_cad(mortgage)}
Total Debt{fmt_cad(total_debt)}

📊 Summary

Total Assets{fmt_cad(total_assets)}
Total Debt{fmt_cad(total_debt)}
Net Worth{fmt_cad(net_worth)}
Excl. Home{fmt_cad(net_worth - home_value)}
⚠️ Investment values are from Dec 2025. Update Wealthsimple, RESP, and pension balances to get accurate net worth.
""" return html # ── main ────────────────────────────────────────────────────────────────────── if __name__ == "__main__": print("Fetching data...") btc = get_btc_price_cad() print(f"BTC: ${btc:,.0f} CAD") ynab = get_ynab_data() print(f"YNAB loaded") sheet = get_sheet_data() print(f"Sheet loaded: {len(sheet)} rows") html = build_dashboard(ynab, sheet, btc) out = Path(os.path.expanduser("~/.openclaw/workspace/www/finance.html")) out.parent.mkdir(exist_ok=True) out.write_text(html) print(f"Dashboard written to {out}")