EGM (Pension Monitoring Center) Data Analysis

EGM is responsible monitoring and controlling Pension System in Turkey. EGM shares data about pension system from its website. We have summary data about pension funds, participants and contribution etc. We made two analysis and visiulation abaout Avivasa Emeklilik Hayat, which is the leader in the market.

library(tidyverse)
## -- Attaching packages ------------------------------------------------------------ tidyverse 1.2.1 --
## <U+221A> ggplot2 3.0.0     <U+221A> purrr   0.2.5
## <U+221A> tibble  1.4.2     <U+221A> dplyr   0.7.6
## <U+221A> tidyr   0.8.1     <U+221A> stringr 1.3.1
## <U+221A> readr   1.1.1     <U+221A> forcats 0.3.0
## -- Conflicts --------------------------------------------------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()
library(dplyr)
library(readxl)
tmp<-tempfile(fileext=".xlsx")
download.file("https://github.com/MEF-BDA503/pj18-omerbayir/blob/master/week3/egm_example_data.xlsx?raw=true",destfile=tmp,mode = 'wb')
raw_data<-readxl::read_excel(tmp,skip=7,col_names=FALSE)
file.remove(tmp)
## [1] TRUE
head(raw_data)
## # A tibble: 6 x 15
##   X__1  X__2    X__3   X__4   X__5   X__6 X__7    X__8 X__9  X__10  X__11
##   <chr> <chr>  <dbl>  <dbl>  <dbl>  <dbl> <chr>  <dbl> <chr> <chr>  <dbl>
## 1 06.0~ BNP ~ 1.87e5 1.55e9 2.02e8 1.30e9 1196  149094 48359 29668 2.27e5
## 2 06.0~ Cign~ 1.23e5 5.64e8 1.04e8 5.01e8 57    107774 23016 2304  1.33e5
## 3 06.0~ Fiba~ 3.88e4 2.48e8 3.46e7 2.16e8 103    33841 8088  1075  4.30e4
## 4 06.0~ Gara~ 1.13e6 8.42e9 1.17e9 6.86e9 5669  963726 1918~ 58214 1.21e6
## 5 06.0~ Grou~ 6.21e4 8.09e8 8.31e7 6.17e8 1963   61411 8351  1012  7.08e4
## 6 06.0~ Halk~ 4.45e5 1.95e9 3.50e8 1.78e9 111   214731 2389~ 49978 5.04e5
## # ... with 4 more variables: X__12 <dbl>, X__13 <dbl>, X__14 <dbl>,
## #   X__15 <dbl>
colnames(raw_data) <- c("date","pension_fund_company","n_of_participants","fund_size_participants","gov_contribution","contribution","n_of_pensioners","n_of_ind_contracts","n_of_group_ind_contracts","n_of_employer_group_certificates","n_total","size_of_ind_contracts","size_of_group_ind_contracts","size_of_employer_group_certificates","size_total")

# Now we replace NA values with 0 and label the time period with year and month, so when we merge the data we won't be confused.
egm_data <- raw_data %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.)))

```

Avivasa’s Participants Plot

The line graph below shows Avivasa’s participants count day to day depending on the data.

Pension companies that have higher fund than average

The chart below shows avarage fund for every companies participants. After that elimates the companies that they have below the market’s avarage fund volume.