Making the most out of Google Finance

A free resource to get price data

I often find myself using Google Sheets to quickly get a technical overview of what's happening in a specific stock or even a basic market overview.

Here’s an example of a Google Sheets market dashboard. Please note that the data displayed may be outdated; the image is provided solely as a sample to showcase the dashboard layout and functionality.

This is mainly because Google Sheets allows access to Google Finance data through simple formulas. While many websites provide this data, the ability to customize it to my liking is what makes me use it often. Let me walk you through some of the basics.

Getting the Current Price

All you need is the stock ticker.

=googlefinance("Ticker", "price")

For example, if you were trying to get the price for Icici Bank, it would be:

=googlefinance("NSE:ICICIBANK", "price")

And you are not limited to just stock prices. Think currencies, commodities, mutual funds, etc. For instance, want the India Vix, all you do is

= googlefinance("INDEXNSE:INDIA_VIX", "price")

To find the right ticker always head to google finance and search for the stock/index. The ticker should pop up automatically in the search bar.

An example of the ticker automatically coming when you search on google finance.

Get historical data

To get historical data between two specific dates you can use the formula -
=googlefinance("Ticker", "close", "Start_Date", "End_Date")

For example, to get the historical closing prices of ICICI Bank from January 1, 2020, to January 1, 2024, the formula would be:
=googlefinance("NSE:ICICIBANK", "close", "01-01-2020", "01-01-2024")

An example of using a google finance formula for ICICI Bank

The moment you get historical closing data, you can calculate all sorts of metrics such as daily returns, rolling returns, standard deviation, moving averages, beta, etc. (As seen above)

Calculating Returns

Again, spend enough time on GPT and you’ll find really efficient ways of doing this. Here is something I have come up with, to get returns. The beauty is that once you set it up, all you do is enter the right ticker.

Other metrics

There’s a lot more you can explore. You can use historical returns to calculate momentum scores and create a dashboard to sort stocks based on momentum. (I do this) You could also calculate basic metrics like 52-week lows, 52-week highs, and more using built-in Google Finance formulas. Here are a few examples:

• 52-week low: =googlefinance(“NSE:ICICIBANK”, “Low52”)

• 52-week high: =googlefinance(“NSE:ICICIBANK”, “High52”)

• Price to Earnings (P/E): =googlefinance("NSE:ICICIBANK", "PE")

I could go on, but as I mentioned, it’s simple, free, and easy to follow. For more information on what you can do, check out the Google Finance documentation page.