Asset Classes

Free investment financial education


Multilingual content from IBKR

Close Navigation
Learn more about IBKR accounts
Use a Python Script to Read and Write on Excel File in Python

Use a Python Script to Read and Write on Excel File in Python

Posted June 21, 2024 at 11:19 am
Sang-Heon Lee
SHLee AI Financial Model

This post demonstrates how to utilize the xlwings Python package to execute a Python script to read and write Excel in Python.

Running a Python script to read and write Excel file in Python

Unlike the previous post, I will demonstrate how to read and write an Excel file in Python without relying on a button in Excel.

By bypassing the use of an Excel button, we can verify the functionality in Python to ensure it operates correctly. Once confirmed, we can then integrate this process by clicking a button within Excel.

This approach is preferred because testing Python functionalities directly from Excel right from the beginning can be quite cumbersome.

Python code

The Python code below handles three inputs: whether the file is new or existing, the file name (whether it’s new or existing), and whether the Excel instance should be visible or not.

import os
import xlwings as xw
import sys
# Check if a file exists and exit with a warning message if it does.    
def exit_file_exists(base_name, directory):
    filename = os.path.join(directory, base_name)
    if os.path.exists(filename):
        print(f"""File '{filename}' already exists. 
              Exiting the program.""")
        sys.exit(0)  # Use 0 for a normal termination
    return filename
# Input
# new or existing file
b_excel_new_file = True #False
# new or existing filename
file_name_w_ext = "TestBook9.xlsm" 
# Excel instance visible or not
b_excel_visible = True #False
# Excel instance
app = xw.App(visible=b_excel_visible)
# create file 
if b_excel_new_file == True:
    # Get the directory of the running Python file
    current_directory = os.path.dirname(os.path.abspath(__file__))
    # Check if the file exists
    filename = exit_file_exists(file_name_w_ext, current_directory)
    # New workbook
    wb = xw.Book()
    print(f"File '{filename}' is newly created.")
    filename = os.path.join(os.path.dirname(os.path.abspath(__file__)), 
    wb = xw.Book(filename)
# select first sheet
sht1 = wb.sheets[0]
# 1) one cell
sht1.range('A1').value = 'Hello World'
# 2) a range of cells
sht1.range('A3:D4').value = 11
# 3) formula
sht1.range('F3').formula = '=SUM(A3:D3)'
#4) clear cells
# exit instance only when xw.App(visible=False)
if b_excel_visible == False:  # save 
    app.kill() # shut down excel process

A result is simple as follows.

After creating a new file named TestBook9.xlsm, attempting to create another new file with the same name, TestBook9.xlsm, will result in an error message. The program will then exit to ensure the existing TestBook9.xlsm file is not overwritten.

The folder and file names were too long, so they have been abbreviated to “aaa,” “bbb,” and “ccc” for convenience.

runfile('', wdir='bbb')
File 'ccc\TestBook9.xlsm' is newly created.
runfile('', wdir='bbb')
File 'ccc\TestBook9.xlsm' already exists. 
     Exiting the program.

Originally posted on SH Fintech Modeling.

Join The Conversation

If you have a general question, it may already be covered in our FAQs. If you have an account-specific question or concern, please reach out to Client Services.

Leave a Reply

Disclosure: Interactive Brokers

Information posted on IBKR Campus that is provided by third-parties does NOT constitute a recommendation that you should contract for the services of that third party. Third-party participants who contribute to IBKR Campus are independent of Interactive Brokers and Interactive Brokers does not make any representations or warranties concerning the services offered, their past or future performance, or the accuracy of the information provided by the third party. Past performance is no guarantee of future results.

This material is from SHLee AI Financial Model and is being posted with its permission. The views expressed in this material are solely those of the author and/or SHLee AI Financial Model and Interactive Brokers is not endorsing or recommending any investment or trading discussed in the material. This material is not and should not be construed as an offer to buy or sell any security. It should not be construed as research or investment advice or a recommendation to buy, sell or hold any security or commodity. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.

IBKR Campus Newsletters

This website uses cookies to collect usage information in order to offer a better browsing experience. By browsing this site or by clicking on the "ACCEPT COOKIES" button you accept our Cookie Policy.