QlikSense ETL Analysis

📊 Overview: Equity & Trading Reports Category

This section provides complete data lineage for all equity and trading reports, covering the most complex business logic in the application including profit/loss calculations, transaction cost breakdowns, and previous day comparisons.

Reports Covered (11 Reports):

Sheet 02 Reports (9 reports):

RPT_000003 - Trade Transaction – Equity
RPT_000030 - Details of Investment
RPT_000047 - Status of Shares Sold
RPT_000048 - Status of Purchase Shares
RPT_000060 - Daily Summary of Sale Contract Note
RPT_000061 - Daily Summary of Purchase Contract Note
RPT_000197 - Equity Contract Note
RPT_000206 - Daily Status of Shares Sold
RPT_000207 - Daily Status of Purchased Shares

Sheet 03 Reports (2 reports):

RPT_000209 - Share Purchase Order
RPT_000210 - Share Sold Order

Sheet 04 Report (1 report):

RPT_000211 - Purchase and Sales of Shares

Sheet 05 Reports (3 reports - overlaps with Sheet 02):

RPT_000003 - Trade Transaction – Equity (also in Sheet 02)
RPT_000030 - Details of Investment (also in Sheet 02)
RPT_000025 - Details of Investment Activities

🔄 Complete Data Flow Diagram

🔗 Interactive Diagram: View Complete Equity & Trading Reports Data Lineage Flow in Figma →

┌─────────────────────────────────────────────────────────────────────┐
│                     RAW DATA SOURCES (QVD)                           │
└─────────────────────────────────────────────────────────────────────┘
                                    ↓
        ┌───────────────────────────┼────────────────────────────┐
        ↓                           ↓                            ↓
┌──────────────┐       ┌────────────────────┐       ┌──────────────────┐
│Transactions  │       │ Transactions       │       │ Transactions     │
│   .qvd       │       │ Profit Loss.qvd    │       │ Costs.qvd        │
└──────┬───────┘       └─────────┬──────────┘       └────────┬─────────┘
       │                         │                            │
       ↓                         ↓                            ↓
┌─────────────────────────────────────────────────────────────────────┐
│                      MAPPINGS PHASE                                  │
│  - mBusTransCode, mSecId, mCostType, mBroker, etc.                 │
└─────────────────────────────┬───────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────────┐
│           CORE TRANSACTIONS TABLE (Transactions.txt)                 │
│  - Base Load with Calculated Fields                                 │
│  - LEFT KEEP: Transactions Profit Loss (Cost Value, P&L)            │
│  - LEFT KEEP: Transactions Costs (Brokerage, Fees, Stamp Duty)      │
│  - Calculate: IsS2, IsS3 flags for sheet filtering                  │
│  - Calculate: Buy or Sell classification                            │
│  - Calculate: Previous Day Values (from BalBook transactions)       │
│  - Calculate: Sheet5Rpt003Level composite key                       │
└─────────────────────────────┬───────────────────────────────────────┘
                              ↓
        ┌─────────────────────┼─────────────────────┬──────────────┐
        ↓                     ↓                     ↓              ↓
┌──────────────┐   ┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│  Sheet_02    │   │  Sheet_03    │   │  Sheet_04    │   │  Sheet_05    │
│              │   │              │   │              │   │              │
│ IsS2 Filter: │   │ IsS3 Filter: │   │ AllocBuyExt  │   │ Complex P&L  │
│ Equity +     │   │ Equity +     │   │ AllocSellExt │   │ Calculations │
│ ETF/REIT     │   │ Corp Actions │   │ Only         │   │              │
│ Buy/Sell     │   │ with Price>0 │   │              │   │ 3 Filter     │
│              │   │              │   │ Summary by   │   │ Combinations │
│ Auxiliary:   │   │ Auxiliary:   │   │ Buy/Sell     │   │              │
│ - S2AuxPor   │   │ - S3AuxPor   │   │              │   │ Auxiliary:   │
│              │   │              │   │ 6 Measures   │   │ - S5AuxPor   │
│ 11 Measures  │   │ 3 Measures   │   │              │   │ - S5Aux003   │
│              │   │              │   │              │   │              │
│              │   │              │   │              │   │ 28 Measures  │
└──────┬───────┘   └──────┬───────┘   └──────┬───────┘   └──────┬───────┘
       │                  │                  │                  │
       └──────────────────┼──────────────────┼──────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────────────────┐
│                   FINAL REPORTS (NPrinting)                          │
│  RPT_000003 | RPT_000030 | RPT_000025 | RPT_000047 | RPT_000048    │
│  RPT_000060 | RPT_000061 | RPT_000197 | RPT_000206 | RPT_000207    │
│  RPT_000209 | RPT_000210 | RPT_000211                               │
└─────────────────────────────────────────────────────────────────────┘

📦 Phase 1: Raw Data Sources (QVD Files)

Source 1: Transactions.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd]

Key Fields Used for Equity Reports:

Field Name	Data Type	Description	Purpose
`Bus Trans No`	Text	Transaction Number	Unique identifier
`Trade Date Full`	Date	Transaction Date	All reports
`Settlement Date Full`	Date	Settlement Date	Contract notes
`Price`	Number	Unit price	Calculations
`Nominal`	Number	Number of shares/units	Quantity
`Nominal Basis`	Number	Basis for calculations	Bonus issues, ratios
`Current Value Pc`	Number	Gross value in Portfolio Currency	Before fees
`Current Value Qc`	Number	Gross value in Quotation Currency	Before fees
`Payment Amount Pc`	Number	Net payment in Portfolio Currency	After all costs
`Payment Amount Qc`	Number	Net payment in Quotation Currency	After all costs
`SECURITY_KEY`	Key	Security identifier	Joins
`PORTFOLIO_KEY`	Key	Portfolio identifier	Joins
`HOLDING_KEY`	Key	Holding identifier	Previous day tracking
`BUS_TRANS_CATEGORY_KEY`	Key	Transaction category	Mappings
`Fx Rate Qc Pc`	Number	FX rate QC to PC	Currency conversion
`Ifrs9Purpose`	Text	IFRS9 classification	Sheet 05 grouping
`ELEMENTARY_TRANS_KEY`	Key	Elementary transaction type	Sheet 05 grouping

Filter Applied:

WHERE [Transaction Cancellation No] = 0
  AND [Transaction Status Level No] >= 30

Source 2: Transactions Profit Loss.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Transactions Profit Loss/Transactions Profit Loss.qvd]

Key Fields:

Field Name	Description	Purpose
`Transaction Ik`	Transaction key (join key)	Link to Transactions
`ACCOUNTING_FRAMEWORK_KEY`	Accounting framework	Multiple accounting standards
`Booked Current Value Pc`	Booked current value (MYR)	Accounting value
`Booked Current Value Qc`	Booked current value (QC)	Accounting value
`Cost Value Pc`	Cost basis in Portfolio Currency	Cost tracking
`Cost Value Qc`	Cost basis in Quotation Currency	Cost tracking
`Cost Value Myr`	Cost value in MYR	Reporting
`Por Lcost Myr`	Portfolio L-cost MYR	Realized P&L component
`Por Lcost Qc`	Portfolio L-cost QC	Realized P&L component
`Por Lcost Sec Pc`	Portfolio L-cost Security PC	Security P&L
`Por Lcost Ccy Pc`	Portfolio L-cost Currency PC	Forex P&L
`Total Realised PL Cost Pc`	Total realized P&L	Complete P&L
`Payment Date Full`	Payment date	Settlement tracking
`Finally Booked`	Final booking flag	Filter condition

