Apaleo Gross Transactions Export – Apaleo Help Center

Overview
Structure and Format of the List in PDF or Excel/CSV
Why is an Account Presented in Debit or Credit in the Gross Transaction List?
Why Use the Gross Transaction Report?
Use Case: Tracking Receivables
Use Case: Tracking Liabilities

Overview

This finance export shows the accounting transactions for a chosen business date or date range in a structured format with extra details.

Business date is the day the day when services were delivered and revenue was made, which is important for accounting, like daily or monthly closing. The timestamp of when the transactions were recorded in Apaleo is less important.
Structured format means the raw data is organized to make it easier to read by:
- Showing Apaleo accounts as either debit or credit,
- Displaying net and gross amounts, VAT amounts, and the applied VAT rate,
- Separating and showing liabilities (like received prepayments or deposits) by VAT rates,
- Providing a reference like reservation ID, external folio ID, or house account, without including folio and/or version numbers.
Extra details means extra information is added to help with transferring data to the general ledger and for analysis:
- Arrival and departure dates
- Reference type: guest or external folio
- Status of the reservation at the time of the query.

The Gross Transactions export can be accessed and exported via Finance - Accounting - Export:

Export formats available include Excel, CSV, and PDF. The data can also be retrieved via the Apaleo API.

Structure and Format of the Export📒

The report structure is fixed and contains the following columns:

In PDF format, showing a reduced number of columns:

In Excel and CSV format:

Column Title	Content
Business Date	The day on which a transaction was booked Only the night audit can write back to previous business days, never a direct booking For corrections, e.g., refunding a service, the business day is always the day the transaction was booked
Timestamp	The day and time when the transaction was recorded in the system Not relevant for accounting! Useful for analyses
Debit Account Name *	Account name
Debit Account Number *	Account number
Credit Account Name *	Account name
Credit Account Number *	Account number
Command	Information on what triggered the transaction: PostCharge: Posting a service PostPayment: Treating a payment as settlement of a receivable PostPrepayment: Treating a payment as a prepayment (aka liability/deposit) PostPrepaymentVat: Adjustment of VAT amounts for a prepayment PostToAccountsReceivables: Creating an accounts receivable invoice, settling a guest receivable (#1200 receivables for reservation x)
Net, Tax, and Gross Amount	Breakdown of the amount into these components Only relevant for accounts booked in credit! Accounts in debit are exclusively gross
Currency	Set currency in the Apaleo account
Tax Type	Information on the applied tax type/tax rate
Document Type	Information on how the document can be identified: Reservation PSP Reference (payments via Apaleo Payment) Custom: manual booking Invoice: invoice
Receipt Type	Receipt Type, which can be: Reservation: reservation ID PSP Reference: the number Custom: default is reservation, otherwise the entry in the document number field Invoice: invoice number
Entry Number	A unique, sequential system-generated number in Apaleo to chronologically record the transactions
Reference	Reservation ID without folio and/or version number
Reference Type	Where the transactions were recorded: Guest: reservation External: external folio
Arrival and Departure	Informative, for analysis purposes
Status	Status at the time of report retrieval: In-house Checked-out Canceled No-Show Confirmed

* Accounts are presented either in debit or in credit

These accounts are always in debit:

all payments, i.e. all accounts in group # 1000
# 1200 Receivables
# 9999 Receivables from LuL
# 9000 House account (separate receivables account against the house itself)
# 7000 Transitory items
Auxiliary account “Offset account” ..._Liabilities/Offset”
Only the gross amount is relevant for accounts on the debit side.

These accounts are always in credit:

All revenue accounts, i.e. all accounts in group # 5000
# 6000 City Tax
all liabilities aka deposit aka advance payments, split according to tax rates (this corresponds to the
sum of the Apaleo accounts 3000 liabilities and 8001 VAT on advance payment)

Why Is an Account Presented in Debit or Credit in the Gross Transaction List? 🤔

The goal is to enable simple evaluations, whether through filters in Excel, analyses using a pivot table, or utilizing this data for import into the general ledger.

For example:

If you want to view all transactions on account 1102 Visa Credit Card Payment, you can filter by the columns "Debit Account Name" for Visa Credit Card Payment or "Debit Account Number" for 1102. The amounts can then be summed.
If you want to find all transactions related to payments, you can filter by "Debit Account Number" and enter "1." The amounts can then be summed.
If you want to see the movements for liabilities over a specific period, you can either filter by the column "Credit Account Name" with the entry "Liability" or by the column "Credit Account Number" with "_liabilities." The amounts can then be summed.

How are Transactions that Affect Both Debit and Credit Accounts Presented?

To clearly present transactions that affect both debit and credit accounts, Apaleo uses "offset accounts." These accounts help keep everything balanced, ensuring that the total amount remains accurate when money is moved between accounts.

What Is an Offset Account? 🤔

An offset account is a temporary account used to balance transactions between debit and credit sides. Imagine it as a helper account that ensures no money is lost or unaccounted for during a transaction. When money is moved from one account (like paying a bill or booking a service), the offset account tracks this movement to keep everything in balance. At the end of the transaction, the offset account will have a balance of zero, showing that everything has been accounted for.

Table of Offset Accounts 📋

Account Name	Account Number
Receivables for reservation xx (Offset account)	xx_Receivables/Offset
Receivables from LuL for reservation xx (Offset account)	xx_AccountsReceivable/Offset
Liabilities for reservation xx (Offset account)	xx_Liabilities/Offset

Here, the "xx" represents the reservation ID. The sum of the offset accounts is always 0.

Example 📝

Imagine a guest staying at the hotel who decides to pay for their services later with an invoice. The resulting receivable on account 1200 Receivables is offset by the receivable on account 9999 Receivables from LuL.

Since both accounts are receivables and shown on the debit side, two transaction lines are needed. On the credit side, the amounts in the offset accounts balance each other out, so they add up to zero.

These transactions are linked by the same entry number.

Examples of Journal Entries (AKA records of financial transactions) 🧾

Journal Entry (AKA record of a financial transaction):
#9999 Receivable from LuL to #1200 Receivable

Journal Entry in Raw Data (AKA record of a financial transaction):
#ReservationID_AccountsReceivable to #ReservationID_Receivables

In the system, this is shown as one journal entry—with one entry number—but presented in one line with the amount. If the amount is positive, the "_AccountsReceivable" account is debited, and the "_Receivables" account is credited.

Journal Entry in the Gross Transaction List 📑

One journal entry—with one entry number—is shown in two lines with opposite amounts, ensuring that everything is balanced.

Why Use the Gross Transaction Export? 📊

The list of all Gross Transactions can be used for several purposes:

Identifying Transactions: You can identify transactions for one or more accounts by setting a filter in the header.
Tracking Receivables: This report helps you find reservations that show a receivable on account 1200 within the selected period, especially useful at the end of the month.
Tracking Liabilities: Similarly, it allows you to identify reservations that show a liability within the selected period, especially useful at the end of the month.

For tracking receivables and liabilities, you can create a pivot table over all columns using the following parameters:

Use case: Tracking #1200 Receivables 💵

Receivables Overview

With a filter on Debit Account Number "_rec":

Debit Account Filter

Result – Example:

For example, viewing the list of all bookings from entry number 1 shows the reservations that have an outstanding receivable balance up to the time the file is exported.

By selecting from entry number 1 to the end of a month, e.g., January 31, 2024, you get the reservations that make up the balance of account 1200 Receivables in Apaleo. The sum can also be cross-checked with the Total debited and credited finance export.

Additionally, you can check the validity of a receivable based on the departure date. A reservation with a receivable and departure on February 17, 2021, queried up to the business day of 27th June, 2024, indicates that this reservation in Apaleo has an open balance and needs to be reviewed.

Possible Reasons:

The guest has departed, but no payment was recorded.
The guest has departed and was allowed to check out "on AR/Accounts Receivables," but no accounts receivable invoice was created.
Services were only partially paid.
A service was posted directly on the folio (e.g., with today's business day) even though the guest's arrival is in the future.

Use Case: Tracking Liabilities (Apaleo Accounts #3000 Liabilities and #8001 VAT on Prepayments)💸

With a filter on Credit Account Number "_liabi":

Result – Example Excerpt:

For example, viewing the list of all bookings from entry number 1 shows the reservations that have a liability up to the time the file is exported, separated by tax rates.

By selecting from entry number 1 to the end of a month, e.g., January 31, 2021, you get the reservations that make up the sum of the balances in accounts 3000 Liabilities and 8001 VAT on Prepayments in Apaleo. The amount can be cross-checked with the sum of these accounts in the report Sum of Debit/Credit Bookings per Account.

Additionally, you can check the validity of a liability based on the travel date. A reservation with a liability and departure on February 17, 2021, queried up to the business day of February 28, 2021, indicates that this reservation in Apaleo has an open balance and needs to be reviewed.