23|TABLEAU – To the Moon

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.

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:

  1. The fund must consist of the stocks of between 30 and 50 companies.
  2. 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.
  3. The fund must be equally weighted.
  4. 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.

  • 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.

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


This was a great Challenge! I struggled a bit trying to figure out the formulas, but your hints really helped! I found that making a heat map of the visualization made it a little easier to see which company had the largest earnings momentum. Something like this:

But the bubble chart is much prettier. Really insightful way to use Calculated fields! Thank you!

Very interesting challenge! I love the economic application. Here’s my bubble chart:

This one stretched me! I had a hard time coming up with the second visualization, but thanks for recreating it in your video. Here’s a picture of my bubble chart though. My color palette? It’s summer… and it can’t come soon enough!

1 Like

I enjoyed this challenge, which pushed me to explore further into Tableau and use it in new ways!