winDialog(type="ok","Load the Activity Summary File from the NJ Server.")
AccSummaryNJDC <- choose.files(caption = "Select Activity Summary File from the NJ Server ")
AccSummaryNJDC <- fread(AccSummaryNJDC, skip = 9, header = TRUE)
winDialog(type="ok","Load the Activity Summary File from the TY Server.")
AccSummaryTYDC <- choose.files(caption = "Select Activity Summary File from the TY Server ")
AccSummaryTYDC <- fread(AccSummaryTYDC, skip = 9, header = TRUE)
-----------------------------------Read Open Trades
if(winDialog(type="yesno","Do you need to enter the Bloomberg Macro File ?")=="NO"){
} else{ # Specify the Column Classes.
BMacro<-choose.files(caption = "Select Bloomberg Macro File ")
BloombergFile<-read_xlsx(BMacro,sheet = 2,skip = 2)}
Worksheet<-read_xlsx(BMacro,sheet = 3)
winDialog(type="ok","Load the Open Trades File from the NJ Server.")
NJFile <- file.choose()
OpenTradesNJDC <- fread(NJFile, select = c(1:14), header = TRUE)
MergedOpenTrades[,NotionalusingMarketRate:=ifelse(MergedOpenTrades$Type=="FX",MergedOpenTrades$Conv rate to USD MergedOpenTrades$Lots,MergedOpenTrades$Conv rate to USD MergedOpenTrades$Lots as.numeric(MergedOpenTrades$Share Price (Native)) MergedOpenTrades$Rate to USD/MergedOpenTrades$Factor)]
Create a Exposure Table which contains the data in the "Exposure' Sheet in the template .
Stress_Clear_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts, Acc Type == "Clearing")
Stress_Credit_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts, Acc Type == "Credit")
Stress_Omnibus_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts, Acc Type == "Omnibus")
Stress_Clear_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[1,], Stress_Clear_Simulation_Per_Acc_credit_accounts)
for (i in 2:nrow(Stress_Clear_Simulation_Per_Acc_credit_accounts)) {
Stress_Clear_Simulation_Per_Acc_credit_accounts[i, 1] <- ""
}
Stress_Credit_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[2,], Stress_Credit_Simulation_Per_Acc_credit_accounts)
for (i in 2:nrow(Stress_Credit_Simulation_Per_Acc_credit_accounts)) {
Stress_Credit_Simulation_Per_Acc_credit_accounts[i, 1] <- ""
}
Stress_Omnibus_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[3,], Stress_Omnibus_Simulation_Per_Acc_credit_accounts)
for (i in 2:nrow(Stress_Omnibus_Simulation_Per_Acc_credit_accounts)) {
Stress_Omnibus_Simulation_Per_Acc_credit_accounts[i, 1] <- ""
}
-------------------------------Write the files with the two tables.
ReportDate<-ifelse(weekdays(Sys.Date())=="Monday",toString(Sys.Date()-3),toString(Sys.Date()-1))
FileName_Retail<-ifelse(grepl("_now",NJFile),paste0("Stress Test Summary Retail ",gsub("\-","",toString(Sys.Date())),"now.csv"),paste0("Stress Test Summary Retail ",gsub("\-","",ReportDate),"EOD.csv"))
FileName_Special<-ifelse(grepl("_now",NJFile),
paste0("Stress Test Summary Special ",gsub("\-","",toString(Sys.Date())),"now.csv"),paste0("Stress Test Summary Special ",gsub("\-","",ReportDate),"EOD.csv"))
if (! require("readxl")) { install.packages("readxl") } library(readxl) if (! require("data.table")) { install.packages("data.table") } library(data.table) if (! require("dplyr")) { install.packages("dplyr") } library(dplyr)
-----------------------------Read Special Accounts File
SpecAccounts <- fread("Credit_Clients.csv",header = TRUE)
-----------------------------Read Activity Summary Files
winDialog(type="ok","Load the Activity Summary File from the NJ Server.") AccSummaryNJDC <- choose.files(caption = "Select Activity Summary File from the NJ Server ") AccSummaryNJDC <- fread(AccSummaryNJDC, skip = 9, header = TRUE)
winDialog(type="ok","Load the Activity Summary File from the TY Server.") AccSummaryTYDC <- choose.files(caption = "Select Activity Summary File from the TY Server ") AccSummaryTYDC <- fread(AccSummaryTYDC, skip = 9, header = TRUE)
MergedAccSummary <- rbind(AccSummaryNJDC, AccSummaryTYDC)
colnames(MergedAccSummary)[4] <- "Account"
MergedAccSummary <- MergedAccSummary %>% select(Account, Server, Book, Beginning, Ending,NetPL)
MergedAccSummary[, Beginning := as.numeric(gsub(",", "", MergedAccSummary[, Beginning]))] MergedAccSummary[, Ending := as.numeric(gsub(",", "", MergedAccSummary[, Ending]))] MergedAccSummary[, NetPL := as.numeric(gsub(",", "", MergedAccSummary[, NetPL]))]
Create a Special Clients List to distinguish between Retail and Special Clients
OMCRCL <- c('Credit','Omnibus','Clearing')
MergedAccSummary <- left_join(MergedAccSummary,SpecAccounts[,c("Account","Client","Acc Type")],by= "Account") MergedAccSummary$
Acc Type[is.na(MergedAccSummary$Acc Type)]<-"Retail"Acc_Clients <- MergedAccSummary %>%select(Account, Server, Book) Acc_Clients <- unique(Acc_Clients)
-----------------------------------Read Open Trades
if(winDialog(type="yesno","Do you need to enter the Bloomberg Macro File ?")=="NO"){ } else{ # Specify the Column Classes.
BMacro<-choose.files(caption = "Select Bloomberg Macro File ") BloombergFile<-read_xlsx(BMacro,sheet = 2,skip = 2)}
Worksheet<-read_xlsx(BMacro,sheet = 3)
winDialog(type="ok","Load the Open Trades File from the NJ Server.") NJFile <- file.choose() OpenTradesNJDC <- fread(NJFile, select = c(1:14), header = TRUE)
OpenTradesNJDC[, c("Type", "Country", "Ticket", "Order ID", "DateTime", "Comm", "Current") :=NULL]
OpenTradesNJDC[, Open := as.numeric(gsub(",", "", OpenTradesNJDC[, Open]))] OpenTradesNJDC[, Lots := as.numeric(gsub(",", "", OpenTradesNJDC[, Lots]))] OpenTradesNJDC[, FloatingPL := as.numeric(gsub(",", "", OpenTradesNJDC[, FloatingPL]))]
winDialog(type="ok","Load the Open Trades File from the TY Server.") TYFile <- file.choose() OpenTradesTYDC <- fread(TYFile, select = c(1:13), header = TRUE) OpenTradesTYDC[, c("Type", "Ticket", "Order ID", "DateTime", "Comm") := NULL] OpenTradesTYDC[, Open := as.numeric(gsub(",", "", OpenTradesTYDC[, Open]))] OpenTradesTYDC[, Account := as.character( OpenTradesTYDC[, Account])] OpenTradesTYDC[, Lots := as.numeric(gsub(",", "", OpenTradesTYDC[, Lots]))] OpenTradesTYDC[, FloatingPL := as.numeric(gsub(",", "", OpenTradesTYDC[, FloatingPL]))] OpenTradesTYDC[, Current :=NULL]
MergedOpenTrades <- rbind(OpenTradesNJDC, OpenTradesTYDC)
----------------------------------Calculate and create Exposure Sheet
Add a column with the corresponding Rate from Bloomberg
MergedOpenTrades<-left_join(MergedOpenTrades,BloombergFile[,c("Symbol","Conv rate to USD")],by= "Symbol")
Add the Conversion rate based on the server from the Worksheet file and the Factor from the Worksheet
MergedOpenTrades <-setDT(left_join(left_join(MergedOpenTrades,Worksheet[,c("Server","Rate to USD")],by= "Server"),Worksheet[,c(1,3)],by="Symbol"))
Convert the Floating PL in to usd Based on the Server and Istrument Traded
MergedOpenTrades[,FloatingPL:=(MergedOpenTrades$
Rate to USD* MergedOpenTrades$FloatingPL)]MergedOpenTrades<-MergedOpenTrades %>% select(Server, Book, Account, Symbol, Lots, BS,
Conv rate to USD,Rate to USD, FloatingPL,Factor)Add an empty Column which will comntain a 1 if the position is Long and -1 if the position is short
MergedOpenTrades[,PositionFactor:=as.numeric()]
inputs the 1 and -1 respective of the Position (B,S)
set(MergedOpenTrades,i=which(MergedOpenTrades$BS=="B"),j="PositionFactor",value = 1) set(MergedOpenTrades,i=which(MergedOpenTrades$BS=="S"),j="PositionFactor",value = -1)
Add the Bloomberg Rate which will be needed to calculate the Notionals of Indices and Commodities and
MergedOpenTrades<-setDT(left_join(MergedOpenTrades,BloombergFile[,c(1,2,8)],by="Symbol"))
MergedOpenTrades[,NotionalusingMarketRate:=ifelse(MergedOpenTrades$Type=="FX",MergedOpenTrades$
Conv rate to USDMergedOpenTrades$Lots,MergedOpenTrades$Conv rate to USDMergedOpenTrades$Lots as.numeric(MergedOpenTrades$Share Price (Native)) MergedOpenTrades$Rate to USD/MergedOpenTrades$Factor)]Create a Exposure Table which contains the data in the "Exposure' Sheet in the template .
Exposure <- MergedOpenTrades %>% select(Account, Server, Book, BS, Symbol, PositionFactor, Lots, Type, FloatingPL, NotionalusingMarketRate)
Add the PLs under Each scenario :
initializes them as Empty Columns
Exposure[,c("Scenario 1", "Scenario 2","Scenario 3","Scenario 4"):=as.numeric()]
----------------------------------Import Stress Test Values
winDialog(type="ok","Load the Stress Test Template.") StressTemplate <- file.choose() Stress_Values_Template <- fread(StressTemplate, skip = 0, header = TRUE)
Stress_Values_Template[is.na(Stress_Values_Template)] <- 0
Add the Stress Value to each Open Trade by the Symbol
MergedOpenTrades<-left_join(MergedOpenTrades,Stress_Values_Template,by="Symbol")
Calculate the Floating PLs by Account and Open Trade in scenarios 1-5
Exposure[,
Scenario 1:=MergedOpenTrades$NotionalusingMarketRate MergedOpenTrades$Scenario 1MergedOpenTrades$PositionFactor]Exposure[,
Scenario 2:=MergedOpenTrades$NotionalusingMarketRate MergedOpenTrades$Scenario 2MergedOpenTrades$PositionFactor]Exposure[,
Scenario 3:=MergedOpenTrades$NotionalusingMarketRate MergedOpenTrades$Scenario 3MergedOpenTrades$PositionFactor]Exposure[,
Scenario 4:=MergedOpenTrades$NotionalusingMarketRate MergedOpenTrades$Scenario 4MergedOpenTrades$PositionFactor]----------------------------------Create Account List
Trades_Clients <- MergedOpenTrades %>% select(Account, Server, Book) Trades_Clients <- unique(Trades_Clients) AccountList <-rbind(Acc_Clients, Trades_Clients) AccountList <-unique(AccountList)
----------------------------------Create Balance Conversion Sheets
Balance_Conversion <- AccountList %>% select(Account,Server) Balance_Conversion <- inner_join(Worksheet[, c("Server", "Rate to USD")], Balance_Conversion, by = "Server") Balance_Conversion <- inner_join(MergedAccSummary[, c("Account", "Ending")], Balance_Conversion, by = "Account") Balance_Conversion <- inner_join(MergedAccSummary[, c("Account", "NetPL")], Balance_Conversion, by = "Account") Balance_Conversion <- cbind(Balance_Conversion,Balance_USD = (Balance_Conversion$Ending * Balance_Conversion$
Rate to USD))balance conversion_credit
Balance_Conversion_credit <- Balance_Conversion Balance_Conversion_credit <- left_join(Balance_Conversion_credit,SpecAccounts[,c("Account","Client","Acc Type")],by= "Account") Balance_Conversion_credit$
Acc Type[is.na(Balance_Conversion_credit$Acc Type)]<-"Retail" Balance_Conversion_credit <-filter(Balance_Conversion_credit,Balance_Conversion_credit$Acc Type%in% OMCRCL) setDT(Balance_Conversion_credit)-------------------------------Separate Retail and Special Clients Exposure
Exposure<-left_join(Exposure,SpecAccounts[,c("Account","Client","Acc Type")],by= "Account") Exposure$
Acc Type[is.na(Exposure$Acc Type)]<-"Retail"Credit_Exposure <- filter(Exposure,Exposure$
Acc Type%in% OMCRCL) setDT(Credit_Exposure)Exposure <- filter(Exposure,
Acc Type== "Retail") Exposure$Client <- NULL Exposure$Acc Type<-NULL setDT(Exposure)------------------------------Calculate the total PL for each Retail Account
AggExposuresbyAccount<-Exposure[, lapply(.SD, sum), by = "Account",.SDcols=c(9,11,12,13,14)]
Stress_Simulation_Per_Acc <-setDT(left_join(left_join(AccountList,Balance_Conversion[,c(1,6)],by="Account"),AggExposuresbyAccount,by="Account")) Stress_Simulation_Per_Acc[,Equity:=Stress_Simulation_Per_Acc$Balance_USD + Stress_Simulation_Per_Acc$FloatingPL] # Stress_Simulation_Per_Acc[,NewEquity1:= Stress_Simulation_Per_Acc$Equity + Stress_Simulation_Per_Acc$
Scenario 1] Stress_Simulation_Per_Acc[,NewEquity2:= Stress_Simulation_Per_Acc$Equity + Stress_Simulation_Per_Acc$Scenario 2] Stress_Simulation_Per_Acc[,NewEquity3:= Stress_Simulation_Per_Acc$Equity + Stress_Simulation_Per_Acc$Scenario 3] Stress_Simulation_Per_Acc[,NewEquity4:= Stress_Simulation_Per_Acc$Equity + Stress_Simulation_Per_Acc$Scenario 4]-------------------------------Calculate the total PL for each Credit Account
AggExposuresbyAccount_credit<-Credit_Exposure[, lapply(.SD, sum), by = "Account",.SDcols=c(9,11,12,13,14)]
Stress_Simulation_Per_Acc_credit<-setDT(left_join(left_join(AccountList,Balance_Conversion_credit[,c(1,6,7,8)],by="Account"),AggExposuresbyAccount_credit,by="Account")) Stress_Simulation_Per_Acc_credit[is.na(Stress_Simulation_Per_Acc_credit)] <- 0 Stress_Simulation_Per_Acc_credit[,Equity:=Stress_Simulation_Per_Acc_credit$Balance_USD + Stress_Simulation_Per_Acc_credit$FloatingPL] Stress_Simulation_Per_Acc_credit$Equity <- as.numeric(formatC(Stress_Simulation_Per_Acc_credit$Equity, digits = 0, format = "f"))
Stress_Simulation_Per_Acc_credit[,NewEquity1:=Stress_Simulation_Per_Acc_credit$Equity+ Stress_Simulation_Per_Acc_credit$
Scenario 1] Stress_Simulation_Per_Acc_credit$NewEquity1 <- as.numeric(formatC(Stress_Simulation_Per_Acc_credit$NewEquity1, digits = 0, format = "f"))Stress_Simulation_Per_Acc_credit[,NewEquity2:=Stress_Simulation_Per_Acc_credit$Equity + Stress_Simulation_Per_Acc_credit$
Scenario 2] Stress_Simulation_Per_Acc_credit$NewEquity2 <- as.numeric(formatC(Stress_Simulation_Per_Acc_credit$NewEquity2, digits = 0, format = "f"))Stress_Simulation_Per_Acc_credit[,NewEquity3:=Stress_Simulation_Per_Acc_credit$Equity + Stress_Simulation_Per_Acc_credit$
Scenario 3] Stress_Simulation_Per_Acc_credit$NewEquity3 <- as.numeric(formatC(Stress_Simulation_Per_Acc_credit$NewEquity3, digits = 0, format = "f"))Stress_Simulation_Per_Acc_credit[,NewEquity4:=Stress_Simulation_Per_Acc_credit$Equity + Stress_Simulation_Per_Acc_credit$
Scenario 4] Stress_Simulation_Per_Acc_credit$NewEquity4 <- as.numeric(formatC(Stress_Simulation_Per_Acc_credit$NewEquity4, digits = 0, format = "f"))--------------------Create Special Clients Table
Stress_Simulation_Per_Acc_credit$
Acc Type[is.na(Stress_Simulation_Per_Acc_credit$Acc Type)]<-"Retail" Stress_Simulation_Per_Acc_credit <- filter(Stress_Simulation_Per_Acc_credit,Stress_Simulation_Per_Acc_credit$Acc Type%in% OMCRCL) Stress_Simulation_Per_Acc_credit_summary <- summarise_at(group_by(Stress_Simulation_Per_Acc_credit,Acc Type), vars(Equity:NewEquity4), sum) %>% rename("Scenario 1" = NewEquity1, "Scenario 2" = NewEquity2, "Scenario 3" = NewEquity3, "Scenario 4" = NewEquity4) %>% mutate(Client = "") Stress_Simulation_Per_Acc_credit_summary <- Stress_Simulation_Per_Acc_credit_summary[,c(1,7,2,3,4,5,6)] Stress_Simulation_Per_Acc_credit_accounts <- summarise_at(group_by(Stress_Simulation_Per_Acc_credit, Client), vars(Equity:NewEquity4), sum) %>% rename("Scenario 1" = NewEquity1, "Scenario 2" = NewEquity2, "Scenario 3" = NewEquity3, "Scenario 4" = NewEquity4)Stress_2_Simulation_Per_Acc_credit_accounts <- left_join(Stress_Simulation_Per_Acc_credit_accounts, SpecAccounts, by = "Client") %>% select("Acc Type", Client, Equity, "Scenario 1", "Scenario 2", "Scenario 3", "Scenario 4") %>% distinct
Stress_Clear_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts,
Acc Type== "Clearing") Stress_Credit_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts,Acc Type== "Credit") Stress_Omnibus_Simulation_Per_Acc_credit_accounts <- filter(Stress_2_Simulation_Per_Acc_credit_accounts,Acc Type== "Omnibus")Stress_Clear_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[1,], Stress_Clear_Simulation_Per_Acc_credit_accounts) for (i in 2:nrow(Stress_Clear_Simulation_Per_Acc_credit_accounts)) { Stress_Clear_Simulation_Per_Acc_credit_accounts[i, 1] <- "" }
Stress_Credit_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[2,], Stress_Credit_Simulation_Per_Acc_credit_accounts) for (i in 2:nrow(Stress_Credit_Simulation_Per_Acc_credit_accounts)) { Stress_Credit_Simulation_Per_Acc_credit_accounts[i, 1] <- "" }
Stress_Omnibus_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Simulation_Per_Acc_credit_summary[3,], Stress_Omnibus_Simulation_Per_Acc_credit_accounts) for (i in 2:nrow(Stress_Omnibus_Simulation_Per_Acc_credit_accounts)) { Stress_Omnibus_Simulation_Per_Acc_credit_accounts[i, 1] <- "" }
Stress_3_Simulation_Per_Acc_credit_accounts <- rbind(Stress_Clear_Simulation_Per_Acc_credit_accounts, Stress_Credit_Simulation_Per_Acc_credit_accounts, Stress_Omnibus_Simulation_Per_Acc_credit_accounts)
Stress_Simulation_Per_Acc[is.na(Stress_Simulation_Per_Acc)] <- 0
---------------------------------Create Retail Clients Table
StressSummary<-data.frame()
Insert 0 for all Control Case rows.
for(i in 1:5){ StressSummary[i,1]<-0
}
control <- sum(Stress_Simulation_Per_Acc[,Equity])
Calculate New Equity for each Scenario.
for(i in 2:5) {
Value will output "NewEquity i"
Value<-paste0("NewEquity",toString(-1+i))
Debit Balances Row
StressSummary[1,i]<-sum(Stress_Simulation_Per_Acc[get(Value)<0,get(Value)])
DD Debit Balance Rows
StressSummary[2,i]<-sum(Stress_Simulation_Per_Acc[which(Stress_Simulation_Per_Acc[,get(Value)]<0&grepl("DD",Stress_Simulation_Per_Acc[,Book])==TRUE),get(Value)])
EE Debit Balance Row
StressSummary[3,i]<-StressSummary[1,i]-StressSummary[2,i]
DD Profit Row
Summs the Negative Equities( in the Control column) in USD which contain DD in their Book column and their Scenario equity is <0
StressSummary[4,i]<- control - sum(Stress_Simulation_Per_Acc[,get(Value)]) + StressSummary[2,i] print(paste0(control, " - ", sum(Stress_Simulation_Per_Acc[,get(Value)]), " + ", StressSummary[2,i], " = ", control - sum(Stress_Simulation_Per_Acc[,get(Value)]) + StressSummary[2,i]))
StressSummary[5,i]<-StressSummary[3,i]+StressSummary[4,i] }
Add the Row Names
rownames(StressSummary)<-c("Debit Balances","DD Debit Balance","EE Debit Balance","DD Profit","Net Profit(Loss)") colnames(StressSummary)<-colnames(Stress_Values_Template)[-1]
-------------------------------Write the files with the two tables.
ReportDate<-ifelse(weekdays(Sys.Date())=="Monday",toString(Sys.Date()-3),toString(Sys.Date()-1)) FileName_Retail<-ifelse(grepl("_now",NJFile),paste0("Stress Test Summary Retail ",gsub("\-","",toString(Sys.Date())),"now.csv"),paste0("Stress Test Summary Retail ",gsub("\-","",ReportDate),"EOD.csv")) FileName_Special<-ifelse(grepl("_now",NJFile), paste0("Stress Test Summary Special ",gsub("\-","",toString(Sys.Date())),"now.csv"),paste0("Stress Test Summary Special ",gsub("\-","",ReportDate),"EOD.csv"))
fwrite(StressSummary,file = FileName_Retail,row.names=TRUE)
fwrite(Stress_3_Simulation_Per_Acc_credit_accounts,file= FileName_Special)
shell.exec(getwd())