canarionyc/loanroll
Routines for Parsing and Doing Analysis on Loan Data Files from Fannie Mae

Package index
Search the canarionyc/loanroll package
Functions
22
Source code
19
Man pages
0
Home
/
GitHub
/
canarionyc/loanroll
/
R/Last_P.R

R/Last_P.R
In canarionyc/loanroll: Routines for Parsing and Doing Analysis on Loan Data Files from Fannie Mae

Defines functions run_net_loss run_analytics.Last_P make_first_events.Data_P 

if(getRversion() >= "2.15.1") {
  utils::globalVariables(c('i.Monthly.Rpt.Prd','i.last_upb','prev_last_rt', 'prev_Maturity.Date',
                           'D150_DTE', 'D150_UPB', 'D180CE_DTE', 'D180CE_UPB', 'D180_DTE', 'D180_UPB', 'D60_DTE',
                           'D60_UPB', 'D90_DTE', 'D90_UPB', 'FMOD_DTE', 'FMOD_UPB'))
}

#' @importFrom utils count.fields str unzip
#' @importFrom stats na.omit weighted.mean
make_first_events.Data_P <- function(Data_P,  verbose=FALSE) {
  #  by <- key(Data_P)[1]
str(Data_P)
  setkeyv(Data_P, c("LOAN_ID","Monthly.Rpt.Prd"))
  # print(key(Data_P))

  # Data_P[, Count:=1:.N, by="LOAN_ID"]
  # Data_P[, last_upb:=ifelse(Count<=7 & is.na(last_upb), ORIG_AMT, last_upb)]
  # Data_P[, prev_upb:=shift(last_upb), by="LOAN_ID"]
  # browser()
  Last_P <- Data_P[, .SD[.N], by="LOAN_ID"]
  str(Last_P)


  Data_P[, c('prev_upb','prev_last_rt','prev_Maturity.Date') :=list(
    shift(last_upb),
    shift(LAST_RT),
    shift(Maturity.Date)),
    by="LOAN_ID"]

  Last_P[,
    "LAST_DTE":= as.IDate(ifelse(!is.na(DISP_DT), DISP_DT,
      ifelse(!is.na(ZB_DTE), ZB_DTE, Monthly.Rpt.Prd)))]



  # First Mod ------------------------------------------------------------------------
  First_MOD <- Data_P[MOD_FLAG =="Y",
    .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_MOD, c("FMOD_DTE","FMOD_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  #  Rate Mod ------------------------------------------------------------------------


  ModRT <- Data_P[LAST_RT!=prev_last_rt,
    c("LOAN_ID", "Monthly.Rpt.Prd","last_upb","prev_last_rt", "LAST_RT")]
  if(verbose) { print(ModRT) }

  ModRT1 <- ModRT[, .SD[1],  by="LOAN_ID"]
  Last_P[ModRT1, c("ModRT1_DTE","ModRT1_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  ModRT2 <- na.omit(ModRT[, .SD[2],  by="LOAN_ID"])
  if(nrow(ModRT2)>0) {
    if(verbose) print(ModRT2)
    Last_P[ModRT2, c("ModRT2_DTE","ModRT2_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]
  }

  ModRT3 <- na.omit(ModRT[, .SD[3],  by="LOAN_ID"])
  if(nrow(ModRT3)>0) {
    if(verbose) print(ModRT3)
    Last_P[ModRT3, c("ModRT3_DTE","ModRT3_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]
  }
  #  Maturity Date Mod ------------------------------------------------------------------------
  if( "Maturity.Date" %in% names(Data_P)) {


    ModMatDt <- Data_P[Maturity.Date!=prev_Maturity.Date,
      c("LOAN_ID", "Monthly.Rpt.Prd","last_upb","prev_Maturity.Date", "Maturity.Date")]
    if(verbose) print(ModMatDt)

    ModMatDt1 <- ModMatDt[, .SD[1],  by="LOAN_ID"]
    if(nrow(ModMatDt1)>0) {
      if(verbose) print(ModMatDt1)
      Last_P[ModMatDt1, c("ModMatDt1_DTE","ModMatDt1_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

      ModMatDt2 <- na.omit(ModMatDt[, .SD[2],  by="LOAN_ID"])
      if(nrow(ModMatDt2)>0) {
        if(verbose) print(ModMatDt2)
        Last_P[ModMatDt2, c("ModMatDt2_DTE","ModMatDt2_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

        ModMatDt3 <- na.omit(ModMatDt[, .SD[3],  by="LOAN_ID"])
        if(nrow(ModMatDt3)>0) {
          if(verbose) print(ModMatDt3)
          Last_P[ModMatDt3, c("ModMatDt3_DTE","ModMatDt3_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]
        }
      }
    }
  }

  # MOD_Bal ----
  # ModBal <- Data_P[, .SD[last_upb>prev_upb],
  #   .SDcols=c( "Monthly.Rpt.Prd","prev_upb",  "last_upb"), by="LOAN_ID"]


  ModBal <- Data_P[last_upb>prev_upb*1.005, .SD,
    .SDcols=c("LOAN_ID", "Monthly.Rpt.Prd","prev_upb",  "last_upb")]
  if(verbose) print(ModBal)
  ModBal1 <- ModBal[, .SD[1],  by="LOAN_ID"]
  if(nrow(ModBal1)>0) {
    if(verbose)print(ModBal1)
    Last_P[ModBal1, c("ModBal1_DTE","ModBal1_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

    ModBal2 <- na.omit(ModBal[, .SD[2],  by="LOAN_ID"])
    if(nrow(ModBal2)>0) {
      if(verbose)print(ModBal2)
      Last_P[ModBal2, c("ModBal2_DTE","ModBal2_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

      ModBal3 <- na.omit(ModBal[, .SD[3],  by="LOAN_ID"])
      if(nrow(ModBal3)>0) {
        if(verbose)print(ModBal3)
        Last_P[ModBal3, c("ModBal3_DTE","ModBal3_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]
      }
    }
  }

  # D30  ------------------------------------------------------------------------
  First_D30 <- Data_P[Delq.Status>= 1, .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_D30, c("D30_DTE","D30_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  # D60  ------------------------------------------------------------------------
  First_D60 <- Data_P[Delq.Status>= 2, .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_D60, c("D60_DTE","D60_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  # D90  ------------------------------------------------------------------------
  First_D90 <- Data_P[Delq.Status>= 3, .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_D90, c("D90_DTE","D90_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  # D120  ------------------------------------------------------------------------
  First_D120 <- Data_P[Delq.Status>= 4, .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_D120, c("D120_DTE","D120_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]


  # D150  ------------------------------------------------------------------------
  First_D150 <- Data_P[Delq.Status>= 5, .SD[1], .SDcols =c("Monthly.Rpt.Prd","last_upb"), by="LOAN_ID"]
  Last_P[First_D150, c("D150_DTE","D150_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  # D180  -----------------------------------------------------------------------
  First_D180 <- Data_P[Delq.Status>= 6, .SD[1],
    .SDcols =c("Monthly.Rpt.Prd","last_upb"),  by="LOAN_ID"]
  Last_P[First_D180, c("D180_DTE","D180_UPB"):=list(i.Monthly.Rpt.Prd,i.last_upb)]

  # Data_P[SERVICING_ACTIVITY_INDICATOR=="Y"]

  #  Last_P[, Monthly.Rpt.Prd:=NULL]
  rm(list = ls(pattern = "^(Mod|First)"))
  return(Last_P)
}

# Definitions ----
CreditEvents <- c('02', '03', '09', '15') # '06'?

if(getRversion() >= "2.15.1") {
  utils::globalVariables(c('lpi2disp',"INT_COST",'NET_LOSS', 'Tot_Liq_Ex',
                           'Tot_Procs', 'Total_Cost' ))
}

run_analytics.Last_P <- function(Last_P, verbose=FALSE) {

  setDT(Last_P, key="LOAN_ID")
  if( !"OrigYr"%in% names(Last_P) ) {
    Last_P[, "OrigYr":=factor(year(ORIG_DTE))]
  }

  # ---- lpi2disp,zb2disp ------------------------------------------------------------------------
  Last_P[ Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    c("lpi2disp", "zb2disp") := list(
      as.integer(ifelse(!is.na(LPI_DTE) ,
        (year(DISP_DT)-year(LPI_DTE))*12+month(DISP_DT)-month(LPI_DTE), NA_integer_)),
      as.integer(ifelse( !is.na(ZB_DTE) ,
        (year(DISP_DT)-year(ZB_DTE))*12+month(DISP_DT)-month(ZB_DTE), NA_integer_))
    )]
  if(verbose){
    print(summary(Last_P[,c("lpi2disp", "zb2disp")]))
    cat("Minimum lpi2disp:\n")
    print(Last_P[lpi2disp==min(lpi2disp, na.rm = TRUE)])
  }
  # INT_COST -----------------------------------------------------------------------------------
  Last_P[ Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    "INT_COST" := pmax(
      #      Fin_UPB *
      last_upb *
        (((LAST_RT/100) - .0035)/12)*lpi2disp, 0)
    ]
  if(verbose){
    cat("Interest Cost:\n")
    print(summary(Last_P$INT_COST))
  }

  Last_P[Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    "Tot_Liq_Ex":=rowSums(.SD, na.rm = TRUE), .SDcols=c("FCC_COST","PP_COST","AR_COST","TAX_COST","IE_COST")
    ]

  Last_P[Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    "Tot_Procs":=-1*rowSums(.SD, na.rm = TRUE), .SDcols=c("NS_PROCS","CE_PROCS","RMW_PROCS","O_PROCS")]

  Last_P[Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    "Total_Cost":=rowSums(.SD, na.rm = TRUE), .SDcols=c("last_upb","INT_COST","Tot_Liq_Ex")]

  Last_P[Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT),
    "NET_LOSS":=rowSums(.SD, na.rm = TRUE), .SDcols=c("Total_Cost","Tot_Procs")]
  #  Last_P[, last_upb:=NULL]
  return(Last_P)
}

run_net_loss <- function(Last_P) {


}

# summary.Last_P <- function(Last_P, Data_A=NULL, xvar='ORIG_DTE', by.vars=NULL) {
#
#   if(xvar=="") xvar <- "OrigYr"
#
#   Last_P.1 <- Last_P[, .SD, .SDcols=!c('ORIG_RT','ORIG_AMT','ORIG_DTE')]
#   Combined_Data <- Last_P.1[Data_A, nomatch=NULL]
#
#
#   out <- Combined_Data[, j=.(
#     'LCount'=.N,
#
#     'Orig_Amt.M'=sum(ORIG_AMT, na.rm = TRUE)/1e6,
#
#     "Active.LCount"=sum(Zero.Bal.Code=="", na.rm = TRUE),
#     "Active.UPB.M"=sum(fifelse(Zero.Bal.Code=="", last_upb,0,0)/1e6),
#
#     # Modifications
#     'Modified.LCount'=sum(fifelse(MOD_FLAG=="Y",1L,0L,0L)),
#     'Active_Modified.LCount'=sum(fifelse(Zero.Bal.Code=="" & MOD_FLAG=="Y",1L,0L,0L)),
#     #  'New_Modifications.LCount'=sum(!is.na(FMOD_DTE)&Monthly.Rpt.Prd==FMOD_DTE),
#     #  'Cum_MOD.LCount'=sum( ifelse(Monthly.Rpt.Prd>=FMOD_DTE, 1,0), na.rm = TRUE),
#     # 'Cum_ModRT1.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT1_DTE,1,0), na.rm = TRUE),
#     # 'Cum_ModRT2.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT2_DTE,1,0), na.rm = TRUE),
#
#     # Cum_Dq ----
#     'D60.M'=sum(fifelse(!is.na(D60_UPB), yes=D60_UPB, no=0, na=0))/1e6,
#     'D90.M'=sum(fifelse(!is.na(D90_UPB), yes=D90_UPB, no=0, na=0))/1e6,
#
#     # D180 or Credit Event
#     'D180CE.M'=sum(fifelse(Zero.Bal.Code %in% c('02','03','09','15'), yes=last_upb,
#       no=fifelse(is.na(D180_DTE), yes=0, no=D180_UPB, na=0),na=0))/1e6,
#
#     # Voluntary Prepayments
#     'Paidoff.LCount'=sum(fifelse(Zero.Bal.Code =='01', 1L,0L,0L)),
#     'Paidoff.UPB.M'=sum(fifelse(Zero.Bal.Code =='01', last_upb,0,0))/1e6,
#     'Repurchased.LCount'=sum(fifelse(Zero.Bal.Code =='06', 1L,0L,0L)),
#     'Repurchased.UPB.M'=sum(fifelse(Zero.Bal.Code =='06', last_upb,0,0))/1e6,
#     'RPL.LCount'=sum(fifelse(Zero.Bal.Code == '16', 1L,0L,0L)),
#     'RPL.UPB.M'=sum(fifelse(Zero.Bal.Code == '16', last_upb,0,0))/1e6,
#
#     # Involuntary Prepayments
#     'Third_Party_Sale.LCount'=sum(fifelse(Zero.Bal.Code=='02' , 1L,0L,0L)),
#     'Third_Party_Sale.UPB.M'=sum(fifelse(Zero.Bal.Code=='02', last_upb,0,0))/1e6,
#     'Short_Sale.LCount' =sum(fifelse(Zero.Bal.Code=='03', 1L,0L,0L)),
#     'Short_Sale.UPB.M' =sum(fifelse(Zero.Bal.Code=='03', last_upb,0,0))/1e6,
#     'REO.LCount'=sum(fifelse(Zero.Bal.Code=='09', 1L,0L,0L)),
#     'REO.UPB.M'=sum(fifelse(Zero.Bal.Code=='09', last_upb,0,0))/1e6,
#     'NPL.LCount'=sum(fifelse(Zero.Bal.Code=='15', 1L,0L,0L)),
#     'NPL.UPB.M'=sum(fifelse(Zero.Bal.Code=='15', last_upb,0,0))/1e6
#
#   ), keyby=c(xvar,by.vars)]
#
#
#   out[, 'Default.M':=rowSums(.SD), .SDcols=c(
#     'Third_Party_Sale.UPB.M','Short_Sale.UPB.M',
#     'REO.UPB.M','NPL.UPB.M')]
#   # if(!is.null(xvar)) { setorderv(out, c(xvar), order = 1L, na.last = FALSE) }
#
#   if(!is.null(xvar) && xvar !=  "OrigYr") {
#     cum_cols <- c('LCount',
#       "Orig_Amt.M",
#       'D60.M','D90.M','D180CE.M',
#       'Paidoff.LCount','Paidoff.UPB.M',
#       'Repurchased.LCount','Repurchased.UPB.M',
#       'RPL.LCount','RPL.UPB.M',
#       'Third_Party_Sale.LCount','Third_Party_Sale.UPB.M',
#       'Short_Sale.LCount','Short_Sale.UPB.M',
#       'REO.LCount','REO.UPB.M',
#       'NPL.LCount','NPL.UPB.M','Default.M')
#     new_cols <- paste0("Cum_", cum_cols)
#     if(!is.null(by.vars)) {
#       out[!is.na(get(xvar)), (new_cols):=lapply(.SD, cumsum), .SDcols=cum_cols, by=c(by.vars)]
#     } else {
#       out[!is.na(get(xvar)), (new_cols):=lapply(.SD[], cumsum), .SDcols=cum_cols]
#     }
#     out[, c('Cum_D60.Rate','Cum_D90.Rate'):=list(
#       Cum_D60.M/Cum_Orig_Amt.M,
#       Cum_D90.M/Cum_Orig_Amt.M
#     )]
#     out[, c('Cum_D180CE.Rate',
#       'Cum_Paidoff.UPB.Rate',
#       'Cum_Repurchased.UPB.Rate',
#       'Cum_RPL.UPB.Rate',
#       'Cum_Third_Party_Sale.UPB.Rate',
#       'Cum_Short_Sale.UPB.Rate',
#       'Cum_REO.UPB.Rate',
#       'Cum_NPL.UPB.Rate',
#       'Cum_Default.Rate'
#     ):=list(
#       Cum_D180CE.M/Cum_Orig_Amt.M,
#       Cum_Paidoff.UPB.M/Cum_Orig_Amt.M,
#       Cum_Repurchased.UPB.M/Cum_Orig_Amt.M,
#       Cum_RPL.UPB.M/Cum_Orig_Amt.M,
#       Cum_Third_Party_Sale.UPB.M/Cum_Orig_Amt.M,
#       Cum_Short_Sale.UPB.M/Cum_Orig_Amt.M,
#       Cum_REO.UPB.M/Cum_Orig_Amt.M,
#       Cum_NPL.UPB.M/Cum_Orig_Amt.M,
#       Cum_Default.M/Cum_Orig_Amt.M
#     )]
#   }
#   out[, c('Factor'):=list(
#     Active.UPB.M/Orig_Amt.M
#   )]
#   out[, c('D180CE.Rate'):=list(D180CE.M/Orig_Amt.M)]
#
#   pct_cols <- c('Third_Party_Sale.UPB.M','Short_Sale.UPB.M','REO.UPB.M','NPL.UPB.M')
#   new_cols <- sub(".M",".Rate", pct_cols)
#   out[, (new_cols):=lapply(.SD,function(x) x/Orig_Amt.M),  .SDcols=pct_cols]
#
#   out[, 'MDR':=Default.M/Orig_Amt.M]
#
#   if(!is.null(xvar)) {
#     #    out <- out[!is.na(get(xvar))]
#
#     if('IDate' %in% class(out[[xvar]])) {
#       cols <- xvar
#       out[, (cols):=lapply(.SD,  as.Date), .SDcols=cols]
#     }
#   }
#   if(verbose) {
#
#     str(out)
#     print(out)
#   }
#   return(out)
# }
canarionyc/loanroll documentation built on Sept. 7, 2020, 4:50 a.m.

R Package Documentation

rdrr.io home R language documentation Run R code online

Browse R Packages

CRAN packages Bioconductor packages R-Forge packages GitHub packages

We want your feedback!

Note that we can't provide technical support on individual packages. You should contact the package authors for that.
GitHub issue tracker
ian@mutexlabs.com
Personal blog

 
Embedding an R snippet on your website

Add the following code to your website.

For more information on customizing the embed code, read Embedding Snippets.

Close