65|PYTHON – Investment Investigator

Challenges
, , , ,
1

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

We provide the solution to this challenge using:

  • Python

Need a program? Click here.

Overview
Your friend is a savvy investor with a passion for picking individual stocks. They have meticulously tracked their investments in an Excel workbook on the “Portfolio” sheet, but they neglected to record the stock prices at the time of purchase and sale. How do they even know if they made money?!

That’s where you come in. As a Python guru with a knack for data analysis, your friend gave you their “Portfolio” spreadsheet. You quickly realized you could help fill in the gaps and evaluate their investment results. After carefully examining your friend’s data, you discovered a pattern in their investment strategy. They purchased a new security every week, holding onto it for almost two years, and only focused on the tickers instead of the company names. Additionally, they always scheduled purchases to finalize at market open and sales to occur at market close.

Seeing an opportunity to improve this investing nightmare (shh, don’t tell your friend), you decided to offer your assistance. With your Python knowledge and genuine care for your friend, you embarked on a journey to evaluate their investing.

To summarize your friend’s stock trading strategy/habits:

  • They kept track of stock tickers, purchase and sale dates, but not prices
  • They purchased a new security weekly and held for about two years
  • They did not track company names, only tickers
  • Their brokerage service scheduled purchases for next available trading date if market was closed on “Desired Purchase Date”
  • Their purchases always scheduled to finalize at market open price
  • Their brokerage service scheduled sales for next available trading date if market closed on “Desired Sale Date”
  • They always scheduled sales to occur at market close to receive the closing price for sold stocks
  • You were able to find the company names for most of the tickers in that data.

Instructions
Complete the following:

  1. Load every sheet from the data file
    Suggestions and Hints
    • Use pandas’ read_excel() function.
  2. For each ticker in the portfolio dataframe, add the correct company name to the portfolio dataframe. If no company name exists, write “Company Name Unavailable”.
    Suggestions and Hints
    • Iterate through each row of the “Portfolio” dataframe using a “for” loop and access the ticker for each row.
    • Use pandas filtering methods to find the corresponding company name from the “Company_Names” dataframe by matching the ticker.
    • If there is a match, assign the company name to the “Company Name” column in the “Portfolio” dataframe.
    • If there is no match, assign “Company Name Unavailable” to the “Company Name” column in the “Portfolio” dataframe.
  3. Find the purchase and sale prices by using dataframes and the iloc method
    Suggestions and Hints
    • Iterate through each row of the “Portfolio” dataframe again and access the ticker, desired purchase date, and desired sale date for each row.
    • Use pandas filtering methods to find the corresponding stock prices for the ticker on the purchase and sale dates in the “5_Yr_Stock_Price_Data_2013-2018” dataframe.
    • Assign the purchase price and sale price to the “Purchase Price” and “Sale Price” columns in the “Portfolio” dataframe.
    • Learn more about iloc here
  4. Calculate the total dollars spent and total dollars received.
    Suggestions and Hints
    • Use pandas to multiply the “# Shares Purchased” column by the “Purchase Price” column and assign the result to a new “Total dollars spent” column.
    • Then, multiply the “# Shares Sold” column by the “Sale Price” column and assign the result to a new “Total dollars received” column.
  5. Calculate the total gain (loss)
    Suggestions and Hints
    • Subtract the “Total dollars spent” column from the “Total dollars received” column and assign the result to a new “Total Gain (Loss)” column.
  6. Save the updated dataframe to an Excel file to double check your work.
    Suggestions and Hints
    • Use pandas’ to_excel() function to save the updated “Portfolio” dataframe to a new Excel file.
    • Learn more about reading and writing Excel files in pandas here

Data Files

Solution

Solution Code 
import pandas as pd 

# specify path of Excel file containing data 
path = "INSERT FILE PATH" 

# Step 1: read in data from different sheets of the Excel file 
Portfolio = pd.read_excel(path, "Portfolio") 
Data = pd.read_excel(path, "5_Yr_Stock_Price_Data_2013-2018") 
Companies = pd.read_excel(path, "Company_Names") 

# Steps 2 & 3 

# Step 2: Add the company names  
# Initialize empty lists to hold purchase prices, sale prices, and company names 
purchase_prices = [] 
sale_prices = [] 
company_names = [] 

# loop over rows in the Portfolio dataframe 
for i, row in Portfolio.iterrows(): 

    # get the ticker from the current row 
    ticker = row['Ticker'] 

    # look up the corresponding company name in the Companies dataframe 
    matches = Companies[Companies['Tickers'] == ticker] 
    if not matches.empty: 
        company_name = matches['Company Name'].iloc[0] 
    else: 
        company_name = "Company Name Unavailable" 

#Step 3: find the purchase and sale prices 
    # get the purchase and sale dates from the current row 
    purchase_date = row['Desired Purchase Date'] 
    sale_date = row['Desired Sale Date'] 

    # look up the purchase and sale prices from the Data dataframe 
    purchase_price = Data[(Data['Name'] == ticker) & (Data['date'] >= purchase_date)].iloc[0]['open'] 
    sale_price = Data[(Data['Name'] == ticker) & (Data['date'] >= sale_date)].iloc[0]['close'] 

    # append the purchase and sale prices, as well as the company name, to the appropriate lists 
    purchase_prices.append(purchase_price) 
    sale_prices.append(sale_price) 
    company_names.append(company_name)     

# add new columns to the Portfolio dataframe for purchase price, sale price, and company name 
Portfolio['Purchase Price'] = purchase_prices 
Portfolio['Sale Price'] = sale_prices 
Portfolio['Company Name'] = company_names

# Steps 4 & 5 

# Step 4 
# calculate total dollars spent, total dollars received, and total gain (loss) for each stock in the Portfolio dataframe 
Portfolio['Total dollars spent'] = Portfolio['# Shares Purchased'] * Portfolio['Purchase Price'] 
Portfolio['Total dollars received'] = Portfolio['# Shares Sold'] * Portfolio['Sale Price'] 

# Step 5 
Portfolio['Total Gain (Loss)'] = Portfolio['Total dollars received'] - Portfolio['Total dollars spent'] 

#Just for fun: 
print(f"Your friend spent ${Portfolio['Total dollars spent'].sum()} investing") 
print(f"Your friend made ${Portfolio['Total Gain (Loss)'].sum()} with their strategy") 
print(f"That's a {round((Portfolio['Total Gain (Loss)'].sum())/(Portfolio['Total dollars spent'].sum()),3)*100}% return!") 
 
# print the first five rows of the Portfolio dataframe 
Portfolio.head() 

#Step 6 

# write the Portfolio dataframe to an Excel file 
Portfolio.to_excel(INSERT FILE PATH, index=False)

Challenge65_Solution.txt
Solution Video: Challenge 65|PYTHON – Investment Investigator