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

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. 

Update 2. Would like to thank the first time visitors from Old School Value and Bogleheads. This Stock Portfolio Tracker works for both dividend, net net or value stocks portfolio tracking in USA, Canada, UK, Singapore, Hong Kong  and many more countries not to mention tracking of ETF portfolio. Unfortunately, my IP was banned at Bogleheads (for reasons I do not know of!) and I couldnt contribute extensively there.

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 Intuits 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 its 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 dont 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 its 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.

20a%20copy.png /%

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

20history.png /%

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.

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

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. (do not fill in the units, unit cost, and all those blue cells here! those are inserted at the Transactions sheet and this stock portfolio tracker will compute all those blue cells for you. Remember blue cells are computed cells! )

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 .

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:. Read update here . My personal preference is still Yahoo Finance. They have a large number of supported exchanges.

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

20dropdown.png /%20sheet.png /%

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

The transactions are managed under the Transactions sheet.

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.

20row%20down.png /%

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

20summary%20review.png /%

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

20summary%20review.png /%

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.

This is the monthly dividend and realise gains sheet

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!

20-%20troubleshoot%20yahoo%20prices%20not%20updating%204.png /%

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.

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.

4. Use keyboard Ctrl X to cut the whole formul a

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

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?

www.gummy-stuff.org/Yahoo-data.htm

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 dont 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

Q: Yahoo Stock Quotes for Various Exchanges

To make it simple for you guys I will try to add the different stock quote examples to help you get started.  You can also see how many different exchanges are supported.

• Singapore SGX: .SI e.g. CC3.SI for Starhub
• USA:   e.g. AAPL for Apple
• Hong Kong: .HK  e.g. 0543.HK for Pacific Online
• Canada: .TO e.g. FFH.TO for Fairfax Financial Holdings
• London: .L e.g. VOD.L for Vodafone
• Germany: .F e.g. BMW.F for BMW
• Malaysia: .KL e.g. 7113.KL for Top Glove
• India cannot work. I tried both .BO and .NS and they just wouldnt work
• China Shanghai stock quotes doesnt work I tried SS and it wouldnt work

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.

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 >