R/Wrangler.R
In alejandro-sotolongo/InvTools:
#' @export
Wrangler <- R6::R6Class(
classname = 'Wrangler',
public = list(
acct_store = NULL,
write_fail_csv = TRUE,
initialize = function(
acct_store = NULL,
write_fail_csv = TRUE,
init_wrangle = TRUE) {
if (is.null(acct_store)) {
acct_store <- AcctStore$new()
}
self$acct_store <- acct_store
self$write_fail_csv <- write_fail_csv
if (init_wrangle) {
self$addFoMgp()
self$addCmeClass()
self$addExposureValue()
self$admiAdjust()
}
},
addFoMgp = function() {
# add front office MGP
balance_sheet <- self$acct_store$balance_sheet
port_code <- self$acct_store$port_code_dict %>%
select(`Portfolio Code`, `FO MGP`)
balance_sheet <- balance_sheet %>%
left_join(port_code, by = 'Portfolio Code')
self$acct_store$balance_sheet <- balance_sheet
if (self$write_fail_csv) {
port_code_in_dict <- balance_sheet$`Portfolio Code` %in% port_code$`Portfolio Code`
write.csv(
x = balance_sheet[!port_code_in_dict, ],
file = paste0(
'X:/Client-Security-Data/Dictionaries/Account-Fail/',
format(Sys.time(), '%Y-%m-%d-%h-%m-%s'),
'.csv'
)
)
}
},
addCmeClass = function() {
# add front office CME Index, exposure scaler, and asset classification
balance_sheet <- self$acct_store$balance_sheet
ticker_dict <- self$acct_store$ticker_dict %>%
select(Ticker, `CME ID`, `Scale Exposure`)
balance_sheet <- balance_sheet %>%
rename(`Advent Asset Class` = `Asset Class`) %>%
left_join(ticker_dict, by = 'Ticker') %>%
left_join(self$acct_store$asset_class, by = 'CME ID')
self$acct_store$balance_sheet <- balance_sheet
if (self$write_fail_csv) {
ticker_in_dict <- balance_sheet$Ticker %in% ticker_dict$Ticker
write.csv(
x = balance_sheet[!ticker_in_dict, ],
file = paste0(
'X:/Client-Security-Data/Dictionaries/Tick-Fail/',
format(Sys.time(), '%Y-%m-%d-%h-%m-%s'),
'.csv'
)
)
}
},
addExposureValue = function() {
# add exposure value = scale expsoure * market value
if (!'Scale Exposure' %in% colnames(self$acct_store$balance_sheet)) {
warning('Scale Expsoure not found. Running addCmeClass.')
self$addCmeClass()
}
self$acct_store$balance_sheet <-
self$acct_store$balance_sheet %>%
tidyr::replace_na(list(`Scale Exposure` = 1)) %>%
mutate(`Exposure Value` = `Market Value` * `Scale Exposure`)
},
admiAdjust = function() {
# adjusts admi synthetic offset for p/l
if (!'Exposure Value' %in% colnames(self$acct_store$balance_sheet)) {
warning('Exposure Value not found. Running addExposureValue.')
self$addExposureValue()
}
# search for admi accounts in Portfolio Name 3 by 'ADMI'
admi <- self$acct_store$balance_sheet %>%
slice(grep('ADMI', `Portfolio Name 3`, ignore.case = TRUE))
print('ADMI Accounts Found:')
print(admi[, 'Portfolio Name 3'] %>% unique())
acct_vec <- admi$`Portfolio Name 3` %>% unique()
# loop through each admi account and adjust leverage for p/l
for (i_acct in 1:length(acct_vec)) {
i_admi <- admi %>% filter(`Portfolio Name 3` == acct_vec[i_acct])
i_admi_cash <- i_admi %>% filter(`CME ID` == 'CASH / (MARGIN)') %>%
.$`Market Value` %>%
sum(na.rm = TRUE)
# find synthetic offset, use value in p/l calc and replace
# exposure value with market value adjusted for p/l
is_synth <- i_admi$`CME ID` == 'SYNTHETIC OFFSET'
is_synth[is.na(is_synth)] <- FALSE
synth <- i_admi$`Market Value`[is_synth] %>%
sum(na.rm = TRUE)
# find currency hedge, use value in p/l calc and replace exposure
# value to 0
is_ch <- i_admi$`CME ID` == 'CURRENCY HEDGE'
is_ch[is.na(is_ch)] <- FALSE
ch <- i_admi$`Market Value`[is_ch] %>%
sum(na.rm = TRUE)
i_admi[is_ch, 'Exposure Value'] <- 0
i_pnl <- i_admi$`Market Value` %>% sum(na.rm = TRUE) - i_admi_cash
admi_lev <- i_pnl + synth + ch
i_admi[is_synth, 'Exposure Value'] <- admi_lev
# put adjusted admi values back into balance_sheet
replace_admi <- self$acct_store$balance_sheet$`Unique ID` %in%
i_admi$`Unique ID`
self$acct_store$balance_sheet[replace_admi, ] <- i_admi
}
},
getFrontier = function(mgp_name) {
# return frontier for given mgp_name
front_name <- self$acct_store$mgp_dict %>%
filter(MGP == mgp_name) %>%
.$`Frontier File`
if (length(front_name) == 0) {
stop(paste0(mgp_name, ' not found in mgp to frontier dictionary'))
}
if (length(front_name) > 1) {
stop(paste0('multiple matches of ', mgp_name, ' found in mgp to
frontier dictionary'))
}
if (!file.exists(front_name)) {
stop(paste0(front_name, ' not found'))
}
front <- readxl::read_excel(front_name) %>%
mutate(`CME ID` = standardCmeId(`CME ID`))
colnames(front) <- tolower(colnames(front))
return(front)
},
getClient = function(mgp_name, calc_weight = TRUE) {
# get balance sheet for given mgp_name
# calc_weight = boolean to calculate capital weight based on given
# subset
client <- self$acct_store$balance_sheet %>%
filter(`FO MGP` == mgp_name)
if (calc_weight) {
client <- client %>%
mutate(`Exposure Weight` = `Exposure Value` /
sum(`Exposure Value`, na.rm = TRUE))
}
return(client)
},
mergeClientFrontier = function(mgp_name, vol_target) {
# merge client cme index weights and frontier
# vol_target = d4, d3, d2, d1, mgp, u1, u2, u3, or u4
vol_target <- self$checkVolTarget(vol_target)
client <- self$getClient(mgp_name)
front <- self$getFrontier(mgp_name) %>%
select(`cme id`, !! vol_target, mgp)
colnames(front) <- c('CME ID', 'Tactical', 'MGP')
client <- client %>%
group_by(`CME ID`) %>%
summarize_at(c('Exposure Value', 'Exposure Weight'), sum) %>%
full_join(front, by = 'CME ID') %>%
left_join(self$acct_store$asset_class, by = 'CME ID') %>%
arrange(`Sub-Asset Class`)
client <- self$handleSynth(client)
# format data frame and add a total row
total_row <- tibble(
`Asset Class` = '',
`Sub-Asset Class` = 'Total',
`Exposure Value` = formatC(
sum(client$`Exposure Value`, na.rm = TRUE),
digits = 0,
big.mark = ',',
format = 'f'
),
`Exposure Weight` = fPercent(
sum(client$`Exposure Weight`, na.rm = TRUE)
),
Tactical = fPercent(sum(client$Tactical, na.rm = TRUE)),
MGP = fPercent(sum(client$`MGP`, na.rm = TRUE))
)
client_fmt <- client %>%
replace(is.na(.), 0) %>%
mutate(
`Exposure Value` = formatC(
`Exposure Value`,
digits = 0,
big.mark = ',',
format = 'f'
),
`Exposure Weight` = fPercent(`Exposure Weight`),
`Tactical` = fPercent(`Tactical`),
MGP = fPercent(MGP)
) %>%
select(`Asset Class`, `Sub-Asset Class`,
`Exposure Value`, `Exposure Weight`, Tactical, MGP) %>%
bind_rows(total_row)
# return numeric and formated data frames
res <- list()
res$client <- client
res$client_fmt <- client_fmt
return(res)
},
handleSynth = function(client) {
is_synth <- client$`CME ID` == 'SYNTHETIC OFFSET'
is_synth[is.na(is_synth)] <- FALSE
if (sum(is_synth) == 0) {
return(client)
}
is_cash <- client$`CME ID` == 'CASH / (MARGIN)'
is_cash[is.na(is_cash)] <- FALSE
key_col <- c(
'Exposure Value',
'Exposure Weight',
'Tactical',
'MGP'
)
for(i_col in 1:length(key_col)) {
client[is.na(client[, key_col[i_col]]), key_col[i_col]] <- 0
}
client[is_cash, key_col] <- client[is_cash, key_col] +
client[is_synth, key_col]
client <- client[!is_synth, ]
return(client)
},
checkVolTarget = function(vol_target) {
vol_target <- tolower(vol_target)
if (!vol_target %in% c(paste0('d', 1:4), paste0('u', 1:4), 'mgp')) {
stop('vol_target not properly specified, must be one of: d4, d3, d2, d1,
mgp, u1, u2, u3, or u4.')
}
return(vol_target)
}
)
)
alejandro-sotolongo/InvTools documentation built on Nov. 1, 2019, 9:08 p.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
#' @export
Wrangler <- R6::R6Class(
classname = 'Wrangler',
public = list(
acct_store = NULL,
write_fail_csv = TRUE,
initialize = function(
acct_store = NULL,
write_fail_csv = TRUE,
init_wrangle = TRUE) {
if (is.null(acct_store)) {
acct_store <- AcctStore$new()
}
self$acct_store <- acct_store
self$write_fail_csv <- write_fail_csv
if (init_wrangle) {
self$addFoMgp()
self$addCmeClass()
self$addExposureValue()
self$admiAdjust()
}
},
addFoMgp = function() {
# add front office MGP
balance_sheet <- self$acct_store$balance_sheet
port_code <- self$acct_store$port_code_dict %>%
select(`Portfolio Code`, `FO MGP`)
balance_sheet <- balance_sheet %>%
left_join(port_code, by = 'Portfolio Code')
self$acct_store$balance_sheet <- balance_sheet
if (self$write_fail_csv) {
port_code_in_dict <- balance_sheet$`Portfolio Code` %in% port_code$`Portfolio Code`
write.csv(
x = balance_sheet[!port_code_in_dict, ],
file = paste0(
'X:/Client-Security-Data/Dictionaries/Account-Fail/',
format(Sys.time(), '%Y-%m-%d-%h-%m-%s'),
'.csv'
)
)
}
},
addCmeClass = function() {
# add front office CME Index, exposure scaler, and asset classification
balance_sheet <- self$acct_store$balance_sheet
ticker_dict <- self$acct_store$ticker_dict %>%
select(Ticker, `CME ID`, `Scale Exposure`)
balance_sheet <- balance_sheet %>%
rename(`Advent Asset Class` = `Asset Class`) %>%
left_join(ticker_dict, by = 'Ticker') %>%
left_join(self$acct_store$asset_class, by = 'CME ID')
self$acct_store$balance_sheet <- balance_sheet
if (self$write_fail_csv) {
ticker_in_dict <- balance_sheet$Ticker %in% ticker_dict$Ticker
write.csv(
x = balance_sheet[!ticker_in_dict, ],
file = paste0(
'X:/Client-Security-Data/Dictionaries/Tick-Fail/',
format(Sys.time(), '%Y-%m-%d-%h-%m-%s'),
'.csv'
)
)
}
},
addExposureValue = function() {
# add exposure value = scale expsoure * market value
if (!'Scale Exposure' %in% colnames(self$acct_store$balance_sheet)) {
warning('Scale Expsoure not found. Running addCmeClass.')
self$addCmeClass()
}
self$acct_store$balance_sheet <-
self$acct_store$balance_sheet %>%
tidyr::replace_na(list(`Scale Exposure` = 1)) %>%
mutate(`Exposure Value` = `Market Value` * `Scale Exposure`)
},
admiAdjust = function() {
# adjusts admi synthetic offset for p/l
if (!'Exposure Value' %in% colnames(self$acct_store$balance_sheet)) {
warning('Exposure Value not found. Running addExposureValue.')
self$addExposureValue()
}
# search for admi accounts in Portfolio Name 3 by 'ADMI'
admi <- self$acct_store$balance_sheet %>%
slice(grep('ADMI', `Portfolio Name 3`, ignore.case = TRUE))
print('ADMI Accounts Found:')
print(admi[, 'Portfolio Name 3'] %>% unique())
acct_vec <- admi$`Portfolio Name 3` %>% unique()
# loop through each admi account and adjust leverage for p/l
for (i_acct in 1:length(acct_vec)) {
i_admi <- admi %>% filter(`Portfolio Name 3` == acct_vec[i_acct])
i_admi_cash <- i_admi %>% filter(`CME ID` == 'CASH / (MARGIN)') %>%
.$`Market Value` %>%
sum(na.rm = TRUE)
# find synthetic offset, use value in p/l calc and replace
# exposure value with market value adjusted for p/l
is_synth <- i_admi$`CME ID` == 'SYNTHETIC OFFSET'
is_synth[is.na(is_synth)] <- FALSE
synth <- i_admi$`Market Value`[is_synth] %>%
sum(na.rm = TRUE)
# find currency hedge, use value in p/l calc and replace exposure
# value to 0
is_ch <- i_admi$`CME ID` == 'CURRENCY HEDGE'
is_ch[is.na(is_ch)] <- FALSE
ch <- i_admi$`Market Value`[is_ch] %>%
sum(na.rm = TRUE)
i_admi[is_ch, 'Exposure Value'] <- 0
i_pnl <- i_admi$`Market Value` %>% sum(na.rm = TRUE) - i_admi_cash
admi_lev <- i_pnl + synth + ch
i_admi[is_synth, 'Exposure Value'] <- admi_lev
# put adjusted admi values back into balance_sheet
replace_admi <- self$acct_store$balance_sheet$`Unique ID` %in%
i_admi$`Unique ID`
self$acct_store$balance_sheet[replace_admi, ] <- i_admi
}
},
getFrontier = function(mgp_name) {
# return frontier for given mgp_name
front_name <- self$acct_store$mgp_dict %>%
filter(MGP == mgp_name) %>%
.$`Frontier File`
if (length(front_name) == 0) {
stop(paste0(mgp_name, ' not found in mgp to frontier dictionary'))
}
if (length(front_name) > 1) {
stop(paste0('multiple matches of ', mgp_name, ' found in mgp to
frontier dictionary'))
}
if (!file.exists(front_name)) {
stop(paste0(front_name, ' not found'))
}
front <- readxl::read_excel(front_name) %>%
mutate(`CME ID` = standardCmeId(`CME ID`))
colnames(front) <- tolower(colnames(front))
return(front)
},
getClient = function(mgp_name, calc_weight = TRUE) {
# get balance sheet for given mgp_name
# calc_weight = boolean to calculate capital weight based on given
# subset
client <- self$acct_store$balance_sheet %>%
filter(`FO MGP` == mgp_name)
if (calc_weight) {
client <- client %>%
mutate(`Exposure Weight` = `Exposure Value` /
sum(`Exposure Value`, na.rm = TRUE))
}
return(client)
},
mergeClientFrontier = function(mgp_name, vol_target) {
# merge client cme index weights and frontier
# vol_target = d4, d3, d2, d1, mgp, u1, u2, u3, or u4
vol_target <- self$checkVolTarget(vol_target)
client <- self$getClient(mgp_name)
front <- self$getFrontier(mgp_name) %>%
select(`cme id`, !! vol_target, mgp)
colnames(front) <- c('CME ID', 'Tactical', 'MGP')
client <- client %>%
group_by(`CME ID`) %>%
summarize_at(c('Exposure Value', 'Exposure Weight'), sum) %>%
full_join(front, by = 'CME ID') %>%
left_join(self$acct_store$asset_class, by = 'CME ID') %>%
arrange(`Sub-Asset Class`)
client <- self$handleSynth(client)
# format data frame and add a total row
total_row <- tibble(
`Asset Class` = '',
`Sub-Asset Class` = 'Total',
`Exposure Value` = formatC(
sum(client$`Exposure Value`, na.rm = TRUE),
digits = 0,
big.mark = ',',
format = 'f'
),
`Exposure Weight` = fPercent(
sum(client$`Exposure Weight`, na.rm = TRUE)
),
Tactical = fPercent(sum(client$Tactical, na.rm = TRUE)),
MGP = fPercent(sum(client$`MGP`, na.rm = TRUE))
)
client_fmt <- client %>%
replace(is.na(.), 0) %>%
mutate(
`Exposure Value` = formatC(
`Exposure Value`,
digits = 0,
big.mark = ',',
format = 'f'
),
`Exposure Weight` = fPercent(`Exposure Weight`),
`Tactical` = fPercent(`Tactical`),
MGP = fPercent(MGP)
) %>%
select(`Asset Class`, `Sub-Asset Class`,
`Exposure Value`, `Exposure Weight`, Tactical, MGP) %>%
bind_rows(total_row)
# return numeric and formated data frames
res <- list()
res$client <- client
res$client_fmt <- client_fmt
return(res)
},
handleSynth = function(client) {
is_synth <- client$`CME ID` == 'SYNTHETIC OFFSET'
is_synth[is.na(is_synth)] <- FALSE
if (sum(is_synth) == 0) {
return(client)
}
is_cash <- client$`CME ID` == 'CASH / (MARGIN)'
is_cash[is.na(is_cash)] <- FALSE
key_col <- c(
'Exposure Value',
'Exposure Weight',
'Tactical',
'MGP'
)
for(i_col in 1:length(key_col)) {
client[is.na(client[, key_col[i_col]]), key_col[i_col]] <- 0
}
client[is_cash, key_col] <- client[is_cash, key_col] +
client[is_synth, key_col]
client <- client[!is_synth, ]
return(client)
},
checkVolTarget = function(vol_target) {
vol_target <- tolower(vol_target)
if (!vol_target %in% c(paste0('d', 1:4), paste0('u', 1:4), 'mgp')) {
stop('vol_target not properly specified, must be one of: d4, d3, d2, d1,
mgp, u1, u2, u3, or u4.')
}
return(vol_target)
}
)
)
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
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.