QlikSense ETL Analysis

📊 Overview: Remaining Reports Category

This section provides complete data lineage for the remaining reports covering interest earned, money market activities, transaction history, cost adjustments, and domestic bonds.

Reports Covered (7 Reports):

Sheet 07 Report (1 report):

RPT_000049 - Interest Earned

Sheet 06 Report (1 report): 2. RPT_000080 - MYR Money Market Activities Daily Average Placement

Sheet 09 Report (1 report): 3. RPT_000198 - Summary Transaction Report

Sheet 11 Report (1 report): 4. RPT_000066 - Daily Transaction Value

Sheet 12 Report (1 report): 5. RPT_000178 - Transaction History Trading

Sheet 13 Report (no RPT number specified): 6. Cost Adjustments Report

Sheet 14 Report (no RPT number specified): 7. Domestic Bonds Report

🔄 Complete Data Flow Diagram

🔗 Interactive Diagram: View Complete Data Lineage Flow in Figma →

┌─────────────────────────────────────────────────────────────────────┐
│                     RAW DATA SOURCES (QVD)                           │
└─────────────────────────────────────────────────────────────────────┘
                                    ↓
        ┌───────────────────────────┼────────────────────────────┐
        ↓                           ↓                            ↓
┌──────────────┐       ┌────────────────────┐       ┌──────────────────┐
│Transactions  │       │ Portfolio          │       │ Quotation        │
│   .qvd       │       │ Calculation        │       │ Currency.qvd     │
│              │       │ Results.qvd        │       │                  │
│ Interest/    │       │ (PFC_Holdings)     │       │ (for Sheet 06)   │
│ Capital      │       │                    │       │                  │
│ Event fields │       │ Deposit holdings   │       │                  │
└──────┬───────┘       └─────────┬──────────┘       └────────┬─────────┘
       │                         │                            │
       ↓                         ↓                            ↓
┌─────────────────────────────────────────────────────────────────────┐
│                      MAPPINGS PHASE                                  │
│  - mInsType, mPorCalc, mBusTransCode, mPorGrp                       │
└─────────────────────────────┬───────────────────────────────────────┘
                              ↓
┌─────────────────────────────────────────────────────────────────────┐
│           CORE TRANSACTIONS TABLE (Transactions.txt)                 │
│  - Interest Dividend Capital Event Pc/Qc/Myr                        │
│  - Elementary Trans Code                                             │
│  - Trade Date Month Name Short                                       │
│  - Instrument Type                                                   │
│  - Buy or Sell or Dividend                                           │
│  - Inp Unreal PL Cost Ccy Pc / Inp Unreal PL Cost Sec Pc           │
│  - Input Int Appr Pc                                                 │
│  - Impairment Pc, Write Off Pc                                       │
└─────────────────────────────┬───────────────────────────────────────┘
                              ↓
        ┌─────────────────────┼─────────────────────┬──────────────┬──────────────┐
        ↓                     ↓                     ↓              ↓              ↓
┌──────────────┐   ┌──────────────┐   ┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│  Sheet_07    │   │  Sheet_06    │   │  Sheet_09    │   │  Sheet_11    │   │  Sheet_12    │
│  Interest    │   │  Money       │   │  Summary     │   │  Daily       │   │  Transaction │
│  Earned      │   │  Market      │   │  Transaction │   │  Transaction │   │  History     │
│              │   │              │   │              │   │  Value       │   │              │
│ Filter:      │   │ Filter:      │   │ Filter:      │   │ Filter:      │   │ Filter:      │
│ Bond, Index  │   │ Deposit +    │   │ Buy/Sell/    │   │ Equity +     │   │ Equity +     │
│ Bond,Deposit │   │ *Placement*  │   │ Dividend     │   │ ETF/REIT     │   │ ETF/REIT     │
│              │   │              │   │              │   │ Buy/Sell     │   │ Buy/Sell     │
│ Monthly      │   │ Uses:        │   │              │   │              │   │              │
│ Aggregation  │   │ PFC_Holdings │   │ 3 Measures   │   │ 5 Measures   │   │ 2 Tables     │
│              │   │              │   │              │   │              │   │ 13 Measures  │
│ 26 Measures  │   │ 2 Measures   │   │              │   │              │   │              │
│ (12 months + │   │              │   │              │   │              │   │              │
│ 2 dynamic)   │   │              │   │              │   │              │   │              │
└──────┬───────┘   └──────┬───────┘   └──────┬───────┘   └──────┬───────┘   └──────┬───────┘
       │                  │                  │                  │                  │
       ↓                  ↓                  ↓                  ↓                  ↓
┌──────────────┐   ┌──────────────┐   
│  Sheet_13    │   │  Sheet_14    │   
│  Cost        │   │  Domestic    │   
│  Adjustments │   │  Bonds       │   
│              │   │              │   
│ Filter:      │   │ Filter:      │   
│ Elementary   │   │ MYR Bond,    │   
│ Trans Code:  │   │ ABS, Index   │   
│ - General    │   │ Bond         │   
│   Cost       │   │              │   
│ - EOP adj.   │   │ 2 Measures   │   
│ - ScripDiv   │   │              │   
│ - CoupCap    │   │              │   
│ - Impairment │   │              │   
│ - Write-off  │   │              │   
│              │   │              │   
│ 1 Complex    │   │              │   
│ Measure      │   │              │   
└──────┬───────┘   └──────┬───────┘   
       │                  │
       └──────────────────┼──────────────────────────────────┐
                          ↓                                  ↓
┌─────────────────────────────────────────────────────────────────────┐
│                   FINAL REPORTS (NPrinting)                          │
│  RPT_000049 | RPT_000080 | RPT_000198 | RPT_000066 | RPT_000178    │
│  Cost Adjustments | Domestic Bonds                                  │
└─────────────────────────────────────────────────────────────────────┘

📦 Phase 1: Raw Data Sources (QVD Files)

Source 1: Transactions.qvd (Primary Source)

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

Key Fields Used for These Reports:

