How ClearLedger Analytics Optimizes Portfolio Weights

Executive Summary

For decades, wealth advisors have faced a persistent professional friction: defending portfolio optimizations driven by opaque, black‑box algorithms. When a model shifts asset weights overnight, explaining the underlying rationale to an astute client—or verifying that the output aligns strictly with fiduciary intent—often boils down to trusting the software.

ClearLedger Analytics rejects this paradigm. Built specifically for advisors who demand absolute clarity, our optimization engine replaces stochastic solvers and hidden parameters with a fully deterministic, mathematically grounded workflow. By fusing institutional‑grade data management with an asymmetry‑driven objective function, ClearLedger Analytics transforms portfolio construction from an act of faith into a fully explainable, repeatable science.

The Advisor’s Dilemma: The Black‑Box Problem

Traditional portfolio optimizers—whether legacy desktop software or cloud‑based robo‑advisory engines—frequently operate as closed systems. Inputs cross the threshold, proprietary algorithms wrestle with unconstrained variance or rigid mean‑variance frameworks, and target weights emerge on the other side.

This lack of transparency introduces three critical operational risks for advisory practices:

• The Defensibility Gap: Advisors cannot justify solver drift or unexplained penalty matrices when clients ask why core positions were trimmed.

• Instability: Traditional mean‑variance optimization is notoriously sensitive to small input changes, producing erratic turnover and unpredictable weight shifts.

• Misalignment with Behavioral Reality: Sharpe‑maximizing or variance‑minimizing portfolios often ignore liquidity needs, real‑world constraints, and asymmetric upside opportunities.

ClearLedger Analytics eliminates these vulnerabilities. Every movement of capital is governed by visible business rules, calculated via transparent matrix operations, and backed by high‑performance architecture that guarantees identical inputs will always yield identical, deterministic outputs.

1. Data Foundation: Adjusted‑Close Series

ClearLedger Analytics begins with historical price data sourced from Yahoo Finance (yfinance), utilizing adjusted‑close values to automatically account for:

• Dividends

• Splits

• Corporate actions

From this clean price history, the engine derives two core statistical inputs:

Expected Average Return (ExpR)

A true measure of total historical performance (price plus dividends) feeding directly into the report and Asym Score calculation.

Risk (Volatility)

Derived from the distribution of returns to build the foundational covariance matrix used in quadratic risk calculations.

2. The Technology Leap: VertiPaq Storage

Once return series and metrics are derived, data is loaded into the Excel VertiPaq engine—the same in‑memory column‑store technology powering Microsoft’s enterprise analytics stack.

This architecture transforms Excel into a high‑performance analytical database, enabling:

• Column‑store compression that drastically reduces memory overhead.

• In‑memory analytics operating at RAM speed for real‑time slicing without recomputation.

• Guaranteed determinism with zero randomness or solver drift.

By moving away from cell‑based bottlenecks, covariance matrices build instantly, and Asym Score components recalculate without lag.

3. Asym Score: The Objective Function

Instead of minimizing variance or maximizing Sharpe, ClearLedger Analytics optimizes for return asymmetry. The proprietary Asym Score evaluates risk‑adjusted performance by combining three independent behavioral signals into a single scalar:

• Sortino Ratio: Measures upside potential while isolating downside penalty.

• Drift Percentile: Applies a penalty (β × Drift) to unstable return profiles.

• Insider Conviction (ICvx200): Applies an upward boost when conviction metrics meet or exceed threshold (≥ 1).

The formula implemented inside the workbook:

Asym Score = Sortino × (1 − β × Drift) + InsiderBoost

By maximizing the Asym Score rather than traditional mean‑variance metrics, the engine produces portfolios with stronger upside characteristics, controlled downside risk, and better behavioral stability.

How ClearLedger Optimizes Portfolio Weights (The Solver)

ClearLedger Analytics optimizes portfolio weights using a deterministic solver that adjusts allocations to maximize the Asym Score while respecting all advisor‑defined constraints. The solver does not use randomness, stochastic search, or hidden penalty matrices. Instead, it evaluates feasible weight combinations inside a strictly defined constraint space and selects the allocation that produces the strongest asymmetric return profile.

The solver operates on three core components:

• Objective Function: Maximize the Asym Score.

• Constraint Set: Enforce Min/Max bounds, frozen holdings, cash targets, and total capacity.

• Risk Engine: Compute risk deterministically using Excel’s MMULT with the quadratic form wᵀ Σ w.

The Iterative Evaluation Loop

Because the weight space is bounded by strict Min/Max limits and the 1.20 Total Capacity rule, the solver evaluates allowable weight permutations deterministically. It tests candidate vectors against the objective function, applies the quadratic risk penalty via MMULT, and locks in the precise weight distribution that maximizes the Asym Score without stochastic guessing.

Every candidate weight vector is evaluated using the same transparent mathematical steps, ensuring that identical inputs always produce identical outputs—no drift, no randomness, no surprises.

4. Constraints & The Total Capacity Model

ClearLedger Analytics uses a deterministic Min/Max constraint system to govern asset allocations:

Minimum Weight (Min)

Enforces a floor to protect core positions from elimination.

Maximum Weight (Max)

Enforces a ceiling to prevent over‑concentration.

The 1.20 Total Capacity Rule

Total portfolio capacity is fixed at 1.20 (120%). Remaining capacity after summing all Min values is distributed evenly across flexible assets (Min = 0), maintaining strict adherence to advisor intent with zero hidden rule sets.

5. Risk Calculation & Execution (MMULT)

Portfolio risk is computed directly inside Excel using native matrix operations. While the covariance matrix lives in VertiPaq, the risk calculation is executed via Excel’s MMULT function using the standard quadratic form:

Risk = wᵀ Σ w

Evaluated deterministically across two MMULT operations, the math remains completely visible inside the workbook, honoring asset bounds, frozen holdings, and cash targets without stochastic variance.

6. Advisor‑Ready Diagnostics

The optimization process concludes with two clear output reports designed for client communication:

Technicals Table

Compares Current vs. Target metrics across Expected Return, Volatility, Sharpe Ratio, Alpha, Beta, Correlation, and Benchmark relative performance—complete with Variance and Signal indicators.

Asset Table

Summarizes high‑level weight shifts across Stocks/ETFs, Cash, and Other holdings, verifying that all constraints and frozen positions were strictly respected.