Google Finance Investment Portfolio Tracker for Indian Stocks (using Google Sheets)

Till Nov’2017, Google Finance provided excellent tools for portfolio tracking and stock screening. But Google decided to discontinue this service since then. If you want to know about the discontinued features of Google Finance, you can read this blog post.

People like me were a die-hard fan of Google Finance’s stock screener. I used to track all my stock holdings in their portfolio tracker.

Though I’ll admit that their portfolio tracker was not awesome, but due to continued usage, I became a habitual user of it. I’m sure there were many people like me, who would have wanted the Google Portfolio tracker to continue. But anyways, it saw its demise.

So people like me did what? Where we migrated after Google Finance? I know, majority went to use other services like Yahoo Finance etc. But I remained with Google Finance. How? I started using Google Finance in Google Sheets

Why Google Finance?

[Check how I use MS EXCEL to analyse stocks]

As I told you, I was almost a habitual user of Google Finance from my earlier days of stock investing. Hence, a transition to another service provider was a task.

But more than my personal limitation, there was something else which held me with Google Finance – it is called Google Finance Attributes. Using these attributes, I was able to create an almost duplicate portfolio tracker (like Google Finance) in Google Sheets.

Once I was able to create my stock’s portfolio in Google Sheets, I could see no reasons to switch to any other service provider.

In this article, I will explain in brief how I built my Google-Finance-like portfolio tracker in Google Sheets.

Steps to build a portfolio tracker

There are two steps necessary to build a portfolio tracker in Google Sheets. A trained mind can create a template, and start tracking stocks performance in less than 30 minutes. It only takes time for the first stock. The balance is almost a copy-paste task. So let’s see the two steps:

• Step #1 – Fetching Data: Before we can start tracking performance of our stocks in Google Sheets, we must build a template in which the relevant stock data can be fetched (over internet). How to fetch the data? Using Google Finance Attributes. We will see the details later.
• Step #2 – Prepare Report Sheets: The old Google Finance theme had three main reporting sheets, they were named like this: (a) Overview, (b) Fundamentals, and (c) Performance. When I built my stock’s portfolio tracker, I exactly duplicated the overview and fundamental reports. I made my customised changes in Performance reporting.

In the next portion of this article, you will get further detailing of the above 2 steps.

[Do you know you can use EXCEL formulas to get your investment answers, check out how.]

Step #1: Fetching Data

The above is a template which I use to fetch stock data in Google Sheets. What you can see above is the data of two stocks TCS and RIL. Let’s see how to prepare this template:

• Create a Duplicate Template: Open your Google Sheets and prepare a format same as above. Copy the columns marked as “SL and Attributes”. Take special care to copy the info from SL 7 to 24 (without spelling errors). You can also get this data in Google support page. Additional notes are here:
  • Symbol: This is the stock ticker. Type the name of your stock in Google Search, you will get the ticker name. Manually enter this data in your google sheet.
  • Name: This is the name of your stock. Example: For ticker “NSE:TCS”, the full name will be Tata Consultancy Services. Manually enter the name in your google sheet.
  • Purchase Date: This is the date on which you have purchased your stock. Manually enter this data in your google sheet.
  • Qty (nos): This is the number of stocks you have purchased on the above date. Manually enter this data in your google sheet.
  • Total Cost: This is the total cost (including brokerage, taxes etc), you paid to buy the above number of stocks. Manually enter this data in your google sheet.
  • Average Cost Per Share: This is cost per share you paid to buy the above stocks. Use this formula to calculate average cost per share ( = Total Cost / Qty).

[Learn how to build a expense tracking software in EXCEL]

• Fetch Data: Which are the stock’s data that can be fetched using Google Finance? All attributes shown in serial number 7 to 24 can be fetched. How to do it? By using a simple formula. Above I have shown how to fetch “price” data of TCS. Below you will get formulas for few more attributes:
  • priceopen: =GoogleFinance(D2,B9)
  • high: =GoogleFinance(D2,B10)
  • low: =GoogleFinance(D2,B11)
  • volume: =GoogleFinance(D2,B12)
  • marketcap: =GoogleFinance(D2,B13)
  • closeyest: =GoogleFinance(D2,B24)
  • Shares: =GoogleFinance(D2,B25)

In case you want to know more about using the Google Finance formulas, check the support page of Google.

Once you are done here, you have fetched the stock data in your google sheets. Now you are ready to create the report sheets.

Step #2 – Prepare Report Sheets

[Learn how you can analyse your stocks on your own using MS EXCEL.]

There are 3 report sheets: ‘Overview’, ‘Fundamentals’, and ‘Performance’. I will explain how I’ve prepared the Performance report sheet. Using this information, it will be easy for you to prepare the ‘overview’ and ‘fundamentals’ reports.

To prepare the Performance report, I have used the “HLOOKUP” formula of Google Sheets. It is similar to HLOOKUP formula of MS Excel.

Why HLOOKUP formula is used? In Step #1, we have fetched stock’s data from internet into our Google Sheets. This has become our main database (I have also named this sheet as Database). Now, we will use HLOOKUP function to pick relevant information from our ‘database sheet’, to other repot sheets.

Performance Sheet

The header of the performance sheets looks like shown above. You must copy the same in your google sheet.

Please note the numbers written on top of the headers. These numbers are the serial numbers indicated in Step#1 (see fetching data template).

What is the use of these numbers? Example:

• “Number 7” indicated above ‘Latest Price’ means, in this column we will call “price” data from the ‘Database Sheet’ using HLOOKUP formula.
• “Number 6” indicates, we will call “Avg Cost / Share” from the ‘Database Sheet’ using HLOOKUP formula..

Let’s see how this is done:

Let me show you what it means by “Database!$D$2:$J$25” visible in the above highlighted formula. Check the below pic.

To learn more about HLOOKUP function of Google Sheets check this support page.

Overview Sheet

How the ‘overview sheet’ looks, is shown below. You can use the same HLOOKUP function explained above to generate the overview report.

Which value will appear in the overview sheet? Price, Change, Market Cap, Volume, Open price, High price, Low price, and Day’s Gain.

In the above sheet all data will be pulled by HLOOKUP formula from the main Database sheet, except for “Day’s Gain”. This must be calculated using this formula = Change/Price.

How to use HLOOKUP formula? Check the explanation given for the Performance Sheet.

Fundamentals Sheet

In this sheet all data is pulled from the main Database sheet using HLOOKUP formula. Nothing needs to be calculated manually.

Which value will appear in the overview sheet? Price, Change, Market Cap, Average Volume, 52 Wk High, 52 Wk Low, EPS, P/E, and Beta.

How to use HLOOKUP formula? Check the explanation given for the Performance Sheet.

Final Words

Apart from the above set of reports, I also use Google Sheets and Google Finance Attributes to generate the stocks ’10 year price data’. Why I do it? Because it helps me to gauge how the price of stock as performed in last 10 years.

How 10 years price data can be fetched? Use the formula shown in the below graphics:

Once the price data is available, one can also generate the 10 years price chart as shown below. This is the 10 Year price chart of TCS. It gives a good idea of how the price has behaved in long term:

I hope you’ve found this article helpful. It will be great if you can leave your comment below.

Have a happy investing.

Handpicked Articles:

• A Stock Analysis Worksheet V2.1.3.
• How to use EXCEL Formulas for investment?
• Track your expenses in MS Excel.
• Analyse your stocks on your own using Excel.