Mastering Excel Wizardry: Unleash Your Boss-Level Skills with Python and Xlwings

10 min readJun 27, 2023

Welcome, Excel enthusiasts and aspiring wizards of data manipulation! In this exciting journey, we’ll dive into the realm of Excel automation using the powerful duo of Python and Xlwings. You don’t need to be a seasoned developer to harness the magic of programming and impress your boss with your newfound skills. Whether you’re a data analyst, project manager, or simply someone who wants to supercharge their spreadsheet prowess, this article is tailored just for you. Get ready to unlock a whole new level of productivity as we embark on this adventure of creating Excel sheets that will leave your boss in awe!

you can also view this article’s codes using this link.

Exploring Xlwings: A Powerful Python Library for Excel Integration

Xlwings, the superhero of Excel integration, is here to revolutionize the way you interact with spreadsheets. As a Python library, xlwings serves as a bridge between the world of Excel and the versatility of Python programming. Its robust features empower you to automate tasks, perform complex data manipulations, and unleash the true potential of Excel through the simplicity and flexibility of Python code. Whether you’re a data enthusiast, a business professional, or a non-developer looking to enhance your Excel skills, xlwings is your go-to tool for taking Excel to new heights.

One of the standout features of Xlwings is its seamless integration with Excel. It allows you to leverage the powerful functionality of Excel while harnessing the flexibility and efficiency of Python. You can write Python code to read, write, and manipulate data in Excel, creating a dynamic connection between your spreadsheet and your code. Whether you want to automate repetitive tasks, generate reports, or perform complex calculations, xlwings provides the necessary tools to streamline your workflow and boost productivity. With xlwings as your trusty sidekick, you’ll be able to tap into the immense capabilities of Excel with the ease and elegance of Python programming.

Understanding the Magic: How Xlwings Works Behind the Scenes

As we embark on our journey of Excel automation with xlwings, it’s essential to take a moment to understand the magic happening behind the scenes. xlwings acts as a powerful bridge between the Excel application and Python, enabling seamless communication and integration between the two. Underneath its user-friendly interface lies a sophisticated framework that leverages the power of the COM (Component Object Model) interface on Windows or AppleScript on macOS. This allows xlwings to interact with Excel, create workbooks, access worksheets, modify cells, apply formatting, execute formulas, and even generate charts, all through simple Python commands. So, let’s unravel the inner workings of xlwings and discover how it empowers us to unleash the true potential of Excel using the elegance and flexibility of Python programming.

Getting Started: A Simple Example of Creating an Excel Sheet with Xlwings

In our previous article, we ventured into the exciting world of web scraping and learned how to extract valuable data from websites. We explored Yahoo Finance, a treasure trove of financial information, and successfully scraped the latest prices of four major indices: S&P 500, DOW30, Nasdaq, and Russel2000. Now, armed with this data, we’re ready to take our skills to the next level by integrating Python and xlwings to create an Excel sheet that showcases these index prices dynamically. Our project is born out of the desire to streamline financial analysis and empower non-developers with the ability to impress their bosses through the efficient automation of data processing tasks.

For you who have not read the last article or forgotten the codes, I will place the whole code of last article in the following block:

import requests
from bs4 import BeautifulSoup

url = 'https://finance.yahoo.com'
response = requests.get(url)
html_content = response.content
soup = BeautifulSoup(html_content, 'html.parser')

#scrap the S&P 500 price
sp_price_element = soup.find('fin-streamer', {'data-symbol': '^GSPC', 'data-field': 'regularMarketPrice'})
sp_price = sp_price_element['value']
#scrap the DOW30 price
dow30_price_element = soup.find('fin-streamer', {'data-symbol': '^DJI', 'data-field': 'regularMarketPrice'})
dow30_price = dow30_price_element['value']
#scrap the nasdaq price
nasdaq_price_element = soup.find('fin-streamer', {'data-symbol': '^IXIC', 'data-field': 'regularMarketPrice'})
nasdaq_price = nasdaq_price_element['value']
#scrap the RUSSEL price
russel_price_element = soup.find('fin-streamer', {'data-symbol': '^RUT', 'data-field': 'regularMarketPrice'})
russel_price = russel_price_element['value']

So, let’s dive into the world of xlwings and witness the magic unfold as we write Python code to bring our scraped data to life within Excel!

To begin our journey into the realm of Excel automation with xlwings, let’s set up our coding environment. If you haven’t already, follow these step-by-step instructions to create a new project in PyCharm:

1. Open PyCharm and click on ‘Create New Project’ or go to ‘File’ > ‘New Project.’

2. Choose a location to save your project and give it a meaningful name that reflects the purpose of our Excel adventure.

3. Select the Python interpreter of your choice (ensure it has xlwings installed) or create a new virtual environment.

4. Click on ‘Create’ to create the project.

Once your project is set up, we can start installing the necessary dependencies. Luckily, installing xlwings is a breeze. Open the terminal within PyCharm by clicking on ‘Terminal’ at the bottom of the PyCharm window. Then, run the following command:

pip install xlwings

Sit back, and let PyCharm handle the installation magic for you.

With Xlwings successfully installed, it’s time to import this mighty library into our Python script. At the top of your Python file, add the following line:

import xlwings as xw

This statement establishes the connection between your code and the xlwings functionality. Now, we’re ready to delve into the exciting world of Excel automation with Python.

To demonstrate the power of xlwings, let’s write a simple code snippet that inputs the latest index prices into an Excel sheet. Assuming you have the prices stored in variables, such as `sp_price`, `dow30_price`, `nasdaq_price`, and `russel_price`, use the following code to write these prices into an Excel sheet:

# Connect to the active Excel application
wb = xw.Book()
sheet = wb.sheets.active

# Write the index prices to specific cells
sheet.range('A1').value = 'S&P 500'
sheet.range('A2').value = sp_price
sheet.range('B1').value = 'DOW30'
sheet.range('B2').value = dow30_price
sheet.range('C1').value = 'Nasdaq'
sheet.range('C2').value = nasdaq_price
sheet.range('D1').value = 'Russel2000'
sheet.range('D2').value = russel_price

# Save and close the workbook
wb.save('index_prices.xlsx')
wb.close()

This code establishes a connection with Excel, writes the index prices to specific cells (e.g., A2, B2, C2, D2), saves the workbook as ‘index_prices.xlsx,’ and finally, closes the workbook.

Now you’re equipped with the necessary tools to automate Excel tasks using Xlwings and Python. Brace yourself for the wonders you can achieve with this powerful combination!

Going Beyond the Basics: Exploring Complex Excel Manipulations with Xlwings

In our quest to explore complex Excel manipulations with xlwings, let’s take our previous scenario to the next level. Previously, we fetched the latest index prices from Yahoo Finance and stored them in an Excel sheet. Now, we want to automate the process by retrieving the prices every 5 minutes and capturing them in Excel dynamically. Here’s how we can modify our existing code to achieve this, while also incorporating additional conditions.

To begin, we’ll need to make use of a loop to continuously fetch the prices at regular intervals. Import the ‘time’ module at the beginning of your code by adding the line import time. Next, wrap the existing code inside a 'while True' loop to create an infinite loop. This will allow us to fetch the prices repeatedly until we manually stop the program.

import time

We would also like to write the exact time that price was fetched in the first column. To do so, we need another module named datetime. so let’s import it too:

from datetime import datetime

In the first line, we set the URL to ‘https://finance.yahoo.com'. This is the website we will be visiting to retrieve the data for our project. It contains valuable information that we want to extract and use. In the next line, we create a new Excel workbook using xlwings:

url = 'https://finance.yahoo.com'
wb = xw.Book()

Next, we set up our Excel sheet for data storage and visualization. We start by connecting to the active Excel application and accessing the active sheet. Then, we define the column headers for our data, such as ‘Time’, ‘S&P 500’, ‘DOW 30’, ‘NASDAQ’, and ‘RUSSEL 2000’. To make the headers visually appealing, we assign different colors to each column. The timestamp column is highlighted in vibrant green, while the other columns representing index prices are color-coded with red, blue, yellow, and purple. This color scheme helps distinguish the different columns at a glance:

# Connect to the active Excel application
sheet = wb.sheets.active
# Define our column headers and their colors
sheet.range('A1').value = 'Time'  # Header of A column
sheet.range('A:A').color = (0, 255, 0)  # Green for timestamp column
sheet.range('B1').value = 'S&P 500'  # Header of B column
sheet.range('B:B').color = (255, 0, 0)  # Red for S&P 500 price column
sheet.range('C1').value = 'DOW 30'  # Header of C column
sheet.range('C:C').color = (0, 0, 255)  # Blue for DOW30 price column
sheet.range('D1').value = 'NASDAQ'  # Header of D column
sheet.range('D:D').color = (255, 255, 0)  # Yellow for Nasdaq price column
sheet.range('E1').value = 'RUSSEL 2000'  # Header of E column
sheet.range('E:E').color = (255, 0, 255)  # Purple for Russel2000 price column

Now that we have initialized the scopes, lets start building our while loop. first, we need our loop to iterate every 5 minutes which is 300 seconds:

while True:

  time.sleep(300)

Now that our loop structure is defined, let’s insert the price fetching lines inside it:

while True:

  response = requests.get(url)
  html_content = response.content
  soup = BeautifulSoup(html_content, 'html.parser')
  #scrap the S&P 500 price
  sp_price_element = soup.find('fin-streamer', {'data-symbol': '^GSPC', 'data-field': 'regularMarketPrice'})
  sp_price = sp_price_element['value']
  #scrap the DOW30 price
  dow30_price_element = soup.find('fin-streamer', {'data-symbol': '^DJI', 'data-field': 'regularMarketPrice'})
  dow30_price = dow30_price_element['value']
  #scrap the nasdaq price
  nasdaq_price_element = soup.find('fin-streamer', {'data-symbol': '^IXIC', 'data-field': 'regularMarketPrice'})
  nasdaq_price = nasdaq_price_element['value']
  #scrap the RUSSEL price
  russel_price_element = soup.find('fin-streamer', {'data-symbol': '^RUT', 'data-field': 'regularMarketPrice'})
  russel_price = russel_price_element['value']

  time.sleep(300)

Now, we retrieve the current timestamp and write the index prices to specific cells in the Excel sheet. Firstly, we capture the current date and time using the datetime.now() function and format it as a string. Then, we write the timestamp to the next empty cell in column A using sheet.range(f"A{sheet.cells.last_cell.row}").end('up').offset(1).value. Similarly, we write the corresponding index prices to the next empty cells in columns B, C, D, and E. This ensures that the data is stored in the appropriate cells row by row. Finally, we save the workbook as 'index_prices.xlsx' and close it. With these steps, we are effectively updating our Excel sheet with the latest timestamp and index prices every time the code block is executed, providing real-time data analysis and tracking:

while True:
    response = requests.get(url)
    html_content = response.content
    soup = BeautifulSoup(html_content, 'html.parser')
    # scrap the S&P 500 price
    sp_price_element = soup.find('fin-streamer', {'data-symbol': '^GSPC', 'data-field': 'regularMarketPrice'})
    sp_price = sp_price_element['value']
    #scrap the DOW30 price
    dow30_price_element = soup.find('fin-streamer', {'data-symbol': '^DJI', 'data-field': 'regularMarketPrice'})
    dow30_price = dow30_price_element['value']
    #scrap the nasdaq price
    nasdaq_price_element = soup.find('fin-streamer', {'data-symbol': '^IXIC', 'data-field': 'regularMarketPrice'})
    nasdaq_price = nasdaq_price_element['value']
    #scrap the RUSSEL price
    russel_price_element = soup.find('fin-streamer', {'data-symbol': '^RUT', 'data-field': 'regularMarketPrice'})
    russel_price = russel_price_element['value']

    # Retrieve the current timestamp
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # Write the index prices to specific cells
    sheet.range(f"A{sheet.cells.last_cell.row}").end('up').offset(1).value = timestamp
    sheet.range(f"B{sheet.cells.last_cell.row}").end('up').offset(1).value = sp_price
    sheet.range(f"C{sheet.cells.last_cell.row}").end('up').offset(1).value =dow30_price
    sheet.range(f"D{sheet.cells.last_cell.row}").end('up').offset(1).value = nasdaq_price
    sheet.range(f"E{sheet.cells.last_cell.row}").end('up').offset(1).value = russel_price
   
    # Save and close the workbook
    wb.save('index_prices.xlsx')
    time.sleep(300)

The final outcome will be like this:

For those who might need it, I put the whole code here too:

import requests
from bs4 import BeautifulSoup
import xlwings as xw
from datetime import datetime
import time

url = 'https://finance.yahoo.com'
wb = xw.Book()
# Connect to the active Excel application
sheet = wb.sheets.active
# Define our column headers and their colors
sheet.range('A1').value = 'Time'  # Header of A column
sheet.range('A:A').color = (0, 255, 0)  # Green for timestamp column
sheet.range('B1').value = 'S&P 500'  # Header of B column
sheet.range('B:B').color = (255, 0, 0)  # Red for S&P 500 price column
sheet.range('C1').value = 'DOW 30'  # Header of C column
sheet.range('C:C').color = (0, 0, 255)  # Blue for DOW30 price column
sheet.range('D1').value = 'NASDAQ'  # Header of D column
sheet.range('D:D').color = (255, 255, 0)  # Yellow for Nasdaq price column
sheet.range('E1').value = 'RUSSEL 2000'  # Header of E column
sheet.range('E:E').color = (255, 0, 255)  # Purple for Russel2000 price column

while True:
    response = requests.get(url)
    html_content = response.content
    soup = BeautifulSoup(html_content, 'html.parser')
    # scrap the S&P 500 price
    sp_price_element = soup.find('fin-streamer', {'data-symbol': '^GSPC', 'data-field': 'regularMarketPrice'})
    sp_price = sp_price_element['value']
    #scrap the DOW30 price
    dow30_price_element = soup.find('fin-streamer', {'data-symbol': '^DJI', 'data-field': 'regularMarketPrice'})
    dow30_price = dow30_price_element['value']
    #scrap the nasdaq price
    nasdaq_price_element = soup.find('fin-streamer', {'data-symbol': '^IXIC', 'data-field': 'regularMarketPrice'})
    nasdaq_price = nasdaq_price_element['value']
    #scrap the RUSSEL price
    russel_price_element = soup.find('fin-streamer', {'data-symbol': '^RUT', 'data-field': 'regularMarketPrice'})
    russel_price = russel_price_element['value']

    # Retrieve the current timestamp
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # Write the index prices to specific cells
    sheet.range(f"A{sheet.cells.last_cell.row}").end('up').offset(1).value = timestamp
    sheet.range(f"B{sheet.cells.last_cell.row}").end('up').offset(1).value = sp_price
    sheet.range(f"C{sheet.cells.last_cell.row}").end('up').offset(1).value =dow30_price
    sheet.range(f"D{sheet.cells.last_cell.row}").end('up').offset(1).value = nasdaq_price
    sheet.range(f"E{sheet.cells.last_cell.row}").end('up').offset(1).value = russel_price
    # Save and close the workbook
    wb.save('index_prices.xlsx')
    time.sleep(300)

Conclusion

As we bring our exploration of xlwings and Python for Excel automation to a close, we stand in awe of the immense potential they hold. With xlwings, we have unlocked a powerful tool that seamlessly integrates Python and Excel, empowering us to automate complex manipulations, streamline data processing tasks, and unleash the true power of Excel with the elegance and flexibility of Python programming. From simple data inputs to dynamic updates and visual enhancements, xlwings has proven to be an invaluable asset for non-developers seeking to impress their bosses with their programming prowess. By harnessing the capabilities of xlwings and leveraging Python’s vast ecosystem, the possibilities for Excel automation are virtually limitless. So, dive into this powerful duo, explore its depths, and let your imagination run wild as you create impressive Excel solutions that will leave your boss in awe.

If you had any problems or questions, feel free to ask me!