Track your portfolio using Google sheets

Investing Strategies Tracking Fund Manager Investments
1

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!

8 Likes
2

Do you have a sample google spread sheet.

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

3

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.

3 Likes
4

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.

5

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

1 Like
6

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.

2 Likes
7

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

8

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.

10

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

Would be really helpful. Thanks!

11

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’]”)

3 Likes
12

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!

2 Likes
13

Do we have a way to pull / publish list of tickers using GOOGLEFINANCE option in google sheets based on certain criteria e.g.

  • All tickers trading above certain price (say 500)
  • All tickers weekly return above 5%
  • All tickers trading above their 50 DMA
  • All tickers with mcap >500
14

I used the below snippet and it worked for me:

=QUERY(FLATTEN(IMPORTXML(“https://economictimes.indiatimes.com/mangalam-worldwide-ltd/stocks/companyid-2046000.cms","//span[@class=‘ltp’]”)), “limit 1”)

1 Like
15

I tried copy pasting this formula but it doesnt work

16

keep getting the parse error

17

Below worked for me - Do try by taking the url for your scrip from investing.com… Sample below for another scrip…

=IMPORTXML(“https://www.investing.com/equities/suzlon-energy","//div[@data-test=‘instrument-header-details’]/div[1]/div[1]”)

18

I tried this and it worked: =QUERY(FLATTEN(IMPORTXML(“https://economictimes.indiatimes.com/mangalam-worldwide-ltd/stocks/companyid-2046000.cms","//span[@class=‘ltp’] ”)), “limit 1”)

Thanks for sharing this @sairaghav

19

It’s not working, can you upload an excel sheet with the formula.
Thanks

20

Here’s the link to the Google sheet, I have enabled access to anyone with the link:

Since this is a Google doc, when I download it, the MS Excel file auto-converts the formulas.

1 Like