自動トレーディングへの道 Archive

データベースをExcelなど表計算ソフトで見る

Rubyにsqlite3-rubyを入れてやるとRubyからSQLiteが使える。Javaよりこっちの方が手軽に思う。

#!/usr/bin/ruby

require 'sqlite3'

db = SQLite3::Database.new('test.db')
sql = "SELECT time,price,volume FROM _5411"
db.execute(sql) do |row|
	milisec = row[0].to_i
	milisec /= 1000
	t = Time.at(milisec)
	a = t.to_a

#	print t.to_s + ", "
	print a[2].to_s + ":" + a[1].to_s + ":" + a[0].to_s + ", "
	print row[1] + ", "
	print row[2] + "\n"
end

Javaの時刻は1970年1月1日から数えてミリ秒単位で表す。Rubyは秒単位らしいので、まず1000で割ってTime.at()で時刻オブジェクトを取得する。to_aで配列に変換すると時、分、秒を取得しやすいので変換しておく。単に人間が見るだけなら.to_sで文字列に変換してやるのがよいだろう。

結果

[piyo@MacBookPro ~/Source/Ruby]# ruby Monex2DB.rb
9:29:13, 4450, 630200
9:29:27, 4440, 636500
9:29:35, 4450, 637000
9:29:43, 4460, 648100
9:29:54, 4460, 648200
9:30:8, 4450, 648300
9:30:15, 4460, 651600
9:30:23, 4460, 657600
9:30:30, 4460, 663900
9:30:38, 4460, 664000
9:30:45, 4450, 692500
9:30:59, 4440, 706500
9:31:6, 4450, 716800
9:31:14, 4450, 718500
9:31:21, 4450, 718700
9:31:28, 4440, 720100
9:31:49, 4450, 730100
9:31:57, 4450, 730900
（略）

これをリダイレクトでも何でもファイルに保存すれば表計算で扱える。

SQLiteに株価を記録する

閑話休題で、今までのプロジェクトから一部のコードを引っこ抜いてSQLiteにデータを記録してみる。

方針

株価を監視するStockMonitorクラスを作り、ここで株価をデータベースにごりごり書き込む。急落、急騰を察知したら通知する。

StockMonitorクラス

staticなchainを作り、コンストラクタでthisをadd()すると、呼び出し側はnew StockMonitor();するだけでインスタンスを保持する必要がない。全てのインスタンスに対して処理を行うためにstaticなメソッドeach()を用いてchainを全部たどって必要な処理を行う。

public class StockMonitor {
	private static List chain = new ArrayList();
	private int code;

	public StockMonitor(int code) {
		this.code = code;
		chain.add(this);

		// テーブルがないときはテーブルを作る
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
			Statement stat = conn.createStatement();

			String sql = SQL.getSQLFromFile("CreateTable.sql");
			sql = sql.replace("_PLEASE_REPLACE", "_" + code);

			System.out.println(sql);
			stat.executeUpdate(sql);
		} catch (Exception e) {
		}
	}

	public static void each() {
		Iterator it = chain.iterator();

		while (it.hasNext()) {
			StockMonitor m = it.next();
			m.update();
		}
	}

	public void update() {
		try {
			getPrice();
			writeDatabase();
			findSharpDrop();
		} catch (CodeNotFoundException e) {}
	}

SQLをもうちょっと綺麗に書きたい

今のところSQLをStringBufferにappend()しているけれど、これは読みにくい。もう少し何とかならないのかと思ってSQLクラスを作ってみた。

public class SQL {
	// SQLファイルを読み、1行のStringに変換して返す
	public static String getSQLFromFile(String filename) {
		StringBuffer sql = new StringBuffer();

		try
		{
			FileInputStream fis = new FileInputStream(filename);
			InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
			BufferedReader br = new BufferedReader(isr);

			String line = "";
			while(( line = br.readLine()) != null )
			{
				sql.append(line);
			}

			fis.close();
			isr.close();
			fis.close();

		}
		catch (Exception e1)
		{
			e1.printStackTrace();
		}

		// タブをスペース1個に置き換える
		return sql.toString().replaceAll("\t+", " ");
	}
}

これで、SQLをファイルに書いて転がしておける。

CREATE TABLE _PLEASE_REPLACE
(
	id			INTEGER NOT NULL PRIMARY KEY,
	time		TIME,
	price		INTEGER,
	volume		INTEGER
);

とでも書いておいて、Stringメソッドのreplace()を使って”_PLEASE_REPLACE”を置き換える。

getPrice()メソッド

前回のMonexから株価引っこ抜きコードをそのまま使うが、今回は株価と出来高のみ注目する。時刻はメソッドを呼び出した側で取得することにする。JavaはSystem.currentTimeMillis()でミリ秒単位で時刻を取得できるので、これを使う。

