Python for Excel Summary
Python for Excel: A Modern Environment for Automation and Data Analysis, While Excel remains ubiquitous in the business world, recent Microsoft feedback forums are full of requests to include Python as an Excel scripting language. In fact, it’s the top feature requested.
What makes this combination so compelling? In this hands-on guide, Felix Zumstein–creator of xlwings, a popular open-source package for automating Excel with Python–shows experienced Excel users how to integrate these two worlds efficiently.
Excel has added quite a few new capabilities over the past couple of years, but its automation language, VBA, stopped evolving a long time ago. Many Excel power users have already adopted Python for daily automation tasks. This guide gets you started.
- Use Python without extensive programming knowledge
- Get started with modern tools, including Jupyter notebooks and Visual Studio code
- Use pandas to acquire, clean, and analyze data and replace typical Excel calculations
- Automate tedious tasks like consolidation of Excel workbooks and production of Excel reports
- Use xlwings to build interactive Excel tools that use Python as a calculation engine
- Connect Excel to databases and CSV files and fetch data from the internet using Python code
- Use Python as a single tool to replace VBA, Power Query, and Power Pivot
About the Author
Felix Zumstein is the creator and maintainer of xlwings, a popular open-source package that allows the automation of Excel with Python on Windows and macOS. He also organizes the xlwings meetups in London and NYC to promote a broad range of innovative solutions for Excel.
As CEO of xltrail, a version control system for Excel files, he has talked to hundreds of users who use Excel for business-critical tasks and has therefore gained deep insight into the typical uses cases and issues with Excel across various industries.
Python for Excel Introduction
Excerpt. © Reprinted by permission. All rights reserved.
Why Python for Excel?
Usually, Excel users start to question their spreadsheet tools when they hit a limitation. A classic example is when Excel workbooks contain so much data and formulas that they become slow or in the worst case, crash. It does make sense, though, to question your setup before things go south: if you work on mission-critical workbooks where errors can result in financial or reputational damage or if you spend hours every day updating Excel workbooks manually, you should learn how to automate your processes with a programming language. Automation takes out the risk of human error and allows you to spend your time on more productive tasks than copy/
pasting data into an Excel spreadsheet.
In this chapter, I will give you a few reasons why Python is an excellent choice in combination with Excel and what its advantages are compared to Excel’s built-in automation language, VBA. After introducing Excel as a programming language and understanding its particularities, I will point out the specific features that make Python so much stronger in comparison with VBA. To start with, however, let’s take a quick look at the origins of our two main characters!
In terms of computer technology, Excel and Python have both been around for a very long time: Excel was first launched in 1985 by Microsoft—and this may come as a surprise to many—it was only available for Apple Macintosh. It wasn’t until 1987 that Microsoft Windows got its first version in the form of Excel 2.0. Microsoft wasn’t the first player in the spreadsheet market, though: VisiCorp came out with VisiCalc in 1979, followed by Lotus Software in 1983 with Lotus 1-2-3. And Microsoft didn’t lead with Excel: three years earlier, they released Multiplan, a spreadsheet program that could be used on MS-DOS and a few other operating systems, but not on Windows.
Python was born in 1991, only six years after Excel. While Excel became popular early on, it took Python a bit longer until it got adopted in certain areas like web development or system administration. In 2005, Python started to become a serious 3
alternative for scientific computing when NumPy, a package for array-based computing and linear algebra, was first released. NumPy combined two predecessor packages and therefore streamlined all development efforts around scientific computing into a single project. Today, it forms the basis of countless scientific packages, including pandas, which came out in 2008 and which is largely responsible for the widespread adoption of Python in the world of data science and finance that started to happen after 2010. Thanks to pandas, Python, alongside R, has become one of the most commonly used languages for data science tasks like data analysis, statistics, and machine learning.
The fact that Python and Excel were both invented a long time ago isn’t the only thing they have in common: Excel and Python are also both a programming language.
While you are probably not surprised to hear that about Python, it may require an explanation for Excel, which I’ll give you next.
Excel Is a Programming Language
This section starts by introducing Excel as a programming language, which will help you to understand why spreadsheet issues turn up in the news on a regular basis.
We’ll then have a look at a few best practices that have emerged in the software development community and that can save you from many typical Excel errors. We’ll conclude with a brief introduction to Power Query and Power Pivot, two modern Excel tools that cover the sort of functionality for which we will use pandas instead.
If you use Excel for more than your grocery list, you are definitely using functions like =SUM(A1:A4) to sum up a range of cells. If you think for a moment about how this works, you will notice that the value of a cell usually depends on one or more other cells, which may again use functions that depend on one or more other cells, and so on.
Doing such nested function calls is no different from how other programming languages work, only that you write the code in cells instead of text files. And if that didn’t convince you just yet: at the end of 2020, Microsoft announced the introduction of lambda functions, which allow you to write reusable functions in Excel’s own formula language, i.e., without having to rely on a different language like VBA.
According to Brian Jones, Excel’s head of product, this was the missing piece that finally makes Excel a “real” programming language.1 This also means that Excel users should really be called Excel programmers!
There is a special thing, though, about Excel programmers: most of them are business users or domain experts without a formal education in computer science. They are traders, accountants, or engineers, to mention just a few examples. Their spreadsheet tools are designed to solve a business problem and often ignore best practices in 1 You can read the announcement of lambda functions on the Excel Blog.
Why Python for Excel?
software development. As a consequence, these spreadsheet tools often mix inputs, calculations, and outputs on the same sheets, they may require nonobvious steps to be performed for them to work properly, and critical changes are done without any safety net. In other words, the spreadsheet tools are lacking a solid application architecture and are often undocumented and untested.
Sometimes, these issues can have devastating consequences: if you forget to recalculate your trading workbook before placing a trade, you may buy or sell the wrong number of shares, which can cause you to lose money. And if it isn’t just your own money you are trading, we can read about it in the news, as we’ll see next.
Excel in the News
Excel is a regular guest in the news, and during the course of this writing, two new stories hit the headlines. The first one was about the HUGO Gene Nomenclature Committee, which renamed a few human genes so they wouldn’t be interpreted by Excel as dates anymore.
For example, to prevent that the gene MARCH 1 would be turned into 1-Mar, it was renamed into MARCHF1. 2 In the second story, Excel was blamed for the delayed reporting of 16,000 COVID-19 test results in England. The issue was caused by the test results being written to the older Excel file format ( .xls) that was limited to roughly 65,000 rows.
This meant that larger datasets were simply cut off beyond that limit.3 While these two stories show the continued importance and dominance of Excel in today’s world, there is probably no other “Excel incident”
that is more famous than the London Whale.
London Whale is the nickname of a trader whose trading mistakes forced JP Morgan to announce a staggering loss of $6 billion in 2012. The source of the blowup was an Excel-based value-at-risk model that was substantially underestimating the true risk of losing money in one of their portfolios. The Report of JPMorgan Chase & Co. Management Task Force Regarding 2012 CIO Losses 4 (2013)
mentions that “the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” On top of these operational issues, they had a logical error: in one calculation, they were dividing by a sum instead of an average.
This site complies with DMCA Digital Copyright Laws. Please bear in mind that we do not own copyrights to this book/software. We are not hosting any copyrighted content on our servers, it’s a catalog of links that have already been found on the internet. hubpdf.com doesn’t have any material hosted on the server of this page, only links to books that are taken from other sites on the web are published and these links are unrelated to the book server. Moreover, Epicpdf.com server does not store any type of book, guide, software, or images. No illegal copies are made or any copyright © and/or copyright is damaged or infringed since all material is free on the internet. Check out our DMCA Policy. If you feel that we have violated your copyrights, then please contact us immediately. We’re sharing this with our audience ONLY for educational purposes and we highly encourage our visitors to purchase original licensed software/Books. If someone with copyrights wants us to remove this software/Book, please contact us. immediately.
You may send an email to firstname.lastname@example.org for all DMCA / Removal Requests.
Python for Excel PDF
|Posted on||March 30, 2021|
|Page Count||338 pages|
Python for Excel By Felix Zumstein PDF Free Download - Epicpdf
Python for Excel: A Modern Environment for Automation and Data Analysis, While Excel remains ubiquitous in the business world, recent Microsoft feedback forums are full of requests to include Python as an Excel scripting language. In fact, it's the top feature requested.
Author: Felix Zumstein