#This code replicates ASX's SOFIA
#1. import official SOFIA from ASX webpage 
#2. get raw repo data
#3. derive ASX SOFIA from raw data
#4. compare SOFIA calculated with SOFIA provided by ASX


# Packages ----------------------------------------------------------------

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

packagesNeeded <- c("tidyverse",
                    "data.table",
                    "mpi", 
                    "lubridate", 
                    "bankRun",
                    "ggrba",
                    "arphit",
                    "rbatools",
                    "mpi", 
                    "tseries", 
                    "spatstat.univar",
                    "janitor", 
                    "lmtest", 
                    "sandwich",
                    "minpack.lm", 
                    "msm", 
                    "ivreg", 
                    "fixest", 
                    "ggtext",
                    "writexl")

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)])
  
}

for(i in packagesNeeded){library(i, character.only = TRUE)}
rm(list=ls())
#-------------------------------------------------------------------------------
#set date

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

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


#-------------------------------------------------------------------------------  
#1. import official SOFIA from ASX webpage 

directory <- tempdir()

saveDir <- paste0(directory, "/sofiaBeta.xlsx")

rbatools::set_proxy()

utils::download.file("https://www.asx.com.au/content/dam/asx/connectivity-and-data/sofia-beta-version.xlsx",
                     saveDir,
                     mode = "wb")

SOFIAData <- readxl::read_excel(saveDir, skip = 3) %>%
  # dplyr::select(1:3) %>%
  tidyr::drop_na() %>%
  dplyr::mutate(Date = as.Date(Date)) %>%
  dplyr::select("Date" = 'Date', "SOFIA" = 'SOFIA Overnight (%) VWAP',
                "SOFIAVolume" = 'Eligible Volume ($)',
                "SOFIAMedian" = "SOFIA Overnight  (%) Volume Weighted Median",
                "SOFIATrades" = "Eligible Trades",
                "SOFIAMin" = "Minimum Yield",
                "SOFIAMax" = "Maximum Yield") %>%
  dplyr::mutate(SOFIAVolume = .data$SOFIAVolume/1e9)

if(is.null(endDate)){endDate <- max(SOFIAData$Date)}
if(is.null(startDate)){startDate <- endDate}


#-------------------------------------------------------------------------------
#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))

SOFIAFiltered <- SOFIAData %>%
  dplyr::filter(dplyr::between(.data$Date, .env$startDate, .env$endDate))

#-------------------------------------------------------------------------------  
#2. get raw repo data


rawASXData <- fetchASXRepoData(startDate = "2022-01-01", 
                               endDate = Sys.Date(), 
                               ERUser = TRUE)

#  businessDays <- tibble::tibble(days = generateBusinessDays(startDate = min(SOFIAData$Date),
#                                                             endDate = max(SOFIAData$Date)))   %>%
#    dplyr::mutate(shifted = shiftBusinessDays(vecDate = days, shiftBy = 1),
#                  adjustment = as.numeric(shifted - days - 1))

#-------------------------------------------------------------------------------




#Original SOFIA (RBA calculation has minor differences to ASX) 
SOFIANew <- rawASXData %>%
  dplyr::left_join(businessDays, by = c("tradeDate" = "days")) %>%
  mutate(repoTermBusDays = originalTerm - adjustment) %>%                             #need to check that
  mutate(collateralDummy = case_when(issuerType %in% c("Govt", "Semi-Govt") ~ 1,      #create collateral dummy (AGS and Semi)
                                     TRUE ~ 0)) %>%
  filter( collateralDummy == 1,                                                        #keep only GC1
          repoTermBusDays == 1,                                                        #check that
          tradeDate == settleDate,                                                     # same day settlement
          originalRepoType != "Call",                                                  #exclude call repos
          actualRepoType != "Call") %>% 
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode)) %>%                #exclude RBA as counterparty
  group_by(tradeDate, repoRate) %>%                                                   #question2
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share)) %>% 
  mutate(diff25 = cumVolume - 25) %>% 
  filter(diff25 >= 0) %>%                                                           #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               TRUE ~ volume)) %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>%
  dplyr::mutate(Type = "RBA")




