Boost Productivity by Automating Excel with Python

Published in

Python in Plain English

7 min readFeb 10, 2021

It was a Saturday afternoon. I was clearing out my desk and dug up a Python 3 primer I’d bought last Christmas and haven’t read yet!

It was February, and the afternoon sun was shining brightly. I felt like someone said to me to start doing this, right now. By chance, I had something to do in Excel next week, so I got a thought if I could use Python to get rid of the pain I had to do in Excel.

Maybe what I came up with is commonly done with a combination of Excel and VBA. So, assuming the same thing can be done with Python, I typed “Excel add-in Python” in a Google search.

Then, several search results listed the strange word ‘xlwings’. So this time I typed ‘xlwings Python’ on YouTube and found a lot of web scraping videos. One of the words I saw was “pandas”, which was also new to me.

After about half an hour of looking around the web, I realized that I could use a derived object called ‘pandas-datareader’ to retrieve data from web services. For another 5 minutes, I was able to browse the web and find out that a Warsaw company called ‘stooq’ is providing stock price information in real-time via ‘pandas-datereader’.

I’ve never written a program in Python before, but I bought a Python 3 book for Christmas, so I thought today was the day to start studying.

Some packages have already been downloaded at the same time you install Anaconda. Fortunately, the ‘xlwings’ I want to use now seems to be included in one of them. The feeling that the rotations of all the gears match has emerged. “Flow?” Mihaly Csikszentmihalyi would say so.

Instructions on how to install ‘xlwings’ can be found in the following documentation.

Installation - xlwings dev documentation

xlwings requires an installation of Excel and therefore only works on Windows and macOS. Note that macOS currently does…

docs.xlwings.org

I launched the terminal App on Mac OS and typed the following two lines:

$ pip install xlwings
$ xlwings addin install

Then, an alert message appears and asks for permission to control “Microsoft Excel”, so I gave permission with [OK]. Then, the display that had stopped on the middle in steps moved and the message “Successfully installed the xlwings add-in! Please restart Excel” appeared.

I launched up Excel right away, and now I see ‘xlwings’ between the tabs “Add-ins” and “Tell me”. (yay!)

From here, we can finally work on software development

Find the Anaconda application icon and double-click on it to launch it. This will bring up a rather massive application screen called ANACONDA.NAVIGATOR. However, this time we will only use the “Spyder” as editor and debugger functions. Click on the [Launch] button at the bottom of the pane with the following icons.

A gorgeous editor window and monitors will appear. This is where you will write your program.

Important point 1

First of all, save the new file to your own folder with a name, even if it is empty. The name of the Excel file to be automated should be the same as the name of the Python file.

I created a brand-new folder named ‘PlayPython’ and put the two files ‘stock_chart.py’ and ‘stock_chart.xlsx’ in it. You don’t have to align to me for the file names, but be sure to check that the names match.

Let’s take a look at the contents of the program.

The beginning of the program looks like this:

import time
import xlwings as xw
import pandas_datareader as spooky

Think of import as importing and using a class created by someone else. You can do a lot of complicated things, but the point this time is: ‘import xlwings as xw’. It is enough to understand this in the sense that the class called ‘xlwings’ should be touched from the heading ‘xw’ for the time being at the execution stage of this program.

Same usage: Write ‘import pandas_datareader as spooky’.

This ‘xw’ or ‘spooky’ is completely my choice and you can use any name you like. :-)

Important point 2

The next point is the most important point of the whole process. The ‘xlwings’ Add-in can only execute the block named “main()”. So, think of ‘xlwings’ as executing the part after what is declared in ‘def main():’.

After that, there are two lines both indented due to Python’s syntax:

def main():    
    # --- create the work book instance
    this_book = xw.Book.caller()
    
    # --- get the 1st page as an active work sheet
    ws = this_book.sheets('Sheet1')

