Data Analysis
can-excel-use-python

Can Excel Use Python?

An Alternative to Microsoft Excel

By now, you must have realized that from the billionaire CEOs to the interns working in the finance industry, everyone uses Microsoft Excel. Basic data processing, crunching numbers, creating and organizing spreadsheets are some of the required and necessary tasks everyone does while working in this sector. And the number one resource they use in order to achieve these tasks are always Microsoft Excel.

However, that does not mean there are no alternatives for Microsoft Excel. In fact, there is an endless number of other spreadsheet programs. There are easier and faster ways for you to operate your data in other software. The keyword and the term here in order to save time and gain more energy for your data processing is programming.

Python: The Most Useful Programming Tool

You can use the most popular programming language out on the Internet: Python. This open-source programming language is used to build websites, create servers and take charge in complicated and massive transactions. Even though writing and managing code can seem complex to those who have not done any programming in their entire life, learning Python is not that hard. Especially, for experienced Microsoft Excel users!

Python’s integration into the finance industry should not come as a surprise. Other than creating applications for smartphones and programs for Mac and Windows operating systems, it is also used in processing and analyzing large quantities of data. Perhaps, the biggest part Python plays can be seen in the banking and cryptocurrency transactions. Some of the largest banking software are created by using Python and Python is still in use in their infrastructure.

And due to the fact that it is efficient, flexible and more importantly powerful, it is heavily used in cryptocurrency trading and transactions as well. The last decade showed us how cryptocurrencies can become instantly more valuable than any other currency in the world. And this ascension is going to continue into this decade as well. Python has to stay in this area, while ensuring the security and the consistency of these platforms that users can buy and sell cryptocurrencies with the touch or a click of a button.

A Language Everyone Can Learn

Reading what can be achieved with the Python programming language can create the impression ‘only geniuses can use this tool’. That is completely false. You, yes YOU, can just take a couple of courses and get into learning and writing code. There are many different opinions and estimations on how much you need to learn Python. According to some of the experienced software developers and computer programmers, you can learn the most basic features of the language in just two weeks. And if you want to continue and gain more experience, they state that you will be able to get comfortable with the programming language in six to eight weeks.

By casually taking up learning Python as a hobby, you can create your own application, your own program, your own website, etc. And in addition to that, if you are already an avid Microsoft Excel user, learning Python for your data processing and analysis will be much easier!

In this blog post, in the later chapters below, you will be reading the reasons why you -as a Microsoft Excel user- should learn Python and you will find out detailed comparisons of the two software. However, if you do not know the basic knowledge of Python and its terminology, you can experience some sleepless nights while you try to learn coding. That is why you can examine the first chapter in which you can see the 11 most common keywords in Python.

Most Common Keywords in Python Terminology

In order to write in a programming language, you must first learn to read that language. Fortunately for those of you who have not written a single line of code, Python is a statement based language. This means that it can be read as well as written with words. That is important because of course, some of the code you will be writing will include mathematics and basic operations; however, simple statements and codes can exist as they are (mostly) written in the English language. That is what makes Python easier to learn, it is not zeros and ones. It is not binary. Even though it is a written programming language, it still has detailed and comprehensive features.

Python’s detailed language includes many categories. There are conventions, keywords, values, exceptions and the list go on. You will be exploring 11 different keywords in this chapter. Before going on with the first one, let’s define what a keyword actually is.

A keyword in Python is a word that is assigned a specific purpose. You, as a user of Python, cannot change what this keyword does or how it operates. You can only choose whether you want to use it or not. Now, let’s begin with the first basic keyword!

  • if:

If you have used the basic formulas in Microsoft Excel via VBA (Visual Basic for Applications), you would have used the IF function as well. This keyword checks the ‘true’ or ‘falseness’ of the statement that comes after it.

  • true:

The ‘true’ value indicates the statement before it is true.

  • false:

The ‘false’ value indicates that the statement is false. These are most commonly used with the ‘if’ keyword. Lines such as:

if STATEMENT true

or

if STATEMENT false

indicate that the code will continue to operate a task.

  • none:

And then there is ‘none’. The none value does not indicate any value. Even though, if the ‘none’ value is used the statement is considered ‘false’, it will make the statement valueless.

  • else:

The ‘else’ keywords operate as the opposite of the ‘if’ keyword. It functions as it limits the statements. An example of the keywords can be found below:

else true if STATEMENT-A false

This line of code indicates that if the “STATEMENT-A” is false then the other statements (else) will be true. It is also sometimes used as in the function similar to either…or.

  • not:

This keyword negates whatever comes after it. It inverts the value of the statements. For example:

STATEMENT-B true

not STATEMENT-B

As you can see “STATEMENT-B” was considered ‘true’ at it was given that value. However, in the next line of code with the use of the ‘not’ keyword, the value of STATEMENT-B was turned into ‘false’.

  • import:

You can easily bring your data into your Python code as well! With the ‘import’ keyword, you can read and write on your Excel files. You can find detailed information about reading Excel files in Python using openpyxl in the later chapters below.

  • from:

This keyword is used mostly with the ‘import’ function. You can select where you want to import your modules, files and data from.

  • as:

This keyword creates a new or an alternative name for your file, module or data. It is also used with the ‘import’ function as can be seen below with an example:

import FILE as FILE-NEW

This way you can import the original file and when you import the data into Python, your file (or at least the alias of it) will be called “FILE-NEW”.

  • try:

This function is used to ‘try’ and test statements or actions in the code. It is a highly versatile keyword so that means it can be used with a bunch of other and different keywords as well.

if BLOCK-A false

try:

BLOCK-B

This line of code indicates that the code written at the end valued “BLOCK-A” as false, then the code will test “BLOCK-B”.

  • except:

This keyword excludes different objects or statements from a task. For example, you want to test out the line of code-named “BLOCK-C”. However, you want to exclude and keep out “STATEMENT-A” which is located in “BLOCK-C”. The line of code for this task can be:

try:

BLOCK-C

except:

STATEMENT-A

Before You Start Using Python

Find Accurate Data

No matter whether you are putting data into a Microsoft Excel file or Python code, you are going to need data… a lot of data. The quantity of your data is irrelevant, the quality of your data is always going to be the most important factor. If you are working in the finance industry, then, you know how important your data you put in can be. It can literally make or break your and your company’s financial futures. That is why it is important you gather up accurate data. Other than .xls and .xlsx, do not hesitate to look for files in other formats such as .txt and .csv in order to confirm the data you have.

Adjusting Your File (Before Python)

If you know you are going to import your spreadsheet into Python, then, you should consider some of the basic format principles listed below.

  • The Header

Your spreadsheet’s first row should only consist of your header and nothing else. When you import your spreadsheet into Python, the program will consider the first row of data as your header.

  • Avoiding Blank Spaces

Do not use any blank spaces in your code or data. You should not use blank spaces with names, statements and values in your data. If you do use it, Python will consider every element of your data ‘separately’. It will not function properly.

  • Underscores Everywhere!

If you are not satisfied with not using blank spaces, just use underscores in order to combine different words. You can use dashes if you prefer.

  • Removing Comments and Notes

Before you import your data into Python, eliminate all the comments and notes added to your file. Otherwise, all of your comments will be shown in the Python code and confuse both the program and you.

  • Symbols

There are some symbols that you should try to avoid using due to the fact that they have different functions in the Python program. These symbols are but not limited to:

? (question mark),
$ (dollar sign),
% (percentage symbol),
^ (caret), & (and symbol),
( ) (parentheses),
# (number sign [commonly known as hashtag]),
* (asterisk),
/ (slash),
\ (backslash),
[ ] (square brackets) and
{ } (curly brackets).

  • Saving Your File

