Spaces:
Running
Running
| """ | |
| 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") | |