Free Online Investment Stock Portfolio Tracker Spreadsheet

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. 

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 couldn’t 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 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.

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

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:{your stock symbol}”. 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 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.

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.

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

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

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


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!

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

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

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: {singapore stock quote}.SI e.g. CC3.SI for Starhub
  • USA:  {USA stock quote} e.g. AAPL for Apple
  • Hong Kong: {hong kong stock quote}.HK  e.g. 0543.HK for Pacific Online
  • Canada: {canada stock quote}.TO e.g. FFH.TO for Fairfax Financial Holdings
  • London: {london stock quote}.L e.g. VOD.L for Vodafone
  • Germany: {german stock quote}.F e.g. BMW.F for BMW
  • Malaysia: {malaysia stock quote}.KL e.g. 7113.KL for Top Glove
  • India cannot work. I tried both .BO and .NS and they just wouldn’t work
  • China Shanghai stock quotes doesn’t work I tried SS and it wouldn’t work


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.

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 >

Share on FacebookEmail this to someoneShare on Google+Share on LinkedInTweet about this on TwitterShare on Reddit
  • JD

    Have been using this great spreadsheet for a while and hoping for some more assistance. In the ‘new’ google sheets (green tick at the bottom of the browser window), the $ amounts on the Portfolio Summary worksheet in the ‘Current Value’ (and other columns, are slow to update. The initial values on launch are also not the last known values, but just a random amount from I don’t know how long ago. I open the spreadsheet daily primarily on 2 different desktop machines, but the amount is the same on both (not a local device caching issue). Any suggestions on how to either have the current value appear faster and/or store the last known ‘current value’ instead of a value that is very out of date? Happy to share my spreadsheet if required.

  • Kyith

    Hi Ian, I think I would do that. We have scrip or drip over here as well and they are really take the cash and put it back to work

  • Kyith

    Hi JD, I think we can’t get around the processing as it takes some time. But can I find out if after the processing did they give you the correct value ?

  • JD

    Hi Kyith, thanks for the quick reply. Yes, after the processing happens the values are correct. On a phone it is very slow (a few minutes), and on the desktop it is bearable but the value on initial opening is often the same for several weeks and isn’t even the ‘last opened’ value, so the spreadsheet seems to store an old value somewhere. I could do with a workaround as I’m now using this value via a ‘Importrange’ fomrula in another google sheet, but the other sheet doesn’t refresh, and takes the ‘pre processed’ value from the portfolio regardless of whether the portfolio is open or not. My workaround at the moment is to not do a lookup and to instead open the portfolio sheet, wait for it to update, then manually input the value into my other spreadsheet, not ideal or seamless…..

  • Kyith

    Hi JD. Ah I see you want a quick reference . its difficult as I try to optimize the formula but it very much depends on how google handles it. I think I understand your solution and I think its crumble some but at this moment not much solution here

  • JD

    No problem. Sounds like I will have to live with it for now. Most things improved with the new version of google sheets but the updating of prices has slowed a lot. Can’t win all the time. Thanks for the confirmation.

  • Kyith

    JD actually it looks slower with the process bar but I felt its actually better handled. My main transaction sheet is 700 rows and on previous one it will just show a working…. And sometimes it will not work. On this one it is much much more predictable

  • JD

    Yes, I agress with the improved predictability of where the updating is at when the sheet loads. The progress bar certainly helps. I have over 500 rows so even when it was less in the old version it used to look loaded for a while and then suddenly change without warning.

  • Pingback: Investing Spreadsheets - Two Investing()

  • Pingback: Transactions Sheet in Dividend Portfolio Tracker - Two Investing()

  • Robert

    One thing that seems to be missing is fees. I see you have transaction fees but what about fees that are charged by ETFs? I remember in older versions you had fee included in the buy, sell, div pull down menu

  • Kyith

    Hi Ankeny,

    I believe that the transaction costs can be used for commissions. It depends very much on how you want to input this. for funds you could put in a higher buyprice to factor in the cost, but for me, i invest in exchange traded funds. Each transactions is 25 bucks so i put that in the transactional cost.

    do let me know if that is missing for you.

  • PetriA

    what’s the correct way to input selling Rights Issues? Should I mark the as Dividends of the stock?

  • PetriA

    Hi again,
    This is great tool and besides enabling to follow my portfolio also enables learning Google spreadsheet. I have been inserting my transactions since 2007. I faced to couple cases that my portfolio company has split itself to two different companies. Typically the old one continuing with old name and the second company getting a new name. How am I supposed to enter this transaction if the original company’s purchase price (e.g. 16$/share) is divided so that the new original company is given 70% (11,20$) and the new company purchase price is decided to be 30% (4,80$)? Number of shares will be same for both companies. E.g. if I originally bought 100 shares, after the transaction I will have 100 the new original company shares and 100 the new company shares. Is there anyway to implement this transaction or should I create a new transaction type for this?

  • k2bro

    I too would like to see: “Also another feature that would be great would be to calculate the monthly/quarterly/annual % return of the portfolio, and portfolio growth over time.”

  • Kyith

    Hi PetriA,

    For rights issue, they are usually a Buy transaction at a discounted price, so what you can do is to key in another Buy transaction at the discounted price. If you do it correctly, what happens is after the rights issue, the price should fall to the the ex rights price. This should be made up by the shares you purchase at discounted price.

    For the spin off case, i suppose your original shares the price will drop, and then u need to start a new stock call “new stock” and then enter a buy transaction at the price at a 30% discount.

  • Kyith

    hi k2bro,

    that is going to be difficult to implement. it seems i will need to keep track at the end of the year what is the ending price so as to be able to have this feature. if i need to track monthly and quarterly i have to have a never ending price table.

  • PetriA

    Hi Kyith, thanks for the reply.
    Regarding the right issues, in some cases I have sold right issues instead of actually purchased new shares with discounted price. Therefore I cannot use Buy transaction. I have now marked them as dividend, although they are not exactly dividends.
    For the spin off case your suggestion for the new stocks is very good and obvious, but If I change the old purchase transaction, it changes the cash flow calculation from the original stock purchase date onwards, which is not nice. I’m considering separating cash-flow follow-up completely to its own tab, but that changes overall sheet quite much. Do you have any other suggestions?

  • PetriA

    I’m wondering the same. I may be doable if return is followed only on annual level, but still quite laborious exercise. And the question is can you automate to get historical values from Google finance or Yahoo.

  • Pingback: First Portfolio update | The Independent Abecedarian Blog()

  • Pingback: Recording Divs/Trades? -

  • Dao Jun

    Hi Kyith, first of all, thanks for the great spreadsheet!

    I’m having trouble with “Transactions”. A certain row’s cumulative units couldn’t add up properly. I used your formula at the header to correct it, which worked.

    However, the cumulative cost section is still wrong. I manually added the numbers together for that particular cell. Will this cause any problems subsequently? Or will I be fine? As I’m not sure how the cell formula’s are.

    Thank you!

  • Eddiepwns

    Does this Spreadsheet support short selling? I can’t find that option anywhere

  • Jeff Tapia


    I’ve been using your spreadsheet for a couple years now and donated at that time.
    But again, thanks.

    To my version I have since added sparklines (using google function), 52 week range “chart”, etc.

    I’ve moved around colums but, for example, you can chart 52 week range:
    current price: I2
    52 low: V2
    52 High: W2

    Anyway, thx.

  • David

    Thank you for the amazing portfolio. A quick question, is there a way to track portfolio cash in flow and out flow so that the return data can be accurately calculates? Thank you,

  • Steve

    i need some more help with this

  • Steve

    I tried adding two new sheets and then adding them to the portfolio summary. I edited the name =sum(‘Ameritrade Summary’!N3:N)*Ref!$D$6, to match my new sheet, but it doesn’t work, what am i doing wrong?

  • Pingback: USA Stocks discussion - 2015 - Page 28 -

  • Kyith

    hi Eddiepwns, currently it does not i’m afraid.

  • Kyith

    hi John, you can add that to your spread sheet but i felt that its not something everyone would require so i did not add.

  • Kyith

    Wow Jeff! that looks awesome! i never new you can do a chart in the cell!

  • Kyith

    Hi David could you elaborate further?

  • Kyith

    there shouldn’t be an issue. do you want to share it with me so that i can take a look?

  • Pingback: Which Stock Portfolio spreedsheet/app you guys using? -

  • David

    Hi Kyith,

    Thanks once again for the GSpreadsheet. I had been using this for a year now. I was having some doubt in the last input row. Why was the cost of transaction was $1716.56? Why did the cumulative cost is $480.64? Why was there a loss of -$531.56 since I still have partial shares holding? I’m trying to understand.

    Thanks in advance.

  • David

    Hi Kyith, for example say the portfolio started at $10,000 then later I deposited another $2,000 and later again I withdrew $1,000. Is there a way to account for future deposits and redemptions so that the return % will not be affected?

    Also, is there way that the “cash #” can be auto adjusted for every buy/sell/div transactions recorded in the Transaction sheet? I am just subtracting and adding #s to cash manually at the moment.

    Thank you,

  • Kyith

    Hi David, the cash is difficult to auto adjust. I present the possibility to users that you can have cash, then when you buy X shares you ‘sell’ cash to ‘buy’ X. when you ‘sell’ X later, you ‘buy’ cash.

    I think the idea behind the average cost price is sound and it is difficult to see how much unrealized gains, unless you modify the ‘Transactions’ worksheet to get the latest price from Stock Summary and use it to calculate the amount of unrealize gains.

    i find that not very useful since we should be looking at the average value of our assets. unless you are viewing it as a trader.

  • Jane


    What is DPU?

  • Dao Jun

    Heya Jane, Distribution Per Unit or dividends

  • Dao Jun

    Hi Kyith,

    I’m having some problems with 3 column’s, Previous Units, Previous Cost and Cumulative Cost. They don’t seem to be adding up properly for certain stocks only, but are fine for others. Not sure if you saw my previous comment, but the same error happened for a new stock that I added. Had to erase the formula and put in figures for it to work.

    Wondering if you would happen to have any solution for this? Once again thank you very much for the spreadsheet, it’s been really useful.

    Dao Jun

  • Tristan Coleman

    Hi there,
    Really enjoy your site and the effort you put into it. I’ve been using a modified version of your stock portfolio tracker for my investments, but recently my spreadsheet has stopped working. I think there is an error in fetching the yahoo share price. The google price data is downloading, but the (from memory) default method of inputting the yahoo, google or manual price into the “Last Price” field is also not working. I’ve re-downloaded your spreadsheet template, but this causes my browser to quit, so I think this might be a widespread problem. Thanks for any suggestions.

  • margin call

    Hi, can I request a margin call section where the portfolio can have LVR on each stock to get a portfolio LVR and show how close you are to getting a margin call

  • Kyith

    Hi Tristan, so sorry to hear that. I observed my own template and it seems ok. perhaps you can make a copy of yours and share it with me at so that i can see if there are some issues.


  • Kyith

    hi margin call, that would mean i need to put in a margin ratio, current equity. I don’t think this is the prevalent usage and perhaps you can modified your own copy to make it work for you.

    Best Regards,


  • Tristan Coleman

    Hi Kyith, thanks for your reply. There seems to be a lot of idiosyncratic behaviour going on in google sheets. I haven’t changed anything on my modified portfolio tracker and it just stopped updating stock prices completely. I’ve tried opening your template – works in firefox and chrome (on mac), causes safari to try to reload the page many times until it quits with error message “safari web content failed to load”. My modified sheet doesn’t work on any of these browsers, or in google sheets app for iOS and android (previously working fine). I have a feeling that google is messing around at their end, but I’m unable to confirm this. I’ll email a copy of mine to see if you can see any glaring errors. Thanks again.

  • Kyith

    Hi Tristan, on nov 2014, i changed my template to the new google spreadsheets because it seems they are changing permanently to it.

    you can find the post here >

    if you obtained it before this period then you may be using the old one. even with the told one i check jsut now it is ok.

    i am using chrome.

    you can check out the screen capture. in the past on and off i do have periods where the things are not updating. can i check with you if Yahoo Data Ref is updating.

  • Tristan Coleman

    Hi Kyith, thanks for having a look. I’m still getting strange behaviour both with your old template (modified) and the new one. Yahoo Data Ref not updating some times. AUD to USD conversion not updating sometimes. Your new template opens in chrome on mac, but causes safari to crash. Also, it doesn’t open in sheets for ios or android. I might try migrating my modified sheet to your new template and opening it in chrome and see if that works. Thanks for taking the time to have a look at this for me. Really appreciate it.

  • Kyith

    Hi Tristan,

    Can’t open up your sheet. damn not sure why. try not to try on ios and android now.

  • LJ


    First of all, thank you so much for this wonderful spreadsheet. I’ve been using it for 2 years now and it has been amazing!

    I’ve encountered a few problems lately and I wonder if you can help me with them:-

    (a) For one of my latest transactions, the data is not reflected in my Stock Summary tab;

    (b) My “Yrly” tab has not been updated and I do not see any input at all for year 2014, even though I did receive dividends in that year;

    (c) Some of the data in my Stock Summary tab does not seem to make sense. For instance, it reflects that I am getting yield on costs of 116.61% on one of my stocks, even though I have not received any dividends on that stock yet.

    I would be grateful if you could help me with these issues. Thanks again.


  • Pingback: Keeping up - live portfolio tracking spreadsheet - 7 Circles()