Excel now supports more languages than the United Nations
We had been hearing about this since 2020, Python finally decides to crash the Excel party, but Excel now already has more languages than a United Nations conference. I mean, seriously, it’s like a linguistic circus in there. First, there’s the good old Excel formulas – the gangsta language for numbers. Then, VBA joins, the old-timer who’s been around since the dawn of Excel time, barely receiving any updates at all. And wait, there’s more ! Office scripts, the shiny new kid on the block for automations and scripts that work across the web. But that’s not all, you’ve also got PowerQuery for data transformations. And Dax, or Power Pivot measures, for some fancy pivot table calculations. That’s already five languages in Excel’s toolkit.
Now, Python strolls in. And sure, it’s exciting for those of us who love learning new programming languages. But it’s already making our heads spin – thinking that, for better or worse, Excel in itself now supports at least six languages.
Python Implementation for Excel by Microsoft
Python is a general-purpose programming language that is becoming increasingly popular for data analysis and visualization. In September 2020, Microsoft announced that it would be integrating Python into Excel, allowing users to write Python code directly in Excel spreadsheets and very recently its being rolled out the the Office 365 family. Some key features of this implementation are:
- It is available only for Office 365 subscription plans.
- This integration makes it easy to write and run Python code without having to leave Excel.
- This runs over the internet in Microsoft’s server and is not using the client computer’s memory.
- It has a limited set of features. Not all Python features are available in Excel. For example, you cannot use Python to create custom functions or modules.
- It can be slow. The performance of Python in Excel can be slow, especially for large datasets and the user cannot take advantage of their local machine for computing as this is a cloud based service.
- The Python environment in Excel is isolated from the user’s local Python installation so there is a duplicacy in managing and maintaining python libraries.
However, this Python implementation is still very welcome. Python can be used to automate tasks that would be difficult or time-consuming to do with Excel alone and very untidy or time consuming to implement in VBA. Python can be used to create dynamic, interactive and complex visualizations that can be shared with others very much faster than Excel’s inbuilt tools. This Python integration allows Excel users to harness the power of Python for data analysis and visualization tasks that would be difficult or time-consuming to do with Excel alone.
More on this here: New In Excel – Python
A better alternative for Python in Excel: XLWings
XLWings is a Python library that allows you to interact with Microsoft Excel from Python. It serves as a bridge between Python and Excel, enabling you to automate Excel tasks, manipulate Excel workbooks and worksheets, and perform data analysis using Python scripts and python installation on your local computer. XLWings is particularly useful for data scientists, analysts, accountants and developers who want to leverage the power of Python for working with Excel data. With XLWings, you can:
- Automate Excel: You can write Python code to control Excel, including opening and saving workbooks, formatting cells, creating charts, and more.
- Read and Write Data: XLWings allows you to easily read data from Excel worksheets into Python data structures like pandas DataFrames and write data back to Excel.
- Use Excel Functions: You can call Excel functions and formulas from Python, allowing you to perform calculations using Excel’s built-in capabilities.
- Create User-Defined Functions (UDFs): XLWings allows you to create custom Python functions that you can use as formulas within Excel.
- Integrate with Excel VBA: You can use XLWings alongside Excel VBA (Visual Basic for Applications) to create more powerful automation solutions.
- Cross-Platform Compatibility: XLWings is compatible with both Windows and macOS, making it a versatile tool for Excel automation.
It’s important to note that XLWings requires Excel to be installed on your computer since it interacts with Excel through its COM interface on Windows or AppleScript on macOS. The python installation and library management is also done locally. Overall, XLWings is a valuable tool for those who want to combine the data manipulation and analysis capabilities of Python with the familiar interface and functionality of Microsoft Excel.
Get Started with XLWings
To install and use XLWings on your computer, you can follow these steps. Link to XLWings most recent developers documentation here.
Prerequisites
- Install Python: You should have Python installed on your computer. If you haven’t already, you can download and install Python from the official Python website. During installation, make sure to check the box that says “Add Python X.Y to PATH” during the installation process. This will automatically add Python to your system’s PATH variable.
- Install Microsoft Office: You must have Microsoft Excel installed on your computer because XLWings interacts with Excel.
- Install Visual Studio Code: Visual Studio is an integrated development environment from Microsoft that we will use for writing python scripts.
Installation of XLWings
Step 1: Open terminal in Visual Studio Code
Step 2: Install XLWings Python Library in your machine
pip install xlwings
Step 3: Install XLWings Addin in your Microsoft Excel
xlwings addin install
Step 4: Install all dependencies required to run xlwings
pip install “xlwings[all]”
Step 5: Update to the latest version of XLWings
pip install --upgrade xlwings
Step 6: openpyxl is a Python library to read/write Excel files, this is optional but very easy to use python library which I personally prefer, other python libraries for excel can also be installed as per your need.
pip install openpyxl
Security and References
Step 1: Enable Trust access to the VBA project object model in Microsoft Excel and enable VBA Macros. In Microsoft Excel Under File > Options > Trust Center > Trust Center Settings > Macro Settings. You only need to do this once.
Step 2: Enable To enable references for XLWings first enable Developer Tab. In Microsoft Excel Under File > Options > Customize Ribbon > Main Tabs > Check Developer. You only need to do this once.
Step 3: Then to finally enable references, in Microsoft Excel Under Developer > Visual Basic > Tools > References > check xlwings
Understanding the XLWings Ribbon
Once you have the XLWings installed you will be greeted with a tab in the Excel ribbon section.
- Interpreter: This is the path to the Python interpreter. Although this will be filled in automatically upon installation for you, if not you will have to put in the path to python.exe installed in your local machine.
- Python Path: This is the path to the python file that has your python scripts. If this is left empty, you can also check “add the path to the directory” just below, which will search for the python file with the same name as the excel file in the same directory that you are calling python scripts from.
- Conda Variables: The conda path and conda environment variables are not applicable unless you are using conda package management system, so I will skip that.
- UDF Modules: Names of Python modules (without .py extension) from which the UDFs are being imported. Separate multiple modules by “;”. Example: UDF_MODULES = “common_udfs;myproject” The default imports a file in the same directory as the Excel spreadsheet with the same name but ending in .py.
Calling Python from Excel
Option One: Call the default main() function from python script
You can call Python functions either by the “Run main” button from the ribbon. The Run button expects a function called main in a Python script with the same name as your workbook. The great thing about that approach is that you don’t need your workbooks to be macro-enabled, you can save it as xlsx.
Option Two: Call any function from python script
If you want to call any Python function no matter in what module it lives or what name it has, use RunPython:
' hello.xlsx
' Your VBA Module that calls the python script
Sub HelloWorld()
RunPython "import hello; hello.main()"
End Sub
# hello.py
# your python script
import numpy as np
import xlwings as xw
def main():
wb = xw.Book.caller()
wb.sheets[0]['A1'].value = 'Hello World!'
RunPython expects {filename}.py in the same directory as the Excel file with the same name, but you can change both of these things: if your Python file is in a different folder, add that folder to the PYTHONPATH in the config. If the file has a different name, change the RunPython command accordingly.
Creating User Defined Functions
Writing a UDF in Python is as easy as:
Import Functions: On the excel ribbon, use the “Import Functions”” button to refresh and import the python modules with the functions after changes have been made to the python scripts. You only need to re-import your functions if you change the function arguments or the function name.
# hello.py
# your python script
import xlwings as xw
@xw.func
def double_sum(x, y):
"""Returns twice the sum of the two arguments"""
return 2 * (x + y)
The possibilities are immense with XLWings
XLWings unlocks a world of possibilities when it comes to Excel automation and data manipulation. With XLWings, you can seamlessly integrate the power of Python with the familiarity of Excel. Imagine effortlessly automating repetitive tasks, creating custom Excel functions using Python, or performing complex data analysis with pandas and NumPy—all within the Excel environment helping us to boosting productivity, enhancing data workflows, and supercharging Excel experience. Whether you’re a data analyst, financial expert, or just looking to make Excel work smarter for you, the possibilities are endless and yet so simple.
XLWings is a versatile Swiss Army knife for Excel users. It can effortlessly scrape data from the web, enabling you to keep your spreadsheets updated with the latest information from online sources. It’s also a powerful plotting tool, allowing you to create stunning data visualizations using Python’s popular libraries like Matplotlib or Seaborn. Moreover, XLWings is a wizard at automating Windows tasks—whether it’s generating reports, sending emails, or handling file operations, Python scripts with XLWings can be your go-to automation solution. Its seamless integration with other Python packages like NumPy, pandas, and SciPy makes it an indispensable tool for anyone looking to take their Excel projects to the next level.
So, finally, its here, the best time for an accountant to do more is by switching from VBA to Python, if you had not been sufficiently overworked already !!