Portfolio instructions as given in Assignment 1:

“Run a portfolio simulation of stocks, using the OTIS platform. The strategy or methods you use for choosing

stocks is up to you, though I suggest at minimum you begin with a random selection of at least 10 stocks from

the S&P500 (large cap, US stocks). Then work from there implementing your chosen strategy throughout the

semester. At semester’s end, you will analyze that portfolio, and write a 2-page report.”

The portfolio data runs from Monday 29 July until Friday 11 October. You can write most of the report in

advance, but will need to wait until after 11 October to calculate your final performance metrics. The report is

due 18 October.

Report Instructions:

You should have 1 page of performance numbers (as shown below), and 1-2 pages of discussion and table.

Page format should be single-spaced lines, and double-sided printing. Use section headings as shown below.

Ensure that your name (surname/familyname in CAPS) and ID are in the header of every page.

In the discussion section, be clear and concise, using proper English. Verbosity will count against you, as will

spelling / grammar errors and unintelligible sentences.

Make the effort and take the time to make sure your work is better than "just presentable".

Some of the information required for this report can be obtained directly from OTIS, though for TE and the

measures against the Class Portfolio you'll have to do some extra calculations.

Submitting:

The report text must be submitted using the format as noted below, both on Stream and in hardcopy.

The spreadsheet with data and optimization must be submitted on Stream, in an Excel file.

The hardcopy must be submitted in the dropbox in the hallway next to QB 2.36 (near my office and the

office for School of Economics & Finance) – level 2 of Quad B.

This has changed – do NOT submit the hardcopy

The submission deadline is 18 October, 1pm.

Late reports will lose 10% per calendar day (or partial day).

Technical Details:

Numbers reporting and calculation details

Name ___________________________ ID __________________

All performance numbers must be reported in Annualized terms (assume 250 trading days in the year). Do

not report daily numbers. These should be reported in Percent (%) or Basis Points if very small. Make sure

you are clear which you use.

To annualize return, multiply the daily average by 250

To annualize standard deviation, multiply the daily std dev by sqrt(250)

All performance numbers must be reported to at least 3 significant digits.

For example, r = 0.334% or r = 33.4 bps, or IR = 1.56. Do not round 1.74 to 2; and do not report r = 0.003.

In your calculations, however, carry out each step with as many decimal places as you can (4 or 5 at absolute

minimum). Otherwise, interim rounding may cause your final answer to appear incorrect.

For returns, use arithmetic rather than log returns

For variance, standard deviation and covariance calculations, use population rather than sample.

(except when using the covariance tool in Excel for the optimization step)

Getting data for the Performance section

Data from OTIS

Data for the daily values of all the portfolios can be found on OTIS under "Rankings", then "Performance

Value Data".

About a sporadic problem in OTIS

As I mentioned in class, there has been an ongoing issue with how OTIS reports the Day 1 value and/or

return. Elsewhere on stream you may see instructions about using a Day 0, but this time I want you to ignore

that.

So, after the Main Portfolio closes, just download your data and use it as is with no modification to the raw

data. I’ll do the same in my calculations.

VERY IMPORTANT: download your data as Excel, not CSV. Their CSV data are not reliable. But note

that the data will be in newest date first order, so you’ll want to re-order the data before calculating returns.

Risk-free rate

For the Sharpe, Treynor and Jensen calculations you will need the risk-free rate. You can get the daily

numbers from OTIS on the Rankings page for Sharpe. Download the spreadsheet (at the bottom of that page)

"How was the Sharpe Ratio for my portfolio calculated." That spreadsheet has the daily risk-free rate

numbers (those are the rates I will use to calculate these stats).

Other details in the Performance section

Beta

In the Performance section, you must calculate your beta over the period of this simulation. When you do this,

beware you may get odd numbers (like -4.5 or 126). As you know, portfolio betas should be somewhat close

to 1, so that may seem like an error. However, with only a few observations (as we have in these portfolios),

strange numbers can occur. So, check your work carefully, but it may be correct. If after checking your work

