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

このページでは，金価格の変動データ（時系列データ）の情報源を紹介します

さらに、SQLite 3 のテーブル lbma を生成します。

◆ 作成するSQLite 3 データベース: lbmadb

* データ源の URL: http://www.lbma.org.uk/pages/index.cfm?page_id=53&title=gold_fixings

免責事項：このページでは，金価格のデータを扱いますが，あくまでも入門演習での教材データを作る手順である．

ダウンロード手順

Web ページを開く
Web ブラウザで， The London Bulion Market Association の「Daily gold prices」の Web ページ http://www.lbma.org.uk/pages/index.cfm?page_id=53&title=gold_fixings を開く．
http://www.lbma.org.uk/pages/?page_id=53&title=gold_fixings&show=2013&type=daily
ダウンロードしたら「a.tsv」のような名前で保存
拡張子を「.tsv」にしておく

次の 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

SQLite 3 データベースの生成

SQLite 3

SQLite 3 のインストール: SQLite 3インストール，データベース作成，テーブル定義，レコード挿入（Windows 上）別ページ »で説明

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

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

データベース名: lbmadb
データベース名は，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

テーブルの作成

テーブル定義

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

lbma(Date, 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 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

テーブルの作成

◆ 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 で確認

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