BYU Student Author: @Parker_Sherwood
Reviewers: @Nate, @Mike_Paulsin, @Spencer
Estimated Time to Solve: 60 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 Python. In this challenge, you’ll develop a Python program to automate 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.
Suggestions and Hints
- Import the necessary packages, including pandas, numpy, and pyplot from matplotlib.
- Read in the CSV file containing the stock data and create a Pandas dataframe.
- Convert the ‘date’ column to a datetime object and sort the dataframe by ticker and date.
- Drop any duplicate rows.
- Create a summary dataframe that contains the starting price (opening price on the earliest day that ticker appears in the original data) and the ending price (closing price on the latest day that ticker appears in the original data) for each ticker.
- Create a list of tickers to iterate through.
- Create dictionaries to store the starting and ending prices.
- Iterate through the list of tickers and find the starting and ending prices.
- Create dataframes with the starting and ending prices.
- Merge the starting and ending dataframes on the ‘Name’ column.
- Create ‘$ return’ (ending price – starting price) and ‘% return’ ($ return / starting price) columns, drop any that include divide by 0 errors.
- Identify the stock with the highest return.
Suggestions and Hints
- Print out the stock with the highest ‘% return’ by first identifying the highest % return by using the .max() method. The solution file then uses the .loc method to find the name associated with that return.
- Identify what percentage of stocks in this dataset outperformed the S&P 500 (0.7856 or 78.56% for this time period).
Suggestions and Hints
- Store the S&P 500’s return over this same time period as a variable.
- Create a dataframe of stocks that outperformed the S&P 500 and print the percentage of the sample this represents.
- 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.
Suggestions and Hints
- Use your summary data, not the outperformers, as the data for the histogram.
- Use plt.hist() to create the histogram.
- Use plt.axvline to add a vertical line for the S&P return.
- You can use “bins=” to change the number of bars in the histogram. This may help you visualize the data better.
Data Files
Solution
Solution Code
import pandas as pd
import numpy as np
# Import pyplot to create the histogram later
from matplotlib import pyplot as plt
#Read in the CSV and create dataframe
stock_data = pd.read_csv(#Put your file path here)
# Convert the date column to a datetime object
stock_data['date'] = pd.to_datetime(stock_data['date'])
#Sort dataframe by ticker and date
stock_data = stock_data.sort_values(by = ['Name', 'date'], ascending = [True, True], ignore_index=True)
# Drop any duplicate rows
stock_data.drop_duplicates(subset=['date', 'Name'], inplace=True)
#Print out the first five rows of the dataframe
# stock_data.head()
#create a list of tickers to iterate through
tickers = []
for ticker in stock_data.Name:
if ticker not in tickers:
tickers.append(ticker)
# Create dictionaries to store the starting and ending prices
starting_prices = {}
ending_prices = {}
# Iterate through the list of tickers and find the starting and ending prices
for ticker in tickers:
first_date = stock_data.loc[stock_data['Name'] == ticker, 'date'].min()
last_date = stock_data.loc[stock_data['Name'] == ticker, 'date'].max()
starting_price = stock_data.loc[(stock_data['Name'] == ticker) & (stock_data['date'] == first_date), 'open'].values[0]
ending_price = stock_data.loc[(stock_data['Name'] == ticker) & (stock_data['date'] == last_date), 'close'].values[0]
starting_prices[ticker] = starting_price
ending_prices[ticker] = ending_price
# Create dataframes with the starting and ending prices
starting_data = pd.DataFrame({'Name': list(starting_prices.keys()), 'starting_price': list(starting_prices.values())})
ending_data = pd.DataFrame({'Name': list(ending_prices.keys()), 'ending_price': list(ending_prices.values())})
# Merge the starting and ending dataframes on the 'ticker' column
summary_data = pd.merge(starting_data, ending_data, on='Name')
# Print the summary dataframe
# summary_data.head()
# Create the $ return and % return columns. Drop any that include divide by 0 errors
summary_data['$ return'] = summary_data['ending_price']-summary_data['starting_price']
summary_data['% return'] = summary_data['$ return']/summary_data['starting_price']
summary_data['% return'] = summary_data['% return'].replace('nan%', np.nan)
summary_data.dropna(subset=['% return'], inplace=True)
# summary_data.head()
# Find the stock and the % return of the stock with the highest return
max_return = summary_data['% return'].max()
max_return_stock = summary_data.loc[summary_data['% return'] == max_return, 'Name'].values[0]
print(f"The stock with the highest % return is {max_return_stock} with a return of {float(max_return):.2%}")
#Over this same time period, the S&P 500 had a total return of 78.56%.
#How many stocks outperformed the S&P 500? What percentage of stocks in this sample does that represent?
sp_return = 0.7856
# Create a dataframe of stocks that outperformed the S&P 500 and print the percentage of the sample this represents
high_performers = summary_data[summary_data['% return'] > sp_return]
high_performers = high_performers.sort_values(by = ['% return'], ascending = [False], ignore_index = True)
print(f"{float(len(high_performers)/len(summary_data)):.2%} of stocks in the dataset outperformed the S&P 500")
# Create a histogram based on % return including a title and labels
plt.hist(summary_data['% return']*100, bins = 200)
plt.title('% Returns')
plt.xlabel('% return')
plt.ylabel('Count of Stocks')
# Add a vertical line using axvline() method to show where the S&P 500 return lies on the distribution
plt.axvline(sp_return*100, color='black', linestyle='dashed', linewidth=0.5)
Challenge72_Solution.txt
Solution Video: Challenge 72|PYTHON – True Blue Investing
