Tracking Stock Data using Google Sheets

Google Sheets can pull stock prices (delayed) using a function called GOOGLEFINANCE. This can be helpful if you want to derive your own customized formula like where is the stock positioned w.r.t 52-week high-low prices OR you may want to get the highest price of the last 30 days or 10 days.

To use the function, enter the formula into the desired cell:
=GOOGLEFINANCE(“symbol”, “attribute”)
where “symbol” represents the stock symbol of the company you are looking for and “attribute” represents the type of stock data you want information about.

For example, if you wanted to find out the current price for Reliance stock, you should enter
=GOOGLEFINANCE(“NSE:RELIANCE”, “price”)
Make sure to put quotation marks around both the symbol and the attribute for the formula to compute properly. Few attributes you can access using the GOOGLEFINANCE function are:

price: current market price of the stock, delayed by upto 20 min
priceopen: current day's opening price.
high: current day's highest price.
low: current day's lowest price.
changepct: percentage price change since previous trading day's market close
closeyest: previous trading day's market close

You can check the Google Support page for complete list of attributes.

Let us start to create a Google Sheet which could track stock data and write customized formulae. We divide our post into the following parts:

A. Fetching Data of a stock
B. Historical Stock Data
C. Customized Formulae
D. Tips - like hide, conditional formatting, %, freeze pane
E. Fetching Data for multiple stocks

How to find Company Symbol?

You can find the symbol of a company by Google Search, “CompanyName share price” and you will get the result as shown below - "ExchangeSymbol: CompanySymbol".
For ex, Google Search "Reliance industries share price" will give the stock symbol as "NSE: RELIANCE".
If you want to find the company symbol in a different exchange say, BSE, just append bse into your search query. Google Search "Reliance industries share price bse" will give the stock symbol as "BOM: 500325"
Notice the difference in Exchange Symbol and Company symbol. BSE symbol for stock is numerical.

To find the symbols of all stocks listed in the Indian Stock Exchanges :
BSE:
1. Go to BSE India OR search for "listed stocks in bse".
2. Select Segment=Equity and Status=Active. Click Submit button.
3. We get a Download icon towards the right of the page. Click on the icon to download. The excel sheet has complete list of stocks with Security Code and Security Name.

NSE:
1. Go to NSE India OR search for "listed stocks in nse".
2. Click on 'Securities available for Equity segment (.csv)' to download.
3. The csv file has Symbol and Name of Company.

A. Fetching Data of a stock

Open a BLANK Google Sheet and name it "Stock Data Tracker"
1. Column A is the Company Symbol derived above. Enter the symbol "RELIANCE" in Row2. First Row is used as Column Headers.
2. Column B is the Exchange Symbol. Enter the symbol "NSE" in Row2.
3. Column C is the Stock Symbol which is a concatenation of the "Exchange Symbol:CompanySymbol". Enter the below formula
=CONCATENATE(B2, ":" ,A2)
Notice that there is no space around the colon.
4. From Column D, we write the formula in Row2 to fetch the different stock data using the available attributes:

Column	Column Header	Formula
Column D	Company Name	GOOGLEFINANCE(C2, "name")
Column E	Current Price	GOOGLEFINANCE(C2, "price")
Column F	% Change	GOOGLEFINANCE(C2, "changepct")
Column G	Last Trade Time	GOOGLEFINANCE(C2, "tradetime")
Column H	Open	GOOGLEFINANCE(C2, "priceopen")
Column I	High	GOOGLEFINANCE(C2, "high")
Column J	Low	GOOGLEFINANCE(C2, "low")
Column K	Previous Close	GOOGLEFINANCE(C2, "closeyest")
Column L	Change	GOOGLEFINANCE(C2, "change")
Column M	52 Week High	GOOGLEFINANCE(C2, "high52")
Column N	52 Week Low	GOOGLEFINANCE(C2, "low52")
Column O	Trading Volume (in lakhs)	ROUND(GOOGLEFINANCE(C2, "volume")/100000,2)
Column P	Market Capitalization (in Cr.)	ROUND(GOOGLEFINANCE(C2, "marketcap")/10000000,2)
Column Q	Data Delay	GOOGLEFINANCE(C2, "datadelay")
Column R	P/E	GOOGLEFINANCE(C2, "pe")
Column S	EPS	GOOGLEFINANCE(C2, "eps")
Column T	Outstanding Shares (in Cr.)	ROUND(GOOGLEFINANCE(C2, "shares")/10000000,2)
Column U	Currency	GOOGLEFINANCE(C2, "currency")

