Investment Moats – Stock Market Investing

Investing in the stock market

How to track your stock transactions with Google Spreadsheet

I created a Stock Portfolio Tracker Google Spreadsheet some time ago (See here). Its free and keeps track of stocks by keying in transactions. But the instructions perhaps is not detail enough.

Here I would like to offer another guide.

1. I hope you have a google account if not you can sign up for one
2. Instruction guide is here >>  Guide
3. Make a copy of my spreadsheet
   1. Log in to google account. 
   2. Go to my spreadsheet here >>  Spreadsheet
   3. Go to File > Make a Copy
   4. Now you have your own sheet.
4. This is currently my portfolio so let me explain a few things
   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
            4. Fill in the price you sell under Transacted Price (per unit)
            5. Fill in the commission or fees under Fees
            6. 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. Current Allocation Chart > 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.
   8. 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
   9. 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.
   10. Yahoo Data Ref USD > Same as (9) only for (5)
5. Enter a new stock
   1. Ok we first go to Stock Summary sheet
   2. Except for row 2 delete away rows 3 downwards so you are left with row 1 and 2 only.
   3. Every time you need to track a new stock you make a new row by duplicating the row above (in this case row 2)
   4. In our case you can edit row 2 for your first stock. Edit the fields describe under (4.3)
6. Enter a new transaction. You have specify the stock but not any buy or sell transaction. now we do this
   1. Go to Transactions sheet
   2. Except for row 2 delete away rows 3 downwards so you are left with row 1 and 2 only.
   3. Every time you want to input a new transaction just duplicate the row above (in this case row 2) and edit the content.
   4. To enter your first transaction edit row 2 with a buy transaction. Edit the fields describe under (4.4)
7. That’s it you are on your way tracking.
   1. Verify a few data to see if its correct.
      1. If you specify a Yahoo Quote, see if your Yahoo price is correct.
         1. If the price is not updating (there is a bug here), go over to Yahoo Data Ref to see if your stock quote can be found in that table
         2. If it can be found perhaps check with Yahoo site to see if the data is correct. 
         3. If its not then its due to the bug. What happens is that every time you track a new stock, somehow Yahoo Data Ref will now show this new stock.
         4. To resolve this bug, do this
            1. Go to Yahoo Data Ref sheet
            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 formula
            5. Click on a cell that is out of range like what is show 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 your Yahoo price is correctly updated. On the Yahoo Data Ref you should also see that the new stock is correctly retrieved.
            8. For pictures go to the FAQ section of this post.
         5. You will need to do this every time you buy a new unique stock. I don’t think this is a big problem
      2. Try to check to see after a few buy sell transactions the average cost is right.
   2. Some handy tricks
      1. Share: Like me you can click on Share to specify who you want to collaborate with public or private. Your wife and you can update one spreadsheet.
      2. Grab more data from Yahoo Finance: From the Yahoo Data Ref, you can see i only grab part of what Yahoo provided. You can actually grab others when you are better with it. Take a look at the definition here  http://www.gummy-stuff.org/Yahoo-data.htm
      3. Embed in your blog: Easily show others your up to date portfolio using the function under File > Publish to Web
      4. Create Charts
      5. Create Pivot Tables
8. Additional Benefits
   1. You have flexibility to track the way you want
   2. Easily view on mobile devices as you can just book mark your portfolio link
   3. Free

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

Related posts:

1. Auto Update Stock Prices from Yahoo Finance into Google Spreadsheet
2. Free Online Investment Stock Portfolio Tracker Spreadsheet
3. Google, Yahoo!, CNBC, WSJ: Seems like now it is really real time quote streaming!
4. Google Chrome: Download Google’s web browser today!
5. Watch Google IO 2011 Keynote Day 1

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Powered by Wordpress | Stock Market Investing
Copyright 2007. Investment Moats – Stock Market Investing. All rights reserved

• Home
• About SGX Dividend Stock Tracker
• Advertise
• About
• Past Stock Reports
• Resources