#PROJECT-2: MF ANALYSIS INDIA General Analysis of the MF’s of India.Data used is taken from open data available on various sites. 

knitr::opts_chunk$set(root.dir = "C:/Users/rsman/Documents/PERS FOLDERS/RAJDEEP/INVESTMENTS/ANALYSIS/DATA")
library(lubridate)
library(stringr)
library(pander)
library(data.table)
library(xlsx)
library(dplyr)
library(scales)
library(ggplot2)
library(Quandl)
library(tabulizer)
library(knitr)
knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(root.dir = "C:/Users/rsman/Documents/PERS FOLDERS/RAJDEEP/INVESTMENTS/ANALYSIS/DATA")
getwd()

[1] “C:/Users/rsman/Documents/PERS FOLDERS/RAJDEEP/INVESTMENTS/ANALYSIS/DATA”

#Data from Quandl
#qdl.link <- "https://www.quandl.com/api/v3/databases/AMFI/metadata?api_key=yKy3ZHB-brjUqC83QqRn"
#temp.amfi.data <- tempfile()
#download.file(qdl.link,temp.amfi.data)
#data <- read.table(unz(temp, "a1.dat"))
#unlink(temp)

###Functions
colFmt1 = function(x,color){
  outputFormat = knitr::opts_knit$get("rmarkdown.pandoc.to")
  if(outputFormat == 'latex')
    paste("\\textcolor{",color,"}{",x,"}",sep="")
  else if(outputFormat == 'html')
    paste("<font color='",color,"'>",x,"</font>",sep="")
  else
    x
}

colFmt = function(x,color){
  outputFormat = knitr::opts_knit$get("rmarkdown.pandoc.to")
  if(outputFormat == 'html')
    paste("<font color='",color,"'>",x,"</font>",sep="")
  else
    x
}

makeCharac <- function(rx.vr){
  new.vr <- as.character(rx.vr)
  return(new.vr)
}

###End Functions


pandoc.header(colFmt("Section-1: India's Mutual Funds-General Details", 'red'), 1, 'setext')

Section-1: India’s Mutual Funds-General Details

CWD <- "C:/Users/rsman/Documents/PERS FOLDERS/RAJDEEP/INVESTMENTS/ANALYSIS/DATA/"
setwd("C:/Users/rsman/Documents/PERS FOLDERS/RAJDEEP/INVESTMENTS/ANALYSIS/DATA")

mfdetails <- as.data.frame(read.csv(unzip("AMFI_metadata.csv.zip"), sep = ","))#AMFI Data
mfportfolio <- as.data.frame(read.xlsx("selectedmf.xlsx", sheetName = "Sheet1"))#My selected MF's
amcdetails <- as.data.frame(read.xlsx("amfi_amc.xlsx", sheetName = "Sheet1"))#Fund Companies
aum1 <- as.data.frame(read.xlsx("amfi_aum_amcwise_one.xlsx", sheetIndex = 1))# AUM of Period-1
aum2 <- as.data.frame(read.xlsx("amfi_aum_amcwise_two.xlsx", sheetIndex = 1))#AUM of Period-2

amcdetails <- amcdetails[-which(is.na(amcdetails$AMC)),]


#mfdetails <- read.csv("MFData.csv", sep = ",")
mfdetails$from_date <- as.Date(mfdetails$from_date)
mfdetails$to_date <- as.Date(mfdetails$to_date)

##No of MF per AMC
mfdetails$n_company <- trimws(substr(mfdetails[,2],1, str_locate(mfdetails[,2],"-")-2))
mfdetails[grep(";", mfdetails[,"n_company"]),"n_company"]<- 
  substr(mfdetails[grep(";", mfdetails[,"n_company"]),"n_company"],str_locate(mfdetails[grep(";", mfdetails[,"n_company"]),"n_company"],";")+1 ,500)

mfdata <- mfdetails
mfdata$n_company <- trimws(substr(mfdetails[,2],1, str_locate(mfdetails[,2],"-")-2))
mfdata[grep(";", mfdata[,"n_company"]),"n_company"]<- 
  substr(mfdata[grep(";", mfdata[,"n_company"]),"n_company"],str_locate(mfdata[grep(";", mfdata[,"n_company"]),"n_company"],";")+1 ,500)

df.tmp <- mfdata[mfdata$to_date >= Sys.Date()-30,]
df.coys <- data.frame(tapply(df.tmp$name, df.tmp$n_company, length))
rm(df.tmp)
df.coys$company <- rownames(df.coys)
df.coys$no_of_funds <- df.coys$tapply.df.tmp.name..df.tmp.n_company..length.
#rownames(df.coys) <- rep(1:dim(df.coys$no_of_funds))
rownames(df.coys) <- rep(1:length(df.coys$no_of_funds))
df.coys <- df.coys[,-1]
df.coys <- subset(df.coys, company %in% amcdetails$AMC)

