Adding Stock Market Data to a Google Spreadsheet with a Perl Script

In the previous post it was shown how to download stock data data prices into Google spreadsheet using perl script.
This post will extend this script by considering situation when we just want to add the latest data to the existing spreadsheet with the data downloaded previously. For example first time we can download data for the last 3 month and then going forward in the future each day we will need only to add data for the previous day or days that are not loaded yet. This will significantly save time on upgrading process.

Below is segment of code that is used for adding new data to the existing spreadsheet with previously downloaded data. We call this mode = “ad”. In this mode we find blank row and then put values for the record that is matching to blank row number.

For this to work we need always have the same start date for our downloading data set.
if ($mode eq “ad”)
{ for($i=2; $icell({col => 1, row > $i});
$v=$cell->input_value;
if ($v eq “”)
{ for($j=1; $jcell({col => $j, row => $i});
$cell->input_value($data[$i-2][$j-1]);
} } }
exit; }
# end of if $mode = “ad”

Here is the whole script. To run it first time you need disable the line $mode=”ad”; by putting # in front of $ sign.
Then remove # so it will run in the addition of data mode.
Don’t forget also put your email, password and document / spreadsheet title names.
use Finance::QuoteHist;
#if first time download data into empty sheet $mode =””;
#if adding data to the sheet with previously downloaded data $mode=”ad”;
#disable this line if first time download data
$q = Finance::QuoteHist->
new ( symbols => [qw(IBM UPS AMZN)], start_date => ‘8/18/2013’, end_date => ‘today’, );
$count=0;
foreach $row ($q->quotes()) { ($symbol, $date, $open, $high, $low, $close, $volume) = @$row;
$date =~ s/\//-/g;
print “$symbol, $date, $open, $high, $low, $close, $volume \n”; $data[$count][0]=$symbol;
$data[$count][1]=$date;
$data[$count][2]=$open;
$data[$count][3]=$high;
$data[$count][4]=$low;
$data[$count][5]=$close;
$data[$count][6]=$volume;
$count=$count+1; }
use Net::Google::Spreadsheets;
my $service = Net::Google::Spreadsheets->new( username => ‘youremail@company.com’, password => ‘xxxxxxx’ );
my @spreadsheets = $service->spreadsheets();
my $spreadsheet = $service->spreadsheet( { title => ‘titlename’ } );
my $worksheet1 = $spreadsheet->worksheet( { title => ‘Sheet_name’ } );
if ($mode eq “ad”) { for($i=2; $icell({col => 1, row => $i}); $v=$cell->input_value;
if ($v eq “”) { for($j=1; $jcell({col => $j, row => $i}); $cell->
input_value($data[$i-2][$j-1]); } } }
exit; }
# if $mode = “ad”
$cell = $worksheet1->
cell({col => 1, row => 1});
$cell->input_value(“symbol”);
$cell = $worksheet1->cell({col => 2, row => 1});
$cell->input_value(“date”);
$cell = $worksheet1->cell({col => 3, row => 1});
$cell->input_value(“open”);
$cell = $worksheet1->
cell({col =>4, row => 1});
$cell->input_value(“high”);
$cell = $worksheet1->cell({col => 5, row => 1});
$cell->input_value(“low”);
$cell = $worksheet1->
cell({col => 6, row => 1});
$cell->input_value(“close”);
$cell = $worksheet1->cell({col =>7, row =>; 1});
$cell->input_value(“volume”);
for($i=2; $i { for($j=1; $jcell({col => $j, row => $i});
$cell->
input_value($data[$i-2][$j-1]);
} }
Now we can use this script for downloading and updating data on regular basis.



Leave a Comment