fbpx

How to Track Your Investment Anywhere with this Online Spreadsheet


Written by

Jae Jun

follow me on

Facebook

Twitter

To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.

********

[edit] Newer Version Now Available

Online Investment Tracking Spreadsheet

Online Investment Tracking Spreadsheet

Working Hard on The New Online Investment Tracking Spreadsheet | Flickr: kenteegradin

There is now an even better version available. The new and updated version of the best free stock tracking portfolio spreadsheet. Go check it out now.

download

Continue on with the Original Article

The new year is just around the corner and maybe it’s time to reorganize your portfolio tracking spreadsheet or method. Whether it be through a stock tracking service or an investment tracking spreadsheet, keeping track of your performance in very important.

I have an automated portfolio tracking spreadsheet based off a template, that compares the performance between my investments and the S&P500 ETF (SPY) that utilizes the amazing SMF add-in to automatically pull the historical quotes.

How to Create a Portfolio Tracking Spreadsheet

Google Docs is great because you can now link Google Finance to a Google Spreadsheet. You can get 20 min delayed quotes, PE, EPS, change %, historical prices, etc automatically for any ticker.

So if you have a Google account, here are some simple formulas and steps to start creating your own portfolio spreadsheet online.

Get Ticker Price Quotes

Google has their own predefined finance functions to make this real easy. The syntax is =GoogleFinance(“symbol”; “attribute”);

To get the last price of a stock, simply enter into a cell

=GoogleFinance(“KO”,”price”)

If you have a list of stocks in column A, you can do something like

=GoogleFinance(A2,”price”)

Pretty easy. Other functions can be found on the Google help page.

Get Today’s Price Change

To get the price change enter the following

=GoogleFinance(A2,”change”) & ”  (” & GoogleFinance(A2,”changepct”) & “%)”

Again, this formula assumes you have a list of tickers in Column A starting at row 2. Click on the image below or go to the portfolio tracking spreadsheet page if you are not sure.

Get Historical Stock Data

The syntax for this function is: =GoogleFinance(“symbol”, “attribute”, “start_date”, “num_days|end_date”, “interval”)

A little tricky but very easy in practice.

In the online investment tracking spreadsheet, I enter the stock purchase date in column E. I then use the historical stock data function to get the closing price of the S&P500 ETF on the day I bought a particular stock. So if I bought GOOG on 1/1/2008, the stock purchase date in column E would 1/1/2008 and the formula to get the same closing price of the SPY ETF would be

=INDEX(GoogleFinance(“SPY”,”close”,E2);2;2)

download investing checklists

Other Attributes You Can Use

Basically, all you need is the price and historical stock price formula to start tracking a portfolio but you can always add more if you want to.

Some others you can use include:

  • price: market price of the stock – delayed by up to 20 minutes.
  • priceopen: the opening price of the stock for the current day.
  • high: the highest price the stock traded for the current day.
  • low: the lowest price the stock traded for the current day.
  • volume: number of shares traded of this stock for the current day.
  • marketcap: the market cap of the stock.
  • tradetime: the last time the stock traded.
  • datadelay: the delay in the data presented for this stock using the googleFinance() function.
  • volumeavg: the average volume for this stock.
  • pe: the Price-to-Earnings ratio for this stock.
  • eps: the earnings-per-share for this stock.
  • high52: the 52-week high for this stock.
  • low52: the 52-week low for this stock.
  • change: the change in the price of this stock since yesterday’s market close.
  • beta: the beta value of this stock.
  • changepct: the percentage change in the price of this stock since yesterday’s close.
  • closeyest: yesterday’s closing price of this stock.
  • shares: the number of shares outstanding of this stock.
  • currency: the currency in which this stock is traded

Free Online Investment Tracking Spreadsheet

Of course I won’t let you go empty handed. I’ve now gone ahead and put an online version of the stock portfolio spreadsheet on Google Docs. So now all the information will be constantly up to date and you can access it anywhere, anytime. You can then export it as an excel file, pdf or openoffice file for record keeping.

To access it simply follow this link to the investment tracking spreadsheet. Anyone can view it but to be able to use it, you’ll just  need to create a Google account.

How to Use The Investment Tracking Spreadsheet

To use it, you first need a Google account and then have to copy it to your own account.

  • Create Google account if you don’t have one
  • Click the above image to take you to the page
  • On the portfolio page, click on “File” in the menu and then “download as”
  • Click on “Download as Excel” -> OK
  • It will download a copy to your computer and if you want to use it online, upload it back to your account.

If you now double click on the “Current Price” or “SPY Close on Purchase Date” you will see the formula I mentioned above.

When you want to enter your trade information, enter the details only in the yellow columns. Then highlight the non yellow cells, and drag down with the right hand corner box.

And you get the following

So that’s how you add transactions and you do the same for the positions you have sold. The performance tab is updated automatically so nothing to do there.

The free portfolio tracking spreadsheet unfortunately cannot account for dividends, splits, ticker changes and such. These things will have to be updated manually. You could then make it better by including graphs and other goodies.

To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.

********

download investing checklists

What is Old School Value?

Old School Value is a suite of value investing tools designed to fatten your portfolio by identifying what stocks to buy and sell.

It is a stock grader, value screener, and valuation tools for the busy investor designed to help you pick stocks 4x faster.

Check out the live preview of AMZN, MSFT, BAC, AAPL and FB.

38 responses to “How to Track Your Investment Anywhere with this Online Spreadsheet”

  1. Jason says:

    Thanks a lot Jae, a real treat! Happy new years!

  2. Fabrice says:

    Terrific!

    I didn’t know this option to link with Google Finance.

    I am sure in few weeks, we’ll find many other interesting usages. Thank you.

  3. Tyler says:

    Awesome Jae,

    I wish someone did a stock screen (for ASX) that had like ROE,DEBT-Equity, ROC etc.

  4. Jae Jun says:

    Tyler,

    I think you could grab the ASX market by slowly building your own site and eventually your own screener.

    Just no room for the ASX here.

  5. Aron says:

    Hi Jae,

    Happy New Year and another good post! How would you treat a second purchase of the same security? or dividend reinvestment? would you treat each one as a new purchase?

    thanks!

  6. Luis says:

    Jae,

    AGAIN you provide another great service to those looking for help investing. Thanks so much. I am going to start using this tracker immediately… one question, what’s your opinion for tracking the impact of keeping cash on the sidelines while looking for other opportunities, etc. That opportunity cash that is not being employed is taking away from possible returns (or losses). Most portfolios, most of the time, are not 100% invested. That cash on the sidelines should have some consideration when comparing performance to the S&P, right? If you agree, how do you think it should be accounted for?

  7. Jae Jun says:

    @ Luis,

    Thanks Luis 🙂

    Tracking cash is good for a IRR but it depends on what account you have. e.g. Mine is a 401k which I contributed to until last year which does skew the results. However, the result that I put up is what other people would gain had I managed their money. More like a fund manager’s return.

    Calculating it is a different story as well. I can’t think of a possible way to make it into a simple spreadsheet…

  8. SARA says:

    Hi
    Thanks for providing this I have many questions.

    1. It says uptop
    “When you want to enter your trade information, enter the details only in the yellow columns. Then highlight the non yellow cells, and drag down with the right hand corner box.”
    Where is the right hand corner box and am I supposed to click on it while I’m dragging my mouse the screenshot can’t be enlarged for me to see

    2. In the holding spreadsheet If I enter a position will it also show in the sold spreadsheet.

    3. How do you enter it if your shorting a stock

    4. I entered a position in the yellow columns in the holding spreadsheet and I dragged the non yellow columns with my mouse but for the market value column and the equivalent to spy shares column on the same line I entered the position it shows up $0.00 for two of those columns and for all the other lines and columns that was previously entered by you as an example it shows up fine , what am I doing wrong?

    I hope you can answer my questions I love this spreadsheet and would love to use it I know I’ll have more questions in the future as I’m not excel savvy I Hope you can help me again.

    Sincerely,

    Sara

  9. Jae Jun says:

    Hi Sara,

    1. When you highlight a cell or multiple cells, you will see a small dark solid square in the bottom right corner. In the image I posted, I drew a red circle around it. Click that small square in the corner and drag down.
    Or do a search for “excel drag fill”.
    http://www.designcad.com.au/support/office/excel/drag_fill/Drag-Fill.htm

    2. Holding is for something you currently hold. Sold is for something you sold and no longer hold, so if you sold something, you would delete that amount from the holding section.

    3. I’ve never shorted so I didn’t think about that.

    4. Refer to Q2. Just make sure you drag and fill properly.

    I would definitely recommend some simple tutorials to help you get started with mastering excel in no time.
    Lots of free tutorials on the internet.
    http://people.usd.edu/~bwjames/tut/excel/
    http://www.fgcu.edu/support/office2007/Excel/index.asp

  10. Ranajit says:

    Hey Jae,
    I tried out your tracking spreadsheet and it was excellent. Sometime back I tried creating my portfolio tracking sheet on excel but since I had to manually change the prices, I lost track of it. But, this spreadsheet is excellent. Thanks a lot for making it available to everyone. I came to your blog from Fwallstreet and I must commend you for the excellent material on this blog.

    -Ranajit

  11. robin says:

    is there anyway this investment tracking spreadsheet can be uploaded to Turbo Tax during for filing income tax?

  12. Jae Jun says:

    Afraid not. You can use it to determine what you need to enter into turbo tax however.

  13. Thanks, this was really helpful. It also helped me see how dumb I was being this month. I’m sure I can turn it around over the course of the year though.

  14. Jae Jun says:

    I’m creating a new excel spreadsheet that should provide more and better functionality than this online one. Google spreadsheets still has too many limitations.

  15. Andrew says:

    I used this spreadsheet as a basis for tracking stock purchases. I work in various currencies and tried to automatically import the exchange rates on the day of the trade for tax purposes (example =importData(\http://currencies.apps.grandtrunk.net/getrate/2009-11-12/CAD/USD\). I wish I knew before getting as far as I did that Google spreadsheet will only support 50 imports per document. What a stupid restriction. Grrrrrrr

  16. bpowerstv says:

    Thanks much i have been trying dozenes of “portfolio trackers” and excel sheets, trackign LONGS is easy, but need to keep track of SOLD results too!

  17. DP says:

    I followed below steps. However I am not getting “Edit this page” option.
    I am not able create a copy of this spreadsheet. Please help me.
    Thanks,
    –DP
    To use it, you first need a Google account and then have to copy it to your own account.

    Create Google account if you don’t have one
    On the portfolio page, scroll down to the bottom of the page and click on “Edit this page”
    Click on “File” -> “Make a copy” -> OK
    You have now successfully copied it to your own account
    If you now double click on the “Current Price” or “SPY Close on Purchase Date

  18. Jae Jun says:

    @ DP,

    It seems like Google changed the way they share. The edit this page seems to have disappeared. I’ll see whether there is a way to edit it.

  19. JJ says:

    Hi Jae, love the Blog!
    Need further help though.
    I too, do not get the “Edit this page” option, therefore cannot copy to my Google Docs!
    Any further help would be appreciated.
    Thanks.

  20. Jae Jun says:

    Yes Google changed the way docs are shared. Not sure how to get people to be able to save it to their own account.
    The best method so far is to follow the instructions in the article and create your own. You’ll learn more in the process as well.

  21. DP says:

    Jun,
    As you are owner of the spread sheet you need to make few changes so that any one can save it.
    Here are the steps.
    Go to share link on the right side of the spread sheet.
    Select publish as a web page > Click stop publishing.
    Then Close the window.
    Again go to Share Menu > Select Share > Change who has access. > Select Anyone with the link.
    Then you will get a link to share window with a link.
    Now share that new link in this blog page.
    Everyone will have option to save the spreadsheet from the file menu.

  22. Jae Jun says:

    Thanks for the tip. Here is the link.
    http://spreadsheets.google.com/pub?key=tZG9334Ma2Q2AEhcxHIVNiA&gid=2

    Go to file and download as then select excel. Then upload it back to your google docs account.

  23. Tom Vogt says:

    Do you have any plans to develop your Stock Valuation Spreadsheet for use on MAC? I think you would find an active group of MAC users willing to purchase your program.

    Tom

  24. Jae Jun says:

    At this point, there is no plan. I was working on one but it didn’t work out. Just ended up spending a lot of money. I want to do it in the future, but I need the right people to help me make it possible.

  25. Kay Waterfield says:

    Hi Jae… I don’t know how old your link is from your post with “Thanks for the tip. Here’s the new link…” but I am still (9/4/11) unable to download (I’m on a Mac) or to edit. It seems identical.
    Mac doesn’t have “Download” under FILE menu. I can only “Save As” a Web page or HTML.

    I really thank you for the work you’ve done here!! I truly would appreciate being able to share. (I do already have a gmail account and use Docs regularly.)

  26. Jae Jun says:

    @ Kay,

    Try again by clicking on one of the images. It will load to the proper screen so that you can save it to your account.

  27. Scott says:

    Hi, Your reference “(an improved version of the online portfolio tracking spreadsheet is available)” but I cannot find anywhere to access, instructions to create, or download the online version as seen here: https://spreadsheets.google.com/spreadsheet/pub?key=0AlkTYa6J3P_pdFpHOTMzNE1hMlEyQUVoY3hISVZOaUE&gid=1

  28. Scott says:

    The following does not work, when I’m logged into Google there is no “file” location:

    Thanks for the tip. Here is the link.
    http://spreadsheets.google.com/pub?key=tZG9334Ma2Q2AEhcxHIVNiA&gid=2

    Go to file and download as then select excel. Then upload it back to your google docs account.

  29. Scott says:

    I was finally able to access your free online portfolio tracker spreadsheet. However, how do you get the equity curve graph in the performance tab embedded in the online version?

  30. goodgenie4u says:

    Brilliant use of Google. As a senior I have BCE.TO stocks that were part of my emplyee monthly savings plan for 29 years. The dividends were reinvested. I have the onerous task of computing the Adjusted Cost Base of this stock to determine Capital Gains exposures and find ways to offset some with CG Loses. I am wondering….

    (1) Will Google Finance’s historical data base go as far back as 1973?
    (2) If so would it automatically factor in dividends reinvested/
    (3) Would it factor is stock splits?
    (4) I will have to enter the stocks purchase info, every time as part of the savings plan each year.
    (5) Is it possible for Google finance to compute the Adjusted Cost Base?

    If this is not possible, I get the feeling I will have to look at all my quarterly statements and manually compute this. These are on line calculators, but I am reticent of their security as well as the stability given the large number of transactions (4 DRIPS and 1 purchase per year x 35 years)

    Any computing solution you suggest, with built automatic accurate computing (formulae) would be great. I can enter the numbers accurately.

    Thanks

  31. Samuel says:

    Hi Jae,
    Sorry, I am not familiar with Google docs and had trouble copying your google spreadsheet (1/2/12).
    I am using Windows 7. I tried with both Internet Explorer and Firefox. When I clicked on the link:

    http://spreadsheets.google.com/pub?key=tZG9334Ma2Q2AEhcxHIVNiA&gid=2

    I could only get “Save As” a Web page or HTML after clicking on File->Save As …
    I couldn’t find a way to save as an Excel file.
    With your comment to @Kay, I couldn’t figure out what you meant by:
    “Try again by clicking on one of the images. It will load to the proper screen so that you can save it to your account.”
    I tried the different tabs ie holding, sold, performance, index. None of them had the “edit this page” either. Any suggestions?
    Thanks.
    Samuel

  32. Jae Jun says:

    @ goodgenie4u,
    1. I don’t know.
    2. no. Only price.
    3. Yes
    4. Yes.
    5. You will have to calculate the adjusted cost basis.
    Google is just a tool. It won’t do anything unless you tell it to. There are lots of limitations still which is why I still prefer to do it in excel.

  33. Rachel says:

    Hi I want to convert this spreadsheet into Pound Sterling, how do I do this?

  34. It’s very simple. All you have to do is change the $ sign to a pound sign. You do that by selecting all the cells related to $, and then go to the menu -> format -> number -> more currencies and select the British pound.

    The only problem is getting the automatic stock pricing from google because I’m using US google finance to get the price of the stocks.

  35. B Bond says:

    Thank you

  36. switesh says:

    Thanks Jae. Handy article.

  37. Guest says:

    It was awesome to calculate investors profit using this spread sheet. As well as investment calculator is other option to calculate profit. I was learnt precise information that how to use spread sheet whereas calculator.
    http://www.ptp-fund.com/calculator.php

Pick Winning Stocks and Fatten Your Portfolio

LIVE PREVIEW OF AAPL, MSFT