1 事前準備

  • NEEDS-FinancialQUEST（FQ） から「ROE」のデータをダウンロードする。
  • エラーとなるので、Excelファイルからあらかじめ「検索条件シート」を削除しておく必要がある。

2 データの読み込み

# 全てのデータを削除
rm(list = ls())


# Data_rawフォルダにあるROE2015.xlsx--ROE2023.xlsxを読み込む
library(readxl)
ROE2015 <- read_excel("Data_raw/ROE2015.xlsx", sheet = "Sheet1")
ROE2016 <- read_excel("Data_raw/ROE2016.xlsx", sheet = "Sheet1")
ROE2017 <- read_excel("Data_raw/ROE2017.xlsx", sheet = "Sheet1")
ROE2018 <- read_excel("Data_raw/ROE2018.xlsx", sheet = "Sheet1")
ROE2019 <- read_excel("Data_raw/ROE2019.xlsx", sheet = "Sheet1")
ROE2020 <- read_excel("Data_raw/ROE2020.xlsx", sheet = "Sheet1")
ROE2021 <- read_excel("Data_raw/ROE2021.xlsx", sheet = "Sheet1")
ROE2022 <- read_excel("Data_raw/ROE2022.xlsx", sheet = "Sheet1")

# 1〜8行目を削除
ROE2015 <- ROE2015[-c(1:8),]
ROE2016 <- ROE2016[-c(1:8),]
ROE2017 <- ROE2017[-c(1:8),]
ROE2018 <- ROE2018[-c(1:8),]
ROE2019 <- ROE2019[-c(1:8),]
ROE2020 <- ROE2020[-c(1:8),]
ROE2021 <- ROE2021[-c(1:8),]
ROE2022 <- ROE2022[-c(1:8),]

# 年次yearを追加する
ROE2015$year <- 2015
ROE2016$year <- 2016
ROE2017$year <- 2017
ROE2018$year <- 2018
ROE2019$year <- 2019
ROE2020$year <- 2020
ROE2021$year <- 2021
ROE2022$year <- 2022

3 データの結合

# 2015〜2023年のROEデータを結合する
ROE <- rbind(ROE2015, ROE2016, ROE2017, ROE2018, 
              ROE2019, ROE2020, ROE2021, ROE2022)

# 2015〜2023年のROEデータを削除する
rm(ROE2015, ROE2016, ROE2017, ROE2018, 
   ROE2019, ROE2020, ROE2021, ROE2022)

4 データの整理

# ROEの変数名を確認する
names(ROE)
##  [1] "...1"                "...2"                "...3"               
##  [4] "...4"                "Unconsolidated...5"  "Unconsolidated...6" 
##  [7] "Unconsolidated...7"  "Unconsolidated...8"  "Unconsolidated...9" 
## [10] "Unconsolidated...10" "Unconsolidated...11" "Unconsolidated...12"
## [13] "Unconsolidated...13" "Unconsolidated...14" "Unconsolidated...15"
## [16] "Unconsolidated...16" "year"
# Rename "Unconsolidated...5" to "ROE01"
names(ROE)[names(ROE) == "Unconsolidated...5"] <- "ROE1"
names(ROE)[names(ROE) == "Unconsolidated...6"] <- "ROE2"
names(ROE)[names(ROE) == "Unconsolidated...7"] <- "ROE3"
names(ROE)[names(ROE) == "Unconsolidated...8"] <- "ROE4"
names(ROE)[names(ROE) == "Unconsolidated...9"] <- "ROE5"
names(ROE)[names(ROE) == "Unconsolidated...10"] <- "ROE6"
names(ROE)[names(ROE) == "Unconsolidated...11"] <- "ROE7"
names(ROE)[names(ROE) == "Unconsolidated...12"] <- "ROE8"
names(ROE)[names(ROE) == "Unconsolidated...13"] <- "ROE9"
names(ROE)[names(ROE) == "Unconsolidated...14"] <- "ROE10"
names(ROE)[names(ROE) == "Unconsolidated...15"] <- "ROE11"
names(ROE)[names(ROE) == "Unconsolidated...16"] <- "ROE12"

# Rename "...1" to "企業名"
names(ROE)[names(ROE) == "...1"] <- "企業名"

# Rename "...2" to "証券コード"
names(ROE)[names(ROE) == "...2"] <- "証券コード"

# Remove columns 3 and 4
ROE <- ROE[, -c(3, 4)]


# ROE1からROE12を数値に変換
ROE$ROE1 <- as.numeric(ROE$ROE1)
ROE$ROE2 <- as.numeric(ROE$ROE2)
ROE$ROE3 <- as.numeric(ROE$ROE3)
ROE$ROE4 <- as.numeric(ROE$ROE4)
ROE$ROE5 <- as.numeric(ROE$ROE5)
ROE$ROE6 <- as.numeric(ROE$ROE6)
ROE$ROE7 <- as.numeric(ROE$ROE7)
ROE$ROE8 <- as.numeric(ROE$ROE8)
ROE$ROE9 <- as.numeric(ROE$ROE9)
ROE$ROE10 <- as.numeric(ROE$ROE10)
ROE$ROE11 <- as.numeric(ROE$ROE11)
ROE$ROE12 <- as.numeric(ROE$ROE12)

# ROE1からROE12の平均を求める
ROE$ROE_mean <- rowMeans(ROE[, 3:14], na.rm = TRUE)

# ROE1からROE12を削除する
ROE <- ROE[, -c(3:14)]

5 証券コードから”T”を除く

# 証券コードから"T"を除く
ROE$証券コード <- gsub("T", "", ROE$証券コード)

6 csvにデータを保存

# csvにデータを保存
write.csv(ROE, "Data_output/ROE.csv", row.names = FALSE)