Python Xlsxwriter Create Excel Part 2 (Formula, Link & Named Range)

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Xlsxwriter is a powerful library that can recreate most of the features of Excel, including formula, link, and named range. Read part 1 of the series if you need to get familiarized with xlsxwriter, then it’s time to level up our Excel skills by leveraging Python’s capabilities.

For demonstration, we’ll create a cashflow projection model using xlsxwriter. The video version of the tutorial has very detailed explanations if anything is unclear in the text version here. Or simply leave a comment down below if you need any clarification!

Although the calculation detail itself isn’t important for this tutorial, given the variety of backgrounds of our readers, it’s necessary to even the playing field by giving a brief introduction. NOTE: below is not financial advice ????

Update: part 3 is also available and we walk through how to format the sheets and add filters, charts, sparklines, etc.

Finance 101 – cashflow projection

In finance, a “cashflow projection” refers to a set of calculations carried out over a certain number of years. In our demo, we project out (i.e. calculate) a person’s income and expense for 10 years, with salary increases and inflation taken into account. We also (naively) assume that the person saves all remaining income and put it towards investment, which will also grow at an assumed percentage. Then we can calculate how much money the person has in x number of years.

Setting up our “Excel” workbook and worksheets

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell

wb = xlsxwriter.Workbook(r'C:\Users\JZ\Desktop\PythonInOffice\high_fidelity_excel\hf_excel.xlsx')

ws_1 = wb.add_worksheet('input')
ws_2 = wb.add_worksheet('calculation')

variables = {
            'No. of Years': 10,
            'Income': 50000,
            'Expense': 30000,
            'Income increase': 0.05,
            'Investment return': 0.06,
            'Inflation': 0.03,
                           
    }
row = 1
col =1

We store several input parameters into a dictionary called variables. These are the assumptions we’ll use to project our cashflows. We also initialize two counters row and col which we’ll use later for looping through the cells. Of course we can use the enumerate instead of setting up the counter, but I like the freedom to change the cell coordinates later, and also it’s easier for this purpose. So we’ll stick with the counter for this demo.

Let’s first write the dictionary content into the “input” tab, or ws_1. See the code below how easy that is to loop through and input values into Excel? If we used the .write(“A1”) style, it’s not really looping-friendly.

for i in variables.keys():
    ws_1.write(row,col, i)
    ws_1.write(row, col+1, variables[i])
    row += 1 #row = row + 1

Define Names For Excel Workbook (Using Python)

It’s a common practice to create “names” in Excel workbook. The names basically are just variables that we can access by calling the names given to them, instead of using cell/range addresses. To create & modify names in Excel, go to Formulas -> Name Manager.

In Python, we can use the method define_name() from the Workbook object. We need two arguments for this method: first, the name we want to use, the second argument is a string that points to the cell address or simply a value. For example:

wb.define_name('income_increase',"=input!$C$5")
wb.define_name('investment_return',"=input!$C$6")
wb.define_name('inflation',"=input!$C$7")
wb.define_name('test',"=100")

Once we have these names, we can call them directly without having to worry about whether a value is in cell “G10” or whatever address!

Modeling cashflow projection

It’s a good practice to show the input assumptions again on the actual calculation tab. It’s indeed repetitive, but it helps make things more transparent. For that reason, we’ll bring everything on the <input> tab over to the <calculation> tab. However, the values on <calculation> will be links to <input>.

row = 1

for i in variables.keys():
    ws_2.write(row, col, i)
    ws_2.write(row, col+1, f"=input!{xl_rowcol_to_cell(row, col+1)}")
    row += 1

After resetting the row counter back to 1, we can use a similar loop to write the dictionary content to ws_2 the <calculation> tab.

We use the f-string to help pass variables into the string, so f”=input!{xl_rowcol_to_cell(1,2)}” evaluates to “=input!C2”.

Then we quickly set up the calculation column names and the row numbers (i.e. years). We can use Worksheet.write_row and Worksheet.write_column methods to write iterables to Excel sheets.

calc_cols = ['Year','Income','Expense','Investable income', 'Cumulative welath']
ws_2.write_row(9,1,calc_cols)
ws_2.write_column(10,1,range(variables['No. of Years']))

Then we define a helper function annual_increase() to avoid repetitive code. The function will first write the initial value (year 0), then project forward the remaining 9 values.

• ws (worksheet) as an argument so it’s easy for us to write to any worksheet
• name_range as an argument so we can pass different names into the function for calculation

def annual_increase(ws, start_row, start_col, n, initial_value, name_range):
    ws.write(start_row, start_col, initial_value)
    for i in range(n-1):
        ws.write(start_row+1+i, start_col,f"={xl_rowcol_to_cell(start_row+i,start_col)}*(1+{name_range})")

For linking to external Excel files, it’s the same method, just add the full path to the file you want to link to, then you are good to go.

Writing Excel Formulas With Python

We can use the annual_increase helper function to create the formula “array” for the first two calculation items, “Income” and “Expense”.

annual_increase(ws_2, 10, 2,variables['No. of Years'], variables['Income'], 'income_increase')
annual_increase(ws_2, 10, 3,variables['No. of Years'], variables['Expense'], 'inflation')

To write the formulas for “Investable Income” calculation, simply calculate the difference between Income and Expense.

for i in range(variables['No. of Years']):
    ws_2.write(10+i,
               4,
               f'={xl_rowcol_to_cell(10+i, 2)}-{xl_rowcol_to_cell(10+i, 3)}')

Finally, write the formulas for the “Cumulative Wealth” column. And last but not least, don’t forget to wb.close() the workbook. Otherwise nothing will be saved.

ws_2.write(10, 5, f'={xl_rowcol_to_cell(10, 4)}')
for i in range(variables['No. of Years']-1):
    ws_2.write(11+i, 5,
               f"={xl_rowcol_to_cell(10+i, 5)}*(1+investment_return)+{xl_rowcol_to_cell(11+i,4)}",
               )

wb.close()

Pro Tips:

As you saw in the process of making the Excel formulas, we have to constantly switch between (0,0) to “A1” style. It’s pretty easy to make mistakes. Therefore, it will make your life a lot easier if you save the code into a script so you can run & test it as you progress.

Putting It All Together

If you read this far, here’s a bonus. All code is pieced together so you don’t have to copy from each of the sections above ????

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell
wb = xlsxwriter.Workbook(r'C:\Users\JZ\Desktop\PythonInOffice\high_fidelity_excel\hf_excel.xlsx')
ws_1 = wb.add_worksheet('input')
ws_2 = wb.add_worksheet('calculation')
variables = {
            'No. of Years': 10,
            'Income': 50000,
            'Expense': 30000,
            'Income increase': 0.05,
            'Investment return': 0.06,
            'Inflation': 0.03,
                           
    }
row = 1
col =1
for i in variables.keys():
    ws_1.write(row,col, i)
    ws_1.write(row, col+1, variables[i])
    row += 1 #row = row + 1

wb.define_name('income_increase',"=input!$C$5")
wb.define_name('investment_return',"=input!$C$6")
wb.define_name('inflation',"=input!$C$7")
wb.define_name('test',"=100")
row = 1

for i in variables.keys():
    ws_2.write(row, col, i)
    ws_2.write(row, col+1, f"=input!{xl_rowcol_to_cell(row, col+1)}")
    row += 1
   

calc_cols = ['Year','Income','Expense','Investable income', 'Cumulative welath']
ws_2.write_row(9,1,calc_cols)
ws_2.write_column(10,1,range(variables['No. of Years']))

def annual_increase(ws, start_row, start_col, n, initial_value, name_range):
    ws.write(start_row, start_col, initial_value)
    for i in range(n-1):
        ws.write(start_row+1+i, start_col,f"={xl_rowcol_to_cell(start_row+i,start_col)}*(1+{name_range})")


annual_increase(ws_2, 10, 2,variables['No. of Years'], variables['Income'], 'income_increase')
annual_increase(ws_2, 10, 3,variables['No. of Years'], variables['Expense'], 'inflation')

for i in range(variables['No. of Years']):
    ws_2.write(10+i,
               4,
               f'={xl_rowcol_to_cell(10+i, 2)}-{xl_rowcol_to_cell(10+i, 3)}')

ws_2.write(10, 5, f'={xl_rowcol_to_cell(10, 4)}')
for i in range(variables['No. of Years']-1):
    ws_2.write(11+i, 5,
               f"={xl_rowcol_to_cell(10+i, 5)}*(1+investment_return)+{xl_rowcol_to_cell(11+i,4)}",
               )

wb.close()