Category: Excel

What will we cover in this tutorial?

How to create excel report automation with Python. This will be done on the data similar to this.

And we will create Excel reports from Python like these ones.

Get direct Download link to JuPyter Notebook and Excel sheet used in YouTube video and follow along.

Step 1: Load the data in Pandas DataFrame

Pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.

Pandas has great integration with Excel – both reading and writing Excel files. We will use that in this tutorial.

To read the data simply use the read_excel(…).

import pandas as pd

data = pd.read_excel("SalesData.xlsx", index_col="Date")
print(data.head())

Which will result in output similar to this.

	Sales rep	Item	Price	Quantity	Sale
Date					
2020-05-31	Mia	Markers	4	1	4
2020-02-01	Mia	Desk chair	199	2	398
2020-09-21	Oliver	Table	1099	2	2198
2020-07-15	Charlotte	Desk pad	9	2	18
2020-05-27	Emma	Book	12	1	12

Step 2: Group the data by Sales rep

To identify how much each Sales rep has sold, we will group it and calculate the sum.

repr = data.groupby("Sales rep")
repr_sales = repr['Sale'].sum()

Now that is too simple, right?

The data in rep_sales looks like this.

Sales rep
Charlotte     74599
Emma          65867
Ethan         40970
Liam          66989
Mia           88199
Noah          78575
Oliver        89355
Sophia       103480
William       80400
Name: Sale, dtype: int64

Step 3: Group the data by month, change to month names and sum it

This is a bit more challenging, as we need to group the dates in months and rename the index to month names.

Except if you know how to do it, as you will in a moment.

months = data.groupby(pd.Grouper(freq="M"))
months_sales = months['Sale'].sum()
months_sales.index = months_sales.index.month_name()

You are awesome. See, you use groupby on a Grouper with frequency month (“M”). To use the month name on index and not the last day of month, you reassign the index to use index.month_name().

This creates the following output where you can see the month names are used.

Date
January      69990
February     51847
March        67500
April        58401
May          40319
June         59397
July         64251
August       51571
September    55666
October      50093
November     57458
December     61941
Name: Sale, dtype: int64

Step 4: Create Excel sheet with the data from Step 2 and 3

This is done by creating an Excel writer.

writer = pd.ExcelWriter("SalesReport.xlsx")
repr_sales.to_excel(writer, sheet_name="Sale per rep")
months_sales.to_excel(writer, sheet_name="Sale per month")
writer.close()

This will create an Excel sheet of name SalesReport.xlsx, with two sheets Sale per rep and Sale per month, respectively.

But let’s try to add the two charts.

Step 5: Adding the charts

This is a bit more involved. As you see, the above steps have been quite straight forward and not required any high level Python programming skills (or Excel, except you need to know how to open an Excel sheet).

writer = pd.ExcelWriter("SalesReport.xlsx")
repr_sales.to_excel(writer, sheet_name="Sale per rep")
months_sales.to_excel(writer, sheet_name="Sale per month")

chart = writer.book.add_chart({'type': 'column'})
chart.add_series({
    'values': '=\'Sale per rep\'!$B$2:$B$10',
    'categories': '=\'Sale per rep\'!$A$2:$A$10',
    'name': "Sale"
})
writer.sheets['Sale per rep'].insert_chart("D2", chart)

chart = writer.book.add_chart({'type': 'column'})
chart.add_series({
    'values': '=\'Sale per month\'!$B$2:$B$13',
    'categories': '=\'Sale per month\'!$A$2:$A$13',
    'name': "Sale"
})
writer.sheets['Sale per month'].insert_chart("D2", chart)

writer.close()

This will create the sheets we have been waiting for. The charts are dynamic, meaning, that if you change the values the charts will change accordingly.

Final remarks

Remember to see the YouTube video, which explains the material in more depth.

If you like the above content, please share it on a social media or point a friend in this direction.

What will we cover in this tutorial?

We will learn how you can dynamically format the cells in Excel to have a specific color based on the value of a function – all done from Python.

As an example we will color the cells of two columns depending on which is larger.

In this concrete example we will collect historical stock prices and calculate the Moving Averages of 50 and 200 days on the closing price. In a crossover way to utilize moving averages, you would send a buy-signal when the 50-day moving average crosses above the 200-day moving average and a sell-signal if the other way around.

Step 1: Collect the data from Yahoo! Finance using Pandas-datareader

A great library to use to collect financial data is the Pandas-datareader.

We will collect data from Yahoo! Finance. As an example we will use General Motors historical stock data.

This can be done as follows.

import pandas_datareader as pdr
import datetime as dt


start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)

This will result in a Pandas DataFrame with data similar to this.

                 High        Low       Open      Close      Volume  Adj Close
Date                                                                         
2019-01-02  33.810001  32.410000  32.849998  33.639999   7662300.0  31.893360
2019-01-03  33.680000  32.200001  33.340000  32.250000  11618300.0  30.575533
2019-01-04  33.570000  32.430000  32.799999  33.330002  10784000.0  31.599455
2019-01-07  34.970001  33.480000  33.709999  34.360001  10722300.0  32.575977
2019-01-08  35.680000  34.490002  35.000000  34.810001  10532900.0  33.002617

Step 2: Calculate the Moving Averages

The moving average can be calculated by applying the rolling() and mean() methods on the DataFrame.

The rolling() creates a window size which is the number of observations used for calculating the statistic.

To calculate the Moving Average of window size 50 will use the last 50 data points to calculate the mean. To be consistent with Yahoo! Finance way of calculate the value, they use the Close price.

import pandas_datareader as pdr
import datetime as dt
import xlsxwriter
import pandas as pd


start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)

ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()

Which will give a tail similar to this output.

                 High        Low       Open  ...  Adj Close     MA50     MA200
Date                                         ...                              
2020-12-14  42.540001  41.419998  42.490002  ...  41.619999  38.5632  29.03155
2020-12-15  42.160000  41.310001  41.990002  ...  41.660000  38.7772  29.08725
2020-12-16  41.869999  40.810001  41.790001  ...  41.419998  38.9976  29.13670
2020-12-17  42.029999  41.430000  41.709999  ...  42.029999  39.2058  29.19635
2020-12-18  42.042801  41.139999  42.020000  ...  41.389999  39.3894  29.25985

Where the Moving Averages are added to the DataFrame.

Step 3: Exporting to Excel using XlsxWriter

We will use the powerful XlsxWriter to create our Excel sheet.

You can write the DataFrame directly by using to_excel(…), but we want to do more than that. Hence, we use the XlsxWriter directly.

import pandas_datareader as pdr
import datetime as dt
import pandas as pd


start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)

ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()

# We only want the index as a date and not datetime object
ticker.index = ticker.index.date

# We skip the first 200 entries, as it they do not have the MA200
ticker = ticker.iloc[200:]
# We reverse the DataFrame to have newest first
ticker = ticker.iloc[::-1]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('moving_average.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
ticker.to_excel(writer, sheet_name='Moving Averages')

# Remember to close the writer to write the Excel sheet
writer.close()

This will create an Excel sheet similar to this one.

Step 4: Adding formatting to the cells

This is a bit more complex as we need to do the magic here.

We first need to create a reference to the sheet (worksheet) we work on to access the functionality.

The first thing we do is to resize column A, such that it opens in the correct size to see the date. This is done by using set_column(…).

Then we create a format, which can be applied on Excel cells. This is how we color them. Hence, we create one for green cells and later one for red cells.

We insert them by using conditional_format(…), which a range of cells. This can be applied in different ways, we use the A1 notation.

The options parameter in conditional_format(…) must be a dictionary containing the parameters that describe the type and style of the conditional format. To see details of the format please refer to the manual.

import pandas_datareader as pdr
import datetime as dt
import pandas as pd


start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)

ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()

# We only want the index as a date and not datetime object
ticker.index = ticker.index.date

# We skip the first 200 entries, as it they do not have the MA200
ticker = ticker.iloc[200:]
# We reverse the DataFrame to have newest first
ticker = ticker.iloc[::-1]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('moving_average.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
ticker.to_excel(writer, sheet_name='Moving Averages')

# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet = writer.sheets['Moving Averages']
# Resize the column A
worksheet.set_column("A:A", 12)

# Calculate the last row number (we insert first DataFrame row in row 2)
last_row = len(ticker) + 1

# Create a format for a green cell
cell_format_green = workbook.add_format()
cell_format_green.set_bg_color('green')

# Create a conditional formatted of type formula
worksheet.conditional_format('H2:I{}'.format(last_row), {'type':     'formula',
                                                         'criteria': '=$H2>=$I2',
                                                         'format':   cell_format_green})

# Create a format for a red cell
cell_format_red = workbook.add_format()
cell_format_red.set_bg_color('red')

# Create a conditional formatted of type formula
worksheet.conditional_format('H2:I{}'.format(last_row), {'type':     'formula',
                                                         'criteria': '=$H2<$I2',
                                                         'format':   cell_format_red})

# Remember to close the writer
writer.close()

This will result in the expected Excel sheet.

Step 5: Validating it works as expected

Now for the final test.

The colors should be updated dynamically. Let’s try to change the value and see what happens.

It does. If we change the value of H2 to 10, it turns the cells to red.

What will we cover in this tutorial?

In this tutorial we will show how you can concatenate CSV files of the same format and export it to an Excel sheet. All done in Python by using Pandas.

This is something you want in your Python toolbox as it will make it easy for you to get quick wins helping people with manual work.

Step 0: Preparation

We need multiple CSV files in this tutorial. If you have CSV files with the same format, then you can skip to the next step.

First, let’s create some random data by using the power of NumPy. This can be done by using the uniform method in the NumPy random package.

import numpy as np
import pandas as pd


for file_name in ['data_0.csv', 'data_1.csv', 'data_2.csv']:
    df = pd.DataFrame(np.random.uniform(0, 100, size=(100, 4)), columns=list('ABCD'))
    df.to_csv(file_name)

As you see, we use the nice way to generate the random data by calling the random.uniform, which takes the lower bound and upper bound of the uniform interval to get numbers from, and the size parameter is the dimension of the NumPy array. Here it generates 100 rows in 4 columns.

The DataFrame constructor can convert the NumPy array to the DataFrame and setting the column names to A, B, C, and D, respectively. This is done by converting the the string ‘ABCD‘ to a list, resulting in [‘A’, ‘B’, ‘C’, ‘D’].

The content is stored to a csv file by simply calling to_csv on the DataFrame object.

The files will look similar to this (only the beginning of one here).

,A,B,C,D
0,59.74500080886668,59.63123820144265,47.53898343676622,50.31013442167491
1,31.00881083223196,30.91970178258706,93.2538048567809,41.95421609366061
2,32.03557607643186,96.34180274801857,70.8926221704894,90.79417905937711
3,22.44980451436659,61.25077305144971,24.013823071196082,31.401361361506062
4,71.03827853534764,20.527320250264925,29.379395377514662,94.89076442880472
5,76.1462091142596,20.509207361552917,66.57572207423806,69.38906836001392

Step 1: Read a CSV file with Pandas

Now it is time for the fun stuff. Read one csv file into a Pandas DataFrame.

This can be done by calling the read_csv method in the Pandas library.

import pandas as pd


df = pd.read_csv("data_0.csv")
print(df)

This gives the following output.

    Unnamed: 0          A          B          C          D
0            0  59.745001  59.631238  47.538983  50.310134
1            1  31.008811  30.919702  93.253805  41.954216
2            2  32.035576  96.341803  70.892622  90.794179
3            3  22.449805  61.250773  24.013823  31.401361
4            4  71.038279  20.527320  29.379395  94.890764

Or the beginning of it.

Now we are ready to read all the content files and concatenate them.

Step 2: Concatenate Multiple DataFrames into One DataFrame

First we need to read all the csv files. This can be done in many ways. Here we will iterate over them and append them to a list.

After that we will concatenate them all.

This can be done by using the concat method in the Pandas library. The concat function takes an iterable as the the first argument. That is great, because a list is an iterable.

import pandas as pd


df_list = []
for file_name in ['data_0.csv', 'data_1.csv', 'data_2.csv']:
    df = pd.read_csv("data_0.csv")
    df_list.append(df)

df_concat = pd.concat(df_list)
print(df_concat)

Which results in a single DataFrame with 300 rows.

    Unnamed: 0          A          B          C          D
0            0  59.745001  59.631238  47.538983  50.310134
1            1  31.008811  30.919702  93.253805  41.954216
..         ...        ...        ...        ...        ...
98          98  65.831197  47.070415  65.205620  64.701177
99          99  27.259875  97.514085   8.444346  65.991040

[300 rows x 5 columns]

Notice that the index is reset for each concatenation. We can handle that by adding a ignore_index=True in the concat call.

import pandas as pd


df_list = []
for file_name in ['data_0.csv', 'data_1.csv', 'data_2.csv']:
    df = pd.read_csv("data_0.csv")
    df_list.append(df)

df_concat = pd.concat(df_list, ignore_index=True)
print(df_concat)

Which results in the following result.

     Unnamed: 0          A          B          C          D
0             0  59.745001  59.631238  47.538983  50.310134
1             1  31.008811  30.919702  93.253805  41.954216
..          ...        ...        ...        ...        ...
298          98  65.831197  47.070415  65.205620  64.701177
299          99  27.259875  97.514085   8.444346  65.991040

[300 rows x 5 columns]

Now ending at 299 rows.

Step 3: Write the output to an Excel file

This is simply done by using the to_excel method on the DataFrame object. It takes the file name as argument, and it should be of the format xlsx.

Notice, some installations of Pandas do not include the XlsxWriter module. Then you need to install that. You will know when you run the code below, it will say it is missing. Then you can install it by using the pip.

pip install xlsxwriter

Or if you are in PyCharm, you can simply write import xlsxwriter on a line and hover over the red line of xlsxwriter and select install. Then it should install it for you.

Now to the full code.

import pandas as pd


df_list = []
for file_name in ['data_0.csv', 'data_1.csv', 'data_2.csv']:
    df = pd.read_csv("data_0.csv")
    df_list.append(df)

df_concat = pd.concat(df_list, ignore_index=True)
df_concat.to_excel("output.xlsx")

This results in an Excel file similar to this.

What will we cover in this tutorial?

We will cover the basic process of saving data from a Pandas DataFrame to an Excel sheet. This includes

• The simple export of a DataFrame to Excel from Python.
• How to set the Sheet name in Excel from Python.
• To set which columns to export from the DataFrame to Excel.

Step 1: The simple export of a full DataFrame

Often you just need to figure out how to save the full DataFrame to an Excel sheet. This is often needed, as you have collected the data in Python, but want to explore it or work more on it in Excel.

If you are new to Pandas, we can recommend to read this tutorial.

Pandas is a great library to work with data similar to an Excel sheet. The advantage of Pandas is working with the data in Python and automate many things. That could be collecting data from API’s on the internet or simply automate the stuff you do manually in Excel.

Another beauty of Pandas is that it has great built in methods that does all the work for you.

Here we will demonstrate how a call to to_excel(…) can be done on a DataFrame.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx")

The code above creates a random DataFrame using the random function from NumPy.

Then it continues to call to_excel on the data frame with the output Excel file name. This will create an Excel sheet, which could look similar to this.

Step 2: How to set the sheet name in Excel from Python

Sometimes you want your data more organized. Here we are going to show how to set the sheet name where you export your data.

The default sheet name is Sheet1, hence in the previous step you would notice that the created sheet has that name.

Sometimes you want a different name. Luckily, this is simple.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx", sheet_name="My Sheet")

Notice that we only set the sheet_name parameter in the call. That is all it takes.

How did I know? I read the documentation.

This creates an Excel file with the desired sheet name.

Step 3: Only export the specific columns

Often it happens that you are only interested in exporting some of the columns in your DataFrame. Luckily, this is also simple to do from the standard export function on the DataFrame.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'C': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'D': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'E': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'F': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx", sheet_name="My Sheet", columns=['B', 'E', 'F'])

In the above example we create a DataFrame with a few columns. We only export columns B, E, and F.

This is set directly in the to_excel method, which can set the parameter columns. The parameter can accept a list of the column names to export to the Excel sheet.

Next step

This is only to get you started and see how simple it is to work with data from Python using Pandas. This enables you to make great things with only a few lines of code.