Field Name	Data Type	Description	Used In
`Interest Dividend Capital Event Pc`	Number	Interest/Dividend in Portfolio Currency	Sheet 07, 10
`Interest Dividend Capital Event Qc`	Number	Interest/Dividend in Quotation Currency	Sheet 07, 10
`Interest Dividend Capital Event Myr`	Number	Interest/Dividend in MYR	Sheet 07
`Trade Date Month`	Text	Month name (Jan, Feb, etc.)	Sheet 07
`Trade Date Month Name Short`	Text	3-letter month (JAN, FEB, etc.)	Sheet 07
`Elementary Trans Code`	Text	Elementary transaction code	Sheet 13
`ELEMENTARY_TRANS_KEY`	Key	Elementary transaction key	Mappings
`Inp Unreal PL Cost Ccy Pc`	Number	Input Unrealized P&L Cost Currency	Sheet 13
`Inp Unreal PL Cost Sec Pc`	Number	Input Unrealized P&L Cost Security	Sheet 13
`Input Int Appr Pc`	Number	Input Interest Appreciation	Sheet 13
`Impairment Pc`	Number	Impairment amount in PC	Sheet 13
`Write Off Pc`	Number	Write-off amount in PC	Sheet 13
`Payment Amount Pc`	Number	Net payment in Portfolio Currency	All sheets
`Payment Amount Qc`	Number	Net payment in Quotation Currency	All sheets
`Current Value Pc`	Number	Current value in Portfolio Currency	Sheet 11, 12, 14
`Current Value Qc`	Number	Current value in Quotation Currency	Sheet 11, 12
`Nominal`	Number	Number of shares/units	All sheets
`Bus Trans No`	Text	Transaction number	Sheet 09
`Bus Trans Code`	Text	Transaction code	All sheets
`Instrument Type`	Text	Instrument classification	All sheets
`Quotation Currency`	Text	Currency of security	Sheet 14

Filters Applied:

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

Source 2: Portfolio Calculation Results.qvd (for Sheet 06)

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Portfolio Calculation Results.qvd]

Key Fields:

Field Name	Description	Purpose
`SECURITY_KEY`	Security identifier	Join key
`QUOTATION_CURRENCY_KEY`	Currency key	Currency info
`PORTFOLIO_KEY`	Portfolio identifier	Portfolio grouping
`PORTFOLIO_CALCULATION_KEY`	PFC calculation key	Filter specific calc type
`Valuation Date Full`	Valuation date	Date dimension
`Calculation Date Full`	Calculation date	Latest calc filter
`Balance Nominal Or Number`	Balance quantity	Deposit holdings
`Fx Rate Qc Pc`	FX rate QC to PC	Currency conversion
`Dirty Value Pc`	Dirty value in PC	Market value
`Dirty Value Qc`	Dirty value in QC	Market value
`Calculation Result Type`	Result type	Filter = 1

Filters Applied:

WHERE APPLYMAP('mPorCalc', PORTFOLIO_CALCULATION_KEY, NULL()) = 'DW_VAL_IFRS_PL'
  AND "Calculation Result Type" = 1
  AND ApplyMap('mInsType', SECURITY_KEY) = 'Deposit'
  AND MATCH(PORTFOLIO_CALCULATION_KEY & '_' & NUM([Valuation Date Full]) & '-' & NUM([Calculation Date Full]), $(vPFCDList))
  AND [Valuation Date Full] < (Today(0) + 1)

Note: This creates the PFC_Holdings table specifically for money market (deposit) positions.

Source 3: Quotation Currency.qvd (for Sheet 06)

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Quotation Currency.qvd]

Key Fields:

Field Name	Description
`QUOTATION_CURRENCY_KEY`	Currency key (join key)
`Quotation Currency`	Currency code (USD, MYR, etc.)

Join Type: INNER JOIN with PFC_Holdings

📦 Phase 2: Auxiliary Table - Monthly Aggregations

Created In: Monthly_Aggregations.txt

Purpose: Create a dimension table for monthly aggregation reporting used in Sheet 07

LEFT KEEP ([Transactions]) 
LOAD * INLINE [
Month Name w Total
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
TOTAL
];

Result: A table with 13 rows (12 months + TOTAL)

Join Type: LEFT KEEP - Only keeps month names that have transactions

Used By: Sheet 07 (Interest Earned) for monthly cross-tab reporting

Business Logic:

This creates a dimension that allows pivoting by month
Includes special "TOTAL" row for annual totals
Matches against [Trade Date Month Name Short] field

📦 Phase 3: Sheet-Specific Transformations

Sheet 07 - Interest Earned (RPT_000049)

Purpose:

Monthly breakdown of interest income from bonds and deposits

No Auxiliary Tables

Filter Definitions:

// Set analysis filter
LET xFILTER_SHEET07 = '<[Instrument Type] *= {"Bond","Index bond","Deposit"}>';

// IF statement filter
LET xFILTER_SHEET07_MATCH = 'MATCH([Instrument Type],''Bond'',''Index bond'',''Deposit'')';

Business Logic:

Include only:
- Bond
- Index bond (inflation-linked bonds)
- Deposit (money market instruments)

Exclude:
- Equity
- Fund certificates
- All other instrument types

Measure Variables (26 Measures):

DYNAMIC MEASURES (2 measures - for pivot tables):

These measures dynamically show interest for the correct month or total:

// 1. Interest Earned (QC) - Dynamic by Month
LET xSHEET07_InterestEarnedQc = 
    'IF(MATCH([Instrument Type],''Bond'',''Index bond'',''Deposit''),
        IF(Sum([Interest Dividend Capital Event Qc]) <> 0,
            IF([Month Name w Total] = ''TOTAL'',
                Sum([Interest Dividend Capital Event Qc]),
                IF([Month Name w Total] = [Trade Date Month Name Short],
                    Sum({<[Month Name w Total] = [Trade Date Month Name Short]>} 
                        [Interest Dividend Capital Event Qc]),
                    ''-'')
            ),
        NULL()),
    NULL())';

// 2. Interest Earned (MYR) - Dynamic by Month
LET xSHEET07_InterestEarnedMyr = 
    'IF(MATCH([Instrument Type],''Bond'',''Index bond'',''Deposit''),
        IF(Sum([Interest Dividend Capital Event Qc]) <> 0,
            IF([Month Name w Total] = ''TOTAL'',
                Sum([Interest Dividend Capital Event Pc]),
                IF([Month Name w Total] = [Trade Date Month Name Short],
                    Sum({<[Month Name w Total] = [Trade Date Month Name Short]>} 
                        [Interest Dividend Capital Event Pc]),
                    ''-'')
            ),
        NULL()),
    NULL())';

Complex Logic Explained:

Step 1: Check if instrument is Bond/Index bond/Deposit
  ↓
Step 2: Check if there is any interest for this instrument
  ↓
Step 3: Check dimension value:
  IF dimension = 'TOTAL':
    → Show total interest across all months
  
  IF dimension matches the trade month:
    → Show interest for that specific month
    → Uses set analysis to filter to matching month
  
  ELSE:
    → Show '-' (dash) for non-matching months

Example Output in Pivot Table:

Security    | JAN   | FEB   | MAR   | ... | DEC   | TOTAL
------------|-------|-------|-------|-----|-------|-------
Bond A      | 1,000 | -     | 1,000 | ... | -     | 2,000
Bond B      | -     | 500   | -     | ... | 500   | 1,000
Deposit C   | 200   | 200   | 200   | ... | 200   | 2,400

Note: The dash '-' appears when the [Month Name w Total] dimension doesn't match the transaction's month

MONTHLY STATIC MEASURES (24 measures - 12 months × 2 currencies):

Each month has both QC and PC (MYR) measures:

// JANUARY
LET xSHEET07_JAN_QC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Jan"}>} 
          [Interest Dividend Capital Event Qc])';

LET xSHEET07_JAN_PC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Jan"}>} 
          [Interest Dividend Capital Event Pc])';

// FEBRUARY
LET xSHEET07_FEB_QC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Feb"}>} 
          [Interest Dividend Capital Event Qc])';

LET xSHEET07_FEB_PC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Feb"}>} 
          [Interest Dividend Capital Event Pc])';

// ... continues for MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC ...

// DECEMBER
LET xSHEET07_DEC_QC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Dec"}>} 
          [Interest Dividend Capital Event Qc])';

LET xSHEET07_DEC_PC = 
    'Sum({$(xFILTER_SHEET07)*<[Trade Date Month]={"Dec"}>} 
          [Interest Dividend Capital Event Pc])';

// TOTAL (Annual)
LET xSHEET07_Total_QC = 
    'Sum({$(xFILTER_SHEET07)} [Interest Dividend Capital Event Qc])';

LET xSHEET07_Total_PC = 
    'Sum({$(xFILTER_SHEET07)} [Interest Dividend Capital Event Pc])';

Business Logic:

Static measures filter by [Trade Date Month] field
Each month gets its own dedicated measure
Total measures sum all months (no month filter)

Report Layout Options:

Option 1: Pivot Table (uses dynamic measures)

Dimension: [Month Name w Total] (JAN, FEB, ..., DEC, TOTAL)
Dimension: Security Name
Measure: xSHEET07_InterestEarnedQc or xSHEET07_InterestEarnedMyr

Option 2: Straight Table (uses static measures)

Dimension: Security Name
Measures: 
  xSHEET07_JAN_QC, xSHEET07_FEB_QC, ..., xSHEET07_DEC_QC, xSHEET07_Total_QC
  (12 month columns + 1 total column)

RPT_000049 - Interest Earned Report:

Purpose: Track monthly interest income from bonds and deposits

Tables Used:

Transactions (main)
Monthly_Aggregations (dimension table)

Key Dimensions:

Security Name
Instrument Type
Portfolio Group
Month Name w Total (for pivot)
Trade Date Year

Key Measures:

All 26 measures (2 dynamic + 24 static)
Typically shows either QC or PC, not both

Business Use Cases:

Monthly income tracking
Year-over-year comparison
Accrual accounting verification
Portfolio income analysis

Typical Grouping:

By Security:
  → Shows each bond/deposit with monthly breakdown

By Portfolio:
  → Shows total interest per portfolio per month

By Instrument Type:
  → Separates Bond vs Deposit income

Sheet 06 - Money Market Placement Activity (RPT_000080)

Purpose:

Track MYR money market (deposit) placements and daily average balances

No Auxiliary Tables

Filter Definitions:

// Instrument filter
LET xFILTER_SHEET06 = '<[Instrument Type] *= {"Deposit"}>';

// Hide blank instruments
LET xFILTER_SHEET06_HIDE = '<[Instrument] -= {" "}>';

Business Logic:

Include only: Deposit instruments (money market)
Exclude: Empty/blank instrument codes

Measure Variables (2 Measures):

// 1. Principal Amount - from Transactions table
// Only transactions with *Placement* in the code
LET xSHEET06_PrincipalAmt = 
    'Sum({$(xFILTER_SHEET06)*$(xFILTER_SHEET06_HIDE)
          *<[Bus Trans Code]={"*Placement*"}>} 
          [Payment Amount Pc])';

// 2. Principal Amount Holdings - from PFC_Holdings table
// Balance from Portfolio Calculation Results
LET xSHEET06_PrincipalAmtH = 
    'Sum({$(xFILTER_SHEET06)*$(xFILTER_SHEET06_HIDE)
          *<[Valuation Date Full]=P([Trade Date]), 
            [Portfolio Group PFC]=P([Portfolio Group])>} 
          [Balance Nominal Or Number])';

Key Difference Between Measures:

Measure	Source	Purpose	Usage
`xSHEET06_PrincipalAmt`	Transactions table	New placements	Cash flow tracking
`xSHEET06_PrincipalAmtH`	PFC_Holdings table	Ending balances	Position tracking

Set Analysis Explained:

Measure 1 (PrincipalAmt):

Filter: Instrument Type = Deposit
AND: Instrument is not blank
AND: Bus Trans Code contains "Placement"

Example codes: 
- "Placement"
- "PlacementIn"
- "PlacementOut"

Sum: Payment Amount Pc (net amount placed/withdrawn)

Measure 2 (PrincipalAmtH):

Filter: Instrument Type = Deposit
AND: Instrument is not blank
AND: Valuation Date Full = P([Trade Date])
  → P() means "Possible values in current selection"
  → Matches PFC valuation date to selected trade dates
AND: Portfolio Group PFC = P([Portfolio Group])
  → Matches PFC portfolio to selected portfolios

Sum: Balance Nominal Or Number (holdings balance)

PFC_Holdings Table (from PFC_at_Lodgement.txt):

[PFC_Holdings]:
LOAD
    SECURITY_KEY, 
    QUOTATION_CURRENCY_KEY,
    [Valuation Date Full],
    APPLYMAP('mPorGrp', PORTFOLIO_KEY, '-') AS [Portfolio Group PFC],
    [Balance Nominal Or Number] * [Fx Rate Qc Pc] AS [Balance Nominal Or Number],
    [Dirty Value Pc],
    [Dirty Value Qc]
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Portfolio Calculation Results.qvd] (qvd)
WHERE APPLYMAP('mPorCalc', PORTFOLIO_CALCULATION_KEY, NULL()) = 'DW_VAL_IFRS_PL'
    AND "Calculation Result Type" = 1
    AND ApplyMap('mInsType', SECURITY_KEY) = 'Deposit'
    AND MATCH(PORTFOLIO_CALCULATION_KEY & '_' & NUM([Valuation Date Full]) & '-' & NUM([Calculation Date Full]), $(vPFCDList))
    AND [Valuation Date Full] < (Today(0) + 1);

INNER JOIN LOAD 
    QUOTATION_CURRENCY_KEY,
    [Quotation Currency] AS [Quotation Currency PFC]
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Quotation Currency.qvd] (qvd);

Key Filters:

DW_VAL_IFRS_PL - Specific IFRS valuation calculation
Calculation Result Type = 1 - Main result type
Deposit instruments only
Latest calculation per day (vPFCDList filter)
No future dates

Fields Created:

Portfolio Group PFC - From portfolio mapping
Balance Nominal Or Number - Converted to PC using FX rate
Quotation Currency PFC - Currency description

RPT_000080 - MYR Money Market Activities Daily Average Placement:

Purpose: Track daily money market placements and average balances

Tables Used:

Transactions (main) - for new placements
PFC_Holdings - for ending balances

Key Dimensions:

Trade Date / Valuation Date Full
Security Name
Portfolio Group / Portfolio Group PFC
Quotation Currency

Key Measures:

Principal Amount (xSHEET06_PrincipalAmt) - transaction-based
Principal Amount Holdings (xSHEET06_PrincipalAmtH) - balance-based

Business Calculations:

Daily Average Balance = Sum(Holdings) / Number of Days

Interest Calculation = Average Balance × Rate × Days / 365

Cash Flow Tracking:
  Opening Balance
  + Placements (from xSHEET06_PrincipalAmt where positive)
  - Withdrawals (from xSHEET06_PrincipalAmt where negative)
  = Closing Balance (from xSHEET06_PrincipalAmtH)

Business Use Cases:

Liquidity management
Interest income forecasting
Treasury reporting
Cash positioning

Sheet 09 - Summary Transaction Report (RPT_000198)

Purpose:

High-level summary of all transaction types (Buy, Sell, Dividend)

No Auxiliary Tables

Filter Definition:

LET xFILTER_SHEET08 = '<[Buy or Sell or Dividend] -= {"-"}>';

Note: Variable named xFILTER_SHEET08 but used in Sheet 09

Business Logic:

Include: All transactions where [Buy or Sell or Dividend] is NOT "-"

This field is calculated in Transactions.txt:
"Buy or Sell or Dividend" = 
  IF MATCH(Bus Trans Code, 'Dividend') → 'Dividend'
  IF MATCH(Bus Trans Code, 'Buy', 'AllocBuyExt') → 'Buy'
  IF MATCH(Bus Trans Code, 'Sell', 'AllocSellExt') → 'Sell'
  ELSE → '-'

So this filter excludes:
- Balance book transactions
- Accounting transactions
- Internal transfers
- All non-trading activities

From Transactions.txt (line 51):

"Buy or Sell or Dividend" = 
    UPPER(IF(
        MATCH("Bus Trans Code",'Dividend'), 'Dividend',
        IF(MATCH("Bus Trans Code",'Buy','AllocBuyExt'), 'Buy',
        IF(MATCH("Bus Trans Code",'Sell','AllocSellExt'), 'Sell', '-'))
    ))

Measure Variables (3 Measures):

// 1. No. of Trades - distinct transaction count
LET xSHEET09_NoOfTrades = 
    'Count({$(xFILTER_SHEET08)} DISTINCT [Bus Trans No])';

// 2. Total Amount (QC)
LET xSHEET09_TotalAmount = 
    'Sum({$(xFILTER_SHEET08)} [Payment Amount Qc])';

// 3. Total Amount (MYR)
LET xSHEET09_TotalAmountMyr = 
    'Sum({$(xFILTER_SHEET08)} [Payment Amount Pc])';

Measure Explanations:

Measure	Calculation	Sign Convention
No. of Trades	COUNT(DISTINCT Bus Trans No)	Positive count
Total Amount QC	SUM(Payment Amount Qc)	+/- based on buy/sell
Total Amount MYR	SUM(Payment Amount Pc)	+/- based on buy/sell

Sign Convention:

Buy transactions: Payment Amount < 0 (cash outflow)
Sell transactions: Payment Amount > 0 (cash inflow)
Dividend: Payment Amount > 0 (cash inflow)

Net Amount = Sum(Sell + Dividend) - Sum(Buy)

RPT_000198 - Summary Transaction Report:

Purpose: Executive summary of all trading and dividend activity

Tables Used:

Transactions (main)

Key Dimensions:

Trade Date
Buy or Sell or Dividend (classification)
Portfolio Group
Instrument Type

Key Measures:

Number of Trades (xSHEET09_NoOfTrades)
Total Amount QC (xSHEET09_TotalAmount)
Total Amount MYR (xSHEET09_TotalAmountMyr)

Typical Layout:

Date       | Type     | No. of Trades | Total Amount (MYR)
-----------|----------|---------------|-------------------
2024-01-15 | Buy      | 25           | -1,500,000
2024-01-15 | Sell     | 18           | 1,200,000
2024-01-15 | Dividend | 5            | 50,000
-----------|----------|---------------|-------------------
TOTAL      |          | 48           | -250,000

Business Use Cases:

Daily activity summary
Portfolio manager dashboard
Compliance reporting
Management overview

Sheet 11 - Daily Transaction Value (RPT_000066)

Purpose:

Daily summary of equity purchase and sale values

No Auxiliary Tables

Filter Definitions:

// Base equity filter (includes ETF/REIT)
LET xFILTER_SHEET11 = 
    '($(xFILTER_Equity)+<[Instrument Type] *= {"Fund Certificate"}, 
                          [Security Type]={"ETF", "ETP", "ETN", "REIT"}>)';

// Buy filter
LET xFILTER_SHEET11_BUY = 
    REPLACE('$(xFILTER_SHEET11)', '>', ', [Buy or Sell] = {"Buy"}>');

// Sell filter
LET xFILTER_SHEET11_SELL = 
    REPLACE('$(xFILTER_SHEET11)', '>', ', [Buy or Sell] = {"Sell"}>');

Filter Breakdown:

xFILTER_Equity (from General_Filters.txt):

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

Combined xFILTER_SHEET11:

Condition 1: Equity instruments (Equity, GDR/ADR, Right, Warrant, Covered warrant, Bond)
OR
Condition 2: Fund Certificate AND (ETF, ETP, ETN, REIT)

Note: This is similar to IsS2 filter but implemented differently

Measure Variables (5 Measures):

// 1. Purchase Value (MYR) - gross amount
LET xSHEET11_Purchase = 
    'Sum({$(xFILTER_SHEET11_BUY)} [Current Value Pc])';

// 2. Purchase Value (QC)
LET xSHEET11_PurchaseQc = 
    'Sum({$(xFILTER_SHEET11_BUY)} [Current Value Qc])';

// 3. Sales Value (MYR) - gross amount
LET xSHEET11_Sales = 
    'Sum({$(xFILTER_SHEET11_SELL)} [Current Value Pc])';

// 4. Sales Value (QC)
LET xSHEET11_SalesQc = 
    'Sum({$(xFILTER_SHEET11_SELL)} [Current Value Qc])';

// 5. Total Shares Traded
LET xSHEET11_Shares = 
    'Sum({$(xFILTER_SHEET11_BUY)+$(xFILTER_SHEET11_SELL)} [Nominal])';

Key Difference from Sheet 04:

Sheet 04: Uses Payment Amount (net after costs)
Sheet 11: Uses Current Value (gross before costs)

Sheet 04: Only AllocBuyExt/AllocSellExt
Sheet 11: All Buy/Sell types

RPT_000066 - Daily Transaction Value:

Purpose: Daily trading volume summary by value

Tables Used:

Transactions (main)

Key Dimensions:

Trade Date
Portfolio Group
Security Type
Instrument Type

Key Measures:

Purchase MYR/QC (xSHEET11_Purchase/PurchaseQc)
Sales MYR/QC (xSHEET11_Sales/SalesQc)
Total Shares (xSHEET11_Shares)

Typical Layout:

Date       | Purchase (MYR) | Sales (MYR) | Net (MYR)  | Shares
-----------|----------------|-------------|------------|--------
2024-01-15 | 5,000,000     | 3,500,000   | 1,500,000  | 250,000
2024-01-16 | 2,800,000     | 4,200,000   | -1,400,000 | 180,000

Calculated Fields in Report:

Net Value = Purchase - Sales
Average Buy Price = Purchase / (Shares where Buy)
Average Sell Price = Sales / (Shares where Sell)
Turnover = Purchase + Sales

Business Use Cases:

Daily trading activity monitoring
Volume trend analysis
Market impact assessment
Broker allocation analysis

Sheet 12 - Transaction History Trading (RPT_000178)

Purpose:

Detailed transaction history with two table formats

No Auxiliary Tables

Filter Definition:

LET xFILTER_SHEET12 = 
    '(' & REPLACE('$(xFILTER_Equity)', '>', 
                  ', [Bus Trans Code] = {"*Buy*","*Sell*"}>')
    & '+<[Instrument Type] *= {"Fund Certificate"}, 
         [Security Type]={"ETF", "ETP", "ETN", "REIT"}, 
         [Bus Trans Code] = {"*Buy*","*Sell*"}>)';

Same logic as Sheet 11 and Sheet 02 (IsS2)

Commented Line (line 12):

// Example of portfolio/counterparty specific filter (not currently used):
// LET xFILTER_SHEET12 = '$(xFILTER_Equity)' & '*' & 
//     '<[Portfolio] = {"ASB_A","ASM_A","ASM2_A","ASB3_A"}, 
//       [Counterparty Name] = {"*Maybank*","*CIMB*","*Citigroup*","*JP*Morgan*"}>';

This shows the filter can be customized for specific portfolios/brokers if needed.

Measure Variables - TABLE 1 (8 Measures):

Table 1 focuses on gross amounts and cost breakdowns:

// 1. Quantity
LET xSHEET12_TABLE1_Quantity = 
    'Sum({$(xFILTER_SHEET12)} Nominal)';

// 2. Commission/Brokerage (MYR)
LET xSHEET12_TABLE1_CommissionMyr = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_Brokerage)} 
          [Amount Portfolio Currency])';

// 3. Fees (MYR) - all fees except brokerage
LET xSHEET12_TABLE1_FeesMyr = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_NotBrokerage)} 
          [Amount Portfolio Currency])';

// 4. Gross Principal (MYR) - absolute value
LET xSHEET12_TABLE1_GrossPrincipalRm = 
    'Sum({$(xFILTER_SHEET12)} FABS([Current Value Pc]))';

// 5. Commission/Brokerage (QC)
LET xSHEET12_TABLE1_CommissionQc = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_Brokerage)} 
          [Amount Quotation Currency])';

// 6. Fees (QC)
LET xSHEET12_TABLE1_FeesQc = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_NotBrokerage)} 
          [Amount Quotation Currency])';

// 7. Gross Principal (QC) - absolute value
LET xSHEET12_TABLE1_GrossPrincipalQc = 
    'Sum({$(xFILTER_SHEET12)} FABS([Current Value Qc]))';

// 8. Net Money (MYR) - from Costs table
LET xSHEET12_TABLE1_NetMoneyRm = 
    'Sum({$(xFILTER_SHEET12)} [Amount Portfolio Currency])';

// 9. Net Money (QC) - from Costs table
LET xSHEET12_TABLE1_NetMoneyQc = 
    'Sum({$(xFILTER_SHEET12)} [Amount Quotation Currency])';

Note: Measures 8-9 sum from Transactions Costs table, not main Transactions table

Measure Variables - TABLE 2 (5 Measures):

Table 2 focuses on per-share metrics and specific cost types:

// 1. No. of Shares
LET xSHEET12_TABLE2_NoOfShare = 
    'Sum({$(xFILTER_SHEET12)} Nominal)';

// 2. Price Done (MYR) - average execution price
LET xSHEET12_TABLE2_PriceDoneRm = 
    'ALT(Sum({$(xFILTER_SHEET12)} FABS([Current Value Pc])) / 
         Sum({$(xFILTER_SHEET12)} FABS([Nominal])), 0)';

// 3. Brokerage (MYR)
LET xSHEET12_TABLE2_BrokerageRm = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_Brokerage)} 
          [Amount Portfolio Currency])';

// 4. Scan Fee (MYR)
LET xSHEET12_TABLE2_ScanFeeRm = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_ScanFee)} 
          [Amount Portfolio Currency])';

// 5. Stamp Duty (MYR)
LET xSHEET12_TABLE2_StampDutyRm = 
    'Sum({$(xFILTER_SHEET12)*$(xFILTER_StampDuty)} 
          [Amount Portfolio Currency])';

Key Calculation - Price Done:

Price Done = Total Gross Value / Total Shares
           = Average execution price per share
           
Uses FABS() to handle both buy and sell as positive values
ALT() returns 0 if division by zero

RPT_000178 - Transaction History Trading:

Purpose: Complete transaction history with detailed cost breakdowns

Tables Used:

Transactions (main)
Transactions Costs (for cost details)

Key Dimensions:

Trade Date
Settlement Date
Security Name
Portfolio Group
Counterparty Name
Broker Name
Bus Trans Code

Report Structure:

TABLE 1 - Transaction Summary:

Security | Quantity | Gross (MYR) | Commission | Fees | Net (MYR)
---------|----------|-------------|------------|------|----------
ABC      | 10,000   | 50,000      | 250        | 100  | 49,650
XYZ      | 5,000    | 25,000      | 125        | 50   | 24,825

TABLE 2 - Cost Breakdown:

Security | Shares | Price Done | Brokerage | Scan Fee | Stamp Duty
---------|--------|------------|-----------|----------|------------
ABC      | 10,000 | 5.00       | 250       | 25       | 50
XYZ      | 5,000  | 5.00       | 125       | 12       | 25

Business Use Cases:

Audit trail
Cost analysis
Execution quality review
Broker performance evaluation
Historical transaction lookup

Sheet 13 - Cost Adjustments

Purpose:

Track non-trading cost adjustments and accounting entries

No Auxiliary Tables

Filter Definition:

LET xFILTER_SHEET13 = 
    '<[Elementary Trans Code] *={
        "General Cost",
        "EOP adj.",
        "ScripDivBuyDef",
        "CoupCapital",
        "ImpairmentStart",
        "ImpairReverse",
        "Write-off"
    }>';

Elementary Transaction Codes Explained:

Code	Full Name	Description	Source Field
`General Cost`	General Cost	Various costs not in other categories	Payment Amount Pc
`EOP adj.`	End of Period Adjustment	P&L adjustments at period end	Inp Unreal PL Cost Ccy Pc + Inp Unreal PL Cost Sec Pc
`ScripDivBuyDef`	Scrip Dividend Buy Deferred	Scrip dividend receiving	Payment Amount Pc
`CoupCapital`	Coupon Capital	Bond coupon payment	Current Value Pc
`ImpairmentStart`	Impairment Start	Asset impairment booking	Impairment Pc
`ImpairReverse`	Impairment Reverse	Impairment reversal	(Assumed negative Impairment Pc)
`Write-off`	Write-off	Asset write-off	Write Off Pc

Note: MatIntAdj (Maturity Interest Adjustment) is commented out in the code (lines 5, 30)

Measure Variable (1 Complex Measure):

LET xSHEET13_Amount = 
    'Sum({$(xFILTER_SHEET13)} 
        IF([Elementary Trans Code]=''General Cost'', [Payment Amount Pc],
        IF([Elementary Trans Code]=''EOP adj.'', 
           [Inp Unreal PL Cost Ccy Pc]+[Inp Unreal PL Cost Sec Pc],
        IF([Elementary Trans Code]=''ScripDivBuyDef'', [Payment Amount Pc],
        IF([Elementary Trans Code]=''CoupCapital'', [Current Value Pc],
        IF([Elementary Trans Code]=''ImpairmentStart'', [Impairment Pc],
        IF([Elementary Trans Code]=''Write-off'', [Write off Pc],
        0))))))
    )';

Nested IF Logic Breakdown:

For each transaction, check Elementary Trans Code and use appropriate field:

General Cost → Payment Amount Pc
  (Normal cost transactions)

EOP adj. → Inp Unreal PL Cost Ccy Pc + Inp Unreal PL Cost Sec Pc
  (Sum of currency and security unrealized P&L adjustments)

ScripDivBuyDef → Payment Amount Pc
  (Scrip dividend value)

CoupCapital → Current Value Pc
  (Coupon payment amount)

ImpairmentStart → Impairment Pc
  (Impairment charge amount)

Write-off → Write Off Pc
  (Write-off amount)

Other → 0
  (Should not occur given filter)

Fields from Transactions.qvd:

Payment Amount Pc - Standard payment field
Inp Unreal PL Cost Ccy Pc - Input Unrealized P&L Cost Currency (line 103)
Inp Unreal PL Cost Sec Pc - Input Unrealized P&L Cost Security (line 104)
Current Value Pc - Current value field
Impairment Pc - Impairment amount (line 187)
Write Off Pc - Write-off amount (line 181)

Cost Adjustments Report:

Purpose: Track non-standard cost entries and accounting adjustments

Tables Used:

Transactions (main)

Key Dimensions:

Trade Date
Elementary Trans Code
Elementary Trans Code Name
Security Name
Portfolio Group
Portfolio Group Name S13

Key Measure:

Amount (xSHEET13_Amount) - dynamically calculated based on transaction type

Typical Layout:

Date       | Code            | Description              | Security | Amount (MYR)
-----------|-----------------|--------------------------|----------|--------------
2024-01-15 | General Cost    | Management Fee           | Fund A   | -10,000
2024-01-31 | EOP adj.        | MTM Adjustment           | Bond B   | 5,500
2024-02-01 | ImpairmentStart | Credit Impairment        | Bond C   | -50,000
2024-03-15 | CoupCapital     | Coupon Payment           | Bond D   | 25,000
2024-06-30 | Write-off       | Defaulted Security       | Bond E   | -100,000

Business Use Cases:

P&L reconciliation
Non-trading cost tracking
Impairment monitoring
Accounting adjustment audit
End-of-period reviews

Sheet 14 - Domestic Bonds

Purpose:

Track domestic (MYR) bond transactions

No Auxiliary Tables

Filter Definition:

LET xFILTER_SHEET14 = 
    '<[Quotation Currency] = {"MYR"}, 
      [Instrument Type] = {"Bond","ABS","Index Bond"}>';

Business Logic:

Include only:
- Quotation Currency = MYR (Malaysian Ringgit)
- Instrument Type = Bond, ABS (Asset-Backed Security), or Index Bond

Exclude:
- Foreign currency bonds (USD, SGD, etc.)
- Deposit instruments
- Equity instruments

Instrument Types:

Bond: Standard fixed income securities
ABS: Asset-Backed Securities
Index Bond: Inflation-linked bonds

Measure Variables (2 Measures):

// 1. Nominal - face value amount
LET xSHEET14_Nominal = 
    'Sum({$(xFILTER_SHEET14)} Nominal)';

// 2. Proceeds - net amount
LET xSHEET14_Proceeds = 
    'Sum({$(xFILTER_SHEET14)} [Payment Amount Pc])';

Simple and straightforward:

No cost breakdowns
No complex calculations
Just nominal amount and net proceeds

Domestic Bonds Report:

Purpose: Summary of domestic bond transactions

Tables Used:

Transactions (main)

Key Dimensions:

Trade Date
Security Name
Issuer Name
Maturity Date
Coupon Rate
Portfolio Group
Buy or Sell

Key Measures:

Nominal (xSHEET14_Nominal) - face value
Proceeds (xSHEET14_Proceeds) - cash amount

Typical Layout:

Date       | Security | Issuer  | Maturity   | Coupon | Nominal   | Proceeds (MYR)
-----------|----------|---------|------------|--------|-----------|----------------
2024-01-15 | MGS 3/26 | Govt MY | 15-MAR-26  | 3.00%  | 1,000,000 | 1,015,000
2024-01-16 | CIMB 5/27| CIMB    | 20-MAY-27  | 5.00%  | 500,000   | 510,000

Calculated Fields in Report:

Price (%) = (Proceeds / Nominal) × 100
  Example: (1,015,000 / 1,000,000) × 100 = 101.5%

Clean Price = Dirty Price - Accrued Interest

Yield to Maturity = Complex calculation based on:
  - Price
  - Coupon
  - Time to maturity

Business Use Cases:

Domestic bond trading summary
MYR exposure tracking
Yield curve analysis
Maturity ladder management
Government vs Corporate split

📊 Complete Field Mapping Summary

Source → Transactions → Reports Field Flow:

Source Field	Intermediate	Sheet	Final Report Field	Calculation
`Interest Dividend Capital Event Pc`	Same	07	Interest Earned (MYR)	Sum by month
`Interest Dividend Capital Event Qc`	Same	07	Interest Earned (QC)	Sum by month
`Trade Date Month`	Same	07	Month dimension	For filtering
`Payment Amount Pc`	Same	06	Principal Amount	Where Bus Trans Code = Placement
`Balance Nominal Or Number` (PFC)	Same	06	Principal Amount Holdings	From PFC_Holdings
`Bus Trans No`	Same	09	No. of Trades	COUNT(DISTINCT)
`Payment Amount Pc/Qc`	Same	09, 11, 14	Total Amount	Sum
`Current Value Pc/Qc`	Same	11, 12	Gross Principal	Sum with FABS()
`Nominal`	Same	11, 12, 14	Quantity/Shares	Sum
`Elementary Trans Code`	Same	13	Cost Type	For conditional logic
`Impairment Pc`	Same	13	Impairment Amount	IF logic
`Write Off Pc`	Same	13	Write-off Amount	IF logic
`Quotation Currency`	Same	14	Currency Filter	WHERE = MYR

🔍 Critical Transformations Recap

1. Monthly Aggregation (Sheet 07):

Create dimension table with month names (JAN-DEC) + TOTAL
Join to Transactions via [Month Name w Total]
Use dynamic IF logic to show interest in correct month or total

2. PFC Holdings Table (Sheet 06):

Load from Portfolio Calculation Results
Filter: DW_VAL_IFRS_PL calculation + Deposit instruments
Take latest calculation per day (vPFCDList)
Convert Balance to PC using FX rate
Join Quotation Currency for description

3. Dynamic Interest Measures (Sheet 07):

IF [Month Name w Total] = 'TOTAL':
  → Sum all months
ELSE IF [Month Name w Total] = [Trade Date Month Name Short]:
  → Sum for matching month only
ELSE:
  → Show '-' (dash)

4. Cost Adjustment Amount (Sheet 13):

Nested IF based on Elementary Trans Code:
  General Cost → Payment Amount
  EOP adj. → Unrealized P&L components
  Impairment → Impairment Pc
  Write-off → Write Off Pc
  etc.

5. Average Execution Price (Sheet 12):

Price Done = FABS(Total Current Value) / FABS(Total Nominal)
Uses FABS() to handle buy and sell consistently
ALT() provides 0 if division by zero

📋 Data Quality Checks

Validation Rules:

Monthly Aggregation Completeness:

// All 12 months should exist in Monthly_Aggregations
COUNT(DISTINCT [Month Name w Total]) = 13  // 12 months + TOTAL

PFC Holdings Balance:

// PFC balances should match for selected date
SELECT * FROM PFC_Holdings
WHERE [Valuation Date Full] NOT IN (
  SELECT DISTINCT [Trade Date] FROM Transactions
  WHERE [Instrument Type] = 'Deposit'
)

Elementary Trans Code Coverage:

// Check for unhandled codes in Sheet 13
SELECT DISTINCT [Elementary Trans Code]
FROM Transactions
WHERE [Elementary Trans Code] NOT IN (
  'General Cost', 'EOP adj.', 'ScripDivBuyDef', 
  'CoupCapital', 'ImpairmentStart', 'ImpairReverse', 'Write-off'
)
AND xSHEET13_Amount IS NOT NULL

Domestic Bond Currency Check:

// Verify MYR bonds don't have FX exposure
SELECT * FROM Transactions
WHERE [Instrument Type] IN ('Bond', 'ABS', 'Index Bond')
  AND [Quotation Currency] = 'MYR'
  AND [Fx Rate Qc Pc] <> 1

🎯 Key Performance Indicators (KPIs) by Report

RPT_000049 - Interest Earned:

KPI	Formula	Business Meaning
Total Annual Interest	Sum(All Months)	Total income
Average Monthly Interest	Sum(Interest) / 12	Monthly average
Interest by Instrument	Group by Bond vs Deposit	Source analysis
YoY Growth	This Year / Last Year - 1	Growth rate

RPT_000080 - Money Market:

KPI	Formula	Business Meaning
Average Daily Balance	Sum(Holdings) / Days	Avg position
Placement Activity	Sum(Placements)	Cash flow
Implied Rate	Interest / Avg Balance × 365	Yield
Liquidity Ratio	Deposit / Total Assets	Cash position

RPT_000198 - Summary Transaction:

KPI	Formula	Business Meaning
Trade Count	Count(Distinct Trades)	Activity level
Net Cash Flow	Sum(Payment Amount)	Cash movement
Buy/Sell Ratio	Buy Amount / Sell Amount	Portfolio direction

RPT_000178 - Transaction History:

KPI	Formula	Business Meaning
Average Commission Rate	Commission / Gross Amount	Cost efficiency
Total Transaction Costs	Broker + Fees + Stamp + Scan	Cost tracking
Execution Quality	Price Done vs Benchmark	Performance

🔧 Troubleshooting Guide

Issue 1: Missing Monthly Data in Sheet 07

Symptom: Months showing as blank instead of 0 or dash

Possible Causes:

Monthly_Aggregations table not loaded
[Month Name w Total] field missing
LEFT KEEP filtering out months

Solution:

// Verify Monthly_Aggregations loaded
SELECT * FROM [Month Name w Total]

// Check if LEFT KEEP removed months
// Should have 13 rows (12 months + TOTAL)

Issue 2: PFC Holdings Not Matching Transactions (Sheet 06)

Symptom: Principal Amount Holdings is null or mismatched

Possible Causes:

Valuation Date doesn't match Trade Date
Portfolio Group mapping mismatch
Latest PFC calculation not selected
vPFCDList filter too restrictive

Solution:

// Check PFC_Holdings table exists and has data
SELECT COUNT(*) FROM PFC_Holdings

// Verify date alignment
SELECT [Valuation Date Full], [Trade Date]
FROM PFC_Holdings
JOIN Transactions ON ...

// Check if Latest PFC filter working
SELECT PORTFOLIO_CALCULATION_KEY, 
       [Valuation Date Full], 
       [Calculation Date Full]
FROM Portfolio Calculation Results
WHERE ...

Issue 3: Cost Adjustment Amounts Incorrect (Sheet 13)

Symptom: Amounts don't match source fields

Possible Causes:

Elementary Trans Code not in filter list
Wrong field used for specific code
NULL values in source fields

Solution:

// Check which codes exist
SELECT DISTINCT [Elementary Trans Code], COUNT(*)
FROM Transactions
GROUP BY [Elementary Trans Code]

