Hello World!

Hello and welcome to Excel with Python! In this first post I would like to discuss the goals for this blog, and the topics I plan to cover.

The goal of this blog is to introduce and teach Python in a way that makes sense for professionals who use and are familiar with Microsoft Excel. I am not a professional software developer, and you don't have to be either to get value out of this blog and out of Python.

Given that I am a CPA many of my examples and use cases will lean towards Accounting/Finance, but the vast majority of the concepts should be applicable to Excel users in general.

While the primary focus of this blog will be on Python, I also plan on writing about Excel as Excel plays a huge role in the day to day for many professions. This blog isn't about replacing Excel with Python but instead using them together in a way that maximises your time efficiency.

What is Python?

Python is a programming language. Like any other programming language it is a way for a human to write something human-readable (i.e code) and have it be translated into instructions your computer can understand and follow. One of the key advantages Python has over other programming languages is that the code you need to write is quite close to plain English. This makes it relatively easy to write and read even if you know nothing about Python.

Take the below code for example:

book_list = ['Dracula', 'The Great Gatsby', 'Hamlet']

new_book = 'Frankenstein'

book_list.append(new_book)

My guess would be that most of you would be able to guess what this code is doing without any further explanation. If not, that's ok. Let's review this code line by line.

book_list = ['Dracula', 'The Great Gatsby', 'Hamlet']

Here we define a variable called book_list and set it equal to a list containing 3 books.

new_book = 'Frankenstein'

Next, we define a new variable called new_book and set it equal to 1 book, Frankenstein.

book_list.append(new_book)

Lastly, we take our existing book_list and append (or add) the new_book to it.

This code then leaves us list of 4 books, stored in the variable book_list. To see the full list, we could add one more line of code.

print(book_list)

The output of which would be the following:

['Dracula', 'The Great Gatsby', 'Hamlet', 'Frankenstein']

OK, but how can this help Excel users?

In the above example, we saw that taking a list of books and adding one book to it isn't very hard. That's great, but obviously not of much use to an everyday Excel user.

There are tons and tons of use cases where Excel users can use Python. Most things done in Excel are not done only once. There are daily, monthly, quarterly, and even annual tasks, many of which involve some sort of data processing and manipulation and are ripe for some level of automation using Python.

An example

Let's say you have some data you receive in CSV format, all with the same header names but different data. Maybe you have multiple accounts with the same bank, but they don't have the ability to export the data in one file.

What do you do?

In general, most people would start an Excel workbook and begin copy and pasting out of each CSV to combine them.

Maybe that's not so bad if you have 3 files, but what if you have 30 files? What about 300? iI don't know about you, but my limit is somewhere around 5 files.

This is where Python comes in. Take a look at the following few lines of code.

import glob
import pandas as pd

input_folder = 'C:\\Users\\User\\Desktop\\FolderFullofCSVs'

csv_list = []
for file in glob.glob(input_folder + '/*.csv'):
    df = pd.read_csv(file)
    csv_list.append(df)

df = pd.concat(csv_list, ignore_index=True, sort=False)
df.to_excel(input_folder + '\\Combined File.xlsx', index=False)

Using this snippet of code, you could have a 1000 CSV's combined for you into one Excel file, likely in less time than it would take you to get up and grab a fresh cup of coffee.

Depending on the number of files this could result in multiple hours of time savings. If you were having to do it on a monthly basis, the time savings continues to pile up.

Giving you options

Ultimately, the goal is to provide you with the knowledge to use Python as a tool. Having another tool will allow you to have options. Options allow you to better make decisions depending on the scenario. Have a task that is a one-off? May be best to do it in Excel. Have something you do on a repetitive basis? May be worth considering writing some Python.

If you're interested in learning more about how to Excel with Python by using Python and Excel, stay tuned for my future posts!

Have questions, comments, or feedback? Contact me