{"id":76,"date":"2016-02-13T12:45:09","date_gmt":"2016-02-13T12:45:09","guid":{"rendered":"http:\/\/intelligentonlinetools.com\/blog\/?p=76"},"modified":"2016-03-11T02:23:38","modified_gmt":"2016-03-11T02:23:38","slug":"downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script","status":"publish","type":"post","link":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","title":{"rendered":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

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

Now you just iterate through each row and do what you need to do with the data: <\/p>\n

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

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.
\nAlso 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.<\/p>\n

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<\/a><\/p>\n

use Finance::QuoteHist;
\n$q = Finance::QuoteHist->new ( symbols => [qw(IBM UPS AMZN)], start_date => ‘8\/18\/2013’, end_date => ‘today’, );
\n $count=0;
\n foreach $row ($q->quotes()) { ($symbol, $date, $open, $high, $low, $close, $volume) = @$row; print “$symbol, $date, $open, $high, $low, $close, $volume \\n”;
\n $data[$count][0]=$symbol;
\n $data[$count][1]=$date;
\n $data[$count][2]=$open;
\n $data[$count][3]=$high;
\n $data[$count][4]=$low;
\n $data[$count][5]=$close;
\n $data[$count][6]=$volume;
\n $count=$count+1; }<\/p>\n

use Net::Google::Spreadsheets;
\n my $service = Net::Google::Spreadsheets->new( username => ‘xxxxxxx’, password => ‘xxxxxxx’ );
\n my @spreadsheets = $service->spreadsheets();
\n my $spreadsheet = $service->spreadsheet( { title => ‘put_actual_title_here’ } );
\n my $worksheet1 = $spreadsheet->worksheet( { title => ‘put_actual_title_here’ } );
\n $cell = $worksheet1->cell({col => 1, row => 1}); $cell->input_value(“symbol”);
\n $cell = $worksheet1->cell({col => 2, row => 1}); $cell->input_value(“date”);
\n $cell = $worksheet1->cell({col => 3, row => 1}); $cell->input_value(“open”);
\n $cell = $worksheet1->cell({col => 4, row => 1}); $cell->input_value(“high”);
\n $cell = $worksheet1->cell({col => 5, row => 1}); $cell->input_value(“low”);
\n $cell = $worksheet1->cell({col => 6, row => 1}); $cell->input_value(“close”);
\n $cell = $worksheet1->cell({col => 7, row => 1}); $cell->input_value(“volume”);
\nfor($i=2; $i<$count+2; $i++) \n{ for($j=1; $j<8; $j++) \n{ $cell = $worksheet1->cell({col => $j, row => $i}); $cell->input_value($data[$i-2][$j-1]); } }<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[3],"tags":[],"jetpack_publicize_connections":[],"yoast_head":"\nDownloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications\" \/>\n<meta property=\"og:description\" content=\"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 ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Machine Learning Applications\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-13T12:45:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-03-11T02:23:38+00:00\" \/>\n<meta name=\"author\" content=\"owygs156\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"owygs156\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\",\"url\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\",\"name\":\"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications\",\"isPartOf\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\"},\"datePublished\":\"2016-02-13T12:45:09+00:00\",\"dateModified\":\"2016-03-11T02:23:38+00:00\",\"author\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\"},\"breadcrumb\":{\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/intelligentonlinetools.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\",\"url\":\"http:\/\/intelligentonlinetools.com\/blog\/\",\"name\":\"Machine Learning Applications\",\"description\":\"Artificial intelligence, data mining and machine learning for building web based tools and services.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\",\"name\":\"owygs156\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"caption\":\"owygs156\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","og_locale":"en_US","og_type":"article","og_title":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications","og_description":"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 ... Read more","og_url":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","og_site_name":"Machine Learning Applications","article_published_time":"2016-02-13T12:45:09+00:00","article_modified_time":"2016-03-11T02:23:38+00:00","author":"owygs156","twitter_card":"summary_large_image","twitter_misc":{"Written by":"owygs156","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","url":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","name":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications","isPartOf":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#website"},"datePublished":"2016-02-13T12:45:09+00:00","dateModified":"2016-03-11T02:23:38+00:00","author":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478"},"breadcrumb":{"@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/intelligentonlinetools.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script"}]},{"@type":"WebSite","@id":"http:\/\/intelligentonlinetools.com\/blog\/#website","url":"http:\/\/intelligentonlinetools.com\/blog\/","name":"Machine Learning Applications","description":"Artificial intelligence, data mining and machine learning for building web based tools and services.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478","name":"owygs156","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","caption":"owygs156"}}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7h1IJ-1e","jetpack-related-posts":[{"id":79,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","url_meta":{"origin":76,"position":0},"title":"Adding Stock Market Data to a Google Spreadsheet with a Perl Script","date":"February 13, 2016","format":false,"excerpt":"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\u2026","rel":"","context":"In "Stock data analysis"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":123,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/20\/calculating-indicators-for-stock-data-forecasting\/","url_meta":{"origin":76,"position":1},"title":"Calculating Indicators for Stock Data Forecasting","date":"February 20, 2016","format":false,"excerpt":"In the previous posts was shown how to download stock data into Google spreadsheet using perl script. Here we will look how to add other data indicators based on downloaded stock data prices and volume. The use for stock data prediction of such indicators based on stock data that is\u2026","rel":"","context":"In "Stock data analysis"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":125,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/20\/calculating-indicators-for-stock-data-forecasting-2\/","url_meta":{"origin":76,"position":2},"title":"Calculating Indicators for Stock Data Forecasting","date":"February 20, 2016","format":false,"excerpt":"In the previous posts was shown how to download stock data into Google spreadsheet using perl script. Here we will look how to add other data indicators based on downloaded stock data prices and volume. The use for stock data prediction of such indicators based on stock data that is\u2026","rel":"","context":"In "Stock data analysis"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":90,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/how-to-write-to-a-google-spreadsheet-with-a-perl-script\/","url_meta":{"origin":76,"position":3},"title":"How to Write to a Google Spreadsheet with a Perl Script","date":"February 13, 2016","format":false,"excerpt":"Google Docs is one of many cloud computing document-sharing services and provides an alternative to MS Office applications such as MS Excel. This post will show how to write data to Google Docs Spreadsheet and read data back from the same spreadsheet using perl programming language. Google Sheets is an\u2026","rel":"","context":"In "Perl Scripts"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":383,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/07\/03\/getting-the-data-from-the-web-using-php-for-api-using-the-api-with-php\/","url_meta":{"origin":76,"position":4},"title":"Getting the Data from the Web using PHP or Python for API","date":"July 3, 2016","format":false,"excerpt":"In the previous posts [1],[2] perl was used to get content from the web through Faroo API and Guardian APIs. In this post PHP and Pyhton will be used to get web data using same APIs. PHP has a powerful JSON parsing mechanism, which, because PHP is a dynamic language,\u2026","rel":"","context":"In "API Programming"","img":{"alt_text":"Trend for Python, Perl, PHP","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2016\/07\/trend_for_python_perl_php-300x144.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":313,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/06\/15\/faroo\/","url_meta":{"origin":76,"position":5},"title":"Getting Data from the Web with Perl and The Guardian API","date":"June 15, 2016","format":false,"excerpt":"In one of previous post the Faroo API was used in order to get data content from the web. In this post we will look at different API that can be also used for downloading content from web. Here we will use the Guardian API \/ open platform. At the\u2026","rel":"","context":"In "API Programming"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/76"}],"collection":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/comments?post=76"}],"version-history":[{"count":1,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/76\/revisions"}],"predecessor-version":[{"id":77,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/76\/revisions\/77"}],"wp:attachment":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/media?parent=76"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/categories?post=76"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/tags?post=76"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}