1.BES Dataset

This is a worksheet of BES Assignment.The dataset for this project includes key indicators of Individual Pension System (BES) from the January 2017 through July 2019.

2.Preparing and Cleaning Dataset

We begin the project by reading the excel files that we download from the EGM website. After the “Rapor Tarihi” filter was removed, raw data was created in the excel sheet. This sheet was uploaded to spo’R’ify Github Page for further analysis. Downloading file and cleaning process has given below:

library(tidyverse)
library(readxl)
library(bsts)
#Downloading the excelel file
tmp<-tempfile(fileext=".xlsx")
download.file("https://github.com/pjournal/mef03g-spo-R-ify/blob/master/BES%20Assignment/BES_2017_07.2019.xlsx?raw=true",mode="wb",destfile=tmp)
                      
bes_data <-readxl::read_excel(tmp,skip=2,col_names=FALSE)
file.remove(tmp)
## [1] TRUE
#cleaining process
names(bes_data) <- c("sirket_adi", "katilimci_sayisi", "katilimci_fon_TL", "devlet_katkisi_TL", "katki_payi_TL", "tarih", "ss_emekli_sayisi", "ss_bireysel_sozlesme", "ss_grup_bireysel_sozlesme", "ss_isveren_grup_sertifika", "ss_toplam", "yy_bireysel_TL", "yy_grup_bireysel_TL", "yy_isveren_grup_TL", "yy_toplam_TL")
bes_data$tarih <-  format(as.Date(bes_data$tarih), "%Y-%m-%d")
bes_data$tarih <- as.Date(bes_data$tarih)
#To use monthly data, we need to get the last day of each month. With this code, we select the last day of the month.
#In pipe, "filter(tarih %in% lastday)" can be used.
lastday <- unique(LastDayInMonth(bes_data$tarih))
bes_data <- bes_data %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.)))
options(scipen=999)

3.Loding Libraries and Civilized Data Display

library(tidyverse)
library(ggplot2)
library(zoo)
library(scales)
library(formattable)
library(knitr)
library(bsts)
print(bes_data,width=Inf)
## # A tibble: 2,880 x 15
##    sirket_adi                      katilimci_sayisi katilimci_fon_TL
##    <chr>                                      <dbl>            <dbl>
##  1 Aegon Emeklilik ve Hayat                   37671       132128516.
##  2 Allianz Hayat ve Emeklilik                 94630      2801049763.
##  3 Allianz Yasam ve Emeklilik                728934     12158903088.
##  4 Anadolu Hayat Emeklilik                  1091010     16312824287.
##  5 Avivasa Emeklilik ve Hayat                787046     16801925214.
##  6 Axa Hayat ve Emeklilik                     33794       426214299.
##  7 Bereket Emeklilik ve Hayat                 93913       360296123.
##  8 BNP Paribas Cardif Emeklilik              181937      2260767911.
##  9 Cigna Finans Emeklilik ve Hayat            83601       707565760.
## 10 Fiba Emeklilik ve Hayat                    91108      1614645975.
##    devlet_katkisi_TL katki_payi_TL tarih      ss_emekli_sayisi
##                <dbl>         <dbl> <date>                <dbl>
##  1         15559754.     82440953. 2019-07-31              750
##  2        385315268.   1866089008. 2019-07-31             4157
##  3       1435419831.   8128374067. 2019-07-31            12706
##  4       2768140863.  11444491475. 2019-07-31            26444
##  5       2467365796.  11691712895. 2019-07-31            20650
##  6         86715369.    310708020. 2019-07-31               73
##  7         70720767.    240230751. 2019-07-31               39
##  8        349915170.   1570196604. 2019-07-31             2686
##  9        139122983.    508935718  2019-07-31              248
## 10        210616191.   1114959066. 2019-07-31             1676
##    ss_bireysel_sozlesme ss_grup_bireysel_sozlesme ss_isveren_grup_sertifika
##                   <dbl>                     <dbl>                     <dbl>
##  1                29407                     12727                       706
##  2                92110                     14191                      4077
##  3               602553                    114447                    155683
##  4               947707                    299000                     34272
##  5               893025                     61095                     46248
##  6                41396                      1096                       461
##  7                26991                     44599                     27090
##  8               157130                     39991                     22614
##  9                75024                     12997                      1489
## 10                84021                     18395                      5522
##    ss_toplam yy_bireysel_TL yy_grup_bireysel_TL yy_isveren_grup_TL
##        <dbl>          <dbl>               <dbl>              <dbl>
##  1     42840      62462285.           17367905.            853583.
##  2    110378    1546418719.          250266033.          51553466.
##  3    872683    5518456598.         1615632745.         867772629.
##  4   1280979    8512052830.         2515204122.         249674051.
##  5   1000368   10045042696.          999359846.         429577866.
##  6     42953     290879649.           11907208.           3635211.
##  7     98680     101363011.          128832613.           8555047.
##  8    219735    1192784348.          286615210.          67393093.
##  9     89510     419257460.           75935434.           1766624.
## 10    107938     839766784.          213080704.          55703199.
##    yy_toplam_TL
##           <dbl>
##  1    80683772.
##  2  1848238218.
##  3  8001861972.
##  4 11276931003.
##  5 11473980409.
##  6   306422068.
##  7   238750671.
##  8  1546792651.
##  9   496959518.
## 10  1108550686.
## # ... with 2,870 more rows

