Skip to content

Instantly share code, notes, and snippets.

@soardex
Created May 1, 2014 10:53
Show Gist options
  • Save soardex/d11cb30c6c85f519e547 to your computer and use it in GitHub Desktop.
Save soardex/d11cb30c6c85f519e547 to your computer and use it in GitHub Desktop.
Get CVS from remote host then save to a MySQL database.
use strict;
use warnings;
use DBI;
use Time::Local;
use LWP::Simple;
sub trim {
my $s = shift;
$s =~ s/^"|"$//g;
$s =~ s/^\s+|\s+$//g;
return $s;
}
my $dsn = 'dbi:mysql:dbname=khfinance;host=localhost;port=3306';
my $dbh = DBI->connect($dsn, 'root', 'toor', {AutoCommit=>1, RaiseError=>1, PrintError=>0});
my $sth = $dbh->prepare('SELECT * FROM khf_symbols');
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
my $symbol = $ref->{'c_symbol'};
my $file = "http://download.finance.yahoo.com/d/quotes.csv?s=$symbol&f=sl1d1t1c1ohgv&e=.csv";
print "Found SS: $file \n";
my $content = get($file);
my @fields = split(/,/, $content);
my ($tm, $td, $ty) = split(/[\/]+/, $fields[2]);
my $timestamp = "$ty-$tm-$td";
$dbh->do('INSERT INTO khf_quotes (s_id, c_id, s_opening_price, s_closing_price, s_highest_price, s_lowest_price, s_volume_share, s_transact_date) VALUES (0, ?, ?, ?, ?, ?, ?, ?)',
undef,
trim($ref->{'c_id'}),
trim($fields[5]),
trim($fields[1]),
trim($fields[6]),
trim($fields[7]),
trim($fields[8]),
trim($timestamp)
);
}
$sth->finish();
$dbh->disconnect();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment