""" Generate a synthetic Addepar / iCapital book for the reconciliation demo. Everything here is fictional: households, funds, policies and amounts. The script seeds a clean book, mirrors it into both systems, then injects the kinds of breaks advisors' operations teams actually chase each quarter. Run: python data/generate.py (writes CSVs next to this file) """ import csv import random from datetime import date, timedelta from pathlib import Path random.seed(1980) OUT = Path(__file__).parent AS_OF = date(2026, 9, 30) Q2 = date(2026, 6, 30) SECURITIES = [ # sec_key, name, asset_class, drawdown? ("SRG4", "Summit Ridge Growth Fund IV", "Private Equity", True), ("HBP3", "Harborview Buyout Partners III", "Private Equity", True), ("GDL2", "Granite Direct Lending Fund II", "Private Credit", True), ("MSC1", "Meridian Senior Credit Fund", "Private Credit", True), ("WMF1", "Wasatch Multifamily Fund I", "Real Estate", True), ("KIF2", "Keystone Infrastructure Fund II", "Infrastructure", True), ("NBMS", "Northbeam Multi-Strategy Ltd", "Hedge Fund", False), ("CIRT", "Cascade Industrial Income Trust", "Real Estate", False), ("AC29", "Autocallable Note on S&P 500 (2029)", "Structured Note", False), ("BR28", "Buffered Note on Russell 2000 (2028)", "Structured Note", False), ("PPLI", "Private Placement Life Insurance Policy", "PPLI", False), ("PPVA", "Private Placement Variable Annuity", "PPVA", False), ("FIA7", "Fixed Indexed Annuity (7-yr)", "Fixed Indexed Annuity", False), ("RILA", "Registered Index-Linked Annuity", "RILA", False), ] ACCOUNTS = [ ("ACC-1001", "Alder Family Trust"), ("ACC-1002", "Brennan Revocable Trust"), ("ACC-1003", "Castillo Holdings LLC"), ("ACC-1004", "Delacroix Family Foundation"), ("ACC-1005", "Everly Irrevocable Trust"), ("ACC-1006", "Foss Joint Account"), ] def money(x): return round(x, 2) def build(): secmaster, icap_pos, add_pos, icap_act, add_txn = [], [], [], [], [] for key, name, cls, drawdown in SECURITIES: secmaster.append({"sec_key": key, "name": name, "asset_class": cls, "icapital_id": f"IC-{key}", "addepar_id": f"AD-{key}"}) ev = 0 for acct, _ in ACCOUNTS: held = random.sample(SECURITIES, 5) for key, name, cls, drawdown in held: commitment = random.choice([250_000, 500_000, 750_000, 1_000_000, 1_500_000]) called = 0.0 dists = [] if drawdown: n_calls = random.randint(2, 5) for i in range(n_calls): amt = money(commitment * random.uniform(0.08, 0.18)) d = date(2024, 3, 1) + timedelta(days=150 * i + random.randint(0, 40)) ev += 1 icap_act.append({"event_id": f"EV-{ev:05d}", "account_id": acct, "icapital_id": f"IC-{key}", "type": "Capital Call", "amount": amt, "notice_date": d.isoformat(), "due_date": (d + timedelta(days=10)).isoformat()}) called += amt for i in range(random.randint(0, 2)): amt = money(called * random.uniform(0.02, 0.06)) typ = random.choice(["Distribution - Income", "Distribution - Return of Capital"]) d = date(2025, 6, 15) + timedelta(days=120 * i) ev += 1 icap_act.append({"event_id": f"EV-{ev:05d}", "account_id": acct, "icapital_id": f"IC-{key}", "type": typ, "amount": amt, "notice_date": d.isoformat(), "due_date": d.isoformat()}) dists.append((typ, amt)) nav = money(called * random.uniform(0.95, 1.35) - sum(a for _, a in dists)) elif cls in ("PPLI", "PPVA", "Fixed Indexed Annuity", "RILA"): ev += 1 d = date(2024, 1, 10) + timedelta(days=random.randint(0, 200)) icap_act.append({"event_id": f"EV-{ev:05d}", "account_id": acct, "icapital_id": f"IC-{key}", "type": "Premium", "amount": commitment, "notice_date": d.isoformat(), "due_date": d.isoformat()}) called = commitment nav = money(commitment * random.uniform(1.02, 1.18)) else: # evergreen fund or structured note: fully funded at purchase ev += 1 d = date(2024, 2, 1) + timedelta(days=random.randint(0, 300)) icap_act.append({"event_id": f"EV-{ev:05d}", "account_id": acct, "icapital_id": f"IC-{key}", "type": "Subscription", "amount": commitment, "notice_date": d.isoformat(), "due_date": d.isoformat()}) called = commitment nav = money(commitment * random.uniform(0.92, 1.22)) nav_date = AS_OF if cls != "Private Equity" else Q2 # PE marks lag a quarter icap_pos.append({"account_id": acct, "icapital_id": f"IC-{key}", "commitment": commitment, "called_to_date": money(called), "nav": nav, "nav_date": nav_date.isoformat()}) add_pos.append({"account_id": acct, "addepar_id": f"AD-{key}", "commitment": commitment, "market_value": nav, "valuation_date": nav_date.isoformat()}) # Mirror every activity event into Addepar as a transaction (clean state). for e in icap_act: add_txn.append({"txn_id": f"TX-{e['event_id'][3:]}", "account_id": e["account_id"], "addepar_id": e["icapital_id"].replace("IC-", "AD-"), "type": e["type"], "amount": e["amount"], "trade_date": e["due_date"], "source_ref": e["event_id"]}) return secmaster, icap_pos, add_pos, icap_act, add_txn def inject(secmaster, icap_pos, add_pos, icap_act, add_txn): """Introduce realistic breaks. Returns a log of what was injected (answer key).""" log = [] idx = {(p["account_id"], p["addepar_id"]): p for p in add_pos} def apos(ic): return idx[(ic["account_id"], ic["icapital_id"].replace("IC-", "AD-"))] drawdown = [p for p in icap_pos if any(e["icapital_id"] == p["icapital_id"] and e["account_id"] == p["account_id"] and e["type"] == "Capital Call" for e in icap_act)] random.shuffle(drawdown) # 1. New capital calls in iCapital not yet booked in Addepar (4) for p in drawdown[:4]: ev = f"EV-9{len(log):04d}" amt = money(p["commitment"] * 0.07) icap_act.append({"event_id": ev, "account_id": p["account_id"], "icapital_id": p["icapital_id"], "type": "Capital Call", "amount": amt, "notice_date": "2026-09-12", "due_date": "2026-09-24"}) p["called_to_date"] = money(p["called_to_date"] + amt) log.append(("Unbooked capital call", p["account_id"], p["icapital_id"])) # 2. ROC distribution booked as income in Addepar (2) roc = [t for t in add_txn if t["type"] == "Distribution - Return of Capital"][:2] for t in roc: t["type"] = "Distribution - Income" log.append(("Distribution misclassified", t["account_id"], t["addepar_id"])) # 3. Duplicate transaction in Addepar (1) hit = {(p["account_id"], p["icapital_id"].replace("IC-", "AD-")) for p in drawdown[:4]} hit |= {(t["account_id"], t["addepar_id"]) for t in roc} dup = dict(next(t for t in add_txn if t["type"] == "Capital Call" and (t["account_id"], t["addepar_id"]) not in hit)) dup["txn_id"] = dup["txn_id"] + "-D" add_txn.append(dup) log.append(("Duplicate transaction", dup["account_id"], dup["addepar_id"])) # 4. Valuation lag: Addepar still on prior quarter mark (3, incl. one PPLI) lag = [p for p in icap_pos if p["nav_date"] == AS_OF.isoformat() and p["icapital_id"] != "IC-PPLI"][:2] lag += [p for p in icap_pos if p["icapital_id"] == "IC-PPLI"][:1] for p in lag: a = apos(p) a["market_value"] = money(p["nav"] * 0.97) a["valuation_date"] = Q2.isoformat() log.append(("Valuation lag", p["account_id"], p["icapital_id"])) # 5. Rounding differences under $1 (2) rnd = [p for p in icap_pos if p not in lag][5:7] for p in rnd: apos(p)["market_value"] = money(p["nav"] + 0.37) log.append(("Rounding", p["account_id"], p["icapital_id"])) # 6. True value break on the same valuation date (1 fund, 1 annuity) vb = [p for p in icap_pos if p not in lag + rnd and p["icapital_id"] in ("IC-CIRT", "IC-NBMS", "IC-GDL2")][:1] vb += [p for p in icap_pos if p not in lag + rnd and p["icapital_id"] in ("IC-FIA7", "IC-RILA")][:1] for p in vb: apos(p)["market_value"] = money(p["nav"] * 1.042) log.append(("Value break", p["account_id"], p["icapital_id"])) # 7. Commitment keyed wrong in Addepar (1) cm = [p for p in icap_pos if p not in lag + rnd + vb][2] apos(cm)["commitment"] = cm["commitment"] + 50_000 log.append(("Commitment mismatch", cm["account_id"], cm["icapital_id"])) # 8. Stale GP statement: iCapital NAV older than 120 days (1) st = [p for p in icap_pos if p["icapital_id"][3:] in ("HBP3", "SRG4", "MSC1", "GDL2", "WMF1")] st = [p for p in st if p not in lag + rnd + vb + [cm] + drawdown[:4] and (p["account_id"], p["icapital_id"].replace("IC-", "AD-")) not in hit | {(dup["account_id"], dup["addepar_id"])}][:1] for p in st: p["nav_date"] = "2026-03-31" apos(p)["valuation_date"] = "2026-03-31" log.append(("Stale valuation", p["account_id"], p["icapital_id"])) # 9. New iCapital subscription with no Addepar security mapping or position (1) secmaster.append({"sec_key": "LCV5", "name": "Lakeshore Co-Invest Vehicle V", "asset_class": "Private Equity", "icapital_id": "IC-LCV5", "addepar_id": ""}) icap_pos.append({"account_id": "ACC-1003", "icapital_id": "IC-LCV5", "commitment": 300_000, "called_to_date": 120_000.0, "nav": 118_450.0, "nav_date": Q2.isoformat()}) icap_act.append({"event_id": "EV-99990", "account_id": "ACC-1003", "icapital_id": "IC-LCV5", "type": "Capital Call", "amount": 120_000.0, "notice_date": "2026-08-01", "due_date": "2026-08-11"}) log.append(("Unmapped security", "ACC-1003", "IC-LCV5")) # 10. Mapped fund held in iCapital but position never set up in Addepar (1) icap_pos.append({"account_id": "ACC-1006", "icapital_id": "IC-KIF2", "commitment": 400_000, "called_to_date": 60_000.0, "nav": 61_200.0, "nav_date": AS_OF.isoformat()}) icap_act.append({"event_id": "EV-99991", "account_id": "ACC-1006", "icapital_id": "IC-KIF2", "type": "Capital Call", "amount": 60_000.0, "notice_date": "2026-07-20", "due_date": "2026-07-30"}) if ("ACC-1006", "AD-KIF2") in idx: # keep the scenario valid regardless of sampling add_pos.remove(idx[("ACC-1006", "AD-KIF2")]) add_txn[:] = [t for t in add_txn if not (t["account_id"] == "ACC-1006" and t["addepar_id"] == "AD-KIF2")] icap_pos[:] = [p for p in icap_pos if not (p["account_id"] == "ACC-1006" and p["icapital_id"] == "IC-KIF2" and p["called_to_date"] != 60_000.0)] icap_act[:] = [e for e in icap_act if not (e["account_id"] == "ACC-1006" and e["icapital_id"] == "IC-KIF2" and e["event_id"] != "EV-99991")] log.append(("Missing position", "ACC-1006", "IC-KIF2")) return log def write(name, rows): with open(OUT / name, "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(rows[0].keys())) w.writeheader() w.writerows(rows) if __name__ == "__main__": sm, ip, ap, ia, at = build() log = inject(sm, ip, ap, ia, at) write("security_master.csv", sm) write("accounts.csv", [{"account_id": a, "household": h} for a, h in ACCOUNTS]) write("icapital_positions.csv", ip) write("icapital_activity.csv", ia) write("addepar_positions.csv", ap) write("addepar_transactions.csv", at) write("injected_breaks.csv", [{"break": b, "account_id": a, "icapital_id": s} for b, a, s in log]) print(f"{len(ip)} iCapital positions, {len(ia)} events, {len(log)} injected breaks")