Multicurrency books and courses

1) What are "multicurrency books"

• Wallet-book: player's account (game currency/currencies).
• Settlement-book: settlements with PSP/acquirer (settlement currency).
• Treasury-book: bank accounts, conversions, hedge deals.
• Reporting ledger: summary in reporting currency (for example, EUR) without retrospective revaluation of product metrics.

Each book records the transaction currency, presentation currency, and rate at the time of recognition.

2) Hierarchy of currencies and policies

1. Base/Reporting Currency - single reporting currency (for example, EUR).
2. Wallet Currencies - purse currencies (USD/EUR/TRY/UAH, etc.).
3. Settlement Currencies - what the PSP sends (for example, USD).
4. Bank Currencies - Merchant/Treasury account currency.

• Product metrics (ND/NGR/ARPPU) - at the historical rate of the event (usually 'settled _ at').
• Finance/treasuries - additionally fix the course on 'funded _ at' and 'payout _ at'.
• "Hidden" revaluation in food displays is prohibited.

3) Courses and their sources

Intraday reference (tick/minute): Refinitiv/ECB/banks - to normalize events.
EOD (end of day): for unrealized FX.
Effective FX: from PSP/bank file (actual conversion).
Triangulation: through an anchor (EUR or USD) in the absence of a direct quotation.
Quote policy: `mid` либо `bid/ask` → `mid ± spread_bps`. The spread is stored separately.

Store: 'fx _ source', 'fx _ pair', 'fx _ rate', 'fx _ timestamp', 'quote _ type', 'spread _ bps', and triangulation route.

4) Recognition points and course layers

'authorize _ at '- we do not fix the course (there is no recognition without capture).
'captured/settled _ at '- historical course for product layer and ND.
'funded _ at '- exchange rate per bank (realized FX for FI/treasury).
'payout _ at '- rate when paid to the player.
'eod'is the end-of-day rate for unrealized FX.

5) Precision, rounding, minimum units

Money - whole minor units (int) + 'scale' in the currency directory.
Courses - at least 8-10 decimal places.
Rounding: bank (half-even) for reporting; in UI - local rules.
Maintain separate fields: 'amount _ original', 'amount _ wallet', 'amount _ reporting', 'amount _ effective'.

6) Double entry and GL card (simplified)

6. 1. DEPOSIT_CAPTURED (GBP, reporting - EUR)

JT: AR: PSP (GBP)

Ct: Player Balance (GBP/EUR by wallet)

In parallel, fix 'fx _ rate _ settle (GBP→EUR)' and 'amount _ reporting'.

6. 2. FUNDING_RECEIVED (USD per bank)

Dt: Bank USD

Ct: AR: PSP GBP (close by equivalent; difference → Realized FX).

6. 3. WITHDRAWAL_PAID (TRY)

Дт: Liability: Player (TRY)

Ct: Bank TRY (or Bank EUR + conversion; difference - realized FX).

7) Data architecture (minimal model)

ref. currencies (
code PK, scale, symbol, is_crypto, is_active
)

ref. fx_rates_intraday (
pair PK, ts PK, rate, quote_type, source, spread_bps, triangulation_meta
)

ref. fx_rates_eod (
pair PK, date PK, rate, source
)

ledger. entries (
entry_id PK, book, -- WALLET      SETTLEMENT      TREASURY      REPORTING debit_account, credit_account,
amount_original, currency_original,
amount_reporting, reporting_currency,
fx_rate_at_settle, fx_source, fx_pair, fx_timestamp,
event_type, event_id, user_id, provider, method,
occurred_at, created_at, meta
)

treasury. funding_receipts (
funding_id PK, provider, bank_account,
currency, amount, fx_to_reporting, amount_reporting,
received_at, value_date, meta
)

treasury. balances (
date PK, account PK, currency PK, amount
)

dw. transactions_flat (
tx_id PK, user_id, provider, method, type, status,
amount_original, currency_original,
amount_reporting, reporting_currency, fx_rate_at_settle,
settled_at, funded_at, conversion_owner, meta
)

8) Normalization flow (ETL/ELT)

1. Raw material ingest → PSP status normalization.
2. Assignment of historical rate to 'settled _ at' from 'fx _ rates _ intraday'.
3. Write a double transaction to the desired ledger (wallet/settlement).
4. Separate imports funding and effective FX.
5. Daily reval of balances by 'fx _ rates _ eod' (treasury-book).
6. Shop window construction (ND/NGR/LTV) without retro reassessment.

9) Reconciliation and consistency

Tx→File: all captured/settled were in the PSP file (by amounts/currencies/dates).
File→Tx: everything in the file is reflected in books/transactions.
FX Reference vs Effective: count 'slippage _ bps'; alert to go beyond the threshold.
Check of triangulation: 'A→B B→C' ≈ 'A→C' within a bps-tolerans.
Idempotency: 'event _ id' and 'idempotency _ key' - protection against duplicates.

