Tracking Portfolio Performance vs SP500 in Excel

May 2, 2008

Most professionals recommend you only check your statements once or twice a year to balance them. Checking them too frequently might cause you to do stupid things, like sell your stocks when the market is down and buy them when the market is high. You can’t be emotional when investing, and when you check your portfolio too often, you’re bound to do stuff that/when you shouldn’t.

Easier said than done. I know what I need to do, but I’m still obsessed about daily performance, net performance, annualized performance and even performance against the S&P500.

Managing my portfolio in Excel would be too cumbersome without being able to automatically update stock prices. I tried Web Queries, but it wasn’t really what I wanted. So I tried Google docs. Google Spreadsheet is just amazing. I could track my overall portfolio’s performance in real time (using Googlefinance function). Stock prices are updated every few secs without any intervention. Just sit back and ~~relax~~ obssess. However, in addition to my daily and total net gains, I wanted to track my annualized gains. Google Docs provides the XIRR function that calculates annualized gains, but because it doesn’t have Lookup tables, the setup became quite cumbersome. Also, for complex spreadsheets with too many lookups, the spreadsheet became quite slow to use.

Google Docs wasn’t perfect, but it was still my best solution.

That is, until I found MSN Money Stock Quote Add In. It’s an Add In that allows you to update all your stock quotes from the internet. Unlike GoogleFinance, it doesn’t refresh prices automatically. You have to run the plugin for it to connect and refresh your stock prices and you can only do it every 5 minutes. Not a big deal. I was more than willing to give up real time stock updates for all the power of Excel.

And here’s how you can do it:

Note that I’m using Excel 2007.

First Step - Download and install the MSN Stock Quote add in

After you install it, you can access its help pages for more reference.

Here’s a summary:

MSNStockQuote(Symbol,Property,CountryCode)

Symbol is a stock symbol (for example: “MSFT”, “AOL”, “IBM”, “AAPL”).

Property is a piece of information regarding the stock, such as Ask Price or Close Price. This is an optional parameter. If omitted, the default is Last Price. You can either type the indicated property as text, or use a value from 1 to 25 to represent the property you want to return.

And the function reference:

Property argument	Value returned
1 or “Last” or “Last Price” or “Last Sale”	Price of the last trade
2 or “Time” or “Time of Last Sale” or “Last Sale Time” or “Last Time” or “Date” or “Date of Last Sale” or “Last Sale Date”	Date/time of the last trade, returned as a serial number, in local time for the New York Stock Exchange
3 or “Size” or “Size Of Last Sale” or “Last Sale Size” or “Last Size”	Size of the last trade
4 or “Ask” or “Asking Price” or “Ask Price”	Last asking price
5 or “Ask Size” or “Asking Size”	Last asking size
6 or “Bid” or “Bidding Price” or “Bid Price”	Last bid price
7 or “Bid Size” or “Bidding Size”	Last bid size
8 or “Open” or “Opening Price” or “Open Price”	Opening price
9 or “Close” or “Closing Price” or “Previous Close” or “Close Price”	Last closing price
10 or “High” or “High Price” or “Day High” or “Day’s High”	High price for the day
11 or “Low” or “Low Price” or “Day Low” or “Day’s Low”	Low price for the day
12 or “Volume” or “Vol”	Volume traded for the day
13 or “Change” or “Chg”	Change for the day
14 or “Percent Change” or “% Change” or “%Change” or “% Chg” or “%Chg”	Percent change for the day
15 or “Year High” or “52 Week High” or “52 Wk High”	52 week high
16 or “Year Low” or “52 Week Low” or “52 Wk Low”	52 week low
17 or “Market Cap” or “Market Capital” or “Market Cap.”	Market capital
18 or “Earnings Per Share” or “EPS”	Earnings per share
19 or “PE” or “P/E Ratio” or “PE Ratio”	PE ratio
20 or “Shares Outstanding” or “Shares Out” or “Shares Out.” or “# Shares Out” or “# Shares Out”	Number of shares outstanding
21 or “Currency”	Currency in which the stock is traded
22 or “Exchange”	Exchange on which the stock is traded
23 or “Country”	Country in which the stock is traded
24 or “Type”	Type of stock traded
25 or “Company Name”	Company’s name

Step 2 - Track your Transactions

The next step is to create a separate “table” for each stock you own so you can track your transactions. This is only necessary for more detailed performance tracking, like annualized (where you need cashflow) and return vs any index.

