2016年9月27日 星期二

股票經理:導入數據


有了數據,第二步便是把數據導入到自己的數據庫,方便進行分析及運算,甚至是供給 Machine Learning 系統使用。於是編寫了以下程序:
<?php
//----------------------------------------------------------------------------------------
//  Stock Manager: Historical Price Parser
//----------------------------------------------------------------------------------------
//  Platform: CentOS7 + PHP + Apache
//  Written by Pacess HO
//  Copyright 2016 Pacess Studio.  All rights reserved.
//----------------------------------------------------------------------------------------

//  Reference:
//  https://greenido.wordpress.com/2009/12/22/work-like-a-pro-with-yahoo-finance-hidden-api/
//  http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol=%222313.HK%22%20and%20startDate%20=%20%222012-09-11%22%20and%20endDate%20=%20%222014-02-11%22&format=json&diagnostics=true&env=store://datatables.org/alltableswithkeys&callback=
//  http://finance.yahoo.com/d/quotes.csv?s=2313.HK&f=snd1l1m3m4l1yr
//  http://finance.yahoo.com/quote/0005.HK/history?period1=1388505600&period2=1474819200&interval=1d&filter=history&frequency=1d

header("Content-type: application/json");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Tue, 10 Mar 1987 00:00:00 GMT");

date_default_timezone_set("Asia/Hong_Kong");
mb_internal_encoding("UTF-8");
ini_set("memory_limit", "-1");
set_time_limit(0);

//----------------------------------------------------------------------------------------
//  * Order is important
require("libraries/constants.php");
require("libraries/databaseConnection.php");
require("libraries/stockData.php");

//----------------------------------------------------------------------------------------
//  Reading parameter
if (!isset($_REQUEST["data"]))  {echo(json_encode(array("status"=>-1, "message"=>"Parameter not found...")));  exit(-1);}
if (!isset($_REQUEST["symbol"]))  {echo(json_encode(array("status"=>-2, "message"=>"Parameter not found...")));  exit(-2);}

$data = $_REQUEST["data"];
$dataArray = explode("\n", $data);

$symbol = $_REQUEST["symbol"];

//----------------------------------------------------------------------------------------
//  Main parse routine
$skipCount = 0;
$errorCount = 0;
$parseCount = 0;
$stockData = new stockData();
foreach ($dataArray as $row)  {

   $rowArray = explode("\t", $row);
   $rowCount = count($rowArray);

   //  Skip it
   if ($rowCount < 7)  {$skipCount++;  continue;}

   //  Parse data
   //  0            1      2      3      4      5         6
   //    Date         Open   High   Low      Close   Adj Close*   Volume
   //    Sep 23, 2016   58.75   59.00   57.70   58.05   58.05      44,806,106
   $date = $rowArray[0];
   $open = $rowArray[1];
   $high = $rowArray[2];
   $low = $rowArray[3];
   $close = $rowArray[4];
   $adjustedClose = $rowArray[5];
   $volume = $rowArray[6];

   //  Convert 'Sep-26, 2016' to '2016-09-26'
   $dateObject = strtotime($date);
   $date = date("Y-m-d", $dateObject);

   //  Remove comma
   $volume = str_replace(",", "", $volume);

   $dictionary = array("date"=>$date, "status"=>1, "symbol"=>$symbol, "open"=>$open,
      "high"=>$high, "low"=>$low, "close"=>$close, "adjustedClose"=>$adjustedClose,
      "volume"=>$volume, "sma50"=>null, "sma200"=>null, "ema9"=>null, "ema12"=>null,
      "ema26"=>null);
   $dictionary = $stockData->addOrUpdateRecord($dictionary);
   if ($dictionary == null)  {
      $errorCount++;
   }  else  {
      $parseCount++;
   }
}
unset($stockData);  $stockData = null;
echo(json_encode(array(
   "status"=>1, "message"=>"Parse completed.", "errorCount"=>$errorCount,
   "parseCount"=>$parseCount, "skipCount"=>$skipCount, "symbol"=>$symbol)));

?>

使用上方的程式時,需要不停把網頁拖拉到底部,直至所有數據載入完成,才好拷貝並貼上程式。使用了一會兒之後,才發現頁面右上角有一個下載數據按鈕,可以簡單地下載 CSV 格式,不用慢慢拖拉。>_<

沒有留言: