Skip to main content

Quickstart: Zero to First =EPM() Value in 15 Minutes

Get Konsolidat running locally and pull your first financial value into Excel.

Prerequisites​

  • Docker Desktop (or Docker Engine + Compose)
  • Python 3.10+ with pip
  • Excel (desktop, Windows or Mac)
  • Frappe Bench installed (bench docs)

Step 1: Deploy the Stack (2 min)​

./deploy.sh is the one-click entrypoint. It generates a .env with random secrets (or reuses an existing one), brings up the full stack, and runs the one-shot setup profiles for you.

cd konsolidat
./deploy.sh           # First-time setup (generates .env, builds + starts everything)

Konsolidat ships as a full multi-service stack, not just ClickHouse. deploy.sh (and docker compose) brings up roughly a dozen services:

  • MariaDB, Redis (cache + queue), and ClickHouse — infrastructure
  • Frappe/Konsol backend, worker, and scheduler — the application
  • Cube.js (semantic layer) and Caddy (reverse proxy + auto-SSL)
  • One-shot setup-profile jobs — configurator (creates the site, installs the Konsol app) and dbt_init (seeds demo data + builds the gold models)

After the first run, start and stop the stack with docker compose up -d and docker compose down (or ./deploy.sh down). The generated ClickHouse credentials are written to .credentials; the .env.example default for local dev is CLICKHOUSE_PASSWORD=open_epm_dev.

Verify ClickHouse is up:

docker exec konsolidat_clickhouse clickhouse-client --query "SELECT 1"
# Should print: 1

Step 2: Run dbt (3 min)​

cd dbt_project
pip install dbt-core dbt-clickhouse    # If not already installed
dbt deps                                # Install packages
dbt seed                                # Load reference data (13 CSV seeds)
dbt build                               # Build all 85 models + run 150+ tests

On success you'll see Completed successfully. Done. with 0 errors.

Step 3: Set Up Frappe/Konsol (5 min)​

If you haven't already set up a Frappe bench with the Konsol app:

cd ~/frappe-bench
bench start    # Starts Frappe on http://localhost:8069

Configure EPM Settings in Frappe Desk:

  1. Go to Setup → EPM Settings
  2. Set ClickHouse Host = localhost, Port = 8123, User = default, Password = your .env password
  3. Save

Step 4: Import VBA into Excel (3 min)​

  1. Open a new Excel workbook
  2. Press Alt+F11 to open the VBA Editor
  3. File → Import File → select excel/OpenEPM.bas
  4. Close the VBA Editor
  5. Save as .xlsm (macro-enabled workbook)

Step 5: Connect and Query (2 min)​

  1. In Excel, run EPM_SetServer from the macro menu (Alt+F8)
    • Enter: http://localhost:8069
  2. Run EPM_Login — enter your Frappe username and password
  3. In any cell, type:
=EPM("USMF", 2024, 5, "401100")
  1. Press Ctrl+Shift+R to refresh

You should see the net amount for entity USMF, fiscal year 2024, period 5, account 401100.

What Just Happened?​

Excel cell → EPM() function → VBA batch POST → Frappe API → ClickHouse query → value returned

The VBA module:

  1. Scanned your sheet for all EPM()-family formulas
  2. Batched them into a single POST to /api/method/konsol.api.epm_batch
  3. Cached the results and triggered a recalculation

Next Steps​