First of all, do not forget to save. Ever! That is why you should turn on the “Autosave” function on whatever spreadsheet program you are using right now. And if you want Python to easily process your data, you can always choose to save your Microsoft Excel file in the .txt format. You can achieve that by going to the “File” tab, choosing “Save As” and selecting the format you want to save your file in.

  • Front-End and Back-End

Have you ever heard the terms “front-end” and “back-end” developer? Which of these categories is Python considered in? The difference between these two terms is that in the front-end development, developers and programmers code what the users see.

Think of a webpage: There are texts, pictures, images, buttons, links, etc. Now, what you see and how you see it is the job of the front-end developers. And in order to achieve that they use programming languages such as HTML and JavaScript.

You can figure out that back-end developers do: They code what the users do not see. These include the security procedures for the website and the server it runs on, the transaction details and all the inside workings of anything that runs in code. Back-end developers use programming languages such as Python and C (C+ and C++). Now you know that being an expert on Python makes you a back-end developer!

How to Read Your Excel Files Using Python (with openpyxl)

Reading this chapter’s title can cause some confusion, it is totally understandable. So, let’s begin with the basics before showing some code examples. In order to read your Excel file in Python and see all of the data, you need to import it and then, return the values and cells you want to see. Python will not show you every cell on every row and column to you at once. You can do that by writing a code; however, it will not automatically show you every data on your spreadsheet once you import it. So, that’s that.

The other question you might have on your mind is: What is openpyxl? Let’s answer it.  Openpyxl is a Python tool that allows you to read and write on Excel files formatted .xls, .xlsx, .xlsm, .xltx and .xltm.

Now, if all the confusion is out of your head about what is what, let’s start reading some spreadsheets!

In order to first open your spreadsheet, you can apply some of the keywords featured above in the previous chapters. Let’s say that the name of your Excel file is ‘test’:

from openpyxl import load_workbook
wb = load_workbook(filename = ‘test.xlsx’)

This simple code will open up your spreadsheet in Python with the help of openpyxl. In order to see the ‘sheets’ you have in your file and to select one, you will use the code below:

workbook.sheetnames

[‘Test Sheet 1’] [‘Test Sheet 2’]

This ‘workbook.sheetnames’ code will show you all of the names of the sheets you have. If you do not know the name of the sheets, you can use the code below. It selects the first sheet that contains data:

sheet = workbook.active

<Worksheet ‘Test Sheet 1’>

This code indicates that the first available sheet was named ‘Test Sheet 1’ and it was chosen for you to read.

Let’s say that the whole worksheet consists of the values of cryptocurrencies. So, the first cell of the sheet (which is A1) includes the word ‘Bitcoin’. In order to return this data and read it in Python, your code will be something like this:

sheet[‘A1’].value

‘Bitcoin’

As you can see, you have to put the code ‘.value’ right after you write the cell number. The other way you can see the data on a cell is to write the rows and columns which they are located in:

sheet.cell(row=1, column=1).value

‘Bitcoin’

No matter which method you use to return the data of a specific cell, you have to use ‘.value’ for the whole task to work.

This process can seem confusing and complicated; however, if you follow these steps and be prepared, you will accomplish it!

Why You (As a Microsoft Excel User) Should Use Python

You might be thinking that ‘I do not use Python at my work, why do I care about it?’. You might be right… for now. If you are not using Python or any other programming language at your workplace, it does not mean it will still be the case in 10 years. That is right. Excel might be the most popular spreadsheet program and one of the most used programs around the world now. But what do you expect to do if Python takes its place in the near future? Will you be still able to say ‘I do not care’?

Coding Is Everywhere

Coding is not in the future; coding is the future. In the digital age, every device you operate in has coding in its core. A developer coded applications and operations into your computer or your phone, so that you can operate with it. A developer coded your internet browser so that you can surf the internet. A developer built the Internet itself! And, a developer coded this very website that you are reading from right now. No matter whether you see it or do not, coding is everywhere and it will get more in quantity in the ever-developing world.

