{"id":5013,"date":"2019-02-07T19:24:10","date_gmt":"2019-02-07T19:24:10","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5013"},"modified":"2019-02-07T19:29:24","modified_gmt":"2019-02-07T19:29:24","slug":"google-sheets-googlefinance-for-free-stock-data","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5013","title":{"rendered":"Google Sheets &#8220;GOOGLEFINANCE()&#8221; for free Stock data"},"content":{"rendered":"<p>I came across this YouTube video that I thought was quite enlightening &#8220;5 Things You Can do in Sheets That You Can&#8217;t Do in Excel&#8221;:<br \/>\n<iframe data-src=\"https:\/\/www.youtube.com\/embed\/vlRPTiVvs1Y\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>I was rather intrigued by it and decided to look into some of the functions more deeply. So far I have only played with the GOOGLEFINANCE() function and SPARKLINE()<\/p>\n<p>My results so far, adapting them to the NZ Sharemarket are as per screenshot below:<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-5039 lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_00_11-NZ-Shares-Google-Sheet111s-300x175.jpg\" alt=\"\" width=\"1785\" height=\"1041\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_00_11-NZ-Shares-Google-Sheet111s-300x175.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_00_11-NZ-Shares-Google-Sheet111s-768x448.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_00_11-NZ-Shares-Google-Sheet111s-1024x597.jpg 1024w\" data-sizes=\"(max-width: 1785px) 100vw, 1785px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1785px; --smush-placeholder-aspect-ratio: 1785\/1041;\" \/><\/p>\n<p>This isdidn&#8217;t take me too much effort once I watched this other YouTube video about getting a spreadsheet to reflect the old Google Finance data before it was changed, using the new features to make a spreadsheet to demonstrate setting up the original view.<\/p>\n<p>The beginning examples of what he is doing is really great. Some of the later stuff goes over my head. I used his examples to test out on the NZ Stock Exchange data.<\/p>\n<p><iframe data-src=\"https:\/\/www.youtube.com\/embed\/mZi5Oe7Vsfg\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>The other very nice thing about the above video is that he lets you download his template. What a lovely man. Thank you for sharing.<\/p>\n<p>I have used postman and Python in the past to try and grab stock data from an API but neither were as easy as this. Also I think trying to hook into the NZX api its a paid service.<\/p>\n<p>In my spreadsheet above I took all the GOOGLEFINANCE() variables\u00a0 (<em>Use help file on the function and it will list them all in google sheets)<\/em> and tested them out on the NZX, most worked, some didn&#8217;t.<\/p>\n<h3>End comment<\/h3>\n<p>As I&#8217;m totally clueless on the Stock Exchange I&#8217;d need to do a bit more research on it to see what data I could elicit that would be useful.<\/p>\n<p>I found accessing the data very easy and I will do some more research on this data.<\/p>\n<p>The GOOGLETRANSLATE() looks fun, not sure where I&#8217;d use that apart from the RSS feeds<\/p>\n<p>Another interesting one that I may explore in another post is the IMPORTFEED() function. I am keen on feeds and wouldn&#8217;t mind seeing if I can set up a more tailored feed for my interest.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I came across this YouTube video that I thought was quite enlightening &#8220;5 Things You Can do in Sheets That You Can&#8217;t Do in Excel&#8221;: I was rather intrigued by it and decided to look into some of the functions more deeply. So far I have only played with the GOOGLEFINANCE() function and SPARKLINE() My [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5040,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[204,1,265,29],"tags":[275,255,274],"class_list":["post-5013","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-automation","category-general","category-google-docs","category-web","tag-free-sharemarket-data","tag-google-sheets","tag-googlefinance"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_19_34-NZ-Shares-Google-Sheets.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/02\/2019-02-08-20_19_34-NZ-Shares-Google-Sheets.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5013","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5013"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5013\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/5040"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}