Find the profit and loss percent in the given Excel sheet using Pandas

Profit and loss percent is an important financial metric that helps in analyzing the profitability of a business. It is calculated by finding the difference between the total revenue and total cost, and then dividing that value by the total cost. In this technical blog, we will learn how to find the profit and loss percent in a given Excel sheet using Pandas. We will be using the same Excel sheet that we used in our previous blog post on finding profit and loss.


  • Import the Pandas library and read the Excel sheet using the read_excel() function.

  • Apply the following calculations to each row to determine total income and total cost −

    • Total Cost = Total Units Bought * Cost of Each Unit

    • Total Revenue = Total Sold Units * Selling Price of Each Unit

  • Use the following formula to determine the profit or loss for each row −

    • Profit/Loss = Total Revenue - Total Cost

    • Calculate the profit and loss percent for each row using the following formula −

    • Profit/Loss Percent = (Profit/Loss / Total Cost) * 100


To handle excel files in Python using Python, you need to install the openpyxl dependency. To do this, open your terminal and type the command −

pip install openpyxl

After successful installation you can proceed with experimenting with Excel files and spreadsheets.

To download the Excel spreadsheet used in the exercises below, please check out this link


import pandas as pd

# Reading the Excel sheet
df = pd.read_excel('sales.xlsx')

# Calculating the Total Cost and Total Revenue
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']

# Calculating the Profit/Loss
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']

# Calculating the Profit/Loss Percent
df['Profit/Loss Percent'] = (df['Profit/Loss'] / df['Total Cost']) * 100

# Displaying the final DataFrame


Units Purchased	Unit Cost	Units Sold	 Unit Price	  Item Name	Total Cost	Total Revenue	Profit/Loss	Profit/Loss Percent
    50	            5.00	       40	       9.00	      Apples	    250.00	   360.0	     110.00	  44.000000
    100	            3.50	       80	       7.00	     Oranges	    350.00	   560.0	     210.00	  60.000000
    25	            12.00	       20	       15.00	  Pineapples	    300.00	   300.0	       0.00	   0.000000
    75	            1.75	       60	       3.50	     Bananas	    131.25	   210.0	      78.75	  60.000000
    200	            0.50	       180	       1.25	     Carrots	    100.00	   225.0	     125.00	 125.000000
    450	            2.00	       120	       4.50	    Potatoes	    900.00	   540.0	    -360.00	 -40.000000
    40	            8.00	       30	       12.00	    Avocados	    320.00	   360.0	      40.00	  12.500000
    80	            1.50	       70	       3.00	    Tomatoes	    120.00	   210.0	      90.00	  75.000000
    300	            20.00	       25	       25.00	     Mangoes	   6000.00	   625.0	   -5375.00	-860.000000
    60	            4.00	       45	       8.00	      Grapes	    240.00	   360.0	     120.00	 100.000000
  • The Excel file "sales.xlsx" is read for data using the pandas read_excel() function.

  • By conducting arithmetic operations on two of the columns that are already present in lines 6 and 7, two additional columns are added to the DataFrame which are named “Total Cost” and “Total Income”.

  • The profit or loss is calculated by dividing the total cost by the total revenue. Following that, the benefit/misfortune is figured by partitioning it by the whole expense, then increasing the outcome by 100 to get the benefit/misfortune rate.

  • The terminal outputs the resulting DataFrame.


  • If you require to calculate profit and loss data and display it in a tabular format, the aforementioned code snippet can be useful, i.e. Businesses trying to track expenditures and evaluate overall performance can find it useful.

  • With the assistance of the tabular format, businesses can easily compare and assess their profit and loss numbers over a certain time, enabling them to confidently decide on their financial plan.

  • The code snippet is a versatile tool for financial analysis since it may be altered to satisfy the particular needs of a business and therefore this code may also be used by individuals to monitor their personal finances, including their assets and outgoings.


Pandas offers a dependable and effective method for figuring out an Excel sheet's profit and loss percentage. Businesses and individuals may quickly get useful insights into their financial performance by using Pandas to read in and change the data. Financial analysis requires the capacity to determine profit and loss percentages, and using Pandas helps expedite and improve this process. As a result, Pandas is a useful tool for anybody handling financial research and data.

Updated on: 09-May-2023


Kickstart Your Career

Get certified by completing the course

Get Started