Create a stock portfolio with Google Spreadsheets

by Manshu on September 26, 2011

in Investments

I have discussed several stock portfolio tools here, and most of them give you a lot of options, and help you maintain your data the way you want. However, there are always some things that you wish the tool had that it doesn’t and it sometimes forces you to maintain Excel spreadsheets of your own.

The number one issue that I have had using Excel is that I couldn’t build one that auto – updated the stock quotes when I opened it. I know there are ways to do it, but I was never successful in it. I don’t mind entering the initial data there myself because I don’t have many transactions, but if the prices don’t update automatically then it becomes pretty much useless.

That’s why I created several Excel portfolio tools over the years, but never really used them frequently.

But I recently checked out Google Spreadsheets, and the feature of automatically updating stock quotes there is simply amazing. It’s very easy to use, and it works for Indian stocks as well.

I have a basic portfolio setup there now, and I think I’m going to use this for a long time to come.

You need a Google account to get started, which I’m sure everyone does, and then you need to go to Google Docs, and open up a new spreadsheet.

You can then setup your columns any way you want – but you would probably want these at the minimum:

  1. Name
  2. Quantity
  3. Cost
  4. Market Price
  5. Value at cost
  6. Market Value
  7. Profit / Loss

The one that I set up looked like this.

Google Spreadsheet Portfolio

Google Spreadsheet Portfolio

The most important thing in this is the market price column – which is the column that has the latest market price of the stock you want to track, and you can see that highlighted in the blue.

The red ellipse shows you the formula that you need to input in the cell to get the market price.

It is simply:

=GoogleFinance(“Stock code”, “Price”)

You can find the stock code from Google Finance by typing the name of the share in the search bar, and Google will return you a list of matching names along with their codes. You can then use that code in this spreadsheet to get the price.

I’m sure this has been around for some time now, but I didn’t discover it until yesterday and I was really happy to see how easy it was to setup.

I’m going to make a lot of use of this going forward, and I hope you find it useful as well.

{ 11 comments… read them below or add one }

Swaroop Rao September 26, 2011 at 9:13 am

This is uber-cool! Thanks, I’ve already started trying this out. I was looking at the Google Docs documentation to understand the list of attributes that the GoogleFinance function will accept (like ‘Price’). I found that it also accepts ‘Name’, but it is not documented. So, you can also use the GoogleFinance function to retrieve the company name from the ticker symbol.

Reply

Manshu September 26, 2011 at 8:23 pm

Oh really? That’s awesome – and to think that I was hoping they build in that feature sometime in the future! lol

Reply

Ayush Kumar Jain September 26, 2011 at 9:13 am

Hi Manshu,

Thanks – this is so simple but does an effective job.

A query – Is it possible to do something like for MFs also ? Since MFs are now supposed to be available through stock brokers (recall something about a MFSS platform), can we get valuation for MF portfolio in similar way ?

I guess the flipside for all of these is the privacy/security angle, just like with the money manager kind of software.

Reply

Manshu September 26, 2011 at 8:25 pm

Hi Ayush – It doesn’t allow you to enter MF data, not yet anyway.

Also, there is no security threat since it’s not connected to your broker or trading account in any way.

There may be a privacy issue if that bothers you but I can’t see a security threat.

Reply

austere September 26, 2011 at 11:37 am

I let walletwatch and moneycontrol handle this for me…
*sheepish*

Reply

Manshu September 26, 2011 at 8:26 pm

I’ve used Moneycontrol for the longest time, but slowly moved on to Google Finance in the last couple of years. I think as far as features are concerned – MC has the most – but as a result it’s not as clean as it used to be a few years ago.

Reply

Ams September 26, 2011 at 5:57 pm

Amazing stuff Manshu. I did not know you could have this.
Thanks a lot for sharing

Reply

Manshu September 26, 2011 at 8:31 pm

It’s amazing how easy this is and that makes all the difference. When I first saw it I thought I was missing something. Was delighted when it worked the first time!

Reply

Financial Planning India - TheWealthWisher.com September 27, 2011 at 1:18 pm

Gosh, even I am missing something, did not know this could be so simple. And this is where I go bonkers.
Which tool to use ? One that is the best or simple ? One usually does not get a tool which is both simple and best πŸ™‚

Reply

piyush modi September 28, 2011 at 5:53 am

well thrs an even simpler way.. instead of doing it in google spreadsheet you could use google finance’s portfolio tool

http://www.google.com/finance/portfolio?action=view&pid=1

it has some quirks but does pretty well.. from thr one can download data into excel i believe..

Reply

Narayana October 6, 2011 at 11:31 am

I would like to try it out…

Reply

Cancel reply

Leave a Comment

Previous post:

Next post: