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

Adding Color Mapping

Adding Color Mapping¶

We want to create some really cool color mapping to add into our excel report. We will break this down into pieces.

RGB Colors¶

RGB colors are three values from 0-255 representing the colors red, green and blue. For our color mapping we are going to need to convert this to hexadecimal colors. We won’t go into the difference here but all you need to know is the following function can take care of the conversion for you.

In [5]:

def rgb_to_hex(rgb: tuple) -> str:
    """
    This function takes a RGB color tuple and returns back
    a hexadecimal version of it.

    Parameters
    ----------
    rgb : tuple
        A tuple of length three with values between 0-255 for each. It
        represents red, green and blue values.

    Returns
    -------
    str
        The hexadecimal representation

    """
    return '%02x%02x%02x' % rgb

# For example just a red color
color = (255,0,0)
hex_color = rgb_to_hex(color)
print(hex_color)

ff0000

Pattern Fills¶

Now we move into changing the background color with pattern fills. The PatternFill object takes three arguments, start_color, end_color and fill_type. For our purposes we will use the same start and end color. And then fill_type will be solid.

We can see how we put a red and green color into our workbook. The attribute of fill on a cell is what is given the fill color.

In [6]:

from openpyxl.styles import PatternFill

# Create a red and green color
red_color = rgb_to_hex((255,0,0))
green_color = rgb_to_hex((0, 255, 0))

# Create basic workbook
ws = wb.active
ws.cell(row=1,column=1).value = -5
ws.cell(row=1,column=2).value = 5

# Build the actual fill colors
red_fill_color = PatternFill(start_color=red_color,
                   end_color=red_color,
                   fill_type='solid')
green_fill_color = PatternFill(start_color=green_color,
                   end_color=red_color,
                   fill_type='solid')

# Change the fill
ws.cell(row=1,column=1).fill = red_fill_color
ws.cell(row=1,column=2).fill = green_fill_color

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

Color Scale¶

Our next order of business is to create a function that scales our values so that the more negative a return is, the more red it is and the more positive, the more green it is.

Let's start with figuring out how we want to do this. We'll divide every number by the maximum absolute value so our data can be bounded between -1 and 1. Below we go through finding the maximum absolute value.

In [7]:

# Find the absolute values
print(real_estate_returns.abs())
print()
print()

# Get the maximum of each column
print(real_estate_returns.abs().max())
print()
print()

# Get the maximum of the max of columns
print(real_estate_returns.abs().max().max())

            Q1        Q2        Q3        Q4
Year                                        
2000  0.019145  0.016295  0.018183  0.015410
2001  0.024656  0.016759  0.015525  0.012418
2002  0.014362  0.017029  0.019829  0.014168
2003  0.012187  0.012184  0.015411  0.028326
2004  0.015643  0.024409  0.038143  0.021346
2005  0.022965  0.031942  0.030194  0.022648
2006  0.015827  0.010232  0.008211  0.010260
2007  0.003739  0.000687  0.011236  0.003155
2008  0.007296  0.025317  0.031519  0.009274
2009  0.007396  0.026413  0.026305  0.007593
2010  0.012011  0.009010  0.009527  0.007248
2011  0.027587  0.017412  0.007316  0.004422
2012  0.010605  0.004222  0.012917  0.007986
2013  0.005207  0.015573  0.015522  0.008166
2014  0.008803  0.019089  0.014360  0.008354
2015  0.010145  0.017208  0.015248  0.008805
2016  0.009170  0.019296  0.017857  0.009101
2017  0.008680  0.024519  0.016899  0.009603
2018  0.014785  0.022364  0.014915  0.005944
2019  0.010980  0.018708  0.011655  0.008440


Q1    0.027587
Q2    0.031942
Q3    0.038143
Q4    0.028326
dtype: float64


0.0381434874979571

With that now squared away, we can put together a function for scaling the values.

In [8]:

def create_color_scale(returns: DataFrame) -> DataFrame:
    """
    Function to build a color scale where each value is bounded
    between -1 and 1 depending on how close it is to the absolute
    maximum in either the positive or negative direction

    Parameters
    ----------
    returns : DataFrame
        Returns data to start with

    Returns
    -------
    DataFrame
        The scaled data
    """
    # Find the maximum returns
    max_abs_return = abs(returns).max().max()
    
    # Divide each number by this
    return returns / max_abs_return

# Test the function
print(create_color_scale(real_estate_returns))

            Q1        Q2        Q3        Q4
Year                                        
2000  0.501924  0.427211  0.476710  0.403999
2001  0.646400  0.439358  0.407005  0.325572
2002  0.376532  0.446444  0.519857  0.371430
2003  0.319508  0.319430  0.404023  0.742608
2004  0.410111  0.639926  1.000000  0.559631
2005  0.602081  0.837421  0.791595  0.593759
2006  0.414928  0.268256  0.215263  0.268995
2007  0.098024 -0.018008 -0.294563 -0.082714
2008 -0.191266 -0.663725 -0.826315 -0.243130
2009  0.193901 -0.692468 -0.689631 -0.199075
2010 -0.314883 -0.236202  0.249777 -0.190012
2011 -0.723244 -0.456476  0.191793  0.115932
2012 -0.278024 -0.110698  0.338634  0.209370
2013  0.136520  0.408270  0.406933  0.214090
2014  0.230787  0.500442  0.376485  0.219005
2015  0.265963  0.451142  0.399753  0.230843
2016  0.240415  0.505880  0.468144  0.238611
2017  0.227549  0.642798  0.443037  0.251752
2018  0.387603  0.586302  0.391035  0.155833
2019  0.287869  0.490456  0.305569  0.221268

Mapping Values to Colors¶