Filters Applied:

WHERE "Accounting Framework Ik" > 0
  AND "Transaction Cancellation No" = 0
  AND [Transaction Status Level No] >= 30
  AND [Finally Booked] = 1

Join Type: LEFT KEEP (Transactions) - Only keeps P&L records for existing transactions

Key P&L Fields Explained:

Cost Value: Original cost basis for calculating gain/loss
Por Lcost (Portfolio Loss/Cost): Components of realized P&L
- Por Lcost Sec Pc: Security price change component
- Por Lcost Ccy Pc: Foreign exchange component
- Por Lcost Myr / Por Lcost Qc: Total in different currencies

Source 3: Transactions Costs.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Transactions Costs/Transactions Costs.qvd]

Key Fields:

Field Name	Description	Purpose
`Transaction Ik`	Transaction key (join key)	Link to Transactions
`COST_TYPE_KEY`	Cost type key	Mapping to cost descriptions
`Cost Class`	Cost classification	STAMP_DUTY, etc.
`Amount Myr`	Cost amount in MYR	Cost tracking
`Amount Portfolio Currency`	Cost in portfolio currency	Main currency
`Amount Quotation Currency`	Cost in quotation currency	Security currency

Filter Applied:

WHERE "Transaction Cancellation No" = 0

Join Type: LEFT KEEP (Transactions) - Only keeps costs for existing transactions

Cost Types (from General_Filters.txt):

// Brokerage
BROKER, COMMISSION

// Brokerage SST (Service Tax)
BROKER_SST

// Scan Fee (Clearing fee)
MY_CLRG

// Scan Fee GST
SCAN_SST

// Stamp Duty
*STMP (wildcard match)
OR Cost Class = "STAMP_DUTY"

// Other Fees
Everything except above = Transaction fees

📦 Phase 2: Calculated Flags in Transactions Table

Flag 1: IsS2 (Sheet 02 Filter)

Calculated In: Transactions.txt (lines 5-7)

IsS2 = ((
    MATCH([Instrument Type], 'Equity', 'GDR/ADR', 'Right', 'Warrant', 'Covered warrant')
    OR (
        [Instrument Type] = 'Fund certificate' 
        AND MATCH([Security Type], 'ETF', 'ETP', 'ETN', 'REIT')
    )
) AND MATCH([Buy or Sell], 'Buy', 'Sell'))

Business Logic:

IsS2 = TRUE when:
  1. Instrument is Equity, GDR/ADR, Right, Warrant, or Covered warrant
     OR
  2. Instrument is Fund certificate AND Security Type is ETF/ETP/ETN/REIT
  
  AND
  
  3. Transaction is Buy or Sell (not balance book, not other types)

Used By: Sheet 02 reports (all 9 reports)

Flag 2: IsS3 (Sheet 03 Filter)

Calculated In: Transactions.txt (lines 8-17)

IsS3 = ((
    MATCH([Instrument Type], 'Equity', 'GDR/ADR', 'Right', 'Warrant', 'Covered warrant')
    AND MATCH([Bus Trans Code], 'Buy', 'Sell', 'AllocBuyExt', 'AllocSellExt')
) OR (
    MATCH([Instrument Type], 'Equity', 'GDR/ADR', 'Right', 'Warrant', 'Covered warrant')
    AND [Bus Trans Category] = 'Corporate actions'
    AND Price > 0
))

Business Logic:

IsS3 = TRUE when:
  Condition 1: Equity instruments AND Buy/Sell transaction codes
  
  OR
  
  Condition 2: Equity instruments AND Corporate action AND Price > 0

Note: The comment in code shows this was changed on 2025.05.21 (ticket HA-26641) to remove the "Security Quoted = Not quoted" filter

Used By: Sheet 03 reports (RPT_000209, RPT_000210)

Flag 3: Sheet5Rpt003Level (Sheet 05 Grouping)

Calculated In: Transactions.txt (line 17)

Sheet5Rpt003Level = [Security Type] & '|' & Ifrs9Purpose & '|' & [Elementary Trans Code]

Purpose: Creates a composite key for grouping in RPT_000003

Example Values:

"ETF|FVTPL|BuyDirect"
"Equity|FVTPL|SellDirect"
"Right|HeldForTrading|Exercise"

Used By: Sheet 05 reports for detailed classification

Flag 4: Buy or Sell Classification

Calculated In: Transactions.txt (lines 30-32)

"Buy or Sell" = IF(
    "Bus Trans Code" <> 'BalBook' AND [Current Value Qc] > 0, 'Sell',
    IF(
        ("Bus Trans Code" <> 'BalBook' AND [Current Value Qc] < 0) 
        OR "Bus Trans Code" = 'OpenBalance', 
        'Buy'
    )
)

Business Logic:

IF Transaction is not BalBook:
    IF Current Value > 0 → 'Sell' (money coming in)
    IF Current Value < 0 → 'Buy'  (money going out)
ELSE IF Transaction is OpenBalance:
    → 'Buy' (opening position)
ELSE:
    → NULL (balance book transactions)

Critical Note: This is based on the sign of Current Value, not the transaction code alone

📦 Phase 3: Previous Day Calculations (Complex Logic)

Purpose:

Calculate previous day's values for mark-to-market and unrealized P&L calculations

Step-by-Step Process:

Step 1: Load All Transactions with BalBook (lines 255-266)

[TmpPrevDay]:
LOAD
    [Transaction Ik] AS TmpTx,
    HOLDING_KEY AS TmpHk,
    Num([Trade Date]) AS TmpTD
RESIDENT [Transactions] 
WHERE NOT MATCH([Bus Trans Code], 'BalBook');  // Regular transactions

JOIN LOAD DISTINCT
    HOLDING_KEY AS TmpHk,
    Num([Trade Date]) AS TmpBBTD
RESIDENT [Transactions] 
WHERE MATCH([Bus Trans Code], 'BalBook');      // Balance book transactions

Result: Table with each transaction linked to all balance book dates for same holding

Step 2: Find Last Balance Book Before Each Transaction (lines 267-273)

[MaxPrevDay]:
LOAD 
    TmpTx, 
    TmpHk, 
    TmpHk & '|' & MAX(TmpBBTD) AS TmpLastBBTD
RESIDENT TmpPrevDay
WHERE TmpBBTD < TmpTD           // Balance book date BEFORE transaction date
GROUP BY TmpTx, TmpHk, TmpTD;

Business Logic:

For each transaction:
  Find MAX(BalBook date) where BalBook date < Transaction date
  This is the "previous day" balance

Step 3: Get Previous Day Values (lines 275-290)

[PrevDayTx]:
LOAD DISTINCT
    TmpTx,
    TmpLastBBTD
RESIDENT MaxPrevDay;

INNER JOIN LOAD DISTINCT
    HOLDING_KEY & '|' & Num([Trade Date]) AS TmpLastBBTD,
    [Transaction Ik] AS TmpPrevTransactionIk,
    [Current Value Myr] AS TmpPrevValueMyr,
    [Current Value Qc] AS TmpPrevValueQc,
    [Current Value Pc] AS TmpPrevValuePc,
    [Balance Cost Value Pc] AS TmpPrevCostValPc,
    [Balance Cost Value Qc] AS TmpPrevCostValQc,
    [Nominal Basis] AS TmpNom
RESIDENT [Transactions] 
WHERE MATCH([Bus Trans Code], 'BalBook');

Result: For each transaction, get the balance book values from previous day

Step 4: Calculate Per-Share Previous Values (lines 293-302)

LEFT JOIN (Transactions)
LOAD 
    TmpTx AS [Transaction Ik],
    TmpPrevValueMyr / TmpNom AS [Previous Value Myr],
    TmpPrevValueQc / TmpNom AS [Previous Value Qc],
    TmpPrevValuePc / TmpNom AS [Previous Value Pc],
    TmpPrevCostValQc / TmpNom AS [Previous Cost Value Qc],
    TmpPrevCostValPc / TmpNom AS [Previous Cost Value Pc],
    TmpPrevTransactionIk AS [Previous Transaction Ik]
RESIDENT PrevDayTx;

Result: Transactions table now has per-share previous day values

Fields Added:

Previous Value Myr - Yesterday's price per share (MYR)
Previous Value Qc - Yesterday's price per share (QC)
Previous Value Pc - Yesterday's price per share (PC)
Previous Cost Value Qc - Yesterday's cost per share (QC)
Previous Cost Value Pc - Yesterday's cost per share (PC)
Previous Transaction Ik - Reference to balance book transaction

Used In: Sheet 05 for mark-to-market and reversal of unrealized calculations

📦 Phase 4: Sheet-Specific Transformations

Sheet 02 - Equity Contract Notes (9 Reports)

Auxiliary Tables:

S2AuxNprintingPorGroup:
LOAD  
    [Transaction Ik],
    [Portfolio Group] AS [Portfolio Group S2],
    [Portfolio Group Name] AS [Portfolio Group Name S2],
    IF(WildMatch([Bus Trans Code], '*Buy*'), [Portfolio Group]) AS [Portfolio Group S2 Buy],
    IF(WildMatch([Bus Trans Code], '*Sell*'), [Portfolio Group]) AS [Portfolio Group S2 Sell],
    [Trade Date] AS [Trade Date S2],
    [Settlement Date] AS [Settlement Date S2]
RESIDENT Transactions
WHERE IsS2;

Purpose:

Portfolio filters for NPrinting
Separate Buy/Sell portfolio dimensions
Date dimensions for selection

Sheet 02 - Filter Definition:

LET xFILTER_SHEET02 = '[IsS2]={"-1"}, [Bus Trans Code] = {"*Buy*","*Sell*"}';

Equivalent to:

WHERE IsS2 = TRUE (flag value -1 in QlikSense)
  AND Bus Trans Code matches wildcard pattern *Buy* or *Sell*

Includes:

Buy, AllocBuyExt, Shape Buy
Sell, AllocSellExt, Shape Sell
Equity, GDR/ADR, Rights, Warrants, Covered Warrants
ETF, ETP, ETN, REIT (fund certificates)

Sheet 02 - Measure Variables (11 Measures):

// 1. Quantity
LET xSHEET02_Quantity = 'Sum({<$(xFILTER_SHEET02)>} [Nominal])';

// 2. Gross Amount (MYR) - before any costs
LET xSHEET02_GrossAmountMyr = 'Sum({<$(xFILTER_SHEET02)>} [Current Value Pc])';

// 3. Gross Amount (QC) - before any costs
LET xSHEET02_GrossAmountQc = 'Sum({<$(xFILTER_SHEET02)>} [Current Value Qc])';

// 4. Brokerage (MYR) - commission only
LET xSHEET02_BrokerageMyr = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Portfolio Currency])';

// 5. Brokerage (QC)
LET xSHEET02_BrokerageQc = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Quotation Currency])';

// 6. Brokerage SST (MYR) - service tax on brokerage
LET xSHEET02_BrokerageSSTMyr = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Portfolio Currency])';

// 7. Brokerage SST (QC)
LET xSHEET02_BrokerageSSTQc = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Quotation Currency])';

// 8. Stamp Duty (MYR) - uses Cost Class instead of Cost Type
LET xSHEET02_StampDutyMyr = 
    'Sum({<$(xFILTER_SHEET02), [Cost Class] = {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])';

// 9. Scan Fees (MYR) - clearing fees
LET xSHEET02_ScanFeesMyr = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] = {"MY_CLRG"}>} 
          [Amount Portfolio Currency])';

// 10. Transaction Fees (MYR) - all fees except brokerage
LET xSHEET02_FeesMyr = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] -= {"BROKER","COMMISSION"}>} 
          [Amount Portfolio Currency])';

// 11. Transaction Fees (QC)
LET xSHEET02_FeesQc = 
    'Sum({<$(xFILTER_SHEET02), [Cost Type] -= {"BROKER","COMMISSION"}>} 
          [Amount Quotation Currency])';

// 12. Net Amount (MYR) - final payment after all costs
LET xSHEET02_NetAmountMyr = 'Sum({<$(xFILTER_SHEET02)>} [Payment Amount Pc])';

// 13. Net Amount (QC)
LET xSHEET02_NetAmountQc = 'Sum({<$(xFILTER_SHEET02)>} [Payment Amount Qc])';

Cost Breakdown Formula:

Net Amount = Gross Amount ± Brokerage ± Brokerage SST ± Stamp Duty ± Scan Fees ± Other Fees

For Buy:  Net = Gross + All Costs (outflow)
For Sell: Net = Gross - All Costs (inflow)

Sheet 02 Reports Summary:

Report	Primary Purpose	Key Measures
RPT_000003	Trade transactions	Quantity, Gross, Net
RPT_000030	Investment details	Quantity, Gross, Brokerage, Net
RPT_000047	Sold shares status	Sell transactions only
RPT_000048	Purchased shares status	Buy transactions only
RPT_000060	Daily sale summary	Aggregated by portfolio, date
RPT_000061	Daily purchase summary	Aggregated by portfolio, date
RPT_000197	Detailed contract note	All cost breakdowns
RPT_000206	Daily sold status	Daily monitoring
RPT_000207	Daily purchased status	Daily monitoring

Common Dimensions:

Security Name
Portfolio Group
Trade Date
Settlement Date
Counterparty/Broker

Sheet 03 - Share Orders (2 Reports)

Auxiliary Tables:

S3AuxNprintingPorGroup:
LOAD  
    [Transaction Ik],
    [Portfolio Group] AS [Portfolio Group S3]
RESIDENT Transactions
WHERE IsS3;

Sheet 03 - Filter Definition:

LET xFILTER_SHEET03 = 
    '(' & 
    '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant","Covered warrant"}, 
      [Bus Trans Code] *= {"Buy","Sell","AllocBuyExt","AllocSellExt"}>' &
    ' + ' &
    '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant","Covered warrant"}, 
      [Bus Trans Category] = {"Corporate actions"}, 
      Price = {">0"}, 
      [Security Quoted] = {"*"}>' &
    ')';

Business Logic:

Condition 1: Equity instruments + Buy/Sell transaction codes
OR
Condition 2: Equity instruments + Corporate actions + Price > 0 + Any quoted status

Key Change (2025.05.21 - HA-26641):

Old: Required "Security Quoted = Not quoted" for buy/sell
New: Removed this restriction, added quoted check only for corporate actions

Sheet 03 - Measure Variables (3 Measures):

// 1. No. of Shares
LET xSHEET03_NoOfShares = 'Sum({$(xFILTER_SHEET03)} [Nominal])';

// 2. Charges (MYR) - from Transactions Costs table
LET xSHEET03_Charges = 
    'Sum({$(xFILTER_SHEET03)} [Amount Portfolio Currency])';

// 3. Charges SST (MYR) - service tax on charges
LET xSHEET03_ChargesSst = 
    'Sum({$(xFILTER_SHEET03), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Portfolio Currency])';

