library(readxl)
## Warning: package 'readxl' was built under R version 3.4.4
read_excel_allsheets <- function(filename) {
  # Zdroj: https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames
  sheets <- readxl::excel_sheets(filename)
  x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  x <- lapply(x, as.data.frame)
  names(x) <- sheets
  return(x)
}

subory.zoznam <- paste0("data/hist_panel_EURIBOR_", 
                        rep(c("Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov"), 2),
                        c(rep("_2016",1), rep("_2017", 12), rep("_2018", 11)),
                        ".xls"
)

subory.zoznam
##  [1] "data/hist_panel_EURIBOR_Dec_2016.xls"
##  [2] "data/hist_panel_EURIBOR_Jan_2017.xls"
##  [3] "data/hist_panel_EURIBOR_Feb_2017.xls"
##  [4] "data/hist_panel_EURIBOR_Mar_2017.xls"
##  [5] "data/hist_panel_EURIBOR_Apr_2017.xls"
##  [6] "data/hist_panel_EURIBOR_May_2017.xls"
##  [7] "data/hist_panel_EURIBOR_Jun_2017.xls"
##  [8] "data/hist_panel_EURIBOR_Jul_2017.xls"
##  [9] "data/hist_panel_EURIBOR_Aug_2017.xls"
## [10] "data/hist_panel_EURIBOR_Sep_2017.xls"
## [11] "data/hist_panel_EURIBOR_Oct_2017.xls"
## [12] "data/hist_panel_EURIBOR_Nov_2017.xls"
## [13] "data/hist_panel_EURIBOR_Dec_2017.xls"
## [14] "data/hist_panel_EURIBOR_Jan_2018.xls"
## [15] "data/hist_panel_EURIBOR_Feb_2018.xls"
## [16] "data/hist_panel_EURIBOR_Mar_2018.xls"
## [17] "data/hist_panel_EURIBOR_Apr_2018.xls"
## [18] "data/hist_panel_EURIBOR_May_2018.xls"
## [19] "data/hist_panel_EURIBOR_Jun_2018.xls"
## [20] "data/hist_panel_EURIBOR_Jul_2018.xls"
## [21] "data/hist_panel_EURIBOR_Aug_2018.xls"
## [22] "data/hist_panel_EURIBOR_Sep_2018.xls"
## [23] "data/hist_panel_EURIBOR_Oct_2018.xls"
## [24] "data/hist_panel_EURIBOR_Nov_2018.xls"
df <- data.frame(Bank = character(), Rate = numeric(), Date = character()) 

for (subor in subory.zoznam){
  mysheets <- read_excel_allsheets(subor)
  for (i in 1:length(mysheets)){
    colnames(mysheets[[i]])[1] <- "Bank"
    Date <- names(mysheets)[i]
    if (grepl("2016", subor)) Date <- paste0(Date, "_2016")
    if (grepl("2017", subor)) Date <- paste0(Date, "_2017")
    if (grepl("2018", subor)) Date <- paste0(Date, "_2018")
    df <- rbind(df, data.frame(mysheets[[i]]["Bank"], 
                               Rate = unname(mysheets[[i]]["3 Months"]), 
                               Date
                               )
                )
  }
}

head(df)
##                                   Bank  Rate      Date
## 1                          BNP-Paribas -0.40 0112_2016
## 2      Banca Monte Dei Paschi Di Siena -0.31 0112_2016
## 3      Banco Bilbao Vizcaya Argentaria -0.31 0112_2016
## 4                      Banco Santander -0.32 0112_2016
## 5 Banque et Caisse d'Épargne de l'État -0.32 0112_2016
## 6                        Barclays Bank -0.30 0112_2016
library(reshape2)
df2 <- dcast(df, Date ~ Bank, value.var = "Rate" )
head(df2)
##        Date Banca Monte Dei Paschi Di Siena
## 1 0112_2016                           -0.31
## 2 0212_2016                           -0.31
## 3 0512_2016                           -0.31
## 4 0612_2016                           -0.31
## 5 0712_2016                           -0.32
## 6 0812_2016                           -0.32
##   Banco Bilbao Vizcaya Argentaria Banco Santander
## 1                           -0.31           -0.32
## 2                           -0.31           -0.32
## 3                           -0.31           -0.32
## 4                           -0.31           -0.32
## 5                           -0.31           -0.32
## 6                           -0.32           -0.32
##   Banque et Caisse d'?pargne de l'?tat
## 1                                   NA
## 2                                   NA
## 3                                   NA
## 4                                   NA
## 5                                   NA
## 6                                   NA
##   Banque et Caisse d'Épargne de l'État Barclays Bank Belfius BNP-Paribas
## 1                                -0.32         -0.30   -0.32        -0.4
## 2                                -0.32         -0.30   -0.32        -0.4
## 3                                -0.32         -0.30   -0.32        -0.4
## 4                                -0.32         -0.33   -0.32        -0.4
## 5                                -0.32         -0.33   -0.32        -0.4
## 6                                -0.32         -0.33   -0.32        -0.4
##   Caixa Geral De Dep?sitos Caixa Geral De Depósitos CaixaBank S.A.
## 1                       NA                    -0.29          -0.31
## 2                       NA                    -0.29          -0.31
## 3                       NA                    -0.29          -0.31
## 4                       NA                    -0.29          -0.31
## 5                       NA                    -0.29          -0.31
## 6                       NA                    -0.29          -0.31
##   CECABANK Cr?dit Agricole s.a. Crédit Agricole s.a. Deutsche Bank DZ Bank
## 1    -0.31                   NA                -0.32         -0.17   -0.35
## 2    -0.31                   NA                -0.32         -0.17   -0.35
## 3    -0.31                   NA                -0.32         -0.17   -0.35
## 4    -0.31                   NA                -0.32         -0.17   -0.35
## 5    -0.32                   NA                -0.32         -0.17   -0.35
## 6    -0.32                   NA                -0.32         -0.17   -0.35
##   HSBC France ING Bank Intesa Sanpaolo National Bank of Greece Natixis
## 1       -0.31    -0.32           -0.31                   -0.31   -0.31
## 2       -0.31    -0.32           -0.31                   -0.31   -0.31
## 3       -0.31    -0.32           -0.31                   -0.31   -0.31
## 4       -0.31    -0.32           -0.31                   -0.31   -0.31
## 5       -0.31    -0.32           -0.31                   -0.31   -0.31
## 6       -0.31    -0.32           -0.31                   -0.31   -0.32
##   Soci?t? G?n?rale Société Générale UniCredit
## 1               NA            -0.34      -0.3
## 2               NA            -0.34      -0.3
## 3               NA            -0.33      -0.3
## 4               NA            -0.33      -0.3
## 5               NA            -0.33      -0.3
## 6               NA            -0.33      -0.3
nahradit.na <- function(from, to){
  ind <- is.na(df2[,to])
  df2[ind, to] <<- df2[ind, from]
}

