QlikSense ETL Analysis - Part 1: Business Context & Report Distribution

This QlikSense application serves Portfolio Management and Investment Operations for what appears to be a financial institution PNB - Permodalan Nasional Berhad based on references in the code.

PRIMARY BUSINESS USE CASE

Investment Portfolio Transaction Management & Reporting System

The system manages and reports on:

Equity trading (stocks, GDRs, ADRs, warrants, rights)
Dividend income tracking and receivables
Bond & Fixed Income investments
Money Market placements (deposits)
Corporate Actions (mergers, bonus issues, rights issues, demergers)
Portfolio Performance (realized/unrealized P&L)
Transaction Costs (brokerage, fees, stamp duty)

BUSINESS CHALLENGES BEING SOLVED

1. Dividend Receivable Tracking Challenge

Problem: Managing unpaid dividends across multiple portfolios and securities
Solution: Real-time tracking of dividend entitlements, payment status, and reconciliation with actual holdings at lodgement date

2. Regulatory Compliance & Audit Trail

Problem: Need detailed contract notes and transaction summaries for regulators
Solution: Comprehensive equity contract notes with all fees, duties, and net amounts broken down

3. Portfolio Performance Measurement

Problem: Calculate accurate realized and unrealized gains/losses across multiple currencies
Solution: Sophisticated P&L calculations with mark-to-market valuations, forex impact, and cost basis tracking

4. Multi-Portfolio Management

Problem: Managing transactions across different portfolio groups (PNB vs UTF - Unit Trust Funds)
Solution: Consolidated reporting with drill-down capability by portfolio, geography (domestic/international), and Islamic/Conventional classification

5. Transaction Cost Analysis

Problem: Understanding the true cost of trading (brokerage, scan fees, stamp duty, SST)
Solution: Detailed cost breakdowns for every transaction type with set analysis filters

6. Cash Flow Management

Problem: Tracking settlement dates, payment dates, and outstanding amounts
Solution: T+1, T+2 settlement tracking with open payment monitoring

COMPLETE REPORT DISTRIBUTION SUMMARY

TOTAL REPORTS IDENTIFIED: 24 Reports

Report Category	Count	Report Numbers	Primary Sheets
Dividend Reports	4	RPT_000019, RPT_000059, RPT_000040, RPT_000537	Sheet 01, Sheet 10
Equity Trading Reports	11	RPT_000003, RPT_000030, RPT_000025, RPT_000047, RPT_000048, RPT_000060, RPT_000061, RPT_000197, RPT_000206, RPT_000207, RPT_000211	Sheet 02, Sheet 04, Sheet 05
Share Order Reports	2	RPT_000209, RPT_000210	Sheet 03
Transaction Summary	2	RPT_000198, RPT_000066	Sheet 09, Sheet 11
Transaction History	1	RPT_000178	Sheet 12
Interest Earned	1	RPT_000049	Sheet 07
Money Market	1	RPT_000080	Sheet 06
Cost Adjustments	1	Unnamed (Sheet 13)	Sheet 13
Bond Reports	1	Unnamed (Sheet 14)	Sheet 14

DETAILED REPORT CATALOG

DIVIDEND REPORTS CATEGORY

Report: RPT_000019 - Dividend Receivables

Sheet: Sheet 01
Purpose: Track unpaid dividends that are yet to be received
Key Metrics: Entitlement, Gross Amount, Tax, Net Amount, Outstanding Amount
Business Rule: Shows dividends where Payment Date >= Report Date OR PaymentClose = 0

Report: RPT_000059 - Dividend Receivable for Common Stock

Sheet: Sheet 01
Purpose: Specific focus on common stock dividends receivable
Key Metrics: Net Cash at Lodgement, Reinvestment Amount, Cash Received vs To Receive
Business Rule: Uses PFC (Portfolio Calculation) balance at lodgement date

Report: RPT_000040 - Dividend Received Report

Sheet: Sheet 10
Purpose: Track dividends that have been fully paid
Key Metrics: Entitlement, Dividend Per Share, Gross/Tax/Net breakdown
Business Rule: PaymentClose = 1 (closed payments only)

Report: RPT_000537 - Dividend Track Record for Counter

Sheet: Sheet 10
Purpose: Historical dividend tracking by security counter
Key Metrics: From Date, To Date, Payment history
Business Rule: Includes GenCost, DivPayIn, Charge transaction types

EQUITY TRADING REPORTS CATEGORY

Report: RPT_000003 - Trade Transaction – Equity

Sheets: Sheet 02, Sheet 05
Purpose: Core equity trading transactions report
Key Metrics: Quantity, Price, Gross Amount, Brokerage, Fees, Net Amount
Business Rule: Covers Buy/Sell of Equity, GDR/ADR, Rights, Warrants, Covered Warrants, ETFs

Report: RPT_000030 - Details of Investment

Sheets: Sheet 02, Sheet 05
Purpose: Investment details with cost analysis
Key Metrics: Average Cost per Share, Cost per Unit, Equity Percentage
Business Rule: Includes External/Internal Trades + Accounting (Valuation/Initialization)

Report: RPT_000025 - Details of Investment Activities

Sheet: Sheet 05
Purpose: Comprehensive investment activity analysis
Key Metrics: Realized G/L, Unrealized G/L, Mark-to-Market G/L, Forex Impact
Business Rule: Complex calculation including reversal of unrealized gains

Report: RPT_000047 - Status of Shares Sold

Sheet: Sheet 02
Purpose: Monitor sold equity positions
Key Metrics: Settlement status, payment amounts
Business Rule: Sell transactions only

Report: RPT_000048 - Status of Purchase Shares

Sheet: Sheet 02
Purpose: Monitor purchased equity positions
Key Metrics: Settlement status, payment amounts
Business Rule: Buy transactions only

Report: RPT_000060 - Daily Summary of Sale Contract Note

Sheet: Sheet 02
Purpose: Daily contract note summary for sales
Key Metrics: Portfolio Group, Transaction Date, Transfer Date (T+1), Settlement Date (T+2)
Business Rule: Aggregated by portfolio group, date-specific

Report: RPT_000061 - Daily Summary of Purchase Contract Note

Sheet: Sheet 02
Purpose: Daily contract note summary for purchases
Key Metrics: Portfolio Group, Transaction Date, Transfer Date (T+1), Settlement Date (T+2)
Business Rule: Aggregated by portfolio group, date-specific

Report: RPT_000197 - Equity Contract Note

Sheet: Sheet 02
Purpose: Detailed contract note for equity transactions
Key Metrics: Full breakdown of costs (Brokerage, Brokerage SST, Scan Fee, Stamp Duty)
Business Rule: Transaction-level detail for audit and compliance

Report: RPT_000206 - Daily Status of Shares Sold

Sheet: Sheet 02
Purpose: Daily monitoring of sold shares
Key Metrics: Quantity, Amount, Status
Business Rule: Daily aggregation of sell transactions

Report: RPT_000207 - Daily Status of Purchased Shares

Sheet: Sheet 02
Purpose: Daily monitoring of purchased shares
Key Metrics: Quantity, Amount, Status
Business Rule: Daily aggregation of buy transactions

Report: RPT_000211 - Purchase and Sales of Shares

Sheet: Sheet 04
Purpose: Combined buy/sell summary
Key Metrics: Buy Amount, Sell Amount, No. of Trades, No. of Shares
Business Rule: Includes Equity, GDR/ADR, Rights, Warrants, Fund Certificates

SHARE ORDER REPORTS CATEGORY

Report: RPT_000209 - Share Purchase Order

Sheet: Sheet 03
Purpose: Track purchase orders and charges
Key Metrics: No. of Shares, Charges, Charges SST, Net Amount
Business Rule: Buy/AllocBuyExt OR Corporate Actions with Price > 0

Report: RPT_000210 - Share Sold Order

Sheet: Sheet 03
Purpose: Track sale orders and charges
Key Metrics: No. of Shares, Charges, Charges SST, Net Amount
Business Rule: Sell/AllocSellExt OR Corporate Actions with Price > 0

TRANSACTION SUMMARY REPORTS CATEGORY

Report: RPT_000198 - Summary Transaction Report

Sheet: Sheet 09
Purpose: High-level transaction summary across all types
Key Metrics: No. of Trades, Total Amount (QC), Total Amount (MYR)
Business Rule: All transactions with Buy/Sell/Dividend classification

