Free Online Investment Stock Portfolio Tracker Spreadsheet

Free Online Investment Stock Portfolio Tracker Spreadsheet stockportfoliotracker

What is this Stock Portfolio Tracker in a Nutshell?

The stock portfolio tracker is a FREE Google Spreadsheet hosted at Google that can do the following:

  • Track your stocks, shares or bonds by transactions
  • You can enter Buy, Sell, Stock Splits, Rights Issues (or Cash Calls), Cash Dividends, Stock Dividends, Gift Stocks
  • Show you each of your asset (stocks, bonds) realized returns, unrealized returns, cash dividends received
  • AUTOMATICALLY UPDATES STOCK PRICES. If the shares symbol can be found on Yahoo Finance, chances are you don’t have to key in prices yourself. Every time you login to your spreadsheet on your phone, computer or anywhere, that is very up to date prices automatically.
  • Embed your portfolio in your own website
  • Access your portfolio on your iOS, Android phones via Google Docs App

Update: Would like to Thank Everyone for the support! I created this in 2011 and have received overwhelming responses on how it managed help so many folks who is looking for a sophisticated spreadsheet that you can trust. 

Why do we need another portfolio spreadsheet? Well I have a few criteria and objectives:

As little lock in as possible

I have blogged about in the past that I use Intuit’s Quicken to budget and track monetary stuff. Well that causes money and intuit is not the only provider. Microsoft used to provide MS Money but they have stopped the support for it. Some folks may have concern with going too far with the app and difficult to switch to an alternative if another MS Money happens to their paid app.

Stock Portfolio Tracker do not have this problem because it’s stored as a spreadsheet in Google Docs in the Internet cloud. You can always download the data as an excel xls or xlsx workbook to backup locally or if u intend to port it to another platform.

Manage portfolio based on transactions

Actually I don’t want to do this portfolio tracker. I searched around the net to see if someone came up with this simple excel sheet to track my portfolio this way. I would only need to do little modification.

It turns out that most portfolio trackers track based on one stock and the number of units needs to be calculated ad hoc by the user.

Lets just say that there are quite a few investors that hold stocks for a long time. They want to review their holdings, see how much they have gain or loss, how much income in total.

This kind of portfolio should be constructed based on an aggregation of their buy, sell and div transactions.

With these transaction data, user can then construct different review perspective whether it is return on investment or different portfolio category.

Portfolio access anywhere

One problem with Quicken and local excel spreadsheet is you only can access your portfolio on your computer.

With Google Spreadsheet, your workbook is stored on the Internet. That brings advantages.

  1. You can access your portfolio from any computer with Internet access. Right now, smartphones are powerful enough to power a web browser and it’s HTML and JavaScript pages, which Google Spreadsheet runs on.
  2. It acts as a backup should your laptop or desktop gets trashed.
  3. If you would like to share the viewing and editing of the portfolio with friends, family and associates you can do that with the sharing feature.
  4. If you are a finance blogger you can append these data on your blogs.
    Part of me was thinking: how bad is Google Spreadsheet versus Excel. It turns out that they are generally very similar when it comes to formulas but those that I need they do not provide. Still you can cook up a smashing portfolio with what Google provided and they are constantly improving it.

Instructions: How to get started now to use this spreadsheet

The Google Spreadsheet can be found here [Link to spreadsheet >].

The spreadsheet you see is my portfolio of stocks listed on SGX and NYSE.

  1. To assess this Google Spreadsheet you will need a Google account. [Sign up for Google account >>]
  2. At my Google Spreadsheet page in the link above, go to the top right corner and click the “Sign In” link.
  3. Once you are signed in , make a copy of this document by doing the instructions in the image below.

Free Online Investment Stock Portfolio Tracker Spreadsheet duplicate%20a%20copy

Note: You do not have to click Share and ask for my permission. Just go to File > Make a copy… and you can create a copy for you to get started.

Once you have made a copy you would need to understand a few things.

Version history and notes

Free Online Investment Stock Portfolio Tracker Spreadsheet version%20history
The “Read This First” contains instructions on how you can make use of this spreadsheet.

It also contains notes on amendment that I constantly do to this spreadsheet.

Legend

Free Online Investment Stock Portfolio Tracker Spreadsheet legend

Yellow cells – these are cells that user will need to fill in appropriate data input.

Light blue cells – these are cells that will be computed by Stock Portfolio Tracker automatically. Do not fill in your own input here!

Drop down selection – these cells are user inputs but they are fixed values that are derived from “Ref” sheet or other cells.

Define the stocks you want to track

Free Online Investment Stock Portfolio Tracker Spreadsheet stock%20summary

Stocks are defined in the worksheet “Stock Summary“. This is where you define the stock name and symbol. Upon viewing it for the first time, you will see rows of already populated stocks. Those are mine. What you need to do is clear row 3 onwards if you are starting afresh by populating your stocks one by one.

For each row of unique stock, make a copy of row 2, and then enter the stock name, quote [optional], manual price [optional] and expected dividend yield [optional] in “Stock Summary”.

Note: The stock name is used not just in this sheet but also “Transactions”, so whenever you make a name change here do change the corresponding stock transactions tagged to this stock name.

The “Last Price” is determine by which data source is available. For this to work you will need to find out the stock quote of the stock you want to track in Google Finance or Yahoo Finance.

We provide 3 options for updating of current last done stock price.

Free Online Investment Stock Portfolio Tracker Spreadsheet quotes

Should your stock be unsupported by Google Finance, if you specify the stock symbol in Yahoo Finance, Stock Portfolio Tracker will automatically update your stock data with values from Yahoo Finance.

Should your stock belong to exchange supported by Google Finance (e.g. US exchange), entering the quote will enable Google Spreadsheet to automatically update your stock data every time you review your spreadsheet.

Update (28 Apr 2013): As of today, Singapore SGX Stock Exchange is supported. Instead of what is depicted in the picture, look up the symbol in Google Finance. They should be as “SGX:{your stock symbol}”. Read update here.

The difference between Google and Yahoo is that Yahoo supports more decimal places, Google can only go up to 2 decimal places. Yahoo takes last trade price while Google seem to take active trade price. Thus if your stock is halted for long, Google will show price 0, which may not be what you want. The upside for Google is that the data seem to be REAL TIME. So you may be able to make use of it.

If you are unable to get live prices from Google Finance or Yahoo Finance, you will need to update the last done price manually every time you open this spreadsheet to review.

The sequence is to take Yahoo Finance as a priority, then Google, then Manual. If you don’t like that sequence go to Last Price column and change the sequence.

One of the main purpose is to track dividend stocks so there is a column that yield investors can make use of to track for this stock how much yield on cost (yield on your average cost of stock purchase) you expect.

Free Online Investment Stock Portfolio Tracker Spreadsheet category%20dropdown Free Online Investment Stock Portfolio Tracker Spreadsheet ref%20sheet

Notice there is a “category” field, this is a drop down validation whose data is defined in the “Ref” sheet. It is to help an investor better segregate the stocks that he or she is tracking.  The user can change and add more category by going to that column in “Ref” to manage.

Enter your stock transactions as they take place

Free Online Investment Stock Portfolio Tracker Spreadsheet transactions%20sheet

The transactions are managed under the “Transactions” sheet.

Free Online Investment Stock Portfolio Tracker Spreadsheet row2

Note that the most important row is row 2, which is the first transaction row. Each new transaction you create is done by selecting the previous row and dragging it down one row and then amending this new row.

Note: Do Not Delete this Row 2! For your first transaction, amend the yellow cells in this row. Only input values for the yellow cells and the “Type” field.

Stock Portfolio Tracker supports 4 kinds of transaction “Type” currently: “Buy”, “Sell”, “Div” and “Split”. From the drop down select the type you are entering.

For all transactions, enter the date of transaction, select the stock by name and select the type.

For “Buy” transaction, enter the “Transacted Units”, “Transacted Price”, “Fees”. Ensure that the value in “Stock Split Ratio” is 1.0.

For “Sell” transaction, enter the “Transacted Units”, “Transacted Price”, “Fees”. Ensure that the value in “Stock Split Ratio” is 1.0.

For “Div” transaction, enter the “Transacted Units”, “Transacted Price”. Ensure that the value in “Stock Split Ratio” is 1.0 and “Fees” is 0.0.

For “Split” transaction, enter the “Stock Split Ratio”. Ensure that the value in  “Transacted Units” is 0.0, “Transacted Price” is 0.0 and “Fees” is 0.0.

The Stock Split Ratio is calculated as follows:

If it is a 4 to 2, the ratio is 2/4 = 0.5.

If it is a 1 to 5, the ratio is 5/1 = 5.0.

For Rights Issues/Cash Call, Bonus Shares, Dividend Reinvestments, please take a look at the FAQ section below to guide you.

Free Online Investment Stock Portfolio Tracker Spreadsheet drag%20row%20down

Note: At times after you insert one row below the formula will go astray. That can be easily solved by re-propagating the formula again by dragging the light blue cells in row 2 to the rest of the transactions row.

Reviewing your portfolio

Once all the data are entered whenever a transaction is made, reviewing your portfolio is easy.

Aggregate transactions in stock summary view

Free Online Investment Stock Portfolio Tracker Spreadsheet stock%20summary%20review

The “Stock Summary” provides you with an aggregate view of your realize and unrealized gains or losses. You will also be able to see your average cost of current shares for each stock.

If you learn from my formulas you can insert more columns possibly to calculate XIRR or total gains or losses.

Aggregate Stocks in multiple portfolio in Portfolio View

Free Online Investment Stock Portfolio Tracker Spreadsheet portfolio%20summary%20review

The “Portfolio Summary” sheet provides more aggregation. This is an aggregation of “Stock Summary” and “Stock Summary USD”

I did not fully develop this at the time of writing because different investors track different set of information. Should you be comfortable with it you can modify this to make it more powerful.

Dividend Collected, Realised Gains and Losses by Months and Years

We also provide 2 worksheets so that as bloggers you can easily tell your readers how much dividends you collected each month and how much gains or losses you realise.

Free Online Investment Stock Portfolio Tracker Spreadsheet monthly%20aggregate

This is the monthly dividend and realise gains sheet

Free Online Investment Stock Portfolio Tracker Spreadsheet yearly%20aggregate

This aggregates dividends and gains on an annual basis

FAQ

Q: I created a new stock / asset in stock summary with a valid Yahoo stock quote but the price does not seem to be updating!

Free Online Investment Stock Portfolio Tracker Spreadsheet faq%20 %20troubleshoot%20yahoo%20prices%20not%20updating%204

This happens to be a strange bug that i cannot get rid of and i have to apologize for not being able to solved this. That said, the work around solution is very simple.

The reason the price shows wrongly is because the Stock Portfolio Tracker fails to grab the last price from Yahoo. Follow the instructions below to refresh any new stock added.

This means that every time you add a stock such as Intel that you have not keep track of previously, you do this. Once you are keeping track of it, you do not have to do this.

Free Online Investment Stock Portfolio Tracker Spreadsheet faq%20 %20troubleshoot%20yahoo%20prices%20not%20updating%201

1. Go to the worksheet “Yahoo Data Ref“. This is where the data is retrieved.

2. Click on cell A2.

3. At the top formula bar (fx), click and use Ctrl A on your keyboard to select the whole formula string.

Free Online Investment Stock Portfolio Tracker Spreadsheet faq%20 %20troubleshoot%20yahoo%20prices%20not%20updating%202

4. Use keyboard Ctrl X to cut the whole formula

5. Click on a cell that is out of range like what is shown in the picture above. This should make the rest of the cell computation disappear

Free Online Investment Stock Portfolio Tracker Spreadsheet faq%20 %20troubleshoot%20yahoo%20prices%20not%20updating%203

6. Now click on cell A2 again. Use Ctrl V to put back the formula you cut away just now.

7. Now check your Stock Summary that the last price is updated. On the Yahoo Data Ref, you should also see that the new stock is correctly retrieved.

Q: I am still confuse by the terms use in the headers of each field in each sheet could you make it simpler for me?

  1. Read This First > Basic version history and introduction
  2. Portfolio Summary > You do not need to edit this. This pulls data from the other sheets to show an aggregate portfolio view. Once you get the hang of it you can edit to put more things you want to monitor here
  3. Stock Summary > This is where you will define your stock e.g. General Electric, Macdonalds, Total SA
    1. As in all places, cells in yellow means you need to input things, cells in light blue or colored generally means it is computed by the spreadsheet.
    2. Category > this drop down specifies which category this stock is under. You can increase the drop down in the Ref sheet.
    3. Stock Name > The name of the stock. Note that this will impact the dropdown in the Transactions sheet, and how the stocks transaction tracks back to this stock in this sheet
    4. Google Quote > This is where you will specify your google stock quote. Go to google finance and find the stock quote and enter here e.g. TEF for telefonica, VXX for Vix ETF
    5. Yahoo Quote > Similar to (4) but only this one is the quote from Yahoo. Actually Google and Yahoo’s quote look pretty similar if you look at my sheet!
    6. Manual Price > If for some unfortunate reasons your stock cannot be found on these 2 platform you can enter it manually
    7. Last DPU > For dividend stocks you may want to track the annual dividend payout. Here you can manually enter it.
    8. This is all you need to enter. The rest is auto computed. Note the Last Price use will be in this order: If you enter a Google quote it will use the Google Quote then follow by Yahoo Quote then follow by Manual Quote
  4. Transactions > This is your transactions of the stock in your Stock Summary. You will input buy, sell, div and splits here
    1. As in all places, cells in yellow means you need to input things, cells in light blue or colored generally means it is computed by the spreadsheet.
    2. Date > the date of the transaction
    3. Stock > the name of the stock. select the name of the stock from the drop down. if your stock is not seen here it means that you have not specify it under Stock Summary.
    4. Type > what kind of transaction. currently you can choose the drop down with the following
      1. Buy > an accumulation of that stock
        1. Fill in the amount of stocks you bought under transacted units
        2. Fill in the price you buy under Transacted Price (per unit)
        3. Fill in the commission or fees under Fees
        4. Keep the Stock Split Ratio as 1.0
      2. Sell > a distribution of that stock
        1. You have to do a check to ensure you don’t sell more until your total units left is less than zero. My sheet does not take care of that.
        2. Fill in the amount of stocks you sold under transacted units
        3. Fill in the price you sell under Transacted Price (per unit)
        4. Fill in the commission or fees under Fees
        5. Keep the Stock Split Ratio as 1.0
      3. Div > a cash dividend distribution
        1. Note: ensure that transactions to be in chronological order. for dividends use the ex-dividend date for the transaction instead of the date you get paid.
        2. Fill in the amount of stocks under consideration for cash dividends under Transacted units.(for reference you can take a look at Previous Units which shows the last amount of units you have for this stock.)
        3. Fill in the dividend per share under Transacted Price (per unit).
        4. Leave Fees as 0 unless you get charged for dividends
        5. Keep the Stock Split Ratio as 1.0
      4. Split > there is a stock split or a reverse stock split
        1. Keep Transacted Units at 0.
        2. Keep Transacted Price (per unit) at 0.
        3. Keep Fees at 0.
        4. Enter your split ratio under Stock Split Ratio. If its a 1 to 5 split the ratio is 5.0. If its a 5 to 1 consolidation the ratio is 0.2
  5. Stock Summary USD > When i come up with this spreadsheet i only have in mind local currency which to me is SGD. So i have a sepearte one that is similar to (3) here.
  6. Transactions USD > This is the transactions to Stock Summary USD.
  7. Mthly > This sheet splits Transactions and Transactions USD into total dividends and gains/losses collected by months.
  8. Yrly >This sheet splits Transactions and Transactions USD into total dividends and gains/losses collected by year.
  9. Allocation > Just a chart showing a pie of how much each stock i have. Once you are familiar with google spreadsheet you can create many of these to show to your readers.
  10. Ref > This sheet contains data found in the drop downs in the other sheets.
    1. Stock Category is some categorization that i set for my stocks. You can change them next time when you get comfortable.
    2. REIT Category – ignore this
    3. Price Tolerance – some warning indicator under Stock Summary to change the color of cells should it breach a certain level
    4. USDTOSGD - this you may find useful if you are new to Google Spreadsheet. I use this to convert USD to SGD via a formula in Google Finance. This will be used under Portfolio Summary to convert US portfolio to SGD portfolio
  11. Yahoo Data Ref > Here is where we get the data for the stocks under Stock Summary from Yahoo Finance.
    1. Do this right and your data from yahoo finance auto updates.
    2. There is only one cell that you need to edit here which is the cell A2. Take a look at it. It basically aggregates all the stock quotes in Stock Summary and then asks to return it in a CSV file and then display it in the cells below.
    3. You can request for more things. for more information take a look at http://www.gummy-stuff.org/Yahoo-data.htm
    4. There is a bug here but it is not something big. i will tell you about it below.
  12. Yahoo Data Ref USD > Same as (9) only for (5)

Q: How do I input Rights Issues or Cash Calls?

Rights Issues are basically company asking for more money from you to buy more shares. They make it attractive for you to buy at a lower price.

So take for example First REIT has a rights issue. Current price is $0.90 and they issue a  1 for 2 rights at $0.70. You currently have 2000 shares.

So a 1 for 2 rights means that you can purchase 1000 shares at $0.70. On top of that you can attempt to purchase excess rights (rights other investors don’t want). Lets say you can purchase another 2000 excess shares making your total 3000 @ $0.70.

Your input to Stock Portfolio Tracker will be a Buy transaction with a quantity of 3000 shares at a transacted price of $0.70 with zero commission.

Q: How do I input Bonus Shares?

Bonus shares are company paying you shares instead of cash. Take the example of Challenger Technologies issuing a 1 FOR 2 bonus shares. This means if you have 2000 Challenger shares you get 1000 (crazy good deal!)

Bonus shares on my Stock Portfolio Tracker is actually buying the stocks at ZERO transacted price.

You input a Buy transaction with a quantity of 1000 shares at a transacted price of ZERO with ZERO commission

Q: How do I input Dividend Reinvestments or DRIP?

Some companies like Aims AMP, Exxon Mobil have reinvestment plans. They are basically like bonus shares. Instead of the company taking that action, the share holder (you) choose to get paid by shares.

So in my Stock Portfolio Tracker it is basically handled like Bonus Shares.

Determine how much shares you will get from Reinvestment statements and input a Buy transaction with a quantity of shares at a transacted price of Zero with Zero commission

Conclusion

Must say that I thoroughly enjoyed this experience while creating this spreadsheet. I hope I am able to help someone who was having the same problem as myself.

Take a look at the spreadsheet, make a copy and modify it to suit your needs.

Free Online Investment Stock Portfolio Tracker Spreadsheet stockportfoliotracker

For those interested in tracking my most current holdings, you can review my portfolio over here. Since I am updating this together with my Quicken (yes still using that)

I hope you can make use of this tracker to help you fulfil your financial dreams. This spreadsheet is free, however should you want to contribute to my efforts in developing this tracker into something even better you can donate to me here! Else you may want to Like and Google Plus my site at the side panel!

For your FREE Dividend Stock Tracker you can review them here!

Want to read the best articles on Investment Moats? You can read them here >

Free Online Investment Stock Portfolio Tracker Spreadsheet facebook Free Online Investment Stock Portfolio Tracker Spreadsheet email Free Online Investment Stock Portfolio Tracker Spreadsheet google Free Online Investment Stock Portfolio Tracker Spreadsheet linkedin Free Online Investment Stock Portfolio Tracker Spreadsheet twitter Free Online Investment Stock Portfolio Tracker Spreadsheet reddit
  • Colin

    Hi,

    Thanks for sharing the amazing spread sheet! Am having some issues with “Yahoo Data Ref” picking up the relevant quotes and its showing a “#value!” in the cells. Cell A2 is now showing “=arrayformula(ImportData(ʺhttp://finance.yahoo.com/d/quotes.csv?s=ʺ&LEFT(CONCATenate(‘Stock Summary’!F2:F),LEN(CONCATenate(‘Stock Summary’!F2:F))-1)&ʺ&f=snk2l1jkm3m4ʺ))” The Yahoo prices are not reflecting the relevant data, as a result of this. How can I go about resolving this?

    Thank you

  • http://www.productiveorganizer.com/ Kyith

    if you higlight the whole formula, CUT it. then press Enter, go up to that cell again, paste the formula back. does it work?

  • plrisa

    Thank you, Kyith, for so selflessly sharing this. I’ve tried this before in 2012 but had some problem and just picked it up again. I signed in with my email address but it did not auto update the prices. How do I update them? Thanks.

  • http://www.productiveorganizer.com/ Kyith

    Hmm, which country stocks are you using

  • Colin

    It doesn’t work, and the cell shows an error “The data cannot be verified. Pls check the URL” Any idea how to resolve this?

  • http://www.productiveorganizer.com/ Kyith

    Perhaps you can share your spreadsheet with me kyith.place@gmail.com and i can help you see whats wrong.

  • MSP

    Thanks for the great spreadsheet. I was just wondering how everyone handles new cash coming into the account for investing. Would this be considered buying cash or selling?

  • http://www.productiveorganizer.com/ Kyith

    hi there, usually that will constitute a new buy. but to be honest it is tedious to implemetn. its like everytime you buy something its a sell on cash and then buy on the stock

  • MSP

    I guess simplest thing is to add it manually in portfolio summary section to account for cash on hand.

  • http://www.productiveorganizer.com/ Kyith

    yes thats right!

  • MSP

    As I am new to this I had another question. Do you have a way to track annualized or quarterly returns both realized and unrealized.
    I have also modified the template a little to differentiate interest income vs dividends as tax implications are a little different here in Canada. Not sure how to capture this in yearly total.

    Thanks again

  • http://www.productiveorganizer.com/ Kyith

    hi there, i seldom encounter this in my neck of the woods. sorry about the annaulized and quarterly portion. i dont think i have a way to do that currently (might research). do you mean some stocks are taxed and some are not?

  • MSP

    We have tax on interest that is taxes like income. Local Dividends are taxed according to a formula and are taxed at a lower rate. Capital gains on sold stocks are taxed at half the rate of interest income. There are also withholding taxes for US stocks that apply in taxable accounts but not sheltered accounts. It get complicated.

    Would be helpful to track some of the differences.

    Annualized or quarterly returns are a separate thing. I was hoping to track all in one database rather than implement another system.

  • http://www.productiveorganizer.com/ Kyith

    shucks, i think my spreadsheet doesn’t handle that, we might need to add additional columns for pre and post taxes, perhaps u need to calculate manually. i know US stocks have 30% withholding tax but my dividends are usually taken into account net of that.

  • Pingback: which application you all are using to monitor stocks? - www.hardwarezone.com.sg

  • Pingback: How do you guys track ur portfolio performance? - Page 2 - www.hardwarezone.com.sg

  • Pingback: money management software? - www.hardwarezone.com.sg