Adding a credential to Actuals [GUIDE-1]

Working with JSON data in Actuals [GUIDE-2]

Actuals allows two functionalities related to JSON structured data.

Extract data from a JSON string

Create a JSON string

1. Extract data from a JSON string

The Actuals platform allows to extract data from a JSON string inside a column. Imagine a file is provided that contains a column with a string of JSON data, for example a csv file that looks like this:

header name: ID header number: __column1__	header name: JSON_DATA header number: __column2__
Item 1	{“orderId”:”order123”}
Item 2	{“orderId”:”order124”}
Item 3	{“orderId”:”order125”}

If we want extract the orderId from the json string we can use this formula:

sql
JSON_EXTRACT_PATH_TEXT(<column_identifier>, '<path_name>')

In this example the JSON_DATA column is the column we want to look for. The column is located as second column. We need to define this with the column number, in this case this is the second column so you should use __column2__. The path is orderId, to extract the orderId we will create this formula:

sql
JSON_EXTRACT_PATH_TEXT(__column2__, '"orderId"')

It is also possible to extract nested data, for a more comprehensive guide on how to use this formula see: Snowflake JSON_EXTRACT_PATH_TEXT documentation.

2. Construct a JSON String

Actuals also supports the use case to construct a JSON object from source data. Imagine a file that provided that contains a column with a string of JSON data, for example a csv file that looks like this:

ID	payment_method	payment_method_variant
Order 1	Creditcard	VISA
Order 2	Creditcard	VISA
Order 3	Creditcard	Mastercard

If we want construct a json object containing all information about the payment method used for a orders we can use this formula:

OBJECT_CONSTRUCT('<column_key>', '<column_value>', '<column_key2>', '<column_value2>')

In this example we want to construct a object containing columns payment_method and payment_method_variant The columns are located as second and third column in the source data. We need to define this with the column number, in this case this is the second and third column so you should use __column2__ and __column3__.

To construct the object we will create this formula: OBJECT_CONSTRUCT('payment_method', __column2__,'payment_method_variant',__column3__).

For a more comprehensive guide on how to construct objects see: Snowflake OBJECT_CONSTRUCT documentation.

Working with XML data in Actuals [GUIDE-3]

Actuals contains functionality to work with XML structured data. The available functions are:

Extract properties from a XML column

1. Extract properties from a XML column

The Actuals platform allows to extract data from XML data inside a column. Imagine a file is provided that contains a column with a string of XML data, for example a csv file that looks like this:

ID	XML_DATA
Item 1	<order><orderId>order123</orderId></order>
Item 2	<order><orderId>order124</orderId></order>
Item 3	<order><orderId>order125</orderId></order>

If we want to extract the orderId from the XML string we can use this formula:

sql
XML_EXTRACT_XPATH_TEXT(<column_identifier>, <xpath>)

In this example the XML_DATA column is the column we want to look for. The column is located as the second column. We need to define this with the column number, in this case this is the second column so you should use is __column2__. The path is order --> orderId, to extract the orderId we will create this formula:

sql
XML_EXTRACT_XPATH_TEXT(__column3__, '/order/orderId')

To specify the path of the data you are looking for, we are using the XPATH standard. More information on what possibilities you have to extract data can be found in the XPath specification.

ℹ️

The XML_EXTRACT_XPATH_TEXT function will return NULL if the provided XPATH doesn’t exist in the XML string (similar to the JSON_EXTRACT_PATH_TEXT function).

When the XPATH returns multiple results it will return a value error. Make sure you always provide an explicit XPATH function that returns a maximum of 1 result. If you want to test the result of a XPATH formula on a certain XML string you can use an online XML XPATH evaluation tool like Xpather.

Exchanging large datasets via compressed CSV files [GUIDE-4]

Uploading a file to the Actuals SFTP server [GUIDE-5]

Add a General Ledger Account [GUIDE-9]

In Actuals you can maintain a Chart of Accounts. To add a new General Ledger Account:

Click ‘’Add new ledger’’

(Optionally) Provide your desired logo.

💡

Logo’s are displayed in several reports and can help to distinguish data from different sources (like different PSP’s visually easily). Actuals provides a predefined list with available logo’s.

Provide the Ledger Code

💡

The general ledger account code is a property you can define and modify to match your with your schema in your ERP.

(Optionally) provide the Code Addition

💡

The code addition property is reserved for accounts identifiers at Payment Service Providers or banks. When you want to add multiple accounts of the same PSP you can use fill this property with you account identifier.

Provide a desired name for the Ledger

Choose a balance type

💡

Define if the general ledger account belongs to the Balance or Profit & Loss statement. A third option “Not Affecting” is available, this option can be used to store analytical information not affecting your balance or profit & loss statement.

Provide a name for the Ledger Category, i.e. Balance Sheet

💡

The category property can be used to define a reporting structure for a general ledger account. You can create a reporting structure consisting of multiple levels. Use the forward slash “/” as separator for the reporting structure levels.

Currency Revaluation - choose not allowed, unless advised otherwise

💡

Currency revaluation allows you recalculate the value of foreign currency amounts in the general ledger. When this setting is set to “Revaluation allowed.” This general ledger account is available for currency revaluation. When set to “Not Allowed” this general ledger account will be excluded.

Global Code

💡

Actuals comes with a predefined general ledger account schema for certain PSP integrations. This global code property is used to link a general ledger account inside a customers administration to the Actuals global schema. You don’t need to populate this property unless you want to upgrade from a custom connector to the Actuals pre-defined one without adding new general ledger accounts to your administration.

Click ‘’Create’’

Importing opening balances [GUIDE-10]

Importing Opening Balances into Actuals

Opening balances represent the financial position of your organization at the start of using Actuals (commonly at the start of new financial period). Importing them correctly ensures that all subsequent transactions, reports, and reconciliations are accurate. This guide explains how to import opening balances into the Actuals platform.

Prerequisites

Before you begin, ensure that:

Your Chart of Accounts (GL accounts) already exists in Actuals

Any required matching package is configured

You know the cutover date for which the opening balances should apply

Step 1: Create a Source for Opening Balances

Opening balances must be imported via a dedicated Source so they are clearly identifiable and auditable.

Navigate to Configuration → Sources

Create a new source

Recommended settings

Name: Opening Balances

Type: Actuals SFTP

For detailed instructions, see our sources page.

Step 2: Create the Opening Balances CSV File

2.1 Determine the number of lines to be provided per GL Account

The number of transaction lines to be provided in the csv file per GL account depends if the general ledger account is included in a matching package.

For GL accounts not subject to matching, opening balances can be provided as a single line per GL account (and per required subanalysis combination, such as entity or currency).

Examples include:

Bank accounts balances

Most non-operational balance sheet accounts

In these cases:

No match key is required

A single aggregated opening balance is sufficient

Matching logic is not applied going forward

For GL accounts that are part of a matching package (for example: Accounts Receivable, Accounts Payable, clearing accounts), opening balances should be provided per match key.

This means:

Each row represents a unique GL account + match key combination

Match keys typically represent items such as:

Invoice reference
Settlement reference

The sum of all rows for a given GL account must equal the total opening balance of that account

Providing balances at match-key level enables Actuals to:

Continue matching historical balances with future transactions

Accurately track open and settled positions over time

If a customer cannot fully specify the opening balance across all match keys, they can still proceed by:

Providing opening balances for all known match keys

Assigning the remaining (unspecified) balance to a dedicated match key: opening_balance_not_specified

This ensures that:

The total opening balance remains correct

Matching can still occur for the known portion

The residual balance is explicitly tracked and isolated

✅ Best practice: Always ensure the sum of all match-key rows (including opening_balance_not_specified ) equals the balance from the source system.

Balances assigned to the opening_balance_not_specified match key are treated as valid opening balances but are not automatically matchable to specific transactions.

Over time, customers can:

Match future transactions against known match keys as usual

Explicitly write off or clear transactions against the opening_balance_not_specified opening balance when details become available

Gradually reduce the unspecified balance without compromising historical accuracy

This approach allows customers to:

Start with correct balances

Improve matching quality over time

Avoid blocking go-live due to incomplete historical detail

2.2 Identify the Opening Balance Date

Choose a single posting date for all opening balance entries. This is typically the last day of the previous financial period (e.g. 2025-12-31).

All rows in the file should use the same posting date.

2.3 Identify the GL Accounts

Use the GL account numbers as configured in Actuals

Each general ledger account located on the balance should typically have one line in case there isn’t matching activated.

For general ledger accounts located on the balance sheet for which a matching package is activated matchkey level details need to be provided.

General ledger accounts located on the Profit & Loss should usually start at zero.

2.4 Identify amounts and currency

Use signed amounts (provide debit amounts as positive, credit amounts as negative)

Assets: typically positive
Liabilities / Equity: typically negative (depending on your accounting conventions)

Make sure debits and credits balance overall

Provide the amounts in the same currency that future transactions will be provided. In case of multi currency. Provide the opening balances in the foreign currency amounts.

2.5 Sample CSV Format

CSV

javascript
date,source,gl_account,amount,currency,match_key,description
2025-12-31,openingbalances,1000,5000000,EUR,,opening balance HSBC bank EUR account 
2025-12-31,openingbalances,1300,12100,EUR,INV-10001,AR opening balance customer 1 invoice 10001 
2025-12-31,openingbalances,1300,24200,EUR,INV-10002,AR opening balance customer 2 invoice 10002
2025-12-31,openingbalances,1300,500,EUR,opening_balance_not_specified,AR opening balance - unspecified
2025-12-31,openingbalances,0900,-86800,EUR,,opening balance

Common columns

date: Opening balance date

source: Must match the source created earlier

gl_account: GL account identifier in Actuals

amount: Opening balance value

currency: Transaction currency

match_key: Required when subanalysis applies

description: Optional but recommended

✅ Tip: It is possible to upload multiple versions of the opening balance file and remove previous versions before importing a new file. Start with a small subset of accounts to validate formatting before uploading the full file.

Step 3: Upload the Opening Balances File

Navigate to Configuration → Sources → Select the “Opening balance” source → Upload File

Upload the CSV file

Step 4: Create the Mapping

Go to Configuration → Source → Iterations

Assign the general ledger accounts

Go to Configuration → Source → Mapping

Ensure all fields are correctly mapped:

Date → posting date

Amount → signed amount

Match key → subanalysis mapping

If subanalysis exists:

Confirm the match key resolves to a valid subanalysis combination

Fix any unmapped or ambiguous values before proceeding

Step 5: Process the File and Validate the Opening Balances

5.1 Process the File

Run the import processing job

Ensure the job completes successfully

5.2 Validate the Results

After processing, validate the opening balances:

Balance Sheet Report

Confirm total debits equal total credits
Verify that balances match your source system exactly

General Ledger Account Details

Confirm one entry per GL account (and subanalysis combination)
Confirm the match keys are populated correctly

✅ Recommended check: Export a balance sheet from Actuals and reconcile it with your legacy system.

Guides