We now want to build a version of this where we can take values and convert them to colors. To do that, we will set either the green or red value to 255 and then lower the values on the other colors as our value goes up (to make it darker as our value is higher. Below this function achieves that.

In [9]:

def value_to_red_green(value: float) -> tuple:
    """
    Convert a value ranging from [-1, 1] to red or green with
    intensity based on the value

    Parameters
    ----------
    value : float
        The value ranging from -1 to 1

    Returns
    -------
    tuple
        The RGB tuple

    """
    # Positive values are green
    if value > 0:
        return (int(255-value*255), 255, int(255-value*255))
    # Negative values are red
    else:
        value = abs(value)
        return (255, int(255-value*255), int(255-value*255))
    
# Test our new function
print(value_to_red_green(.5))

(127, 255, 127)

Now to update the create_color_scale function to be using this. What we are going to do is use applymap twice. This function takes every single value and applys a function to it.

The first function will be for conversion to the red/green rgb values. The second applymap will be for converting the rgb color to a hexadecimal color.

In [10]:

def create_color_scale(returns: DataFrame) -> DataFrame:
    """
    Function to build a color scale where each value is transformed
    into hexadecimal colors based on a scaling where more red is more
    negative and more green is more positive.

    Parameters
    ----------
    returns : DataFrame
        Returns data to start with

    Returns
    -------
    DataFrame
        The color data
    """
    # Find the maximum returns
    max_abs_return = abs(returns).max().max()
    
    # Divide each number by this
    scale = returns / max_abs_return
    
    # Map the conversion to rgb
    scale = scale.applymap(value_to_red_green)
    
    # Map rgb to hex
    scale = scale.applymap(rgb_to_hex)
    
    return scale

# Test the function
print(create_color_scale(real_estate_returns))

          Q1      Q2      Q3      Q4
Year                                
2000  7fff7f  92ff92  85ff85  97ff97
2001  5aff5a  8eff8e  97ff97  abffab
2002  9eff9e  8dff8d  7aff7a  a0ffa0
2003  adffad  adffad  97ff97  41ff41
2004  96ff96  5bff5b  00ff00  70ff70
2005  65ff65  29ff29  35ff35  67ff67
2006  95ff95  baffba  c8ffc8  baffba
2007  e6ffe6  fffafa  ffb3b3  ffe9e9
2008  ffcece  ff5555  ff2c2c  ffc1c1
2009  cdffcd  ff4e4e  ff4f4f  ffcccc
2010  ffaeae  ffc2c2  bfffbf  ffcece
2011  ff4646  ff8a8a  ceffce  e1ffe1
2012  ffb8b8  ffe2e2  a8ffa8  c9ffc9
2013  dcffdc  96ff96  97ff97  c8ffc8
2014  c4ffc4  7fff7f  9eff9e  c7ffc7
2015  bbffbb  8bff8b  99ff99  c4ffc4
2016  c1ffc1  7eff7e  87ff87  c2ffc2
2017  c4ffc4  5bff5b  8eff8e  beffbe
2018  9cff9c  69ff69  9bff9b  d7ffd7
2019  b5ffb5  81ff81  b1ffb1  c6ffc6

Filling Out the Red-Green Map Function¶

With all of the functionality put together, we can take care of our final step: making a red green mapping function. It will take a returns dataframe and put the colors in for each cell (given a starting cell and row to begin at).

In [11]:

def red_green_map(returns: DataFrame, start_row: int, start_column: int) -> None:
    """
    Function which, given a DataFrame, maps the colors to red and
    green based on how positive/negative values are.

    Parameters
    ----------
    returns : DataFrame
        The returns data
    start_row : int
        The starting row
    start_column : int
        The starting column

    """
    # Create the color scale
    color_scale = create_color_scale(real_estate_returns.fillna(0))
    
    # Iterate through rows and columns adding the colors
    for row in range(returns.shape[0]):
        for column in range(returns.shape[1]):
            # Define the color
            color = PatternFill(start_color=color_scale.iloc[row, column],
                   end_color=color_scale.iloc[row, column],
                   fill_type='solid')
            
            # Set the colors
            ws.cell(row=start_row+row,column=start_column+column).fill = color

Update the real estate report function.

In [12]:

def create_real_estate_report(ws: Worksheet, real_estate_returns: DataFrame):
    """
    Function to write the real estate report
    
    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    real_estate_returns : DataFrame
        The returns data for real estate

    """
    
    # Write the table
    write_to_excel(ws, 4, 2, real_estate_returns)
    
    # Create the axis labels
    create_axis_labels(ws)
    
    # Create percentage number formatting
    create_number_formatting(ws, 5, 3, real_estate_returns.shape[0], real_estate_returns.shape[1], "0.0%")
    
    # Map the red-green color map
    red_green_map(real_estate_returns, 5, 3)
    
wb = openpyxl.Workbook()
ws = wb.active
create_real_estate_report(ws, real_estate_returns)
wb.save("Workbooks/Economic Report.xlsx")

Adding Images¶

Finally, we are able to add images by calling ws.add_image and then passing in an image and a place to put it. We will add an image in for our report to finish out this lesson.

In [13]:

from openpyxl.drawing.image import Image


def create_real_estate_report(ws: Worksheet, real_estate_returns: DataFrame, image_name: str):
    """
    Function to write the real estate report
    
    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    real_estate_returns : DataFrame
        The returns data for real estate
    image_name : str
        The name of the image to import in and place
        in the report

    """
    
    # Write the table
    write_to_excel(ws, 4, 2, real_estate_returns)
    
    # Create the axis labels
    create_axis_labels(ws)
    
    # Create percentage number formatting
    create_number_formatting(ws, 5, 3, real_estate_returns.shape[0], real_estate_returns.shape[1], "0.0%")
    
    # Map the red-green color map
    red_green_map(real_estate_returns, 5, 3)
    
    # Add image
    img = Image(image_name)
    ws.add_image(img, 'H5')
    
wb = openpyxl.Workbook()
ws = wb.active
create_real_estate_report(ws, real_estate_returns, "Images/Real Estate vs. Inflation.png")
wb.save("Workbooks/Economic Report.xlsx")

Prev Writing the First Report Versions

Next Getting Started

Automation