-
Yield Curve Data 4
-
Treasury Curve Data IntroductionLecture1.1
-
Handling DatesLecture1.2
-
Yield Table CreationLecture1.3
-
VisualizationLecture1.4
-
-
Treasury Curve Report 4
-
Treasury Curve ReportLecture2.1
-
Creating our Table Writing FunctionLecture2.2
-
More Modifications for Our Excel WorksheetLecture2.3
-
Borders and ImagesLecture2.4
-
-
Real Estate Data 4
-
Data ProcessingLecture3.1
-
Index OperationsLecture3.2
-
Plotting the Real Estate DataLecture3.3
-
Adjusting for InflationLecture3.4
-
-
Real Estate Report 2
-
Writing the First Report VersionsLecture4.1
-
Adding Color MappingLecture4.2
-
-
Final Report 5
-
Getting StartedLecture5.1
-
Data CreationLecture5.2
-
Data ProcessingLecture5.3
-
Generating the Report & ImagesLecture5.4
-
Finalizing the ReportLecture5.5
-
Treasury Curve Report
Treasury Curve Report¶
This lessons works through how we can create our first report, the treasury curve report. It will take some of our functions created from before and translate it into an automated pipeline for writing an excel sheet for our report.
Creating our First Workbook¶
Before we get to the stage of writing our treasury curve report, we need to make our first excel workbook using the openpyxl library. If you do not have openpyxl installed you can install it through pip install or however you prefer to install your python libraries.
! pip install openpyxl
Requirement already satisfied: openpyxl in /Users/seanmcowen/opt/anaconda3/lib/python3.9/site-packages (3.0.9)
Requirement already satisfied: et-xmlfile in /Users/seanmcowen/opt/anaconda3/lib/python3.9/site-packages (from openpyxl) (1.1.0)
With openpyxl, you can create an excel workbook to modify. To build a blank template, just call openpyxl.Workbook() and it will return a workbook object.
import openpyxl
wb = openpyxl.Workbook()
With our workbook, we can grab the active sheet through wb.active. This will return to us what the current active sheet is in the excel workbook.
# Grab the active worksheet
ws = wb.active
One way to index into cells of a worksheet is to give the letter and number combination and then set a value. So, for example, below we can see how to set the value for the A1 cell to 42.
# Write data to cell by the letter number combination
ws['A1'] = 42
The other way that we can index into cells is by passing the argument of row and column.
# Write data to cell by the row/column combination
ws.cell(row=1,column=5).value = 5
ws.cell(row=5,column=1).value = 5
Just in case the folder does not yet exist for the workbooks folder. We can create it using the os library.
# Save the file
import os
if not os.path.exists("Workbooks"):
os.mkdir("Workbooks")
Then we can save the workbook with wb.save with a filename.
wb.save("Workbooks/Example.xlsx")
Now that we know how to create and modify a workbook we can read in our data to use it.
import pandas as pd
# Read in table
yield_curve_table = pd.read_csv("Tables/Treasury Yield Table.csv", index_col=0)
print(yield_curve_table)
Current 1 Year Ago Curve 1 Year Change 5 Years Ago Curve \
1 Mo 1.48 2.44 -0.96 0.03
3 Mo 1.55 2.45 -0.90 0.04
6 Mo 1.60 2.56 -0.96 0.12
1 Yr 1.59 2.63 -1.04 0.25
2 Yr 1.58 2.48 -0.90 0.67
3 Yr 1.62 2.46 -0.84 1.10
5 Yr 1.69 2.51 -0.82 1.65
7 Yr 1.83 2.59 -0.76 1.97
10 Yr 1.92 2.69 -0.77 2.17
20 Yr 2.25 2.87 -0.62 2.47
30 Yr 2.39 3.02 -0.63 2.75
5 Year Change
1 Mo 1.45
3 Mo 1.51
6 Mo 1.48
1 Yr 1.34
2 Yr 0.91
3 Yr 0.52
5 Yr 0.04
7 Yr -0.14
10 Yr -0.25
20 Yr -0.22
30 Yr -0.36