private void getPrice() throws CodeNotFoundException {
	// 株価ページを取得
	Monex2 application = Manager.getApplication();
	String url = application.makeSeaechStockCodeURL(code);

	GetMethod get = new GetMethod(url);
	HTTP.getInstance().executeMethod(get);
	get.releaseConnection();

	// 株価コードがない
	if (HTTP.getInstance().getResult().indexOf("該当する銘柄がありません") ＞= 0) {
		throw new CodeNotFoundException();
	}

	// 大ざっぱに該当部分を切り出す
	Perl5Util pu = new Perl5Util();
	String pattern = "/＜br＞(\\(\\d\\d\\d\\d\\).*?現値.*?前比.*?売気.*?株数.*?買気.*?株数.*?始値.*?高値.*?安値.*?出来.*?)＜br＞＜br＞＜a href=\"quote.cgi?/";
	if (pu.match(pattern, HTTP.getInstance().getResult())) {
		// さらに＜br＞区切りで要素に分ける
		String result = pu.group(1);
		String[] element = result.split("＜br＞");

		// 株価を取得
		pattern = "/現値: (.*?)$/";
		if (pu.match(pattern, element[3])) {
			price = NumUtilities.toInt(pu.group(1));
		}

		// 出来高を取得
		pattern = "/出来:.*?(\\d.*)/";
		if (pu.match(pattern, element[12])) {
			volume = NumUtilities.toInt(pu.group(1));
		}
	}
}

writeDatabase()メソッド

	private void writeDatabase() {
		// 出来高が変わっていないときはリターン
		if (volume == previous)
			return;

		previous = volume;

		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
			Statement stat = conn.createStatement();

			StringBuffer s = new StringBuffer();
			s.append("INSERT INTO _" + code + " VALUES(");
			s.append("NULL, ");
			s.append("'" + System.currentTimeMillis() + "', ");
			s.append("" + price + ", ");
			s.append("" + volume + " ");
			s.append("); ");

			// System.out.println(s);
			stat.executeUpdate(s.toString());
		} catch (Exception e) {}
	}

findSharpDrop()メソッド

これはいい加減に書いたせいか、ろくに機能しなかった。寄り付きではたくさん報告するけど、ザラ場では一度も引っかかることがない。これはあとで見直す必要がありそう。原理は現在時刻からミリ秒で1分前を計算して、SQLでそれ以降のものを列挙してリストに放り込む。高値と安値の差が1%を超えている場合、急落あるいは急騰と判断することにする。

private void findSharpDrop() {
	long minute = 1000 * 60;
	long time = System.currentTimeMillis() - minute;

	try {
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
		Statement stat = conn.createStatement();

		String sql = "SELECT * FROM _" + code + " WHERE time > " + time + ";";
		ResultSet rs = stat.executeQuery(sql);

		List list = new ArrayList();
		while (rs.next()) {
			list.add(NumUtilities.toInt(rs.getString("price")));
		}

		// コレクションを整列する
		if( list.isEmpty() != true )
		{
			Collections.sort(list);

			double min = list.get(0);
			double max = list.get(list.size()-1);				

			if( max / min > 1.01 )
			{
				System.out.println("SHARP DROP:(" + code + "):"+max+"-"+min);
			}
		}

	} catch (Exception e){}
}

実行結果

今日の朝は暴落が酷かったので、落ち着いてから起動して、引けまで放置した。できあがったデータベース。出来高が変化しなかったときは追加しない5秒ごとの株価推移。さて、これをどう料理しようか。

ダウンロード

project

今回のコード一式と2008年3月2日のザラ場に起動しっぱなしにして生成したデータベースも付属します。

株の時系列データをどう扱うか

とりあえずデータベースに放り込んでみることにする。データベースの操作もうまくラッパーを作ってしまえばSQLiteだろうとMySQLだろうと使えるはず。

SQLite JDBC Driver

検索してみるとすぐに色々見つかる。Javaで使うのは面倒くさいという話もあるけど、とりあえずSQLite JDBC Driverを試す。WindowsとMac OS X向けにはJNIを使ったネイティブ版もあるけれど、せっかくJavaを使うのだからPure Javaで試してみたい。ダウンロードして解凍するとsqlitejdbc-v037-nested.jarというものが出てくるので、パスの通っているところに設置。

サンプルコード

まずはSQLite JDBC Driverのサイトのコードをそのまま試す。

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLiteTest {

/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("create table people (name, occupation);");
stat.executeUpdate("insert into people values ('Gandhi', 'politics');");
stat.executeUpdate("insert into people values ('Turing', 'computers');");
stat.executeUpdate("insert into people values ('Wittgenstein', 'smartypants');");

ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("occupation = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
}

実行結果

なんなくビルドは通ってtest.dbというファイルが作られる。結果を確認するべくSQLite Database Browserでtest.dbを開く。

このようにちゃんとデータベースが作られている。