Zo stránky https://www.emmi-benchmarks.eu/euribor-org/about-euribor.html:
Euribor® is the rate at which Euro interbank term deposits are offered by one prime bank to another prime bank within the EMU zone, and is calculated at 11:00 a.m. (CET) for spot value (T+2).
The choice of banks quoting for Euribor® is based on market criteria. These banks have been selected to ensure that the diversity of the euro money market is adequately reflected, thereby making Euribor® an efficient and representative benchmark.
Aktuálne (https://www.emmi-benchmarks.eu/euribor-org/panel-banks.html):
Historické dáta (https://www.emmi-benchmarks.eu/euribor-org/euribor-rates.html):
Jeden hárok zodpovedá jednému dňu, v riadkoch sú banky, v stĺpcoch sú rôzne maturity:
Dáta za 2 roky (December 2016 - November 2018) máme už pripravené v zip archíve.
Budeme potrebovať knižnicu readxl
a zo stránky https://stackoverflow.com si zoberieme funkciu, ktor načíta všetky hárky a spojí ich do štruktúry list, pričom každý prvok bude typu data frame.
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)
}
Vyskúšajme si:
mysheets <- read_excel_allsheets("data/hist_panel_EURIBOR_Dec_2017.xls")
class(mysheets)
## [1] "list"
str(mysheets[[1]])
## 'data.frame': 20 obs. of 9 variables:
## $ 43070 : chr "BNP-Paribas" "Banca Monte Dei Paschi Di Siena" "Banco Bilbao Vizcaya Argentaria" "Banco Santander" ...
## $ 1 Week : num -0.39 -0.38 -0.37 -0.38 -0.38 -0.45 -0.39 -0.37 -0.37 -0.38 ...
## $ 2 Weeks : num -0.41 -0.38 -0.37 -0.37 -0.38 -0.42 -0.39 -0.37 -0.37 -0.38 ...
## $ 1 Month : num -0.41 -0.37 -0.36 -0.37 -0.38 -0.41 -0.38 -0.37 -0.36 -0.37 ...
## $ 2 Months : num -0.39 -0.34 -0.33 -0.34 -0.34 -0.38 -0.35 -0.33 -0.32 -0.34 ...
## $ 3 Months : num -0.34 -0.33 -0.32 -0.33 -0.33 -0.37 -0.33 -0.32 -0.31 -0.33 ...
## $ 6 Months : num -0.31 -0.27 -0.26 -0.27 -0.28 -0.36 -0.28 -0.27 -0.25 -0.27 ...
## $ 9 Months : num -0.27 -0.22 -0.22 -0.22 -0.22 -0.33 -0.22 -0.22 -0.2 -0.22 ...
## $ 12 Months: num -0.23 -0.19 -0.18 -0.19 -0.19 -0.3 -0.19 -0.18 -0.17 -0.19 ...
Otázky:
xls
súbore?Bank
.names(mysheets)
?Dáta v adresári vyzerajú takto:
Vytvoríme si vektor, v ktorom budú názvy jednotlivých súborov. Nemusíme ich ručne vypisovať, lebo majú spoločnú štruktúru, napríklad data/hist_panel_EURIBOR_
na začiatku (ak ich máme v adresári data
) alebo príponu xls
.
Na ukážku, ak by sme chceli iba posledných 12 dostupných mesiacov:
subory.zoznam.1rok <- paste0("data/hist_panel_EURIBOR_",
c("Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov"),
c("_2017", rep("_2018", 11)),
".xls"
)
Vytvorte teraz vektor so všetkými 24 názvami súborov:
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"
Z týchto dát nás bude zaujímať trojmesačná úroková miera.
Najskôr vytvoríme prázdny data frame:
df <- data.frame(Bank = character(), Rate = numeric(), Date = character())
Doplňte nasledovnú kostru kódu:
for (subor in subory.zoznam){
mysheets <- read_excel_allsheets(subor)
for (i in 1:length(mysheets)){
..... # zmente nazov prveho stplca na `Bank`
Date<- names(mysheets)[i] # upravte este tak, aby ste pridali aj rok
df <- rbind(df, ... ) # doplnte
}
}
Čo by sme chceli dostať:
head(df, 30)
## 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
## 7 Belfius -0.32 0112_2016
## 8 CECABANK -0.31 0112_2016
## 9 Caixa Geral De Depósitos -0.29 0112_2016
## 10 CaixaBank S.A. -0.31 0112_2016
## 11 Crédit Agricole s.a. -0.32 0112_2016
## 12 DZ Bank -0.35 0112_2016
## 13 Deutsche Bank -0.17 0112_2016
## 14 HSBC France -0.31 0112_2016
## 15 ING Bank -0.32 0112_2016
## 16 Intesa Sanpaolo -0.31 0112_2016
## 17 National Bank of Greece -0.31 0112_2016
## 18 Natixis -0.31 0112_2016
## 19 Société Générale -0.34 0112_2016
## 20 UniCredit -0.30 0112_2016
## 21 BNP-Paribas -0.40 0212_2016
## 22 Banca Monte Dei Paschi Di Siena -0.31 0212_2016
## 23 Banco Bilbao Vizcaya Argentaria -0.31 0212_2016
## 24 Banco Santander -0.32 0212_2016
## 25 Banque et Caisse d'Épargne de l'État -0.32 0212_2016
## 26 Barclays Bank -0.30 0212_2016
## 27 Belfius -0.32 0212_2016
## 28 CECABANK -0.31 0212_2016
## 29 Caixa Geral De Depósitos -0.29 0212_2016
## 30 CaixaBank S.A. -0.31 0212_2016
Dáta by sme chceli v inom formáte. Preto:
library(reshape2)
df2 <- dcast(df, Date ~ Bank, value.var = "Rate" )
str(df2)
## 'data.frame': 512 obs. of 25 variables:
## $ Date : Factor w/ 512 levels "0112_2016","0212_2016",..: 1 2 3 4 5 6 7 8 9 10 ...
## $ Banca Monte Dei Paschi Di Siena : num -0.31 -0.31 -0.31 -0.31 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Banco Bilbao Vizcaya Argentaria : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Banco Santander : num -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Banque et Caisse d'?pargne de l'?tat: num NA NA NA NA NA NA NA NA NA NA ...
## $ Banque et Caisse d'Épargne de l'État: num -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Barclays Bank : num -0.3 -0.3 -0.3 -0.33 -0.33 -0.33 -0.3 -0.3 -0.3 -0.31 ...
## $ Belfius : num -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ BNP-Paribas : num -0.4 -0.4 -0.4 -0.4 -0.4 -0.4 -0.4 -0.4 -0.4 -0.4 ...
## $ Caixa Geral De Dep?sitos : num NA NA NA NA NA NA NA NA NA NA ...
## $ Caixa Geral De Depósitos : num -0.29 -0.29 -0.29 -0.29 -0.29 -0.29 -0.29 -0.29 -0.29 -0.29 ...
## $ CaixaBank S.A. : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.32 -0.32 -0.32 ...
## $ CECABANK : num -0.31 -0.31 -0.31 -0.31 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Cr?dit Agricole s.a. : num NA NA NA NA NA NA NA NA NA NA ...
## $ Crédit Agricole s.a. : num -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Deutsche Bank : num -0.17 -0.17 -0.17 -0.17 -0.17 -0.17 -0.17 -0.17 -0.17 -0.17 ...
## $ DZ Bank : num -0.35 -0.35 -0.35 -0.35 -0.35 -0.35 -0.35 -0.35 -0.35 -0.35 ...
## $ HSBC France : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 ...
## $ ING Bank : num -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 -0.32 ...
## $ Intesa Sanpaolo : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 ...
## $ National Bank of Greece : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 -0.31 ...
## $ Natixis : num -0.31 -0.31 -0.31 -0.31 -0.31 -0.32 -0.32 -0.32 -0.32 -0.31 ...
## $ Soci?t? G?n?rale : num NA NA NA NA NA NA NA NA NA NA ...
## $ Société Générale : num -0.34 -0.34 -0.33 -0.33 -0.33 -0.33 -0.33 -0.33 -0.33 -0.31 ...
## $ UniCredit : num -0.3 -0.3 -0.3 -0.3 -0.3 -0.3 -0.3 -0.3 -0.3 -0.3 ...
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
To ešte nie je celkom to, čo potrebujeme, ale už sme blízko. Upravte data frame df2
a vyberte z neho banky, ktoré majú dáta pre celé sledované obdobie.
Teraz už máme pripravené dáta a môžeme s nimi pracovať tak ako predtým: zostaviť sieť a použiť ju na zhlukovanie hierarchickým zhlukovaním a hľadaním komunít v minimálnej kostre.