#Code for: Designing an efficient reference rate: Lessons from SOFIA
#Date April 2026
#
#This code creates variables used in the panel data analysis


dir <- "C:\\repo_market\\repo_market"

packagesNeeded <- c("devtools",
                    "tidyverse",
                    "data.table",
                    
                    "lubridate", 
                    "bankRun",
                    "ggrba",
                    "arphit",
                    "rbatools",
                    "mpi", 
                    "tseries", 
                    "spatstat.univar",
                    "janitor", 
                    "lmtest", 
                    "sandwich",
                    "minpack.lm", 
                    "msm", 
                    "ivreg", 
                    "fixest", 
                    "ggtext",
                    "purrr",
                    "writexl",
                    "furrr",
                    "future",
                    "ISOcodes",
                    "imf.api",
                    "kableExtra",
                    "rbastattable",
                    "readabs",
                    "readrba", 
                    "reldist", 
                    "rfido", 
                    "spatstat.geom", 
                    "timeDate", 
                    "trim.api", 
                    "tsibble", 
                    "visNetwork")

if(any(!packagesNeeded %in% dplyr::pull(tibble::as_tibble(installed.packages()), Package))){
  
  install.packages(packagesNeeded[!packagesNeeded %in% dplyr::pull(tibble::as_tibble(installed.packages()), Package)])
  
}

library(mpi)
library(bankRun)

for(i in packagesNeeded){library(i, character.only = TRUE)}

rm(list=ls())


startDate <- "2022-01-01"
endDate <- Sys.Date()

startDate <- asDate(startDate)
endDate <- asDate(endDate)


#-----------------------------------------------------------------
#load AX transaction level data (MPI package)
rawASXData <- fetchASXRepoData(startDate = "2022-01-01", 
                               endDate = Sys.Date(), 
                               ERUser = TRUE)

#-------------------------------------------------------------------------------
#import some files that would otherwise be pulled from system (no)

cashRateDaily <- read.csv("C:/repo_market/repo_market/cashRateDaily.csv") %>% 
  as_tibble() %>% 
  mutate(date = as.Date(date)) %>% 
  select(-X)

businessDays <- read.csv("C:/repo_market/repo_market/businessDaySchedule.csv") %>% 
  as_tibble() %>% 
  mutate(days = as.Date(days), 
         shifted = as.Date(shifted))

noiseRatio <- read.csv("C:/repo_market/repo_market/sign_noise_sofia_vwap_rba_apr2025.csv") %>% 
  as_tibble()%>% 
  mutate(datefmt = as.Date(datefmt))


#----------------------------------------------------------------------------------
#Creating cutoff rates 

cutoff <- rawASXData %>%
  dplyr::left_join(businessDays, by = c("tradeDate" = "days")) %>%
  mutate(repoTermBusDays = originalTerm - adjustment) %>%                             
  mutate(collateralDummy = case_when(issuerType %in% c("Govt", "Semi-Govt") ~ 1,      #create collateral dummy (AGS and Semi)
                                     TRUE ~ 0))%>%
  filter(collateralDummy == 1,                                                        
         repoTermBusDays == 1,                                                        
         tradeDate == settleDate,                                                     
         originalRepoType != "Call",                                                  
         actualRepoType != "Call" ) %>%
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode))%>%
  mutate(parentBuyerCode2=ifelse(parentBuyerCode == "BNPT", "BPSS", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "CHAM", "JPMA", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "CINA", "SALS", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "DBAL", "DEUT", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "HKBA", "HKSB", parentBuyerCode),
         
         parentSellerCode2=ifelse(parentSellerCode == "BNPT", "BPSS", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "CHAM", "JPMA", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "CINA", "SALS", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "DBAL", "DEUT", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "HKBA", "HKSB", parentSellerCode))%>%
  mutate(relPartyDummy = case_when(parentBuyerCode2 == parentSellerCode2 ~ 1,      
                                   TRUE ~ 0),
         relPartyVolume=relPartyDummy*leg1FV,
         dailyVol=ave(leg1FV,tradeDate, FUN = sum ))%>%
  mutate(marketshare= leg1FV/dailyVol,
         mssquare=marketshare^2)%>%
  group_by(tradeDate, repoRate) %>%                                                  
  dplyr::summarise(volume = sum(leg1FV), 
                   trades = n(),
                   relTrades = sum(relPartyDummy),
                   relVolume = sum(relPartyVolume),
                   sumMS= sum(marketshare),
                   sumMSS = sum(mssquare))  %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                
  mutate( dailyVol=ave(volume,tradeDate, FUN = sum ),
          share = volume/sum(volume)*100,                                       
          share2= volume/dailyVol*100,
          shareTrades = trades/sum(trades)) %>%                                      
  mutate(cumVolume = cumsum(share)) %>% 
  mutate(diff25 = cumVolume - 25,
         atmin=diff25/share) %>% 
  filter(diff25 >= 0)%>%   
  mutate(cutoffRate = ave(repoRate, tradeDate, FUN = min) )%>%
  filter(repoRate==cutoffRate)%>%
  subset(select = c(tradeDate, atmin, cutoffRate) )




#__________________________________________________________________________________
#putting data together

paneldata<- rawASXData %>%
  dplyr::left_join(businessDays, by = c("tradeDate" = "days")) %>%
  mutate(repoTermBusDays = originalTerm - adjustment) %>%                             
  mutate(collateralDummy = case_when(issuerType %in% c("Govt", "Semi-Govt") ~ 1,      #create collateral dummy (AGS and Semi)
                                     TRUE ~ 0))%>%
  filter(collateralDummy == 1,                                                        
         repoTermBusDays == 1,                                                        
         tradeDate == settleDate,                                                     
         originalRepoType != "Call",                                                  
         actualRepoType != "Call"                                                   
  ) %>%
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode))%>%
  mutate(parentBuyerCode2=ifelse(parentBuyerCode == "BNPT", "BPSS", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "CHAM", "JPMA", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "CINA", "SALS", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "DBAL", "DEUT", parentBuyerCode),
         parentBuyerCode2=ifelse(parentBuyerCode == "HKBA", "HKSB", parentBuyerCode),
         
         parentSellerCode2=ifelse(parentSellerCode == "BNPT", "BPSS", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "CHAM", "JPMA", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "CINA", "SALS", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "DBAL", "DEUT", parentSellerCode),
         parentSellerCode2=ifelse(parentSellerCode == "HKBA", "HKSB", parentSellerCode))%>%
      arrange(tradeDate, repoRate) %>% 
  dplyr::left_join(cutoff, by = "tradeDate" ) %>%
  filter(repoRate >= cutoffRate)%>%
  mutate(relPartyDummy = case_when(parentBuyerCode2 == parentSellerCode2 ~ 1,      
                                   TRUE ~ 0),
         relPartyVolume=relPartyDummy*leg1FV,
         dailyVol=ave(leg1FV,tradeDate, FUN = sum ),
         market_share=leg1FV/dailyVol,
         mssqr=(leg1FV/dailyVol)^2 )%>%
  arrange(tradeDate, repoRate) %>%
  dplyr::left_join(noiseRatio, by = c("tradeDate"="datefmt" ) ) 
         

write_xlsx(paneldata, "paneldata.xlsx") 

