The GOOGLEFINANCE function in Google Sheets is useful when you want to track stocks in a spreadsheet.
GOOGLEFINANCE allows you to track data about a specific stock in real-time. You just need to provide the company’s ticker symbol and the specific information you need.
Let’s take a look at a use case for tracking stocks in Google Sheets.
As an investor, you want to have a good view of the price movement of your top investments. Many online services can help you track your stocks, but they are often not free. With Google Sheets, we can easily create our tracker for our stocks without any cost.
GOOGLEFINANCE function allows us to get accurate price data from various sources, including the New York Stock Exchange. Besides price, we can also check several other attributes, including the current trading volume, the high and low price of the day, and the market cap. The function allows us to compare historical data as well.
There are so many possible attributes to consider when creating a Google Sheets stock tracker. For this article, we’ll look into computing the current value of our investment and visualizing price movement from the past three months.
Move on to the next section to learn how to track stocks in Google Sheets and figure out how to make the tracker from scratch.
A Real Example of Tracking Stocks in Google Sheets
In this section, we’ll take a look at a real example of a spreadsheet that allows us to track stocks in Google Sheets.
In the example below, we have an investment tracker that gives us an overview of stock performance. The tracker requires us to provide the ticker symbol and the number of shares per stock.
Google Finance provides the current price, today’s change in price, and the historical data to make the 90-day chart. From the tracker, we can see that our largest holding is in AMZN stock and that ORCL has dropped the lowest today in terms of percentage.
To get the prices in Column C, we just need to use the following formula:
The current value of each investment is computed by simply multiplying the current price with the number of shares in the second column.
The 90-Day price chart is possible because of the
SPARKLINE function. The sparkline is a miniature chart that’s contained within a single cell. In this case, we’ve set a line graph that visualizes Google Finance data from the past 90 days.
We can get historical data by providing the start date and end date. This particular case sets the start date with
TODAY() - 90 and the end date with
The full formula used for creating the sparkline is seen below:
Lastly, we can get the percent change in price today by using the “changepct” attribute.
The percentages in column F is retrieved using the following formula:
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try tracking stocks in Google Sheets, follow the steps in the next section to make your tracker!
How to Track Stocks in Google Sheets
In this section, we will go through the process of creating an investment tracker in Google Sheets. This guide will show you how easy it is to track stocks in Google Sheets.
Follow these simple steps to start creating the investment tracker:
- First, make sure that we have the necessary information. We need to know the ticker symbol of the stock and the number of shares you have. Afterward, select the first cell in the third column, which will hold our price data.
- Next, we just have to type the equal sign ‘=‘ to begin the function, followed by ‘GOOGLEFINANCE(‘.
- You may find a tooltip box with hints on how to use the function. Click on the arrow on the top-right-hand corner of the box to minimize this box.
- Type in our argument, which is just the ticker symbol of our stock. Next, we add a cell reference A2 with the AAPL ticker. Hit Enter on your keyboard to let the function evaluate. We now have the latest price of our stock.
- To get the current value of our holdings, we just need to multiply the current price per share by the number of shares we have. In this example, we now know that we have over $8.6K in Apple stock.
- Adding a sparkline will give us a good visualization of the historical performance of our stocks. We do this by using the SPARKLINE function and setting a start, and end date on our GOOGLEFINANCE data pull.
- To get the percentage change in price today, we just have to use the
GOOGLEFINANCEfunction. After selecting which stock to refer to, we add “changepct” as our second argument. This attribute refers to the percentage change today. We divide by 100 to get the actual percentage value.
- Lastly, we can drag down the first row to fill out the rest of our dataset.
That’s all you need to remember if you want to track stocks in Google Sheets. This step-by-step guide shows how easy it is to create your own investment tracker using data provided by the Google Finance service.
The GOOGLEFINANCE function is just one example of a financial function you can work with in Google Sheets. With so many other Google Sheets functions out there, you can surely find one that suits your use case.
Are you interested in learning more about what Google Sheets can do? Stay notified of new Google Sheets guides like this by subscribing to our newsletter!