2016年9月28日 星期三

股票經理:導入數據・二

經過昨天的烏龍後,今天編寫了新的導入數據程式。我的目標是觀察 50 隻恒生指數成份股,如果利用昨天找到的 CSV 逐個導入,也很浪費時間。所以程式是直接從 Yahoo 下載 CSV 並進行導入工作。我只需要執行一句「php updateHistorical.php」指令就行。
<?php
//----------------------------------------------------------------------------------------
//  Stock Manager: Update Historical Data with Yahoo Finance API
//----------------------------------------------------------------------------------------
//  Platform: CentOS7 + PHP + Apache
//  Written by Pacess HO
//  Copyright 2016 Pacess Studio.  All rights reserved.
//----------------------------------------------------------------------------------------

header("Content-type: text/plain");
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");

//----------------------------------------------------------------------------------------
//  Yahoo Finance API
//   http://chart.finance.yahoo.com/table.csv?s=0005.HK&a=0&b=1&c=2014&d=8&e=26&f=2016&g=d&ignore=.csv
//
//  Data Sample:
//    Date,Open,High,Low,Close,Volume,Adj Close
//    2016-08-05,53.30,53.85,53.25,53.50,34276500,52.742
//    2016-08-04,52.35,53.25,52.20,53.00,47823500,52.249
//    2016-08-03,50.00,51.80,49.75,51.60,56329500,50.869
//    2016-08-02,50.80,50.80,50.80,50.80,000,50.08
//    2016-08-01,50.80,51.10,50.75,50.80,20010100,50.08

$yahooHistoricalAPI = "http://chart.finance.yahoo.com/table.csv?s=0000.HK&a=mmmm&b=dddd&c=yyyy&d=MMMM&e=DDDD&f=YYYY&g=d&ignore=.csv";

$date = date("Ymd");
$year = intval(substr($date, 0, 4));
$month = intval(substr($date, 4, 2));
$day = intval(substr($date, 6, 2));

//  Prepare historical data URL up to today
$yahooHistoricalAPI = str_replace("YYYY", $year, $yahooHistoricalAPI);
$yahooHistoricalAPI = str_replace("MMMM", $month-1, $yahooHistoricalAPI);
$yahooHistoricalAPI = str_replace("DDDD", $day, $yahooHistoricalAPI);

//----------------------------------------------------------------------------------------
//  Get parameters
$year = 2010;
$month = 1;
$day = 1;

if (isset($_REQUEST["yyyy"]))  {$year = intval($_REQUEST["yyyy"]);}
if (isset($_REQUEST["mm"]))  {$month = intval($_REQUEST["mm"])-1;}
if (isset($_REQUEST["dd"]))  {$day = intval($_REQUEST["dd"]);}

$yahooHistoricalAPI = str_replace("yyyy", $year, $yahooHistoricalAPI);
$yahooHistoricalAPI = str_replace("mmmm", $month-1, $yahooHistoricalAPI);
$yahooHistoricalAPI = str_replace("dddd", $day, $yahooHistoricalAPI);

//----------------------------------------------------------------------------------------
//  Connect Yahoo Finance API and get current day value
$stockData = new stockData();
foreach ($_hengSengIndexesArray as $index)  {

   echo("Retrieving $index data from $url...");
   $url = str_replace("0000.HK", $index, $yahooHistoricalAPI);

   //  Download historical .csv
   //  * Not use fopen here because somehow it cannot read complete content
   $csvContent = file_get_contents($url);
   if ($csvContent)  {

      //  Extract CSV content
      $rowID = 0;
      $dataArray = explode("\n", $csvContent);
      $rowCount = count($dataArray);
      echo("$rowCount rows.\n");
      foreach ($dataArray as $row)  {
         $rowID++;

         $rowArray = str_getcsv($row);
         $count = count($rowArray);
         if ($count < 7)  {

            //  Not enough data, skip it
            echo("Skip #$rowID ($count)...\n");
            continue;
         }

         //  0 = Trade Date
         //  1 = Open Price
         //  2 = High Price
         //  3 = Low Price
         //  4 = Close Price
         //  5 = Volume
         //  6 = Adjusted Close Price
         $date = $rowArray[0];
         $dateSize = strlen($date);
         if ($dateSize < 10)  {

            //  First row is header, skip if row is now date
            continue;
         }

         //  It is data row, continue process
         $open = $rowArray[1];
         $high = $rowArray[2];
         $low = $rowArray[3];
         $close = $rowArray[4];
         $volume = $rowArray[5];
         $adjustedClose = $rowArray[6];

         $sma50 = 0;
         $sma200 = 0;
         $ema9 = 0;
         $ema12 = 0;
         $ema26 = 0;

         //  Save to database         
         $dictionary = array("date"=>$date, "status"=>1, "symbol"=>$index, "open"=>$open,
            "high"=>$high, "low"=>$low, "close"=>$close, "adjustedClose"=>$adjustedClose,
            "volume"=>$volume, "sma50"=>$sma50, "sma200"=>$sma200, "ema9"=>0, "ema12"=>0,
            "ema26"=>0);
         $dictionary = $stockData->addOrUpdateRecord($dictionary);
         if ($dictionary == null)  {
            echo("Parameter not found\n");
         }  else  {

            $affectedRow = $dictionary["affectedRows"];
            switch ($affectedRow)  {
               case 0:  echo("No change ($row)\n");  break;
               case 1:  echo("OK ($row)\n");  break;
               case 2:  echo("Updated ($row)\n");  break;
               default:  echo("Unable to save to database #$affectedRow\n");  break;
            }
         }
      }

   }  else  {

      //  Error
      echo("Unable to download historical data\n");
   }
}
unset($stockData);  $stockData = null;

?>

沒有留言: