The Best Free Stock Portfolio Tracking Spreadsheet

January 18th, 2012 | Comments (32)

A stock portfolio tracker using Google Docs with advanced functions than your average tracker. Get it for yourself.


Written by

Jae Jun

follow me on

Facebook

Twitter

Stock Portfolio Tracking Spreadsheet

Stock Portfolio Tracking Spreadsheet

Measure it – Stock Portfolio Tracking Spreadsheet | Flickr: Pink Sherbet 

A project that I’ve always had, was to improve on my stock portfolio tracking spreadsheets.

During this time, I’ve probably used 10 or so different portfolio trackers, but nothing met my needs. I don’t do complicated transactions, but still, nothing could really satisfy me.

But those four long years have passed, and I believe I finally have a version that will serve my needs and fulfill its purpose for a long time.

Main Needs from a Portfolio Spreadsheet

  • Enter transactions into a single column without splitting up different transactions
  • Spreadsheet should be able to automatically update how many shares I’m holding for any company
  • Account for dividends
  • Account for splits

I don’t do options so I have no need for such transactions. Don’t see why it would be hard for you to edit though.

Previous Versions

The original portfolio spreadsheet could only factor in simple buy and sell transactions and had to separate it.

Then a Google spreadsheet version was released which made it easier to track but not being able to automatically update the cumulative number of shares held for each position, made keeping track difficult.

The improved portfolio spreadsheet even had an interactive time line to track the portfolio growth but it never really took off. Too much hassle of having to update the portfolio values regularly. Also bad to look at your total portfolio that often as well.

Now this new stock portfolio tracking spreadsheet blows it all out of the water. It doesn’t solve everything but it does most of what I need.

New Stock Portfolio Tracker Spreadsheet

Full credit goes to Investment Moats for his amazing spreadsheet. See it in action.

The creator is a Singaporean investor and it will work right away with what he has, but I made some edits to tailor it for the US exchanges (including pink sheets, OTC and ADR’s) and to clean it up a little.

Enjoy what you're reading? Don't risk missing out.
Get more tips and investing strategies in your inbox. I'll send you 9 FREE Investing Spreadsheets as a welcome bonus.

So you have two options. Use the original or use my edited version.

How to Use and Get a Copy for Yourself

Full details and instructions can be found on Investment Moats’ website. Read the “Read me section” in the spreadsheet as well.

How to Edit the Charts

I’ve added three charts to the summary tab. For every new position, you will have to edit the ranges for it to be reflected in the graphs. I will show you how here.

There is a tab called “ChartsData” which holds and sorts the data for the graphs by market value. No need to change anything in the “ChartsData” tab. This tab is only used to display and sort data. Do not enter anything in this section.

Below is one of the graphs/tables in the Summary section.

Click on the table once and a “Chart” menu will appear. Click that and in the menu you can edit the chart.

Select Edit Chart to bring up the chart editor.

The data range is: ChartData!B1:B11, ChartData!D1:D11, ChartData!M1:N11, ChartData!Q1:R11

Columns B, D, M, N, Q and R are used in the table with values from row 1 to 11. In the spreadsheet, I only have 10 holding positions where row 1 is the table headings and the data is contained within row 2 to row 11.

E.g. if you have 20 current holdings and need to update the graph, the data ranges would be

ChartData!B1:B21, ChartData!D1:D21, ChartData!M1:N21, ChartData!Q1:R21

To see the ranges in more detail, click the grid icon next to the range values to bring up this window.

You can add more detail to your table by adding another range.

Press ok and save.

The other two graphs you will need to update include a pie chart displaying sizing and gain/loss.

Another Portfolio Tracker made in Java

Maybe you don’t like the idea of storing your information on the cloud.

Then as an alternative, here is a free, lite, portable and java portfolio tracker that you can use. Again thanks to Investment Moats for bringing it to my attention.

You need to have Java installed on your computer for this to run though. It is still in beta stage so expect bugs and limited documentation but a very good freebie.

Import feature doesn’t work too well so if you have hundreds of transactions to enter, it will take you a long time. But still, better than most.

Truly the Best Stock Portfolio Tracker

After plugging in all my historical transactions, all I can say is that I’m hooked with this version. It has made tracking so much easier. I will continue to add good features to this Stock Portfolio Tracking Spreadsheet when it becomes a need, but until then, enjoy.

Don’t forget to Like, share or tweet it if you like this.

About Jae Jun


Jae Jun is the founder of Old School Value. He is on a mission to provide practical and actionable value investing tools, tutorials and educational material to help empower the individual investor. Keep in touch with Jae via any of the methods linked below.

Get Tips and Strategies to Achieve Higher Returns
Bonus: Get 9 FREE Investing Spreadsheet Calculators
  • http://nzhedge.wordpress.com konrad

    This looks great – thanks.
    I can’t seem to make a copy of the spreadsheet. the only available options under the File tab are share, download as, and print.

  • http://www.oldschoolvalue.com Jae Jun

    log into your google docs account

  • brYan

    Thank you Jae and Investment Moats for sharing. I was already using Google Docs to track portfolios but your versions are better than mine.

  • Felix

    Any thoughts of converting this into an Excel spreadsheet with SMF_ADDIN

  • http://www.oldschoolvalue.com Jae Jun

    Yes I did think about that. I wouldn’t need the SMF add in for that though because mainly only the price quote is needed. Next time though.

  • Mariano

    A friend recommended me http://www.impok.com. It is a Spanish website, but its portfolio tool is really good. It is simple to use, but it gives good information about the risk and return. The problem is they still do not cover the US market so they do not have US Mutual Funds. I wrote them and they told me that they are considering the US market.

  • http://www.evanbleker.com Evan

    Thanks a lot! Tracking is an issue I have wanted to fix for a while.

    How would I go ahead and add Canadian stock data to the spreadsheet?

  • http://www.oldschoolvalue.com Jae Jun

    You would just enter the Canadian index and ticker I would think. e.g. TSE:DSA or something like that.

  • Abe

    I have tried to download the spreadsheet but was unsuccessful. I went Google Docs and when I copy I end up copying the Stock Portfolio Tracker which is not the spreadsheet showing stocks. It is the one that refers to the instructions and the revision information. How do I download the actual stock spreadsheet?

  • http://www.oldschoolvalue.com Jae Jun

    @ Abe,
    You have to “copy as” to your own google account instead of trying to download it. This is not a version that you can download. This is an online version.

  • http://rwinvesting.blogspot.com DIY Investor

    I need some help here. Why spend your retirement doing all this spreadsheet stuff when Schwab and others do it all for you. All you do is the trade. And Schwab and possibly others as well calculate total return and compare it to a benchmark.

  • http://www.oldschoolvalue.com Jae Jun

    This spreadsheet tracks every purchase and sale. Much more powerful and detailed than any portfolio management feature from a broker which I find to be very lacking.

  • scheye

    Is it possible to add a chart for time comparison of your portfolio market value and compare it to SPY?

  • Raj

    I made a copy of this spreadsheet in my google docs. However, the link on how to use this spreadsheet has a problem and I cannot seem to get access to it – says 403-forbidden. Any idea why? Are you still using this cloud document or have you converted this into a downloadable spreadsheet?

  • http://www.oldschoolvalue.com Jae Jun

    Try again. Working fine.

  • Dennis Goodwin

    To really and effectively make a stock tracker effective, it would be nice to show what the ANNUALIZED return is of the stock and the entire portfolio as a whole, taking into consideration dividends, partial sales, cash deposits to the portfolio and of course cash withdrawls either from stock sales and/or cash dividend withdrawls.
    We all project what annual return is needed to survive in retirement for example, but it get complicated when we withdraw and add cash to a porfolio.
    Can you do this??

    Dennis Goodwin

  • Manuel Crescini

    First time I tried to use google spreadsheet(the copy)for tracking my stocks portfolio. I like this but my first problem is assessing and using google get data and it seems I had a hard time getting it right. My data entry seems doing nothing. Please advise.

    Manny

  • Richard

    I appreciate the shared spreadsheet. In the Transactions Tab Cell K3 showing “Error. No Previous Units” which i assume relates to Cell H2 which is defaulted to 0.0 because H2 is the first Previous Unit cell. I have replaced the spread sheet twice in case it was a bug issue but the problem is still there.

    Please advise
    Richard
    Sydney, AUS

  • http://www.oldschoolvalue.com Jae Jun

    @ Dennis,
    That’s going to be awfully. Probably best suited to get a professional software for a job like that.

    @ Manuel,
    Not sure what you mean. Please clarify.

    @ Richard,
    Did you change anything in the formula because it doesn’t depend on H2 at all. It just looks through the list to see whether the ticker appears before and then retrieves a value if it does.

    So you still get an error even with the default version that I have?

  • Martin Murphy

    Jae,
    I just happened upon your site last week, it has a lot of great information that I am still digesting. I happened upon this Portfolio spreadsheet this morning. However when I open the “shared spreadsheet” in my google docs and try to save a copy, the only option available on the sheet is “view only” on my browser file tab, there is a “save as…” if I select that, it opens a save a webpage window, and selects the “save as type” box to “Web archive, single file(*.mht) what am I doing wrong?

    Thanks

  • http://www.oldschoolvalue.com Jae Jun

    Hey Martin.
    You need to be logged into your google docs account. You will then see the option to “make a copy” under File.

  • Martin Murphy

    Thanks Jae,
    Everything works perfectly now.

    -Martin

  • http://PortfolioSlicer.com Portfolio Slicer

    Well, I created advanced portfolio tracking system using Excel and latest Microsoft technology – PowerPivot (free add-in for Excel). To be honest not all of your requirements are met, but I am still working on making dividends and splits downloaded automatically. But check reports that already included – new technology lests me get portfolio value for any point in time so I can do very advanced reports. I am also tracking cash balance. In my case I was able to match bank statements and my Excel workbook penny-for-penny for any date I select. Please take a look and consider it using for your investment tracking: http://PortfolioSlicer.com.

  • Lou chen

    @mariano…í´ve tried impok.com…it is fun, the portfolio tool is very good and simple to use…much better than excel, which for me is far too complex…and they have an iphone app that does the work. As for the rest, the community they have looks fun, although it is all in spanish and I do not understand what they talk about.

  • http://none Makinmoney24

    how do you enter dividends

  • Pingback: Investment Portfolio Spreadsheet

  • JT

    Jae, great spreadsheet. Any worries about Google’s limitations on number of cells (400K total)?
    https://support.google.com/docs/bin/answer.py?hl=en&answer=2505921

    I guess it probably shouldn’t be a problem unless you’re an active trader.

  • http://www.oldschoolvalue.com Jae Jun

    I’ve never used 400k cells so that hasn’t been an issue. Otherwise you can just create another copy and rename it part 2 or something.

  • Pingback: Neil Olshey didn't follow a traditional path to the top, but made smart career … - A Free Online Guide for Valuable Investment Information - Investor-Guide.net

  • http://www.productiveorganizer.com Kyith

    HI Jae Jun, thanks for highlighting my spreadsheet. I hope you guys find it useful here.

  • http://www.facebook.com/williamlouislee William Lee

    Hi your google docs spreadsheet is great. I saved it to my google docs account and then saved as a spreadsheet in excel format because I don’t want my boss to see me surfing the net at work and we use excel. What is the excel equivalent to this formula that you have in the Transactions_OSV tab in cell k2 ie how do I convert this to excel formula

    |=iferror(if(row()2,INDEX(arrayformula(filter($N1:$N$2,$C1:$C$2″”,row($C1:$C$2)=max(if($C1:$C$2=C2,row($C1:$C$2),0)))) ;1),0),0)

    please let me know as everything else appears to work (in terms of excel formulae).

  • http://www.oldschoolvalue.com/ Old School Value

    The arrayformula function does not exist in excel.
    I’ve been meaning to create an excel version but didn’t get around to doing it.

    Instead of using the arrayformula, you’ll have to use a much more complicated formula to get it working in excel which I haven’t figured out myself.

    Been meaning try and convert it to an excel version but just forgot about it all.

x
Receive Email Tips and Strategies on Achieving Higher Stock Returns

Bonus: FREE Set of 9 Investing Spreadsheets