Python for Excel By Felix Zumstein PDF Free Download

Share on facebook
Share on whatsapp
Share on twitter
Share on telegram

Table of Contents

Python for Excel

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.

Disclaimer:
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, hubpdf.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 [email protected] for all DMCA / Removal Requests.

For More Computers Books

Python for Excel

Python for Excel PDF

Product details:

EditionInternational Edition
ISBN1492081000, 978-1492081005
Posted onMarch 30, 2021
Formatpdf
Page Count338 pages
AuthorFelix Zumstein

Python for Excel By Felix Zumstein PDF Free Download - HUB PDF

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.

URL: https://amzn.to/3IKoy9T

Author: Felix Zumstein

Editor's Rating:
4.7
Recent Books
Audible Plus Free
Recent Posts