#Figure 3 data
#SOFIA without related-party transactions

SOFIArelParty <- 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,                                                        #keep only GC1
          repoTermBusDays == 1,                                                        #check that
          tradeDate == settleDate,                                                     # same day settlement
          originalRepoType != "Call",                                                  #exclude call repos
          actualRepoType != "Call"                                                    #exclude call repos
  ) %>% 
  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))  %>%
  filter(relPartyDummy == 0)%>%
  group_by(tradeDate, repoRate) %>%                                                   
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share)) %>% 
  mutate(diff25 = cumVolume - 25) %>% 
  filter(diff25 >= 0) %>%                                                           #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               TRUE ~ volume)) %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>% 
  dplyr::mutate(Type = "excl. rel. party")


#Figure 4 data
#25/95 trims


SOFIAv2595 <- 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,                                                        #keep only GC1
         repoTermBusDays == 1,                                                        #check that
         tradeDate == settleDate,                                                     # same day settlement
         originalRepoType != "Call",                                                  #exclude call repos
         actualRepoType != "Call"                                                    #exclude call repos
        ) %>% 
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode)) %>%    
  group_by(tradeDate, repoRate) %>%                                                   
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share))   %>% 
  mutate(diff25 = cumVolume - 25,
         diff75 = cumVolume - 95)   %>% 
  filter(diff25 >= 0 &
           diff75 <= 0) %>%   
  #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               diff75 == min(diff75) ~ (diff75/share)*volume,
                               TRUE ~ volume))  %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>% 
  dplyr::mutate(Type = "25/95")


#Figure 5 data
#25/95 trims, >$2m


SOFIAv2595_2m <- rawASXData %>%
  dplyr::left_join(businessDays, by = c("tradeDate" = "days")) %>%
  mutate(repoTermBusDays = originalTerm - adjustment) %>%                             #need to check that
  mutate(collateralDummy = case_when(issuerType %in% c("Govt", "Semi-Govt") ~ 1,      #create collateral dummy (AGS and Semi)
                                     TRUE ~ 0)) %>%
  filter(collateralDummy == 1,                                                        #keep only GC1
         repoTermBusDays == 1,                                                       
         tradeDate == settleDate,                                                     # same day settlement
         originalRepoType != "Call",                                                  #exclude call repos
         actualRepoType != "Call",                                                    #exclude call repos
         leg1FV > 2000000                                                         #exclude call repos
  ) %>% 
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode)) %>%    
  #exclude RBA as counterparty
  group_by(tradeDate, repoRate) %>%                                                   
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share))   %>% 
  mutate(diff25 = cumVolume - 25,
         diff75 = cumVolume - 95)   %>% 
  filter(diff25 >= 0 &
           diff75 <= 0) %>%   
  #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               diff75 == min(diff75) ~ (diff75/share)*volume,
                               TRUE ~ volume))  %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>% 
  dplyr::mutate(Type = "25/95, >$2mi")



#Figure 6 data
#25/75 trims


SOFIAv2575 <- 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,                                                        #keep only GC1
         repoTermBusDays == 1,                                                        
         tradeDate == settleDate,                                                     # same day settlement
         originalRepoType != "Call",                                                  #exclude call repos
         actualRepoType != "Call"                                                    #exclude call repos
  ) %>% 
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode)) %>%    
  #exclude RBA as counterparty
  group_by(tradeDate, repoRate) %>%                                                  
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share))   %>% 
  mutate(diff25 = cumVolume - 25,
         diff75 = cumVolume - 75)   %>% 
  filter(diff25 >= 0 &
           diff75 <= 0) %>%   
  #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               diff75 == min(diff75) ~ (diff75/share)*volume,
                               TRUE ~ volume))  %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>% 
  dplyr::mutate(Type = "25/75")


#Figure 7 data
#25/75 trims, >$2m


SOFIAv2575_2m <- 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,                                                        #keep only GC1
         repoTermBusDays == 1,                                                        
         tradeDate == settleDate,                                                     # same day settlement
         originalRepoType != "Call",                                                  #exclude call repos
         actualRepoType != "Call",                                                    #exclude call repos
         leg1FV > 2000000                                                         #exclude call repos
  ) %>% 
  filter(., !grepl("RBAA", buyerCode), !grepl("RBAA", sellerCode)) %>%    
  #exclude RBA as counterparty
  group_by(tradeDate, repoRate) %>%                                                   
  summarise(volume = sum(leg1FV), 
            trades = n()) %>% 
  group_by(tradeDate) %>% 
  arrange(tradeDate, repoRate) %>%                                                   #order by repo rate
  mutate(share = volume/sum(volume)*100,                                            #volume market share
         shareTrades = trades/sum(trades)) %>%                                      #transaction market share
  mutate(cumVolume = cumsum(share))   %>% 
  mutate(diff25 = cumVolume - 25,
         diff75 = cumVolume - 75)   %>% 
  filter(diff25 >= 0 &
           diff75 <= 0) %>%   
  #keep only transactions with market share above 25%
  mutate(newVolume = case_when(diff25 == min(diff25) ~ (diff25/share)*volume,       #volume after cutting data for w. median
                               diff75 == min(diff75) ~ (diff75/share)*volume,
                               TRUE ~ volume))  %>% 
  dplyr::summarise(SOFIAMedian = spatstat.univar::weighted.quantile(x = repoRate,   
                                                                    w = newVolume,
                                                                    probs = 0.5,
                                                                    type = 1),
                   SOFIA = round(weighted.mean(x = repoRate, w = newVolume), 6),
                   SOFIAMin = min(repoRate), 
                   SOFIAMax = max(repoRate), 
                   SOFIATrades = sum(trades),
                   SOFIAVolume = round(sum(newVolume, na.rm = TRUE), 0)/1e9) %>%
  dplyr::ungroup() %>% 
  dplyr::rename("Date" = "tradeDate") %>% 
  dplyr::mutate(Type = "25/75,>$2mi")






#############################################################################
#Graphs


#Figure 3: SOFIA excluding related-party transactions 


#add ASX version of SOFIA to compare
SOFIASplitCalc <- SOFIArelParty %>% 
  bind_rows(SOFIAData %>% mutate(Type = 'ASX')) %>% 
  dplyr::left_join(cashRateDaily, by = c("Date" = "date")) %>%
  dplyr::mutate(SOFIAspread = round((.data$SOFIA - round(ESRate, 4))*100, 2),
                SOFIAspreadMedian = (.data$SOFIAMedian - .data$ESRate)*100,
                SOFIAspreadMin = (.data$SOFIAMin - .data$ESRate)*100,
                SOFIAspreadMax = (.data$SOFIAMax - .data$ESRate)*100) %>% 
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  filter(!Date %in% as.Date(c("2022-07-29", "2022-09-30", "2023-08-04", "2023-10-02", "2023-08-07", "2023-09-29")))

#create variables that show the difference
investigation <- SOFIASplitCalc %>% 
  select(Date, Type, SOFIAspread, SOFIATrades, SOFIAVolume) %>%
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  distinct() %>% 
  pivot_wider(names_from = Type, values_from = c(SOFIAspread, SOFIATrades, SOFIAVolume)) %>%
  mutate(tradeDiff = SOFIATrades_ASX - SOFIATrades_ASX, 
         volumeDiff = SOFIAVolume_ASX - SOFIAVolume_ASX, 
         SOFIADiff = SOFIAspread_ASX - SOFIAspread_ASX)

