Mutual fund main variables data manipulation.

MutualFunds.R

#####
## Load packages
#library(gdata)
library(readxl)
library(data.table)
library(tidyr)
library(magrittr)
library(SIT)

#####
##
#tic(1)
#folder <- '~/IGEE/FondosMutuos/Variables/'
#files <- list.files(path = folder, pattern = 'xls')
#for (i in files){
#  filename <- paste(folder, i, sep = '')
#  assign(paste('mf',substr(i,1,4),sep = ''), read_excel(filename, 1, col_names = T))
#}
#remove(files, folder, filename, i)
#toc(1)

tic(1)
folder <- '~/IGEE/FondosMutuos/Variables/'
files <- list.files(path = folder, pattern = 'xls')
for (i in files){
  filename <- paste(folder, i, sep = '')
  # if the merged dataset doesn't exist, create it
  if (!exists("dataset")){
    dataset <- read_excel(filename, 1, col_names = T)
  }
  # if the merged dataset does exist, append to it
  if (exists("dataset")){
    temp_dataset <- read_excel(filename, 1, col_names = T)
    dataset <- rbind(dataset, temp_dataset)
    rm(temp_dataset)
  }
}
remove(files, folder, filename, i)
toc(1)

#####
##
#tic(2)
#mf <- data.frame()
#for (i in files){
#  varnames <- paste('mf', substr(i,1,4), sep = '')
#  mf <- rbind(mf, get(varnames))
#}
#remove(i)
#toc(2)
#
dataset <- dataset[,!names(dataset) %in% "flagfm_fi"]
dataset <- dataset[,-(3:4)]
dataset$codigopj <- as.integer(dataset$codigopj)
dataset$codfondo <- as.integer(dataset$codfondo)
dataset$ccodcarfm <- as.integer(dataset$ccodcarfm)
dataset$nparticipes <- as.integer(dataset$nparticipes)
dataset %<>% unite(codFund, codigopj, codfondo, sep = '-', remove = F)
colnames(dataset) <- c('codFund', 'codAdm', 'codMF', 'Adm', 'Fund', 'Clients', 'NAV', 'Assets', 'Price', 'Units', 'FX', 'codClass', 'Date', 'Class', 'Currency')
dataset$Currency <- as.character(dataset$Currency)
dataset$Currency[dataset$Currency=='NUEVOS SOLES' | dataset$Currency=='SOLES'] <- 'PEN'
dataset$Currency[dataset$Currency=='DOLARES'] <- 'USD'
dataset$Date <- as.Date(dataset$Date, '%Y-%m-%d')
# Remove NAs and zeros
dataset[c('Assets', 'Units')][dataset[c('Assets', 'Units')] == 0] <- NA
dataset <- na.omit(dataset)

#####
## Asset classes
assetclass <- unique(data.frame(dataset$codClass, dataset$Class))
names(assetclass) <- c('codClass', 'Class')
assetclass <- arrange(assetclass, codClass, Class)
## Asset managers
adminfund <- unique(data.frame(dataset$codAdm, dataset$Adm))
names(adminfund) <- c('codAdm', 'Admin')
adminfund <- arrange(adminfund, codAdm, Admin)
## Mutual funds
mutualfund <- unique(data.frame(dataset$codAdm, dataset$codMF, dataset$codFund, dataset$Fund, dataset$Currency))
names(mutualfund) <- c('codAdm', 'codMF', 'codFund', 'Fund', 'Currency')
mutualfund <- arrange(mutualfund, codAdm, codMF, codFund)
## FX Rate
fxrate <- unique(data.frame(dataset$Date, dataset$FX))
names(fxrate) <- c('Date', 'USDPEN')
fxrate <- arrange(fxrate, Date)
fxrate$USDPEN[fxrate$USDPEN==1]<- NA
fxrate <- na.omit(fxrate)

#####
##