The internet has hundreds of blogs, posts, examples and tutorials about using the Google Finance functions in Google Sheets. However, the vast majority are basic and add little to the official Google documentation. Here will will cover the basics very quickly and move on to include some advanced functions and scripting. Automatic Quantitative and technical analysis of markets and instruments will be implemented into stock watchlists and portfolio monitors.
A Google account, access to Google Drive and Google Sheets. Any OS that supports a modern browser will do, and while Google Chrome browser may potentially have some advantage over Firefox, Opera, or Microsoft edge , everything works fine in most browsers. Any examples produced here are on Windows 10 OS running the Google Drive app.
Google, Google Drive and Google Sheets basics
Google Drive is online storage with 15GB (more for premium users). For some OS’s there’s a software or App that can be used to sync with a folder on a local device (eg PC, tablet, smartphone). Any data stored in Google Drive can be moved around in a folder structure just as on most operating systems, as well as the ability to share or publish documents online.
Google Sheets is one of the office programs included in Google Drive, and is a spreadsheet software similar to Microsoft Excel, Open Office Sheets, LibreOffice, or WPS. The main difference with Google Sheets is that it’s in the cloud and therefore requires internet access, though some offline capabilities may be available (for example in Chrome OS or Chrome Browser). In the past, Google Sheets was an inferior spreadsheet software but in the past few years it has become increasingly viable.
Google Sheets basic functionality
The functionality of Google Sheets has greatly improved over the years and nowadays even relatively complex spreadsheet tools are built in, including the ability to perform fairly complicated calculations. A full overview can be found here. Its worth pointing out that one area where Google “excels” is in the Help documentation – it is far more accessible and coherent than most other Office software packages. Engineering, financial, statistical, math and other types of calculations are built in, and Help contains easy to follow instructions and examples.
About Google Finance
Google Finance is a market data website with fairly limited functionality. It allows basic searches and limited charting. For more advanced charting, Trading View is better. Google Finance does not seem to have been actively developed for a while. “Information is provided “as is” and solely for informational purposes, not for trading purposes or advice, and may be delayed.” A list of markets available on Google Finance can be found here.
Getting Finance data into Sheet
The built-in functions are documented here and there are plenty of sites and tutorials covering these. The screen-shot below illustrates the simplest possible use, in the following post will delve into the process of retrieving historical data.