#plot graphs of both SOFIA
SOFIANewGraph3 <- arphit::arphitgg(layout = "3h") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAspread, group = Type),
                   colour = c(arphit::RBA["Red2"], arphit::RBA["Blue9"], arphit::RBA["Grey10"]),
                   panel = "1") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAVolume, group = Type),
                   colour = c(arphit::RBA["Red2"], arphit::RBA["Blue9"], arphit::RBA["Grey10"]),
                   panel = "3") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIATrades, group = Type),
                   colour = c(arphit::RBA["Red2"], arphit::RBA["Blue9"], arphit::RBA["Grey10"]),
                   panel = "5") +
  arphit::agg_units("bps", panel = "1") +
  arphit::agg_units("$b", panel = "3") +
  arphit::agg_units("no", panel = "5") +
  arphit::agg_title("") +
  arphit::agg_title("Spread to ES rate", panel = "1") +
  arphit::agg_title("Eligible volume", panel = "3") +
  arphit::agg_title("Eligible trades", panel = "5") +
  agg_label("ASX", as.Date("2022-05-20"), 12.56, 1, RBA["Red2"]) +
  agg_label("excl related party", as.Date("2025-3-31"), 7.51, 1, RBA["Blue9"]) +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "1") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "3") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "5") 


agg_draw(SOFIANewGraph3, "C:/repo_market/repo_market/RDP graphs/graph3.png")







#Figure 4: SOFIA versus 25 95 trims


#add ASX version of SOFIA to compare
SOFIASplitCalc <- SOFIAv2595 %>% 
  bind_rows(SOFIAData %>% mutate(Type = 'ASX')) %>% 
  dplyr::left_join(cashRateDaily, by = c("Date" = "date")) %>%
  dplyr::mutate(SOFIAspread = round((.data$SOFIA - round(ESRate, 4))*100, 2),
                SOFIAspreadMedian = (.data$SOFIAMedian - .data$ESRate)*100,
                SOFIAspreadMin = (.data$SOFIAMin - .data$ESRate)*100,
                SOFIAspreadMax = (.data$SOFIAMax - .data$ESRate)*100) %>% 
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  filter(!Date %in% as.Date(c("2022-07-29", "2022-09-30", "2023-08-04", "2023-10-02", "2023-08-07", "2023-09-29")))

#create variables that show the difference
investigation <- SOFIASplitCalc %>% 
  select(Date, Type, SOFIAspread, SOFIATrades, SOFIAVolume) %>%
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  distinct() %>% 
  pivot_wider(names_from = Type, values_from = c(SOFIAspread, SOFIATrades, SOFIAVolume)) %>%
  mutate(tradeDiff = SOFIATrades_ASX - SOFIATrades_ASX, 
         volumeDiff = SOFIAVolume_ASX - SOFIAVolume_ASX, 
         SOFIADiff = SOFIAspread_ASX - SOFIAspread_ASX)

#plot graphs of both SOFIA
SOFIANewGraph4 <- arphit::arphitgg(layout = "3h") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAspread, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "1") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAVolume, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "3") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIATrades, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "5") +
  arphit::agg_units("bps", panel = "1") +
  arphit::agg_units("$b", panel = "3") +
  arphit::agg_units("no", panel = "5") +
  arphit::agg_title("") +
  arphit::agg_title("Spread to ES rate", panel = "1") +
  arphit::agg_title("Eligible volume", panel = "3") +
  arphit::agg_title("Eligible trades", panel = "5") +
  agg_label("ASX", as.Date("2022-05-20"), 12.56, 1, RBA["Red2"]) +
  agg_label("25/95", as.Date("2025-2-10"), 7.51, 1, RBA["Blue9"]) +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "1") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "3") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "5") 


agg_draw(SOFIANewGraph4, "C:/repo_market/repo_market/RDP graphs/graph4.png")



#Figure 5: SOFIA versus 25 95 trims, orders > $2mi


#add ASX version of SOFIA to compare
SOFIASplitCalc <- SOFIAv2595_2m %>% 
  bind_rows(SOFIAData %>% mutate(Type = 'ASX')) %>% 
  dplyr::left_join(cashRateDaily, by = c("Date" = "date")) %>%
  dplyr::mutate(SOFIAspread = round((.data$SOFIA - round(ESRate, 4))*100, 2),
                SOFIAspreadMedian = (.data$SOFIAMedian - .data$ESRate)*100,
                SOFIAspreadMin = (.data$SOFIAMin - .data$ESRate)*100,
                SOFIAspreadMax = (.data$SOFIAMax - .data$ESRate)*100) %>% 
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  filter(!Date %in% as.Date(c("2022-07-29", "2022-09-30", "2023-08-04", "2023-10-02", "2023-08-07", "2023-09-29")))

#create variables that show the difference
investigation <- SOFIASplitCalc %>% 
  select(Date, Type, SOFIAspread, SOFIATrades, SOFIAVolume) %>%
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  distinct() %>% 
  pivot_wider(names_from = Type, values_from = c(SOFIAspread, SOFIATrades, SOFIAVolume)) %>%
  mutate(tradeDiff = SOFIATrades_ASX - SOFIATrades_ASX, 
         volumeDiff = SOFIAVolume_ASX - SOFIAVolume_ASX, 
         SOFIADiff = SOFIAspread_ASX - SOFIAspread_ASX)

#plot graphs of both SOFIA
SOFIANewGraph5 <- arphit::arphitgg(layout = "3h") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAspread, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "1") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAVolume, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "3") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIATrades, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "5") +
  arphit::agg_units("bps", panel = "1") +
  arphit::agg_units("$b", panel = "3") +
  arphit::agg_units("no", panel = "5") +
  arphit::agg_title("") +
  arphit::agg_title("Spread to ES rate", panel = "1") +
  arphit::agg_title("Eligible volume", panel = "3") +
  arphit::agg_title("Eligible trades", panel = "5") +
  agg_label("ASX", as.Date("2022-05-20"), 12.56, 1, RBA["Red2"]) +
  agg_label("25/95 > $2m", as.Date("2025-2-10"), 7.51, 1, RBA["Blue9"]) +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "1") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "3") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "5") 


agg_draw(SOFIANewGraph5, "C:/repo_market/repo_market/RDP graphs/graph5.png")




#Figure 6: SOFIA versus 25 75 trims


#add ASX version of SOFIA to compare
SOFIASplitCalc <- SOFIAv2575 %>% 
  bind_rows(SOFIAData %>% mutate(Type = 'ASX')) %>% 
  dplyr::left_join(cashRateDaily, by = c("Date" = "date")) %>%
  dplyr::mutate(SOFIAspread = round((.data$SOFIA - round(ESRate, 4))*100, 2),
                SOFIAspreadMedian = (.data$SOFIAMedian - .data$ESRate)*100,
                SOFIAspreadMin = (.data$SOFIAMin - .data$ESRate)*100,
                SOFIAspreadMax = (.data$SOFIAMax - .data$ESRate)*100) %>% 
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  filter(!Date %in% as.Date(c("2022-07-29", "2022-09-30", "2023-08-04", "2023-10-02", "2023-08-07", "2023-09-29")))

#create variables that show the difference
investigation <- SOFIASplitCalc %>% 
  select(Date, Type, SOFIAspread, SOFIATrades, SOFIAVolume) %>%
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  distinct() %>% 
  pivot_wider(names_from = Type, values_from = c(SOFIAspread, SOFIATrades, SOFIAVolume)) %>%
  mutate(tradeDiff = SOFIATrades_ASX - SOFIATrades_ASX, 
         volumeDiff = SOFIAVolume_ASX - SOFIAVolume_ASX, 
         SOFIADiff = SOFIAspread_ASX - SOFIAspread_ASX)

