Excel files#
The openpyxl package is a Python library that allows you to read and write Excel files. It is a great tool for working with spreadsheets.
For additional examples/content not covered here, see the following resources:
Creating a new workbook#
As with any Python package, you need to install openpyxl before you can use it.
Once installed, we need to import openpyxl into our Python script to start using its functionalities. In this example, we will create a new workbook and write some data to it. The example is presented here in its entirety with explanations below.
import openpyxl as xl
workbook = xl.Workbook()
sheet = workbook.active
sheet.title = 'Calculations'
sheet['B2'] = 'Discount Rate'
sheet['C2'] = 0.1
sheet['B3'] = 'Cash Flow'
sheet['C3'] = 100
sheet['B4'] = 'Periods'
sheet['C4'] = 5
sheet['B5'] = 'Discounted Cash Flow'
sheet['C5'] = '=C3/(1+C2)^C4'
# right align the text cells
sheet['B2'].alignment = xl.styles.Alignment(horizontal='right')
sheet['B3'].alignment = xl.styles.Alignment(horizontal='right')
sheet['B4'].alignment = xl.styles.Alignment(horizontal='right')
sheet['B5'].alignment = xl.styles.Alignment(horizontal='right')
workbook.save('dcf.xlsx')
In the first part of the code, we import the openpyxl package and create a new workbook using the xl.Workbook() method. We then access the active sheet in the workbook using the active attribute.
We can then change the title of the active sheet to “Calculations” using the title attribute.
We then write some data to the sheet using the sheet object. We write the data to cells B2, C2, B3, C3, B4, C4, B5, and C5. We also write a formula to cell C5 that calculates the discounted cash flow.
We then right-align the text cells using the Alignment class from the openpyxl.styles module. This is done by setting the horizontal attribute of the Alignment class to 'right'.
Finally, we save the workbook to a file called dcf.xlsx using the save method.
Editing an existing workbook#
We can edit an existing workbook in a similar manner. In this example, we open our previous workbook, update the cash flow value to be 200, and save the updated workbook.
workbook = xl.load_workbook('dcf.xlsx')
sheet = workbook.active
sheet['C3'] = 200
workbook.save('dcf.xlsx')
If we instead wanted to pull values from the workbook to use elsewhere in our python program, we could do something like this.
workbook = xl.load_workbook('dcf.xlsx')
sheet = workbook.active
discount_rate = sheet['C2'].value
# now we can use the discount_rate value in our calculations
# ...
Another example#
This example combines the openpyxl and yfinance packages (see corresponding guide) to create a new workbook and write stock information to it.
It also demonstrates additional styling options available in openpyxl.
import openpyxl as xl
import yfinance as yf
# get data from yahoo finance
tsla_data = yf.Ticker('TSLA')
tsla_info = tsla_data.info
# create a new workbook
workbook = xl.Workbook()
sheet = workbook.active
# write stock information to the workbook
sheet.title = tsla_info['symbol']
sheet['B1'] = tsla_info['longName']
sheet['A2'] = 'Sector'
sheet['B2'] = tsla_info['sector']
sheet['A3'] = 'Industry'
sheet['B3'] = tsla_info['industry']
sheet['A4'] = 'Market Cap'
sheet['B4'] = tsla_info['marketCap'] / 1_000_000
sheet['C4'] = 'millions'
sheet['A5'] = 'Beta'
sheet['B5'] = tsla_info['beta']
sheet['A6'] = 'Closing Price'
sheet['B6'] = tsla_info['previousClose']
# add some style
title_style = xl.styles.Font(bold=True, size=20)
bold_style = xl.styles.Font(bold=True)
right_align = xl.styles.Alignment(horizontal='right')
sheet['B1'].font = title_style
sheet['A2'].font = bold_style
sheet['A3'].font = bold_style
sheet['A4'].font = bold_style
sheet['A5'].font = bold_style
sheet['A6'].font = bold_style
sheet['A2'].alignment = right_align
sheet['A3'].alignment = right_align
sheet['A4'].alignment = right_align
sheet['A5'].alignment = right_align
sheet['A6'].alignment = right_align
sheet['B2'].alignment = right_align
sheet['B3'].alignment = right_align
# change the widths of the columns
sheet.column_dimensions['A'].width = 15
sheet.column_dimensions['B'].width = 20
# change the number formatting
sheet['B4'].number_format = '#,##0.00'
sheet['B6'].number_format = '"$"#,##0.00_);("$"#,##0.00)'
workbook.save('tsla.xlsx')
Which would result in a workbook with the following content (as of Feb 12, 2024):
See here for more information on the available number formats:
https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html