景気ウォッチャー指数をトレードステーション形式でダウンロードする!! : 豊島政一の投資ブログ

Rで内閣府のページから景気ウォッチャー指数のエクセルデータを取り出して、トレードステーション形式で保存するスクリプトを作りました。

エクセルのフォーマットが、特殊加工してあり、なかなか手ごわかったです。あまり、参考にする人もいないかもしれませんが、このスクリプトをホームページ更新後に実行すれば、30秒でトレードステーションに読み込めるようになりました。

季節調整値のダウンロード

library(gdata)
curl <- "https://www5.cao.go.jp/keizai3/watcher/watcher5.xls"
cdestfile <- "C:/Users/Downloads/A.xls"
download.file(curl,cdestfile,mode="wb")

library("readxl")
data <- read_excel("C:/Users/Downloads/A.xls", sheet = 1)
data

library(dplyr)
library(tidyverse)

data.result = filter(data, rowSums(is.na(data)) < ncol(data)-10)

names(data.result)[1] <- "year"
names(data.result)[2] <- "date"
names(data.result)[3] <- "open"

data.result = fill(data.result, "year")

data.result = mutate(data.result, date = str_sub(date, start=-2, end=-1))

data.result = mutate(data.result, date = str_replace(date, pattern="01", replacement="1/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="02", replacement="2/28"))
data.result = mutate(data.result, date = str_replace(date, pattern="03", replacement="3/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="04", replacement="4/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="05", replacement="5/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="06", replacement="6/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="07", replacement="7/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="08", replacement="8/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="09", replacement="9/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="10", replacement="10/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="11", replacement="11/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="12", replacement="12/31"))

data.result = mutate(data.result, year = str_sub(year, start=1, end=4))

data.result = mutate(data.result, date = str_c(year,"/", date))

data.result = select(data.result, 2:3)

data.result = mutate(data.result, high = (open))
data.result = mutate(data.result, low = (open))
data.result = mutate(data.result, close = (open))
data.result = mutate(data.result, volume = 0)

data.result = mutate(data.result, date = str_replace(date, pattern="2004/2/28", replacement="2004/2/29"))
data.result = mutate(data.result, date = str_replace(date, pattern="2008/2/28", replacement="2008/2/29"))
data.result = mutate(data.result, date = str_replace(date, pattern="2012/2/28", replacement="2012/2/29"))
data.result = mutate(data.result, date = str_replace(date, pattern="2016/2/28", replacement="2016/2/29"))
data.result = mutate(data.result, date = str_replace(date, pattern="2020/2/28", replacement="2020/2/29"))
data.result

write.csv(data.result, "D:/data/watch_gk.csv",, row.names=FALSE)

data <- read_excel("C:/Users/Downloads/A.xls", sheet = 2)

data.result = filter(data, rowSums(is.na(data)) < ncol(data)-10)

names(data.result)[1] <- "year"
names(data.result)[2] <- "date"
names(data.result)[3] <- "open"

data.result = fill(data.result, "year")

data.result = mutate(data.result, date = str_sub(date, start=-2, end=-1))

data.result = mutate(data.result, year = str_sub(year, start=1, end=4))

data.result = mutate(data.result, date = str_c(year,"/", date))

data.result = select(data.result, 2:3)

data.result = mutate(data.result, high = (open))
data.result = mutate(data.result, low = (open))
data.result = mutate(data.result, close = (open))
data.result = mutate(data.result, volume = 0)

write.csv(data.result, "D:/data/watch_sk.csv",, row.names=FALSE)

原数値のダウンロード

library(gdata)
curl <- "https://www5.cao.go.jp/keizai3/watcher/watcher3.xls"
cdestfile <- "C:/Users/Downloads/A.xls"
download.file(curl,cdestfile,mode="wb")

library("readxl")
data <- read_excel("C:/Users/Downloads/A.xls", sheet = 1)
data

library(dplyr)
library(tidyverse)
data.result = filter(data, rowSums(is.na(data)) < ncol(data)-7)

names(data.result)[1] <- "year"
names(data.result)[2] <- "date"
names(data.result)[3] <- "open"

data.result = fill(data.result, "year")

#data.result = mutate(data.result, date = str_sub(date, start=-2, end=-1))

data.result = mutate(data.result, date = str_replace(date, pattern="１月", replacement="1/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="２月", replacement="2/28"))
data.result = mutate(data.result, date = str_replace(date, pattern="３月", replacement="3/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="４月", replacement="4/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="５月", replacement="5/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="６月", replacement="6/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="７月", replacement="7/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="８月", replacement="8/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="９月", replacement="9/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="10月", replacement="10/31"))
data.result = mutate(data.result, date = str_replace(date, pattern="11月", replacement="11/30"))
data.result = mutate(data.result, date = str_replace(date, pattern="12月", replacement="12/31"))

data.result = mutate(data.result, year = str_sub(year, start=1, end=4))

data.result = mutate(data.result, date = str_c(year,"/", date))

data.result = select(data.result, 2:3)

data.result = mutate(data.result, high = (open))
data.result = mutate(data.result, low = (open))
data.result = mutate(data.result, close = (open))
data.result = mutate(data.result, volume = 0)

data.result = filter(data.result, rowSums(is.na(data.result)) < ncol(data.result)-1)

write.csv(data.result, "D:/data/watch_g.csv",, row.names=FALSE)

data <- read_excel("C:/Users/Downloads/A.xls", sheet = 2)

data.result = filter(data, rowSums(is.na(data)) < ncol(data)-7)

names(data.result)[1] <- "year"
names(data.result)[2] <- "date"
names(data.result)[3] <- "open"

data.result = fill(data.result, "year")

#data.result = mutate(data.result, date = str_sub(date, start=-2, end=-1))

data.result = mutate(data.result, year = str_sub(year, start=1, end=4))

data.result = mutate(data.result, date = str_c(year,"/", date))

data.result = select(data.result, 2:3)

data.result = mutate(data.result, high = (open))
data.result = mutate(data.result, low = (open))
data.result = mutate(data.result, close = (open))
data.result = mutate(data.result, volume = 0)

data.result = filter(data.result, rowSums(is.na(data.result)) < ncol(data.result)-1)

data.result

write.csv(data.result, "D:/data/watch_s.csv",, row.names=FALSE)

読み込んだ結果

景気ウォッチャー投資法の成績をみると、やはりコロナショックはとらえられなかったようです。2月以降の下落で損失を出しています。

季節調整値

原数値

改良版コード

数字の置換部分を少しスマートにしてみました。日本語の場合は、全角数字の処理が一番困ります。

library(gdata)

library("readxl")

library(dplyr)

library(tidyverse)

curl <- "https://www5.cao.go.jp/keizai3/watcher/watcher5.xls"

cdestfile <- "C:/A.xls"

download.file(curl,cdestfile,mode="wb")

data <- read_excel("C:/A.xls", sheet = 1)

XX = filter(data, rowSums(is.na(data)) < ncol(data)-7)

names(XX)[1] <- "year"

names(XX)[2] <- "date"

names(XX)[3] <- "open"

XX = fill(XX, "year")

XX = mutate(XX, date = str_sub(date, start=-2, end=-1))

XX = mutate(XX, date = as.integer(date))

XX = mutate(XX, date = as.character(date))

XX = mutate(XX, date = if_else((condition = date == 4|date == 6|date == 9|date == 11 ) , true = str_c(date, "/30"), false = if_else((condition = date == 2 ) , true = str_c(date, "/28"), false = str_c(date, "/31"))))

XX = mutate(XX, date = if_else((condition = (year == "2004年"|year == "2008年"|year == "2016年"|year == "2020年")&(date == "2/28") ) , true = "2/29", false = date ))

XX = mutate(XX, year = str_replace(year, pattern="年", replacement="/"))

XX = mutate(XX, date = str_c( year,date ))

XX = select(XX, 2:3)

XX = mutate(XX, high = open)

XX = mutate(XX,low = open)

XX = mutate(XX,close = open)

XX = mutate(XX,volume = 0)

write.csv(XX, "D:/watch_gk.csv",, row.names=FALSE)

data <- read_excel("C:/A.xls", sheet = 2)

XX = filter(data, rowSums(is.na(data)) < ncol(data)-7)

names(XX)[1] <- "year"

names(XX)[2] <- "date"

names(XX)[3] <- "open"

XX = fill(XX, "year")

XX = mutate(XX, date = str_sub(date, start=-2, end=-1))

XX = mutate(XX, date = as.integer(date))

XX = mutate(XX, date = as.character(date))

XX = mutate(XX, date = if_else((condition = (year == "2004年"|year == "2008年"|year == "2016年"|year == "2020年")&(date == "2/28") ) , true = "2/29", false = date ))

XX = mutate(XX, year = str_replace(year, pattern="年", replacement="/"))

XX = mutate(XX, date = str_c( year,date ))

XX = select(XX, 2:3)

XX = mutate(XX, high = open)

XX = mutate(XX,low = open)

XX = mutate(XX,close = open)

XX = mutate(XX,volume = 0)

write.csv(XX, "D:/watch_sk.csv",, row.names=FALSE)

curl <- "https://www5.cao.go.jp/keizai3/watcher/watcher3.xls"

cdestfile <- "C:/A.xls"

download.file(curl,cdestfile,mode="wb")

data <- read_excel("C:/A.xls", sheet = 1)

XX = filter(data, rowSums(is.na(data)) < ncol(data)-7)

names(XX)[1] <- "year"

names(XX)[2] <- "date"

names(XX)[3] <- "open"

XX = fill(XX, "year")

XX = mutate(XX, date = str_replace(date, pattern="月", replacement=""))

XX = mutate(XX, date = str_replace(date, pattern="１", replacement="1"))

XX = mutate(XX, date = str_replace(date, pattern="２", replacement="2"))

XX = mutate(XX, date = str_replace(date, pattern="３", replacement="3"))

XX = mutate(XX, date = str_replace(date, pattern="４", replacement="4"))

XX = mutate(XX, date = str_replace(date, pattern="５", replacement="5"))

XX = mutate(XX, date = str_replace(date, pattern="６", replacement="6"))

XX = mutate(XX, date = str_replace(date, pattern="７", replacement="7"))

XX = mutate(XX, date = str_replace(date, pattern="８", replacement="8"))

XX = mutate(XX, date = str_replace(date, pattern="９", replacement="9"))

XX = mutate(XX, date = if_else((condition = date == 4 |date == 6|date == 9|date == 11 ) , true = str_c(date, "/30"), false = if_else((condition = date == 2 ) , true = str_c(date, "/28"), false = str_c(date, "/31"))))

XX = mutate(XX, date = if_else((condition = (year == "2004年"|year == "2008年"|year == "2016年"|year == "2020年")&(date == "2/28") ) , true = "2/29", false = date ))

XX = mutate(XX, year = str_replace(year, pattern="年", replacement="/"))

XX = mutate(XX, date = str_c( year,date ))

XX = select(XX, 2:3)

XX = mutate(XX, high = open)

XX = mutate(XX,low = open)

XX = mutate(XX,close = open)

XX = mutate(XX,volume = 0)

XX = filter(XX, rowSums(is.na(XX)) < ncol(XX)-1)

write.csv(XX, "D:/watch_g.csv",, row.names=FALSE)