Bloomberg Assignment 1 - Portfolio Theory


The goal of this assignment is to introduce you to the Bloomberg terminal. We will use it to perform quick exploratory analysis and then download data from Bloomberg into Excel for further analysis in Excel (and potentially other packages like Python and R). Watch the video here and replicate the analysis for your securities. Then follow the directions for your write-up and submit both your Excel and Word documents to Dropbox.

In this video, you will:

  1. Use EQS to screen 5 public US equities
    • Examine the DES, FA, BRC, GP modules
  2. Create portfolio in PRTU
  3. Optimize portfolio weights in PORT OP
  4. In PORT, examine
    • Portfolio VaR
    • Scenario Analysis – Lehman
    • Volatility Decomposition
    • Statistical Summary
  5. Explore the Excel templates: XLTP XAAO, XAAC
  6. Create Bloomberg charts in Excel
  7. Download prices PX_LAST
  8. Perform the analysis:
    • Calculate returns
    • Calculate average, standard deviation and Sharpe ratio (Rf = 3/12)
    • Calculate correlation matrix of securities
    • Find optimal weights to maximize portfolio’s Sharpe ratio
    • Plot securities expected return vs. standard deviation
    • Bonus: Using Python or R, calculate and plot the efficient frontier
      Hint: Write a function that maximizes a portfolio’s expected return subject to having a standard deviation less than or equal to UB. Write a for loop that calls your function and saves the expected return as you vary the standard deviation UB. Plot the expected returns against the standard deviations.

After watching the video and completing your analysis in Excel, write-up your analysis in a Word document. Specifically:

  1. Describe the five companies’ business models and outlook. Why did you choose these securities to place into your portfolio? What are recent news events that have impacted their performance?
  2. Describe the securities’ risk-reward statistics. Give some commentary about these statistics based on the news events and research reports you read.
  3. What are the optimal investment weights that maximize the portfolio’s Sharpe ratio? Discuss the economic reasoning behind the optimal investment weights. What is the benefit of diversification for your portfolio?

For each team, submit to Dropbox both your Excel file and Word document. Make sure to include all team members’ names on both documents.