Python in Excel: How to create custom date ranges and metrics

In previous posts, we explored building interactive visualizations in Excel, including interactive window functions like moving averages and dynamic measures:

In this post, we’re going to merge these concepts and delve into dynamic window functions. We’ll create a workbook that allows users to visualize data as either a cumulative average or a cumulative sum. Plus, we’ll add functionality for users to control the date range.

To get started and follow along, download the exercise file provided below:

This is a relatively basic sales dataset stored in our Excel table named sales. Before I import this dataset into Python, I’m going to set up the user-driven elements of this visualization.

Specifically, I will ask the user to define the start date, the end date, and whether they prefer to see an average or a sum. We could also add data validation for the date ranges. For instance, I’ve included some basic data validation in a dropdown menu to allow the user to select either a cumulative average or a cumulative sum:

Next, I’ll set up all the variables needed in Python to define the analysis and create the plot. We’ll create separate variables for the dataset itself and then for the three user inputs:

Next, we need to prepare the source data for visualization by performing some data wrangling. First, I’ll filter the dataset to only include data within the specified date range. I’ll use the between() method for this filtering. Let’s store the filtered results in sales_df_filtered:

sales_df_filtered = sales_df[sales_df['Date'].between(start_date, end_date)]

Next, we’ll create a dynamic column called ‘cum_value’ to calculate the cumulative value based on user preferences. The expanding() method ensures that each new data point incorporates all previous data in its calculation.

By using getattr(), the code selects the calculation method—in this case, sum or mean—specified by the agg_type variable and applies it to the data. The resulting cumulative total or average is stored in the cum_value column. This setup allows the data to update dynamically according to what the user chooses to see.

sales_df_filtered['cum_value'] = getattr(sales_df_filtered['Sales'].expanding(), agg_type)()

Finally, let’s create a line chart to visualize the aggregated results over the specified date range. I’ll set up a dynamic chart title to indicate whether we’re displaying a total or an average. Additionally, I’ll adjust the size of the plot to make it wider, which will help us see the trends over time more clearly and provide more space for the x-axis labels. This makes it easier to follow the data trends.

if agg_type == 'sum':
    title = 'Cumulative Total Sales Over Time'
elif agg_type == 'mean':
    title = 'Cumulative Average Sales Over Time'

plt.figure(figsize=(12, 5)) 
plt.plot(sales_df_filtered['Date'], sales_df_filtered['cum_value'])
plt.title(title)

You can view the completed code and its results. Be sure to download the exercise file to try it out for yourself!

What questions do you have about this specific example or about using Python in Excel for data visualization more broadly? Is there a particular interactive feature you’re interested in creating, and you’re wondering if Python is a viable option for building it? Let me know in the comments.