The achievements in machine learning, your ability to download anything online, your messaging application, your whole digital life, your cryptocurrency, the spaceship that orbits the Earth, every little and incredibly huge achievement is because of coding.

And do not think for one second that it is ‘hard to learn’. If you are able to write the functions and formulas in Microsoft Excel, then, you are more than qualified to write code in Python. The programming language of VBA is actually considered harder to learn than the Python language. Python shows you the actual functioning words you need to write down, not zeros and ones!

Which One You Should Use for Data Analysis: Excel or Python?

Basic Data Processing

If you are not interested in writing code or formulas in order to process your data, Excel is an easier tool to use. It does not promise more than it can do. And also, there is no learning curve for Excel. You can buy and download the software and start using it immediately. If you want to use some of the basic formulas for your calculations, it will take only a couple minutes for you to watch or read tutorials for it.

On the other hand, Python is a heavy programming language. It does not deal with basic data processing or anything basic at all. Unlike Excel, it does have a learning, as it absolutely should. As it is mentioned above in the previous chapters, it will take at least a couple of weeks for you to get the gist of the Python programming language and coding. So, Excel takes the win in this category.

Price

As it can come as a surprise, Python is free to use! It is an open-source programming language with huge libraries to accompany the program itself. And the open-source aspect of the software makes it more accessible. Have you ever downloaded a patch for your video game or updated one of the applications on your phone? Python’s open source feature allows developers, yes even you, to create their own updates and improvements. It also handles bugs and errors much better.

However, Excel has a price. It is included in Microsoft’s Office 365 package. Even though Microsoft developers and programmers deliver a purchasable update every three years; Excel is not an open-source software whatsoever. You can download any third party templates such as Someka’s, but no developer can improve the program’s code. That means Python is the clear winner in the price category.

Handling Large Amounts of Data

Of course, Excel is a powerful tool. However, have you ever tried working with an Excel file or multiple files featuring millions of rows of data? If so, were you happy with the program’s handling? Making mistakes among the vast pool of data in Excel is inescapable. The worst part about it is that when you find the error, it is even a bigger nightmare to fix it in an Excel file. And if you have multiple files with endless worksheets, it is a time consuming and energy-wasting process to find individual mistakes.

Python is not affected by the amount of data due to the fact that it does not have an interface which is interacting with the data. It only works with your queries; this makes merging multiple big files including long spreadsheets much easier and faster. Without a question, Python takes the cake on handling large amounts of data.

Integrations and Connectivity

Python plays a ‘bridge’ role with many different applications and software in many different fields. It works with various file formats as well including .xls and .xlsx formats which are Excel’s own file formats. Python has been integrated with other programming languages such as SQL and interacted with Application Program Interfaces (APIs). Also, thanks to its open-source feature, the data in the program can be saved and sent to clouds. This allows that the datasets it functions with can be accessed through the Internet and enables a level of connectivity that Excel cannot provide.

Connectivity and accessibility have been Excel’s biggest hurdles to get over. You can upload your .xls or .xlsx files online and edit with your employees in real-time… if you use Google Sheets. As of this time Excel is still behind on improving their collaboration tools. So, Python is the winner in this category as well.

FINAL WORDS

In conclusion, Excel is one of the most popular programs around the world. Because it is easy to learn and easy to use, it attracts people with simple tasks. Python, on the other hand, is like a never-stopping machine on handling data. That is why if you want to use only one of the programs for your data analysis, you should opt to choose Python.

This blog post detailed which one to use instead of the other software. However, it is possible to use them both! They have different purposes which they excel at, that is why Someka has a post in its blog about how you can get real-time stock quotes in Excel while using Python’s codes! You can check the blog post by clicking here.

Related Posts

Search