Portfolio Performance Monitoring Agent

Natural-language performance monitoring over a ~175-asset real-estate portfolio, built by reverse-engineering the schema out of a consultant’s working spreadsheet - with client figures never leaving the room.

1. The problem

An institutional investor’s portfolio baseline existed where these things always exist: a large, organically-grown Excel workbook. Thirteen sheets, roughly 175 asset rows, with calculation sheets, chart-source sheets, dividend tables, financial breakdowns, total-shareholder-return calculations in multiple fee variants, and lookup tables - all cross-linked, none documented.

The ask was to demonstrate how AI capability could support portfolio performance monitoring. The real engineering problem was upstream of any dashboard: you cannot build against a schema nobody has written down.

Two constraints made it harder:

• The time dimension wasn’t a column. There was no snapshot date field - the temporal axis was encoded in year-suffixed KPI column names. Any naive tabular ingestion would silently flatten years together.
• Confidentiality. This was live client financial data. The schema had to be extracted and documented while numerical values were deliberately excluded - structure captured, figures left behind.

2. Approach

Phase 1 - Privacy-constrained schema inference

Rather than hand-auditing thirteen sheets, I built a structured extraction prompt run against the workbook and the accompanying assessment deck, producing a formal schema specification: sheet-by-sheet role classification (data table / calculation / chart source / lookup / blank), primary-key identification, and a column-by-column inventory capturing inferred type, semantic role, percentage missing, distinct-value bucket, example labels, and a per-column confidence score.

The privacy rule was encoded into the extraction itself: asset names permitted as structural evidence, numerical values excluded by instruction, with sensitive categoricals explicitly marked redacted. The output was a reviewable specification document - reviewed and annotated with decisions before a line of application code was written.

Confidence scoring per column mattered in practice: it told me exactly which fields to verify with the engagement team rather than treating every inference as equally reliable.

Phase 2 - Agent implementation

The specification translated directly into agent configuration: typed domain model, the client’s own assessment framework pinned into the system prompt so the agent reasons in the client’s vocabulary, and tool-based data access.

The agent answers only through tools - never from model memory. A real tool-use loop iterates against the model API, dispatching tool calls through a registry and feeding results back, with a fallback to a cheaper model tier if the primary is unavailable. Five tools: schema inspection (column inventory, section grouping, sample values - designed to be called first), single-column aggregation (sum/avg/min/max/median/count with a filter expression language), group-by aggregation, structured query (select/where/order/limit), and asset lookup by ID or name.

Schema and distinct values are fetched before any filter is applied, which prevents the classic failure of an LLM confidently filtering on a category value that doesn’t exist. All aggregation is computed in code - the model chooses arguments and narrates results; it never does arithmetic.

Two details I’d point to as the real work. The filter layer implements a genuine operator language (equals, contains, greater/less-than, in, is-empty) with duplicate-column deduplication, because the source workbook is a messy real-world export. And the drill-down helper computes peer benchmarking properly: peer averages by investment strategy, percentile rank within the cohort, and equal-width histogram binning - so an asset is positioned against its peers rather than just displayed.

The tool descriptions also carry corrective guidance steering the model toward the right column among several similarly-named ones, including past a misspelled column heading in the client’s own workbook. Rather than silently renaming client columns (which would break every future re-import), the fix lives in the tool contract. Column typos are preserved deliberately: the workbook is the client’s, and the application stays agnostic to its quirks.

Phase 3 - Dashboard and delivery

A dashboard surface over the same data - summary statistics, status distribution, category and strategy breakdowns, asset-level drill-down - deployed to cloud static hosting with enterprise identity.

The privacy control that actually shipped

The runtime configuration hard-codes a synthetic workbook, with the real baseline file excluded from the container build. The schema tool itself reports the position honestly to the model: asset names are real; numeric values are synthetic.

So the demo shows genuine portfolio structure and real asset names with shuffled figures, and the client’s actual financial values were never deployed anywhere. That is the correct shape for a capability demo on live client data - and it’s enforced by build exclusion and configuration, not by remembering to swap a file.

3. What I’d highlight

• Recognising schema inference as the actual first problem. The instinct is to start building the dashboard; the correct move was to formally reverse-engineer and document the data contract first, with confidence scores flagging what needed human verification.
• Privacy encoded into the extraction step, not applied as a cleanup pass afterwards - structure extracted, client figures never captured.
• Catching the temporal encoding. Years living in column names rather than a date column is exactly the trap that silently corrupts every subsequent calculation.
• Tools-only agent grounding, with schema and distinct values fetched before filtering - eliminating hallucinated filter values by construction.
• Client vocabulary in the system prompt, so answers land in the client’s own framework rather than generic financial language.
• Reusable pattern. This build became the template for a subsequent government strategy-monitoring dashboard in an entirely different domain - the infrastructure and interaction model transferred; the domain logic was rebuilt.

4. Skills demonstrated

schema inference from unstructured spreadsheets · privacy-by-design data extraction · LLM tool-calling architecture · hallucination prevention via tool grounding · structured query agents · domain-framework prompt engineering · FastAPI · React dashboard development · Azure Static Web Apps · rapid client-facing prototyping