#MF Gen Details
mf.gen.df <- data.frame(matrix(ncol = 2, nrow = 0))
colnames(mf.gen.df) <- c("Details", "Data") 

tmp.df <- data.frame("Details" = "Data Since", "Data" = as.character(min(mfdata$from_date)))
tmp.df$Details <- as.character(tmp.df$Details)
tmp.df$Data <- as.character(tmp.df$Data)
mf.gen.df <- rbind(mf.gen.df, tmp.df)

tmp.df <- data.frame("Details" = "No of Asset Management Companies", "Data" = nrow(df.coys))
mf.gen.df <- rbind(mf.gen.df, tmp.df)

tmp.df <- data.frame("Details" = "No of MF's as on today", "Data" = nrow(mfdata[mfdata$to_date >= Sys.Date()-30,]))
mf.gen.df <- rbind(mf.gen.df, tmp.df)

min_dt <- min(mfdata$from_date)
tmp.df <- data.frame("Details" = paste("No of MF's launched on or before ", min_dt," and still active"), "Data" = nrow(mfdata[mfdata$from_date==min_dt & mfdata$to_date >= Sys.Date()-30,]))
mf.gen.df <- rbind(mf.gen.df, tmp.df)


dt.var <- as.Date(gsub(" ","",paste(as.character(year(Sys.Date())),"-01-01")))
dt.var.lst <- as.Date(gsub(" ","",paste(as.character(year(Sys.Date())-1),"-01-01")))

tmp.df <- data.frame("Details" = paste("MF's Launched in ", year(dt.var.lst)), 
                     "Data" = nrow(mfdata[year(mfdata$from_date) == year(dt.var.lst),]))
mf.gen.df <- rbind(mf.gen.df, tmp.df)

tmp.df <- data.frame("Details" = paste("MF's Launched in ", 
                                       year(Sys.Date()), " till now"),"Data" = nrow(mfdata[mfdata$from_date >= dt.var,]))
mf.gen.df <- rbind(mf.gen.df, tmp.df)


pandoc.header('1A. Mutual Funds Summary', 1, 'setext')

1A. Mutual Funds Summary

#pander(mf.gen.df,justify = c("left"))
knitr::kable(mf.gen.df, caption = colFmt("MF's General Details", "blue"))
MF’s General Details
Details Data
Data Since 2006-04-01
No of Asset Management Companies 37
No of MF’s as on today 5983
No of MF’s launched on or before 2006-04-01 and still active 36
MF’s Launched in 2019 1124
MF’s Launched in 2020 till now 398 
write.xlsx(mf.gen.df, file = "MFDETAILS.xlsx", sheetName = "GenDetails")
rm(mf.gen.df)
rm(tmp.df)


pandoc.header("1B.Top 10 Asset Management Companies(AMC's) by Number of Funds", 1, 'setext')

1B.Top 10 Asset Management Companies(AMC’s) by Number of Funds

df.coys <- df.coys[order(df.coys$no_of_funds, decreasing = TRUE),]
rownames(df.coys) <- NULL 
knitr::kable(df.coys[1:10,], caption = colFmt("Top 10 AMC's by Number of Funds", "blue"))
Top 10 AMC’s by Number of Funds
company no_of_funds
ICICI Prudential Mutual Fund 383
UTI Mutual Fund 379
Aditya Birla Sun Life Mutual Fund 302
Franklin Templeton Mutual Fund 293
IDFC Mutual Fund 255
Sundaram Mutual Fund 235
SBI Mutual Fund 231
DSP Mutual Fund 223
Axis Mutual Fund 214
Tata Mutual Fund 210 
mf.yrly.calc <- function(mfdata, rx.yr){
  
  mfdata <- mfdata[year(mfdata$from_date) <= rx.yr & mfdata$to_date >= Sys.Date()-30,]
  
  df.coys.tmp <- data.frame(tapply(mfdata$name, mfdata$n_company, length))
  df.coys.tmp$company <- rownames(df.coys.tmp)
  df.coys.tmp$no_of_funds <- df.coys.tmp[,1]
  rownames(df.coys.tmp) <- rep(1:nrow(df.coys.tmp))
  df.coys.tmp <- df.coys.tmp[,-1]
  colnames(df.coys.tmp) <- c("company", rx.yr)
  
  plot.data <- df.coys.tmp
  plot.data$year <- rx.yr
 
  return(list(df.coys.tmp, plot.data))
  
}