4.Exploratory Data Analysis

4.1.Total Fund Values for Each Firm Yearly

bes_data_monthly<- bes_data %>% filter(tarih %in% lastday) %>% mutate (year = format(tarih,"%Y"),month=format(tarih,"%B")) 
slct_1<-bes_data_monthly %>% filter (month == "July")
slct_1
## # A tibble: 54 x 17
##    sirket_adi katilimci_sayisi katilimci_fon_TL devlet_katkisi_~
##    <chr>                 <dbl>            <dbl>            <dbl>
##  1 Aegon Eme~            37671       132128516.        15559754.
##  2 Allianz H~            94630      2801049763.       385315268.
##  3 Allianz Y~           728934     12158903088.      1435419831.
##  4 Anadolu H~          1091010     16312824287.      2768140863.
##  5 Avivasa E~           787046     16801925214.      2467365796.
##  6 Axa Hayat~            33794       426214299.        86715369.
##  7 Bereket E~            93913       360296123.        70720767.
##  8 BNP Parib~           181937      2260767911.       349915170.
##  9 Cigna Fin~            83601       707565760.       139122983.
## 10 Fiba Emek~            91108      1614645975.       210616191.
## # ... with 44 more rows, and 13 more variables: katki_payi_TL <dbl>,
## #   tarih <date>, ss_emekli_sayisi <dbl>, ss_bireysel_sozlesme <dbl>,
## #   ss_grup_bireysel_sozlesme <dbl>, ss_isveren_grup_sertifika <dbl>,
## #   ss_toplam <dbl>, yy_bireysel_TL <dbl>, yy_grup_bireysel_TL <dbl>,
## #   yy_isveren_grup_TL <dbl>, yy_toplam_TL <dbl>, year <chr>, month <chr>
ggplot(slct_1,aes(x=sirket_adi,y=katilimci_fon_TL ,fill=ordered(year))) +geom_bar(stat="identity",position="dodge") + labs(y="Total Funds",x="Firms",fill="") + theme(axis.text.x = element_text(angle = 90, hjust = 1))+scale_y_continuous(labels=comma)

4.2.Ratio of Number of Retired Participants to Total Number of Participants by Each Firm

bes_katilimci <- bes_data %>% filter(tarih %in% lastday) %>% group_by(sirket_adi, tarih) %>% summarise(emekli_toplam = sum(ss_emekli_sayisi), sozlesme_toplam = sum(ss_toplam), emekli_oran = emekli_toplam / sozlesme_toplam)

