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.

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

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

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!

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

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

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.

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 >

  • Ron B

    Hi everyone,

    I’ve been using the spreadsheet extensively for several years and have a tremendous amount of data and history loaded. Recently, the spreadsheet stopped working. First, it does not update with new prices from Google or Yahoo. Plus, any time I try to enter new data I get the error message. “There was a problem. Sorry, Google Sheets has encountered an error. Some of your data on this spreadsheet may have been lost. We’re reloading your spreadsheet now…” I get this error message using different PCs, ISPs, browsers, etc. Always the same. I’m now sunk!

    Thanks for any advice…!!

    RB

  • Steve

    Hi there,

    This is fantastic. Any chance of getting the ASX (Australian Stock Exchange) added to this?

    Thank you

  • wheat

    Thanks for your effort to create this speadsheet. I have a question…
    I would like to track my initial amount (startup money) and then subsequent contribution….and total gain/loss over the period of time…. Thanks.

  • Arielle

    Hi Kyith!

    Thanks for the spreadsheet. I just started using this recently. When I made a copy of the original, there’s no monthly tracker for dividends. How do i add it in? Thanks!

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

    Hi Steve Australia is supported. For google use in this way: ASX:WOW , for yahoo use WOW.AX for woolworths

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

    Hi Wheat, what i did is to have another sheet to handle the Cash transactions. so everytime u buy something you need to sell cash units and buy the stock units. when you sell something, you sell the stock units and buy cash units.

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

    Hi ArielLE ,

    I took out the sheet because i tend to find it troublesome and probably not needed.

  • J

    Hi Kyith, I think what David meant in the first question is if there is a way to separate the growth in your portfolio due to your investment skill/performance vs. your addition of cash into your portfolio.

    For example, using David’s example, say you start 2014 with $10,000 and end 2014 with $12,000. Then it’s quite simple to say that you generated $2,000 or 20% return on your portfolio because your investments worked out well.

    But if you started 2014 with $10,000. Then you added $2,000 in June 2014 because you managed to save. And by end 2014 you have $12,000. In this scenario, it clearly means that you have not invested as well as in the first scenario. In fact, what is your return? 0% or 20% or something else?

    So basically the question is: is there a way to separate the performance of your portfolio and the impact of adding/subtracting cash from the portfolio?

    Sorry to hijack your question David. But I have been thinking about this too and couldn’t figure out a way to solve it.

    Thanks Kyith. Your spreadsheet is awesome! I tried to create one from scratch but eventually found yours which is way more complete. Thanks for sharing!

  • wheat

    Thanks Kyith for your response. I was trying to add few more columns for total debt, equity, total asset. Actually I want to calculate debt ratio….Do you have any idea how can I import it from google or yahoo finance? I can get a table where it gives debt from yahoo finance but that table has all other information which I dont need? Thanks so much.

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

    Thanks J for clarifying. the solution to the performance thing is to use something like XIRR. What i did is to try to compute the cash inflow and outflow, and aggregate up to give the XIRR. the number doesn’t look like it make sense! and the computation is heavy! so i took it out, but for folks who are interested, they can take a look at the read me sheet to see how i tried to do it.

    Cheers

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

    Hi wheat, the financials are harder to grab and i haven’t found a good source yet. I suppose even the yahoo data only have dividend yield and PE. you can take a look at this evernote shared note for reference. the original post was taken down > https://www.evernote.com/shard/s2/sh/d112f50c-375e-40f8-aeb0-35f9df6b8bc8/abf1903772ac79ae3d4c7443b0d7ddf2

  • WJ

    I’m having the same problem also!

  • Mattsun

    This is brilliant Kyith. I’m a novice stock investor and user of this sheet. Could you pls let me know how can I find the summary of a particular stock, say Ascendas – if I want to know how much I have invested, what’s the profit/loss etc. sorry if its a silly question. Thanks in advance buddy,

  • Tony

    Hi,
    Thanks for the great spreadsheet. Could you please add the feature of Cash Deposit/Withdrawal and “Deduct from cash” for transaction? Something what Google Finance portfolio is having. Thanks.

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

    Hi Mattsun,

    What do you mean where to find the summary. do you mean where to find the name of the stock? On my stock portfolio tracker, the profit and loss, dividends are tabulated under Stock Summary sheet, while the transactions for the stock, say ascendas whether it is buy, sell or dividend will be at the Transactions sheet.

    Best Regards,

    Kyith

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

    Hi Tony,

    I thought about adding and will continue to evaluate. The reason why i do not add is because i am afraid that i will confuse my users. Some just want to track whatever they buy. but seems more and more people are asking for it so let me see how i can modify this.

    Best Regards

  • Mattsun

    Got you, Kyith. I meant – where to find the total number of units that I’m holding for any given share. Found it in the Stock Summary sheet. Thanks much :)
    Could you also please help me understand what does the categories Dividend, Cold Storage, Growth mean (in the Summary sheet)? I mean, why are those shares in those categories? Cheers,

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

  • Nero Osyris

    Hi,

    I just can’t seem to get the Google Docs to work. It keeps crashing and I can’t use the document at all…

    Are you able to fix it?

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

    Hi mattsun, those categories are what I used to compartmentalize some of the stocks I owned. You might have your way of doing it. You can go to the ref sheet to change. I have them in those days because I kept things simple by looking at them as either growth or more dividend yielding

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

    Hi Nero,

    What platform are you opening the sheets on

  • Jeff King

    I can’t get this to work at all. I downloaded the doc as an Excel 2013 (*.xlsx) file. Upon opening it, Excel 2013 detected a bunch of errors and corruption with the file. It prompted me to make the repairs and I let it. When Excel attempts to repair a workbook, it makes a log of the issues it found, in XML format. Here’s the XML repair report.

    error074640_01.xmlErrors were detected in file ‘Stock Portfolio Tracker (New).xlsx’Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.Removed Records: Formula from /xl/worksheets/sheet12.xml partRemoved Records: Formula from /xl/worksheets/sheet6.xml partRemoved Records: Formula from /xl/worksheets/sheet3.xml partRemoved Records: Formula from /xl/worksheets/sheet7.xml part

    Now, I’m not sure if that report will help you or not and so here’s some behavior that seems wrong to me.

    Starting in ‘Stock Summary’, I did a ‘Clear Contents’ of all the yellow cells to get rid of all your stuff. I did the same thing for the rest of the sheets, clearing only the _contents_ of only the yellow cells.

    Let’s revisit ‘Stock Summary’. I enter “Microsoft” in B3, and ‘MSFT’ in both C3 (Google Quote) and E3 (yahoo quote).
    D3 output = “#N/A”
    F3 = MSFT+
    G3 = (empty)
    I3 (last price) = (empty)
    The Units (M3) and Cost (N3) columns have no formulas in them but rather they are just text.

    I saw your instructions on how to fix the problem of not getting updated pricing from Yahoo! but I think this might be one of the repairs Excel attempted. This is now the formula for [Yahoo Data Ref]:A2:
    =IFERROR(__xludf.DUMMYFUNCTION(arrayformula(ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”&LEFT(CONCATENATE(‘Stock Summary’!F2:F1034),LEN(CONCATENATE(‘Stock Summary’!F2:F1034))-1)&”&f=snk2l1jkm3m4″))),”CC3.SI”)

    And this is where I stop. There are more issues but I haven’t dug into them deeply. Can you help?

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

    Hi Jeff, this is a Google spreadsheet and only works in Google. You can go to File > Make a copy and enjoy.

    Kyith

  • Nero Osyris

    I tried opening using Safari in Mac and also Internet Explorer on Windows Vista. It doesn’t work for both.

  • Allen

    I dont seem to get this spreadsheet to appear in google am I missing something

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

    hi Allen, did you go to File > Make a copy to use it?

  • Nero Osyris

    I tried opening using Safari in Mac and also Internet Explorer on Windows Vista. It doesn’t work for both.
    Does it work for only specific browsers?

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

    Hi Nero,

    I don’t received any complaints on unable to open up. I am using it on chrome mainly.

    Best regards,

    Kyith

  • Nero Osyris

    I see. I’ll try it on Chrome. Thanks.

  • Taylor Schon

    HI THERE, your spreadsheet looks great; only problem is when i save a copy and try to modify the stock tickers, the prices don’t update. Do i have to do something in google docs to get the prices updated? Like click on something? Or are they supposed to update in real time? What am I doing wrong >.<

  • BFC

    Great tool I have to say. Is there anyway to update it so that it can include “Short selling”?

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

    Hi Taylor, what ticker symbol are you trying to update and how are you doing it. Perhaps a screen capture here and I can help you with it

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

    Hi bfc , there used to have a person that does the short selling but I have to find it first

  • BFC

    Hi, I actually managed to customize the tool myself. Now it works with Short Selling! Lots of nested “if” statements! :)

  • Hieu Nguyen

    anyone use this spreadsheet to track proceeds from selling options?

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

    HI BFC,

    you are great! This is what the sheet is for in the first place to make it your own!

    Best Regards,

    Kyith

  • Mohammed Hakimi

    Thanks for spreadsheet.

    It took off a lot of manual work from I was thinking of.

    Is it possible to have a new sheet tab which details about total holding in hand of stocks on basis of all the transaction made in transaction tab?

    Is it possible to have a version connect to NSE site of India?

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

    Hi Hakimi,

    You can see that the transaction data consolidates to the stock summary data. however I cannot connect this to the NSE site of india. perhaps you have to work out that part on your own. Yahoo Finance also stop furnishing data for India.

    Best Regards,

    Kyith

  • Mohammed

    Nice work, embedding my experience and able to customize, fetch details from NSE site using google finance. Nice work google. Need to modify google finance formula .

    Can graph for each stock history price be accommodated? i.e of day, weeks, months, year.

    You are correct, on stock summary data I able to view the consolidated position.

    I was thinking how is it possible to track amount original invested as capital…Keeping amount release from gain per day separate. Tracking the extra exposure using over limit of brokering firm.