72|EXCEL – True Blue Investing

[Parker_Sherwood]

BYU Student Author: @Parker_Sherwood
Reviewers: @Jae, @Mark
Estimated Time to Solve: 45 Minutes

We provide the solution to this challenge using:

• Excel
• Go here to perform this challenge in Python

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:

1. 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.
2. 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.
3. 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.
4. 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

• Challenge72_Data.xlsx
• Challenge72_Data_Dictionary.docx

Solution

Challenge72_Solution.xlsx
Solution Video: Challenge 72|EXCEL – True Blue Investing

[Dallin_Hwang]

Here is my solution. Wasn’t able to upload excel file because it was too large. So A picture will have to do.

[Meg_McEachern]

The file was too big so I attached a screenshot. I found that the XLOOKUP function was a lot more useful that the VLOOKUP is. It was also really helpful to nest functions like MAX, SUM, and Count if

[Elizabeth_M_Wheatley]

I’ve had the same problem with my file size being too large, but I was able to complete the problem. Here’s a picture. It took me a minute to remember how to use some of the functions, so I’m so glad I was able to work through it

[Peter_Warnick]

I guess I am not as competent with excel as I thought! This is labeled as beginner and I really struggled! took me almost an hour and a half.

[Elise_Snow]

I like how you color coordinated your spreadsheet. It is much easier to read.

[Andrew_Wilson]

This was a good refresh on the usefulness of XLookup. It was a fun challenge!

I started by using xlookup to pull data from the first sheet and organizing it. Next I calculated returns and found the top performing stock and the comparison to the S&P500. Here is my finished product

This text will be blurred

[Bailey_Moore]

Using XLOOKUP made me wonder when I would use VLOOKUP again–super useful! It was nice to review combining multiple functions to return a single cell value.

[Keyyan_Lugo]

Here is my solution to the challenge. The file was too big to upload. I had a hard time with this, but I wanted to do it to challenge myself. Took me much longer than it should have to figure out, but I got it in the end.

[Kyle_Scott]

Here’s my solution to the challenge. XLOOKUP is a life saver!

[Hilary_Trochez]

This challenge took me almost 2 hours. I had forgotten everything on Xlookup and struggled to get my horizontal axis on the histogram to match but I finally got it!

[Braedan_Fowler]

Here was my attempt.

[Alexander_Smith]

Here is my screenshot solution!

[jason_ng]

It took me awhile to adjust my formulas in accordance to the suggested hints. This is what I got so far.

[Logan_Robison]

The file size for the solution was too big, so here’s a screenshot instead. Not going to lie this one took me a while. My major obstacle was that I was not previously familiar with the ins and outs of XLOOKUPS, so it took me a bit of research before I figured out the best way to get the first and last day prices. I initially tried finding them with a concatenated key, which would have worked had all the stock prices started and ended being collected on the same days. Fortunately, I was able to find a better solution in the XLOOKUP itself by returning the first and last values for the relevant data. It was a good exercise. I learned a lot.

[Chase_Halverson]

Challenge72_Data.xlsx (36.2 KB)
Thanks for the practice. Here is my solution. I had to delete the data page because the file was too big, causing me to replace my formulas with the values they produced.

[Nathan_Strong]

Thanks for sharing your thought process and how you were resourceful in this process. It’s cool to hear about the power of XLOOKUP. Great job on the chart!

[Alexander_Jimenez]

[James_Gerstner]

Here is my solution:

I had to do some research to understand that an XLOOKUP is able to grab only one value (instead of a range), which then made finding the start and end dollar amounts much easier. From there, it was pretty straightforward!

[Dakin_Rust]

Here is my solution! I used Xlookups, Counts, and CountIfs as suggested. I didn’t have much experience with histograms, so that piece was good practice.