Web公開情報から株価を自動取得しFlotでチャート化
September 13, 2014 – 11:02 amYahoo Finance上に公開されている株価を自動的に抽出し、データベース上に蓄積、そして画像描画ソフトFlotを用いてチャート化する一連の手続きを実現してみた。
Webからの公開情報からのデータの抽出方法などについて、これまで本ブログ上に記載してきているものを用いている。今回は、これらの要素技術を組み合わせて、どの程度のことが可能になるか、実際に試してみた。
今回作成したPHP、JavaScriptなどのソースをメモしておいた。
株価の日足チャート表示ツール： 今回作成した株価日足チャート描画用ツールを以下に示す：
Flot Sample: Daily Stock
銘 柄: 日 付:
— Data at the Cursored Position —————————–
Label: stock_data Time: hh:mm Value:
このツール上では、データベース上に蓄積した株価データ（１分おきにYahoo Financeの株価を抽出蓄積）を銘柄、日付を指定し検索抽出。そして[DRAW LINE]ボタンを押釦することによって、指定した株価データの日足のチャートを描画表示する。２銘柄までの株価日足を表示できる。異なる日時の株価チャートをトータル5本まで重ねて表示できる。描画した株価チャートは[RESET LINE]ボタンを押釦することによりリセットできる。また、チャートのライン上にカーソルをあてるとチャート下方にそのポイントに対応する銘柄、日時そして株価が表示される。
チャート図作成に用いている各種ソースを以下に掲げる。なお、ソースの中身についての説明は割愛。
HTMLソース：
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>Checking Flot</title>
<!--[if lt IE 9]> <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]--> <script type="text/javascript" src="./jquery.js"></script>
<script type="text/javascript" src="./flot/jquery.flot.js"></script>
<script type="text/javascript" src="./flot/jquery.flot.axislabels.js"></script>
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/themes/base/jquery.ui.all.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>
<style>
.ui-datepicker {
font-size: 70%;
}
</style>
</head>
<body>
<p>Flot Sample: Daily Stock</p>
<div class="data_box">
<span class='stocknm'> 銘 柄: </span>
<select name="sample">
<option value='1'></option>
<option value='2'>三菱重工業</option>
<option value='3'>三菱自動車</option>
<option value='4'>三菱ＵＦＪ</option>
<option value='5'>全日空</option>
<option value='6'>東芝</option>
<option value='7'>ソニー</option>
<option value='8'>パナソニック</option>
<option value='9'>味の素</option>
<option value='10'>旭化成</option>
<option value='11'>大日本印刷</option>
<option value='12'>日立製作所</option>
<option value='13'>三菱マテリアル</option>
<option value='14'>シャープ</option>
<option value='15'>東京電力</option>
<option value='16'>トヨタ自動車</option>
<option value='17'>日本郵船</option>
<option value='18'>三井不動産</option>
</select>
<span class='seldate'> 日 付: </span> <input type='text' name='date' id='datepicker'>
<span class='drawbutton'><input type="button" value="DRAW LINE " /></span>
<span class='resetbutton'><input type="button" value="RESET LINE" /></span>
</div>
<div id="placeholder" style="width:550px;height:350px;"></div>
<p>-- Data at the Cursored Position -----------------------------</p>
<p>Label : <span id="label"> stock: date </span> Time : <span id="time">hh:mm</span> Value:<span id="value"></span></p>
<script type="text/javascript" src="./stock_daily.js"></script>
</body>
</html>
JavaScript stock_daily.js ソース：
$(document).ready(function() {
var date_val = '';
var istock = '';
var istep;
var istep_max;
var color = ['#3366CC', '#DC3912', '#FF9900', '#109618', '#990099'];
$('select[name="sample"] option:first').prop('selected',true);
$("#placeholder").css({'font-size':'12px','line-height':'1.1em' });
flot_initialize();
var previousPoint = null;
$("#placeholder").bind("plothover", function (event, pos, item ) {
if( item ) {
if( previousPoint != item.dataIndex ) {
previousPoint = item.dataIndex;
// item.series.istep
$("#label").text( item.series.dummy );
$("#time").text( timetohm( item.datapoint[0] ) );
$("#value").text( item.datapoint[1] );
}
else {
previousPoint = null;
}
}
});
$(".drawbutton").click( function() {
var no_is = istock_array.length;
var no_dt = date_array.length;
var s_flag= false;
if ( no_is != no_dt ) {
alert ('something wrong! array length unbalance');
return;
}
if ( no_is == 0 && date_val=='' ) return;
if ( no_is != 0 ) {
for (k_step=0; k_step<no_is; k_step++ ) {
if( date_val==date_array[k_step] && istock==istock_array[k_step]) {
if(date_val==str_today()) {
istep = k_step+1;
draw_line( date_val, istock );
}
s_flag = true;
}
}
}
if( !s_flag || no_is==0 ) {
istep=no_is+1;
date_array.push( date_val );
istock_array.push( istock );
draw_line (date_val, istock )
}
});
$(".resetbutton").click( function() {
$('#datepicker').val('');
date_val = '';
$('select[name="sample"] option:first').prop('selected',true);
flot_initialize();
$("#label").text(' stock: date ' );
$("#time").text('hh:mm' );
$("#value").text(' ' );
});
$('select[name="sample"]').change(function () {
istock = $('select[name="sample"]').val();
label_text = $('[name="sample"] option:selected').text();
});
$('#datepicker').datepicker(
{
dateFormat: "yy-mm-dd",
onSelect: function(dateText, inst) {
date_val = dateText;
}
});
function flot_initialize() {
options = {xaxis :
{
min: new Date('2011/01/01 18:00').getTime(),
max: new Date('2011/01/02 00:00').getTime(),
mode : "time",
timeformat: "%h:%M",
},
yaxes: [
{
font: {
color:"#545454"
},
//axisLabel: "",
axisLabelUseCanvas: true,
axisLabelFontSizePixels: 10,
axisLabelFontFamily: "Verdana, Arial, Helvetica, Tahoma, sans-serif",
axisLabelPadding: 5
},
{
position: "right",
//axisLabel: "",
axisLabelUseCanvas: true,
axisLabelFontSizePixels: 12,
axisLabelFontFamily: "Verdana, Arial, Helvetica, Tahoma, sans-serif",
axisLabelPadding: 5
}
],
grid: {
hoverable: true,
borderWidth: 1
},
legend: {
labelBoxBorderColor: "none",
margin: [0,0],
position: "se"
}
};
mydata = [
{ data: [],
lines:{show:true, fill: false },
points:{show:false, fillColor: '#3366CC'},
color: "#3366CC",
yaxis: 1
},
{}, {}, {}, {}, {}, {}, {}, {}, {}
];
istep_max = mydata.length /2 ;
istep = 0;
istock_2 = 0;
istock_array = [];
date_array = [];
$('#datepicker').val('');
$.plot($("#placeholder"), mydata, options);
}
function draw_line( date, company ) {
if ( istep > istep_max ) {
alert('Too many lines attempt to draw!');
return;
}
if ( istock_array[istep-1] == istock_array[0] ) {
yaxis_local = 1;
}
else {
if ( istock_2 != 0 && istock_array[istep-1] != istock_2 ) {
alert('no of stock over 2! not draw line')
istock_array.pop();
date_array.pop();
return;
}
yaxis_local = 2;
istock_2 = istock_array[istep-1];
}
var is01 = 2*(istep-1);
var is02 = 2*(istep-1) + 1;
var color_local = color[istep-1];
var param = new Object();
param.ampm = 'AM';
param.date = date;
param.company = company;
$.get("/scripts/stock_daily/get_price.php", param, function( json ){
mydata[is01].data = eval("("+json+")");
if(mydata[is01].data.length <= 0 ) {
alert('no data stored!');
istock_array.pop();
date_array.pop();
return;
}
mydata[is01].label = param.date + ':'+label_text;
mydata[is01].dummy = mydata[is01].label;
// mydata[is01].istep = istep;
mydata[is01].color = color_local;
mydata[is01].yaxis = yaxis_local;
options.yaxes[yaxis_local-1].axisLabel=label_text;
$.plot($("#placeholder"), mydata, options);
});
param.ampm = 'PM';
$.get("/scripts/stock_daily/get_price.php", param, function( json ){
mydata[is02].data = eval("("+json+")");
var idt = mydata[is02].data.length - 1;
mydata[is02].data[idt][0] = mydata[is02].data[idt-1][0];
mydata[is02].data[idt][1] = mydata[is02].data[idt-1][1];
mydata[is02].dummy = param.date + ':'+label_text;
mydata[is02].color = color_local;
mydata[is02].yaxis = yaxis_local;
$.plot($("#placeholder"), mydata, options);
});
}
function timetohm( time ) {
var dobj = new Date( time ) ;
var utc = new Date( dobj.getTime() + dobj.getTimezoneOffset()*60000 )
var hh = utc.getHours();
var mm = utc.getMinutes();
if ( mm < 10 ) mm = '0' + mm;
var x_time = hh + ':' + mm;
return x_time;
}
function str_today() {
var now = new Date();
var yy = now.getFullYear();
var mm = now.getMonth() + 1;
if ( mm<10 ) mm='0'+mm;
var dd = now.getDate();
if ( dd<10 ) dd='0'+dd;
//
return yy+'-'+mm+'-'+dd;
}
});
データベースからの株価取得用PHP get_price.php ソース：
<?php
header("Cache-Control: no-cache, must-revalidate");
$date = $_GET["date"];
$ampm = $_GET["ampm"];
$company = $_GET["company"];
getPrice01( $date, $ampm, $company ) ;
//***************************************************************************************************
//* Function getPrice01():
//*
//***************************************************************************************************
function getPrice01($date,$ampm,$company){
require_once(dirname(__FILE__) . "/StockPrice.php");
require_once(dirname(__FILE__) . "/StockPriceDao.php");
$date_head = "new Date('2011/01/01 ";
$date_tale = "').getTime()";
$strng = "[\n";
$stockpricedao = new StockPriceDao();
$ret_array = $stockpricedao->getStockPriceByDate( $date );
$size_of_array = sizeof( $ret_array );
for ( $i=0; $i<$size_of_array; $i++ ) {
if( $ret_array[$i]->getCompany_id()==$company ) {
$time = $ret_array[$i]->getTime();
if ( date('A',strtotime($time)) === $ampm ) {
$time = $time . " +9 hours";
$price = $ret_array[$i]->getPrice();
$price = str_replace(",","",$price);
$strng = $strng."[".$date_head.date('G:i',strtotime($time)).$date_tale.",".$price."],\n";
}
}
}
if( strlen($strng) != 0 ) {
$strng = rtrim($strng);
$strng = rtrim($strng, ",");
}
$strng = $strng . "]";
echo $strng;
$stockdao = NULL;
}
?>
株価データベースSQL文ソース：
DROP TABLE IF EXISTS company; DROP TABLE IF EXISTS stock_price; CREATE TABLE company ( id INTEGER(8) PRIMARY KEY AUTO_INCREMENT, stock_id varchar(128) NOT NULL, name varchar(256) NOT NULL ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `stock_price` ( id INTEGER(8) PRIMARY KEY AUTO_INCREMENT, time TIMESTAMP, turnover varchar(128), price varchar(128), company_id INTEGER(8) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ALTER TABLE stock_price ADD FOREIGN KEY (company_id) REFERENCES company(id);
Yahoo Finance からの株価抽出PHP get_realtime_price.php ソース：
<?php
error_reporting(E_ALL & ~E_NOTICE);
require_once(dirname(__FILE__) . "/StockPriceDao.php");
require_once(dirname(__FILE__) . "/StockPrice.php");
require_once(dirname(__FILE__) . "/Company.php");
require_once(dirname(__FILE__) . "/finance.php");
if ( (time()>=mktime(9,0,0) AND time()<mktime(11,31,0)) OR
(time()>=mktime(12,30,0) ANd time()<mktime(15,1,0)) )
{
$stockpricedao = new StockPriceDao();
// Get RealTimeStockPrices from Yahoo Finance for the Company registered.
//
$ret_array = $stockpricedao->getCompany_All();
$nr = sizeof($ret_array);
for ($i=0; $i<$nr; $i++) {
$stock_codes[$i] = $ret_array[$i]->getStock_id();
}
$ret_array = getStockPrices($stock_codes);
// set stock_value on STOCKDB
$time = date("Y-m-d H:i:s");
for ($n=0; $n<$nr; $n++) {
// $stock_id = $stock_codes[$n];
$stock_id = $ret_array[0][$n];
$company_id = $stockpricedao->getCompany_id($stock_id);
$stock_price = new StockPrice();
$stock_price->setTime($time);
$stock_price->setTurnover($ret_array[2][$n]);
$stock_price->setPrice($ret_array[3][$n]);
$stock_price->setCompany_id($company_id);
$stockpricedao->setStockPrice($stock_price);
$stock_price = null;
}
$stockpricedao = null;
}
?>
株価自動取得のための crontab コマンド:
* * * * 1-5 /usr/bin/php /home/sample_dir/get_realtime_price.php > /dev/null 2>&1
その他PHPソース：
StockPrice.php:
<?php
class StockPrice {
private $id = "";
private $time = "";
private $price = "";
private $company_id = "";
function __construct() {
// echo "Initialized !\n";
}
function __destruct() {
// echo "Destructed !\n";
}
public function __toString() {
return (string) ($this->id . "\t" . $this->time . "\t" . $this->turnover . "\t" . $this->price . "\t" .
$this->company_id );
}
public function getId() {
return $this->id;
}
public function setId($id) {
$this->id = $id;
}
public function getTime() {
return $this->time;
}
public function setTime($time) {
$this->time = $time;
}
public function getTurnover() {
return $this->turnover;
}
public function setTurnover($turnover) {
$this->turnover = $turnover;
}
public function getPrice() {
return $this->price;
}
public function setPrice($price) {
$this->price = $price;
}
public function getCompany_id() {
return $this->company_id;
}
public function setCompany_id($company_id) {
$this->company_id = $company_id;
}
}
?>
StockPriceDao.php:
<?php
Class StockPriceDao {
private $mysqli = null;
//Constructor
function __construct() {
$this->connect();
}
//Destructor
function __destruct() {
$this->disconnect();
}
//Connect to MySQL server
private function connect() {
if( is_null($this->mysqli)) {
$this->mysqli = new mysqli("localhost", "user_name", "password", "DB_NAME" );
$this->mysqli->query("SET NAMES utf8");
if(mysqli_connect_errno()) {
die("Failed to connect to MySQL server<br></br> Reason:" . mysqli_connect_error());
}
}
}
//Disconnect from MySQL server
private function disconnect() {
is_null($this->mysqli) or $this->mysqli->close();
}
public function getCompany_All() {
is_null($this->mysqli) and $this-connect();
//
$sql = "SELECT * FROM company";
$result = $this->mysqli->query($sql);
$company_array = array();
while($row = $result->fetch_array(MYSQL_ASSOC)) {
$company = new Company();
$company->setId($row["id"]);
$company->setStock_id($row["stock_id"]);
$company->setName($row["name"]);
$company_array[] = $company;
}
$result->close();
return $company_array;
}
public function getCompany_id($stock_id) {
is_null($this->mysqli) and $this->connect();
//
$sql = "SELECT id FROM company WHERE stock_id = '" . $stock_id . "'";
$result = $this->mysqli->query($sql);
if(!$row=$result->fetch_array(MYSQLI_ASSOC)) break;
//DEBUG echo " stock_id company_id = " . $stock_id . " " . $row["id"] . "\n";
return $row["id"];
}
public function setStockPrice($stock_price) {
is_null($this->mysqli) and $this->connect();
//
$sql = "INSERT INTO stock_price values(''," .
"'" . $stock_price->getTime() . "'," .
"'" . $stock_price->getTurnover() . "'," .
"'" . $stock_price->getPrice() . "'," .
"'" . $stock_price->getCompany_id() . "')";
$this->mysqli->query($sql);
}
public function getStockPriceByDate( $date ) {
is_null($this->mysqli) and $this->connect();
//
$sql = "SELECT * FROM stock_price WHERE DATE(time)='" . $date . "'";
$result = $this->mysqli->query($sql);
$stockprice_array = array();
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
$stockprice = new StockPrice();
$stockprice->setId($row["id"]);
$stockprice->setTime($row["time"]);
$stockprice->setTurnover($row["turnover"]);
$stockprice->setPrice($row["price"]);
$stockprice->setCompany_id($row["company_id"]);
$stockprice_array[] = $stockprice;
}
$result->close();
return $stockprice_array;
}
}
?>
Company.php:
<?php
class Company {
private $id = "";
private $stock_id = "";
private $name = "";
function __construct() {
// echo "Initialized !\n";
}
function __destruct() {
// echo "Destructed !\n";
}
public function __toString() {
return (string) ($this->id . "\t" . $this->stock_id . "\t" . $this->name );
}
public function getId() {
return $this->id;
}
public function setId($id) {
$this->id = $id;
}
public function getStock_Id() {
return $this->stock_id;
}
public function setStock_Id($stock_id) {
$this->stock_id = $stock_id;
}
public function getName() {
return $this->name;
}
public function setName($name) {
$this->name = $name;
}
}
?>
finance.php:
<?php
function getStockPrices($stockcodes) {
//
$nr = sizeof($stockcodes);
$str_codes = "";
for ( $k=0; $k<$nr; $k++ ) {
// echo $stockcodes[$k] . "\n";
$str_codes = $str_codes . urlencode($stockcodes[$k]);
if ( $k < $nr-1 ) $str_codes = $str_codes . "+";
}
$url ="http://info.finance.yahoo.co.jp/search/?query=" . $str_codes . "&ei=UTF-8&view=l1";
//
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$feed = curl_exec($ch);
curl_close($ch);
//
$stock_value = array();
// 株価コード
//以下に修正 $n = preg_match_all("/code=([^<]+)\"><strong>/",$feed, $cell, PREG_PATTERN_ORDER);
$n = preg_match_all("/code=([^<]+)\" data-/",$feed, $cell, PREG_PATTERN_ORDER);
if ( $n == $nr ) for( $i= 0; $i<$n; $i++ ) $stock_value[0][] = $cell[1][$i];
else return -1;
// 日付/時間 出来高
$n = preg_match_all("/<td nowrap>\n([^<]+)<\/td>/s", $feed, $cell, PREG_PATTERN_ORDER);
if ( $n == $nr*2 ) {
for($i= 0;$i<$n;$i=$i+2) {
$stock_value[1][] = $cell[1][$i];
$stock_value[2][] = $cell[1][$i+1];
}
}
else return -1;
// 株価
$n = preg_match_all("/<td><strong>([^<]+)<\/strong><\/td>/s", $feed, $cell, PREG_PATTERN_ORDER);
if ( $n == $nr ) for( $i= 0; $i<$n; $i++ ) $stock_value[3][] = $cell[1][$i];
else return -1; // 銘柄
$n = preg_match_all("/<strong class=\"ico.*\">([^<]+)<\/strong>/",$feed, $cell, PREG_PATTERN_ORDER);
if ( $n == $nr ) for( $i= 0; $i<$n; $i++ ) $stock_value[4][] = $cell[1][$i];
else return -1;
//
return $stock_value;
}
?>
3 Responses to “Web公開情報から株価を自動取得しFlotでチャート化”
2014年11月4日に、Yahoo Finance の仕様が変更された。
これに伴い、finance.php のなかの関数 getStockPrices()の一行を以下のように変更した：
変更前：
$n = preg_match_all("/code=([^<]+)\"><strong>/",$feed, $cell, PREG_PATTERN_ORDER);変更後：
$n = preg_match_all("/code=([^<]+)\" data-/",$feed, $cell, PREG_PATTERN_ORDER);By yama on Nov 5, 2014