How to track your stock transactions with Google Spreadsheet | Investment Moats Skip to Content

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
          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. 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
  9. Want to read the best articles on Investment Moats? You canread them here >

QQQ:Which Direction Then?
← Previous
Adampak: What does SSD replacing Magnetic Hard Disks mean to them
Next →

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Younus

Sunday 16th of August 2020

Hi there Recently I found your sheet by google search and I am using it to track my portfolio. First of all thank you for the great work It helps a lot for small investors like me. I have in issue with the transactions sheets when I sell the stock it does not apply the FIFO (First in First Out) method and due to this the cost value and stock average price comes differently. Can you please help on this

Kyith

Friday 21st of August 2020

Hi Younus, unfortunately, the way I do my spreadsheet is to account for the cost on average. It is not based on first in first out. Currently, I do not know the logic to do it first in first out.

Kamil

Friday 3rd of July 2020

That's pretty amazing and exactly what I needed! Thank you soooo much!

Kyith

Saturday 4th of July 2020

No problem.Hope it is helpful.

Randy

Thursday 2nd of July 2015

Thanx for this Stock Portfolio Tracker. Is there anyway to put in the stock name in the TRANSACTIONS and heve them go to the STOCK SUMMARY sheet. I use the spreadshhet mostly for the TRANSACTIONS. It is a second step to have to put in the STOCK NAME into the STOCK SUMMARY sheet so that it appears in the TRANSACTIONS sheet.

Drizzt

Tuesday 24th of July 2012

hi M L you may want to let me edit ur doc to see if i can help with the problem. just invite [email protected]

M L

Tuesday 24th of July 2012

Hi,

First I deleted the '.N' at the end of my yahoo codes and the prices appeared. Then the 2nd row price was actually the third row stock while the third row has no price. The formula in the cells are correct. Then I manually keyed in the price in the manual column for the 2nd stock and the 3rd row stock price came out correctly. Strange.

This site uses Akismet to reduce spam. Learn how your comment data is processed.