These two lines say that the excel Book which is called from ‘xlwings’ is defined as this_book. And we will name this worksheet ‘Sheet1’ as ‘ws’. The meaning of these lines is clear, right?

It is better to decide the layout of the worksheet once here, so I will also proceed to edit Excel. I’ll call the second, third, and fourth rows of column C the cells for entering the parameter settings, as shown below.

The second, third, and fourth lines of column F store the input information in internal data and then retranslate it for display. Therefore, by comparing columns C and F, we can verify the input of wrong characters.

The next part of the program is to finally write the file to the Excel sheet. After retrieving the time at the moment when this program was executed, we will record the time of file creation along with the message in the cell of B5. Next, we read the parameters from column C, rows 2, 3, and 4, and write them down in column F, rows 2, 3, and 4 for confirmation. This is a simple process.

    # --- time stamp of data generation
    now = time.time()
    ws.range('B5').value = "This file was automatically generated on "+time.ctime(now)    # --- preparation for some initial parameters 
    stock_num = int(ws.range('C2').value)
    stock_name = str(stock_num)+".JP"
    start = ws.range('C3').value
    end = ws.range('C4').value

One additional point: the web service “Stooq” is a service that provides information on the world’s stock markets. For instance, the word ‘JP’ must be added to the end of the number in order to be recognized as a Japanese stock. This is a small detail, but there are country-specific suffixes for the stock numbers, and as you can see in the third line of the list above, the string is added to correct the ending.

Source code

Now, let’s take another look at all the source code of the program!

import time
import xlwings as xw
import pandas_datareader as spookydef main():
    
    # --- create the work book instance
    this_book = xw.Book.caller()
    
    # --- get the 1st page as an active work sheet
    ws = this_book.sheets('Sheet1')    # --- time stamp of data generation
    now = time.time()
    ws.range('B5').value = "This file was automatically generated on "+time.ctime(now)    # --- preparation for some initial parameters 
    stock_num = int(ws.range('C2').value)
    stock_name = str(stock_num)+".JP"
    start = ws.range('C3').value
    end = ws.range('C4').value    # --- debug section: just displaying the value for programmer's self-cheking
    ws.range('F2').value = stock_name
    ws.range('F3').value = start
    ws.range('F4').value = end    # --- enabling web data Scraping    
    got_stk = spooky.DataReader(stock_name, 'stooq', start, end)list_length = len(got_stk)
    ws.range('B6').value = list_length
    ws.range('C6').value = "number of days"    # --- filling labels in an Excel Sheet columns shown below
    excel_columns = ['B7','C7','D7','E7','F7']
    for index, item in enumerate(excel_columns):
        ws.range(item).value = got_stk.columns.values[index]
        
    # --- date/time stamping in each row
    row_number = 8
    for index, item in enumerate(got_stk.index.values):
        ws.range('A'+str(row_number)).value = got_stk.index.values[index]
        row_number += 1    # --- write out prices of the daily stock(s)
    for index in range(list_length):
        ws.range('B'+str(index+8)).value = got_stk.iat[index,0]
        ws.range('C'+str(index+8)).value = got_stk.iat[index,1]
        ws.range('D'+str(index+8)).value = got_stk.iat[index,2]
        ws.range('E'+str(index+8)).value = got_stk.iat[index,3]
        ws.range('F'+str(index+8)).value = got_stk.iat[index,4]
        
    ws.range('E6').value = "all process has done."# end of main()

That’s all.

The efficiency of writing in Python is very good. I started researching on Saturday afternoon, and the program was up and running by nightfall. In fact, it took me longer to look up the documentation and to understand the ‘pandas-datareader’ class, which I used for the first time.

Summary

I’ve posted a video of it in action on Instagram.

https://www.instagram.com/p/CLKt0oehi3N/?igshid=1ll5kzndnshji

If you click on the ‘xlwings’ tab on the Excel sheet, you will be redirected to the tab screen [>] displayed in the upper left corner, and from there you can press the [>] button to start running it.