BYU Student Author: @Nate
Reviewers: @IWillyerd, @Donovon, @TylerBooth
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
- Tableau
Need a program? Click here.
Overview
Today is February 8, 2018, and you are a financial analyst at Sundance Valley Advisors (SVA), a hedge fund that specializes in factor-based equity funds.
Your team is tasked with building a new momentum fund that meets the following requirements:
- The fund must consist of the stocks of between 30 and 50 companies.
- Each stock in the fund must have an average daily abnormal return of at least 5 basis points (i.e., 0.05%) over the past five years.
- The fund must be equally weighted.
- The fund can hold no short positions (i.e., all stock holdings must be positive).
Your team lead has asked you to generate a list of potential companies to include in this portfolio and has provided you with two data files to use in your analysis. One contains the daily opening and closing prices of each individual stock in the S&P 500 going back to February 7, 2013. The other contains the daily returns of both the S&P 500 (representing total market returns) and the 10-year US Treasury Note (representing a risk-free return) over the same period.
Glossary
- Factor-based equity fund – a group of equity securities that controls exposure to the risks associated with one or more independent factors (e.g., momentum, liquidity, or growth).
- Momentum fund – a type of factor-based fund consisting of securities from companies that have experienced strong economic performance in the recent past.
- Abnormal return – a stock’s return in excess of the market’s return during a specific period.
- Equally weighted fund – a fund that holds an equal dollar amount of each security. Because of this weighting, the returns of an equally weighted fund are a simple average of the returns of each security in the fund.
- Excess returns– returns in excess of the risk-free rate, representing the return an investor receives for taking on a specific level of risk.
Instructions
Using the data provided, create two visualization to help you identify candidates for the fund and test their previous returns against the market.
Data Prep: Import both csv files into Tableau and link the two together using “Date” as the related field.
Viz 1: Identify which companies have the largest earnings momentum over the past 5 years.
- You want to perform your analysis on excess returns. Create a calculated field that subtracts risk-free rate from the market rate on each day.
- Your stock data does not contain daily returns, but it does have opening and closing stock prices. Create a new field to calculate excess daily returns using these prices.
Suggestions and Hints
Daily returns are a simply percent change from the beginning to the end of the day. Remember that to get excess returns you must subtract the risk-free rate from the total daily stock return.
- Now that you have excess daily returns, you want to know what portion of those returns are abnormal returns. Create a field to calculate this.
- Create a bubble chart that shows the stock tickers of all companies, where the size of each bubble is the average daily abnormal return. Put the ticker of the company on each bubble and add some color. Filter the chart to show only companies with average daily abnormal returns above 5 bps.
Suggestions and Hints
Remember that 5 basis points is 0.0005. There should be 40 stocks that match this criteria.
Viz 2: Create an evenly weighted portfolio of the momentum stocks you identified in Viz 1 and compare its annual returns with those of the market over the past five years.
- You first need to estimate annual returns based on average daily returns. Create calculated fields to annualize the average daily returns for both stocks and the market. Use the following formula to annualize returns:
- ((1 + average of excess daily returns) ^ 365) – 1
- Graph the annualized market returns for each year from 2013 to 2018. Then, graph the annualized stock returns on the same axis.
- Filter the stock returns to include only the momentum stocks you identified in Viz 1.
Suggestions and Hints
You must filter by “Name”. When the dialogue box appears, select “Condition” and filter the names based on your calculated abnormal returns field. Make sure the measure is set to “Average”.
- One line represents the annual excess returns of an equally weighted portfolio of momentum securities, and the other represents the annual excess returns of the market as a whole. Do you think these companies would be a good starting point for building a successful momentum fund?
Data Files
- Challenge23_5_Yr_Market_Return_Data_2013-2018.csv
- Challeng23_5_Yr_Stock_Price_Data_2013-2018.csv
- Challenge23_Data_Dictionary.docx
Solution
Challenge23_Solution.twb
Solution Video: Challenge 23|TABLEAU – To the Moon
