ValuePickr Forum

Track your portfolio using Google sheets

Google provides the facility of GOOGLE FINANCE from where you can easily pull important stock data in google sheets to track your portfolio.

I have explained how you can create and track your stock portfolio using google sheets here: http://www.tradebrains.in/track-your-stock-portfolio-in-google-sheets/

Note: This sheet is useful for small investors who are not involved in frequent trades and just want to keep a proper track of returns and dividends. The current stock price gets auto-updated as the prices are pulled from google finance.

I hope it helps. Happy Investing!

7 Likes

Do you have a sample google spread sheet.

Also will the excel formulas (arithmetic and lookup) work in google spread sheet

You can use this format: https://docs.google.com/spreadsheets/d/1u8LDPF-xliJ-HJoYBLhFG8ddPMn-AhGZWT65FATYM1Q/edit?usp=sharing It’s the basic format.

First thing that you need to do is: Go to File-> 'Make a copy’
Then you’ll be able to edit.

NOTE: I’ve applied most of the formula’s here. Just edit the cells, do not delete it; otherwise you have to re-apply the formulas. If you are not have so many stocks in your portfolio, then simply hide the rows.

Further, most of the excel formulas are applicable here.

2 Likes

Sir thanks for guidance on preparing realtime portfolio tracker by using Google Sheet and Google Finance.
I am newbie to spreadsheet or google sheets.
I wanted to know whether i can fetch data such as Daily Moving Average, Exponential Moving Average using google sheet and formula in google finance.
Thanks in advance.

All the metrics that you can obtain from google finance function can be found on below link. You’ll have to calculate moving averages yourself.

https://support.google.com/docs/answer/3093281?hl=en

Hi. You can fetch the date wise price history of a stock using this function: =GOOGLEFINANCE(“F”, “price”, DATE(2017,1,1), DATE(2017,2,11), “DAILY”) and then draw EMA or SMA.

Further, here is the link to google finance with all the attributes that you can directly fetch from google: Google finance support

I hope it helps.

1 Like

They don’t support NSE SME which is a bummer !

Thanks. So I have to make a separate sheet of DMA or EMA and then fetch value and have to feed the ema or dma value in the google sheet which contains my portfolio. So it cant be made real-time or auto.

Does anyone know how we can get NSE SME segment stock price in Google Sheets?

Would be really helpful. Thanks!

Google finance doesn’t support SME share prices yet. But there is a work around. Paste the following in the cell that you want the SME stock price. Change the URL to whatever the SME stock that you are tracking.

=IMPORTXML(“https://economictimes.indiatimes.com/ice-make-refrigeration-ltd/stocks/companyid-67222.cms","//div[@id=‘nseTradeprice’]”)

It took me a few hours to find but managed to get a similar formula for Yahoo Finance - just need to replace the Scrip Code in the link and works like a charm.

=index(IMPORTXML(“https://finance.yahoo.com/quote/WORTH-SM.NS","//*[@id=‘quote-header-info’]//span”),2)

Tried your as well - seem to be getting a parse error. Thanks for replying and sharing though!

1 Like