Auto-updated Equity Stocks Portfolio in Google Sheets

Although I also tried to search the web to get a 'ready-to-use'  tool to manage an already existing 'Static' investment portfolio that is being managed in spreadsheets, but it ended-up with a discrete set of resources with no exact solution. Among several options, It is interesting to see online portfolios services provides by  some well known financial portals, that just meet the basic requirements if you don't have an online trading account. They have a almost similar dashboard showing standard parameters including Holdings statement, Actual cost, Current Value, LTP, %change and Profit/Loss status etc. However, It is an integral feature at most online trading platforms in India, with Buy or Sell security as additional function at  same dashboard.  It doesn't make a sense to have an just the same portfolio somewhere else as well.

       Other alternatives includes complex macros-enabled excel sheet, built-in VBA scripts to import/fetch live quotes data from leading  financial portals. However, They offer a limited customisation as well as frequent updates at source portal breaks the link with spreadsheet. It requires a latest updated version of script to adapt changes. Average user doesn't have skills to edit these scripts and have to wait for the updated version.

To sum it up, I couldn't find a better alternative to 'Static' portfolios spreadsheets. There is no doubts that spreadsheets are the best tool to manage tabular data when it comes to ease of use, flexibility and end-user customisation. But It is too boring, cumbersome to update every single bit of price quotes and financials, every time before doing an assessment and portfolio analysis to make a fair investment decision.

Using google sheets,  I have finally migrated to a better, dynamic and interactive investment portfolio that features a simple, yet effective way to get live quotes for both NSE/BSE stocks. 

Why to make a portfolio with Google Sheets ??

It is the simplest way!!!

Complex scripts are required to fetch live quotes in excel and other spreadsheet programs. Google sheets has a unique function [=googlefinance] to with a list of pre-defined attribute parameters to directly get the live quotes from finance.google.com.  Google scripts editor provides an additional interface to create custom functions at advanced levels.

Anybody with basic excel skills can easily customise or even start from scratch for one's own portfolio !!

Pre-Requisites : 

• Create a new blank google sheet.
• Capture Security Code or NSE symbols.

Security code or NSE stock symbol is the key is must have requirement to get live quotes.  
Here are the ways, how can you get it !!

However, if you need bulk stocks in a specific index, it is better to directly copy from  NSE or BSE website.

NSE stocks :

https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm

BSE stocks :

https://www.bseindia.com/markets/equity/EQReports/MarketWatch.aspx?expandable=2

Auto-update for mutual funds NAVs is a bit more tricky as google finance is not working for it. So, this blog is just limited to stocks portfolio.

• Create column structure for equity portfolio 

Sample portfolio structure is shown below, however it is relatively easy to customise it for your requirements.

Sample equity portfolio - Download it Here

• Syntax for [google finance] function to fetch Live quotes . . .

After having security codes and symbols, now its time to use them to fetch live quotes. I am using [TCS] for NSE quotes in below examples.

Stock Name 




=googlefinance("NSE:TCS", "Name")




Trade Time 







=googlefinance("NSE:TCS", "tradetime")




Last Trade Price 

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



% Change 

=googlefinance("NSE:TCS", "changepct")

52w High 

=googlefinance("NSE:TCS", "high52")

52w Low

=googlefinance("NSE:TCS", "low52")

Market Cap.

=(googlefinance("NSE:TCS", "marketcap"))/10^7

Earning per Share

=googlefinance("NSE:TCS", "eps")

if you are interested to see a complete list of attributes, must see its Support page .

Download Sample equity portfolio spreadsheet in Google Sheets !!

• Equity portfolio has built-in script to add  security  step-by-step. 
• However, first 80 rows are already populated with formulae. 
• Just copy-paste the values in exchange [Column 'A'] and security code in  [[Column 'B']. That's it !!