// 4. Net Amount (MYR)
LET xSHEET03_NetAmountMyr = 
    'Sum({$(xFILTER_SHEET03)} [Payment Amount Pc])';

Difference from Sheet 02:

Focuses on charges/fees only (not detailed breakdown)
Includes corporate action transactions with price > 0
Simpler measure set

Sheet 03 Reports:

Report	Purpose	Key Feature
RPT_000209	Share Purchase Orders	Buy orders with charges
RPT_000210	Share Sold Orders	Sell orders with charges

Sheet 04 - Purchase and Sales Summary (1 Report)

No Auxiliary Tables

Sheet 04 - Filter Definition:

LET xFILTERs4_Equity = 
    '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant",
                             "Covered warrant","Fund certificate"}>';

LET xFILTER_SHEET04 = 
    '$(xFILTERs4_Equity) * <[Bus Trans Code] = {"AllocBuyExt","AllocSellExt"}>';

Business Logic:

ONLY AllocBuyExt and AllocSellExt transactions
(Allocated buy/sell external - actual market transactions)

Excludes:
- Internal allocations
- Shape buy/sell
- Direct buy/sell
- Corporate actions

Sheet 04 - Measure Variables (6 Measures):

// BUY METRICS
// 1. Buy Amount (MYR)
LET xSHEET04_BuyRm = 
    'Sum({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocBuyExt"}>} 
          [Payment Amount Pc])';

// 2. No. of Buy Trades
LET xSHEET04_BuyTrades = 
    'Count({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocBuyExt"}>} 
            DISTINCT [Bus Trans No])';

// 3. No. of Shares Bought
LET xSHEET04_BuyShares = 
    'Sum({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocBuyExt"}>} 
          [Nominal])';

// SELL METRICS
// 4. Sell Amount (MYR)
LET xSHEET04_SellRm = 
    'Sum({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocSellExt"}>} 
          [Payment Amount Pc])';

// 5. No. of Sell Trades
LET xSHEET04_SellTrades = 
    'Count({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocSellExt"}>} 
            DISTINCT [Bus Trans No])';

// 6. No. of Shares Sold
LET xSHEET04_SellShares = 
    'Sum({$(xFILTERs4_Equity)*<[Bus Trans Code] = {"AllocSellExt"}>} 
          [Nominal])';

Sheet 04 Report:

RPT_000211 - Purchase and Sales of Shares

Layout:

              | Buy Side      | Sell Side
--------------+---------------+---------------
Amount (MYR)  | xSHEET04_BuyRm| xSHEET04_SellRm
No. of Trades | xSHEET04_BuyTrades | xSHEET04_SellTrades
No. of Shares | xSHEET04_BuyShares | xSHEET04_SellShares

Purpose: High-level summary comparing buy vs sell activity

Sheet 05 - Transaction Details & P&L (3 Reports)

Most Complex Sheet - 375 Lines of Code

Auxiliary Tables:

// Portfolio filter
S5AuxNprintingPorGroup:
LOAD DISTINCT 
    [Portfolio Group], 
    [Portfolio Group] AS [Portfolio Group S5]
RESIDENT Transactions
WHERE (
    WILDMATCH([Bus Trans Category], '*Trades')
    OR ([Bus Trans Category]='Accounting' 
        AND MATCH([Bus Trans SubCategory], 'Valuation', 'Initialisation'))
    OR MATCH([Bus Trans Code], 'CABuy', 'CASell')
);

// Classification level filter (RPT_000003)
S5AuxNprinting003Level:
LOAD DISTINCT
    Sheet5Rpt003Level,
    Sheet5Rpt003Level AS S5Prt003Level
RESIDENT Transactions
WHERE (
    WILDMATCH([Bus Trans Category], '*Trades')
    OR ([Bus Trans Category]='Accounting' 
        AND MATCH([Bus Trans SubCategory], 'Valuation', 'Initialisation'))
    OR MATCH([Bus Trans Code], 'CABuy', 'CASell')
);

Key Difference from Sheet 02:

Includes Accounting transactions (Valuation, Initialisation)
Includes Corporate Action Buy/Sell (CABuy, CASell)
Not limited to Buy/Sell market trades

Sheet 05 - Filter Definitions (3 Separate Filters):

// Filter A: All Trades (External + Internal)
LET xFILTER_SHEET05A = '<[Bus Trans Category] = {"*Trades"}>';

// Filter B: Accounting (Valuation + Initialisation)
LET xFILTER_SHEET05B = 
    '<[Bus Trans Category] = {"Accounting"}, 
      [Bus Trans SubCategory] = {"Valuation", "Initialisation"}>';

// Filter C: Corporate Action Buy/Sell
LET xFILTER_SHEET05C = '<[Bus Trans Code] = {"CABuy", "CASell"}>';

// Combined filter
LET xFILTER_SHEET05 = '(' &
    '$(xFILTER_SHEET05A)' & '+' &
    '$(xFILTER_SHEET05B)' & '+' &
    '$(xFILTER_SHEET05C)' & 
')';

// Sell-only combined filter
LET xFILTER_SHEET05_SELL = 
    '<[Bus Trans Category] = {"*Trades"}, [Buy or Sell] *= {"Sell"}>' &
    '+' &
    '<[Bus Trans Code] = {"CASell"}>';

// Sell-only sub-filters
LET xFILTER_SHEET05_SELLA = 
    '<[Bus Trans Category] = {"*Trades"}, [Buy or Sell] *= {"Sell"}>';

LET xFILTER_SHEET05_SELLB = '<[Bus Trans Code] = {"CASell"}>';

Why 3 Separate Filters? Each measure uses all three filters separately and sums them:

Sum({$(xFILTER_SHEET05A)} [Field])
+ Sum({$(xFILTER_SHEET05B)} [Field])
+ Sum({$(xFILTER_SHEET05C)} [Field])

This ensures correct calculation across different transaction categories.

Sheet 05 - Measure Variables (28 Measures)

BASIC MEASURES:

// 1. No. of Shares (signed: Buy=+, Sell=-)
LET xSHEET05_NoOfShares = 
    'Sum({$(xFILTER_SHEET05A)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])
   + Sum({$(xFILTER_SHEET05B)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])
   + Sum({$(xFILTER_SHEET05C)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])';

// 2. Unit Price (MYR) - current price per share
LET xSHEET05_UnitPriceMyr = 
    'ALT(Sum({$(xFILTER_SHEET05A)} [Current Value Pc]/[Nominal])
       + Sum({$(xFILTER_SHEET05B)} [Current Value Pc]/[Nominal])
       + Sum({$(xFILTER_SHEET05C)} [Current Value Pc]/[Nominal]), 0)';

// 3. Unit Price (QC)
LET xSHEET05_UnitPriceQc = 
    'ALT(Sum({$(xFILTER_SHEET05A)} [Current Value Qc]/[Nominal])
       + Sum({$(xFILTER_SHEET05B)} [Current Value Qc]/[Nominal])
       + Sum({$(xFILTER_SHEET05C)} [Current Value Qc]/[Nominal]), 0)';

// 4. Gross Proceeds (MYR) - total before costs
LET xSHEET05_GrossProceedsMyr = 
    'Sum({$(xFILTER_SHEET05A)} [Current Value Pc])
   + Sum({$(xFILTER_SHEET05B)} [Current Value Pc])
   + Sum({$(xFILTER_SHEET05C)} [Current Value Pc])';

// 5. Gross Amount (QC)
LET xSHEET05_GrossAmountQc = 
    'Sum({$(xFILTER_SHEET05A)} [Current Value Qc])
   + Sum({$(xFILTER_SHEET05B)} [Current Value Qc])
   + Sum({$(xFILTER_SHEET05C)} [Current Value Qc])';

// 6. Net Proceeds (MYR) - after all costs
LET xSHEET05_NetProceedsMyr = 
    'Sum({$(xFILTER_SHEET05A)} [Payment Amount Pc])
   + Sum({$(xFILTER_SHEET05B)} [Payment Amount Pc])
   + Sum({$(xFILTER_SHEET05C)} [Payment Amount Pc])';

// 7. Net Proceeds (QC)
LET xSHEET05_NetProceedsQc = 
    'Sum({$(xFILTER_SHEET05A)} [Payment Amount Qc])
   + Sum({$(xFILTER_SHEET05B)} [Payment Amount Qc])
   + Sum({$(xFILTER_SHEET05C)} [Payment Amount Qc])';

COST BASIS MEASURES:

// 8. Average Cost per Share (MYR)
// Logic: For Buy, use Payment Amount (cost); For Sell, use Current Value
LET xSHEET05_AverageCostShareMyr = 
    'FABS(ALT(
        (
            Sum({$(xFILTER_SHEET05A)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])))
          + Sum({$(xFILTER_SHEET05B)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])))
          + Sum({$(xFILTER_SHEET05C)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])))
        ) / (
            Sum({$(xFILTER_SHEET05A)} [Nominal])
          + Sum({$(xFILTER_SHEET05B)} [Nominal])
          + Sum({$(xFILTER_SHEET05C)} [Nominal])
        ), 0))';

// 9. Average Cost per Share (QC)
LET xSHEET05_AverageCostShareQc = 
    'FABS(ALT(
        (
            Sum({$(xFILTER_SHEET05A)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Qc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Qc])))
          + Sum({$(xFILTER_SHEET05B)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Qc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Qc])))
          + Sum({$(xFILTER_SHEET05C)} 
                IF([Buy or Sell] = ''Buy'', [Payment Amount Qc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Qc])))
        ) / (
            Sum({$(xFILTER_SHEET05A)} [Nominal])
          + Sum({$(xFILTER_SHEET05B)} [Nominal])
          + Sum({$(xFILTER_SHEET05C)} [Nominal])
        ), 0))';

// 10. Cost per Unit (MYR) - from Profit Loss table
LET xSHEET05_CostUnitMyr = 
    'ALT(
        (
            Sum({$(xFILTER_SHEET05A)} [Cost Value Pc])
          + Sum({$(xFILTER_SHEET05B)} [Cost Value Pc])
          + Sum({$(xFILTER_SHEET05C)} [Cost Value Pc])
        ) / (
            Sum({$(xFILTER_SHEET05A)} [Nominal])
          + Sum({$(xFILTER_SHEET05B)} [Nominal])
          + Sum({$(xFILTER_SHEET05C)} [Nominal])
        ), 0)';

// 11. Cost per Unit (QC)
LET xSHEET05_CostUnitQc = 
    'ALT(
        (
            Sum({$(xFILTER_SHEET05A)} [Cost Value Qc])
          + Sum({$(xFILTER_SHEET05B)} [Cost Value Qc])
          + Sum({$(xFILTER_SHEET05C)} [Cost Value Qc])
        ) / (
            Sum({$(xFILTER_SHEET05A)} [Nominal])
          + Sum({$(xFILTER_SHEET05B)} [Nominal])
          + Sum({$(xFILTER_SHEET05C)} [Nominal])
        ), 0)';

// 12. Cost of Transaction (MYR) - total cost basis
LET xSHEET05_CostOfTxnMyr = 
    'Sum({$(xFILTER_SHEET05A)} [Cost Value Pc])
   + Sum({$(xFILTER_SHEET05B)} [Cost Value Pc])
   + Sum({$(xFILTER_SHEET05C)} [Cost Value Pc])';

// 13. Cost of Transaction (QC)
LET xSHEET05_CostOfTxnQc = 
    'Sum({$(xFILTER_SHEET05A)} [Cost Value Qc])
   + Sum({$(xFILTER_SHEET05B)} [Cost Value Qc])
   + Sum({$(xFILTER_SHEET05C)} [Cost Value Qc])';

EQUITY OWNERSHIP MEASURES:

// 14. Equity (%) - percentage of issued shares
LET xSHEET05_EquityPercent = 
    'ALT(
        Sum({$(xFILTER_SHEET05A)} [Nominal] / [Issued Volume])
      + Sum({$(xFILTER_SHEET05B)} [Nominal] / [Issued Volume])
      + Sum({$(xFILTER_SHEET05C)} [Nominal] / [Issued Volume])
    , 0)';

// 15. Ratio - for bonus issues (Nominal / Nominal Basis)
// Only applies to Bonus Issue In (BonIssIn) elementary trans code
LET xSHEET05_Ratio = 
    'Sum({$(xFILTER_SHEET05A), [Elementary Trans Code] = {"BonIssIn"}} 
          [Nominal] / [Nominal Basis])
   + Sum({$(xFILTER_SHEET05B), [Elementary Trans Code] = {"BonIssIn"}} 
          [Nominal] / [Nominal Basis])
   + Sum({$(xFILTER_SHEET05C), [Elementary Trans Code] = {"BonIssIn"}} 
          [Nominal] / [Nominal Basis])';

Example Ratio Calculation:

Bonus Issue: 1 for 5 (1 new share for every 5 held)
Nominal Basis: 500 shares
Nominal: 100 new shares
Ratio: 100 / 500 = 0.2 (or 1:5)

REALIZED PROFIT/LOSS MEASURES:

// 16. Realised Gain/Loss (MYR) - SELL only
// Formula: Proceeds - Cost
LET xSHEET05_RealisedGainLossMyr = 
    'Sum({$(xFILTER_SHEET05_SELLA)} [Current Value Pc] - [Cost Value Pc])
   + Sum({$(xFILTER_SHEET05_SELLB)} [Current Value Pc] - [Cost Value Pc])';

// 17. Realised Gain/Loss (QC)
LET xSHEET05_RealisedGainLossQc = 
    'Sum({$(xFILTER_SHEET05_SELLA)} [Current Value Qc] - [Cost Value Qc])
   + Sum({$(xFILTER_SHEET05_SELLB)} [Current Value Qc] - [Cost Value Qc])';

// 18. Realised Gain/Loss (%) - percentage return
LET xSHEET05_RealisedGLPercent = 
    'ALT(
        (
            Sum({$(xFILTER_SHEET05_SELLA)} [Current Value Qc] - [Cost Value Qc])
          + Sum({$(xFILTER_SHEET05_SELLB)} [Current Value Qc] - [Cost Value Qc])
        ) / (
            Sum({$(xFILTER_SHEET05_SELLA)} [Cost Value Qc])
          + Sum({$(xFILTER_SHEET05_SELLB)} [Cost Value Qc])
        ), 0)';

// 19. Realised Forex Gain/Loss (MYR)
// From Profit Loss table: Por Lcost Ccy Pc
LET xSHEET05_RealisedGLForexMyr = 
    'Sum({$(xFILTER_SHEET05_SELLA)} [Por Lcost Ccy Pc])
   + Sum({$(xFILTER_SHEET05_SELLB)} [Por Lcost Ccy Pc])';

Realized P&L Breakdown:

Total Realized P&L = Security P&L + Forex P&L

Security P&L: (Sell Price - Cost) in local currency
Forex P&L: FX rate change impact on foreign holdings

UNREALIZED PROFIT/LOSS MEASURES:

// 20. Reversal of Unrealised Gain/Loss (MYR) - SELL only
// Formula: (Yesterday Price - Yesterday Cost) × Nominal
// When selling, reverse the unrealized P&L that was carried
LET xSHEET05_ReversalOfUnrealisedGLMyr = 
    'Sum({$(xFILTER_SHEET05_SELLA)} 
          (FABS([Previous Value Myr]) - FABS([Previous Cost Value Pc])) * [Nominal])
   + Sum({$(xFILTER_SHEET05_SELLB)} 
          (FABS([Previous Value Myr]) - FABS([Previous Cost Value Pc])) * [Nominal])';

// 21. Reversal of Unrealised Gain/Loss (QC)
LET xSHEET05_ReversalOfUnrealisedGLQc = 
    'Sum({$(xFILTER_SHEET05_SELLA)} 
          (FABS([Previous Value Qc]) - FABS([Previous Cost Value Qc])) * [Nominal])
   + Sum({$(xFILTER_SHEET05_SELLB)} 
          (FABS([Previous Value Qc]) - FABS([Previous Cost Value Qc])) * [Nominal])';

// 22. Marked To Market Gain/Loss (MYR) - SELL only
// Formula: (Today Unit Price - Yesterday Price) × Nominal
LET xSHEET05_MarkedToMarketGLMyr = 
    'Sum({$(xFILTER_SHEET05_SELLA)} 
          (FABS(IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])) / [Nominal])
           - FABS([Previous Value Myr])) * [Nominal])
   + Sum({$(xFILTER_SHEET05_SELLB)} 
          (FABS(IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])) / [Nominal])
           - FABS([Previous Value Myr])) * [Nominal])';

// 23. Marked To Market Gain/Loss (QC)
LET xSHEET05_MarkedToMarketGLQc = 
    'Sum({$(xFILTER_SHEET05_SELLA)} 
          (FABS(IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])) / [Nominal])
           - FABS([Previous Value Qc])) * [Nominal])
   + Sum({$(xFILTER_SHEET05_SELLB)} 
          (FABS(IF([Buy or Sell] = ''Buy'', [Payment Amount Pc], 
                   IF([Buy or Sell] = ''Sell'', [Current Value Pc])) / [Nominal])
           - FABS([Previous Value Qc])) * [Nominal])';

Mark-to-Market Logic Explained:

Comment in code (lines 272-274):
// reversal unrealised gain/loss: (Yesterday Clean price - Yesterday Cost/unit) × nominal
// mark to market gain/loss: (Today unit price - Yesterday clean price) × nominal

When selling:
1. Reverse yesterday's unrealized P&L
2. Calculate today's price movement from yesterday
3. Sum both for total period P&L

Example:
Cost: $10
Yesterday Price: $12 → Unrealized: $2
Today Sell Price: $13
  - Reversal: ($12 - $10) × shares = Realized previous gain
  - MTM: ($13 - $12) × shares = Today's additional gain

TRANSACTION COST MEASURES:

// 24. Brokerage (MYR)
LET xSHEET05_BrokerageMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Portfolio Currency])';

// 25. Brokerage (QC)
LET xSHEET05_BrokerageQc = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"BROKER","COMMISSION"}>} 
          [Amount Quotation Currency])';

// 26. Brokerage SST (MYR)
LET xSHEET05_BrokerageSstMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Portfolio Currency])';

// 27. Brokerage SST (QC)
LET xSHEET05_BrokerageSstQc = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"BROKER_SST"}>} 
          [Amount Quotation Currency])';

// 28. Scan Fee (MYR)
LET xSHEET05_ScanFeeMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"MY_CLRG"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"MY_CLRG"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"MY_CLRG"}>} 
          [Amount Portfolio Currency])';

// 29. Scan Fee GST (MYR)
LET xSHEET05_ScanFeeGstMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] = {"SCAN_SST"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] = {"SCAN_SST"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] = {"SCAN_SST"}>} 
          [Amount Portfolio Currency])';

// 30. Stamp Duty (MYR)
LET xSHEET05_StampDutyMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Class] = {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Class] = {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Class] = {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])';

// 31. Transaction Fees (MYR) - all except brokerage
LET xSHEET05_FeesMyr = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Portfolio Currency])';

