# This code creates Black-Scholes parameters at fiscal year end (fy_end)

library(RPostgreSQL)
library(dplyr, warn.conflicts = FALSE)

pg <- dbConnect(PostgreSQL())
dbExecute(pg, "SET work_mem TO '10GB'") # Speeds-up creation of temporary tables

start_date <- '1995-01-01' # begin data from

# Define PG data ----

# Project schema
rs <- dbGetQuery(pg, "SET search_path TO nonans_product, public")

# Outside of project schema
company_fyear <- tbl(pg, sql("SELECT * FROM exec_comp_2016.company_fyear"))
dsf <- tbl(pg, sql("SELECT * FROM crsp.dsf"))

# Get data ----

# Daily stock file
dsf <- 
    dsf %>%
    filter(date > start_date) %>%
    select(permno, date, prc, ret, vol, cfacpr, cfacshr) %>%
    mutate(year = sql("extract(year from date)"), 
           month = sql("extract(month from date)")) %>%
    compute(index = list(c("permno"), 
                         c("permno", "date"), 
                         c("permno", "year", "month")))

# Get link table with fy_end
company_dates <- 
    company_fyear %>%
    mutate(year = sql("extract(year from fy_end)"), 
           month = sql("extract(month from fy_end)")) %>%
    distinct() %>%
    compute(index = list("permno", 
                         c("permno", "fy_end"),
                         c("permno", "year", "month")))

# Compute volatility and returns at fiscal year end ----

# Compute annualized volatility of returns
sdret_12 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (fy_end - interval '12 month') <= date AND date < fy_end")) %>%
    distinct() %>%
    group_by(permno, fy_end) %>%
    summarize(sdret_12 = sqrt(250)*sd(log(1 + ret)), 
              n_sdret_12 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "fy_end")))

# Compute cumulative returns
ret_12 <- 
    company_dates %>%
    left_join(dsf, by = "permno") %>%
    filter(sql("ret > -1 AND (fy_end - interval '12 month') <= date AND date < fy_end")) %>%
    distinct() %>%
    group_by(permno, fy_end) %>%
    summarize(ret_12 = exp(sum(log(1 + ret))) - 1, 
              n_ret_12 = n()) %>%
    ungroup() %>%
    compute(index = list("permno", c("permno", "fy_end")))

dbGetQuery(pg, "DROP TABLE IF EXISTS crsp_fye")

# Merge parameters
crsp <- 
    company_dates %>%
    left_join(sdret_12) %>%
    left_join(ret_12) %>%
    # Restirctions on n_xxx_12 observations
    mutate(sdret_12 = if_else(n_sdret_12 < 248, NA, sdret_12),
           ret_12 = if_else(n_ret_12 < 248, NA, ret_12)) %>%
    select(permno, fy_end,
           ret_12, n_ret_12, sdret_12, n_sdret_12) %>%
    compute(name = "crsp_fye", temporary = FALSE, 
            index = list("permno", c("permno", "fy_end")))

db_comment <- paste0("CREATED USING azakolyukina/nonans_product/create_crsp_fye.R ON ", Sys.time())
dbGetQuery(pg, paste0("COMMENT ON TABLE crsp_fye IS '",
                      db_comment, "';"))

dbGetQuery(pg, "ALTER TABLE crsp_fye OWNER TO nonans_product")
dbGetQuery(pg, "GRANT SELECT ON crsp_fye TO nonans_product_access")
dbGetQuery(pg, "VACUUM ANALYZE crsp_fye")

dbDisconnect(pg)
