From carta-fund-admin
Fetch Form ADV Schedule D regulatory data and firm rollup for fund-level reporting. Use when asked about Form ADV, regulatory AUM, Schedule D, Form PF Section 1, or annual filing data.
npx claudepluginhub carta/pluginsThis skill uses the workspace's default tool permissions.
Fetch Form ADV Schedule D §7.B.(1) regulatory data for each Fund and SPV, plus an Item 5.D. firm-level rollup.
Conducts multi-round deep research on GitHub repos via API and web searches, generating markdown reports with executive summaries, timelines, metrics, and Mermaid diagrams.
Dynamically discovers and combines enabled skills into cohesive, unexpected delightful experiences like interactive HTML or themed artifacts. Activates on 'surprise me', inspiration, or boredom cues.
Generates images from structured JSON prompts via Python script execution. Supports reference images and aspect ratios for characters, scenes, products, visuals.
Fetch Form ADV Schedule D §7.B.(1) regulatory data for each Fund and SPV, plus an Item 5.D. firm-level rollup.
reporting_date is required (YYYY-MM-DD format, e.g. "2025-12-31") — ask if not providedlist_contexts and set_context to set the firmRun a single query using fetch("dwh:execute:query", {"sql": "..."}) that joins across five datasets to produce the full Form ADV view. The query produces two sections: per-fund detail and a firm rollup.
| Table | Purpose |
|---|---|
FUND_ADMIN.ALLOCATIONS | Fund list — Fund and SPV entity types only |
FUND_ADMIN.JOURNAL_ENTRIES | Balance sheet: cash, cost of investment, unrealized G/L, other assets, LOC |
FUND_ADMIN.MONTHLY_NAV_CALCULATIONS | NAV, commitments, distributions (inception + annual), DPI/TVPI/MOIC |
FUND_ADMIN.AGGREGATE_FUND_METRICS | LP/GP investor counts (current snapshot) |
FUND_ADMIN.AGGREGATE_INVESTMENTS | Active portfolio company count |
| Output Field | Source | Notes |
|---|---|---|
| Fair Market Value | Journal Entries | cost_of_investment + unrealized_gl |
| Cash | Journal Entries | Account types 1000–1099 |
| Other Assets | Journal Entries | Account types 1200–1899 |
| Total Gross Assets | Computed | FMV + Cash + Other Assets |
| LOC / Borrowings | Journal Entries | Accounts 2000 + 2001, negated (liabilities are credit) |
| Unfunded Commitments | NAV Calculations | GREATEST(commitment - contributions, 0) |
| Regulatory AUM | Computed | Total Gross Assets + Unfunded Commitments |
| Net Asset Value | NAV Calculations | ending_total_nav |
| Net AUM (Form PF) | Computed | NAV + Unfunded Commitments |
| Annual Subscriptions | NAV Calculations | SUM of monthly contributions in reporting year |
| Annual Distributions | NAV Calculations | SUM of monthly distributions in reporting year |
| LP/GP Investor Counts | Aggregate Fund Metrics | Current snapshot, latest row per fund |
| Portfolio Company Count | Aggregate Investments | Active investments only |
| DPI / TVPI / MOIC | NAV Calculations | At reporting date |
Use effective_date (accounting date) from journal entries. Do NOT filter on posted_date — it can drop valid entries and cause mismatches against the Carta balance sheet.
Execute this query with fetch("dwh:execute:query", ...), substituting the user's reporting date:
WITH
constants AS (
SELECT LAST_DAY('{reporting_date}'::DATE) AS reporting_date
),
funds AS (
SELECT
a.fund_uuid,
MAX(a.fund_name) AS fund_name,
MAX(a.entity_type_name) AS entity_type_name,
MAX(a.firm_name) AS firm_name,
c.reporting_date
FROM FUND_ADMIN.ALLOCATIONS a
CROSS JOIN constants c
WHERE a.entity_type_name IN ('Fund', 'SPV')
GROUP BY a.fund_uuid, c.reporting_date
),
je_balances AS (
SELECT
j.fund_uuid,
SUM(CASE WHEN j.account_type BETWEEN 1000 AND 1099 THEN j.amount ELSE 0 END) AS cash,
SUM(CASE WHEN j.account_type = 1100 THEN j.amount ELSE 0 END) AS cost_of_investment,
SUM(CASE WHEN j.account_type = 1101 THEN j.amount ELSE 0 END) AS unrealized_gl,
SUM(CASE WHEN j.account_type BETWEEN 1200 AND 1899 THEN j.amount ELSE 0 END) AS other_assets,
-SUM(CASE WHEN j.account_type IN (2000, 2001) THEN j.amount ELSE 0 END) AS loc_outstanding
FROM FUND_ADMIN.JOURNAL_ENTRIES j
INNER JOIN funds f ON j.fund_uuid = f.fund_uuid
WHERE j.effective_date <= f.reporting_date
GROUP BY j.fund_uuid
),
nav_data AS (
SELECT
n.fund_uuid,
n.ending_total_nav,
n.ending_lp_nav,
n.ending_gp_nav,
n.cumulative_commitment_amount,
n.cumulative_total_contributions,
n.cumulative_lp_contributions,
n.cumulative_gp_contributions,
n.cumulative_total_distributions,
GREATEST(n.cumulative_commitment_amount - n.cumulative_total_contributions, 0) AS unfunded_commitments,
n.total_tvpi,
n.total_moic,
n.total_dpi
FROM FUND_ADMIN.MONTHLY_NAV_CALCULATIONS n
INNER JOIN funds f ON n.fund_uuid = f.fund_uuid
WHERE n.is_firm_rollup = FALSE
AND n.month_end_date = f.reporting_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY n.fund_uuid ORDER BY n.last_refreshed_at DESC) = 1
),
annual_activity AS (
SELECT
n.fund_uuid,
SUM(n.total_contributions) AS annual_subscriptions,
SUM(n.total_distributions) AS annual_distributions
FROM FUND_ADMIN.MONTHLY_NAV_CALCULATIONS n
INNER JOIN funds f ON n.fund_uuid = f.fund_uuid
WHERE n.is_firm_rollup = FALSE
AND n.month_end_date BETWEEN DATE_TRUNC('year', f.reporting_date) AND f.reporting_date
GROUP BY n.fund_uuid
),
lp_counts AS (
SELECT
m.fund_uuid,
m.count_lps,
m.count_gps
FROM FUND_ADMIN.AGGREGATE_FUND_METRICS m
INNER JOIN funds f ON m.fund_uuid = f.fund_uuid
QUALIFY ROW_NUMBER() OVER (PARTITION BY m.fund_uuid ORDER BY m.last_refreshed_at DESC) = 1
),
portfolio_summary AS (
SELECT
ai.fund_uuid,
COUNT(DISTINCT ai.issuer_name) AS active_portfolio_companies
FROM FUND_ADMIN.AGGREGATE_INVESTMENTS ai
INNER JOIN funds f ON ai.fund_uuid = f.fund_uuid
WHERE ai.is_active_investment = TRUE
GROUP BY ai.fund_uuid
)
SELECT
'7.B.(1) Fund Detail' AS form_adv_section,
f.fund_name,
f.entity_type_name AS entity_type,
ROUND(je.cost_of_investment + je.unrealized_gl, 2) AS fair_market_value,
ROUND(je.cash, 2) AS cash,
ROUND(je.other_assets, 2) AS other_assets,
ROUND(je.cost_of_investment + je.unrealized_gl + je.cash + je.other_assets, 2) AS total_gross_assets,
ROUND(je.loc_outstanding, 2) AS loc_borrowings_outstanding,
ROUND(nav.unfunded_commitments, 2) AS unfunded_commitments,
ROUND(je.cost_of_investment + je.unrealized_gl + je.cash + je.other_assets + nav.unfunded_commitments, 2) AS regulatory_aum,
ROUND(nav.ending_total_nav, 2) AS net_asset_value,
ROUND(nav.ending_lp_nav, 2) AS lp_nav,
ROUND(nav.ending_gp_nav, 2) AS gp_nav,
ROUND(nav.ending_total_nav + nav.unfunded_commitments, 2) AS net_aum_form_pf,
ROUND(nav.cumulative_commitment_amount, 2) AS total_committed_capital,
ROUND(act.annual_subscriptions, 2) AS annual_subscriptions,
ROUND(act.annual_distributions, 2) AS annual_distributions,
ROUND(nav.cumulative_total_distributions, 2) AS total_distributions_since_inception,
lp.count_lps AS number_of_lp_investors,
lp.count_gps AS number_of_gp_investors,
ps.active_portfolio_companies,
ROUND(nav.total_dpi, 4) AS dpi,
ROUND(nav.total_tvpi, 4) AS tvpi,
ROUND(nav.total_moic, 4) AS moic
FROM funds f
LEFT JOIN je_balances je ON f.fund_uuid = je.fund_uuid
LEFT JOIN nav_data nav ON f.fund_uuid = nav.fund_uuid
LEFT JOIN annual_activity act ON f.fund_uuid = act.fund_uuid
LEFT JOIN lp_counts lp ON f.fund_uuid = lp.fund_uuid
LEFT JOIN portfolio_summary ps ON f.fund_uuid = ps.fund_uuid
UNION ALL
SELECT
'5.D. Firm Rollup',
MAX(f.firm_name),
'ALL FUNDS',
ROUND(SUM(je.cost_of_investment + je.unrealized_gl), 2),
ROUND(SUM(je.cash), 2),
ROUND(SUM(je.other_assets), 2),
ROUND(SUM(je.cost_of_investment + je.unrealized_gl + je.cash + je.other_assets), 2),
ROUND(SUM(je.loc_outstanding), 2),
ROUND(SUM(nav.unfunded_commitments), 2),
ROUND(SUM(je.cost_of_investment + je.unrealized_gl + je.cash + je.other_assets + nav.unfunded_commitments), 2),
ROUND(SUM(nav.ending_total_nav), 2),
ROUND(SUM(nav.ending_lp_nav), 2),
ROUND(SUM(nav.ending_gp_nav), 2),
ROUND(SUM(nav.ending_total_nav + nav.unfunded_commitments), 2),
ROUND(SUM(nav.cumulative_commitment_amount), 2),
ROUND(SUM(act.annual_subscriptions), 2),
ROUND(SUM(act.annual_distributions), 2),
ROUND(SUM(nav.cumulative_total_distributions), 2),
SUM(lp.count_lps),
SUM(lp.count_gps),
SUM(ps.active_portfolio_companies),
NULL, NULL, NULL
FROM funds f
LEFT JOIN je_balances je ON f.fund_uuid = je.fund_uuid
LEFT JOIN nav_data nav ON f.fund_uuid = nav.fund_uuid
LEFT JOIN annual_activity act ON f.fund_uuid = act.fund_uuid
LEFT JOIN lp_counts lp ON f.fund_uuid = lp.fund_uuid
LEFT JOIN portfolio_summary ps ON f.fund_uuid = ps.fund_uuid
ORDER BY form_adv_section DESC, fund_name
Separate the results into fund detail rows (form_adv_section = '7.B.(1) Fund Detail') and the firm rollup row (form_adv_section = '5.D. Firm Rollup').
| Fund | Type | Fair Market Value | Cash | Other Assets | Total Gross Assets | Unfunded Commitments | Regulatory AUM | NAV | LOC Outstanding |
|---|---|---|---|---|---|---|---|---|---|
| Fund I | Fund | $50,000,000 | $5,000,000 | $1,000,000 | $56,000,000 | $20,000,000 | $76,000,000 | $48,000,000 | $0 |
| Fund | LP Investors | GP Investors | Portfolio Cos | Annual Subscriptions | Annual Distributions | Total Distributions (Inception) | Total Committed | Unfunded |
|---|
| Fund | NAV | LP NAV | GP NAV | Net AUM (Form PF) | DPI | TVPI | MOIC |
|---|
| Amount | |
|---|---|
| Fair Market Value | $X |
| Cash | $X |
| Other Assets | $X |
| Total Gross Assets | $X |
| Unfunded Commitments | $X |
| Total Regulatory AUM (Form ADV) | $X |
| Net Asset Value | $X |
| Net AUM (Form PF) | $X |
$X,XXX,XXX with no decimalsX.XXx (e.g. 1.85x)— for null values