CSE EOD Data !

CubedSol wrote:Updated files containing data until 03/08/2011 are located below .

Database Backup :

RAR : 2.8MB
http://www.4shared.com/file/wlNnyPsl/MyNewData__Updated_03_08_2011_.html

ZIP : 3.2MB
http://www.4shared.com/file/tEh3Eb9m/MyNewData__Updated_03_08_2011_.html

PS - ASPI & MPI updated until 02/08/2011

CubedSol wrote: thanks for the -REPS given to me....

SanjivFund wrote:Guys i managed to sought the problem out..the errors we due to incorrect date format.
This is 4th August 2011,should be inserted as 20110804 (yyyymmdd)

CubedSol wrote:

CSE DL.xlsm
http://www.4shared.com/file/n_xtQ_DW/CSE_DL.html

PS - ASPI & MPI updated until 05/08/2011

smallville wrote:
Its a great pleasure if someone with programming knowledge with PHP/JS/etc.. can write a script (VB or whatever) to grab the csv file from CSE site and mail it to our mailboxes..

webhoundx wrote:Thanks for the upload CubedSol

smallville wrote:
Its a great pleasure if someone with programming knowledge with PHP/JS/etc.. can write a script (VB or whatever) to grab the csv file from CSE site and mail it to our mailboxes..

Currently working on this. I have a made a google spreadsheet that will auto update between 3pm-4pm every weekday with CSE site's CSV data. I was planning to test the process this week and let you guys know... Emailing part I'm currently working on. (A bit hard to find time with work and all )

SanjivFund wrote:
Its a great pleasure if someone with programming knowledge with PHP/JS/etc.. can write a script (VB or whatever) to grab the csv file from CSE site and mail it to our mailboxes..

twimalasuriya wrote:
Guys.

I have already developed a similar script that run as a cron job ( a scheduler running on linux ) each day (excluding holidays) , download the EOD, data, convert to excel , and then mail it to csereports@googlegroups.com, so the members of the google group get a copy.

I have another script that hourly runs at 10am till 3pm that gives same data, for the owner of http://samueldaniel7.blogspot.com/

If anyone wants I can easily develop a script to send EOD data in metastock format itself.

If anyone is interested I can share my scripts too. Its written in php.

webhoundx wrote:
I was searching for a script but couldn't find anything developed for CSE. That's why I came up with a system of my own.
Anyways great news. Really appreciate you willing to share the sources as well
Now if only we can come up with a system to auto import the data to Ami as well...

Code:



<?php

require_once ("support_functions.php");

$url = 'http://www.cse.lk/listedcompanies/overview.htm?d-16544-e=2&6578706f7274=1';
$dir = '/home/wimalasu/';
$file_name = 'summary_'.date("Y-m-d").'.tsv' ;
$excel_file_name = 'summary_'.date("Y-m-d").'.xls' ;

if (isholiday() == 0) {

// download tsv
downloadFile($url , $dir , $file_name);


// convert to excel

convertToExcel($dir.$file_name ,$dir.$excel_file_name );

// send it by email.

sendemail($file = $dir.$excel_file_name , 'thiwankaw@gmail.com' , 'cse_summary' , 'application/excel');


}else{

echo 'today is a cse holiday';

}



?>



Code:



<?php


function sendemail($file,$to,$subject,$contenttype){

// Require Pear Mail Packages
require_once ("/home/wimalasu/php/Mail.php");
require_once ("/home/wimalasu/php/Mail/mime.php");
$recipients  = $to;
// Additional headers
$headers["From"] = 'thiwankaw@gmail.com';
$headers["To"]    = $to;
$headers["Subject"] = $subject;
$crlf = "\n";

$mime = new Mail_mime($crlf);
$mime->addAttachment($file, $contenttype);

$message = $mime->get();
$headers = $mime->headers($headers);
$params["host"]    = 'ssl://smtp.gmail.com';
$params["auth"]    = TRUE; // note: there are *no delimiters*
$params["port"] = 465 ;

// note: there are *no delimiters* for DIGEST-MD5 either.
// If you want to use PLAIN,
// you have to use delimiters like this: 'PLAIN'
// $params["auth"]    = DIGEST-MD5;
$params["username"]    = 'thiwankaw@gmail.com';
$params["password"]    = 'mypassword here because i have to send authenticated email';
//$params["localhost"]= '127.0.0.1';
// Debug so that we see what's happenning for the moment.
$params["debug"]    = "True";
// create the mail object using the Mail::factory method
$mail_message =& Mail::factory('smtp', $params);
$mail_message->send ($recipients, $headers, $message);
}