#plot graphs of both SOFIA
SOFIANewGraph6 <- arphit::arphitgg(layout = "3h") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAspread, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "1") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAVolume, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "3") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIATrades, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "5") +
  arphit::agg_units("bps", panel = "1") +
  arphit::agg_units("$b", panel = "3") +
  arphit::agg_units("no", panel = "5") +
  arphit::agg_title("") +
  arphit::agg_title("Spread to ES rate", panel = "1") +
  arphit::agg_title("Eligible volume", panel = "3") +
  arphit::agg_title("Eligible trades", panel = "5") +
  agg_label("ASX", as.Date("2022-05-20"), 12.56, 1, RBA["Red2"]) +
  agg_label("25/75", as.Date("2025-2-10"), 7.51, 1, RBA["Blue9"]) +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "1") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "3") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "5") 


agg_draw(SOFIANewGraph6, "C:/repo_market/repo_market/RDP graphs/graph6.png")




#Figure 7: SOFIA versus 25 75 trims, orders > $2m


#add ASX version of SOFIA to compare
SOFIASplitCalc <- SOFIAv2575_2m %>% 
  bind_rows(SOFIAData %>% mutate(Type = 'ASX')) %>% 
  dplyr::left_join(cashRateDaily, by = c("Date" = "date")) %>%
  dplyr::mutate(SOFIAspread = round((.data$SOFIA - round(ESRate, 4))*100, 2),
                SOFIAspreadMedian = (.data$SOFIAMedian - .data$ESRate)*100,
                SOFIAspreadMin = (.data$SOFIAMin - .data$ESRate)*100,
                SOFIAspreadMax = (.data$SOFIAMax - .data$ESRate)*100) %>% 
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  filter(!Date %in% as.Date(c("2022-07-29", "2022-09-30", "2023-08-04", "2023-10-02", "2023-08-07", "2023-09-29")))


#create variables that show the difference
investigation <- SOFIASplitCalc %>% 
  select(Date, Type, SOFIAspread, SOFIATrades, SOFIAVolume) %>%
  filter(Date >= as.Date("2022-01-01"), 
         Date <= as.Date("2025-03-31")) %>% 
  distinct() %>% 
  pivot_wider(names_from = Type, values_from = c(SOFIAspread, SOFIATrades, SOFIAVolume)) %>%
  mutate(tradeDiff = SOFIATrades_ASX - SOFIATrades_ASX, 
         volumeDiff = SOFIAVolume_ASX - SOFIAVolume_ASX, 
         SOFIADiff = SOFIAspread_ASX - SOFIAspread_ASX)

#plot graphs of both SOFIA
SOFIANewGraph7 <- arphit::arphitgg(layout = "3h") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAspread, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "1") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIAVolume, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "3") +
  arphit::agg_line(data = SOFIASplitCalc,
                   aes = arphit::agg_aes(x = .data$Date, y = .data$SOFIATrades, group = Type),
                   colour = c(arphit::RBA["Blue9"], arphit::RBA["Red2"], arphit::RBA["Grey10"]),
                   panel = "5") +
  arphit::agg_units("bps", panel = "1") +
  arphit::agg_units("$b", panel = "3") +
  arphit::agg_units("no", panel = "5") +
  arphit::agg_title("") +
  arphit::agg_title("Spread to ES rate", panel = "1") +
  arphit::agg_title("Eligible volume", panel = "3") +
  arphit::agg_title("Eligible trades", panel = "5") +
  agg_label("ASX", as.Date("2022-05-20"), 12.56, 1, RBA["Red2"]) +
  agg_label("25/75 > $2m", as.Date("2025-2-10"), 7.51, 1, RBA["Blue9"]) +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "1") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "3") +
  arphit::agg_xlim(yearFrac("2021-12-01"), NA, panel = "5") 


agg_draw(SOFIANewGraph7, "C:/repo_market/repo_market/RDP graphs/graph7.png")
