balm_aggr_dim_m

1. Description

Balm_aggr_dim_m program reads a cf file which is in metadata format and aggregates INT(total_interest_amount), SLR(total_principal_amount) and IRS(irs_total_amount). It aggregates on the basis of llg, currency, cashflow type and item_id for the maturity products like fixed deposits.

It shows us result for the next 30 years from the day of operation. The first year is day wise and then the rest 29 years are shown on monthly basis. It generates 6 Master inflow files , 6 Master outflow files,1 aggregated summary file and 2 aggregated health check reports. One health check report with summary and other with full details on the basis of llg.

Note: If item_id is not provided, then this program works similar to balm_aggr_m program.🔗

2. Program location

<installation_path>/balm_aggr_dim_m

3. Input

Click ⬇️ to download the sample data.

3.1 Mandatory Parameters

#	Parameters	Description	Example
1	as_on_date	The date for which the program has to run.	04-10-2023
2	input_file_path	Path to the input file.	test-bed/cf-ubs-loans-output.cf
3	output_file_path	Path to the output file.	test-bed/output/aggregated
4	log_file_path	Path to write logs.	test-bed/log.txt
5	diagnostics_file_path	Path to write diagnostics logs.	test-bed/diag_log.txt
6	src_local_ccy	Source local currency. 🔗	INR
7	display_local_ccy	Display local currency. 🔗	RUP
8	consol_ccy	Consolidated currency. 🔗	INR
9	currency_conversion_file_path	The path to the exchange rate file.🔗	test-bed/1000ExchangeRate.txt
10	req_fields_file_path	The req_fields_file_path parameter is a path to a file that describes the names with which to refer to such fields. 🔗 Click here to see the list of required fields for this program.	test-bed/req_fields.json
11	account_metadata_file_path	It is a path to a json file that represents the metadata ,i.e., schema of input file. 🔗	test-bed/metadata.json
12	rules_file_path	The path to the file that contains rules by which to aggregate accounts. 🔗	test-bed/rules.txt
13	default_llg_code	If no rules are satisfied, then this llg code will be used.	08888
14	dim_id	It is the dimentional id. It will remain same for all inputs. It is used for stamping purpose only.	SEG

3.2 Non Mandatory Parameters

#	Parameters	Description	Default value	Example
1	is_consolidated	Is consolidated flag is used to define the currency type of given amount in input field. If is_consolidated flag is set, then the amount given is of consolidated type else it is in the curr input type.🔗	false	true or false
2	is_account_level_exchange_rate	The flag that decides whether the account level exchange rate is provided in the input file or not. If provided, then we use exchange rate for conversion.	false	true or false
3	default_overdue_llg_path	All the cashflow with cfdate < as_on_date will be of overdue type.This file contains 'start_days,end_days,llg_id' separated by pipe. Based on the number of days between cf-date and as-on date of current overdue cf, it is being bucketed to different overdue llg ids (only if performing llg is not equal to default llg). Note: if the file is passed but residual period is not present, then the program will stop and throw error.	"NA"(If the file is not passed, the default llg value is considered.)	test-bed/default_overdue_llg_path
4	is_rep_mandatory	The flag that decides whether the repricing date is mandatory or not. If its mandatory, then we make use of next repricing date present in the input file while doing calculation for IRS type. Also if the falg is false, SLR and IRS output will be same.	false	true or false
5	log_level	Level of diagnostics written to the log file.	info	error/warn/info/debug/trace/none
6	is_perf_diagnostics_enabled	The flag that decides whether performance diagnostics will be written to the diagnostics log file.	false	true or false
7	is_amt_abs	The flag that decides whether amount should be taken as absolute or not at account level.	false	true or false
8	is_aggr_amt_abs	The flag that decides whether aggregated amount should be taken as absolute or not at llg level.	false	true or false
9	npa-values	NPA values separated by comma. If any of the value is found for the given npa field(passed in required field), then rate is taken as 0 and repricing_date is taken as none for that account.	""	A123,B123
10	interest_decimal_precision	The value which decides the interest rate round off precision.	4	4
11	amount_decimal_precision	The value which decides the amount value round off precision.	2	2

