Untitled

library(readxl)
library(ggplot2)
library(sqldf)
library(lubridate)
library(forecast)
library(dplyr)


Days <- read_excel("C:/Users/abdulazizs/Desktop/Test Data/test2.xlsx")

Days$Date<- as.Date(Days$Date)

TwoYear <- read_excel("C:/Users/abdulazizs/Desktop/Test Data/DGS2.xls", 
    range = "A11:B20000")

TenYear <- read_excel("C:/Users/abdulazizs/Desktop/Test Data/DGS10.xls", 
    range = "A11:B20000")

TwoYear <- na.omit(TwoYear)
TenYear <- na.omit(TenYear)

TenYear[TwoYear$DGS10 != 0, ]

## Warning: Unknown or uninitialised column: 'DGS10'.

## Warning: Length of logical index must be 1 or 11300, not 0

## # A tibble: 0 x 2
## # ... with 2 variables: observation_date <dttm>, DGS10 <dbl>

TwoYear[TwoYear$DGS2 != 0, ]

## # A tibble: 10,825 x 2
##    observation_date     DGS2
##    <dttm>              <dbl>
##  1 1976-06-01 00:00:00  7.26
##  2 1976-06-02 00:00:00  7.23
##  3 1976-06-03 00:00:00  7.22
##  4 1976-06-04 00:00:00  7.12
##  5 1976-06-07 00:00:00  7.09
##  6 1976-06-08 00:00:00  7.11
##  7 1976-06-09 00:00:00  7.08
##  8 1976-06-10 00:00:00  7   
##  9 1976-06-11 00:00:00  7.03
## 10 1976-06-14 00:00:00  7.01
## # ... with 10,815 more rows

newtable <- sqldf("SELECT a.observation_date, a.DGS2, b.DGS10
       FROM  TwoYear A LEFT OUTER JOIN TenYear B
       ON a.observation_date = b.observation_date
      ")

newtable$observation_date2 <- as.Date(newtable$observation_date)
newtable$observation_date1<- as.Date(newtable$observation_date)

newtable$year = format(as.Date(newtable$observation_date), "%Y")

newtable$month = format(as.Date(newtable$observation_date), "%m")
newtable$day = format(as.Date(newtable$observation_date), "%d")

AgrData <- sqldf("
SELECT day, year, month, avg(DGS2) AS TwoYear, avg(DGS10) AS TenYear, observation_date2 
        FROM newtable
        GROUP BY  year, month
        ORDER BY YEAR
")

AgrData$inversion <- case_when(
  AgrData$TwoYear > AgrData$TenYear~ 1,
  TRUE ~ 0
)

AgrData$date <- as.Date(with(AgrData, paste(year, month, day,sep="-")), "%Y-%m-%d")

above<-AgrData$TwoYear>AgrData$TenYear
intersect.points <-which(diff(above)!=0)

ggplot(data = Days, aes(x = Date, y = Random))+
      geom_rect(aes(xmin = ymd('1980-02-01'),
                xmax = ymd('1980-07-01'),
                ymin= -Inf,
                ymax = Inf), fill = 'gray89', alpha = .05)+
    geom_rect(aes(xmin = ymd('1981-07-01'),
                xmax = ymd('1982-11-01'),
                ymin= -Inf,
                ymax = Inf), fill = 'grey', alpha = .05)+
    geom_rect(aes(xmin = ymd('1990-07-01'),
                xmax = ymd('1991-03-01'),
                ymin= -Inf,
                ymax = Inf), fill = 'grey', alpha = .05)+
    geom_rect(aes(xmin = ymd('2001-03-01'),
                xmax = ymd('2001-11-01'),
                ymin= -Inf,
                ymax = Inf), fill = 'grey', alpha = .05)+
   geom_rect(aes(xmin = ymd('2008-12-01'),
                xmax = ymd('2009-06-01'),
                ymin= -Inf,
                ymax = Inf), fill = 'grey', alpha = .05)+

  geom_line(data = AgrData, aes(x = observation_date2,  y = TwoYear, color = "TwoYear"))+
  geom_line(data = AgrData, aes(x = observation_date2, y = TenYear, color = "TenYear"))+
  labs(
    x = "Date",
    y = "Average Interest Rate Per Month",
    title = " Two & Ten Year Maturity Interest Rates",
    subtitle = "Source:Fred"
  )+
    coord_cartesian(ylim=c(0,15.5))