Skip to content

Excel User Guide

Connecting Excel to Konsolidat

Konsolidat exposes data through Cube's SQL API, which speaks PostgreSQL wire protocol. Excel connects via ODBC.

Install PostgreSQL ODBC Driver

  1. Download from https://www.postgresql.org/ftp/odbc/
  2. Install the 64-bit Unicode driver
  3. Restart Excel after installation

Create ODBC Connection

Option A: Power Query (Recommended)

  1. Excel → Data → Get Data → From Other Sources → From ODBC
  2. Connection string: Driver={PostgreSQL Unicode};Server=localhost;Port=15432;Database=epm_gold;Uid=epm_excel;Pwd=YOUR_PASSWORD
  3. Select a table/view → Load

Option B: ODBC DSN

  1. Open ODBC Data Source Administrator (64-bit)
  2. Add → PostgreSQL Unicode → Configure:
  3. Data Source: Konsolidat
  4. Server: localhost
  5. Port: 15432
  6. Database: epm_gold
  7. User: epm_excel
  8. Password: from .env
  9. Test → OK
  10. In Excel: Data → Get Data → From ODBC → Select DSN

Available Views

View Description Key Dimensions
v_pnl_report Profit & Loss by period Entity, Year, Period, Account, Cost Center
v_balance_sheet Balance Sheet Entity, Year, Period, Account
v_budget_vs_actual Budget vs Actual comparison Entity, Year, Period, Account, Scenario
v_consolidated_report Group consolidated report Group, Entity, Year, Period, Account

Building PivotTables

  1. Load a view into Excel (Data → From ODBC → select view)
  2. Insert → PivotTable → From this data
  3. Drag fields:
  4. Rows: Account Name, Cost Center
  5. Columns: Fiscal Period
  6. Values: Net Amount (Sum)
  7. Filters: Legal Entity, Fiscal Year

Refreshing Data

  • Manual: Data → Refresh All
  • Automatic: Data → Connections → Properties → Refresh every X minutes

Budget Input

Using the Budget Template

  1. Open excel/budget_template.xlsm
  2. Fill in budget lines (entity, period, account, amount)
  3. Click "Submit Budget" button (VBA macro sends POST to API)

Manual API Submission

Use Power Query M formula to POST budget data: 

= Web.Contents("http://localhost:8080/api/v1/budget", [
    Content = Json.FromValue([lines = budgetTable]),
    Headers = [#"Content-Type" = "application/json"]
])

Budget Validation

After submitting, verify your data: 1. Open Frappe Desk (http://epm.local:8069) and check Budget Input records 2. Or query directly: SELECT * FROM v_budget_vs_actual WHERE fiscal_year = 2024