bes_katilimci
## # A tibble: 558 x 5
## # Groups:   sirket_adi [21]
##    sirket_adi          tarih      emekli_toplam sozlesme_toplam emekli_oran
##    <chr>               <date>             <dbl>           <dbl>       <dbl>
##  1 Aegon Emeklilik ve~ 2017-01-31           277           48889     0.00567
##  2 Aegon Emeklilik ve~ 2017-02-28           288           48633     0.00592
##  3 Aegon Emeklilik ve~ 2017-03-31           303           48359     0.00627
##  4 Aegon Emeklilik ve~ 2017-04-30           313           48246     0.00649
##  5 Aegon Emeklilik ve~ 2017-05-31           329           47992     0.00686
##  6 Aegon Emeklilik ve~ 2017-06-30           338           47792     0.00707
##  7 Aegon Emeklilik ve~ 2017-07-31           354           47644     0.00743
##  8 Aegon Emeklilik ve~ 2017-08-31           367           47503     0.00773
##  9 Aegon Emeklilik ve~ 2017-09-30           382           47404     0.00806
## 10 Aegon Emeklilik ve~ 2017-10-31           398           47246     0.00842
## # ... with 548 more rows
ggplot(data = bes_katilimci, mapping = aes(x = tarih, y = emekli_oran, color = sirket_adi, group = 2)) +
  geom_line() +
  facet_wrap(facets =  vars(sirket_adi)) +
  labs(title = "Ratio of Retired Participants to Total Participants", x = "Date", y = "Ratio of Retired Participants ", color = " ") +
  theme(axis.text = element_text( size = 8 ),
        axis.text.x = element_text( size = 8 ), 
        axis.title = element_text( size = 10, face = "bold" ),
         legend.position="none",
         # The new stuff
         strip.text = element_text(size = 8))

4.3.Top 5 Companies

# Table: Top 5 Companies by Participants:
top_5_by_katilimci_sayisi <- bes_data %>% 
  group_by(sirket_adi) %>%
  summarise(total_ks=sum(katilimci_sayisi)) %>%
  arrange(desc(total_ks)) %>%
  top_n(5, total_ks) 
print(top_5_by_katilimci_sayisi)
## # A tibble: 5 x 2
##   sirket_adi                  total_ks
##   <chr>                          <dbl>
## 1 Garanti Emeklilik ve Hayat 187508854
## 2 Anadolu Hayat Emeklilik    180490840
## 3 Avivasa Emeklilik ve Hayat 137561741
## 4 Allianz Yasam ve Emeklilik 121955215
## 5 Ziraat Hayat ve Emeklilik   95385905
# Table: Top 5 Companies by Number of Contracts:
top_5_by_sozlesme_sayisi <- bes_data %>% 
  group_by(sirket_adi) %>%
  summarise(total_ss=sum(ss_toplam)) %>%
  arrange(desc(total_ss)) %>%
  top_n(5, total_ss) 
print(top_5_by_sozlesme_sayisi)
## # A tibble: 5 x 2
##   sirket_adi                  total_ss
##   <chr>                          <dbl>
## 1 Anadolu Hayat Emeklilik    209478006
## 2 Garanti Emeklilik ve Hayat 199740424
## 3 Avivasa Emeklilik ve Hayat 174030734
## 4 Allianz Yasam ve Emeklilik 146395885
## 5 Ziraat Hayat ve Emeklilik  108095015
# Table: Top 5 Companies by Market Share (Mean Participant Funds):
top_5_by_market_share <- bes_data %>% 
  group_by(sirket_adi) %>%
  summarise(mean_of_fundsize=mean(katilimci_fon_TL, na.rm = TRUE)) %>%
  mutate(mshare = percent(mean_of_fundsize / sum(mean_of_fundsize))) %>%
  top_n(5, mshare) 
print(top_5_by_market_share)
## # A tibble: 5 x 3
##   sirket_adi                 mean_of_fundsize mshare    
##   <chr>                                 <dbl> <formttbl>
## 1 Allianz Yasam ve Emeklilik      9686469300. 12.84%    
## 2 Anadolu Hayat Emeklilik        13003558660. 17.24%    
## 3 Avivasa Emeklilik ve Hayat     13707835887. 18.17%    
## 4 Garanti Emeklilik ve Hayat     10443306686. 13.84%    
## 5 Vakif Emeklilik ve Hayat        5805748351. 7.70%
# Plot: Top 5 Companies by Market Share (Mean Participant Funds):
ggplot(data = top_5_by_market_share, aes(x=sirket_adi, y=mshare, fill=sirket_adi)) +
  geom_bar(stat="identity")+
  coord_polar()+
  theme(legend.position = "right", axis.text.x = element_text(angle = 0))+
  geom_text(aes(y = mshare , label = mshare))+
  labs(title = "Market shares of top 5 companies", x="", y="")