Lumonic Excel Add-in Documentation

Getting Started

Building Formulas

Use the sidebar included in the add-in custom tab under Lumonic tab. The sidebar provides tools to quickly look up entities and build Lx expressions.

Refreshing Data On Demand

Use the Refresh command menu to select the appropriate action for your use case:

Worksheet will refresh each LX.* formula on the current worksheet
Selection will refresh LX.* formulas only in currently selected cells

We recommend refreshing smaller selections for data intensive worksheets.

**Entity Formulas (LX.ENTITY.*)**

Entity formulas query data for specific entities using their unique IDs.

LX.ENTITY.GET

Query an entity for a specific date and Lx expression.

Return:

A single data point (string, number, or date)

Arguments:

entityId required[string] – Entity ID to query
lx required[string] – Lx expression to evaluate
effectiveDate required[date] – Date for evaluation

Examples:

=LX.ENTITY.GET("company_abc123", "@company.revenue", "2024-01-01")
=LX.ENTITY.GET("security_xyz789", "@security.amount", TODAY())
=LX.ENTITY.GET("fund_def456", "@fund.nav", "2024-12-31")

LX.ENTITY.DATE

Retrieve the latest or earliest date for an entity based on an Lx expression.

Return:

Excel date number

Arguments:

entityId required[string] – Entity ID to query
lx required[string] – Lx expression to evaluate
dateType optional[string] – "latest" (default) or "earliest"

Examples:

=LX.ENTITY.DATE("company_abc123", "@company.revenue", "latest")
=LX.ENTITY.DATE("security_xyz789", "@security.valuation", "earliest")

LX.ENTITY.AGG

Aggregate values from related entities using an Lx expression.

Return:

Aggregated scalar value (number or string)

Arguments:

entityId required[string] – Parent entity ID
lx required[string] – Lx expression to evaluate on related entities
effectiveDate required[date] – Date for evaluation
aggMethod required[string] – Aggregation method: SUM, AVERAGE, MIN, MAX, COUNT, FIRST, ALL, ANY
filters optional[string] – Filter JSON from LX.FILTER() or LX.FILTER.CHAIN()
options optional[string] – Reserved for future use

Examples:

// Sum revenue from all subsidiaries
=LX.ENTITY.AGG("company123", "@company.revenue", "2024-01-01", "SUM")

// Average revenue from active subsidiaries only
=LX.ENTITY.AGG("company123", "@company.revenue", "2024-01-01", "AVERAGE", 
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"))

// Count securities with amount > 50000
=LX.ENTITY.AGG("company123", "@security.amount", "2024-01-01", "COUNT",
  LX.FILTER("@security.amount > 50000", "EXACT", "2024-01-01"))

**Namespace Formulas (LX.NAMESPACE.*)**

Namespace formulas work with collections of entities within a specific namespace (entity type).

LX.NAMESPACE.LIST

Return a list of entities within a namespace with optional filtering and formatting.

Return:

Dynamic 2D array of entity data

Arguments:

namespace required[string] – Namespace name (e.g., "Company", "Fund", "Security")
filters optional[string] – Filter JSON from LX.FILTER() or LX.FILTER.CHAIN()
fields optional[string] – Comma-separated field list (default: "name,id")
sortBy optional[string] – Sort field, prefix with "-" for descending
limit optional[number] – Maximum results to return
transpose optional[boolean] – Transpose results (default: false)

Examples:

// Simple list with default fields
=LX.NAMESPACE.LIST("Company")

// List with single filter
=LX.NAMESPACE.LIST("Company", LX.FILTER("@company.revenue > 1000000", "ANY", "2023-01-01:2024-12-31"))

// Complex list with custom fields and sorting
=LX.NAMESPACE.LIST("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.revenue > 50000", "ANY", "2023-01-01:2024-12-31"),
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01")
  ),
  "name,id,revenue,status",
  "-revenue",
  50)

LX.NAMESPACE.AGG

Aggregate values from child entities across multiple parent entities in a namespace.

Return:

Aggregated scalar value (number or string)

Arguments:

namespace required[string] – Parent entities' namespace
parentFilter optional[string] – Filter for parent entities
lx required[string] – Lx expression to evaluate on child entities
effectiveDate required[date] – Date for evaluation
aggMethod required[string] – Aggregation method: SUM, AVERAGE, MIN, MAX, COUNT, FIRST, ALL, ANY
childFilters optional[string] – Filter for child entities
options optional[string] – Reserved for future use

