Publications by George Mount
How to generate datasets with Copilot for Microsoft 365
As a trainer and content creator, I regularly generate fake datasets for exercises. This task can be more challenging than it sounds. Creating variables that follow a specific pattern or distribution was difficult with earlier random data distribution tools.Additionally, crafting a dataset that “feels” real for a given industry, domain, or prob...
9943 sym 4 img
Python in Excel: How to calculate investment returns
Whether you’re a seasoned financial analyst or a data-proficient professional, understanding the calculation of investment returns can help assess the profitability and risks of various assets. This post explores three core methods for calculating investment returns: normalized returns, daily returns, and logarithmic daily returns.The integration...
5625 sym 8 img
Python in Excel: How to upsample and interpolate time series data
Upsampling time series data involves increasing the frequency of your data points by filling in gaps to transform a lower-frequency dataset into a higher-frequency one. This process is often necessary when aligning datasets with different frequencies or when a more granular view of the data is required.For instance, in financial analysis, you might...
6302 sym 5 img
Python in Excel: How to work with missing values
Data analysts should be proactive about missing values in their data, as these can significantly impact analysis outcomes. First of all, many algorithms such as linear and logistic regression as well as decision trees require complete datasets and cannot inherently process missing data.But beyond statistical modeling and machine learning, missing v...
6012 sym 4 img
Python in Excel: How to find the top N values by group
Recently, I encountered a task in Excel where I needed to identify the top N values within each group. Generally, I’ve found that working with groups in Excel can be less than user-friendly for many tasks, and I was hopeful that some of the newer dynamic array functions might have improved this aspect.Unfortunately, I came up short. While there a...
5885 sym 3 img
Python in Excel: How to create interactive visualizations
One of the greatest wins for Excel users learning Python is access to a comprehensive suite of data visualization tools. However, compared to Excel, the default plots produced with Python in Excel can seem somewhat static. For example, there’s no straightforward way to add tooltips or interactive elements that allow users to click on parts of the...
5520 sym 8 img
Python in Excel: How to run a Monte Carlo simulation
Monte Carlo simulations leverage probability and randomness to simulate processes multiple times, exploring a wide range of possible outcomes. This approach can illuminate the inherent uncertainty and variability in business processes and outcomes.Integrating Python’s capabilities for Monte Carlo simulations into Excel enables the modeling of com...
13087 sym 6 img
How to create and modify Excel workbooks with openpyxl
Several packages facilitate working with Python and Excel, but openpyxl stands out as an excellent choice due to its comprehensive features for reading and writing workbooks. In this blog post, we will explore how to get started with creating Excel workbooks from scratch and modifying existing ones using openpyxl.For this lesson, I’ll assume you�...
3884 sym Python (339 sym/10 pcs) 1 img
How to create named ranges and tables in Excel with openpyxl
Working with Python code becomes significantly more manageable when objects are assigned clear, sensible names, and Excel is no different. This blog post covers how to create named ranges and tables in Excel using openpyxl.To begin this demonstration, we will import several modules from openpyxl, along with a sample of rows from the penguins datase...
3467 sym Python (1316 sym/10 pcs) 1 img
How to work with cells, rows and columns in Excel with openpyxl
Just like in biology, where cells are the building blocks of life, in your Excel workbook, cells serve as the foundational elements. In this post, you’ll discover how to interact with individual cells and then extend your knowledge to managing entire rows and columns using openpyxl.For this demo, I’ll assume you’re familiar with installing an...
3165 sym Python (1609 sym/17 pcs)