Last Updated on 12/06/2023 by Ndanileka
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.
Basic Attributes
| Attribute | Description |
|---|---|
| “price” | Stock price. In real-time but with a delay of up to 20 minutes. |
| “priceopen” | Opening price (price at market open). |
| “high” | High price of the current day. |
| “low” | Low price of the current day. |
| “volume” | The trading volume of the current day. |
| “marketcap” | The market capitalization of the stock. |
| “tradetime” | The time of the last trade of the stock. |
| “datadelay” | The delay time for the real-time data. |
| “volumeavg” | The average daily trading volume. |
| “pe” | Price-to-earnings ratio |
| “eps” | Earnings per share |
| “high52” | The highest price in the last 52 weeks. |
| “low52” | The lowest price in the last 52 weeks. |
| “change” | The stock price change since the end of yesterday’s trading. |
| “beta” | The beta value |
| “changepct” | The percentage change in price since the end of yesterday’s trading. |
| “closeyest” | Yesterday’s closing price. |
| “shares” | The number of shares outstanding. |
| “currency” | The currency that the stock is priced in. |
Historical Data Attributes
| Attribute | Description |
|---|---|
| “open” | Price at market open. |
| “close” | Price at market close. |
| “high” | The high price during the specified time period. |
| “low” | The low price during the specified time period. |
| “volume” | The volume during the specified time period. |
| “all” | Returns all of the above. |
