You might wonder to know that you can track stocks in Google Sheets. To do so, Google Sheets offers a function called GOOGLEFINANCE. This function is a lesser-known function to many users. But, this can effectively track the stock market for you so that you can invest wisely.
Besides, whether you’re a newbie or an expert in the stock market, you can use this function to track and monitor your stock market investment. Using this function, you could surely get a complete insight into current and previous stock business information.
I’ll be with you for the entire post to let you know what Google Finance is and how you can use it to track stocks in real-time. Well, before showing you the step-by-step process, allow me to let you know what Google Function is and a real example of GOOGLEFINANCE function.
What is Google Finance Actually Is?
Google Finance is a real-time tool of Google. It displays the market-related business news (e.g., latest market information, aggregates business news). However, the good news is that Google Search and Finance are recently integrated.
As a result, if you know a ticker symbol (e.g., Walmart= WMT, Apple= AAPL) of any company or corporation and look that up on Google, then you’ll get all the current stock updates, current value, and historical data.
Once you find the stocks, you can go to that particular company’s Finance page, provided by Google. And you’ll find the financial information of the company on this page.
Despite having many other tools available to track company information, Google Finance is the most reliable one. Plus, it has the integration with Google Sheets.
Therefore, it doesn’t matter whether you’re a novice or a pro in this sector; the integration makes your stock tracking more effortless than ever because you can track stocks in Google Sheets eventually.
N.B: The only language is English which supports Google Sheets integration with Google Finance. Other international languages are yet to come.
Example of Tracking Stocks in Google Sheets Using the GOOGLEFINANCE Function
In this section, I’ll show you a real-time example of using the GOOGLEFINANCE function to track stocks. The below example displays a stock tracker (overall stock performance) for the last 90-days.
However, to make this stock tracker in Google Sheets, you need to have a ticker symbol of stocks and per stock share numbers, as I have in columns A and B.
Using the Google Sheets tracker, you’ll get stocks’ current price, current value, change the price (today), and the historical data so that a 90-day chart results.
As you can see from the tracker chart, the largest stockholder here is Apple (AAPL), and the lowest stockholder is Oracle Corporation (ORCL), based on percent changes (today).
Finding Out the Current Price and Value of the Stocks
According to column C heading, I figured out the current price there. And to do so, the below formula is used:
=GOOGLEFINANCE(A2)
After that, I figured out the current value of the stocks. You need to apply simple multiplying (multiply the number of shares with the current price) here.
Here comes the visually presented 90-day chart part. Now, to get a 90-day tracking chart, the SPARKLINE function is used. However, the chart only contains a single cell.
In this miniature chart, I’ve set a line graph displaying the stock market up and down. The graph shows the past 90 days’ data of Google Finance.
Using of SPARKLINE Function to Get Graphical Chart
To set the data in the SPARKLINE function, I’ve used attributes: TODAY() – 90 as the start date and TODAY() as the end date.
As a result, the tracker chart shows the stock results for the past 90 days. Having described all these, below is the formula is used here-
=SPARKLINE(GOOGLEFINANCE(A2,"price",TODAY()-90,TODAY(),"daily"),{"charttype","line";"linewidth",1.5;"color","#5f88cc"})
Everyday Price Change in terms of Percentage
Last but not least, I figured out the percent change in today’s price in the column of the chart. To do so, I’ve used ‘changepct’ as an attribute.
Here is the formula below that is used in column F
=GOOGLEFINANCE(A2, "changepct")/100
How to Track Stocks in Google Sheets (Step-by-Step)
The following section will show you a step-by-step guideline to help you create an investment tracker. And, once you complete all the steps below, you’ll wonder after seeing the easiness of tracking stocks in Google Sheets. Let’s start-
Step 1: Click on Cell to Get Current Price of the Stock
Once you have all the information you need, click on cell C2 from the third column to get the stock’s current price data. However, before doing this, ensure you have the ticker symbol and number of shares in columns A and B.
Step 2: Type the Function to Track Stocks in Google Sheets
Now type the equal sign (=) to start the GOOGLEFINANCE function in the cell. Once you do it, you’ll get something like this, ‘GOOGLEFINANCE(‘.
When the function appears, you’ll get a guide below the function on how to use all the attributes. However, you hide this box by clicking on the cross sign from the top right of the box.
Step 3: Current Price of the Stock
Now, time to find out the current price. Click on cell C2, and type the GOOGLEFINANCE function with the equal sign. After that, add the cell reference that has the ticker symbol MSFT.
Once done, click on Enter button on the keyboard. And the function will do the rest. Finally, you’ll get the current price of your selected stock.
Step 4: Calculate Stock’s Current Value to Track Stocks in Google Sheets
In this step, you’ll figure out the current value of the stocks. And to get so, you need a simple multiplication of the current price (cell C2) and the number of shares (cell B2).
Once you type the cell reference using the multiplication sign in between, click on Enter button to get the result.
Based on the below example, now you know you have over $16K in Microsoft Corporation stock.
Step 5: Track Stocks in Google Sheets Creating Visual Tracker
This step presents the stock chart’s historical performance as a visual presentation. SPARKLINE is the function that provides you the visualization.
Therefore, a start and end date have been supplied in the GOOGLEFINANCE data. The result you can see below.
Step 6: Everyday Changes in Percentage
Now time to find out today’s price change in a percentage format. The GOOGLEFINANCE function plays the to find that out.
Once you select the stock (first argument) for which you want today’s price change, use ‘changepct,’ as the second argument. However, this second attribute represents the price change today in percentage.
As I said, this will result in a percentage format, so dividing by 100 is there as the end. Once you put all the arguments, type the Enter and get the result in a percentage format.
Step 7: Drag Down Each Row and Complete the Tracker Chart
Last but not least, when you have the result for the initial cell of each column, drag the first row of each column down to the rest of the cell that includes the data. And get the expected result in the complete datasheet.
This is how you can easily track stocks in Google Sheet by making an investment tracker using the GOOGLEFINANCE function.
Insights, Finally
There you have your step-by-step process of quickly tracking stocks in Google Sheets by making an investment tracker. The lesser-known function GOOGLEFINANCE can help you out here.
Therefore, from now on, don’t worry about keeping stock market updates from time to time. Use your Google Sheets, make an investment tracker, and you’re done.
I hope that the post will be helpful for you if you’re someone who is an enthusiast of the stock market. However, if you find this helpful, let others know who are also passionate about the stock market like you so that they can also track stocks in Google Sheets easily.
