Start Excel with Python
It is unbelievable how many hours people spend on copy and pasting, manually doing the same tasks again and again in Excel. Combining multiple Excel sheets to one. Not to mention having way to big datasets inside Excel.
This is not productive. This way of working is error-prone.
One great way to help yourself and others is to automate these manual processes done in Excel.
The guide will help you get started with using Pandas to read data from various data sources, process the data in Python, and finally export it to Excel, including inserting charts and fit trendlines.
Step 1: Get aquatinted with Pandas
Pandas is a data analysis and manipulation tool, which in many ways can seem similar to an Excel data sheet. The Pandas has two data structures, the Series and DataFrames.
The video below explains the basics of what you need to know to get started with Series and DataFrames.
Step 2: Export data from Python to Excel
The first to master is how does the Pandas DataFrame work. How can you take data from a DataFrame and export to an Excel sheet.
Can you only export specific columns? Can you set the sheet name inside Excel?
Learn how to do that.
Step 3: Get data from multiple CSV files and export them to an Excel file
One thing I have seen people do often. They have some equipment, which generates data in an CSV file. This CSV file is then worked on in Excel.
Now that is not a problem. The next thing is.
I have seen that they need to work on data from multiple CSV files in one Excel sheet. This requires that each CSV file is opened in Excel and then manually copied to one master Excel sheet.
Because they handle a massive amount of data from multiple CSV files, this process is doomed to be error-prone.
A first great win is to do it all from Python. Let Python automate the error-prone processes of manually copying big data sets. It was born to do so.
Step 4: Insert a chart with fitted trendline in an Excel sheet from Python
Another great win you can get is by automate things that are done again for each incoming dataset.
In this case, we have that the same chart is plotted for all datasets. This requires that the data is selected and then a chart is inserted. Then you need to fit it to a trendline in Excel.
The great news is, that this can be automated from Python.
Step 5: Extract Numbers from Strings in HTML table and export to Excel
One of the things that can be difficult in Excel is to import raw data and format it correctly.
In this next tutorial we will look at how we with Pandas can read an HTML table, which contains strings with numbers in them.
If imported directly to Excel, they will be interpreted as strings and you cannot make further calculations on the numbers you are actually interested in.
Step 6: Let’s have some FUN!
Okay, this next tutorial is to make some impressive things in Excel with Python.
See the YouTube video in the tutorial and see how you can use it for prank with someone.
Step 7: How to format cell according to value
To make data more readable and easy to digest in Excel you can color the background in cells with a color. This makes it easy to find changes in data fast.
How do you do that automatically when you create an Excel sheet from Python?
Look no further. Read and follow the tutorial here.
Step 8: Automate the boring stuff
You get loads of Excel sheets. You need to do the same again and again.
This is often done manual by highly paid employees. If you are one of them, check out how to automate reporting and get more (paid) free time.
Step 9: Automate Financial Analysis
If you want to make financial analysis of a stock, then it is easy with charts. But you need to collect the data from 10 years of income statements and cash flow statements.
In the next tutorial we will do that.