金価格（時系列データ）の情報源の紹介

【概要】

このページでは，金価格の変動データ（時系列データ）の情報源を紹介する。 さらに，入手したデータを整形し，SQLite 3 のテーブル lbma を生成し，R でプロットする体験を行う。

◆ 作成するSQLite 3 データベース: lbmadb（テーブル名: lbma）

【目次】

【関連する外部ページ】

【サイト内の関連情報】

* データ源の URL: https://www.lbma.org.uk/prices-and-data/lbma-precious-metal-prices

* 最新の状況について: LBMA の金価格（LBMA Gold Price）は現在 ICE Benchmark Administration（IBA）が運営しており， 過去データの一括入手にはライセンスが必要になっている（LBMA の Web ページには当日を含む直近の価格のみが掲載され，過去の表形式データは MyLBMA ポータルに移された）。 このページの手順は，かつて LBMA が公開していた年ごとのタブ区切りデータ（Daily gold prices）を前提とした教材である。 利用条件は各自で確認し，入手できるデータの範囲で演習を行うこと。

免責事項：このページでは，金価格のデータを扱うが，あくまでも入門演習での教材データを作る手順である。投資判断等に用いないこと。

第1章 データの入手と整形の手順

利用条件（ライセンス）を必ず確認すること。利用条件に同意できない場合は，利用（ダウンロード）しないこと。

  1. Web ページを開く

    Web ブラウザで， The London Bullion Market Association の「LBMA Precious Metal Prices」の Web ページ https://www.lbma.org.uk/prices-and-data/lbma-precious-metal-prices を開き，画面の指示に従って，入手できる範囲の金価格データを入手する。

  2. 入手したデータ（タブ区切り）を「a.tsv」のような名前で /tmp に保存する。

    拡張子を「.tsv」にしておく。データは 1 行 1 日で，「日付，USD(AM)，GBP(AM)，EUR(AM)，USD(PM)，GBP(PM)，EUR(PM)」の並びであるとする。

  3. 次の bash プログラムを使い，ファイルを調整する。
    • 区切りをタブからカンマに変える
    • 日付表記を「23-Oct-13」から「2013-10-23」の表記に変える
    • すべての行を同じ列数に揃える（欠けている列に「NULL」を書き足す）→ R プログラムで行う

    拡張子「.csv」のファイルができる。

    ◆ bash プログラム 

    #!/bin/bash
#  23-Oct-13    1333.00    825.132    968.680    1331.25    823.335    966.355 -> 2013-10-23,1333.00,825.132,968.680,1331.25,823.335,966.355
cd /tmp
for i in *.tsv; do
  cat $i | sed 's/\t/,/g' | sed 's/-13/-2013/g' | sed 's/-12/-2012/g' | sed 's/-11/-2011/g' | sed 's/-10/-2010/g' | sed 's/-09/-2009/g' | sed 's/-08/-2008/g' | sed 's/-07/-2007/g' | sed 's/-06/-2006/g' | sed 's/-05/-2005/g' | sed 's/-04/-2004/g' | sed 's/-03/-2003/g' | sed 's/-02/-2002/g' | sed 's/-01/-2001/g' | sed 's/-00/-2000/g' | sed 's/-99/-1999/g' | sed 's/-98/-1998/g' | sed 's/-97/-1997/g' | sed 's/-96/-1996/g' | sed 's/-95/-1995/g' | sed 's/-94/-1994/g' | sed 's/-93/-1993/g' | sed 's/-92/-1992/g' | sed 's/-91/-1991/g' | sed 's/-90/-1990/g' | sed 's/-89/-1989/g' | sed 's/-88/-1988/g' | sed 's/-87/-1987/g' | sed 's/-86/-1986/g' | sed 's/-85/-1985/g' | sed 's/-84/-1984/g' | sed 's/-83/-1983/g' | sed 's/-82/-1982/g' | sed 's/-81/-1981/g' | sed 's/-80/-1980/g' | sed 's/-79/-1979/g' | sed 's/-78/-1978/g' | sed 's/-77/-1977/g' | sed 's/-76/-1976/g' | sed 's/-75/-1975/g' | sed 's/-74/-1974/g' | sed 's/-73/-1973/g' | sed 's/-72/-1972/g' | sed 's/-71/-1971/g' | sed 's/-70/-1970/g' | sed 's/-69/-1969/g' | sed 's/-68/-1968/g' | sed 's/-67/-1967/g' | sed 's/-66/-1966/g' | sed 's/-65/-1965/g' | sed 's/-64/-1964/g' | sed 's/-63/-1963/g' | sed 's/-62/-1962/g' | sed 's/-61/-1961/g' | sed 's/-60/-1960/g' |  sed 's/-Jan/-01/g' | sed 's/-Feb/-02/g' | sed 's/-Mar/-03/g' | sed 's/-Apr/-04/g' | sed 's/-May/-05/g' | sed 's/-Jun/-06/g' | sed 's/-Jul/-07/g' | sed 's/-Aug/-08/g' | sed 's/-Sep/-09/g' | sed 's/-Oct/-10/g' | sed 's/-Nov/-11/g' | sed 's/-Dec/-12/g' | sed 's/\([0-9]*\)-\([0-9]*\)-\([0-9]*\)/\3-\2-\1/g' > /tmp/`basename $i .tsv`.csv
