I have recently started refreshing my knowledge in Perl and have, in the process, re-realized the amazing scripting power of linux. There's a whole lot that can be done with file structure and file contents than what is remotely possible in Windows. Keeping that as a separate topic I'll first dive into using Perl to connect with data sources to download stock quotes and store them in a mysql database that can be used for further analysis.
Creating a MySql Database:
I found a very useful resource on the web to help understand MySql especially for those who have a faint memory from using it in the past: http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html
This tutorial gives a very elaborate overview on how to obtain MySQL and then how to create and modify user accounts and databases.
After getting acquainted with the basics this tutorial will help to create the actual database and provides the syntax on how to insert data using Perl into the just created database: http://scriptingmysql.wordpress.com/2011/08/05/inserting-data-into-mysql-with-perl/
Obtaining Stock Quotes:
I looked for many different alternatives to obtain stock quotes using Perl and I found the Finance::QuoteHist package to be quite relevant to this purpose - http://search.cpan.org/~msisk/Finance-QuoteHist-1.19/lib/Finance/QuoteHist.pm
Final Code snippet:
With all the above information I wrote the following code to obtain the ticker symbols from a local file and then fetch the stock quotes and store them into a local mysql database:
#!/usr/bin/perl
use Finance::QuoteHist;
use DBI;
# Obtain the ticker symbols from file
my $tic_file = '<path of file>/tics2.txt';
open (fh, "< $tic_file"); #Note the syntax
my @lines = <fh>;
close(fh);
#my @tics = @lines[0..99];
#print $#lines, $#tics;
#print "@lines[1..100]";
$dbh = DBI->connect('dbi:mysql:PerlTest','root','<my password>') or die "Connection Error: $DBI::errstr\n";
$q = Finance::QuoteHist->new
(
symbols => \@lines,
start_date => '03/14/2013', # or '1 year ago', see Date::Manip
end_date => 'today',
);
# Quotes
foreach $row ($q->quotes())
{
print "@$row \n"; #double quotes displays properly
($symbol, $date, $open, $high, $low, $close, $volume) = @$row;
$sql = "insert into SampleQuotes (ticker, date, closing) values ('$symbol','$date','$open') ";
$sth = $dbh->prepare($sql);
$sth->execute() or die "SQL Error: $DBI::errstr\n";
}
Creating a MySql Database:
I found a very useful resource on the web to help understand MySql especially for those who have a faint memory from using it in the past: http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html
This tutorial gives a very elaborate overview on how to obtain MySQL and then how to create and modify user accounts and databases.
After getting acquainted with the basics this tutorial will help to create the actual database and provides the syntax on how to insert data using Perl into the just created database: http://scriptingmysql.wordpress.com/2011/08/05/inserting-data-into-mysql-with-perl/
Obtaining Stock Quotes:
I looked for many different alternatives to obtain stock quotes using Perl and I found the Finance::QuoteHist package to be quite relevant to this purpose - http://search.cpan.org/~msisk/Finance-QuoteHist-1.19/lib/Finance/QuoteHist.pm
Final Code snippet:
With all the above information I wrote the following code to obtain the ticker symbols from a local file and then fetch the stock quotes and store them into a local mysql database:
#!/usr/bin/perl
use Finance::QuoteHist;
use DBI;
# Obtain the ticker symbols from file
my $tic_file = '<path of file>/tics2.txt';
open (fh, "< $tic_file"); #Note the syntax
my @lines = <fh>;
close(fh);
#my @tics = @lines[0..99];
#print $#lines, $#tics;
#print "@lines[1..100]";
$dbh = DBI->connect('dbi:mysql:PerlTest','root','<my password>') or die "Connection Error: $DBI::errstr\n";
$q = Finance::QuoteHist->new
(
symbols => \@lines,
start_date => '03/14/2013', # or '1 year ago', see Date::Manip
end_date => 'today',
);
# Quotes
foreach $row ($q->quotes())
{
print "@$row \n"; #double quotes displays properly
($symbol, $date, $open, $high, $low, $close, $volume) = @$row;
$sql = "insert into SampleQuotes (ticker, date, closing) values ('$symbol','$date','$open') ";
$sth = $dbh->prepare($sql);
$sth->execute() or die "SQL Error: $DBI::errstr\n";
}
There is a whole lot that needs to be done in this script around Error Handling, vis-a-vis connection issues, duplication issues, missing data, etc and that is going to be the next topic.