max.yr <- max(year(mfdetails$from_date))-1
st.yr <- max.yr-10
plot.data <- data.frame(matrix(ncol =3, nrow =0))
colnames(plot.data) <- c("company", "no_of_funds", "year")

for (i in st.yr: max.yr) {
  rx.lst <- mf.yrly.calc(mfdata[mfdata$to_date >= Sys.Date()-30,], i)
  
  fn.df <- data.frame(rx.lst[1])
  colnames(fn.df) <- c("company", i)
  df.coys <- left_join(df.coys, fn.df, by = "company")
  df.coys[is.na(df.coys)] <- 0
  
  tmp.df <- data.frame(rx.lst[2])
  colnames(tmp.df) <- c("company", "no_of_funds", "year")
  plot.data <- rbind(plot.data, tmp.df)
  
}

pandoc.header(paste("1C.Top 10 AMC's from ", st.yr ," to ", max.yr), 1, 'setext')

1C.Top 10 AMC’s from 2009 to 2019

df.coys <- df.coys[order(df.coys$no_of_funds, decreasing = TRUE),]
rownames(df.coys) <- NULL 

ctr <- ncol(df.coys)
knitr::kable(df.coys[1:10,c(1,3:ctr,2)], caption = colFmt(paste("Top 10 MF Companies and their absolute growth from ",st.yr, " to ", max.yr), "blue"))
Top 10 MF Companies and their absolute growth from 2009 to 2019
company 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 no_of_funds
ICICI Prudential Mutual Fund 84 101 109 114 242 278 290 297 303 329 375 383
UTI Mutual Fund 86 89 90 92 167 183 229 247 256 289 303 379
Aditya Birla Sun Life Mutual Fund 92 94 96 98 189 225 239 241 241 249 298 302
Franklin Templeton Mutual Fund 87 88 90 97 173 187 187 189 189 197 205 293
IDFC Mutual Fund 52 64 70 74 153 178 186 204 220 236 249 255
Sundaram Mutual Fund 78 83 83 83 142 146 148 155 161 184 217 235
SBI Mutual Fund 92 95 98 99 198 200 218 219 219 221 231 231
DSP Mutual Fund 52 57 59 65 144 160 170 182 186 205 223 223
Axis Mutual Fund 10 33 40 52 95 105 122 124 143 165 199 214
Tata Mutual Fund 42 43 47 47 92 94 126 126 126 138 198 210 
tmp.df <- df.coys[1:10,] #Name of Top 10 companies
plot.1 <- subset(plot.data, company %in% tmp.df$company)

ggplot(plot.1, aes(x=year, y= no_of_funds)) + 
  geom_line(aes(colour=company, group=company))+
  geom_point(aes(colour=company), size=3)+scale_fill_brewer(palette = "Set1")+
  theme_classic()+
  labs(subtitle = "ss")

rm(plot.1)

#calculate the Percent growth
ctr <- ncol(tmp.df)
tmp.df.change <- data.frame(tmp.df[,1])
colnames(tmp.df.change) <- c("company")
tmp.df.percent <- data.frame(matrix(ncol = 3, nrow = 0))
colnames(tmp.df.percent) <- c("company","growth", "period")



for (i in 3:(ctr-1)) {
  
  new.name <- paste(colnames(tmp.df[i]),"-",colnames(tmp.df[i+1]))
  name.str <- colnames(tmp.df.change)
  new.name <- gsub(" ","", new.name)
  
  new.data <- ((tmp.df[i+1] - tmp.df[i])/tmp.df[i])*100
  tmp.df.change <- cbind(tmp.df.change, new.data)
  name.str <- c(name.str, new.name)
  colnames(tmp.df.change) <- name.str
  
  c1<- tmp.df.change[1]
  c2<- data.frame(new.data)
  new.df.percnet <- data.frame(c1, c2, new.name)
  colnames(new.df.percnet) <- c("company","growth", "period")
  tmp.df.percent <- rbind(tmp.df.percent, new.df.percnet)
  
}
ctr <- ncol(tmp.df.change)
tmp.df.change$avg_growth <- rowMeans(tmp.df.change[,c(2:ctr)])
 
pandoc.header(paste('1D.Top 10 AMC by Percent Growth', st.yr ,' to ', max.yr), 1, 'setext')

1D.Top 10 AMC by Percent Growth 2009 to 2019

options(digits = 3)
knitr::kable(tmp.df.change, caption = colFmt(paste("Top 10 MF Companies and their Percent growth from ",st.yr, " to ", max.yr),"blue"))
Top 10 MF Companies and their Percent growth from 2009 to 2019
company 2009-2010 2010-2011 2011-2012 2012-2013 2013-2014 2014-2015 2015-2016 2016-2017 2017-2018 2018-2019 avg_growth
ICICI Prudential Mutual Fund 20.24 7.92 4.59 112.3 14.88 4.32 2.414 2.02 8.581 13.98 19.1
UTI Mutual Fund 3.49 1.12 2.22 81.5 9.58 25.14 7.860 3.64 12.891 4.84 15.2
Aditya Birla Sun Life Mutual Fund 2.17 2.13 2.08 92.9 19.05 6.22 0.837 0.00 3.320 19.68 14.8
Franklin Templeton Mutual Fund 1.15 2.27 7.78 78.4 8.09 0.00 1.070 0.00 4.233 4.06 10.7
IDFC Mutual Fund 23.08 9.38 5.71 106.8 16.34 4.49 9.677 7.84 7.273 5.51 19.6
Sundaram Mutual Fund 6.41 0.00 0.00 71.1 2.82 1.37 4.730 3.87 14.286 17.93 12.2
SBI Mutual Fund 3.26 3.16 1.02 100.0 1.01 9.00 0.459 0.00 0.913 4.53 12.3
DSP Mutual Fund 9.62 3.51 10.17 121.5 11.11 6.25 7.059 2.20 10.215 8.78 19.0
Axis Mutual Fund 230.00 21.21 30.00 82.7 10.53 16.19 1.639 15.32 15.385 20.61 44.4
Tata Mutual Fund 2.38 9.30 0.00 95.7 2.17 34.04 0.000 0.00 9.524 43.48 19.7 
ggplot(tmp.df.percent, aes(x=period, y= growth)) + 
  geom_line(aes(colour=company, group=company))+
  geom_point(aes(colour=company), size=3)+scale_fill_brewer(palette = "Set1")+
  theme_classic()+  theme(axis.text.x=element_text(angle = 90, hjust = 0))+
  labs(subtitle = "ss")

rm(tmp.df.change)
rm(tmp.df.percent)

cat("Looking at the chart above we can see that all the AMC's have been launching new funds in an identical manner. There was one spike in the year 2013 and there was a significant rise in the number of funds launched by AMC's. This data is being analsyed further in the document.   ")

Looking at the chart above we can see that all the AMC’s have been launching new funds in an identical manner. There was one spike in the year 2013 and there was a significant rise in the number of funds launched by AMC’s. This data is being analsyed further in the document.

#New Launch trend monthwise
mf.mthly.df <- data.frame(matrix(ncol = 4, nrow = 0))
colnames(mf.mthly.df) <- c("company", "no_of_funds", "month", "year") 

mf.mthly.calc <- function(rx.df, rx.yr){
  
  mf.mthly.df <- data.frame(matrix(ncol = 4, nrow = 0))
  colnames(mf.mthly.df) <- c("company", "no_of_funds", "month", "year") 
  lpr.df <- rx.df[year(rx.df$from_date) == rx.yr,]
  for (i in 1:12) {
    
    loop.df <- lpr.df[month(lpr.df$from_date)==i,]
    df.coys.tmp <- data.frame(tapply(loop.df$name, loop.df$n_company, length))
    df.coys.tmp$company <- rownames(df.coys.tmp)
    df.coys.tmp$no_of_funds <- df.coys.tmp[,1]
    df.coys.tmp$month <- month.name[i]
    df.coys.tmp$year <- rx.yr
    df.coys.tmp <- df.coys.tmp[,-1]
    colnames(df.coys.tmp) <- c("company", "no_of_funds", "month", "year")
    rownames(df.coys.tmp) <- rep(1:nrow(df.coys.tmp))
    
    mf.mthly.df <- rbind(mf.mthly.df, df.coys.tmp)
  }
  
  df.mths.tmp <- data.frame(tapply(lpr.df$n_company, months(lpr.df$from_date), length))
  colnames(df.mths.tmp) <- c("no_of_funds")
  df.mths.tmp$month <- rownames(df.mths.tmp)
  df.mths.tmp$year <- rx.yr
  rownames(df.mths.tmp) <- rep(1:nrow(df.mths.tmp))
  
  return(list(mf.mthly.df, df.mths.tmp))
}

mthly.plot.df <- data.frame(matrix(ncol = 4, nrow = 0))
colnames(mthly.plot.df) <- c("company", "no_of_funds", "month", "year") 
mthly.tot.plot <- data.frame(matrix(ncol=3, nrow = 0))
colnames(mthly.tot.plot) <- c("no_of_funds", "month", "year")


