Google Sheets simplifies the process of monitoring stocks through an integrated function known as GOOGLEFINANCE. This unique formula enables you to keep tabs on various aspects of your stocks, such as your holdings, purchase cost, current market price, and any gains or losses. Once you've configured a sheet to track these stocks, the information is automatically refreshed, allowing you to view your entire portfolio at a glance rather than inspecting individual stock prices. In the following text, we'll guide you on how Google Sheets can be a valuable tool for tracking your stocks and maintaining a record of historical data.
What is GOOGLEFINANCE Function?
The GOOGLEFINANCE function is a specialized feature within Google Sheets designed to fetch stock data from Google Finance. It's important to note that the stock prices are not updated in real-time; there may be a delay of up to 20 minutes for the data to refresh.
This delay means that GOOGLEFINANCE may not be the ideal choice for day traders or experienced investors who require immediate updates. However, for those who regularly review their portfolio but wish to avoid the intricacies of a dedicated investment application, GOOGLEFINANCE serves as a highly convenient alternative.
Additionally, you have the option to establish a stock watchlist on Google Finance, which can assist you in monitoring other vital metrics necessary for your analysis. The formula for utilizing this function is as follows:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]).
Here are some sample you can look at:
Example: =GOOGLEFINANCE("NASDAQ:AMZN") for Google's stock.
Retrieve Specific Information
- You can retrieve specific information like price, volume, etc., by extending the function.
- Example: =GOOGLEFINANCE("NASDAQ:GOOGL", "price") to get the price of Google's stock.
- To track historical data, you can use the function with a start and end date.
- Example: =GOOGLEFINANCE("NASDAQ:GOOGL", "close", DATE(2021,1,1), DATE(2021,12,31), "DAILY") to get daily closing prices for 2021.
- You can use other attributes like "currency," "change," "high," "low," etc., to get various details.
- Example: =GOOGLEFINANCE("NASDAQ:GOOGL", "high") to get the high price.
Use Other Attributes
Recommended by LinkedIn
When The Previews Are Too Good Patrick Curtis 10 months ago
Here are some of the most commonly used attributes you can use to fetch real-time, historical, or mutual fund data:
- Real time data (price, priceopen, marketcap, tradetime)
- Real time and historical data (high, low, volume)
- Real time and mutual fund data (change, changepct)
- Real time and historical data (returnytd, netassets)
Here’s what each of the arguments in the GOOGLEFINANCE function means:
- Symbol: This is an optional argument but recommended for accurate results. The Symbol refers to the abbreviation for the name of the stock exchange. NYE, NASDAQ, SSE, and EURONEXT are examples of symbols for stock exchanges.
- Tricker: Ticker is the abbreviation for the stock as it appears on the stock exchange. It’s generally two, three, or four letters.
- Attribute: An attribute is the data you want to obtain for a specific stock. There are more than 40 attributes to fetch real-time or historical data. All of them are available on the Google Docs Editors Help page.
- Start Date: Enter a start date here to get historical data for that specific date. If you want historical data for a range of dates, you’ll also need to mention an end date.
- End Date: Enter the end date or the number of days from the start date to get historical data between the start and end dates.
- Interval: You can also specify an interval for data. Specifying an interval allows you to filter the data you're seeing. It fetches data only for days after specified intervals, such as on a daily or weekly basis.
Other features
- You can create a portfolio by using the GOOGLEFINANCE function in different cells for different stocks.
- The data will automatically refresh, but you can set the frequency by clicking on "File" > "Spreadsheet settings" > "Calculation" and choosing the desired refresh interval.
- Google Sheets also offers templates specifically designed for tracking stocks, which can be found under the "Template Gallery."
- You can explore and install add-ons like "Stock Connector" to enhance the stock tracking capabilities.
To sum up:
- By following these steps, you can effectively track stocks in Google Sheets, customizing the information to your specific needs and preferences. Here are use cases that MBA students can attempt.
