balm_aggr_m_2
1. Description
Balm_aggr_m_2 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 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.
2. Program location
<installation_path>/balm_aggr_m_2
3.Screen Configuration
3.1 Process Config Fields
Here enter the base input file and additional inputs required for the setup.
3.1.1 Additional Input file 🔗
| # | File | Type | Description | Required | Example |
|---|---|---|---|---|---|
| 1 | AGGR_Rule | Function call | Calls getrules function which downloads the file to the provided path. The function takes four parameters: fileid, rule_type, connection_string to database, path to download the rule file. | true | getrules(1000,AGGR,{G_BALM_CONN_STRING},{E_2_SH_RESOURCES}/BALM_DOMESTIC/PC_ID.json) |
| 2 | Exchange_Rate | File_path | Provide the path to the exchange rate file. | true | {M_IN002} |
| 3 | OD_Adj_Rule | Function call | Calls getrules function which downloads the file to the provided path. The function takes four parameters: fileid, rule_type, connection_string to database, path to download the rule file. | false | getrules(1000,AGGR,{G_BALM_CONN_STRING},{E_2_SH_RESOURCES}/BALM_DOMESTIC/PC_ID.json) |
3.2 Process Derivations
Here select the aggregator in the first field. The dropdown will show either maturity_aggr or nonmat_aggr depending on the type of source. Map all the values accordingly to the requirements.Account Number,Concat, Cashflows and Institution has to be filled mandatorily.
3.2.1 Derivations desc
| # | 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 | npa | npa field | "" |
3.3 Process Arguments
Here enter the process arguments to be passed to the program. The mandatory and non-mandatory fields are given below.
3.3.1 Mandatory Parameters
| # | Parameters | Description | Example |
|---|---|---|---|
| 1 | src_local_ccy | Source local currency.🔗 | INR |
| 2 | display_local_ccy | Display local currency.🔗 | RUP |
| 3 | consol_ccy | Consolidated currency. 🔗 | INR |
| 4 | default_llg_code | If no rules are satisfied, then this llg code will be used. | 08888 |
3.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 | 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 |
| 4 | log_level | Level of diagnostics written to the log file. | info | error/warn/info/debug/trace/none |
| 5 | is_perf_diagnostics_enabled | The flag that decides whether performance diagnostics will be written to the diagnostics log file. | false | true or false |
| 6 | is_amt_abs | The flag that decides whether amount should be taken as absolute or not at account level. | false | true or false |
| 7 | 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 |
| 8 | npa-values | NPA values separated by comma. If any of the value is found for the given npa field(passed in required field), then interest_rate is taken as 0 and repricing_date is taken as none for that account. | "" | A123,B123 |
| 9 | interest_decimal_precision | The value which decides the interest rate round off precision. | 4 | 4 |
| 10 | amount_decimal_precision | The value which decides the amount value round off precision. | 2 | 2 |
Click ⬇️ to download the sample data.
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 MasterInflowN table where N represents the file number. Eg: aggregated0.txt will be uploaded to MasterInflow1 table and so on.
If its an Outflow file, It will be uploaded in MasterOutflowN table where N represents the file number. Eg: aggregated6.txt will be uploaded to MasterOutflow1 table and so on.
It contains the following fields:
| # | Field | Description | Columns | Example |
|---|---|---|---|---|
| 1 | llg.category | LLLG code as per the rule satisfied or default value. 🔗 | SubType_ID | 8888 |
| 2 | as_on_date | Date of operation as passed through input. | As_On | 26-09-2023 |
| 3 | llg.currency | Currency type for the amount displayed in the subsequent columns. | Currency_ID | RUP |
| 4 | Master | hardcoded | SchemeID | Master |
| 5 | CashflowAggregatedType | It can be INT/SLR/IRS. 🔗 | SLRorIRS | SLR |
| 6 | 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 |
| 7 | 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 |
| 8 | 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 6,7,8 will be repeated to show values for different days(first year) and months(rest 29 years). Eg: for a particular llg.category, as_on_date and llg.currency type:
llg.category|as_on_date|llg.currency|Mater|INT|amt1|rate1|date1|amt2|rate2|date2|...
llg.category|as_on_date|llg.currency|Mater|SLR|amt1|rate1|date1|amt2|rate2|date2|...
llg.category|as_on_date|llg.currency|Mater|IRS|amt1|rate1|date1|amt2|rate2|date2|...
4.2 Output file-13 : aggregated summary file
It will be uploaded in tblProductTotals table.
| # | Field | Description | Columns | Example |
|---|---|---|---|---|
| 1 | Category | LLG code as per the rule satisfied or default value. 🔗 | SubType_ID | 8888 |
| 2 | As On Date | Date of operation as passed through input. | As_On | 26-09-2023 |
| 3 | Currency | Currency type for the amount displayed in the subsequent columns. | Currency_ID | RUP |
| 4 | Cashflow Aggregated Type | It can be INT/SLR/IRS. 🔗 | SLRorIRS | SLR |
| 5 | Master | hardcoded | SchemeID | Master |
| 6 | Cashflow Type | Outflow or Inflow. 🔗 | CashflowType | O or I |
| 7 | Amount | Total INT/SLR/IRS amount. 🔗 | Amount | 11000 |
| 8 | 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": {
"RUP-8888-O": {
"accountsCount": 5,
"cashflowsCount": 5,
"totalPrincipalAmount": 11000.0,
"totalInterestAmount": 11000.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
}
}
}
"llgSummaries": {
"RUP-8888-O": {
"principal_amount": 11000.0,
"interest_amount": 11000.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 and llg.cf_type.
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 and llg.cf_type
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.