How to Track Your Investment Anywhere with this Online Spreadsheet
[edit] Newer Version Now Available
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.
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)
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.
