*BYU Student Author*: @Parker_Sherwood

*Reviewers*: @Jae, @Mark

*Estimated Time to Solve:* 45 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

**Overview**

As an employee at True Blue Financial Solutions, a small financial advisory firm, you’re responsible for calculating the total return for multiple stocks over a given time period. With large datasets, this process can be complicated and time-consuming. That’s why you’re seeking to simplify your workload by automating the process using Excel. In this challenge, you’ll develop a spreadsheet to simplify the calculation of total returns, saving yourself time and increasing the efficiency of True Blue Financial Solutions.

Your manager has tasked you with calculating the total dollar return and total percentage return for each stock, as well as identifying the stock with the highest return. However, you want to go above and beyond by also determining what percentage of the stocks in the dataset outperformed the S&P 500 index. To visualize this information, you plan to use a histogram to display the distribution of percentage returns, like the image below:

**Instructions**

Ultimately your objectives are to:

- Calculate the total dollar return and total percentage return of each stock for the entire time period. First, create a new sheet with each ticker listed once. For each ticker, bring in the correct prices for the first and last days that each ticker was in the dataset.
## Suggestions and Hints

- Use the Unique() function or Data → Data Tools → Remove Duplicates to get the list of tickers.
- Use Xlookup to find the starting and ending prices. You can use the “Search mode” parameter to ensure you get the first and last prices in the dataset.
- Calculate the difference between the starting and ending prices.
- Divide the difference by the starting price to calculate the % return.

- Use a formula to identify the stock with the highest percentage return.
## Suggestions and Hints

- You can use a combination of Xlookup() and Max() to use one cell to return the stock with the highest % return.

- Identify what percentage of stocks in this dataset outperformed the S&P 500’s return (0.7856 or 78.56% for this time period).
## Suggestions and Hints

- You can use a combination of Countifs() and Count() to accomplish this.

- Create a histogram of ‘% return’ including a title, labels, and a vertical line to show where the S&P 500 return lies on the distribution. (If you are completing this challenge in Excel, do not add the vertical line to the Histogram)
## Suggestions and Hints

- Learn more about histograms here.
- I recommend increasing the bins to something like 150 or 200 to better visualize the data.
- The labels may not look very clean, but I found that they are hard to enhance in Excel’s histogram.
- Instead of a line, you can change the bar color of the group that includes the S&P 500 return to visualize the proportion of stocks that performed better and worse than the index.

**Data Files**

**Solution**

Challenge72_Solution.xlsx

*Solution Video:* Challenge 72|EXCEL – True Blue Investing