Portfolio Optimization

1
Portfolio Optimization Web Queries

• Some Financial Wizardry

2
Warning Big Time Simplification and Hand Waving
Ahead

• Investment Science is a complex subject (see text
  of same name by Luenberger)
• We will gloss over many details and hidden
  assumptions
• Underscores value of combining
• Domain knowledge (e.g. finance)
• Mathematical modeling skills (e.g. probability,
  statistics)
• Information systems skills (e.g. VBA, application
  development)
• Financial Engineering at graduate programs
  throughout the country

3
Portfolio Optimization

• Given a set of investments, how do we find the
  portfolio that has the lowest risk and yields an
  acceptable expected return?
• The single period mean-variance Markowitz model
  (1991 Nobel Prize)
• You saw this in your Intro Finance course

Stocks
Bonds
Gold/silver
x1
x2
x3
x4
x5
x6
4
The End ProductCh 27 in VBA
5
Returns
Easy to relate T and R.
Easy to relate X0 and X1 through R.
6
The Expected Return (ER) of a Portfolio

• Let Si (random) return on 1 invested in
  investment i
• Let mi expected return on 1 invested in
  investment i ESi
• Let xi fraction of each investment invested
  in investment i

7
Measuring Portfolio RiskA Variance Approach

• Let s2i variance of return on 1 invested in
  investment i VarSi
• Let rij correlation between Si and Sj
• VarR is used as a model for risk
• Why do you think correlations are relevant?

8
Covariance and Correlation
-

0
9
A Portfolio Optimization Model

• What are the decision variables?
• Is the objective function linear or non-linear?
  So what?
• Are the constraints linear or non-linear? So what?

10
A Portfolio Optimization Model
Minimize
Subject to

• What are the decision variables?
• Is the objective function linear or non-linear?
  So what?
• Are the constraints linear or non-linear? So what?

11
Variants of the Basic Problem

• Ex. 7.9 The Basic Markowitz model
• Ex. 7.10 Scenario approach to future
  uncertainty
• Ex. 7.11 Includes transaction costs (trading
  isnt free)

12
Estimating the Inputs
From where?

• mi ? , sample average of returns on
  investment i. (AVERAGE)
• Fundamentally REALLY, REALLY DIFFICULT to
  estimate the mean return with much precision
  using purely historical data
• s2i ? s2i, sample variance of returns on
  investment i (VAR)
• rij ? rij, sample correlation between investments
  i and j (CORREL)

Portfolio1_Isken.xls
13
Web Queries

• Pull data from tables on HTML pages into Excel
• Can get formatting too
• Relies on Connection String
• A little tricky
• Lets do some from Yahoos Finance pages
• Then automate with VBA
• No doubt, this will get easier over time

14
The Connection String
http//chart.yahoo.com/t?a01b01c95d04e11
f01gmsMSFTy0z
http//chart.yahoo.com/
t?
a01b01c95d04e11f01
gmsMSFTy0z
15
A Porfolio Optimization DSS Application
Main
1. Get stock choices
2. Get date range
3. Clear old stuff
4. Get stock data from web
5. Consolidate returns
6. Calc model parameters
7. Create model
4.1 Add new sheet
8. Solve Opt. problem
9. Efficient Frontier
4.2 Web Query
9.1 Range of returns
4.2.1 Connection String
10. Update Chart
9.2 Solve opt. probs
4.2.2 Run Query