// Verify field values for each code
SELECT [Elementary Trans Code],
       [Payment Amount Pc],
       [Inp Unreal PL Cost Ccy Pc],
       [Impairment Pc],
       [Write Off Pc]
FROM Transactions
WHERE [Elementary Trans Code] IN (...)

Issue 4: Domestic Bonds Including Foreign Bonds (Sheet 14)

Symptom: Non-MYR bonds appearing in report

Possible Causes:

Quotation Currency field incorrect
Filter not applied properly
Currency mapping issue

Solution:

// Check Quotation Currency values
SELECT DISTINCT [Quotation Currency], COUNT(*)
FROM Transactions
WHERE [Instrument Type] IN ('Bond', 'ABS', 'Index Bond')
GROUP BY [Quotation Currency]

// Verify filter application
SELECT * FROM Transactions
WHERE [Instrument Type] IN ('Bond', 'ABS', 'Index Bond')
  AND [Quotation Currency] <> 'MYR'

📚 Business Rules Reference

Interest Accrual Rules:

Instrument	Accrual Basis	Payment Frequency	Calculation
Bond	Actual/Actual	Semi-annual or Annual	Days × Rate / 365
Index Bond	Actual/Actual	Semi-annual	Linked to inflation index
Deposit	Actual/365	Monthly or Maturity	Simple interest

Cost Adjustment Types:

Type	Accounting Impact	Cash Impact	Frequency
General Cost	P&L	Yes	As incurred
EOP Adjustment	P&L	No	Period end
Impairment	P&L	No	As needed
Write-off	P&L	No	As needed
Coupon Capital	Balance Sheet	Yes	Per coupon date

Money Market Conventions:

Placement Types:
- Fixed Deposit: Specific maturity, fixed rate
- Call Deposit: Callable on demand
- Notice Deposit: Requires notice period

Interest Calculation:
Daily Interest = Principal × Rate / 365
Total Interest = Sum(Daily Interest) over holding period

Bond Pricing Formulas:

Dirty Price = Clean Price + Accrued Interest

Accrued Interest = (Coupon × Days Since Last Payment) / Days in Coupon Period

Clean Price = Present Value of Future Cash Flows

Yield Calculation:
Iterative solution where:
Price = Sum(Coupon / (1 + Yield)^t) + Face / (1 + Yield)^n

✅ Validation Checklist

Before using these reports, verify:

Monthly_Aggregations table loaded with 13 rows
PFC_Holdings table loaded for deposits
Latest PFC calculation filter (vPFCDList) working
[Month Name w Total] field populated
[Trade Date Month Name Short] field populated
[Buy or Sell or Dividend] field calculated
All Elementary Trans Codes mapped
Impairment Pc and Write Off Pc fields present
Quotation Currency correctly populated
Interest Dividend Capital Event fields not null
PFC Portfolio Group mapping correct

📖 Glossary of Terms

DW_VAL_IFRS_PL: Data Warehouse Valuation IFRS Profit & Loss calculation type

EOP: End of Period - accounting adjustments made at month/quarter/year end

PFC: Portfolio Calculation - system that calculates portfolio valuations and holdings

vPFCDList: Variable containing list of latest PFC calculations per day

Dirty Value: Bond/security value including accrued interest

Clean Price: Bond price excluding accrued interest

Scrip Dividend: Dividend paid in shares instead of cash

ABS: Asset-Backed Security - security backed by pool of assets

MGS: Malaysian Government Securities

Placement: Money market transaction (deposit placement)

📋 Table of Contents

📊 Overview: Remaining Reports Category

Reports Covered (7 Reports):

🔄 Complete Data Flow Diagram

📦 Phase 1: Raw Data Sources (QVD Files)

Source 1: Transactions.qvd (Primary Source)

Source 2: Portfolio Calculation Results.qvd (for Sheet 06)

Source 3: Quotation Currency.qvd (for Sheet 06)

📦 Phase 2: Auxiliary Table - Monthly Aggregations

Created In: Monthly_Aggregations.txt

📦 Phase 3: Sheet-Specific Transformations

Sheet 07 - Interest Earned (RPT_000049)

Purpose:

No Auxiliary Tables

Filter Definitions:

Measure Variables (26 Measures):

DYNAMIC MEASURES (2 measures - for pivot tables):

MONTHLY STATIC MEASURES (24 measures - 12 months × 2 currencies):

Report Layout Options:

RPT_000049 - Interest Earned Report:

Sheet 06 - Money Market Placement Activity (RPT_000080)

Purpose:

No Auxiliary Tables

Filter Definitions:

Measure Variables (2 Measures):

PFC_Holdings Table (from PFC_at_Lodgement.txt):

RPT_000080 - MYR Money Market Activities Daily Average Placement:

Sheet 09 - Summary Transaction Report (RPT_000198)

Purpose:

No Auxiliary Tables

Filter Definition:

Measure Variables (3 Measures):

RPT_000198 - Summary Transaction Report:

Sheet 11 - Daily Transaction Value (RPT_000066)

Purpose:

No Auxiliary Tables

Filter Definitions:

Measure Variables (5 Measures):

RPT_000066 - Daily Transaction Value:

Sheet 12 - Transaction History Trading (RPT_000178)

Purpose:

No Auxiliary Tables

Filter Definition:

Measure Variables - TABLE 1 (8 Measures):

Measure Variables - TABLE 2 (5 Measures):

RPT_000178 - Transaction History Trading:

Sheet 13 - Cost Adjustments

Purpose:

No Auxiliary Tables

Filter Definition:

Measure Variable (1 Complex Measure):

Cost Adjustments Report:

Sheet 14 - Domestic Bonds

Purpose:

No Auxiliary Tables

Filter Definition:

Measure Variables (2 Measures):

Domestic Bonds Report:

📊 Complete Field Mapping Summary

Source → Transactions → Reports Field Flow:

🔍 Critical Transformations Recap

1. Monthly Aggregation (Sheet 07):

2. PFC Holdings Table (Sheet 06):

3. Dynamic Interest Measures (Sheet 07):

4. Cost Adjustment Amount (Sheet 13):

5. Average Execution Price (Sheet 12):

📋 Data Quality Checks

Validation Rules:

🎯 Key Performance Indicators (KPIs) by Report

RPT_000049 - Interest Earned:

RPT_000080 - Money Market:

RPT_000198 - Summary Transaction:

RPT_000178 - Transaction History:

🔧 Troubleshooting Guide

Issue 1: Missing Monthly Data in Sheet 07

Issue 2: PFC Holdings Not Matching Transactions (Sheet 06)

Issue 3: Cost Adjustment Amounts Incorrect (Sheet 13)

Issue 4: Domestic Bonds Including Foreign Bonds (Sheet 14)

📚 Business Rules Reference