Web Queries

1
Web Queries Portfolio Optimization

• Some Financial Wizardry

2
Web Queries

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

3
Can set various options here. For example, you
can choose how much formatting is inherited from
the web table.
Paste URL in here and click Go
Then select the Tables you want for your Import.
Excel puts a little yellow arrow next to each
HTML table on the web page. The .WebTables
property of the Web Query will contain the table
number.
Click Import button when youre ready to go
4
The Connection String
http//finance.yahoo.com/q/hp?sGOOGa07b31c2
004d02e31f2006gm
http//finance.yahoo.com/q/hp?
sGOOG
a07b31c2004d02e31f2006
gm
5
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

6
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
7
The End ProductCh 31 in VBA
8
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
9
Section 7.7 Portfolio Optimization Models
Easy to relate T and R.
Easy to relate X0 and X1 through R.
10
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

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

12
Covariance and Correlation
-

0
13
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?

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

15
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