Using Validis Formulas
The Validis add-in introduces a set of custom Excel formulas that let you query your engagement data directly in your spreadsheet. All formulas follow the same naming convention:
=validis.formulatype.object(arguments)
Formula Types
| Formula Type | Description |
|---|---|
=validis.list.* | Returns a list of IDs for the specified object. Accepts an optional argument to filter the results. |
=validis.metadata.* | Returns non-period-specific (static) data for an object. Requires the object ID and the attribute name. |
=validis.lookup.* | Returns period-specific data for an object. Requires the object ID, the attribute name, and the period ID. |
List Formulas
List formulas return a collection of IDs for a given object. Use these to populate your template with the IDs you need before retrieving data with metadata or lookup formulas.
validis.list.periods
=validis.list.periods()
Returns a list of IDs for all periods within the currently selected engagement. Each period is typically one month long, though this may vary depending on the engagement.
validis.list.accountset
=validis.list.accountset(filter)
Returns a list of IDs for accounts and categories within the engagement. An optional filter argument lets you narrow the results:
| Filter Value | Returns |
|---|---|
| (no filter) | All IDs — accounts and all categories |
accounts | IDs of accounts only |
primarycategories | IDs of primary categories only |
secondarycategories | IDs of secondary categories only |
Metadata Formulas
Metadata formulas return static, non-period-specific information about an object. You will need the ID of the object you want to query, which can be retrieved using the relevant list formula.
validis.metadata.accountset
=validis.metadata.accountset(account_id, attribute)
Returns static information about an account or category. Available attributes:
- Code
- Name
- Primary Category
- Secondary Category
- Primary Category ID
- Secondary Category ID
- Parent (available for accounts and secondary categories)
- Type (returns "account", "primary category", or "secondary category")
Lookup Formulas
Lookup formulas return period-specific data for an object. You will need both the object ID and the period ID, both of which can be retrieved using the relevant list formulas.
validis.lookup.periods
=validis.lookup.periods(period_id, attribute)
Returns date information for a given period. Available attributes:
- Start Date
- End Date
validis.lookup.accountset
=validis.lookup.accountset(account_id, attribute, period_id)
Returns period-specific financial data for a given account. You must provide both the account ID and the period ID. Available attributes:
- Debit
- Credit
- Opening Balance
- Movement
- Closing Balance
Formula Quick Reference
| Formula | What it does |
|---|---|
=validis.list.periods() | List all period IDs for the selected engagement |
=validis.list.accountset() | List all account and category IDs |
=validis.list.accountset("accounts") | List account IDs only |
=validis.list.accountset("primarycategories") | List primary category IDs only |
=validis.list.accountset("secondarycategories") | List secondary category IDs only |
=validis.metadata.accountset(id, attribute) | Get a static attribute for an account or category |
=validis.lookup.periods(id, attribute) | Get the start or end date for a period |
=validis.lookup.accountset(id, attribute, period_id) | Get a period-specific value for an account |
Tips for Building Templates
- Use list formulas to dynamically populate rows with IDs so your template adjusts automatically when periods or accounts change.
- Reference ID cells in your metadata and lookup formulas rather than hardcoding IDs, making templates reusable across engagements.
- Use the period list formula to drive column headers, and the account set list formula to drive row labels.
- Combine metadata and lookup formulas for a complete view: metadata for account names and codes, lookup formulas for financial values.
- Save your template with Validis formulas in place — simply select a new engagement in the task pane to refresh all values.