the beta looks correct, report that and use it for the Treynor and Jensen measures (and don't forget there are

two Benchmarks so you'll have two betas).

Name ___________________________ ID __________________

Sharpe, etc.

There are two versions of the Sharpe ratio; the original which assumed a constant Risk-free rate, and the

revised (modern) version, which allows the risk-free rate to change daily. Use the modern version, which we

went over in class (and see above about getting the Rf rate).

Report “Excess Return” and “Active Return” as we have discussed in class.

The requested Std Dev is the std(Excess Return), therefore the number used in the Sharpe ratio.

Tracking error

Tracking error must be calculated using the industry version, as we have done in class. As of this writing

OTIS reports two versions – the "Long Term Tracking Score", and the "Daily Tracking Score". Their Daily

Tracking Score is the industry standard that we use. The TE reported for your portfolio against the SP500 will

probably be close to correct, but you should calculate this yourself to check your calculations (and of course

you have to calculate TE against the Class Portfolio).

For all calculations requiring standard deviation, variance, or covariance (TE, Sharpe, Beta, etc), use the

population version of the formula. (e.g. if using Excel, that means using function STDEV.P, rather than

STDEV.S)

Optimization

Unlike the Performance section which is about what you did during the 12 or 13 weeks of the simulation, the

optimization is looking ahead at what might be the best portfolio to have in the future. This should be based

on your portfolio as of the end of the simulation period, but it can be a bit different if necessary due to data

issues.

Getting data for the optimization

To calculate the covariance matrix (in order to get the portfolio variance), you will need a fairly long history

of returns for each of the stocks in your proposed optimum portfolio. I suggest getting daily returns for 1 or 2

years (or daily prices and then you calculate the returns).

You will need this from each of the stocks, the benchmark (not the Class portfolio), and the risk-free rate.

All of these data you can get from yahoo, google, Reuters marketwatch, Bloomberg, etc.

In the case of the benchmark (Russell 1000 or SP500, etc), if you can’t find the exact index, you can use a

closely related ETF (e.g. Vanguard Russell 1000 ETF). The numbers won’t be exactly the same, but they will

be very, very close (plenty close for our purposes).

Using Excel to calculate the covariance matrix

There are 3 methods to do this.

Excel covariance tool

You must have the “Data Analysis” add-in installed, and then choose ‘covariance’, selecting your input and

output ranges. The result will be the lower triangle of the covariance matrix.

To complete the matrix, use copy/paste special/transpose to a different area of the sheet; and then copy/paste

special with “skip blanks” ticked, and then copy back into your original matrix.

I will demonstrate this in about weeks 3 and 11.

Using MMULT

Name ___________________________ ID __________________

If you want to avoid the 2-step manual process above, you can use something like:

=MMULT(TRANSPOSE(B1:K250–B251:K251),(B1:K250–B251:K251))/250.

This would be where daily returns data for 10 stocks for 1 year is in columns B through K, and the means are

in row 251.

Since you must calculate the means, this is still 2 steps, but it will update correctly if you change the input

data (as long as you do not change the number of rows or columns).

Calculating manually

In about week 5, I work through an example of portfolio construction, and have a file with covariances

calculated “manually”; that is with no functions.

This is to demonstrate each part of the covariance calculation, including calculating the squared “deviations

from the mean.” The MMULT method is doing the same thing, and it is probably a useful exercise to try this

out (with a small set of stocks) with each of the 3 methods, and confirm you can get the same covariances.

This will also help you firmly understand what the MMULT and TRANSPOSE functions are doing, which

will help you in the optimization variance calculation.

Report format follows:

Name ___________________________ ID __________________

Portfolio Report (10% of course grade)

(4%) Performance Measurements (annualized):

Be clear about whether you are reporting in decimal, %, or basis points.

Gross return _________

Excess return _________

Std dev of Excess _________

Sharpe _________

Russell 1000 as bench Class Portfolio as bench

Active return _______ _______

Beta _______ _______

TE _______ _______

Treynor _______ _______

Jensen _______ _______

IR _______ _______

(1%) Strategy

1 short paragraph

Briefly describe your strategy and how it evolved or changed during this assessment period (if it did). You

will not be graded on the meaningfulness or validity of your strategy, but it should be something – even as

simple as “stocks that increased in price 3 days in a row”, or even “stocks that begin with Z.”

Name ___________________________ ID __________________

(1%) Portfolio Performance discussion

1 short paragraph

Take the perspective of a portfolio manager wishing to improve the chance for a big bonus. Describe/assess

your portfolio performance. Use specific information from your portfolio – do not embellish with trivia.

Feel free to use evaluation methods or criteria discussed in the lectures or textbook not mentioned above.

NOTE:

Please do not simply repeat info that is in the portfolio metrics table. (E.g. “My Shape ratio was 0.9, compared

to the Treynor of 1.2 and Jensen of 1.5 …”)

I am interested in *interpretation*, not regurgitation.

So something like, ‘my Information ratio of 1.5, relative to the Class portfolio, indicates that my portfolio

outperformed …’

Please also do not discuss/explain performance technique or theory; this report is for an audience assumed to

be familiar with these methods.

(4%) Optimum portfolio

The first 3 parts of this report are about the performance of your portfolio, strictly over the 12 or 13 weeks the

“Main Portfolio” was running; that is, it is “backward looking”.

This section of the report is “forward looking”; that is, what might you do from here on (if the portfolio were

to continue). It is precisely what a real fund manager faces, when asked to rebalance his or her portfolio using

optimization.

There are 3 steps:

1. Determine (or estimate) the ‘current’ portfolio. This should be the existing stocks as of the last week

of the simulation, and what their weights are in the current portfolio.

a. You need at least 5-10 stocks for this exercise. If you don’t have enough, you can either pick

some you sold earlier, or just randomly pick some other stocks.

i. If you have a lot of stocks (especially if there are many with small weight), you can

use just the top 10 by weight.

b. For each stock, determine it’s current weight (for any you ‘randomly’ added, that current

weight will be 0).

2. Find the optimum portfolio.

a. Use the optimization techniques we have studied.

b. This will be in terms of the weights desired in the optimum portfolio.

c. The optimization parameters and constraints are up to you. So, explain in your report your

choices for risk aversion and short-selling at least, but also if there are others, such as

Tracking Error.

i. You do not have to justify those choices; just tell me what you did.

d. Be sure to include what you used as the objective; that is, Utility, Sharpe, etc.

3. Determine the trades required to get to the optimum.

a. Basically, New Weights – Old Weights = Trades

Very briefly (2 or 3 sentences), describe how that optimum portfolio compares to your actual ending weights.

Include (perhaps in a small table) what parameters you used – objective, risk aversion, short selling.

Also describe how some metrics have changed – Sharpe, variance, …

Name ___________________________ ID __________________

Include a table for the stocks and their optimum weights and dollar values. (if you have many stocks, this can

be attached to the report)

E.g.:

Name ticker Current

weight Current Value Desired

weight*

Ending

Value

Ending

price

Required

trades

Henry Capstone HCE 40% $ 400,000 30.30% $303,000 $27.92

10,852

Jane Cornerstone JCE 50% $ 500,000 57.20% $572,000 $1.78

321,348

Xavier Doors XD 10% $ 100,000 12.50% $125,000 $3.15

39,683

*the Desired Weight is what you have determined from your optimization

Required trades result from the difference between current and desired weight.

You can also show the current and desired # of shares (as I did in class), with the trades being the difference.

If applicable, attach a screen shot of your optimization inputs.

DO NOT attach tables of data or return series (that will be in your spreadsheet submission – see below).

Calculating your optimum portfolio

Using the historical means and variances of your portfolio, calculate what would have been the optimum

weights for your stocks. For this purpose, use the stocks that are in your portfolio as of the closing date.

(assume no transactions costs for this exercise).

You can limit this to 10 or so stocks; it is not necessary that you use every stock in your portfolio. I’d suggest

using the 10 with the highest weight, but that is not required. (you do, however, need at least 10 stocks)*

SPREADSHEET submission

(The spreadsheet submission is ONLY for the optimization)

Price and return data for each of the stocks you used.

Ideally you will have prices/returns in one sheet, and covariance and optimization in another. Make sure you

save it with our optimization parameters and constraints, so we can check that.

For each* of those stocks:

1. download daily prices for the past year

a. you can’t get these from OTIS

b. get these from yahoo or google or marketwatch or Bloomberg or …

2. calculate the daily returns,

a. Note: when you download the data, you might be able to download as returns.

3. calculate the covariance matrix.

* if there are stocks for which you cannot find prices, just omit them from your optimization. (you may have

to select some ‘random’ stocks if this causes you to have fewer than 10)

In the spreadsheet submission

Sheet 1 “Name and text”

Your name and ID, and paste in the text from the optimization section of your written report

Sheet 2 “Benchmark & Stock Data”

The raw returns data (don’t include the prices) from the benchmark you used, and each of your stocks. Order

these in date order (oldest to newest), with each stock in columns. (same as I showed you in the example in

class)

Name ___________________________ ID __________________

Sheet 3 “CovMatrix”

Your calculated covariance matrix. This should include the benchmark.

Sheet 4 “Optimization”

Your main worksheet where you do the optimization calculations. If you do your optimization work in another

spreadsheet that’s fine; just copy in the main page here.

Sheet 5 “Summary table of rebalancing”

Copy in the table from your report (or create the table here and copy this into your report), similar to the

example I showed in class, where you list the stocks, original and desired positions, and resulting required

trades.