Personal Statement
Chapter 1 Summary
Chapter 2 Report
Breakeven
Analysis
Price
& Demand Relationship
Quantity
Discounts Decision
Hedging
Investment Risk
Time
Value of Money
Enterprise DSS
Time Series Forecasting
DSS Development Project
Simulation Model Examples
Government
Contract Bidding
GFAuto
Model
Customer
Loyalty
Game
of Craps
Monte Carlo
Simulation
Optimization Modeling
Term Project
Business
Intelligence Research
|
|
Term Project
Introduction
Objective
Data Source
Procedures
Implementation of Results
Finish
Introduction
Both Guolin and Joe graduated from CIS department. And we both took FI8000.
So we would like to apply what we learned in this course to portfolio
management. There is one very important concept in PM, efficient frontier,
which defines the set of efficient portofolios.
Objective
The following project is to demo a portfolio optimization application.
We will download data from website, then calculate daily excess returns
of every stock during the period concerned.
Data
Source
Yahoo! Website has historical daily stock price data during any time
period.
Procedures
- In this project, the application will retrieve data from the web
site and save the data into an excel file. Every stock will have a separate
Excel sheet.
- Then we will calculate the summary measures (like means, standard
deviations (stdev), and correlations (Correl) between every two stocks)
for the excess returns. The excess return excludes the risk free rate
(3% per year).
- Covariance is then calculated.
- Calculate the standard deviation of the portfolio (using Excel's MMult
and Transpose matrix functions.)
- Then I set up a portfolio optimization model to minimize the portfolio
standard deviation for a given minimum required mean return. We solve
this model for several (20) times to find the efficient frontier that
is shown in tabular and graphical form. The application solves the model
for 21 equally spaced values of the minimum required mean return. These
values varied from the minimum to maximum as displayed in "EfficFront".
- In the sheet, we can also see the optimal weights for the various
portfolios.
Implementation
of Results
- Run the application. Or from the demo sheet, we can see the difference
between DELL and YHOO. So we can see YHOO is a higher return and higher
risk stock while DELL is lower risk under this special situation.
- The summary measures are used as the input data for the optimization
model. This optimization model finds the optimal weights (fractions
of each dollar invested in the various stocks) that minimize the standard
deviation of the portfolio (the risk) subject to achieving a minimum
required mean return.
- Efficient Frontier Chart. All of these occur behind the scene, rather
quickly. The connection to the Yahoo site is made, the data are returned.
Then the calculation performed. Then the solver runs. The user will
see the chart.
Finish
Though we tried our best to make it, there are still some bugs. Some
users' concerns are coded like date input, stock input.
Date Input:
Check date inputs before go to next step
Stock Input:
A stock sheet contains all the stocks in the portfolio.
Some of them we cannot control as the source data are from Yahoo website.
The other problem may relate to Excel itself. Since VBA is a rather small
size programming language and it may have difficulties in dealing with
large amount of data. And if the machine is not responding, we have no
choice but stop it and run again.
Portfolio.xls
PortfolioDemo.xls
|