You need to name your tables in a specific pattern that includes the stock ticker. This is to simplify your summary table that will need to know which table to look for transactions.

In my case, I named them “Stock_” and the stock ticker symbol. For example, to track my Citigroup (yaiks) stock transactions, I created a table named “Stocks_C”.

click on the image above to expand it

It’s a pretty straight forward transactions table. The only kink I added to it was the closing SPY price for the day. For those that don’t know, SPY is used to track the S&P500 index.

Column	Description
Date	Date of Transaction
Type	Transaction Type (Dividends, Buy, Sell, etc)
Qty	Stock Qty
Price	Stock Price
Comm	Commissions paid
Purchase Cost	Total Transaction Cost (Qty * Price + Comm)
Cash Flow	Used to calculate annualized return. In my case, I reinvest dividends, so these are excluded from the cash flow.
SPY Price	SPY Closing Price
SPY Qty	SPY Stocks I could’ve purchased had I bought it instead of my stocks

For the table’s Total Row, I calculate the total number of Stocks (Qty) and the total Purchase Cost with the well known SUM function.

I also had to enter today’s date and today’s current value of my holding as part of my Cash Flow (to calculate annualized returns), so I used the TODAY() and the MSNStockQuotes functions. Don’t forget that for Cash Flow, you need to use positive and negative numbers to differentiate between deposits and withdrawls. With detailed information about each transaction, the total cost, the total number of stocks and my current position, I had everything I needed.

Step 3 - Summary - Putting it all together

Now that our Transaction tables are setup, we can finally set our Summary table up.

click on the image above to expand it

All the formulas in this table are based on the first row (the stock ticker), so to add a new stock to our Summary table, we simply have to copy one of the rows and update the ticker symbol. That is why we named the tables as “Stocks_” and the ticker symbol in the first place. We use the INDIRECT function so that we can dynamically figure out the right Transaction table to access based on the ticker symbol (Columnb B).

You’ll notice that at the top, I have SPY and its latest price. I’ll use this to calculate my hypothetical Current Value had I invested in SPY.

Column	Description
Ticker Symbol	Stock ticker symbol
Company Name	Retrieved with MSNStockQuotes
Last Update Price	Retrieved with MSNStockQuotes
Change	Retrieved with MSNStockQuotes
Change %	Retrieved with MSNStockQuotes
Daily Change	Dollar profit/loss for the day
PE	Retrieved with MSNStockQuotes
EPS	Retrieved with MSNStockQuotes
Stocks	Total number of stocks for that ticker. Looks up Transaction Table and retrieve the sum
Total Cost	Annualized Return for the ticker. To calculate the XIRR, we need to include all the transactions with their dates as well as the current date with the current value
Current Value	Stocks * Last Price
Net Gain	Current Value - Total Cost
Net Gain %	Gain %
XIRR	Annualized Return for the ticker. To calculate the XIRR, we need to include all the transactions with their dates as well as the current
SPY Stocks	Number of SPY stocks I would have had I purchased them instead
SPY Current Value	My Current Value had I purchased SPY
Return vs SPY	Difference between my return vs the return I would have with SPY
% From Portfolio	The weight each stock has in my portfolio

Tracking the performance vs the S&P 500 requires a little bit more work. Basically, the way I did this was by figuring out how many SPY (Spyder ETF that tracks the S&P index) stocks I could’ve purchased had I purchased them instead of my stocks. For that, I had to track the closing price for SPY for each transaction I had. A bit of work, but totally worth it to know whether your portfolio is actually beating or losing to the S&P500 index.

So there you go. If you’re an Excel fan, this is one great way to track your portfolio performance using Excel.

Category Posted in Finances, Investment

Comment Feed

3 Responses to “Tracking Portfolio Performance vs SP500 in Excel”

Barbara Swafford said:

May 5th, 2008 at 5:08 pm

Hi J2R

Such a comprehensive post, with detailed information.

Thanks for sharing your great find.
Mat said:

May 5th, 2008 at 6:38 pm

This looks great! thank you for the info I have been wanting to do this for awhile. Just never knew how to get started. Thanks.
Eric said:

July 15th, 2008 at 9:18 pm

I’m an Excel dork with a PF mind… and I’m in love with this post…

i just wish the msn formula provided for a date specification as an input; if the default was today() it’d provide the same functionality but would allow the time-plotting of the portfolio with a lot of ease…