// this downloads a file to local folder
function downloadFile($url , $dir , $file_name){

    if($file_name == NULL){ $file_name = basename($url);}
    $url_stuff = parse_url($url);
    $port = isset($url_stuff['port']) ? $url_stuff['port'] : 80;

    $fp = fsockopen($url_stuff['host'], $port);
    if(!$fp){ return false;}

    $query  = 'GET ' . $url_stuff['path'] .'?' . $url_stuff['query'] . " HTTP/1.0\n";

    $query .= 'Host: ' . $url_stuff['host'];
    $query .= "\n\n";

    fwrite($fp, $query);

    while ($tmp = fread($fp, 8192))  {
        $buffer .= $tmp;
    }

    preg_match('/Content-Length: ([0-9]+)/', $buffer, $parts);
    $file_binary = substr($buffer, - $parts[1]);
    if($file_name == NULL){
        $temp = explode(".",$url);
        $file_name = $temp[count($temp)-1];
    }
    $file_open = fopen($dir . "/" . $file_name,'w');
    if(!$file_open){ return false;}
    fwrite($file_open,$file_binary);
    fclose($file_open);

}

// check if a given day is a holiday for cse
function isholiday(){

$day_index = date("w");
// it's the weekend
if($day_index == 0 || $day_index == 6) {
  return 1;
}


$handle = @fopen("/home/wimalasu/holidays", "r");
$isholiday = 0;
$today = date("Y-m-d");
if ($handle) {
    while (($buffer = fgets($handle, 4096)) !== false) {

      if ( strcmp($today,trim($buffer)) == 0 ){
          $isholiday = 1;
      }
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}
  return $isholiday;
}


function convertToExcel($inputFileName,$outputFileName) {

require_once 'phpexcel/Classes/PHPExcel.php';


$inputFileType = 'CSV';

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Set the delimiter to a TAB character  **/
$objReader->setDelimiter("\t");
/**  Load the file to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

/** Freeze pane so that the heading line won't scroll **/
$objPHPExcel->getActiveSheet()->freezePane('A2');



$writer = new PHPExcel_Writer_Excel5($objPHPExcel);
$writer->save($outputFileName);


}




?>




Code:



2011-01-19
2011-02-04
2011-02-16
2011-02-17
2011-03-02
2011-04-13
2011-04-14
2011-04-15
2011-05-02
2011-05-17
2011-05-18
2011-06-15
2011-07-14
2011-08-31
2011-10-11
2011-10-26
2011-11-10
2011-12-10
2011-12-26




twimalasuriya wrote:

webhoundx wrote:
I was searching for a script but couldn't find anything developed for CSE. That's why I came up with a system of my own.
Anyways great news. Really appreciate you willing to share the sources as well
Now if only we can come up with a system to auto import the data to Ami as well...

I have below files on my host server (3ix), and I run downloadSummary.php as a cron job at 3pm (SL time) to mail this file to me.

By the way, I'm not a php developer but used php for this because its the best suited for the job.

downloadSummary.php

krishaja wrote:@twi malasooriy
pl.. tell me where to add these codes?

ha_na wrote:I need 9 February 2011 and 9 may 2011 EOD data for all companies. I have error on those two days. Can someone help me?
Thanks

Code:



SELECT  share,DATE_FORMAT(date,'%Y%m%d') , open,high,low,close,sharevolume
INTO OUTFILE '/tmp/20110509.txt'
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  from ohlc  where date = '2011-5-9' ;



twimalasuriya wrote:

ha_na wrote:I need 9 February 2011 and 9 may 2011 EOD data for all companies. I have error on those two days. Can someone help me?
Thanks

I just copied two files you wanted to the location below

(Symbol Date (YYMMDD) Open High Low Close Vol )

http://wimalasuriya.com/cse/eod/20110209.txt
http://wimalasuriya.com/cse/eod/20110509.txt

These are from a local database I update each day from data taken from cse.lk

I run a sql like below to get the file

Code:



SELECT  share,DATE_FORMAT(date,'%Y%m%d') , open,high,low,close,sharevolume
INTO OUTFILE '/tmp/20110509.txt'
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  from ohlc  where date = '2011-5-9' ;



ha_na wrote:thanks my dear.
I want excel format as I want to convert it to Metastock. Is it possible?
Thanks.