#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 6002
No of MF’s launched on or before 2006-04-01 and still active 34
MF’s Launched in 2019 1124
MF’s Launched in 2020 till now 542 
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 389
UTI Mutual Fund 380
Aditya Birla Sun Life Mutual Fund 306
IDFC Mutual Fund 254
Franklin Templeton Mutual Fund 253
SBI Mutual Fund 241
Sundaram Mutual Fund 238
DSP Mutual Fund 223
Axis Mutual Fund 219
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 389
UTI Mutual Fund 86 89 90 92 167 183 229 247 256 289 303 380
Aditya Birla Sun Life Mutual Fund 92 94 96 98 189 225 239 241 241 249 298 306
IDFC Mutual Fund 52 64 70 74 154 179 187 205 220 236 248 254
Franklin Templeton Mutual Fund 85 86 88 95 171 185 185 187 187 195 203 253
SBI Mutual Fund 92 95 98 99 198 200 218 219 219 221 231 241
Sundaram Mutual Fund 78 83 83 83 141 145 147 153 159 182 214 238
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 219
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
IDFC Mutual Fund 23.08 9.38 5.71 108.1 16.23 4.47 9.626 7.32 7.273 5.08 19.6
Franklin Templeton Mutual Fund 1.18 2.33 7.96 80.0 8.19 0.00 1.081 0.00 4.278 4.10 10.9
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
Sundaram Mutual Fund 6.41 0.00 0.00 69.9 2.84 1.38 4.082 3.92 14.465 17.58 12.1
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-10-16

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 295
13 ICICI Prudential Mutual Fund 290
1 Aditya Birla Sun Life Mutual Fund 239
37 UTI Mutual Fund 228
31 SBI Mutual Fund 218
15 IDFC Mutual Fund 187
10 Franklin Templeton Mutual Fund 185
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.April…June.2020..Rs.in.Lakhs. *** to *** Average.Assets.under.Management..AAUM..for.the.quarter.of.July…September.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)

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)
## Warning: Removed 7 rows containing missing values (position_stack).

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
43 Quant Small Cap Fund-Dividend 2594
2351 BARODA CONSERVATIVE HYBRID FUND - Plan B (Direct) - Quarterly Dividend Option 2422
3158 Quant Small Cap Fund-Growth Option-Direct Plan 1551
1831 IDBI Short Term Bond Fund Monthly Dividend Direct 1479
3157 Quant Small Cap Fund-Dividend Option-Direct Plan 997
267 Quant Multi Asset Fund-GROWTH OPTION 814
3314 DSP Banking & PSU Debt Fund - Regular Plan - Weekly Dividend Payout & Reinvest 801
2937 JM Equity Hybrid Fund (Direct) - Growth Option 502
1770 IDBI UST Growth Direct 449
2973 Invesco India Gilt Fund - Direct Plan - Quarterly Dividend 432
2906 Invesco India Banking & PSU Debt Fund - Direct Plan -Growth Option 393
2006 Nippon India Low Duration Fund - Direct Plan Growth Plan - Growth Option 392
3153 Quant Active Fund-GROWTH OPTION-Direct Plan 391
1544 Union Liquid Fund - Fortnightly Dividend Option 371
3150 Quant Multi Asset Fund-DIVIDEND OPTION-Direct Plan 370
3434 BNP Paribas Medium Term Fund - Direct Plan - Monthly Dividend Option 340
2054 Nippon India Short Term Fund - Direct Plan Quarterly Dividend Plan 331
2421 Aditya Birla Sun Life Short Term Fund - Growth - Direct Plan 320
3566 BNP PARIBAS LIQUID FUND - DAILY DIVIDEND OPTION 304
3151 Quant Multi Asset Fund-GROWTH OPTION-Direct Plan 302 
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()