

import_yield_curve_data <- function(filepath =  "yield-curve.xlsx"){
  
  readxl::read_xlsx(filepath,   col_types = c("date", "date", "text", "text", rep("numeric",79))) %>% 
    dplyr::select(-name, -decision_date, -decision_date_time) %>% 
    dplyr::rename(date_time = value) %>%
    dplyr::mutate(date_time = lubridate::dmy_hms(date_time)) %>% 
    tidyr::pivot_longer(-date_time, names_to = "id", values_to = "yield") %>% 
    dplyr::filter(str_detect( id, "TB"))
  
}




#' Bond Metadata
#' @description ID, maturity date and coupon
#' @return
#' @export
#'
#' @examples
bond_metadata <- function(){
  
  
  # bond identifiers
  current  <- rbastattable::rba_stat_table("f16") %>%
    distinct(`Series ID`,  .keep_all = T) %>%
    mutate(id = case_when(str_detect(Description, "Treasury Indexed Bond") ~ "TI",
                          str_detect(Description, "Treasury Bond") ~ "TB",
                          TRUE ~ "TB"),
           id = paste0(id, str_remove(Description, "Treasury Indexed Bond |Treasury Bond ") %>% str_sub(end= 3)) ) %>%
    select(id, Description) %>%
    mutate(Description = str_squish(Description)) %>%
    transmute(id ,
              maturity_date  = str_sub(Description, start = nchar(Description)-10 ) %>% lubridate::dmy(),
              coupon = str_sub(Description, start = nchar(Description)-17 , end = nchar(Description)-13 ) %>%
                str_remove("\\%") %>%
                as.numeric )
  
  
  historical <- rbastattable::rba_stat_table("f16hist", historical = T, identifier = "Mnemonic") %>%
    distinct(`Issue ID`, .keep_all = T) %>%
    dplyr::rename(id = `Issue ID`,
           coupon = Coupon) %>%
    transmute(id, coupon = 100*as.numeric(coupon),
              maturity_date = as.Date(as.numeric(Maturity), origin = as.Date("1899-12-30")) )
  
  
  historical_2 <- rbastattable::rba_stat_table("f16hist-2009-2018", historical = T, guess_args = T) %>%
    distinct(Mnemonic, .keep_all = T) %>%
    filter(!is.na(Mnemonic)) %>%
    dplyr::rename(id = `Issue ID`,    coupon = Coupon) %>%
    filter(!is.na(id)) %>%
    transmute(id, coupon = 100*as.numeric(coupon),
              maturity_date = as.Date(as.numeric(Maturity), origin = as.Date("1899-12-30")) )
  
  
  historical_3 <- rbastattable::rba_stat_table("f16hist-2009-2018", historical = T, guess_args = T) %>%
    distinct(`Series ID`, .keep_all = T)  %>%
    filter(!is.na(`Series ID`)) %>%
    mutate(id = case_when(str_detect(Description, "Treasury Indexed Bond") ~ "TI",
                          str_detect(Description, "Treasury Bond") ~ "TB",
                          TRUE ~ "TB"),
           id = paste0(id, str_remove(Description, "Treasury Indexed Bond |Treasury Bond ") %>% str_sub(end= 3)) ) %>%
    select(id, Description) %>%
    transmute(id ,
              maturity_date  = str_extract(Description, "\\%\n.*") %>% str_remove("\\%\n") %>% lubridate::dmy(),
              coupon = str_remove(Description, str_extract(Description,"\\%\n.*" )) %>%
                str_extract( "\n.*") %>%
                str_remove("\\%\n") %>% as.numeric )
  
  
  bonds <- bind_rows(current, historical, historical_2, historical_3) %>% distinct(id, .keep_all = T)
  
  return(bonds)
}



add_bond_metadata <- function(df){
  
 
  
  # Import metadata ----------------------------------------------------------
  bond_data <- bond_metadata() # import bond metadata
  
  # Combine -----------------------------------------------------------------
  yield_data <- left_join(df, bond_data, id = "id")
  
  
  yield_data <-  yield_data %>%
    mutate(resid_maturity = lubridate::time_length(difftime(maturity_date, date_time), "year")) %>%
    filter(resid_maturity > 0) %>%
    mutate(duration = jrvFinance::bond.durations(as.Date(date_time), maturity_date,
                                                 coupon/100, freq = 2, yield/100,
                                                 convention="ACT/ACT", modified = TRUE))
  
  
  return(yield_data)
}





import_ois_data <- function(filepath = here::here("data", "findur-outputs", "ois.xlsx") ){
  
  
  readxl::read_xlsx(filepath,   col_types = c("date", "date", "text", "text", rep("numeric",12))) %>%
    mutate(decision_date = as.Date(decision_date))
  
}


edit_ois_data <- function(ois){
  
  
  # Add 5 to obviously wrong data
  row_index <- which(ois$decision_date == as.Date("2005-10-05") & ois$name == "pre_30")
  column_index <- which(colnames(ois) %in% c("4m", "5m", "6m", "9m", "1y"))
  
  ois[row_index, column_index] <- ois[row_index, column_index] + 5
  
  # Replace post_20 and post_30 data for 2m on 2019-08-06 - with post_10
  row_index <- which(ois$decision_date == as.Date("2019-08-06") & (ois$name == "post_20" |ois$name == "post_30" ))
  column_index <- which(colnames(ois) %in% c("2m"))
  
  replacement_row_index <- which(ois$decision_date == as.Date("2019-08-06") & ois$name == "post_10" )
  replacement_column_index <- which(colnames(ois) %in% "2m")
  
  ois[row_index, column_index] <- ois[replacement_row_index, replacement_column_index]
  
  
  # edit column names to have prefix of 'ois'
  colnames(ois)[str_detect(colnames(ois) , "\\d" )] <- paste0("ois_", colnames(ois)[str_detect(colnames(ois) ,  "\\d" )])
  
  return(ois)
}




import_ois_all <- function(){
  
  ois_all <- readRDS("ois_all.RDS") %>%
    select(date_time, tenor, mid) %>%
    mutate(date_time = lubridate::round_date(lubridate::ymd_hms(date_time), unit = "minute"),
           mid = as.numeric(mid)) %>%
    arrange(date_time) %>%
    filter(tenor != "Matur") %>%
    distinct(date_time, tenor, .keep_all = T) %>%
    pivot_wider(names_from = tenor, values_from = mid) ## JH change slightly here to remove id_cols=c(tenor, date_time)
  
  # put in some edits
  ois_all[ois_all$date_time == ymd_hms("2005-10-05 09:00:00"), c("9m", "1wk", "1y", "5m", "6m", "4m")] <-
    ois_all[ois_all$date_time == ymd_hms("2005-10-05 09:00:00"), c("9m", "1wk", "1y", "5m", "6m", "4m")] + 5
  
  
  ois_all[lubridate::date(ois_all$date_time) ==  "2019-08-06", c("date_time", "2m") ] <-
    ois_all %>% filter(lubridate::date(date_time) == "2019-08-06") %>%
    select(date_time, `2m`) %>%
    mutate(`2m` = case_when(`2m` >= 2.5 ~ NaN,
                            TRUE ~ `2m`) ,
           `2m` = zoo::na.approx(`2m`, na.rm = F))
  
  # merge 1wk and 1w columns
  ois_all <- ois_all %>%
    mutate(`1w` = case_when(is.na(`1w`) ~ `1wk`,
                            TRUE ~ `1w`)) %>%
    select(-`1wk`)
  
  # Replace zeroes with NA
  ois_all[ois_all == 0] <- NA
  
  # Rename so everything is in months
  
  ois_all <- ois_all %>% dplyr::rename(`24m` = `2y`, `12m` = `1y`, `36m` = `3y`) 
  
  
  return(ois_all)
  
}





