Google Sheets is more than just an online spreadsheet. It boasts powerful features, including the ability to add real-time stock quotes to your spreadsheet. That’s where the GOOGLEFINANCE function comes in. In this article, I’ll explain in detail how to use this function to track stock prices in Google Sheets.
Why You Need to Track Stock Prices in Google Sheets?
Before diving into the details of the GOOGLEFINANCE function, it’s important to understand why you might need to track stock prices in Google Sheets. Firstly, Google Sheets provides a secure cloud environment to store and manage your financial data. By adding the GOOGLEFINANCE function, you can easily import live quotes into your spreadsheet, enabling you to build your own real-time stock tracker.
But why use this function? Here are a few reasons:
-
Track your investments: You can use Google Sheets to keep track of the stocks you’ve already invested in. This allows you to see how much you’ve gained (or lost) with your investments.
-
Make investment decisions: By tracking real-time stock prices, you can find signals that prompt you to invest in certain stocks. For example, if a stock’s 52-week low price increases by more than X%, you might decide to invest.
-
Compare different companies: You can use Google Sheets to compare the performances of different companies. This can help you make informed decisions about your investments.
Now that you understand why tracking stock prices in Google Sheets can be useful, let’s move on to how to use the GOOGLEFINANCE function.
Understanding the Syntax of the GOOGLEFINANCE Function
Before delving into the technical details, let me explain the syntax of the GOOGLEFINANCE function. This will help you understand how to use the function correctly.
The syntax of the GOOGLEFINANCE function is as follows:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])Here’s what each element of the syntax means:
-
Ticker: This is a unique code or stock symbol used to identify a company in the stock market. The GOOGLEFINANCE function uses this symbol to return information about the company’s stocks. If the stock symbol is not specified, the function will use its own judgment to choose one or return an #N/A error. -
Attribute: This refers to specific information about the company that you want to retrieve. This can include the stock price, change, opening price, highest price, lowest price, market capitalization, volume, etc. You can find all the possible attributes and the values they return in the official GOOGLEFINANCE documentation. -
Start date (optional): This date is only required if you want to retrieve past information. It indicates the first day from which you want to retrieve data. When used alone, it returns information for that specific day. -
End date (optional): This is the last date from which you want to retrieve historical information. When used alone, it returns the stock price for that date. -
Interval (optional): This indicates the frequency of the data you want to retrieve, whether it’s daily or weekly information. The only accepted values are 1 for daily frequency and 7 for weekly frequency. Any other value will return an error.
Now that you know the syntax of the GOOGLEFINANCE function, let’s see how to use it in practice.
How to Use the GOOGLEFINANCE Function
To show you how to use the GOOGLEFINANCE function in practice, let’s look at how to track the stocks of the four tech giants (Facebook, Amazon, Netflix, and Google) in Google Sheets.
Here are the steps to follow:
-
Create table headers for each data.
-
Enter the stock symbols of the companies in a column of the table.
-
Use the formula
=GOOGLEFINANCE(B2,"name")to get the name of each company. Then copy this formula down the column. -
Use the formula
=GOOGLEFINANCE(B2,"Price")to get the stock price of each company. Copy this formula down the column. -
Use the formula
=GOOGLEFINANCE(B2,"low52")to get the 52-week low price. Copy this formula down the column. -
Use the formula
=GOOGLEFINANCE(B2,"high52")to get the 52-week high price. Copy this formula down the column.
Here’s what your spreadsheet would look like:
As you can see, the GOOGLEFINANCE function is generally called with the stock symbol (like FB or AMZN) followed by an attribute (like “price”, “low52”, or “high52”). This allows you to get basic information about the stocks you’re tracking.
Other Attributes of the GOOGLEFINANCE Function
In addition to the attributes we’ve already mentioned, the GOOGLEFINANCE function offers many other attributes you can use to get specific information about stocks.
Here are a few examples:
-
Volume: This refers to the trading volume of the stock. It tells you how many shares are being traded, which can indicate the popularity of the stock among investors. -
Market Capitalization: This is the market capitalization of the stock. It tells you how much money is invested in the stock, which can be an indicator of its potential for growth. -
Price/Earnings Ratio (P/E): This is the ratio between the stock price and its earnings. It tells you whether the stock is undervalued or overvalued compared to its earnings. -
Earnings per Share (EPS): This refers to the earnings per share of the stock. It allows you to compare the current value of the stock to its earnings. -
Number of Shares: This is the total number of shares outstanding for the stock. This is important in terms of market liquidity.
These are just a few examples of the available attributes. You can use any of these attributes by specifying the stock symbol of the stock you’re tracking.
Perform Basic Analysis with Google Sheets
Now that you know how to use the GOOGLEFINANCE function to track stock prices, you can go even further by performing basic analysis with Google Sheets.
For example, you can find the stock that has recorded the highest percentage change compared to the previous day’s closing. You can also create charts to better visualize the data.
Google Sheets offers many built-in analysis features, such as Vlookup and Index, that allow you to make the most of your stock data.
Display Price History with GOOGLEFINANCE
The GOOGLEFINANCE function is not limited to providing real-time stock information. It also allows you to get the price history of a stock.
For example, you can use the GOOGLEFINANCE function to get the price history of Facebook stock over a given period. Simply specify the start and end dates in the formula.
Here’s a concrete example:
GOOGLEFINANCE("FB","price",DATE(2020,1,1),DATE(2020,1,31),"DAILY")You can also create charts to visualize the price history:
As you can see, the GOOGLEFINANCE function is extremely versatile and allows you to do much more than just track real-time stock prices.
Conclusion
The GOOGLEFINANCE function is a valuable tool for those looking to track stock prices in Google Sheets. With this function, you can easily import live stock quotes into your spreadsheet, allowing you to create your own real-time stock tracker.
However, it’s important to note that the GOOGLEFINANCE function works in real-time and may have a slight delay in updates. Therefore, it is more suited for analysis and reporting rather than real-time tracking.
Now that you know the basics of the GOOGLEFINANCE function, you can start tracking your favorite stocks in Google Sheets. Feel free to try our tips and tricks to get the best performance out of this function. Happy stock analysis!
