e-conomic Monthly Trial Balance | Gatlas help center

What it answers

What was the balance on each account at the end of a month?
How did an account move month by month?

The view is named after the company, such as "Acme ApS Monthly Trial Balance", and reads the company's e-conomic data.

How to add it

Gatlas adds it for a company when the company connects e-conomic, and it fills in after the first sync. For a company that connected earlier, ask an AI assistant on a Read/write MCP connection:

Add the e-conomic Monthly Trial Balance view for Acme ApS.

The assistant creates it with the template id economic/monthly-trial-balance, after checking that the company's data has every column the template reads.

Columns

connection_label: The e-conomic agreement the row comes from; a company can hold several.
accounting_year: e-conomic accounting year.
period_start: First day of the period (month).
period_end: Last day of the period.
account_number: Account number.
account_name: Account name.
account_type: profitAndLoss or status (balance sheet).
debit_credit: The account's normal side.
chart_section: Nearest heading at or above the account in the company's chart.
movement: Net posted amount in the period, base currency, positive is debit.
closing_balance: Running sum of movement within the accounting year: the balance (or year-to-date result) at period_end.
data_as_of: Most recent sync of the period totals.

What the AI assistant is told

The company's monthly trial balance from e-conomic period totals: one row per account, accounting year and month, for P&L (account_type 'profitAndLoss') and balance-sheet ('status') accounts.

movement is the month's net posting in base currency, positive is debit. closing_balance is the running sum of movement within the accounting year.

For a balance-sheet account, the first month's movement includes the opening balance e-conomic restates at the start of each year, so closing_balance is the true balance at period_end. Never add closing balances or movements across accounting years.

For a P&L account, closing_balance is the year-to-date result; revenue is negative (credit).

The closing_balance of the year's last month equals e-conomic's year total for the account. Posted entries only: drafts are in the Ledger view.

The SQL

__SCHEMA__ stands for the company's schema. To adapt the view, copy the SQL, put the schema in its place and change what your company needs.

WITH accts AS (
  SELECT
    COALESCE(a."_gatlas_connection_label", '') AS label_key,
    a."accountNumber" AS account_number,
    a."name" AS account_name,
    a."accountType" AS account_type,
    a."debitCredit" AS debit_credit,
    (SELECT h."name"
       FROM __SCHEMA__.economic_accounts h
      WHERE COALESCE(h."_gatlas_connection_label", '') = COALESCE(a."_gatlas_connection_label", '')
        AND h."accountType" IN ('heading', 'headingStart')
        AND h."accountNumber" <= a."accountNumber"
      ORDER BY h."accountNumber" DESC
      LIMIT 1) AS chart_section
  FROM __SCHEMA__.economic_accounts a
  WHERE a."accountType" IN ('profitAndLoss', 'status')
),
movements AS (
  SELECT
    pt."_gatlas_connection_label" AS connection_label,
    pt."accountingYear" AS accounting_year,
    pt."periodFromDate"::date AS period_start,
    pt."periodToDate"::date AS period_end,
    pt."accountNumber" AS account_number,
    SUM(pt."totalInBaseCurrency") AS movement,
    MAX(pt."_gatlas_synced_at") AS data_as_of
  FROM __SCHEMA__.economic_accounting_year_period_totals pt
  GROUP BY 1, 2, 3, 4, 5
),
-- Every month of a year that has any movement, for every account that moved
-- in that year, so a month without postings still shows its balance.
periods AS (
  SELECT DISTINCT m.connection_label, m.accounting_year, m.period_start, m.period_end
  FROM movements m
),
year_accounts AS (
  SELECT DISTINCT m.connection_label, m.accounting_year, m.account_number
  FROM movements m
),
grid AS (
  SELECT ya.connection_label, ya.accounting_year, p.period_start, p.period_end,
         ya.account_number, COALESCE(m.movement, 0) AS movement, m.data_as_of
  FROM year_accounts ya
  JOIN periods p
    ON COALESCE(p.connection_label, '') = COALESCE(ya.connection_label, '')
   AND p.accounting_year = ya.accounting_year
  LEFT JOIN movements m
    ON COALESCE(m.connection_label, '') = COALESCE(ya.connection_label, '')
   AND m.accounting_year = ya.accounting_year
   AND m.period_start = p.period_start
   AND m.account_number = ya.account_number
)
SELECT
  g.connection_label,
  g.accounting_year,
  g.period_start,
  g.period_end,
  g.account_number,
  ac.account_name,
  ac.account_type,
  ac.debit_credit,
  ac.chart_section,
  ROUND(g.movement::numeric, 2) AS movement,
  ROUND(SUM(g.movement) OVER (
    PARTITION BY g.connection_label, g.accounting_year, g.account_number
    ORDER BY g.period_start
  )::numeric, 2) AS closing_balance,
  MAX(g.data_as_of) OVER () AS data_as_of
FROM grid g
JOIN accts ac
  ON ac.label_key = COALESCE(g.connection_label, '')
 AND ac.account_number = g.account_number
ORDER BY 1, 2, 3, 5