Required field file

#	Parameters	Description	Example
1	account_number	account number	acc_no
2	concat	any non empty string	acc_no
3	cashflows	consists of group of cashflow for given account	cashflows
4	institution	currency	currency
5	interest_rate	interest_rate	""
6	next_rep_date	next repricing date	""
7	exchange_rate	account level exchange rate	""
8	item_id	Item id for aggregation purpose. If not provided, then it will take "NA" as value and won't consider item_id for aggregation and the program will work similar to balm_aggr_m program.	acc_no
9	npa	npa field	""

4. Output

This program will generate: 6 master inflow files(aggregated0.txt to aggregated5.txt), 6 master outflow files(aggregated6.txt to aggregated11.txt), 1 aggregated summary file, and 2 aggregated health check reports. One health check report with summary and other with full details on the basis of llg. The 12 master files and aggregated summary file are uploaded to their respective tables.

These 6 master inflow and 6 master outflow will contain 717 date data: out of which the first 365/366(depending on leap year) date data will be day wise and then the remaining will be monthly wise. So 366 + (12*29) equals 717(approx). So the master files contains 30 years of data from the date of operation(as_on_date). Also, Note: if its non leap year, then the 366th record will be empty.

Among the 6 master inflow/outflow files, the first three files will contain daily aggregation data for the 1st year. The next three files will contain monthly aggregation for the next 29 years.

4.1 Output files-1 to 12 : 6 master inflow/outflow files each

Inflow or outflow is categorised on the basis of LLGcode. If its inflow, then the LLGcode will be of 5 digits. If its outflow, then the LLGcode will be of 4 digits.

If its an Inflow file, it will be uploaded in DimMasterInflowN table where N represents the file number. Eg: aggregated0.txt will be uploaded to DimMasterInflow1 table and so on.

If its an Outflow file, It will be uploaded in DimMasterOutflowN table where N represents the file number. Eg: aggregated6.txt will be uploaded to DimMasterOutflow1 table and so on.

It contains the following fields:

#	Field	Description	Columns	Example
1	llg.category	LLG code as per the rule satisfied or default value. 🔗	SubType_ID	8888
2	llg.item_id	Extra field for aggregation purpose.If not provided, then it will take "NA" as value and won't consider item_id for aggregation and the program will work similar to balm_aggr_m program. If provided, lets say item_id=acc_no, then the aggreagtion will be based on acc_no alongside with llg, currency and cashflow type.	Item_ID	1018
3	llg.dim_id	Dimentional id for stamping purpose only.	Dim_ID	SEG
4	as_on_date	Date of operation as passed through input	As_On	04-10-2023
5	llg.currency	Currency type for the amount displayed in the subsequent columns.	Currency_ID	RUP
6	CashflowAggregatedType	It can be INT/SLR/IRS. 🔗	SLRorIRS	SLR
7	amt	Depends on the Cashflow Aggregated Type. Case SLR: total_principal_amount. Case INT: total_interest_amount. Case IRS: irs_total_amount. 🔗	AmountN	11000.00
8	rate	Depends on the Cashflow Aggregated Type. Case SLR: rate_principal_amount_weighted. Case INT: rate_interest_amount_weighted. Case IRS: irs_rate_principal_amount_weighted. 🔗	InterestRateN	0.0000
9	date	Depends on the Cashflow Aggregated Type. Case SLR: date_principal_amount_weighted. Case INT: date_interest_amount_weighted. Case IRS: irs_date_principal_amount_weighted. 🔗	AvgDateN	1.00

Note: column 7,8,9 will be repeated to show values for different days(first year) and months(rest 29 years). Eg: for a particular llg.category, llg.item_id, llg.dim_id, as_on_date and llg.currency type:

