Spreadsheet basics for efficient portfolio construction

In the previous article, we introduced the concept of portfolio trading as a way to diversify your funds away from the day to day noise and stress of active trading. We also covered the basics on how to formulate your objectives and your initial asset allocation. In this article, we will take the discussion a step further and illustrate how to create an efficient portfolio using Microsoft Excel.

Asset Selection

Just a quick reminder on this whole process of Portfolio Optimization. One of the main problems of the whole sub-prime mortgage crisis was the faith put in mathematical models. In the large investment banks, complacency took over and everyone forgot what the word “risk” really meant.

The Optimizer is only a tool and works like any other computer: garbage in => garbage out. If you are not careful with the data inputs, you will get distorted results.

This process is not a magic black box. It is a tool and as with any tool, it is only as good as the person using it.

With that said, we’re going to start this exercise with four assets:

1. The SPY ETF

visible here:

http://finance.yahoo.com/q/pr?s=SPY+Profile

The investment seeks to provide results that, before expenses, generally correspond to the price and yield performance of the S&P 500 Index.

2. The VB ETF

visible here:

http://finance.yahoo.com/q?s=VB&ql=0

The investment seeks to track the performance of a benchmark index that measures the investment return of small-capitalization stocks. The fund employs an indexing investment approach designed to track the performance of the CRSP US Small Cap Index, a broadly diversified index of stocks of small U.S. Companies.

3. The VEA ETF

visible here:

http://finance.yahoo.com/q?s=VEA&ql=0

The investment seeks to track the performance of a benchmark index that measures the investment return of stocks issued by companies located in the major markets of Europe and the Pacific region. The fund employs an indexing investment approach designed to track the performance of the FTSE Developed ex North America Index, which includes approximately 1,383 common stocks of companies.

4. The BND (or AGG if you prefer) ETF

visible here:

http://finance.yahoo.com/q?s=BND&ql=0

The investment seeks the performance of a broad, market-weighted bond index. The fund employs an indexing investment approach designed to track the performance of the Barclays U.S. Aggregate Float Adjusted Index. This Index represents a wide spectrum of public, investment-grade, taxable, fixed income securities in the United States-including government, corporate, and international dollar-denominated bonds, as well as mortgage-backed and asset-backed securities-all with maturities of more than 1 year.


So the four assets that we have in our portfolio are highly representative of broader asset classes; which is excellent for keeping things simple and efficient.

Yahoo! Finance is good location for historical data because the interface is very easy to use and readily accessible.

Take note of one detail when importing your data into your spreadsheet: we are going to use the Adjusted Close for our purposes because Yahoo! Adjusts the close for any dividends that may be released.

clip_image002

Inserting Adjusted Close into the Spreadsheet and Calculating Descriptive Statistics

After you have copied and pasted and ORDERED your closing prices from least recent on top to most recent on bottom, we can proceed to calculating the returns.