Examples:

// Sum revenue from all securities across all active companies
=LX.NAMESPACE.AGG("Company", 
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"), 
  "@security.revenue", "2024-01-01", "SUM")

// Average valuation of securities > 50000 across all companies
=LX.NAMESPACE.AGG("Company", , "@security.valuation", "2024-01-01", "AVERAGE",
  LX.FILTER("@security.amount > 50000", "EXACT", "2024-01-01"))

// Complex example with chained filters for both parents and children
=LX.NAMESPACE.AGG("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.revenue > 100000", "ANY", "2023-01-01:2024-12-31"),
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01")
  ),
  "@security.amount", "2024-01-01", "SUM",
  LX.FILTER.CHAIN("OR",
    LX.FILTER("@security.type = Equity", "EXACT", "2024-01-01"),
    LX.FILTER("@security.type = Debt", "EXACT", "2024-01-01")
  ))

**Relationship Formulas (LX.RELATIONSHIP.*)**

Relationship formulas work with entity relationships and connections.

LX.RELATIONSHIP.LIST

Return a list of child or related entities for a given parent entity.

Return:

Dynamic 2D array of related entity data

Arguments:

entityId required[string] – Parent entity ID
relatedNamespace required[string] – Child entity type (e.g., "Security", "Company")
filters optional[string] – Filter JSON for child entities
fields optional[string] – Comma-separated field list (default: "name,id")
sortBy optional[string] – Sort field, prefix with "-" for descending
limit optional[number] – Maximum results to return
transpose optional[boolean] – Transpose results (default: false)

Examples:

// Simple list of securities for a company
=LX.RELATIONSHIP.LIST("company123", "Security")

// Filtered list with custom fields
=LX.RELATIONSHIP.LIST("company123", "Security",
  LX.FILTER("@security.status != Paid Off", "ANY", "2023-01-01:2024-12-31"),
  "name,id,amount,status",
  "-amount",
  50)

**Covenant Formulas (LX.COVENANT.*)**

Covenant formulas work with covenant tracking and compliance data.

LX.COVENANT.LIST

Return a list of covenant fields tracked by an entity.

Return:

Dynamic 2D array of covenant field data

Arguments:

entityId required[string] – Entity ID to query
fields optional[string] – Field options: "name", "pointer", or "name,pointer" (default: both)
sortBy optional[string] – Sort by "name" or "status", prefix with "-" for descending
limit optional[number] – Maximum covenant to return
transpose optional[boolean] – Transpose results (default: false)

Examples:

// Simple list with default fields
=LX.COVENANT.LIST("entity123")

// List with only names, sorted by covenant name
=LX.COVENANT.LIST("entity123", "name", "name")

// List sorted by compliance status (out of compliance first)
=LX.COVENANT.LIST("entity123", , "-status")

**Filter Functions (LX.FILTER.*)**

Filter functions create filter conditions for use with other formulas.

LX.FILTER

Create a single filter condition for entity queries.

Return:

JSON filter string

Arguments:

condition required[string] – Lx expression with @ prefixes
strategy optional[string] – Filter strategy: "EXACT", "ANY", or "ALL" (default: ANY with 12-month lookback)
dates optional[string] – Date specification (default: 12-month lookback from today)

Examples:

// Simple condition check (uses ANY strategy with 12-month lookback)
=LX.FILTER("@company.revenue > 1000000")

// Exact date filter
=LX.FILTER("@company.revenue > 1000000", "EXACT", "2024-01-01")

// Any date in range
=LX.FILTER("@company.status = Active", "ANY", "2023-01-01:2024-12-31")

// All dates in range must match
=LX.FILTER("@company.deal_type = Venture", "ALL", "2023-01-01:2024-12-31")

LX.FILTER.CHAIN

Chain multiple filter conditions with AND/OR logic.

Return:

JSON filter chain string

Arguments:

operation required[string] – Logical operation: "AND" or "OR"
filter1 required[string] – First filter JSON from LX.FILTER()
filter2 required[string] – Second filter JSON from LX.FILTER()
filter3-10 optional[string] – Additional filters (up to 10 total)