llg.category|llg.item_id|llg.dim_id|as_on_date|llg.currency|INT|amt1|rate1|date1|amt2|rate2|date2|...
llg.category|llg.item_id|llg.dim_id|as_on_date|llg.currency|SLR|amt1|rate1|date1|amt2|rate2|date2|...
llg.category|llg.item_id|llg.dim_id|as_on_date|llg.currency|IRS|amt1|rate1|date1|amt2|rate2|date2|...

4.2 Output file-13 : aggregated summary file

It will be uploaded in DimMasterProductTotals table.

#	Field	Description	Columns	Example
1	Category	LLG code as per the rule satisfied or default value. 🔗	SubType_ID	8888
2	llg.item_id	Extra field for aggregation purpose.If not provided, then it will take "NA" as value and won't consider item_id for aggregation and the program will work similar to balm_aggr_m program. If provided, lets say item_id=acc_no, then the aggreagtion will be based on acc_no alongside with llg, currency and cashflow type.	Item_ID	1018
3	llg.dim_id	Dimentional id for stamping purpose only.	Dim_ID	SEG
4	As On Date	Date of operation as passed through input.	As_On	04-10-2023
5	Currency	Currency type for the amount displayed in the subsequent columns.	Currency_ID	RUP
6	Cashflow Aggregated Type	It can be INT/SLR/IRS. 🔗	SLRorIRS	SLR
7	Cashflow Type	Outflow or Inflow. 🔗	CashflowType	O or I
8	Amount	Total INT/SLR/IRS amount. 🔗	Amount	11000.00
9	Rate	INT/SLR/IRS weighted rate. 🔗	InterestRate	0.0000

4.3 Output file-14 : LLG level health check report

In the following example, we can see for each llg generated: accountsCount, cashflowsCount, totalPrincipalAmount, totalInterestAmount and then file wise description is provided. Note: Only llg in inflow files are considered for this calculation.

```
..........
    "llgs": {
"CON-8888-O": {
  "accountsCount": 1,
  "cashflowsCount": 1,
  "totalPrincipalAmount": 5000.0,
  "totalInterestAmount": 5000.0,
  "file0": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file1": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file2": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file3": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file4": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file5": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  }
},
"INR-8888-O": {
  "accountsCount": 1,
  "cashflowsCount": 1,
  "totalPrincipalAmount": 1000.0,
  "totalInterestAmount": 1000.0,
  "file0": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file1": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file2": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file3": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file4": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  },
  "file5": {
    "principal_amount": 0.0,
    "interest_amount": 0.0
  }
},
  ..........
```

4.4 Output file-15 : Aggregated health check report

The health check report gives summary about the processed accounts such as success counts, failed counts, etc.🔗

Output derivation logic:

Cashflow Aggregated Type :

  The cashflow aggragated type can be INT(interest), SLR(Structural Liquidity Ratio) or IRS(Interest rate sensitivity). For each case, we calculate:

  Case INT: date_interest_amount_weighted, rate_interest_amount_weighted and total_interest_amount.

  Case SLR: date_principal_amount_weighted, rate_principal_amount_weighted and total_principal_amount.

  case IRS: irs_date_principal_amount_weighted, irs_rate_principal_amount_weighted and irs_total_amount.

  Note: If the is_rep_mandatory falg is false,then SLR and IRS output will be same, i.e., we we make use of cf_date for calculation of day_num for both. 

  In case IRS, if (the is_rep_mandatory flag is set) OR (is_rep_mandatory flag is not set but cf_date < next_repricing_date), then we make use of next_repricing_date for calculation of day_num.

  More details about the derivation is given below.

amt :

  Step1: At account level, on the basis of date. For example, If the cashflow date is after the aggregation date limit (asondate+30years), assume it's on the last day of the aggregation and if the cashflow date is before as on date, then make cashflow date equal to as_on_date.

  Step2: At LLG level, aggregate similar LLG from Step1.

  Step3: At bucket level, for given LLG from Step2,either do daily aggregation(for 1st year) or monthly aggregation(for next 29 years).

  Below for each case: step1, step2 and step3 are given.

  Case INT: total_interest_amount = Σ interest_amount 
            total_interest_amount = Σ total_interest_amount
            amt = Σ total_interest_amount

  Case SLR: total_principal_amount = Σ principal_amount
            total_principal_amount = Σ total_principal_amount
            amt = Σ total_principal_amount

  Case IRS: irs_total_amount = Σ irs_amount
            irs_total_amount = Σ irs_total_amount
            amt = Σ irs_total_amount

  Note: If is_amt_abs is set, then before summation at account level take absolute value of amount.Also if is_aggr_amt_abs is set, then before writing to output take absolute value of total amount.

rate :

  Step1: At account level, on the basis of date. For example, If the cashflow date is after the aggregation date limit (asondate+30years), pretend it's on the last day of the aggregation and if the cashflow date is before as on date, then make cashflow date equal to as_on_date(day1).

  Step2: At LLG level, aggregate similar LLG from Step1.

  Step3: At bucket level, for given LLG from Step2,either do daily aggregation(for 1st year) or monthly aggregation(for next 29 years).

  Below for each case: step1, step2 and step3 are given.

  Case INT: rate_interest_amount_weighted = Σ interest_amount * rate
            rate_interest_amount_weighted = Σ rate_interest_amount_weighted
            rate = Σ rate_interest_amount_weighted / total_interest_amount

  Case SLR: rate_principal_amount_weighted = Σ principal_amount * rate
            rate_principal_amount_weighted = Σ rate_principal_amount_weighted
            rate = Σ rate_principal_amount_weighted / total_principal_amount

  Case IRS: irs_rate_principal_amount_weighted = Σ irs_amount * irs_rate
            irs_rate_principal_amount_weighted = Σ irs_rate_principal_amount_weighted
            rate = Σ irs_rate_principal_amount_weighted / irs_total_amount

  Note: If is_amt_abs is set, then before summation take absolute value of rate at account level. Also if is_aggr_amt_abs is set, then before writing to output take absolute value of weighted rate.
  

date :

  Step1: At account level, on the basis of date. For example, If the cashflow date is after the aggregation date limit (asondate+30years), pretend it's on the last day of the aggregation(but day_num is calculated using original cashflow date, only for bucketing purpose last day is used) and if the cashflow date is before as on date, then make cashflow date equal to as_on_date(day1).

  Step2: At LLG level, aggregate similar LLG from Step1.

  Step3: At bucket level, for given LLG from Step2,either do daily aggregation(for 1st year) or monthly aggregation(for next 29 years).
  
  Below for each case: step1, step2 and step3 are given.

  Case INT: date_interest_amount_weighted = Σ interest_amount * day_num
            date_interest_amount_weighted = Σ date_interest_amount_weighted 
            date = Σ date_interest_amount_weighted / total_interest_amount

  Case SLR: date_principal_amount_weighted = Σ principal_amount * day_num
            date_principal_amount_weighted = Σ date_principal_amount_weighted
            date = Σ date_principal_amount_weighted / total_principal_amount

  Case IRS: irs_date_principal_amount_weighted = Σ irs_amount * irs_day_num
            irs_date_principal_amount_weighted = Σ irs_date_principal_amount_weighted
            date = irs_date_principal_amount_weighted / irs_total_amount

  Note: If cf_date <= as_on_date, then day_num=1.
  
  If cfdate >= as_on_date, then day_num is calculated as number of days from as_on_date to cf_date.
  
  In case of INT and SLR, we use cf_date for calculation of day_num.
  
  In case IRS, if (the is_rep_mandatory flag is set) OR (is_rep_mandatory flag is not set but cf_date < next_repricing_date), then we make use of next_repricing_date for calculation of day_num. In every other case, we make use of cf_date for calculation of day_num.

Aggregated amount :

Aggregated on the basis of Cashflow aggregated type and LLGkey generated, which consists of llg.currency, llg.category, llg.cf_type, llg.item_id and llg.dim_id.

  total_int_amount = Σ int.amount
  total_slr_amount = Σ slr.amount
  total_irs_amount = Σ irs.amount

Note: If is_amt_abs is set, then before summation take absolute value of amount at account level. Also if is_aggr_amt_abs is set, then before writing to output take absolute value of total amount. 
  

Aggregated rate :

Aggregated on the basis of Cashflow aggregated type and LLGkey generated, which consists of llg.currency, llg.category, llg.cf_type, llg.item_id and llg.dim_id.

int_weighted_rate = Σ (int_rate * int_amount) / Σ int_amount
slr_weighted_rate = Σ (slr_rate * slr_amount) / Σ slr_amount
irs_weighted_rate = Σ (irs_rate * irs_amount) / Σ irs_amount

Note: If is_amt_abs is set, then before summation take absolute value of rate at account level. Also if is_aggr_amt_abs is set, then before writing to output take absolute value of weighted rate.

5. Process configuration in streamdef table:

  .........
    "processName": "TD Dimensional Aggr",
    "processId": "2",
    "processBinary": "{SH_PROGRAMS}/balm_aggr_dim_m",
    "processArguments": [
        "--as-on-date",
        "{AsOnDate}",
        "--input-file",
        "{SH_CFDATA}/IND/{ddmmyyyy}/td.cf",
        "--output-file",
        "{SH_SUMMARYDATA}/IND/BALM/{ddmmyyyy}/dim_balm_td",
        "--log-file",
        "{SH_LOGS}/IND/BALM/{ddmmyyyy}/dim_balm_td_log.txt",
        "--diagnostics-log-file",
        "{SH_LOGS}/IND/BALM/{ddmmyyyy}/dim_balm_td_diag_log.txt",
        "--src-local-ccy",
        "INR",
        "--display-local-ccy",
        "RUP",
        "--consol-ccy",
        "INR",
        "--exchange-rate-file",
        "{SH_RESOURCES}/IND/{ddmmyyyy}/exchangeRate.txt",
        "--req-fields-file",
        "{SH_RESOURCES}/IND/OPERATIONAL-FILES/BALM-REQ-FIELD-FILES/dim_balm_td_req_fields.json",
        "--account-metadata-file",
        "{SH_RESOURCES}/IND/OPERATIONAL-FILES/CF-METADATA-FILES/td_metadata.json",
        "--rules-file-path",
        "{SH_RESOURCES}/IND/OPERATIONAL-FILES/BALM-RULE-FILES/dim_balm_td_rules.txt",
        "--default-llg-code",
        "08888",
        "--dim-id",
        "SEG"
    ],
    "processDependencies": [],
    "processReport": "{SH_SUMMARYDATA}/IND/BALM/{ddmmyyyy}/dim_balm_td-health-check-report.json",
    "processType": "AGGR",
    "infoLog": "{SH_LOGS}/IND/BALM/{ddmmyyyy}/dim_balm_td_log.txt",
    "errorLog": ""
  .........

1. Description​

2. Program location​

3. Input​

3.1 Mandatory Parameters​

3.2 Non Mandatory Parameters​

Required field file​

4. Output​

4.1 Output files-1 to 12 : 6 master inflow/outflow files each​

4.2 Output file-13 : aggregated summary file​

4.3 Output file-14 : LLG level health check report​

4.4 Output file-15 : Aggregated health check report​

Output derivation logic:​

Cashflow Aggregated Type :​

amt :​

rate :​

date :​

Aggregated amount :​

Aggregated rate :​

5. Process configuration in streamdef table:​