Custom Functions Reference

Powering your financial reports with spreadsheet-native intelligence.

Abaque extends Google Sheets with specialized functions designed for Canadian and International accounting standards. These functions automate the heavy lifting of aggregating journal entries into GIFI-compliant balances.

Included Functions:
  • GIFI_21_BALANCE
  • GIFI_BALANCE_CARRYFORWARD

GIFI_21_BALANCE

This is the core calculation engine. It displays account balances by reconciling GIFI codes and Account Numbers against your Journal activity.

GIFI_21_BALANCE(items, journal, [prevBalance], [flags])

Arguments

Argument Range Example Description
items D3:E Two columns: GIFI codes (left) and Account Numbers (right).
journal 'Journal'!C2:G The data range of your journal.
Note: Column count and order are strictly defined by flags.
prevBalance K3:K Optional. Single column range having the same number of rows as “items” and containing the starting balances or carry-forwards to add to the current year balances. These accounting balances can be from the preceding fiscal year (see GIFI_BALANCE_CARRYFORWARD) or be the starting balances. This parameter is ignored when computing the trial balance.
flags BITOR(...) Optional. Bit flags controlling regional tax logic, currency, and return types. See table below.

The Flags System (Bitmasks)

Use the BITOR() and HEX2DEC() functions to combine settings. These flags tell the Abaque engine how to interpret your journal columns.

Hex Flag Function / Setting Required [Optional] Journal Columns
0x0 to 0x3 Default Canadian Provincial Sales Tax: 0: QC, 1: BC, 2: MB, 3: SK.
(Canadian Templates)		 DEBIT, CREDIT, AMOUNT, [GST/HST [, PST [, PROV]]].
See Canadian Templates section on the Sales Tax Logic page for more information on how to fine tune the canadian sales tax columns according to your needs.
0x80 Foreign Sales Tax: Enables international tax tracking.
(International Templates)		 DEBIT, CREDIT, AMOUNT, STAX, STJUR.
0x100 Exchange Rate: The journal contains an “XR” (Exchange Rate) column immediately following the “AMOUNT” column. … | AMOUNT | XR | …
0x200 Allows balance transfer between 3545 | 3545 | Undistributed Net Income(Loss) and corresponding partners account during the fiscal year (partnerships only). N/A
Mutually Exclusive Bit flags 0x400 Returns a trial balance instead of net balances. N/A
0x800 Returns in a single string the list of industries associated with the GIFI codes stored under the “items” range’s left column. N/A
0xC00 Returns in a single string that can be exported to a .gfi file the consolidated balance of the GIFI codes referenced by the “items” range. N/A
0x1000 Returns in a single string that can be exported to a .gfi file the consolidated balance of the GIFI-Short derived codes referenced by the “items” range. N/A

Compatibility & Versioning

The 21 in the function name refers to GIFI Revision 21. While the current calculation engine is fully compatible with Revision 23 (as the CRA Appendix A tax mapping has remained unchanged between these versions), Abaque utilizes versioned function names to ensure long-term data integrity.

By using version-specific functions, Abaque guarantees that your historical financial reports remain immutable. If the CRA introduces a future update (e.g., Revision 28) that modifies the underlying tax codes or logic, a new function—such as GIFI_28_BALANCE—will be released. This allows you to manage multiple fiscal years with different regulatory requirements within the same spreadsheet without newer rules affecting your past filings.

GIFI_BALANCE_CARRYFORWARD

This function automates the transfer of Balance Sheet balances from a preceding fiscal year to the current one. It is designed to bridge two separate fiscal periods by matching current accounts against historical data, even as your ledger evolves.

GIFI_BALANCE_CARRYFORWARD(items, prevItems, prevBalance)

Arguments

Argument Range Example Fiscal Year Description
items D3:E Current Range of 2 columns holding the GIFI codes (left) and the Account Numbers (right).
prevItems '2025 - CoA'!D3:E Previous Range of 2 columns holding the GIFI codes (left) and Account Numbers (right) from the preceding fiscal year.
prevBalance '2025 - CoA'!I3:I Previous Single column range containing the balances to be carried forward. This range must have the same number of rows as prevItems.

Key Features

  • Account Matching: The engine reconciles balances based on the unique GIFI/Account pair. If an account from the previous year is removed (e.g., it reached a zero balance), it is safely ignored.
  • New Accounts: Any new accounts added to the current year that do not exist in the previous year’s range are automatically assigned a 0 opening balance.

Automation with Templates

By using a standardized workflow, you can automate the rollover process so that formulas never need manual editing when a new year begins:

  1. Use a header cell (e.g., I2) to store the current fiscal year (e.g., 2026).
  2. Follow a naming convention for your sheets, such as “<year> - CoA”.
  3. Use the INDIRECT function to dynamically construct the ranges for the previous year.
=GIFI_BALANCE_CARRYFORWARD(D3:E, INDIRECT("'" & (I2-1) & " - CoA'!D3:E"), INDIRECT("'" & (I2-1) & " - CoA'!I3:I"))

This approach ensures that as soon as you duplicate your sheet to start a new year and update the year in cell I2, all opening balances are instantly recalculated from the prior year’s data.