#####
# Financial Disclosure: Capital IQ fuzzy matches
#####

rm(list=ls())

library("readxl")
library("dplyr")
library("stringr") 
library(tidyr)
library(reshape2)


####
# Step 1: Prepare raw asset names for Capital IQ
####

fd <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/Financial Disclosure/FinancialDisclosure.csv', header=FALSE, quote="", stringsAsFactors = F)
fd$i <- seq(1, nrow(fd))
fd$rowid_findisc <- paste0("findisc_", fd$i)
fd <- select(fd, -c('i'))

fd <- rename(fd, 'judge' = V2, 'year' = V3, 'asset_orig' = V5, 'income_amount' = V6, 'income_type' = V7, 'gross_value' = V8, 'value_method' = V9, 'type_trans' = V10)
fd <- select(fd, judge, year, asset_orig, income_amount:type_trans, rowid_findisc)

fd <- fd %>% mutate(asset_new = str_replace(asset_orig, "^\\S* ", "")) 
fd$asset_new <- gsub ("\\-","", fd$asset_new) 
fd$asset_new <- gsub ("\\?","", fd$asset_new) 
fd$asset_new <- gsub("t1 ","", fd$asset_new)
fd$asset_new <- gsub("t2 ","", fd$asset_new)
fd$asset_new <- gsub("s6 ","", fd$asset_new)
fd$asset_new <- gsub("s5 ","", fd$asset_new)
fd$asset_new <- gsub("s2 ","", fd$asset_new)
fd$asset_new <- gsub(" x","", fd$asset_new)
fd$asset_new <- gsub ("\\s+", " ", fd$asset_new) 
fd$asset_new <- str_replace_all(fd$asset_new, "[^[:alnum:]]", " ") 
  containsOnlyNumbers = function(x) !is.na(as.numeric(x)) 
  fd$numbersonly <- as.numeric(containsOnlyNumbers(fd$asset_new)) 
  fd$asset_new <- ifelse(fd$numbersonly==1,"", fd$asset_new)
    fd$char_to_first_space <- sub("\\s.*","",fd$asset_new) 
    fd$remove_lead <- as.numeric(containsOnlyNumbers(fd$char_to_first_space))
    fd$asset_new <- ifelse(fd$remove_lead==1, sub(".*? ", "", fd$asset_new), fd$asset_new) 
fd$asset_new <- str_trim(fd$asset_new, side = c("both", "left", "right")) 
fd$asset_new <- tolower(fd$asset_new)
fd$asset_new <- gsub("cont d","", fd$asset_new)

fd_clean <- fd

fd_clean$asset_new <- gsub("stock","", ignore.case=TRUE, fd_clean$asset_new) # 
fd_clean$asset_new <- gsub("common","", ignore.case=TRUE, fd_clean$asset_new) #
fd_clean$asset_new <- gsub("shares","", ignore.case=TRUE, fd_clean$asset_new) # 

fd_clean_capiq <- fd_clean
write.csv(fd_clean_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/FinancialDisclosure_clean_capiq.csv')

fd_CapIQ <- distinct(fd, asset_new, .keep_all = TRUE)
fd_CapIQ$info <- tolower(paste0(fd_CapIQ$income_amount," ", fd_CapIQ$income_type, " ", 
                        fd_CapIQ$gross_value ," ", fd_CapIQ$value_method ," ", fd_CapIQ$type_trans)) # 302955

#########
# Step 2: Add flags for unlikely common stock (CS)
#########
  
fd_CapIQ$interest_flag <- as.integer(grepl(pattern = "interest", x = fd_CapIQ$info))

fd_CapIQ$mf_flag <- as.integer(grepl(pattern = "mutual fund", x = fd_CapIQ$info))

fd_CapIQ$mf_flag <- ifelse(as.integer(grepl(pattern = "mutual fund", x = fd_CapIQ$asset_new))==1,1,fd_CapIQ$mf_flag)

fd_CapIQ$noncs_flag <- ifelse(as.integer(grepl(pattern = "retirement", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " ira", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "ira ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "bond", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " bd ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "fund", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " fd ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " cd ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "emerging", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "small cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "sm cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "mid cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "md cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "large cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "lg cap", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "checking account", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "money market", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "money mkt", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "index", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " muni ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "municipal", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "county", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " etf", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " tax", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "working interest", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "annuity", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "option", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "deferred comp", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "real prop", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "certificate", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " note", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " reit", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = " bill ", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "blank", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "pension", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "property", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "portfolio", x = fd_CapIQ$asset_new))==1|
                                as.integer(grepl(pattern = "coupon", x = fd_CapIQ$asset_new))==1
                                ,1,0)

#########
# Step 3: Add flags for likely common stock (CS)
#########

fd_CapIQ$dividend_flag <- as.integer(grepl(pattern = "dividend", x = fd_CapIQ$info))

fd_CapIQ$cs_flag <- as.integer(grepl(pattern = "stock", x = fd_CapIQ$info))

fd_CapIQ$cs_flag <- ifelse(as.integer(grepl(pattern = "stock", x = fd_CapIQ$asset_new))==1 |
                             as.integer(grepl(pattern = "shares", x = fd_CapIQ$asset_new))==1 |
                             as.integer(grepl(pattern = "common", x = fd_CapIQ$asset_new))==1 |
                             as.integer(grepl(pattern = " cs", x = fd_CapIQ$asset_new))==1,1,fd_CapIQ$cs_flag)

#########
# Step 4: Output datasets for likely CS, unlikely CS, and unsure CS
#########

# unlikely
fd_CapIQ$unlikely_cs <- ifelse(fd_CapIQ$noncs_flag==1 | fd_CapIQ$mf_flag==1 | fd_CapIQ$interest_flag==1, 1, 0)
unlikely_cs_capiq <- fd_CapIQ[fd_CapIQ$unlikely_cs==1,] 

write.csv(unlikely_cs_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unlikely_cs_capiq.csv')

# likely
fd_CapIQ$likely_cs <- ifelse(fd_CapIQ$unlikely_cs==0 & (fd_CapIQ$cs_flag==1 | fd_CapIQ$dividend_flag==1), 1, 0)
likely_cs_capiq <- fd_CapIQ[fd_CapIQ$likely_cs==1,] 
likely_cs_capiq$asset_capIQ <- likely_cs_capiq$asset_new
likely_cs_capiq$asset_capIQ <- gsub("stock","", ignore.case=TRUE, likely_cs_capiq$asset_capIQ) # 
likely_cs_capiq$asset_capIQ <- gsub("common","", ignore.case=TRUE, likely_cs_capiq$asset_capIQ) # 
likely_cs_capiq$asset_capIQ <- gsub("shares","", ignore.case=TRUE, likely_cs_capiq$asset_capIQ) # 

write.csv(likely_cs_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/likely_cs_capiq.csv')

# unsure
fd_CapIQ$unsure_cs <- ifelse(fd_CapIQ$unlikely_cs==0 & fd_CapIQ$likely_cs==0,1,0)

length(which(fd_CapIQ$unlikely_cs==1)) + length(which(fd_CapIQ$likely_cs==1)) + length(which(fd_CapIQ$unsure_cs==1)) == nrow(fd_CapIQ)

unsure_cs_capiq <- fd_CapIQ[fd_CapIQ$unsure_cs==1,]

write.csv(unsure_cs_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unsure_cs_capiq.csv')

#########
# Step 5: CapIQ results
#########

## Likely

likely <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/likely_cs_capiq_output.csv',header=TRUE, check.names = FALSE, stringsAsFactors = F)
likely$id <- paste("L", seq(1:nrow(likely)), sep="_")
likely <- select(likely, id:'IQ_ISIN', rowid_findisc) #

## Unlikely

unlikely <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unlikely_cs_capiq_output.csv',header=TRUE, check.names = FALSE, stringsAsFactors = F)
unlikely$id <- paste("U", seq(1:nrow(unlikely)), sep="_")
unlikely <- select(unlikely, id:'IQ_ISIN', rowid_findisc)

## Unsure

unsure <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unsure_cs_capiq_output.csv',header=TRUE, check.names = FALSE, stringsAsFactors = F)
unsure$id <- paste("S", seq(1:nrow(unsure)), sep="_")
unsure <- select(unsure, id:'IQ_ISIN', rowid_findisc)

# append
tot <- rbind(likely, unlikely, unsure)

tot$likely <- as.numeric(grepl('L', tot$id, fixed = TRUE))
tot$unlikely <- as.numeric(grepl('U', tot$id, fixed = TRUE))
tot$unsure <-  as.numeric(grepl('S', tot$id, fixed = TRUE))
length(unique(tot$id))==nrow(tot) # TRUE

tot2 <- tot[tot$unlikely==0,]
matched_capiq <- tot2[tot2$Ticker!="-",]
unmatched_capiq <- tot2[tot2$Ticker=="-",]

write.csv(matched_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/matched_L.S_capiq.csv')
write.csv(unmatched_capiq, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unmatched_L.S_capiq.csv')

#########
# Step 6: Updating unmatched rows with additional fuzzy matching
#########

# unmatched data to fuzzy match
unmatched <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/unmatched_L.S_capiq.csv', stringsAsFactors = F) 
o1 <- read_xlsx('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/likely_cs/unmatched_L.S._output/data_matching_output_1.xlsx') 
o2 <- read_xlsx('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/likely_cs/unmatched_L.S._output/data_matching_output2.xlsx') 

o1 <- select(o1, unmatched_file_id, crsp_file_rowno, crsp_name, permo)
o1 <- rename(o1, 'permno' = 'permo') # fix typo in variable name
o2 <- select(o2, unmatched_file_id, crsp_file_rowno, crsp_name, permno)
  o2$unmatched_file_id <- ifelse(o2$unmatched_file_id=='L-3420', 'L_3420', o2$unmatched_file_id) # 
o <- rbind(o1,o2)
o <- distinct(o, unmatched_file_id, .keep_all = T)
  
unmatched <- merge(unmatched, o, by.x = 'id', by.y = 'unmatched_file_id', all.x=T)

#  matches to capiq
unmatched_still <- unmatched[is.na(unmatched$permno),]
matched_MLupdate <- unmatched[!is.na(unmatched$permno),]

matched <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/matched_L.S_capiq.csv', stringsAsFactors = F) #
matched$crsp_file_rowno <- NA
matched$crsp_name <- NA
matched$permno <- NA

matched_MLupdate <- rbind(matched_MLupdate, matched)
matched_MLupdate$match_name <- matched_MLupdate$Result.Name
matched_MLupdate$match_name <- ifelse(matched_MLupdate$Ticker=="-", matched_MLupdate$crsp_name, matched_MLupdate$match_name)

(as.numeric(nrow(matched_MLupdate)) + as.numeric(nrow(unmatched_still))) ==  (as.numeric(nrow(matched)) + as.numeric(nrow(unmatched))) # 

write.csv(matched_MLupdate, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/matched_L.S._MLupdate_capiq.csv')

ra <- matched_MLupdate
ra <- dplyr::select(ra, id, Search.Item.Input, match_name, permno, Ticker, IQ_GVKEY, CIQ.ID, rowid_findisc)
write.csv(ra, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/ra_eyeball_capiq.csv')

#########
# Step 7: overall match to gvkeys
#########

matched_MLupdate <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/matched_L.S._MLupdate_capiq.csv', stringsAsFactors = F)
fd_clean <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/FinancialDisclosure_clean_capiq.csv', stringsAsFactors = F)

matched_MLupdate <- select(matched_MLupdate, id, Search.Item.Input, Ticker, CIQ.ID, IQ_GVKEY, IQ_ISIN, permno)
fd_clean <- select(fd_clean, judge, year, asset_orig, asset_new, rowid_findisc)

own <- merge(fd_clean, matched_MLupdate, by.x = c('asset_new'), by.y = c('Search.Item.Input'))

# check
eyeball_check <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/likely_cs/labeled_ra_eyeball.csv')
eyeball_check <- select(eyeball_check, id, Manual.Check)
eyeball_check <- eyeball_check[eyeball_check$Manual.Check==1,]
own <- merge(own, eyeball_check, by.x = c('id'), by.y = c('id'))

own$likely <- as.numeric(grepl('L', own$id, fixed = TRUE))
own$unique_gvkey_permno <- paste0(as.character(own$IQ_GVKEY), as.character(own$permno))
  
own <- distinct(own, unique_gvkey_permno, judge, year, .keep_all = T) #
 own$number <- as.numeric(own$asset_new)
 own <- own[is.na(own$number),]

fd_clean <- distinct(fd_clean, asset_new, judge, year, .keep_all = T)
own <- select(own, -c(unique_gvkey_permno))

############
# Step 8: Link permnos to gvkeys
############

iden <- read.csv('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/Fraud_data_merges/ccmfunda_199501_202012.csv') 
iden <- select(iden, GVKEY, LPERMNO, LPERMCO, fyear, tic, cusip, conm, cik)

own_gvkey <- own[is.na(own$permno),]
own_permno <- own[!is.na(own$permno),] 

own_permno_merge <- merge(own_permno, iden, by.x = c('permno'), by.y = c('LPERMNO'))
own_permno_merge <- dplyr::distinct(own_permno_merge, permno, .keep_all=T)
own_permno_merge <- select(own_permno_merge, permno, GVKEY)
own_permno_update <- merge(own_permno, own_permno_merge, by.x = c('permno'), by.y = ('permno'), all.x = T)
temp <- own_permno_update[is.na(own_permno_update$GVKEY),]
temp <- distinct(temp, permno, .keep_all = T)

#hand-check
temp_update <- read_excel('C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/temp_permno/temp_gvkey_update.xlsx')
temp_update <- select(temp_update, permno, gvkey_handmatch_from_conm)

own_permno_update <- merge(own_permno_update, temp_update, by= c('permno'), all.x=T)
own_permno_update$gvkey <- ifelse( is.na(own_permno_update$GVKEY), own_permno_update$gvkey_handmatch_from_conm,
                                   own_permno_update$GVKEY)
own_permno_update <- select(own_permno_update, -c('GVKEY', 'gvkey_handmatch_from_conm'))

own_gvkey$gvkey <- gsub("GV_","", ignore.case=TRUE, own_gvkey$IQ_GVKEY)  

own_updated <-rbind(own_gvkey, own_permno_update)

own_updated <- own_updated[which(own_updated$gvkey!="NA"),]
own_updated <- separate(own_updated, gvkey, into=c('first', 'second', 'third', 'fourth', 'fifth'), sep="\\,", remove=F)
own_updated <- select(own_updated, -c('fifth'))

  own_updated_1gvkey <- own_updated[is.na(own_updated$second),] 
  own_updated_1gvkey$gvkey_order <- "first"
  own_updated_1gvkey$gvkey_value <- own_updated_1gvkey$gvkey 
  
  own_updated_plus2gvkey <- own_updated[!is.na(own_updated$second),] 
  own_updated_plus2gvkey$id_melt <- as.character(seq(1:nrow(own_updated_plus2gvkey)))
  plus2gvkey_reshape <- melt(own_updated_plus2gvkey, 
                             id.vars="id_melt", 
                            measure.vars =c("first", "second", "third", "fourth"),
                            variable.name="gvkey_order",
                            value.name = "gvkey_value",
                            variable.factor=FALSE) 
  own_updated_plus2final <-  merge(plus2gvkey_reshape, own_updated_plus2gvkey, by = 'id_melt', all.x = TRUE)                            
  nrow(own_updated_plus2final) == nrow(plus2gvkey_reshape) # TRUE
  own_updated_plus2final <- select(own_updated_plus2final, -c('id_melt'))
  
  own_updated_allgvkey <- rbind(own_updated_1gvkey, own_updated_plus2final)
  
own_updated_allgvkey$gvkey_value <- as.character(as.numeric(own_updated_allgvkey$gvkey_value)) 
own_updated_allgvkey <- select(own_updated_allgvkey, -c('gvkey', 'first', 'second', 'third', 'fourth'))
own_updated_allgvkey <- own_updated_allgvkey[!is.na(own_updated_allgvkey$gvkey_value),] 

own_updated_allgvkey <- distinct(own_updated_allgvkey, judge, year, gvkey_value, .keep_all = T)

write.csv(own_updated_allgvkey, 'C:/Users/SCHERF/Dropbox/_AJudge Research Project/x__Old_March2023/CapitalIQ/capiq/own_updated_allgvkey_final.csv')

