Inputs

Key-value pairs that provide configuration options

Usage

Many of the calculations that can be specified in the calcs attribute can be customized by providing specific information to the Universal Calculation Library via inputs. inputs allow for further customization of the calculation outputs by providing the calculations with information about filtering, summarizing, calculating new metrics, and linking in different data sets.

This section details the inputs available for calculations in the eMBS version of the library. If an input only works with a limited number of calculations, those will be specified here. If no calculations are specified, assume the input is valid for all calculations.

inputs are specified using custom attributes only available in the Universal Calculations Library. A basic example is shown below:

<import path="pub.lib.ucl.v3.lib_embs_calc"/>
<insert block="embs_calc" calcs="cpr" ncpr="1,3,6,12" breaks="deal_name"/>

In the example above the ncpr input accepts a comma-separated list of integer values, and the breaks attribute specifies a column name for grouping the final tabulation. The example will calculate 1-month, 3-month, 6-month, and 12-month CPR's broken out by deal.

By default, the Universal Calculation Library automatically links numerous tables to the base data set before producing its final summaries. The tables linked are provided to the library as follows:

master,loanlast,poollast,pub.fin.lpmisc.svccode,suppl,modsupp

Misc inputs

The following groups of inputs are available to the embs_calc block.

loanlevel

Accepts 0 to turn off and 1 to turn on. Default is 1. Determines which tables to use in the calculation. Loan level analysis provides more granularity than pool level analysis, however pool-level analysis has more history.

tablepath
Accepts a valid 1010data table name. The specified table will be used as the base table for the calculation.
inc_always_current
Accepts a 1 to activate. Will be ignored if any other value is provided to the input. When set to true, this input will add an Always Current (AC) column to the roll rate matrix.
inc_payoff_in_always_current
Accepts a 1 to activate. If active, will change the way the Always Current (AC) flag column is calculated. When this flag is not provided, payoff events may be counted as always current. When this input is active, payoffs are not considered to have a status of always current.
ncpr
Accepts a comma-separated list of integers corresponding to the prepayment time horizon (i.e., 1 is a 1 month cpr and 12 is a 12 month cpr). The default if no value is provided is 1 This input is only useable with the following calculations:
  • cpr
ncdr
Accepts a comma-separated list of integers corresponding to the default time horizon (i.e., 1 is a 1 month cdr and 12 is a 12 month cdr). The default if no value is provided is 1. This input is only useable with the following calculations:
  • cdr
suffix
Accepts a text value that is a column name suffix. Appends the text value provided to the column name of all columns created by the library.
notab
Executes the entire query up to the final tabulation. Useful for debugging.
nroll
Accepts an integer value representing the number of months between the start and end of the roll period. This input is only useable with the following calculations:
  • roll_rates
use_whole_group

Accepts 0 or 1. Defaults to 1.

For CMOs in which the collateral is subgrouped (i.e., 1A 4B) use the entire group (i.e., if CMO has groups 1A 1B 1C use all of them) this is necessary since the tranche group mapping in embs does not use subgroups.

subgroup

Accepts a text value (e.g., 1A). If the subgroup is available from another source then use this group.

use_reremics_table

Accepts a 1 to use the use 1010data BETA ReRemic table to determine underlying pools when the tranche is backed by another tranche, and 0 to use the default EMBS collateral table.

Filter inputs

cusips
Accepts a comma-separated list of valid CUSIP values by which to filter the table prior to the final tabulation.

To return results for individual CUSIPs, enter cusip in the break attribute. Otherwise, results will be aggregated across CUSIPs.

start_date and end_date
Both attributes must be used together. Each accepts a month or date. The value of start_date must be less than the value of end_date. Start and End dates are used to filter the output date range. The filter is applied just prior to the final tabulation so that g_functions may be fully evaluated before tabulating.
filter
Accepts a comma-separated list of an arbitrary number of selection statements to be applied before the final tabulation. An example is shown below:
<import path="pub.lib.ucl.v3.lib_embs_calc"/>
<insert block="embs_calc" calcs="cpr,cdr" 
filter="state='NY',prod_type='10',orig_date>20100101"/>
If the selection criteria require the use of a comma then a different delimiter can be used, then specified, by placing a question mark(?) after the final selection criteria and then the new delimiting character in single quotes, as follows:
<import path="pub.lib.ucl.v3.lib_embs_calc"/>
<insert block="embs_calc" calcs="cpr,cdr" 
filter="state='NY':orig_date>20100102:servicer='XYZ,NY'?':'"/>
For Pool level filtering using the ldst_orig table, it is necessary to include ldst in the filter.
filter_post_tab
Accepts a comma-separated list of an arbitrary number of selection statements to be applied before the after tabulation. An example is shown below:
<import path="pub.lib.ucl.v3.lib_embs_calc"/>
<insert block="embs_calc" calcs="cpr,cdr" 
filter_post_tab="state='NY',prod_type='10',orig_date>20100101"/>

