Real-life Excel Automation with Python and Xlwings

A real-life case study of Excel automation with Python and Xlwings in a big corporation.

Xlwings Cover Image, generated by Gemini

After my junior year at university, I got into a summer internship at a big company, which shall stay nameless, in Tashkent, Uzbekistan. With my bachelor’s in Finance, I joined their Financial Planning and Analysis (FP&A) team as a full-time finance intern that knew how to code. Apparently, my finance education and programming skills were the reason why I was selected out of other bright students.

So, here we go. I knew how to create Django websites, not how to automate financial operations. I had to quickly learn Pandas for data manipulation and analysis, Power BI for visualization (the company was fully on Microsoft), and finally Xlwings for using Python with Excel.

Towards the end of my internship, I started automating Excel with Python. In this article, I am going to show you how I automated fetching and updating foreign exchange rates in our local Excel file. Although this task is very simple and easy for an experienced dev, it nicely illustrates Xlwings, as well as a point or two about working in a corporation.

Before going deep into Xlwings, let’s answer the elephant in the room: why Python in Finance?

Python in Finance

Python is the one language to write it all, period. Because of its easy syntax, awesome libraries for working with data (Pandas, Matplotlib), and domain-specific packages (Xlwings), Python has become a standard coding language in business, especially in finance.

You want Excel automation? Boom, here is Xlwings. Need to create a small, fast API endpoint? Here is Flask. Have massive datasets that Excel even cannot open? Try Pandas. With its ready-to-use libraries, excellent tutorials and documentation, and easy syntax, it makes a ton of sense to use Python fast and cheap.

What is Xlwings?

Finance has much to automate with Python (invoice, payroll, data cleaning and standardization, etc.), but the number one place finance professionals want to improve is Excel. As wonderful and useful as it is, Excel has its own limitations and pain points, like pretty much any software.

For once, Excel’s row count is capped at approximately 1 million and encounters serious performance issues on most office laptops; Python is essentially limitless for most automation tasks. For another, VBA and macros are only for Excel; if you need automation outside it, you will anyway reach another tool, i.e. Python.

So, how to automate Excel with Python? Several options exist:

1. Built-in Python in Excel: Microsoft 365 provides cloud-based Python directly inside Excel. Requires internet and 365 subscription.
2. Xlwings: An open-source, free library with PRO support. Relies on external Python and Excel to automate operations.
3. **PyXLL**: Proprietary commercial add-in directly inside Excel. Fast, reliable, but subscription-based per user.

For most practical use cases, Xlwings is the way to go because it is free, fast, and based on external Python that communicates with Excel through COM (Component Object Model) API. Additionally, the creator of this package, Felix Zumstein, had written a book, *Python for Excel*, which I read during my internship.

Python for Excel by Felix Zumstein

So, after a month and a half, I was getting relatively ready to use my Python skills to automate boring stuff in Excel.

Excel Automation: Foreign Exchange Rates

After completing my tasks given by my mentor, I spent my time learning and playing around with Xlwings. When I could not install Python on my corporate laptop due to restrictions, I went to the IT&admin team for installation. My jaws dropped dead when I heard this question: “What is Python and why you need it?” After a brief bureaucratic back and forth, I realized I could bypass this simply by installing Anaconda.

When I came up to my team members stating we could automate some Excel operations with Python and Xlwings, I got a response that surprised pretty much no one: “Yeah, give it a try and let us know.”

Being a big company that had foreign suppliers, the company needed foreign exchange rates, USD and EUR. Someone had to manually go to the Central Bank website, fetch the Excel file, and update our local file. A very good illustration for automation. So, I wrote it and automated my laptop to run it every day at 8.30 a.m. with Windows Task Scheduler.

Below is the code:

Project

All code snippets are available in this repository. All terminal commands work with both Unix-based systems and Windows. Where they don’t, notice is given.

So, how do you get information from the web? The default response is to scrape it. Or check for available API endpoints. If you can, use API because it is simpler.

I could get currency exchange rates from JSON API from the Central Bank, but I was too deep in my task that I just went straight to scraping from its homepage.

Here was the format of the Excel sheet that we had info on FX:

Excel Sheet for Foreign Exchange Rates

The logic of my little program was this: Get the rates of USD and EUR against sum (our national currency) for a given date and put it in the relevant cell. Here is how I went about it:

1. Scrape the data from the website;
2. Determine the date and put it in the relevant cell;
3. Automate this to run every weekday at 8.30 a.m.

#1 Scraping

To scrape, I used requests and BeautifulSoup with lxml . After installing packages, I imported necessary libraries and set constants:

import sys
import time
from string import ascii_uppercase

import requests
import xlwings as xw
from bs4 import BeautifulSoup

CBU_URL = "https://cbu.uz/en/"
SHEET_USD = "USD_2026"
SHEET_EUR = "EUR_2026"

Then, I wrote the scraping logic with a few error handling cases. I first got the html, parsed the necessary rates with bs4 and used a function to return the result:

def get_html(url, timeout=5):
    print(">>> Fetching exchange rates from CBU...")
    try:
        resp = requests.get(url, timeout=timeout)
        resp.raise_for_status()
        return resp.text
    except requests.ConnectionError as e:
        print(f">>> Loss of connectivity:\n{e}")
        time.sleep(10)
        sys.exit(1)
    except requests.Timeout as e:
        print(f">>> Request timed out:\n{e}")
        time.sleep(10)
        sys.exit(1)
    except requests.RequestException as e:
        print(f">>> Request failed:\n{e}")
        time.sleep(10)
        sys.exit(1)

def parse_rates_from_html(html):
    soup = BeautifulSoup(html, "lxml")

    calendar_div = soup.find("div", class_="input_calendar")
    if not calendar_div:
        raise RuntimeError("Calendar div not found: div.input_calendar")

    inp = calendar_div.find("input")
    if not inp or not inp.has_attr("value"):
        raise RuntimeError("Calendar input/value not found inside div.input_calendar")

    currency_date = inp["value"]
    print(f">>> FX Rates date: {currency_date}")

    rates = {"USD": 0.0, "EUR": 0.0}

    for item in soup.find_all("div", class_="exchange__item_value")[:5]:
        code = item.strong.text.strip() if item.strong else ""

        if code in rates:
            rate_str = item.text.split("=")[1].strip()
            rates[code] = float(rate_str)

    return rates, currency_date

def fetch_exchange_rates():
    html = get_html(CBU_URL, timeout=5)
    return parse_rates_from_html(html)

#2 Writing to Excel

This is the step for Xlwings. Once I got the relevant cell, I used this package to write to the sheet. Although this is only a tiny use case of xlwings, it was still important in that it helped me to learn the package:

def get_excel_cell_from_date(date_str):
    day = int(date_str[:2]) + 2
    month_index = int(date_str[3:5])
    column = ascii_uppercase[month_index]
    return f"{column}{day}"

def write_to_excel(file_path, cell, rates):
    print(f">>> Writing to Excel at {file_path}...")
    wb = xw.Book(file_path)

    wb.sheets[SHEET_USD][cell].value = rates["USD"]
    wb.sheets[SHEET_EUR][cell].value = rates["EUR"]
    wb.save()

    print(">>> Excel file saved.")

Then, I wrote the main() function:

def main():
    print(">>> Starting exchange rate fetcher...")

    file_path = r"\\peter\ZDrive\00016243\Desktop\FX_Rate\excel_files\FX_2026.xlsx"
    rates, date_str = fetch_exchange_rates()
    cell = get_excel_cell_from_date(date_str)

    print(f">>> Rates fetched: USD = {rates['USD']}, EUR = {rates['EUR']}")
    print(f">>> Writing rates to cell {cell}...")

    write_to_excel(file_path, cell, rates)
    print(">>> Program finished.")

if __name__ == "__main__":
    main()

This program, run in the VS Code, produced this:

(.venv) PS Microsoft.PowerShell.Core\FileSystem::\\peter\ZDrive\00016243\Desktop\FX_Rate> & //peter/ZDrive/00016243/Desktop/FX_Rate/.venv/Scripts/python.exe //peter/ZDrive/00016243/Desktop/FX_Rate/main.py
>>> Starting exchange rate fetcher...
>>> Fetching exchange rates from CBU...
>>> FX Rates date: 05.02.2026
>>> Rates fetched: USD = 12265.77, EUR = 14492.01
>>> Writing rates to cell C7...
>>> Writing to Excel at \\peter\ZDrive\00016243\Desktop\FX_Rate\excel_files\FX_2026.xlsx...
>>> Excel file saved.
>>> Program finished.

It actually wrote to the Excel file on Feb 5, 2026, which is the date I run the program for this article:

Excel Xlwings Output

#3 Windows Task Scheduler

Windows has its own cron job — Task Scheduler. It is a GUI, so pretty simple to set up and run. Once you have the Python file ready, it is as simple as creating a .bat file that you tell Task Scheduler to run at a predetermined interval.

Concluding Thoughts

The case and the Python code were dead simple. However, it automated one task — and did so perfectly. My manager was surprised how well this worked. Unfortunately, my time was running out and I was assigned a giant Power BI project. In hindsight, I think I should have used Python and Streamlit.

The matter was complicated when I was offered a part-time job but chose a prestigious internship at the Central Bank of Uzbekistan, so I declined the offer. However, I still think what I could have automated if I had stayed.

Unfortunately, as far as I know, my efforts have been in vain as the management did not invest in Python (I was a terrible advocate) and no new Python specialist was hired. So much could have been done, but the corporation is not a startup; it favors stability and predictibility over change.