for (j in st.yr:max.yr) {
  #j<- j+1
  rx.lst <- mf.mthly.calc(mfdetails, j)
  tmp.df <- data.frame(rx.lst[1])
  mthly.plot.df <- rbind(mthly.plot.df, tmp.df)
  
  mth.tot.df <- data.frame(rx.lst[2])
  mthly.tot.plot <- rbind(mthly.tot.plot, mth.tot.df)
  
}


ploter.fn.1 <- function(rx.df){
  plot.data <- rx.df
  
  gg.plot <- ggplot(plot.data , aes(x=month , y= no_of_funds )) + 
    geom_line(aes(colour=year, group=year), show.legend = FALSE)+
    geom_point(aes(colour=year), size=3, show.legend = FALSE)+ 
    facet_wrap(~company, nrow=1, scales = "free")+
    theme(axis.text.x=element_text(angle = 90, hjust = 0))+
    scale_x_discrete(limits = c("January",
                              "February","March","April",
                              "May","June","July","August","September","October","November","December"))
  
  gg.plot
}

plot.data <- plot.data[plot.data$year == max.yr,]


pandoc.header("1F. MFs launched by Top 10 AMC's  in the last three years", 1, 'setext')

1F. MFs launched by Top 10 AMC’s in the last three years

tmp.df <- df.coys[1:2,] #Name of Top 3 companies
plot.data <- subset(mthly.plot.df, company %in% tmp.df$company)
ploter.fn.1(plot.data[plot.data$year >= max.yr-2 ,])

tmp.df <- df.coys[3:4,] #Name of Top 4-6 companies
plot.data <- subset(mthly.plot.df, company %in% tmp.df$company)
ploter.fn.1(plot.data[plot.data$year >= max.yr-2,])

tmp.df <- df.coys[5:6,] #Name of Top 7-9 companies
plot.data <- subset(mthly.plot.df, company %in% tmp.df$company)
ploter.fn.1(plot.data[plot.data$year >= max.yr-2,])

tmp.df <- df.coys[7:8,] #Name of Top 10-12 companies
plot.data <- subset(mthly.plot.df, company %in% tmp.df$company)
ploter.fn.1(plot.data[plot.data$year >= max.yr-2,])

tmp.df <- df.coys[9:10,] #Name of Top 10-12 companies
plot.data <- subset(mthly.plot.df, company %in% tmp.df$company)
ploter.fn.1(plot.data[plot.data$year >= max.yr-2,])

cat("Looking at the chart above we can see that there is no pattern of AMC's launching new funds. Some AMC's launch funds in Jan-Feb period while the others launch them during different times of the year.")

Looking at the chart above we can see that there is no pattern of AMC’s launching new funds. Some AMC’s launch funds in Jan-Feb period while the others launch them during different times of the year.

ploter.fn.2 <- function(rx.df, yr.1, yr.2){
  
  rx.df <- rx.df[rx.df$year==yr.1 | rx.df$year==yr.2, ]
  
  gg.plot <- ggplot(rx.df , aes(x=month , y= no_of_funds )) + 
    geom_line(aes(colour=year, group=year), show.legend = FALSE)+
    geom_point(aes(colour=year), size=3, show.legend = FALSE)+scale_fill_brewer(palette = "Set1")+
    facet_wrap(~year, nrow=1, scales = "free")+
    theme(axis.text.x=element_text(angle = 90, hjust = 0))+
    scale_x_discrete(limits = c("January",
                                "February","March","April",
                                "May","June","July","August","September","October","November","December"))
  #return(g1.plot)
  gg.plot

}

pandoc.header(paste('1G.Total MFs launched since', max.yr-9 ,' to ', max.yr), 1, 'setext')

1G.Total MFs launched since 2010 to 2019

ploter.fn.2(mthly.tot.plot, max.yr-9, max.yr-8)

ploter.fn.2(mthly.tot.plot, max.yr-7, max.yr-6)

ploter.fn.2(mthly.tot.plot, max.yr-5, max.yr-4)

ploter.fn.2(mthly.tot.plot, max.yr-3, max.yr-2)

ploter.fn.2(mthly.tot.plot, max.yr-1, max.yr)

cat("Looking at the chart above we can see that there were two spikes in the number of new funds being lauched, first spikeis around January to February and the second spike is around November to December. In all, maximum number of funds are launched in the period from November to March. This data is being analsyed further in the document to reach conculsions regarding the performance of these funds and the subscription.")

Looking at the chart above we can see that there were two spikes in the number of new funds being lauched, first spikeis around January to February and the second spike is around November to December. In all, maximum number of funds are launched in the period from November to March. This data is being analsyed further in the document to reach conculsions regarding the performance of these funds and the subscription.