names(df2)
##  [1] "Date"                                
##  [2] "Banca Monte Dei Paschi Di Siena"     
##  [3] "Banco Bilbao Vizcaya Argentaria"     
##  [4] "Banco Santander"                     
##  [5] "Banque et Caisse d'?pargne de l'?tat"
##  [6] "Banque et Caisse d'Épargne de l'État"
##  [7] "Barclays Bank"                       
##  [8] "Belfius"                             
##  [9] "BNP-Paribas"                         
## [10] "Caixa Geral De Dep?sitos"            
## [11] "Caixa Geral De Depósitos"            
## [12] "CaixaBank S.A."                      
## [13] "CECABANK"                            
## [14] "Cr?dit Agricole s.a."                
## [15] "Crédit Agricole s.a."                
## [16] "Deutsche Bank"                       
## [17] "DZ Bank"                             
## [18] "HSBC France"                         
## [19] "ING Bank"                            
## [20] "Intesa Sanpaolo"                     
## [21] "National Bank of Greece"             
## [22] "Natixis"                             
## [23] "Soci?t? G?n?rale"                    
## [24] "Société Générale"                    
## [25] "UniCredit"
nahradit.na(from = 5, to = 6)
nahradit.na(from = 10, to = 11)
nahradit.na(from = 14, to = 15)
nahradit.na(from = 23, to = 24)

df2 <- df2[, -c(1, 5, 10, 14, 23)]
any(is.na(df2))
## [1] FALSE
# PRIPRAVENE DATA :)
head(df2)
##   Banca Monte Dei Paschi Di Siena Banco Bilbao Vizcaya Argentaria
## 1                           -0.31                           -0.31
## 2                           -0.31                           -0.31
## 3                           -0.31                           -0.31
## 4                           -0.31                           -0.31
## 5                           -0.32                           -0.31
## 6                           -0.32                           -0.32
##   Banco Santander Banque et Caisse d'Épargne de l'État Barclays Bank
## 1           -0.32                                -0.32         -0.30
## 2           -0.32                                -0.32         -0.30
## 3           -0.32                                -0.32         -0.30
## 4           -0.32                                -0.32         -0.33
## 5           -0.32                                -0.32         -0.33
## 6           -0.32                                -0.32         -0.33
##   Belfius BNP-Paribas Caixa Geral De Depósitos CaixaBank S.A. CECABANK
## 1   -0.32        -0.4                    -0.29          -0.31    -0.31
## 2   -0.32        -0.4                    -0.29          -0.31    -0.31
## 3   -0.32        -0.4                    -0.29          -0.31    -0.31
## 4   -0.32        -0.4                    -0.29          -0.31    -0.31
## 5   -0.32        -0.4                    -0.29          -0.31    -0.32
## 6   -0.32        -0.4                    -0.29          -0.31    -0.32
##   Crédit Agricole s.a. Deutsche Bank DZ Bank HSBC France ING Bank
## 1                -0.32         -0.17   -0.35       -0.31    -0.32
## 2                -0.32         -0.17   -0.35       -0.31    -0.32
## 3                -0.32         -0.17   -0.35       -0.31    -0.32
## 4                -0.32         -0.17   -0.35       -0.31    -0.32
## 5                -0.32         -0.17   -0.35       -0.31    -0.32
## 6                -0.32         -0.17   -0.35       -0.31    -0.32
##   Intesa Sanpaolo National Bank of Greece Natixis Société Générale
## 1           -0.31                   -0.31   -0.31            -0.34
## 2           -0.31                   -0.31   -0.31            -0.34
## 3           -0.31                   -0.31   -0.31            -0.33
## 4           -0.31                   -0.31   -0.31            -0.33
## 5           -0.31                   -0.31   -0.31            -0.33
## 6           -0.31                   -0.31   -0.32            -0.33
##   UniCredit
## 1      -0.3
## 2      -0.3
## 3      -0.3
## 4      -0.3
## 5      -0.3
## 6      -0.3