Create a stock portfolio with Google Spreadsheets

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.

15 thoughts on “Create a stock portfolio with Google Spreadsheets”

  1. hi, the formula doesnt work wid me. =GoogleFinance(“Stock code”, “Price”) i am talkn abt dis formula. even after i entered stock code its showing error. kindly help

    1. 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!

    1. 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.

  2. 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.

    1. 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.

  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *