-
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 Data Introduction
Excel Report Automation¶
A common task asked of analysts early on in their career is to keep updating certain excel reports. While important, it can become tedious and boring. This course is meant to teach the skill of automatically writing excel reports to save time.
To get started, one needs the to have a Data folder with the relevant data sources (which can be downloaded from the FinanceAndPython.com github repository).
Treasury Curve Data Introduction¶
The treasury curve is a curve of yields that different treasury instruments yield at any point in time. This data has been sourced from FRED and we will use it to create part of our economic dashboard. The first thing we are going to do is actually read in the data and do some simple parsing on it.
This lesson is not going to use any excel reporting quite yet, but it is essential to understand this part of the workflow as the data prep is just as important as the actual automation of excel writing, and some might even say more important.
import pandas as pd
# Import the data, index column is the first column
yield_curve = pd.read_csv("Data/Yield Curve.csv", index_col=0)
# Convert the index column to be a datetime object
yield_curve.index = pd.to_datetime(yield_curve.index)
print(yield_curve)
1 Mo 2 Mo 3 Mo 6 Mo 1 Yr 2 Yr 3 Yr 5 Yr 7 Yr 10 Yr \
Date
1990-01-02 NaN NaN 7.83 7.89 7.81 7.87 7.90 7.87 7.98 7.94
1990-01-03 NaN NaN 7.89 7.94 7.85 7.94 7.96 7.92 8.04 7.99
1990-01-04 NaN NaN 7.84 7.90 7.82 7.92 7.93 7.91 8.02 7.98
1990-01-05 NaN NaN 7.79 7.85 7.79 7.90 7.94 7.92 8.03 7.99
1990-01-08 NaN NaN 7.79 7.88 7.81 7.90 7.95 7.92 8.05 8.02
... ... ... ... ... ... ... ... ... ... ...
2020-03-12 0.41 0.33 0.33 0.37 0.39 0.50 0.58 0.66 0.82 0.88
2020-03-13 0.33 0.30 0.28 0.38 0.38 0.49 0.58 0.70 0.89 0.94
2020-03-16 0.25 0.25 0.24 0.29 0.29 0.36 0.43 0.49 0.67 0.73
2020-03-17 0.12 0.18 0.19 0.24 0.30 0.47 0.54 0.66 0.91 1.02
2020-03-18 0.04 0.03 0.02 0.08 0.21 0.54 0.66 0.79 1.08 1.18
20 Yr 30 Yr
Date
1990-01-02 NaN 8.00
1990-01-03 NaN 8.04
1990-01-04 NaN 8.04
1990-01-05 NaN 8.06
1990-01-08 NaN 8.09
... ... ...
2020-03-12 1.27 1.49
2020-03-13 1.31 1.56
2020-03-16 1.10 1.34
2020-03-17 1.45 1.63
2020-03-18 1.60 1.77
[7559 rows x 12 columns]
For working with this data, we do not need the 2 month yield and we also want to get rid of any dates which have null values
# Get rid of the 2 month rate
yield_curve = yield_curve.drop(columns="2 Mo")
# Drop any dates that don't have all yields
yield_curve = yield_curve.dropna()
print(yield_curve)
1 Mo 3 Mo 6 Mo 1 Yr 2 Yr 3 Yr 5 Yr 7 Yr 10 Yr 20 Yr \
Date
2001-07-31 3.67 3.54 3.47 3.53 3.79 4.06 4.57 4.86 5.07 5.61
2001-08-01 3.65 3.53 3.47 3.56 3.83 4.09 4.62 4.90 5.11 5.63
2001-08-02 3.65 3.53 3.46 3.57 3.89 4.17 4.69 4.97 5.17 5.68
2001-08-03 3.63 3.52 3.47 3.57 3.91 4.22 4.72 4.99 5.20 5.70
2001-08-06 3.62 3.52 3.47 3.56 3.88 4.17 4.71 4.99 5.19 5.70
... ... ... ... ... ... ... ... ... ... ...
2020-03-12 0.41 0.33 0.37 0.39 0.50 0.58 0.66 0.82 0.88 1.27
2020-03-13 0.33 0.28 0.38 0.38 0.49 0.58 0.70 0.89 0.94 1.31
2020-03-16 0.25 0.24 0.29 0.29 0.36 0.43 0.49 0.67 0.73 1.10
2020-03-17 0.12 0.19 0.24 0.30 0.47 0.54 0.66 0.91 1.02 1.45
2020-03-18 0.04 0.02 0.08 0.21 0.54 0.66 0.79 1.08 1.18 1.60
30 Yr
Date
2001-07-31 5.51
2001-08-01 5.53
2001-08-02 5.57
2001-08-03 5.59
2001-08-06 5.59
... ...
2020-03-12 1.49
2020-03-13 1.56
2020-03-16 1.34
2020-03-17 1.63
2020-03-18 1.77
[3662 rows x 11 columns]
Now that we have some simple data processing done, let's check out the yield curve at a certain date.
# Inspect a given date's yield curve
print(yield_curve.loc["2018-12-31"])
1 Mo 2.44
3 Mo 2.45
6 Mo 2.56
1 Yr 2.63
2 Yr 2.48
3 Yr 2.46
5 Yr 2.51
7 Yr 2.59
10 Yr 2.69
20 Yr 2.87
30 Yr 3.02
Name: 2018-12-31 00:00:00, dtype: float64
For comparing dates, we could start by looking at what the end of 2019 looks like versus the end of 2018. We go through all the different data processing steps below.
# Index into the two dates and copy
yield_table = yield_curve.loc[["2019-12-31", "2018-12-31"]].copy()
print(yield_table)
1 Mo 3 Mo 6 Mo 1 Yr 2 Yr 3 Yr 5 Yr 7 Yr 10 Yr 20 Yr \
Date
2019-12-31 1.48 1.55 1.60 1.59 1.58 1.62 1.69 1.83 1.92 2.25
2018-12-31 2.44 2.45 2.56 2.63 2.48 2.46 2.51 2.59 2.69 2.87
30 Yr
Date
2019-12-31 2.39
2018-12-31 3.02
# Transpose the table
yield_table = yield_table.T
print(yield_table)
Date 2019-12-31 2018-12-31
1 Mo 1.48 2.44
3 Mo 1.55 2.45
6 Mo 1.60 2.56
1 Yr 1.59 2.63
2 Yr 1.58 2.48
3 Yr 1.62 2.46
5 Yr 1.69 2.51
7 Yr 1.83 2.59
10 Yr 1.92 2.69
20 Yr 2.25 2.87
30 Yr 2.39 3.02
# Rename the columns
yield_table.columns = ["Current", "1 Year Ago"]
print(yield_table)
Current 1 Year Ago
1 Mo 1.48 2.44
3 Mo 1.55 2.45
6 Mo 1.60 2.56
1 Yr 1.59 2.63
2 Yr 1.58 2.48
3 Yr 1.62 2.46
5 Yr 1.69 2.51
7 Yr 1.83 2.59
10 Yr 1.92 2.69
20 Yr 2.25 2.87
30 Yr 2.39 3.02
# Add in a column for the change in the rate
yield_table["Change"] = yield_table["Current"] - yield_table["1 Year Ago"]
print(yield_table)
Current 1 Year Ago Change
1 Mo 1.48 2.44 -0.96
3 Mo 1.55 2.45 -0.90
6 Mo 1.60 2.56 -0.96
1 Yr 1.59 2.63 -1.04
2 Yr 1.58 2.48 -0.90
3 Yr 1.62 2.46 -0.84
5 Yr 1.69 2.51 -0.82
7 Yr 1.83 2.59 -0.76
10 Yr 1.92 2.69 -0.77
20 Yr 2.25 2.87 -0.62
30 Yr 2.39 3.02 -0.63