B. Historical Stock Data

You can use GOOGLEFINANCE to track the stock data for a specified date or across a period of time. Google Sheets creates a table of data with column headers.
For Ex: You can get the stock price 7 days ago:
=GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7)

You can also get the stock price for the past 7 days:
=GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7,TODAY())

Notice that only the trading days are retrieved. As mentioned in the support page, Dates passed into GOOGLEFINANCE are treated as noon UTC time. Exchanges that close before that time may be shifted by a day. Since the Indian Exchanges close at 3:30 PM IST (UTC+5:30), we get the data for the next trading day.

Syntax for retrieving Historical data is:
=GOOGLEFINANCE(“symbol”, “attribute”, "start_date", "end_date", "interval")

1. The attributes available for Historical Data are - open, close, high, low, volume, all.
2. Dates can be specified using a relative number of days from today, like "TODAY()-7" OR using the DATE function like DATE(2020,4,21) which is 21-Apr-2020 OR specifying the full date like "Apr 21, 2020"
3. Interval is either DAILY (default) or WEEKLY.

We can get 10 year back date using
=DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY()))

If you want only the price, you need to use INDEX and get only the lower right box (close price). The formula is:

=INDEX(GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7),2,2)

For only the Date, (row_num=2, col_num=1)
=INDEX(GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7),2,1)

If you want to retrieve only the prices for a period of time, we leave the row_num as BLANK
=INDEX(GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7,TODAY()),,2)

This can be helpful if you want to retrieve the MAX/MIN value over a period of time. For Ex., to retrieve the maximum close price over the past 7 days:
=MAX(INDEX(GOOGLEFINANCE("NSE:RELIANCE","close",TODAY()-7,TODAY()),,2))

C. Customized Formulae

1. 52 Week Index - Stock position w.r.t its 52 week High-Low price band:
(Current price - Low price) / (High price - Low price) -> Format as %

2. 30 day ago date, price, %change
date - INDEX(GOOGLEFINANCE(C2,"close",TODAY()-30),2,1)
price - INDEX(GOOGLEFINANCE(C2,"close",TODAY()-30),2,2)
% Change - (current-price)/price -> Format as %

3. 1 week ago date, price, %change
date - INDEX(GOOGLEFINANCE(C2,"close",TODAY()-7),2,1)
price - INDEX(GOOGLEFINANCE(C2,"close",TODAY()-7),2,2)
% Change - (current-price)/price -> Format as %

4. 30 day High-Low, Index
high - MAX(INDEX(GOOGLEFINANCE(C2,"high",TODAY()-30,TODAY()),0,2))
low - MIN(INDEX(GOOGLEFINANCE(C2,"high",TODAY()-30,TODAY()),0,2))
Index - (Current price - Low price) / (High price - Low price) -> Format as %

5. 30d Highest Close, % Change
Highest close - MAX(INDEX(GOOGLEFINANCE(C2,"close",TODAY()-30,TODAY()),0,2))
% Change - (current-max)/max -> Format as %

6. 1 week Highest Close, % Change
Highest close - MAX(INDEX(GOOGLEFINANCE(C2,"close",TODAY()-7,TODAY()),0,2))
% Change - (current-max)/max -> Format as %

7. Simple Moving Average - 20 Day, 50 Day
20Day = ROUND(AVERAGE(QUERY(GOOGLEFINANCE(C2,"close",TODAY()-35,TODAY()),"select Col2 order by Col1 desc limit 20")),2)
Let's break down the formula:
a. GOOGLEFINANCE function gets the historical data for the past 35 days
b. QUERY function sort the above data in descending (newest to oldest) order  of Col1 i.e. Date and limit the number of rows to only 20. We then pull the Col2 i.e. Close price value of the 20 records
c. AVERAGE function calculates the average of the Close price values.
d. ROUND function rounds up the average value to 2 decimal places.

Note: We took 35 days as we need 20 trading days to calculate the 20 Day SMA.

50Day = ROUND(AVERAGE(QUERY(GOOGLEFINANCE(C2,"close",TODAY()-80,TODAY()),"select Col2 order by Col1 desc limit 50")),2)

D. Tips

1. Conditional Formatting

Highlight positive % with green font and negative % with red font

Right Click F2 (%Change) > Conditional Formatting

Single Color

Apply to range=F2:F100

Format rules = Greater than or equal to 0

Style = Fill is  BLANK and Font is Green

Click Done

Add Another rule similarly,

Single Color

Apply to range=F2:F100

Format rules = Less than 0

Style = Fill is  BLANK and Font is Red

Click Done

Apply this to all the %Change cells like 30d % Change, 1w % Change, 30d Highest Close % Change, 1w Highest Close % Change

Add this Range to the existing format appending comma between ranges:

F2:F100,Y2:Y100,AB2:AB100,AG2:AG100,AI2:AI100

If 20D or 50D EMA is greater than current price, fill the SMA cell with Red color and if less than or equal to SMA, fill with Green color.

Right Click AJ2 (20 Day SMA) > Conditional Formatting

Single Color

Apply to Range=AJ2: AJ100

Format rules=Custom Formula is "=AJ2<=$E2"

Style = Fill Green

Click Done

Add another rule similarly,

Single Color

Apply to Range=AJ2: AJ100

Format rules=Custom Formula is "=AJ2>$E2"

Style = Fill Red

Click Done 

Apply this to 50 day SMA as well. Add this Range to the existing format appending comma between ranges:

AJ2:AJ100,AK2:AK100

2. Hide the not so useful columns. 

Right click on the Column C to select the entire column and select "Hide Column". Unhide by using the arrow between Column B and D

3. Freeze Pane to see the column Header and Company Name always

View > Freeze > 1 row

Select the cell F2 (% Change). View > Freeze > Upto current column (F)

4. Center all the Values in columns except for Company Name column. Make the Column Header in Bold font.

5. We can generate Line Graphs of the stock over a period of time as below:

SPARKLINE(GOOGLEFINANCE(C2, "price", TODAY()-30, TODAY()))

E. Fetching Data for multiple stocks

1. Enter the Company Symbol in Column A of the next BLANK row (say Row3). Say, HDFCBANK

2. Enter the Exchange Symbol in Column B of the next BLANK row (say Row3). Say, NSE

3. select the previous filled row from Column C till last column. say, C2 to AL2

4. COPY the values and paste it on Cell C3. The entire Row3 gets populated with values even if there are hidden columns in between

OR

Go to the last cell, AL2 and you can see a + icon. Drag that to the next row. This also populates the values.

Ensure that the Stock Symbol (Column C) is correctly populated.

Your Tracking sheet is Ready !!!

As a Bonus, you can find a sheet where you can track your Stock Portfolio. Input the following:

• Company Symbol
• Exchange Symbol
• Quantity of shares held
• Total Investment Cost (including Brokerages, Taxes,etc.)
• % Goal - % Profit you are aiming for to Sell the stock

Column Headers marked in Green are to be filled. Others will be calculated.

Also, you can find a Sheet "Calculations" which can help you to calculate the Average Buying Price of a stock if you bought different quantities at different prices.

Get a Copy of the Sheet here.