How to get Excel data using Python?

I already showed You how to get data from Excel file using ListObject and using SQL query in VBA. To make things more interesting I thought it is time for something way more different. In this article I’m going to shortly present how to get Excel data using Python.

Today we will use the same example data, as we used for the previous methods mentioned above.

The exercise

Our goal is to get the amount of rows, which meet the criteria of given country and minimum manufacturing price (same as in previous artciles).

Python code editor

I have installed PyCharm, which is code editor dedicated for Python. In this article I will not guide You through installation of that. Of course this doesn’t has to be PyCharm, it can be other code editor like for example Visual Studio Code.

Code

At the begining of the project, You need to import the Pandas library. This library is essential for Excel data. It’s like a connection between Python & Excel. It’s like You tick the checkbox in the VBE References, but in Python world.

import pandas as pd

Just like in previous articles You want to create a function, so start with defining function with 2 parameters – country and manufacturing price as mentioned in the exercise goal.

Note that in Python You need to use def for sub or function. In VBA You need Sub for Subroutine or Function for function.

def countRowsForGivenParams(country: str, manufacturing_price: int):

Note also that there is no need to declare variables in Python. The same goes with VBA, but I really prefer to declare everywhere it is possible.

Then load Excel data into Pandas pd using read_excel function – at least give the file path (including file name) to Financial Sample data and the name of the worksheet. The workbook will be loaded without opening of the Excel file.

financial_sample = pd.read_excel(io=r"C:\Users\plocitom\Desktop\Financial Sample.xlsx", sheet_name="Sheet1")

And just like that You created DataFrame – builded Pandas object.

Later create filter made out of 2 conditions – specified country and specified manufacturing_price.

Note that in Python You need to use double equal sign == to compare values, only 1 equal sign to assign the value into variable. In VBA in both cases You only need to use 1 equal sign.

df_filter = (financial_sample['Country'] == country) & \
            (financial_sample['Manufacturing Price'] > manufacturing_price)

Note also that in Python You can or even should use & sign instead of And logic operator.

After that put the filter inside the financial_sample and check the length of filtered Data Frame using len function.

result = len(financial_sample[df_filter])

In the end return the results, using keyword return, in the same statement as in previous articles.

Note that in Python You can do this in 1 string variable using {} parenthesis to put inside the variables values and putting f before the string. In VBA it is not really possible.

return f'Country: {country}; Manufacturing Minimum Price: {manufacturing_price}; Amount: {result}'

Note also that in Python to return the value from function You can use keyphrase return. In VBA to return function value You need to use its name and assign the value to it.

Function call

To call out the function, get and check the results use print function and Run the code.

print(countRowsForGivenParams('Canada', 100))
print(countRowsForGivenParams('Germany', 200))

Note that print in Python is like Debug.Print in VBA.

The returned results are below:

Country: Canada; Manufacturing Minimum Price: 100; Amount: 60
Country: Germany; Manufacturing Minimum Price: 200; Amount: 40

Process finished with exit code 0

The results here in Python are the same as in ListObject and ADODB SQL method in VBA.

Gathering all together:

import pandas as pd


def countRowsForGivenParams(country: str, manufacturing_price: int):
    financial_sample = pd.read_excel(io=r"C:\Users\plocitom\Desktop\Financial Sample.xlsx", sheet_name="Sheet1")

    df_filter = (financial_sample['Country'] == country) & \
                (financial_sample['Manufacturing Price'] > manufacturing_price)
    
    result = len(financial_sample[df_filter])

    return f'Country: {country}; Manufacturing Minimum Price: {manufacturing_price}; Amount: {result}'


print(countRowsForGivenParams('Canada', 100))
print(countRowsForGivenParams('Germany', 200))

Summary

As You can see there are lots of methods to achieve the goal, not only in VBA. Also to get Excel data, there are other ways like using for example Pandas library in Python. Hope You find best fit for yourself!