Examples:

// AND multiple conditions
=LX.FILTER.CHAIN("AND",
  LX.FILTER("@company.revenue > 1000000", "ANY", "2023-01-01:2024-12-31"),
  LX.FILTER("@company.deal_type = Venture", "EXACT", "2024-01-01"))

// OR multiple conditions
=LX.FILTER.CHAIN("OR",
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"),
  LX.FILTER("@company.status = Pending", "EXACT", "2024-01-01"))

// Using default 12-month lookback filters
=LX.FILTER.CHAIN("AND",
  LX.FILTER("@company.revenue > 1000000"),
  LX.FILTER("@company.employees > 50"))

Filter Strategies

Default (12-Month Lookback)

When no strategy or dates are provided
Uses ANY strategy with a 12-month date range from today
Checks if the condition was met on any date in the past 12 months
Best for current state queries: LX.FILTER("@company.revenue > 1000000")

EXACT Strategy

Use with single dates: "2024-01-01"
Entity must have data on the exact specified date
Best for point-in-time queries

ANY Strategy

Use with date ranges: "2023-01-01:2024-12-31"
Entity must match condition on at least one date in the range
Best for "has ever been" type queries

ALL Strategy

Use with date ranges: "2023-01-01:2024-12-31"
Entity must match condition on every date in the range where data exists
Best for "always has been" type queries

Advanced Examples

Portfolio Analysis

// Get all active companies with revenue > $1M in 2024
=LX.NAMESPACE.LIST("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"),
    LX.FILTER("@company.revenue > 1000000", "EXACT", "2024-01-01")
  ),
  "name,revenue,employees",
  "-revenue")

// Sum total portfolio value across all funds
=LX.NAMESPACE.AGG("Fund", , "@company.valuation", "2024-12-31", "SUM")

// Get companies with current revenue > $1M (using latest value)
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@company.revenue > 1000000"),
  "name,revenue,status")

Compliance Monitoring

// List all covenant violations for an entity
=LX.COVENANT.LIST("entity123", , "-status", , TRUE)

// Count companies with covenant violations
=LX.NAMESPACE.AGG("Company", , "@covenant.status", "2024-01-01", "COUNT",
  LX.FILTER("@covenant.status = Out of Compliance", "EXACT", "2024-01-01"))

// Get companies currently out of compliance (using latest value)
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@covenant.status = Out of Compliance"),
  "name,covenant_status")

Performance Tracking

// Get latest revenue date for each company
=LX.ENTITY.DATE("company123", "@company.revenue", "latest")

// Track revenue growth across all subsidiaries
=LX.ENTITY.AGG("parent_company", "@company.revenue", "2024-01-01", "SUM",
  LX.FILTER("@company.growth_rate > 0.1", "EXACT", "2024-01-01"))

// Find companies with current positive growth
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@company.growth_rate > 0"),
  "name,revenue,growth_rate",
  "-growth_rate")

**Developer Functions (LX.X.DEVELOPER.*)**

Developer functions assist with troubleshooting and configuration verification.

LX.X.DEVELOPER.ME

Get current authenticated user information.

Return:

User ID and email string

Example:

=LX.X.DEVELOPER.ME()

LX.X.DEVELOPER.CONFIG

Display current add-in configuration including API URL and authentication status.

Return:

Configuration details string

Example:

=LX.X.DEVELOPER.CONFIG()

Tips and Best Practices

Performance Optimization

Use filters to limit result sets when possible
Refresh smaller selections for faster updates
Consider using limits on large datasets
Cache frequently used filter expressions in helper cells

Error Handling

Check entity IDs are valid before building complex formulas
Use developer functions to verify authentication
Test filter expressions with simple cases first

Formula Organization

Build filter expressions in separate cells for reusability
Use named ranges for commonly referenced entity IDs
Document complex formulas with comments

Troubleshooting

Common Issues

Authentication errors: Use LX.X.DEVELOPER.CONFIG() to check status
Invalid entity IDs: Verify IDs using the sidebar lookup tool
Date format errors: Use YYYY-MM-DD format or Excel date functions
Filter syntax errors: Test individual filters before chaining

Getting Help

Use the sidebar for entity and expression lookup
Check the formula examples in this documentation
Contact support for complex Lx expression assistance