pandoc.header(paste("1H. AMC's with schemes aging more than 5 yrs as on ", Sys.Date()-30) , 1, 'setext')

1H. AMC’s with schemes aging more than 5 yrs as on 2020-06-05

old.schemes <- mfdata[mfdata$to_date - mfdata$from_date > 1825 & mfdata$to_date > Sys.Date()-30,]#schemes more than 5 yrs
old.schem.plot <- data.frame(tapply(old.schemes$name, old.schemes$n_company, length))
old.schem.plot$amc <- rownames(old.schem.plot)
old.schem.plot$no_of_funds <- old.schem.plot$tapply.old.schemes.name..old.schemes.n_company..length.
old.schem.plot <- old.schem.plot[,-1]
rownames(old.schem.plot) <- rep(1:nrow(old.schem.plot))
old.schem.plot <- old.schem.plot[order(old.schem.plot$no_of_funds, decreasing = TRUE),]
knitr::kable(old.schem.plot[1:10,], caption = colFmt("Top 10 AMC's with maximum number of Funds more than 5 years old ", "blue"))
Top 10 AMC’s with maximum number of Funds more than 5 years old
amc no_of_funds
25 Nippon India Mutual Fund 291
13 ICICI Prudential Mutual Fund 284
1 Aditya Birla Sun Life Mutual Fund 239
31 SBI Mutual Fund 217
37 UTI Mutual Fund 214
10 Franklin Templeton Mutual Fund 187
15 IDFC Mutual Fund 184
7 DSP Mutual Fund 170
11 HDFC Mutual Fund 168
18 Invesco Mutual Fund 148 
rm(old.schemes)

min_dt <- min(mfdata$from_date)
aa <- mfdata[mfdata$from_date==min_dt & mfdata$to_date >= Sys.Date()-30,]
knitr::opts_chunk$set(echo = TRUE)
aum1 <- as.data.frame(read.xlsx("amfi_aum_amcwise_one.xlsx", sheetIndex = 1))
aum2 <- as.data.frame(read.xlsx("amfi_aum_amcwise_two.xlsx", sheetIndex = 1))
period_1 <- names(aum1)[1]
period_2 <- names(aum2)[1]
pandoc.header(colFmt("Section-2: Assets Under Management(AUM)", "red"), 1, 'setext' )

Section-2: Assets Under Management(AUM)

pd1 <- colnames(aum1[1])
aum1 <- aum1[,-1]
aum1 <- aum1[-1:-5,]
colnames(aum1) <- c("amc", "period1", "fof")
aum1$period1 <- as.numeric(as.character(aum1$period1))
aum1$fof <- as.numeric(as.character(aum1$fof))

pd2 <- colnames(aum2[1])
aum2 <- aum2[,-1]
aum2 <- aum2[-1:-5,]
colnames(aum2) <- c("amc", "period2", "fof")
aum2$period2 <- as.numeric(as.character(aum2$period2))
aum2$fof <- as.numeric(as.character(aum2$fof))


#fin.table.1a[is.na(fin.table.1a)] <- 0
aum.temp1 <- aum1[1:2] 
aum.temp2 <- aum2[1:2] 
aum.change <- merge(aum.temp1, aum.temp2, by.x = "amc", by.y = "amc")

#aum.change <- data.frame(aum1[1], aum1[2], aum2[2])
aum.change <- aum.change[aum.change$period1 !=0 & aum.change$period2 !=0,]
aum.change <-  aum.change[-which(is.na(aum.change$amc)),]
aum.change$percentchange <- 0
aum.change$percentchange <-  ifelse(aum.change$period1 == 0,
                                    100,round(100*(aum.change$period2 - aum.change$period1)/aum.change$period1,2) )


cat("Period:-  ", period_1, "*** to ***", period_2)

Period:- Average.Assets.under.Management..AAUM..for.the.quarter.of.January…March.2020..Rs.in.Lakhs. *** to *** Average.Assets.under.Management..AAUM..for.the.quarter.of.April…June.2020..Rs.in.Lakhs.

ploter.fn.3 <- function(rx.df){
  plot.data <- rx.df
  ggplot(plot.data, aes(x=amc, y=percentchange, fill = rainbow(nrow(plot.data)))) +
    geom_bar(stat = "identity", show.legend = FALSE)+
    coord_flip()
  
}

pandoc.header("2A. Top 10 AMC's as per AUM", 1, 'setext')

2A. Top 10 AMC’s as per AUM