10) Frequent scenarios and how to conduct them

Multi-wallet: the player's wallet can be in a different currency than the deposit - do an internal conversion at your rate ('conversion _ owner =' MERCHANT 'policy).
PSP-conversion: store 'fx _ effective', 'fx _ reference' and 'spread _ bps' for PSP margin analysis.
Crypto: VWAP window score; funding in stables is the second layer of FX.
Cross-wallet transfer: movement within the platform - without FX income, just transfer between books.

11) SQL templates

11. 1. Normalization of the amount to the reporting currency at the historical rate

sql
SELECT t. tx_id,
t. amount_original,
t. currency_original,
r. rate AS fx_rate_at_settle,
ROUND(t. amount_original r. rate, rep. scale) AS amount_reporting
FROM raw. transactions t
JOIN ref. fx_rates_intraday r
ON r. pair = CONCAT(t. currency_original, '/',:reporting_ccy)
AND r. ts = (
SELECT MAX(ts) FROM ref. fx_rates_intraday
WHERE pair = r. pair AND ts <= t. settled_at
)
JOIN ref. currencies rep ON rep. code =:reporting_ccy
WHERE t. settled_at BETWEEN:from AND:to;

11. 2. PSP spread measurement (effective vs reference)

sql
SELECT provider, method, DATE(settled_at) AS d,
SUM(original_amount fx_reference_rate) AS ref_in_reporting,
SUM(settlement_amount_in_reporting)   AS eff_in_reporting,
10000 (SUM(settlement_amount_in_reporting) /
NULLIF(SUM(original_amount fx_reference_rate),0) - 1) AS spread_bps
FROM dw. fx_settlement_view
WHERE settled_at BETWEEN:from AND:to
GROUP BY 1,2,3;

11. 3. unrealized FX

sql
INSERT INTO treasury. fx_reval_ledger (date, currency, position_amount, rate_eod, amount_reporting_eod, reval_diff, type)
SELECT
:eod AS date, b. currency, b. amount,
e. rate AS rate_eod,
b. amount e. rate AS amount_reporting_eod,
b. amount (e. rate - COALESCE(l. rate_eod, e. rate)) AS reval_diff,
'UNREALIZED'
FROM treasury. balances b
JOIN ref. fx_rates_eod e
ON e. pair = CONCAT(b. currency, '/',:reporting_ccy) AND e. date =:eod
LEFT JOIN LATERAL (
SELECT rate_eod FROM treasury. fx_reval_ledger
WHERE currency=b. currency AND date=:eod - INTERVAL '1 day'
ORDER BY date DESC LIMIT 1
) l ON TRUE;

12) KPI and dashboards

FX Slippage (bps) by PSP/Method/MID.
Realized FX P&L (funding/payout) и Unrealized FX (EOD/EOM).
Open FX Position by currency vs policy limits.
Hit-rate "on time" courses (stale-rates incidents).
Share of PSP-conversion vs Merchant-conversion and its TCO.
Rounding accuracy (deviations of amounts ≥ 1 minor unit - flag).

13) Alerts and thresholds

Stale rate: no rate at the time of the event → fallback source/retray.
Triangulation mismatch: divergence> X bps.
Spread spike: 'spread _ bps' above threshold in majors/majors.
Open position breach: exceeding the limit for any currency.
Reval shock: daily revaluation <− X σ or> + X σ - review.

14) Best practices (short)

1. Strictly separate the product layer (historical FX) and FI/treasury (funding/payout/reval).
2. Use minor units and store courses with high accuracy.
3. Log the conversion_owner and measure the PSP margin (effective vs reference).
4. Implement event idempotency and two-way reconciliation (Tx→File and File→Tx).
5. Hold anchor currency for triangulation and validate discrepancies in bps.
6. Reassess the balances with an EOD procedure with a separate GL.
7. Consider DST/timezones when assigning courses to settle/funding.
8. Regularly test property-based tests on scale boundaries.

15) Implementation checklist

• Reporting currency and historical FX policy defined.
• Course sources: intraday + EOD, fallback and SLA updates.
• Models'ledger. entries`, `fx_rates_`, `funding_receipts`, `balances`, витрина `transactions_flat`.
• Triangulation mechanism and route log.
• Алерты: stale-rates, spread spike, triangulation mismatch, open position breach.
• KPI dashboards and PSP/bank reconciliation reports.
• reval procedures and separate GLs for realized/unrealized FX.
• Test sets of rounding and storage accuracy.

Summary

Multicurrency books are a discipline of layer separation: historical FX for a product, actual for a trejerie, EOD revaluation for balance sheets. With transparent rate sources, an accurate data model, double-entry, and automated reconciliation, you will eliminate "currency noise" from analytics, provide auditing, and get managed FX risk in global monetization.