done

# csv ファイルの列の数を整える
for i in *.tsv; do
echo $i
cat >/tmp/a.$$.r <<-RCOMMAND
    library(xts)
    library(chron)
    library(forecast)
    X <- read.csv("/tmp/a.csv", header=TRUE)
    D <- as.xts(read.zoo(X))
    write.csv(as.data.frame(D), file="/tmp/b.csv", na="NULL")
RCOMMAND
  rm -f /tmp/a.csv /tmp/b.csv
  cp /tmp/`basename $i .tsv`.csv /tmp/a.csv
  cat /tmp/a.$$.r | r
  cat /tmp/b.csv | sed 's/"//g'  > /tmp/`basename $i .tsv`.csv
done
rm -f /tmp/lbma.csv
cat *.csv | fgrep -v USD > /tmp/lbma.csv

    ヒント: 最後の「fgrep -v USD」は，各 CSV ファイルのヘッダ行（USD という文字列を含む行）を取り除くための処理である。

第2章 SQLite 3 データベースの生成

SQLite 3

あらかじめ決めておく事項

このページでは，SQLite 3 データベースの生成を行う。 生成するSQLite 3 データベースのデータベース名を決めておくこと。 このページでは，次のように書く。

テーブルの作成

  1. テーブル定義

    1999年以降（1999年を含む）用のテーブル定義（それ以前は EUR がない）

    • lbma(at, USD_AM, GBP_AM, EUR_AM, USD_PM, GBP_PM, EUR_PM)

    ◆ bash プログラム 

    #!/bin/bash

rm -f /tmp/lbmadb
#
cat >/tmp/a.$$.sql <<-SQL
drop table if exists lbma;
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/lbmadb
#
cat >/tmp/a.$$.sql <<-SQL
create table lbma (
  at datetime,
  USD_AM real,
  GBP_AM real,
  EUR_AM real,
  USD_PM real,
  GBP_PM real,
  EUR_PM real );
SQL
cat /tmp/a.$$.sql | sqlite3 /tmp/lbmadb
  2. テーブルへのデータの取り込み

    ◆ bash プログラム 

    #!/bin/bash

cat >/tmp/a.$$.sql <<-SQL
.mode csv
.import /tmp/a.$$.csv lbma
SQL
#
cat /tmp/lbma.csv > /tmp/a.$$.csv
cat /tmp/a.$$.sql | sqlite3 /tmp/lbmadb
#
echo 'select * from lbma limit 10;' | sqlite3 /tmp/lbmadb

    SQLiteman で確認

  3. R のプログラム例を用いてプロット

    *　「プログラム 'r' はまだインストールされていません。」と表示されるときは「sudo apt -y install littler」を実行する。

    ◆ ggplot() を使用 (R プログラム) 

      library(ggplot2)
  library(RSQLite)
  library(xts)
  library(chron)

  fetch_table <- function(sqlite3dbname, tablename) {
    drv <- dbDriver("SQLite", max.con = 1)
    conn <- dbConnect(drv, dbname=sqlite3dbname)
    rs <- dbSendQuery( conn, paste("SELECT * from", tablename, ";") )
    T <- fetch(rs, n = -1)
    return(T)
  }

  T <- fetch_table("/tmp/lbmadb", "lbma")

#
  table_to_melt <- function(T, tsvec, tsformat) {
    # 先に xts 型に変換しておく
    D <- as.xts( read.zoo( T, tsformat ) )
    # melt(Date, AttrNum, Value).
    Date <- rep(strptime(tsvec, tsformat, tz=""), ncol(D))
    AttrNum <- rep(1:ncol(D), each=nrow(D))
    Value <- as.numeric(as.vector(D))
    return( data.frame(Date=Date, AttrNum=AttrNum, Value=Value) )
  }

  M <- table_to_melt(T, T$at, "%Y-%m-%d")
#
  ggplot(M, aes(x=Date, y=Value, colour=factor(AttrNum))) + geom_point(size=1);
#
  ggplot(subset(M, AttrNum==3), aes(x=Date, y=Value, colour=factor(AttrNum))) + geom_point(size=1);

    ◆ plot.zoo() を使用 (bash プログラム) 

    #!/bin/bash
cat > /tmp/a.r <<-COMMAND
  library(RSQLite)
  library(xts)
  library(chron)
  drv <- dbDriver("SQLite", max.con = 1)
  conn <- dbConnect(drv, dbname="/tmp/lbmadb")
  rs <- dbSendQuery( conn, "SELECT * from lbma;" )
  T <- fetch(rs, n = -1)
  D <- as.xts(read.zoo(T))
#
  png("/tmp/a.png");
  plot.zoo(D, col=1:100, plot.type="single", type="p", cex=0.1)
  dev.off()
COMMAND
cat /tmp/a.r | r
display /tmp/a.png

考察ポイント