Ledgers and balances

Every financial figure in GiroPro derives from two event streams:

• common_c_data_duplication_ledgeritem_changed - which ledgers exist and what each one is for (the dimension).
• ledger_e_journal_entry_added - every booking on those ledgers (the facts).

This page shows how to combine them into correct balances. The next page, Calculating Portfolio Value, builds on these balances.

Step 1 - find the ledger details

Keep the latest Ledger (LedgerItem) per Payload.id, as described on Data Lake. The fields that matter:

field	meaning
ownerType / ownerId	who the ledger belongs to. For investor holdings: InvestorAccount and the account id.
ledgerType	Asset (counts units) or Cash (counts money).
ledgerSubType	the role of the ledger. Normal: the regular holdings ledger - the units or cash actually held. Reserved: amounts set aside for a pending trade, for example units reserved while a sell order is open. Dividend: dividend bookings.
subTypeReferenceId	for Asset ledgers: the asset id. For Cash ledgers: the currency id. (The actual id here is not really important if you are denominated in one currency e.g. EUR)

One ledger record therefore reads as: “the free unit count of asset X, owned by investor account Y”.

Do not use credit and debit on the ledger event as a balance. They are a calculated value in GiroPro and act as placeholders and contain no actual value when sent to the Data Lake. Balances come from journal entries only.

Step 2 - derive balances from journal entries

Each JournalEntry (JournalEntryItem) contains lines, and every line books a credit or debit on one ledger:

balance(ledger) = sum(line.credit) - sum(line.debit)
                  over all journal entry lines with that ledgerId

Deduplicate deliveries first (envelope Id), and keep each journal entry once by its Payload.id.

from pyspark.sql import functions as F

balances = (
    journal_entries                       # one row per unique journal entry
    .select(F.explode("lines").alias("line"))
    .groupBy("line.ledgerId")
    .agg((F.sum("line.credit") - F.sum("line.debit")).alias("balance"))
)

Example - two journal entries booking on the units ledger UNITS-FUND-A:

journal entry	credit	debit
BUY-100-UNITS	100.0	-
SELL-10-UNITS	-	10.0

Balance of UNITS-FUND-A = 100.0 - 10.0 = 90.0 units.

Step 3 - always filter on the owner

GiroPro is double-entry bookkeeping: every booking on an investor’s ledger has a mirror booking on a counter-ledger (owner Custodian or Omnibus) of the same ledgerType. Summing over all Asset ledgers therefore always yields exactly 0 - that is the books balancing, not a data problem:

sum over ledgerType = 'Asset', all owners            :          0.0000
sum over ledgerType = 'Asset', owner InvestorAccount : +1,068,408.9636
sum over ledgerType = 'Asset', owner Custodian       : -1,068,408.9636

For customer holdings, always select ownerType = 'InvestorAccount' first.