The world of finance is constantly evolving, and investors need to stay informed about market changes to make smart investment decisions. With the abundance of financial data available on the internet, it can be overwhelming to keep track of everything. Fortunately, Google Sheets offers powerful tools to help investors analyze stocks and monitor their portfolios with the Google Finance function.
While there are many resources available on the internet about using Google Sheets Finance function, most of them cover only the basics and don’t provide much value beyond the official Google documentation. In this article, we’ll quickly cover the basics of getting started before moving on to more advanced functions and scripting techniques in future articles. We’ll also demonstrate how to implement automatic quantitative and technical analysis of markets and instruments into stock watchlists and portfolio monitors.
Google Sheets Prerequisite
To get started with using Google Sheets, all you need is a Google account and access to Google Drive. Any operating system that supports a modern browser can be used. While Google Chrome may have some advantages over other browsers, such as Firefox, Opera, or Microsoft Edge, most browsers will work just fine. It’s worth noting that the examples provided in this article were produced using the Google Drive app on a Windows 11 operating system.
Google Drive and Google Sheets basics
Google Drive is an online storage platform that comes with 15GB of storage space (more for premium users). For some operating systems, there is software or an app available that can be used to synchronize with a folder on a local device, such as a PC, tablet, or smartphone. Any data stored in Google Drive can be easily organized within a folder structure, just as in most operating systems. It also offers the ability to share or publish documents online.
Among the office programs included in Google Drive is Google Sheets, which is a spreadsheet software similar to Microsoft Excel, Open Office Sheets, LibreOffice, or WPS. The primary difference with Google Sheets is that it is cloud-based and therefore requires internet access. However, some offline capabilities may be available, such as in Chrome OS or Chrome Browser.
While Google Sheets was once seen as inferior spreadsheet software, in recent years, it has become increasingly capable and competitive.
The functionality of Google Sheets has greatly improved over the years, and it now includes advanced spreadsheet tools that can handle relatively complex calculations. A complete overview of these features can be found here. Notably, one area where Google “excels” is in its help documentation, which is far more accessible and coherent than that of most other office software packages. Google Sheets includes built-in capabilities for engineering, financial, statistical, mathematical, and other types of calculations, with easy-to-follow instructions and examples.
About Google Finance
Google Finance is a website that provides market data, news, and financial information about publicly traded companies. It allows users to search for a specific stock or company and view a range of financial metrics, including historical price data, key financial ratios, and news articles related to the company.
In addition to individual stock information, Google Finance offers market summaries, world indices, and currency exchange rates. Although the website is a great data resource, the search functionality is relatively limited, allowing only basic searches and providing limited charting capabilities. Therefore, it is better to use TradingView as your charting platform and combine it with Google Finance to access the data.
Google Finance’s disclaimer mentions that the information provided is intended solely for informational purposes, not trading purposes or advice. Furthermore, the information may be delayed. To find a list of the markets available on Google Finance, you can refer to their Finance Data Listing and Disclaimers page.
Google Finance Function Basic Attributes
Google Finance function retrieves current or historical securities price data from Google Finance.
The built-in attributes are documented and can be found here, and there are various websites and tutorials offering guidance on these attributes. The table provided below outlines the fundamental attributes that can be accessed, and in the subsequent post, we will delve further into the process of obtaining historical data
|"price"||Real-time price quote, delayed by up to 20 minutes|
|"priceopen"||The price as of market open|
|"high"||The current day's high price|
|"low"||The current day's low price|
|"volume"||The current day's trading volume|
|"marketcap"||The market capitalization of the stock|
|"tradetime"||The time of the last trade|
|"datadelay"||How far delayed the real-time data is|
|“volumeavg”||The average daily trading volume|
|“pe”||The price/earnings ratio|
|“eps”||The earnings per share.|
|“high52”||The 52-week high price|
|“low52”||The 52-week low price|
|“change”||The price change since the previous trading day's close|
|“beta”||The beta value|
|“changepct”||The percentage change in price since the previous trading day's close|
|“closeyest”||The previous day's closing price|
|“shares”||The number of outstanding shares|
|“currency”||The currency in which the security is priced|
|"name"||The full name of the security|