If the selection criteria require the use of a comma then a different delimiter can be used, then specified, by placing a question mark(?) after the final selection criteria and then the new delimiting character in single quotes, as follows:<import path="pub.lib.ucl.v3.lib_embs_calc" library=""/> <insert block="embs_calc" calcs="cpr,cdr" filter_post_tab="state='NY':orig_date>20100102:servicer='XYZ,NY'?':'"/>

svcr_pct_threshold

Accepts an integer value between 1 and 100. For pool level servicer/seller based calculations use a pool with a percentage servicer above this value (defaults to 100%).

Linking inputs

hpi
Accepts a space-separated list of values representing the specific indices to be included in the analysis. Mapping tables for all indices can be found in the following folder:
pub.fin.lpmisc
Values and table locations are as follows:
  • zillow - Zillow (Default) - pub.fin.misc_datasets.zillow.monthly_index
  • cws - Case Shiller Weiss - pub.fin.csw.monthly
  • csw_sa Case Shiller Weiss (Seasonally Adjusted)
  • ofheo - FHFA Housing Index - pub.fin.ofheo.mon

Zillow is available to all subscribers. Other data sets are dependent upon subscription. If access to an index is not available this link will fail and provide an error.

Note: The link will be performed at either the state or cbsa level. When working with these data sets, 1010data provides an updated lookup for each index which provides the optimal indices for each loan based on geographic parameters. Using this index, a monthly HPA is computed. The hpi input includes to additional inputs that can only be used when hpi is provided with a value. These additional inputs function as follows:
tier
The tier input is only available when hpi="lp". tier accepts an integer value between 1 and 12. Refer to the CoreLogic data documentation for definitions of tiers for each available value.Note: This refers to the CoreLogic Hom Price Index, not the Core Logic Securities data set used in the lp_calc version of the UCL.
seas_adj
The seas_adj input is only available when hpi="ofheo". Accepts the value 1. When seas_adj="1", the calculation will use seasonably adjusted data.
unemp
Accepts 1 to link in BLS Unemployment table using the following columns: zipcode, cbsa, and state. Path to table: pub....
conforming
Accepts 1 to link in the Conforming Loan Limits table, then creates two computed columns: is_conforming, based on the original balance of the loan, and is_conforming_curr, based on the current balance of the loan. New columns are boolean, and can be used for custom analysis and logic before final tabulation.
n_expand and date_expand
Accepts an integer value (N) value and uses a link and expand operation to create N new rows. date_expand accepts an integer date value (YYYYMMDD). For each loan, the most recent record will be copied M months where M is the number of months between the last remittance date of the loan and the value given to date_expand.
default_logic
Accepts a text value that is a valid 1010data expression. This logical expression is used to determine whether a loan has defaulted. When inputting logic, single quotes (') must be escaped with a backslash(\). If no logic is passed to default_logic, the following logic is automatically used:Note: The code below represents the logic used automatically when default_logic is not given a value. This code does not need to be passed to the default_logic input.
if(currmonliquidationflag=\'Y\' & removalreasoncode= 2 3 4 5
      6;g_tshift({@segby};;{@time};\'M\';{@prefix}rpb{@suffix};-1);0)
link_tables
Accepts a comma-separated list of table names for linking. The tables will be linked in the order they are provided in the list passed to embs_calc.
link_from_cols
Accepts a pipe-separated (|) list of comma-separated lists of column names. I.e., if three foreign tables are specified to be linked from, the following list would link on two columns from each foreign table: link_from_cols="tab1_col1,tab1_col2|tab2_col1,tab2_col2|tab3_col1,tab3_col2".
link_to_cols
Accepts a pipe-separated (|) list of comma-separated lists of column names. I.e., if three foreign tables are specified to be linked to, the following list would link on two columns from each foreign table: link_from_cols="tab1_col1,tab1_col2|tab2_col1,tab2_col2|tab3_col1,tab3_col2".
link_suffixes
Accepts a comma-separated list of suffixes to be added to the new column names of the new worksheet created by the liniing operation.
link_types
Accepts a comma-separated list of valid values for the type attribute of a <link/> operation. See the documentation on <link/> for a list of values.
arm
If arm="1", tells the library to link the ARM loan-level tables.
coupontype
Specify arm for library to link ARM tables and fixed for the library to link Fixed tables.

Summarization inputs

breaks

Accepts a comma-separated list of column names which will be used as the groups for the final tabulation. Defaults to the value specified in the time input if no other breaks are specified. When breaks are provided, value specified in the time input will be appended as an additional break column, unless the none value is provided to the time input.

In the case that you are using the loan distribution tables, see the following section for additional information on how to use breaks.

time
Accepts the name of a column that indicates the time order of the data. The default is fctrdt or effdt depending on whether you are looking at loan level or pool level data respectively. Also accepts loanage, wala, none, or the name of a computed column created by a <willbe> operation. If no time-series data is desired for the final results, pass the text value none to the time input.
sources
Accepts a comma-separated list of column names that will be used as the data to aggregate/summarize by the group or groups specified in the breaks, cbreaks, and time inputs.
weights
Accepts a comma-separated list of column names that will be used to weight the results of the final tabulation. Used as the reference column for tabulation functions that require a weight (i.e., weighted average).
names
Accepts a comma-separated list of column names that will be used as the names of the columns in the final tabulation. The number of column names in the comma-separated list provided to names input must correspond to the number of column names provided to the sources input.Note: This option does not apply to cross-tabulations.
functions
Accepts a comma-separated list of functions that will be used in the final tabulation. The number of functions in the comma-separated list provided to functions must correspond to the number of column names provided to the sources input.
cbreaks
Accepts a comma-separated list of columns names which will be used by the cross-tabulation operation to group columns calculated by the cross-tabulation.
csource
The name of a single column that will be used as the data to aggregate/summarize by the group or groups specified in the cbreaks input.
cweight
Accepts a single value that will be used to weight the results of the cross-tabulation component of the final tabulation.
cfunction
The function that will be applied to the cross-tabulation columns of the final tabulation.
sort

Accepts a comma-separated list of the values up and down. Each break column of the summarization will be sorted in the order corresponding to the values in the list passed to sort.

rollup
Accepts either the value cube for a cubed rollup or a comma-separated list of column names to be used as rollup columns in the tabulations.
tickers
Accepts a comma-separated list of valid Bloomberg GNR 2015-5 A / FNR 2015-55 JA FHR 4311 TD/ G2 MA2960 / FHLG30_6_2003 tickers.

Calculation inputs (<willbe>)

willbes
Accepts a comma-separated list of <willbe> operations that specifies the new names and a value expressions for the values of the computed columns. Columns are created before the final tabulation. The syntax for the expression is:
[NEW_COL_NAME]:[EXPRESSION]
An example is shown below:
<import path="pub.lib.ucl.v3.lib_embs_calc" library=""/>
<insert block="embs_calc" calcs="cpr,cdr" 
willbes="is_mod:"mode_rate<>NA,ever_mod:g_cumhi(loan_nid;;({@time};is_mod))"/>
The delimiter for the list of expressions can be changed in the event an expression in the list needs to use commas, as shown below:
<importpath="pub.lib.ucl.v3.lib_embs_calc"/><insertblock="embs_calc"calcs="cpr,cdr"willbes="limit:100!range:range2(ltv;50,60,90,120)?'!'"/>
willbes_post_tab
Accepts a comma-separated list of <willbe> operations that specifies the new columns' names and a value expression for the value of each computed column. Columns are created after the final tabulation. The syntax for the expression is:
[NEW_COL_NAME]:[EXPRESSION]
An example is shown below:
<import path="pub.lib.ucl.v3.lib_embs_calc"/>
<insert block="embs_calc" calcs="cpr,cdr" 
willbes_post_tab="is_mod:
"mode_rate<>NA,ever_mod:g_cumhi(loan_nid;;({@time};is_mod))"/>
The delimiter for the list of expressions can be changed in the event an expression needs in the list needs to use commas, as shown below:
<importpath="pub.lib.ucl.v3.lib_embs_calc"/><insertblock="embs_calc"calcs="cpr,cdr"willbes_post_tab="limit:100!range:range2(ltv;50,60,90,120)?'!'"/>

Spread calculations

spread_range
Accepts a comma-separated list that defines value ranges that will be used by therange2ffunction. The resultant column contains a Range for the current spread to PMMS rates. The library will calculate a spread for each loan to the regional PMMS rate. It then assigns this spread to one of the range buckets provided to spread_range.
spread_range_inc
Accepts a comma-separated list that defines value ranges that will be used by the range1f function. Accepts a numeric value. This value specifies a fixed increment to be used for the current spread range.
sato_range
Accepts a comma-separated list that defines value ranges that will be used by the range2f function. The resultant column contains a range for the spread to PMMS rates at origination. The library will calculate a spread for each loan to the regional PMMS rate. It then assigns this spread to one of the range buckets provided to satp_range.
sato_range_inc
Accepts a numeric value. This value specifies a fixed increment to be used for the current spread range.
sato_lookback
Accepts an integer specifying the number of months to look back from the origination date into PMMS Rates table. The default value is 2.