84|PYTHON – First Quarter Crisis

BYU Student Author: @Hyrum
Reviewers: @Nate, @Mike_Paulsin, @Parker_Sherwood
Estimated Time to Solve: 20 - 30 Minutes

We provide the solution to this challenge using:

  • Python

Need a program? Click here.

Overview
As a problem solver for a growing accounting firm, your boss has presented you with an exciting opportunity: to create a Python program that will revolutionize their financial management. Your task is to build a program that can calculate the total revenue and expenses for a small business in its first quarter, providing insights into the company’s performance.

In this challenge, you will put your coding skills to the test by utilizing a simulated small business dataset that mirrors real-life financial data. By completing this challenge, you will not only improve your coding proficiency, but also help your accounting firm achieve new levels of financial success through accurate and efficient data analysis.

Instructions
Your program should do the following:

  • Calculate the total revenue and expenses for each category (The other information is not relevant)
  • Combine each month into one quarter
  • Print the total revenue, total expenses, and whether the business is making a profit or a loss for the quarter
  • Allow the user to export the results to an Excel file containing the combined data

Data Files

Suggestions and Hints

Use Pandas for uploading and downloading into python as a dataframe

Solution

Solution Code
import pandas as pd 

# Read transactions from Sheet1, Sheet2, and Sheet3 
df1 = pd.read_excel('ChallengeX_First_Quarter_Crisis.xlsx', sheet_name='Month1') 
df2 = pd.read_excel('ChallengeX_First_Quarter_Crisis.xlsx', sheet_name='Month2') 
df3 = pd.read_excel('ChallengeX_First_Quarter_Crisis.xlsx', sheet_name='Month3') 

# Concatenate the four DataFrames vertically 
df = pd.concat([df1, df2, df3], ignore_index=True) 

# Calculate total revenue and expenses 
revenue = df[df['amount'] > 0]['amount'].sum() 
expenses = df[df['amount'] < 0]['amount'].sum() 
profit_loss = revenue + expenses 

# Print results 
print(f"Total Revenue: {revenue}") 
print(f"Total Expenses: {expenses}") 
if profit_loss >= 0: 
    print(f"Profit: {profit_loss}") 
else: 
    print(f"Loss: {profit_loss}") 

# Export to Excel file 
writer = pd.ExcelWriter('combined_transactions.xlsx') 
df.to_excel(writer, index=False, sheet_name='Combined Transactions') 
writer.save() 

This code reads data from four sheets named " Month 1", " Month 2", and “Month3” in an Excel file named “ChallengeX_Dataset.xlsx”. It then concatenates the four data frames vertically to create a single DataFrame named “df”.

The code then calculates the total revenue and expenses by filtering the “amount” column of the DataFrame for positive and negative values, respectively, and computing the sums. It also calculates the profit or loss by adding the revenue and expenses together.

Finally, the code exports the combined transactions DataFrame to an Excel file named “combined_transactions.xlsx” and saves it on the disk.

Solution Video: Challenge 84|PYTHON – First Quarter Crisis

Hi Hyrum,
Thank you for a great, short-and-sweet challenge! I love the setup and the skills you required us to utilize. I did it slightly differently. I aggregate the data using groupby and created a new data frame for the summary data. Then, I created a new column for the profit/loss.

I’m not a coding wizard, so this solution isn’t perfect, but I’ve attached it below!

After reading in each month’s sheet into a separate pandas dataframe, I concatenated the three, made two separate subsets of that data for the expense and revenue data, then set variables equal to the total expense and revenue amounts. I also made an if statement to tell the user if the company had a profit or loss that quarter, and I exported the concatenated data to an Excel file.


Here is my code for this challenge:

Great challenge Hyrum! This is the code I wrote for this challenge:

Great challenge! The code I used to solve the challenge is below: