Excel Custom Functions

The Konsolidat add-in ships a set of live, auto-recalculating Excel worksheet functions built with Office.js custom functions. They cover the same six operations as the legacy VBA module — reading (EPM, EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, EPM_CREDIT) and writing (EPMSAVE) — but instead of waiting for a manual refresh, they fetch their values on their own and debounce every pending cell into one batched request to the server.

The functions are declared in excel-addin/src/functions.json (parameter schemas) and implemented in excel-addin/src/functions.js (behavior and registration).

The K Namespace

All custom functions live under the K namespace, set by the Functions.Namespace resource in manifest.xml. In a cell, prefix every function name with K.:

=K.EPM("USMF", 2024, "1", "401100")
=K.EPM_BUDGET("USMF", 2024, "Q1", "401100")
=K.EPMSAVE(50000, "USMF", 2024, 1, "401100", "SC-2024", "base")

This is what distinguishes the live functions from the VBA functions, which are called without a namespace (=EPM(...)).

Read Functions

The five read functions share a request shape. They differ only in the measure and scenario they send to the backend, which EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, and EPM_CREDIT hard-code (see functions.js).

K.EPM — General Purpose

Retrieves a financial value (actuals by default) for an entity / period / account.

=K.EPM(entity, year, period, account, [measure], [scenario], [costCenter], [department], [scenarioId])

Parameter	Type	Required	Description
entity	String	Yes	Legal entity / data area id
year	Number	Yes	Fiscal year, e.g. 2024
period	String	Yes	1–12, or Q1–Q4, H1–H2, FY
account	String	Yes	Main account code
measure	String	No	Measure (default period_net_amount)
scenario	String	No	Scenario (default actuals)
costCenter	String	No	Cost center dimension
department	String	No	Department dimension
scenarioId	String	No	Explicit scenario id

K.EPM_BUDGET

Budget amount. Sends measure=period_amount, scenario=budget.

=K.EPM_BUDGET(entity, year, period, account, [costCenter], [department], [scenarioId])

K.EPM_VARIANCE

Variance (absolute). Sends measure=variance_abs, scenario=variance.

=K.EPM_VARIANCE(entity, year, period, account, [costCenter], [department], [scenarioId])

K.EPM_DEBIT

Period debit. Sends measure=period_debit, scenario=actuals.

=K.EPM_DEBIT(entity, year, period, account, [costCenter], [department])

K.EPM_CREDIT

Period credit. Sends measure=period_credit, scenario=actuals.

=K.EPM_CREDIT(entity, year, period, account, [costCenter], [department])

EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, and EPM_CREDIT share the same first four required arguments (entity, year, period, account) plus the dimension optionals. They do not accept measure or scenario directly, because those are fixed for each function.

note

Empty optional arguments are omitted from the request so the backend applies its own defaults. A non-finite year fails that one cell with an "Invalid year" error rather than poisoning the rest of the batch (see enqueue in functions.js).

Write Function

K.EPMSAVE

Writes a single budget cell back to Konsol on recalc and returns the amount so the cell displays it.

=K.EPMSAVE(amount, entity, year, period, account, scenarioId, layer, [costCenter], [department])

Parameter	Type	Required	Description
amount	Number	Yes	Value to write
entity	String	Yes	Legal entity / data area id
year	Number	Yes	Fiscal year
period	Number	Yes	Fiscal period 1–12
account	String	Yes	Main account code
scenarioId	String	Yes	Scenario id
layer	String	Yes	base, challenge, management, or board
costCenter	String	No	Cost center dimension
department	String	No	Department dimension

EPMSAVE POSTs to konsol.api.budget_cell_save. It is best-effort: if the save fails, the function still returns the typed amount (keeping the value on screen rather than showing #VALUE!), and because the failure is not cached, the next recalc retries it.

Idempotent saves

EPMSAVE tracks the last successfully-saved value per cell key and skips the POST when an unchanged cell recalculates. This prevents a write-storm where every recalc re-saves every budget cell. Invalid amount, year, or period arguments surface an error instead of being written.

Auto-Recalculation and Batched Reads

Unlike the VBA functions, the read functions fetch on their own — there is no manual refresh step. Behavior implemented in functions.js:

• Debounced batching. Each read call enqueues a request and schedules a one-tick flush (Promise.resolve().then(flush)). Every cell recalculated in the same pass is coalesced into a single batch and sent as one POST /api/method/konsol.api.epm_batch.
• Chunking. Each flush is split into chunks of at most MAX_BATCH = 2000 requests, keeping a margin under the backend's 2000-per-batch cap. Each chunk is one HTTP request.
• Per-cell results. The backend returns { values: [...], errors: [...] }. Each cell resolves to its value (a null/undefined value resolves to 0) or rejects with its own error, so a single bad cell does not break its siblings.

This means editing inputs or recalculating the sheet automatically refreshes the displayed values, with no keypress required.

Shared Runtime and Authentication

The add-in's assets are served same-origin by Frappe at /assets/konsol/excel-addin/, so every request uses a relative path with credentials: "include" — no CORS, no base URL.

Authentication relies on the session cookie set when you sign in through the task pane. For the custom functions to see that cookie, manifest.xml declares a shared runtime (SharedRuntime requirement, <Runtime ... lifetime="long" />). One long-lived browser runtime hosts both the task pane and the custom functions, so the login cookie established in the pane is sent by the =K.EPM() fetch() calls. The JS-only (non-shared) custom-functions runtime does not support cookies, which would make cookie-based auth fail.

Sign in first

Sign in through the task pane before using the functions. Until you do, requests return HTTP 401/403 and the cell shows:

Not logged in — open the Open EPM task pane and sign in.

A failed HTTP request (non-2xx) shows Request failed (HTTP <status>).

Contrast with the VBA Functions

The live functions complement the legacy VBA module in excel/OpenEPM.bas, which remains in place. Both expose the same six function names, but the refresh and session models differ:

Aspect	VBA (=EPM())	Custom functions (=K.EPM())
Call syntax	No namespace — =EPM(...)	K. namespace — =K.EPM(...)
Refresh	Manual — values update on Ctrl+Shift+R	Automatic — fetch on recalc, debounced
Read batching	Batch refresh on demand	Per-tick debounce into one epm_batch POST, chunked at MAX_BATCH = 2000
Session	Maintains its own XMLHTTP session	Shared session cookie from the task pane (shared runtime)
Write (EPMSAVE)	Saves on recalc, skips unchanged	Saves on recalc, skips unchanged (per-cell cache)

Use whichever fits your workbook; they target the same Konsol backend.

Next Steps

• Excel Task Pane Guide — Sign in (sets the shared session cookie), monitor and trigger pipeline runs
• Excel VBA Guide — The manual, Ctrl+Shift+R refresh model and macros