Report: RPT_000066 - Daily Transaction Value

Sheet: Sheet 11
Purpose: Daily value of equity transactions
Key Metrics: Purchase (Total Cost), Sales (Total Proceed), Shares
Business Rule: Equity + Fund Certificates (ETF, ETP, ETN, REIT)

TRANSACTION HISTORY REPORTS CATEGORY

Report: RPT_000178 - Transaction History Trading

Sheet: Sheet 12
Purpose: Detailed transaction history for audit
Key Metrics:
- Table 1: Quantity, Commission, Fees, Gross Principal, Net Money (MYR & QC)
- Table 2: No. of Shares, Price Done, Brokerage, Scan Fee, Stamp Duty
Business Rule: Buy/Sell equity transactions only

INTEREST EARNED REPORTS CATEGORY

Report: RPT_000049 - Interest Earned

Sheet: Sheet 07
Purpose: Monthly interest income tracking
Key Metrics: Interest earned by month (Jan-Dec) + Total
Business Rule: Bonds, Index Bonds, Deposits only
Special Feature: Cross-tab structure with months as columns

MONEY MARKET REPORTS CATEGORY

Report: RPT_000080 - MYR Money Market Activities Daily Average Placement

Sheet: Sheet 06
Purpose: Monitor money market placements
Key Metrics: Principal Amount from Deposit transactions
Business Rule: Uses PFC (Portfolio Calculation) balance for holdings

COST ADJUSTMENT REPORTS CATEGORY

Unnamed Report - Cost Adjustments

Sheet: Sheet 13
Purpose: Track cost adjustments and accounting entries
Key Metrics: Amount for various adjustment types
Business Rule: Includes General Cost, EOP Adjustment, Scrip Dividend, Coupon Capital, Impairment, Write-off

BOND REPORTS CATEGORY

Unnamed Report - Domestic Bonds

Sheet: Sheet 14
Purpose: Domestic bond transactions
Key Metrics: Nominal, Proceeds
Business Rule: MYR currency, Bond/ABS/Index Bond instruments

🔑 KEY USER TYPES

Based on the portfolio structure and reports:

1. Fund Managers

Monitor portfolio performance (Sheet 05)
Track dividend income (Sheet 01, 10)
Analyze transaction costs (Sheet 02, 12)

2. Operations Team

Process contract notes (Sheet 02)
Reconcile dividends (Sheet 01, 10)
Monitor settlement (Sheet 02)

3. Compliance & Audit

Transaction history (Sheet 12)
Cost adjustments (Sheet 13)
Contract note verification (Sheet 02)

4. Senior Management

Summary reports (Sheet 09, 11)
Interest earned (Sheet 07)
Money market overview (Sheet 06)

5. Portfolio Accountants

P&L calculations (Sheet 05)
Bond transactions (Sheet 14)
Corporate actions tracking (Sheet 03)

REPORT USAGE PATTERNS

High-Frequency Reports (Daily)

RPT_000060/061 - Daily Contract Note Summaries
RPT_000066 - Daily Transaction Value
RPT_000206/207 - Daily Status of Shares

Medium-Frequency Reports (Weekly/As-Needed)

RPT_000019/059 - Dividend Receivables
RPT_000003 - Trade Transactions
RPT_000178 - Transaction History

Low-Frequency Reports (Monthly/Quarterly)

RPT_000049 - Interest Earned (Monthly)
RPT_000040 - Dividend Received (Monthly)
RPT_000080 - Money Market Placements

Ad-Hoc Reports

RPT_000025 - Investment Activities (Performance Reviews)
RPT_000211 - Purchase and Sales Summary
RPT_000537 - Dividend Track Record

ORGANIZATION CONTEXT

Portfolio Groups:

PNB: Own portfolios (Portfolio Group Type = 'OWN')
UTF (Unit Trust Funds): Client portfolios (Portfolio Group Type = 'CLIENT')
ASNB/AWSB: Specific fund groups (Malaysian national savings schemes)

Geography:

Domestic: MYR-denominated securities (Malaysian market)
International: Foreign currency securities

Investment Types:

Islamic: Shariah-compliant investments
Conventional: Non-shariah compliant