{"id":79,"date":"2016-02-13T12:56:02","date_gmt":"2016-02-13T12:56:02","guid":{"rendered":"http:\/\/intelligentonlinetools.com\/blog\/?p=79"},"modified":"2017-11-09T23:01:39","modified_gmt":"2017-11-09T23:01:39","slug":"adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script","status":"publish","type":"post","link":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","title":{"rendered":"Adding Stock Market Data to a Google Spreadsheet with a Perl Script"},"content":{"rendered":"

In the previous post<\/a> it was shown how to download stock data data prices into Google spreadsheet using perl script.
\nThis 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. <\/p>\n

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

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

Here is the whole script. To run it first time you need disable the line $mode=”ad”; by putting # in front of $ sign.
\n Then remove # so it will run in the addition of data mode.
\nDon’t forget also put your email, password and document \/ spreadsheet title names.
\nuse Finance::QuoteHist;
\n#if first time download data into empty sheet $mode =””;
\n #if adding data to the sheet with previously downloaded data $mode=”ad”;
\n #disable this line if first time download data
\n$q = Finance::QuoteHist->
\nnew ( 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;
\n $date =~ s\/\\\/\/-\/g;
\n print “$symbol, $date, $open, $high, $low, $close, $volume \\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; }
\n use Net::Google::Spreadsheets;
\n my $service = Net::Google::Spreadsheets->new( username => ‘youremail@company.com’, password => ‘xxxxxxx’ );
\n my @spreadsheets = $service->spreadsheets();
\n my $spreadsheet = $service->spreadsheet( { title => ‘titlename’ } );
\n my $worksheet1 = $spreadsheet->worksheet( { title => ‘Sheet_name’ } );
\n if ($mode eq “ad”) { for($i=2; $icell({col => 1, row => $i}); $v=$cell->input_value;
\n if ($v eq “”) { for($j=1; $jcell({col => $j, row => $i}); $cell->
\ninput_value($data[$i-2][$j-1]); } } }
\nexit; }
\n# if $mode = “ad”
\n$cell = $worksheet1->
\ncell({col => 1, row => 1});
\n $cell->input_value(“symbol”);
\n $cell = $worksheet1->cell({col => 2, row => 1});
\n $cell->input_value(“date”);
\n $cell = $worksheet1->cell({col => 3, row => 1});
\n $cell->input_value(“open”);
\n $cell = $worksheet1->
\ncell({col =>4, row => 1});
\n $cell->input_value(“high”);
\n $cell = $worksheet1->cell({col => 5, row => 1});
\n $cell->input_value(“low”);
\n $cell = $worksheet1->
\ncell({col => 6, row => 1});
\n $cell->input_value(“close”);
\n $cell = $worksheet1->cell({col =>7, row =>; 1});
\n$cell->input_value(“volume”);
\n for($i=2; $i { for($j=1; $jcell({col => $j, row => $i});
\n$cell->
\ninput_value($data[$i-2][$j-1]);
\n} }
\nNow we can use this script for downloading and updating data on regular basis.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … 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":"\nAdding 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=\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-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=\"Adding Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications\" \/>\n<meta property=\"og:description\" content=\"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 ... Read more\" \/>\n<meta property=\"og:url\" content=\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-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:56:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-11-09T23:01:39+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\",\"url\":\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\",\"name\":\"Adding 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:56:02+00:00\",\"dateModified\":\"2017-11-09T23:01:39+00:00\",\"author\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\"},\"breadcrumb\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-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\":\"Adding 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":"Adding 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":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","og_locale":"en_US","og_type":"article","og_title":"Adding Stock Market Data to a Google Spreadsheet with a Perl Script - Machine Learning Applications","og_description":"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 ... Read more","og_url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","og_site_name":"Machine Learning Applications","article_published_time":"2016-02-13T12:56:02+00:00","article_modified_time":"2017-11-09T23:01:39+00:00","author":"owygs156","twitter_card":"summary_large_image","twitter_misc":{"Written by":"owygs156","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","name":"Adding 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:56:02+00:00","dateModified":"2017-11-09T23:01:39+00:00","author":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478"},"breadcrumb":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-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":"Adding 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-1h","jetpack-related-posts":[{"id":76,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/downloading-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","url_meta":{"origin":79,"position":0},"title":"Downloading Stock Market Data to a Google Spreadsheet with a Perl Script","date":"February 13, 2016","format":false,"excerpt":"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\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":79,"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":79,"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":79,"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":827,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/01\/11\/apis\/","url_meta":{"origin":79,"position":4},"title":"Useful APIs for Your Web Site","date":"January 11, 2017","format":false,"excerpt":"Here\u2019s a useful list of resources on how to create an API, compiled from posts that were published recently on this blog. The included APIs can provide a fantastic ways to enhance websites. 1. The WordPress(WP) API exposes a simple yet powerful interface to WP Query, the posts API, post\u2026","rel":"","context":"In "API Programming"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1070,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","url_meta":{"origin":79,"position":5},"title":"How to Write to a Google Sheet with a Python Script","date":"March 12, 2017","format":false,"excerpt":"My post How to Write to a Google Spreadsheet with a Perl Script that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase \"Google Sheet\" from\u2026","rel":"","context":"In "Python Scripts"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/03\/Google-sheet-accessed-through-web-300x179.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/79"}],"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=79"}],"version-history":[{"count":7,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":82,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/79\/revisions\/82"}],"wp:attachment":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}