Portfolio Report

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.

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
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
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
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

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.

Name ___________________________ ID __________________
If you want to avoid the 2-step manual process above, you can use something like:
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.
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
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)
Name ticker Current

weight Current Value Desired
Henry Capstone HCE 40% $ 400,000 30.30% $303,000 $27.92

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


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


*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

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