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

In this post will be shown how to download stock market with perl script into Google spreadsheet. Once the data is loaded in Google spreadsheet you can do trend or forecasting analysis and build charts using Google Docs functionality.

Perl script helps to automate downloading process, to replace manual downloading process with just one command to run script. The proposed perl script is using perl module Finance::QuoteHist.

Here is the example how you can get stock data using this module: use Finance::QuoteHist;
$q = Finance::QuoteHist->new ( symbols => [qw(IBM UPS AMZN)], start_date => ‘8/18/2013’, end_date => ‘today’, );

Now you just iterate through each row and do what you need to do with the data:

foreach $row ($q->quotes()) { ($symbol, $date, $open, $high, $low, $close, $volume) = @$row; }

In this example you will get data for 3 companies with the stock symbols IBM, UPS, AMZN. Below is the full code example showing how to download and save data to Google spreadsheets. To use it you need replace stock symbols and Google spreadsheet names with the actual names.
Also you need to put actual Google account username and password. The script is assuming that spreadsheet document is already created on Google Drive and has enough columns and rows to put data. In other words before running perl script add enough rows and columns to spreadsheet where the data will be saved.

Here is the link for free financial data from Chart Oasis in case you need more data Download free market data of stocks, indices, commodities and forex

use Finance::QuoteHist;
$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; 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 => ‘xxxxxxx’, password => ‘xxxxxxx’ );
my @spreadsheets = $service->spreadsheets();
my $spreadsheet = $service->spreadsheet( { title => ‘put_actual_title_here’ } );
my $worksheet1 = $spreadsheet->worksheet( { title => ‘put_actual_title_here’ } );
$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<$count+2; $i++) { for($j=1; $j<8; $j++) { $cell = $worksheet1->cell({col => $j, row => $i}); $cell->input_value($data[$i-2][$j-1]); } }