aum.plot <- aum.change[order(aum.change$period2, decreasing = TRUE),]
aum.plot <- aum.plot[1:10,]
aum.plot$amc <- factor(as.factor(aum.plot$amc), levels=aum.plot$amc)
ggplot(aum.plot, aes(x=amc, y=period2, fill=rainbow(10)))+
  geom_bar(stat = "identity", show.legend = FALSE)+coord_flip()+
  scale_y_continuous(name="Amount in Lakhs", labels = comma)

##headings from pd1 and pd2 need to be extracted
pandoc.header("2B. Top 10 AMC's whose AUM has grown in the Period", 1, 'setext')

2B. Top 10 AMC’s whose AUM has grown in the Period

aum.plot <- aum.change[aum.change$percentchange > 0,]
aum.plot <- aum.plot[order(aum.plot$percentchange, decreasing = TRUE),]
plot2.1 <- aum.plot[1:10,]
plot2.1$amc <- factor(as.factor(plot2.1$amc), levels = plot2.1$amc)
ploter.fn.3(plot2.1)
## Warning: Removed 7 rows containing missing values (position_stack).

pandoc.header("2C. 10 AMC's whose AUM has reduced in the Period", 1, 'setext')

2C. 10 AMC’s whose AUM has reduced in the Period

aum.plot <- aum.change[aum.change$percentchange < 0,]
aum.plot <- aum.plot[order(aum.plot$percentchange, decreasing = TRUE),]
plot2.1 <- aum.plot[1:10,]
plot2.1$amc <- factor(as.factor(plot2.1$amc), levels = plot2.1$amc)
ploter.fn.3(plot2.1)

knitr::opts_chunk$set(echo = TRUE)
pandoc.header(colFmt("Section-3: Schemewise Analysis", "red"), 1, 'setext')

Section-3: Schemewise Analysis

pandoc.header("3A. Schemes whose AUM has increased maximum from Mar 2019 to Jun 2019", 1, 'setext')

3A. Schemes whose AUM has increased maximum from Mar 2019 to Jun 2019

aum.s1 <- as.data.frame(read.xlsx("amfi_aum_schemewise_one.xlsx", sheetIndex = 1))
aum.s2 <- as.data.frame(read.xlsx("amfi_aum_schemewise_two.xlsx", sheetIndex = 1))


aum.s1 <- aum.s1[-1:-6,]
aum.s1 <-  aum.s1[-which(is.na(aum.s1$NA..1)),]
colnames(aum.s1) <- c("amfi_code", "scheme", "period1", "fof")

aum.s2 <- aum.s2[-1:-6,]
aum.s2 <-  aum.s2[-which(is.na(aum.s2$NA..1)),]
colnames(aum.s2) <- c("amfi_code", "scheme", "period2", "fof")

aum.change.scheme <- merge(aum.s1, aum.s2, by.x = "amfi_code", by.y = "amfi_code")
aum.change.scheme$period1 <- as.numeric(as.character(aum.change.scheme$period1))
aum.change.scheme$period2 <- as.numeric(as.character(aum.change.scheme$period2))


aum.change.scheme$percentchange <- ifelse(aum.change.scheme$period1 == 0, 
                                          100,round(100*(aum.change.scheme$period2 - 
                                                           aum.change.scheme$period1)/aum.change.scheme$period1,2))

old.schemes <- mfdata[mfdata$to_date - mfdata$from_date > 1825 & mfdata$to_date > Sys.Date()-30,]#schemes more than 5 yrs
aum.change.scheme <- subset(aum.change.scheme, amfi_code %in% old.schemes$code)
aum.change.scheme <- aum.change.scheme[order(aum.change.scheme$percentchange, decreasing = TRUE),]
#aum.change.scheme <- aum.change.scheme[aum.change.scheme$]
knitr::kable(aum.change.scheme[1:20,c(2,8)], caption = colFmt("Top 20 Schemes and their Percent growth in AUM in the last three months ", "blue"))
Top 20 Schemes and their Percent growth in AUM in the last three months
scheme.x percentchange
2781 BNP Paribas Flexi Debt Fund - Direct Plan - Daily Dividend Option 2500
1810 Canara Robeco Liquid Fund- Direct Plan-Weekly dividend 1295
2998 JM Short Term Fund (Direct) - Growth Option 721
2605 Sundaram Low DurationFund - Direct Plan - Quarterly Dividend Option 670
1855 IDFC Bond Fund - Income Plan - Direct Plan-Half Yearly Dividend 600
2746 L&T Triple Ace Bond Fund -Direct Plan- Quarterly Dividend 596
3457 SBI BANKING & PSU FUND - Direct Plan - Monthly Dividend 371
2851 ICICI Prudential Liquid Fund - Direct Plan -Half Yearly Dividend 344
2978 Axis Banking & PSU Debt Fund - Direct Plan - Monthly Dividend Option 332
2831 BNP Paribas Short Term Fund - Direct Plan - Monthly Dividend Option 315
2696 L&T Banking and PSU Debt Fund -Direct Plan - Monthly Dividend 303
3887 UTI Bond Fund - Flexi Dividend Option - Direct 300
2984 Axis Gilt Fund - Direct Plan - Regular Dividend Option 285
1856 IDFC Bond Fund - Income Plan - Direct Plan-Quarterly Dividend 254
2644 Kotak Bond - Annual Dividend - Direct 251
2917 LIC MF Banking & PSU Debt Fund-Direct Plan-Weekly Dividend 232
46 JM Large Cap Fund-Growth 231
1656 L&T Short Term Bond Fund - Regular Plan - Quarterly Dividend 229
2035 Nippon India Floating Rate Fund- Direct Plan Weekly Dividend Plan 221
2949 Invesco India Arbitrage Fund - Direct Plan - Growth Option 221 
rm(old.schemes)
rm(aum.change.scheme)
rm(aum.s1)
rm(aum.s2)
knitr::opts_chunk$set(echo = TRUE)
pandoc.header(colFmt("Section-4: Industry wise Analysis", "red"),1,'setext')

Section-4: Industry wise Analysis

pandoc.header("4A. Change in the investments by Industry  Dec 2019 to Mar 2020", 1, 'setext')

4A. Change in the investments by Industry Dec 2019 to Mar 2020

funds.s2 <- as.data.frame(read.xlsx("sebi_DepyofFunds_2.xlsx", sheetIndex = 1))
funds.s1 <- as.data.frame(read.xlsx("sebi_DepyofFunds_1.xlsx", sheetIndex = 1))
sector.details <- as.data.frame(read.xlsx("sectors.xlsx", sheetIndex = 1))

funds.s1 <- funds.s1[,c(-2,-5)]
funds.s2 <- funds.s2[,c(-2,-5)]
colnames(funds.s1) <- c("sector", "aum", "percent")
colnames(funds.s2) <- c("sector", "aum_new", "percent_new")



sector.change <- merge(sector.details, funds.s1, by.x = "sectors", by.y = "sector")
sector.change <- merge(sector.change, funds.s2, by.x = "sectors", by.y = "sector")
sector.change$aum <- as.numeric(as.character(sector.change$aum))
sector.change$percent <- as.numeric(as.character(sector.change$percent))
sector.change$aum_new <- as.numeric(as.character(sector.change$aum_new))
sector.change$percent_new <- as.numeric(as.character(sector.change$percent_new))

sector.change$percent_change <- sector.change$percent_new - sector.change$percent
sector.change$type <- ifelse(sector.change$percent_change >= 0,"Increase","Decrease")
sector.change <- sector.change[order(sector.change$percent_change),]
sector.change$sectors <- factor(as.factor(sector.change$sectors), levels = sector.change$sectors)



knitr::kable(sector.change[1:5,c(1,5,6,7,8)], caption = colFmt("Five sectors with maximum reduction in investment in the last three months", "blue"))
Five sectors with maximum reduction in investment in the last three months
sectors aum_new percent_new percent_change type
3 BANKS 191055 21.264 -4.035 Decrease
15 FINANCE 84479 9.403 -0.833 Decrease
7 CONSTRUCTION PROJECT 25270 2.813 -0.739 Decrease
24 NON - FERROUS METALS 6727 0.749 -0.440 Decrease
1 AUTO 26992 3.004 -0.435 Decrease 
knitr::kable(sector.change[(nrow(sector.change)-4) : nrow(sector.change),c(1,5,6,7,8)], caption = colFmt("Top Five sectors where MF's have increased investment in the last three months", "blue"))
Top Five sectors where MF’s have increased investment in the last three months
sectors aum_new percent_new percent_change type
31 POWER 32830 3.65 0.520 Increase
33 SOFTWARE 80444 8.95 0.985 Increase
35 TELECOM - SERVICES 26063 2.90 0.994 Increase
30 PHARMACEUTICALS 59082 6.58 1.470 Increase
9 CONSUMER NON DURABLES 84709 9.43 1.757 Increase 
ggplot(sector.change, aes(x=sectors, y=percent_change))+
  geom_bar(stat = 'identity', aes(fill=type), width = .5, show.legend = FALSE)+coord_flip()

sector.change <- sector.change[sector.change$percent_change > 0.0125 | sector.change$percent_change < -.01, ]

ggplot(sector.change, aes(x=sectors, y=percent_change))+
  geom_bar(stat = 'identity', aes(fill=type), width = .5, show.legend = FALSE)+coord_flip()