// 32. Transaction Fees (QC)
LET xSHEET05_FeesQc = 
    'Sum({$(xFILTER_SHEET05A), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05B), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Quotation Currency])
   + Sum({$(xFILTER_SHEET05C), [Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, [Cost Class] -= {"STAMP_DUTY"}>} 
          [Amount Quotation Currency])';

📦 Phase 5: Report Outputs

RPT_000003 - Trade Transaction – Equity

Used In: Sheet 02, Sheet 05

Purpose: Core trading transaction report

Tables Used:

Transactions (main)
Transactions Costs
S2AuxNprintingPorGroup (Sheet 02)
S5AuxNprinting003Level (Sheet 05)

Key Dimensions:

Security Name
Portfolio Group
Trade Date
Sheet5Rpt003Level (in Sheet 05 for classification)

Key Measures (from both sheets):

Quantity (xSHEET02_Quantity / xSHEET05_NoOfShares)
Unit Price
Gross Amount
All cost breakdowns
Net Amount

Difference between Sheet 02 and Sheet 05:

Sheet 02: Market trades only (IsS2 filter)
Sheet 05: Includes accounting transactions and corporate actions

RPT_000030 - Details of Investment

Used In: Sheet 02, Sheet 05

Purpose: Investment position details with cost analysis

Tables Used:

Transactions (main)
Transactions Profit Loss (Cost Value fields)
Transactions Costs
S2AuxNprintingPorGroup / S5AuxNprintingPorGroup

Key Measures (Sheet 05 specific):

Average Cost per Share (xSHEET05_AverageCostShareMyr/Qc)
Cost per Unit (xSHEET05_CostUnitMyr/Qc)
Cost of Transaction
Equity Percentage

Business Use: Portfolio managers analyze entry costs

RPT_000025 - Details of Investment Activities

Used In: Sheet 05 only

Purpose: Comprehensive investment activity analysis with full P&L

Tables Used:

Transactions (main)
Transactions Profit Loss (full P&L fields)
Transactions Costs
Previous Day calculations (Previous Value fields)
S5AuxNprintingPorGroup

Key Measures (Sheet 05 exclusive):

Realized Gain/Loss (xSHEET05_RealisedGainLossMyr/Qc)
Realized G/L Percentage
Realized Forex G/L
Reversal of Unrealized G/L (xSHEET05_ReversalOfUnrealisedGLMyr/Qc)
Marked To Market G/L (xSHEET05_MarkedToMarketGLMyr/Qc)

Complex Calculations: This report showcases the most sophisticated calculations:

Cost basis tracking from Transactions Profit Loss
Previous day values from BalBook transactions
Realized P&L when selling
Reversal of unrealized when closing positions
Mark-to-market price changes

Business Use: Performance attribution, P&L reporting, portfolio review

RPT_000047 - Status of Shares Sold

Used In: Sheet 02

Purpose: Monitor sold equity positions and settlement

Filter: IsS2 + Buy or Sell = 'Sell'

Key Measures:

Quantity sold
Gross proceeds
Net proceeds (after costs)

RPT_000048 - Status of Purchase Shares

Used In: Sheet 02

Purpose: Monitor purchased equity positions and settlement

Filter: IsS2 + Buy or Sell = 'Buy'

Key Measures:

Quantity purchased
Gross cost
Net cost (including all fees)

RPT_000060 - Daily Summary of Sale Contract Note

Used In: Sheet 02

Purpose: Daily aggregated sale contract notes by portfolio

Grouping:

Portfolio Group
Trade Date
Settlement Date (T+2)

Variables Used:

// From Report_Variables.txt
SET vRPT_000060_Fund = '=UPPER([Portfolio Group Name])';
SET vRPT_000060_TransactionDate = '=MAX(DATE([Trade Date],''DD/MM/YYYY''))';
SET vRPT_000060_TransferDate = '=MAX(DATE([Trade TD+1BD],''DD/MM/YYYY''))';
SET vRPT_000060_SettlementDate = '=MAX(DATE([Trade TD+2BD],''DD/MM/YYYY''))';

Key Measures:

Total quantity sold
Total gross proceeds
Total costs
Total net proceeds

RPT_000061 - Daily Summary of Purchase Contract Note

Used In: Sheet 02

Purpose: Daily aggregated purchase contract notes by portfolio

Same structure as RPT_000060 but for purchases

RPT_000197 - Equity Contract Note

Used In: Sheet 02

Purpose: Detailed contract note with full cost breakdown

All Sheet 02 measures:

Quantity
Gross Amount
Brokerage + Brokerage SST
Stamp Duty
Scan Fees + Scan Fee GST
Transaction Fees
Net Amount

Business Use: Regulatory compliance, audit trail, client reporting

RPT_000206 - Daily Status of Shares Sold

Used In: Sheet 02

Purpose: Daily monitoring of sold share positions

Grouping: Daily by security

RPT_000207 - Daily Status of Purchased Shares

Used In: Sheet 02

Purpose: Daily monitoring of purchased share positions

Grouping: Daily by security

RPT_000209 - Share Purchase Order

Used In: Sheet 03

Purpose: Track purchase orders with charges

Filter: IsS3 filter (includes corporate actions with price > 0)

Key Measures:

No. of Shares (xSHEET03_NoOfShares)
Charges (xSHEET03_Charges)
Charges SST (xSHEET03_ChargesSst)
Net Amount

RPT_000210 - Share Sold Order

Used In: Sheet 03

Purpose: Track sale orders with charges

Same measures as RPT_000209 but for sells

RPT_000211 - Purchase and Sales of Shares

Used In: Sheet 04

Purpose: High-level buy vs sell summary

Filter: Only AllocBuyExt and AllocSellExt

Layout:

Metric          | Buy Side           | Sell Side
----------------+--------------------+------------------
Amount (MYR)    | xSHEET04_BuyRm    | xSHEET04_SellRm
No. of Trades   | xSHEET04_BuyTrades| xSHEET04_SellTrades
No. of Shares   | xSHEET04_BuyShares| xSHEET04_SellShares

Business Use: Daily trading summary, volume tracking

📊 Complete Field Mapping Summary

Source → Transactions → Reports Field Flow:

Source Field	Intermediate	Final Report Field	Calculation
`Nominal` (QVD)	`Nominal`	Quantity	Sum([Nominal])
`Current Value Pc` (QVD)	`Current Value Pc`	Gross Amount (MYR)	Sum([Current Value Pc])
`Payment Amount Pc` (QVD)	`Payment Amount Pc`	Net Amount (MYR)	Sum([Payment Amount Pc])
`COST_TYPE_KEY` (Costs QVD)	`Cost Type`	Cost breakdown	Via mapping + filtering
`Cost Value Pc` (P&L QVD)	`Cost Value Pc`	Cost Basis	From Profit Loss table
`Por Lcost *` (P&L QVD)	`Por Lcost` fields	Realized P&L	From Profit Loss table
`Current Value` (BalBook)	`Previous Value`	Yesterday Price	Complex Previous Day logic
`Balance Cost Value` (BalBook)	`Previous Cost Value`	Yesterday Cost	Complex Previous Day logic

🔍 Critical Transformations Recap

1. IsS2 Flag (Sheet 02 filter):

TRUE when: (Equity instruments OR ETF/REIT fund certificates) 
           AND (Buy or Sell transaction)

2. IsS3 Flag (Sheet 03 filter):

TRUE when: (Equity + Buy/Sell codes) 
           OR (Equity + Corporate Action + Price > 0)

3. Previous Day Values:

For each transaction:
  1. Find all BalBook transactions for same HOLDING_KEY
  2. Get MAX(BalBook date) where BalBook date < Transaction date
  3. Extract values from that BalBook transaction
  4. Divide by Nominal to get per-share values
  5. JOIN to Transactions table

4. Realized P&L (Sell only):

Realized Gain/Loss = Sell Proceeds - Cost Basis

Components:
- Security P&L: (Sell Price - Cost Price) in local currency
- Forex P&L: FX rate change impact

Total = Security P&L + Forex P&L

5. Reversal of Unrealized P&L (Sell only):

When selling position with unrealized gain/loss:
  Reversal = (Yesterday Price - Yesterday Cost) × Shares Sold

This "realizes" the unrealized P&L carried from previous days

6. Mark-to-Market P&L (Sell only):

MTM = (Today Sell Price - Yesterday Price) × Shares

This captures price movement on the day of sale

7. Cost Breakdown Formula:

Net Amount = Gross Amount ± Total Costs

Total Costs = Brokerage + Brokerage SST + Stamp Duty + Scan Fee + Scan Fee GST + Other Fees

For Buy:  Net = Gross + Costs (outflow increases)
For Sell: Net = Gross - Costs (inflow decreases)

📋 Data Quality Checks

Validation Rules:

IsS2/IsS3 Flag Calculation:

// Should not be null for equity instruments
COUNT(*) WHERE [Instrument Type] IN ('Equity', ...) AND IsS2 IS NULL

Previous Day Values:

// Sell transactions should have Previous Values
COUNT(*) WHERE [Buy or Sell] = 'Sell' 
         AND [Previous Value Qc] IS NULL
         AND NOT MATCH([Bus Trans Code], 'BalBook')

Cost Value Consistency:

// Cost Value should exist for transactions in P&L table
COUNT(*) FROM Transactions 
LEFT JOIN Transactions Profit Loss
WHERE [Cost Value Pc] IS NULL AND [Transaction Status] = 'Settled'

Buy/Sell Classification:

// Should be classified for equity trades
COUNT(*) WHERE IsS2 = TRUE AND [Buy or Sell] IS NULL

🎯 Key Performance Indicators (KPIs) by Report

RPT_000003 - Trade Transaction:

KPI	Formula	Business Meaning
Total Value Traded	Sum(Gross Amount)	Trading volume
Average Trade Size	Sum(Gross) / Count(Trades)	Trade size analysis
Buy/Sell Ratio	Sum(Buy Amount) / Sum(Sell Amount)	Market activity balance

RPT_000025 - Investment Activities:

KPI	Formula	Business Meaning
Total Realized P&L	Sum(Realized G/L)	Actual profit/loss
Average Return (%)	Avg(Realized G/L %)	Performance metric
Forex Impact	Sum(Forex G/L) / Sum(Total G/L)	Currency exposure
MTM Movement	Sum(MTM G/L)	Daily price impact

RPT_000211 - Purchase and Sales:

KPI	Formula	Business Meaning
Net Buy/Sell	Buy Amount - Sell Amount	Portfolio flow direction
Trade Count	Buy Trades + Sell Trades	Activity level
Average Buy Price	Buy Amount / Buy Shares	Entry price tracking
Average Sell Price	Sell Amount / Sell Shares	Exit price tracking

🔧 Troubleshooting Guide

Issue 1: Missing Previous Values

Symptom: Previous Value Myr is null for sell transactions

Possible Causes:

No BalBook transaction before the sale date
HOLDING_KEY mismatch
Transaction happened before any balance book

Solution:

// Check if BalBook exists for this holding
SELECT * FROM Transactions
WHERE HOLDING_KEY = [problematic holding]
  AND MATCH([Bus Trans Code], 'BalBook')
  AND [Trade Date] < [transaction date]

Issue 2: IsS2/IsS3 Flag Not Working

Symptom: Transactions not appearing in Sheet 02 or Sheet 03 reports

Possible Causes:

Instrument Type not in expected list
Buy or Sell not calculated correctly
Security Type mismatch for ETFs

Solution:

// Verify flag calculation
SELECT [Instrument Type], [Security Type], [Buy or Sell], IsS2, IsS3
FROM Transactions
WHERE [Security Name] = [problematic security]

Issue 3: Cost Value Mismatch

Symptom: P&L calculations don't match expected

Possible Causes:

Multiple accounting frameworks
Cost Value not booked yet (Finally Booked = 0)
Transaction not in Profit Loss table

Solution:

// Check P&L table
SELECT COUNT(*) 
FROM Transactions T
LEFT JOIN Transactions Profit Loss TPL
  ON T.[Transaction Ik] = TPL.[Transaction Ik]
WHERE TPL.[Transaction Ik] IS NULL
  AND T.[Transaction Status] = 'Settled'

Issue 4: Duplicate Cost Records

Symptom: Costs appearing multiple times

Possible Causes:

Multiple cost records per transaction
Not using DISTINCT in Transactions Costs load

Solution:

// Verify cost record count
SELECT [Transaction Ik], COUNT(*)
FROM Transactions Costs
GROUP BY [Transaction Ik]
HAVING COUNT(*) > Expected

📚 Business Rules Reference

Transaction Type Classification:

Bus Trans Code	Category	IsS2	IsS3	Sheet 05
`Buy`	External Trades	✅	✅	✅
`Sell`	External Trades	✅	✅	✅
`AllocBuyExt`	External Trades	✅	✅	✅
`AllocSellExt`	External Trades	✅	✅	✅
`CABuy`	Corporate Actions	❌	❌	✅
`CASell`	Corporate Actions	❌	❌	✅
`BalBook`	Accounting	❌	❌	❌
`Valuation`	Accounting	❌	❌	✅
`Initialisation`	Accounting	❌	❌	✅

Settlement Date Calculations:

// T+1 Business Day
Trade Date TD+1BD = IF(WEEKDAY >= 4,
                       Trade Date + 3 - (WEEKDAY - 4),
                       Trade Date + 1)

// T+2 Business Days
Trade Date TD+2BD = IF(WEEKDAY >= 5,
                       Trade Date + 3 - (WEEKDAY - 5),
                       IF(WEEKDAY >= 3,
                          Trade Date + 4,
                          Trade Date + 2))

Example:

Trade on Wednesday (WEEKDAY = 2):
  T+1 = Thursday (Trade Date + 2)
  T+2 = Friday (Trade Date + 2)

Trade on Friday (WEEKDAY = 4):
  T+1 = Monday (Trade Date + 3 - 0 = Trade Date + 3)
  T+2 = Monday (Trade Date + 3 - -1 = Trade Date + 4)

✅ Validation Checklist

Before using equity reports, verify:

IsS2 and IsS3 flags calculated correctly
Buy or Sell classification working
Previous Day values loaded for all holdings
Transactions Profit Loss table joined successfully
Transactions Costs table joined successfully
Cost Type mappings complete
BalBook transactions exist for all holdings
Accounting Framework filtering working
Finally Booked = 1 for all P&L records
Settlement date calculations correct (T+1, T+2)
Sheet5Rpt003Level composite key created
All 3 filter combinations (A+B+C) working in Sheet 05

📋 Table of Contents

📊 Overview: Equity & Trading Reports Category

Reports Covered (11 Reports):

🔄 Complete Data Flow Diagram

📦 Phase 1: Raw Data Sources (QVD Files)

Source 1: Transactions.qvd

Source 2: Transactions Profit Loss.qvd

Source 3: Transactions Costs.qvd

📦 Phase 2: Calculated Flags in Transactions Table

Flag 1: IsS2 (Sheet 02 Filter)

Flag 2: IsS3 (Sheet 03 Filter)

Flag 3: Sheet5Rpt003Level (Sheet 05 Grouping)

Flag 4: Buy or Sell Classification

📦 Phase 3: Previous Day Calculations (Complex Logic)

Purpose:

Step-by-Step Process:

📦 Phase 4: Sheet-Specific Transformations

Sheet 02 - Equity Contract Notes (9 Reports)

Auxiliary Tables:

Sheet 02 - Filter Definition:

Sheet 02 - Measure Variables (11 Measures):

Sheet 02 Reports Summary:

Sheet 03 - Share Orders (2 Reports)

Auxiliary Tables:

Sheet 03 - Filter Definition:

Sheet 03 - Measure Variables (3 Measures):

Sheet 03 Reports:

Sheet 04 - Purchase and Sales Summary (1 Report)

No Auxiliary Tables

Sheet 04 - Filter Definition:

Sheet 04 - Measure Variables (6 Measures):

Sheet 04 Report:

Sheet 05 - Transaction Details & P&L (3 Reports)

Most Complex Sheet - 375 Lines of Code

Auxiliary Tables:

Sheet 05 - Filter Definitions (3 Separate Filters):

Sheet 05 - Measure Variables (28 Measures)

BASIC MEASURES:

COST BASIS MEASURES:

EQUITY OWNERSHIP MEASURES:

REALIZED PROFIT/LOSS MEASURES:

UNREALIZED PROFIT/LOSS MEASURES:

TRANSACTION COST MEASURES:

📦 Phase 5: Report Outputs

RPT_000003 - Trade Transaction – Equity

RPT_000030 - Details of Investment

RPT_000025 - Details of Investment Activities

RPT_000047 - Status of Shares Sold

RPT_000048 - Status of Purchase Shares

RPT_000060 - Daily Summary of Sale Contract Note

RPT_000061 - Daily Summary of Purchase Contract Note

RPT_000197 - Equity Contract Note

RPT_000206 - Daily Status of Shares Sold

RPT_000207 - Daily Status of Purchased Shares

RPT_000209 - Share Purchase Order

RPT_000210 - Share Sold Order

RPT_000211 - Purchase and Sales of Shares

📊 Complete Field Mapping Summary

Source → Transactions → Reports Field Flow:

🔍 Critical Transformations Recap

1. IsS2 Flag (Sheet 02 filter):

2. IsS3 Flag (Sheet 03 filter):

3. Previous Day Values:

4. Realized P&L (Sell only):

5. Reversal of Unrealized P&L (Sell only):

6. Mark-to-Market P&L (Sell only):

7. Cost Breakdown Formula:

📋 Data Quality Checks

Validation Rules:

🎯 Key Performance Indicators (KPIs) by Report

RPT_000003 - Trade Transaction:

RPT_000025 - Investment Activities:

RPT_000211 - Purchase and Sales:

🔧 Troubleshooting Guide

Issue 1: Missing Previous Values

Issue 2: IsS2/IsS3 Flag Not Working

Issue 3: Cost Value Mismatch

Issue 4: Duplicate Cost Records

📚 Business Rules Reference