Yield Curve Data 4
- Lecture1.1
  
  Treasury Curve Data Introduction
- Lecture1.2
  
  Handling Dates
- Lecture1.3
  
  Yield Table Creation
- Lecture1.4
  
  Visualization
Treasury Curve Report 4
- Lecture2.1
  
  Treasury Curve Report
- Lecture2.2
  
  Creating our Table Writing Function
- Lecture2.3
  
  More Modifications for Our Excel Worksheet
- Lecture2.4
  
  Borders and Images
Real Estate Data 4
- Lecture3.1
  
  Data Processing
- Lecture3.2
  
  Index Operations
- Lecture3.3
  
  Plotting the Real Estate Data
- Lecture3.4
  
  Adjusting for Inflation
Real Estate Report 2
- Lecture4.1
  
  Writing the First Report Versions
- Lecture4.2
  
  Adding Color Mapping
Final Report 5
- Lecture5.1
  
  Getting Started
- Lecture5.2
  
  Data Creation
- Lecture5.3
  
  Data Processing
- Lecture5.4
  
  Generating the Report & Images
- Lecture5.5
  
  Finalizing the Report

More Modifications for Our Excel Worksheet

More Modifications for Our Excel Worksheet¶

Converting Column Number to Letter¶

You may have noticed that in excel, the columns are letters. While we can use the number for the column most of the time, we might at times need the letter version. To convert an integer version of the column to the letter version, you can use get_column_letter.

In [14]:

from openpyxl.utils.cell import get_column_letter

# The get_column_letter function will convert a number to a letter for excel
print(get_column_letter(1))

Adjusting Column Width¶

The way to adjust the width of a column in excel is to use ws.column_dimensions and then change the width of column (which is found by indexing into ws.column_dimensions with the letter version of the column).

In [15]:

from typing import List

def adjust_column_widths(ws: Worksheet, start: int, values: List[int]):
    """
    Function to adjust the width of the columns starting at the start value
    to the values listed in values

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start : int
        The column to begin adjustments at
    values : List[int]
        The widths to set each column to in order


    """
    # Iterate through the values
    for i, width in enumerate(values):
        # Set the width of the column
        ws.column_dimensions[get_column_letter(start+i)].width = width

wb = openpyxl.Workbook()
ws = wb.active

write_to_excel(ws, 3, 1, yield_curve_table)

adjust_column_widths(ws,1,[2,10,20,5])

wb.save("Workbooks/Example 5.xlsx")

Given our new function, we want to update the write_treasury_sheet function. We will update the column lengths starting at column 2 (B) to have a length of 15.

In [16]:

# Update the function

def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
    """
    Function to fill out the treasury curve data

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    yield_curve_table : DataFrame
        The yield curve data

    """
    #Write the yield curve table
    write_to_excel(ws, 3, 1, yield_curve_table)
    
    #Color the yield curve changes red/green
    red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
    red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
    
    #Adjust the column widths for the table
    adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
    
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")

Font¶

For each cell, there is an attribute of font which can be modified to change the style, size, etc of the font. We access it through the font attribute of the cell, and we can change it by passing a Font object. Some of the things that can be modified within the font object is bolding with bold = True/False.

One thing to note is that fonts are immutable when taken from the attribute so you need to use copy if you want to take a font and modify it.

In [17]:

from openpyxl.styles import Font
from copy import copy

# Create a new workbook
wb = openpyxl.Workbook()
ws = wb.active

# Write our table like before
write_to_excel(ws, 3, 1, yield_curve_table)

# Create a font with bold = True
font = Font(bold=True)

# Set the font on the cell at A4 to the font we just defined
ws.cell(row=4,column=1).font = font

# Or we can take the current fond and modify it like so to make it bold
font = copy(ws.cell(row=3,column=2).font)
print(font)
font.bold = True
ws.cell(row=3,column=2).font = font

wb.save("Workbooks/Example 6.xlsx")

<openpyxl.styles.fonts.Font object>
Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'

Now we want to make a function which goes through cells in a line and bolds them to make it easier to bold a column or row.

In [18]:

# Create the function

def bold_cells_line(ws: Worksheet, start_row: int, start_column: int,
                    length: int, vertical: bool = True) -> None:
    """
    Function which bolds a column or row of a worksheet

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to start at for bolding
    start_column : int
        The column to start at for bolding
    length : int
        The length of the column or row to bold through
    vertical : bool, optional
        Decides if it is a row or column of the parameter length
        that is bolded. The default is True.

    """
    
    # Iterate through the length of the row/column
    for i in range(length):
        # Increment the row or column depending on if vertical is true
        if vertical:
            cell = ws.cell(row=start_row+i,column=start_column)
        else:
            cell = ws.cell(row=start_row,column=start_column+i)
            
        # Copy the font and set the bold property to true
        font = copy(cell.font)
        font.bold = True
        cell.font = font
        
# Update the treasury sheet function
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
    """
    Function to fill out the treasury curve data

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    yield_curve_table : DataFrame
        The yield curve data

    """
    #Write the yield curve table
    write_to_excel(ws, 3, 1, yield_curve_table)
    
    #Color the yield curve changes red/green
    red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
    red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
    
    #Adjust the column widths for the table
    adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
    
    #Bold the index and columns
    bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
    bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
    
    
# Test the new function
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")

Merging Cells¶

We can merge cells together by passing a range in the format X:Y where X is the upper left corner and Y is the bottom right corner. For example A1:F1 passed into the merge_cells function will merge those cells together. Below is an example of it.

In [19]:

# Set up the worksheet
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 3, 1, yield_curve_table)

# Merging cells can be done with ws.merge_cells
ws.merge_cells('A1:F1')

wb.save("Workbooks/Example 7.xlsx")

Getting Coordinates¶

There are two more functions we want to learn more about as they will help us when we move on to some other improvements in our workbook.

coordinate_from_string: This returns the coordinates for the string representation of a cell
column_index_from_string: This converts a letter to the actual number in the excel sheet for a coordinate

Below we show both examples.

In [20]:

from openpyxl.utils.cell import coordinate_from_string, column_index_from_string

# coordinate_from_string returns the coordinates for the string representation of a cell  
print(coordinate_from_string("B6"))

# column_index_from_string converts a letter to the actual number in the excel sheet for a coordinate
print(column_index_from_string("B"))

('B', 6)
2

Title Function Creation Challenge¶

Given what we have learned so far, here is a challenge. Create a function for creating the title in a workbook which takes as arguments the worksheet, the area to make the title (such as A1:A5), and the text to put in the title.

It should do the following:

Merge the cells for the area
Find the row/column of the most upper left cell in the area parameter
Set the text of this cell to the text (which will set it for the full merged cell)
Change the font of this cell to be of size 16, with bold as true, and a color of "283747".

We will also learn how to do alignment when we see the answer!

Prev Creating our Table Writing Function

Next Borders and Images

Automation