knitr::opts_chunk$set(echo = TRUE)
library(readxl)
library(ggplot2)
library(lubridate)
library(forecast)
library(sqldf)
Oil <- read_excel("C:/Users/shahi/Desktop/Sanjay/Oil Prices.xls",
range = "A11:B2000")
colnames(Oil)[colnames(Oil)== "DCOILWTICO"] <- "Price"
colnames(Oil)[colnames(Oil)== "observation_date"] <- "Date"
Oil$Date <- as.Date(Oil$Date)
str(Oil)
## Classes 'tbl_df', 'tbl' and 'data.frame': 1989 obs. of 2 variables:
## $ Date : Date, format: "2014-09-16" "2014-09-17" ...
## $ Price: num 94.9 94.3 93.1 92.4 91.5 ...
Oil$Year <- format(as.Date(Oil$Date, "%d/%m/%Y"), "%Y")
Oil$Month <- format(as.Date(Oil$Date, "%d/%m/%Y"), "%m")
Oil$Day <- format(as.Date(Oil$Date, "%d/%m/%Y"), "%d")
Oil[Oil$Price != 0, ]
## # A tibble: 1,939 x 5
## Date Price Year Month Day
## <date> <dbl> <chr> <chr> <chr>
## 1 2014-09-16 94.9 2014 09 16
## 2 2014-09-17 94.3 2014 09 17
## 3 2014-09-18 93.1 2014 09 18
## 4 2014-09-19 92.4 2014 09 19
## 5 2014-09-22 91.5 2014 09 22
## 6 2014-09-23 91.6 2014 09 23
## 7 2014-09-24 93.6 2014 09 24
## 8 2014-09-25 93.6 2014 09 25
## 9 2014-09-26 95.6 2014 09 26
## 10 2014-09-29 94.5 2014 09 29
## # ... with 1,929 more rows
AggregateOil <- sqldf("Select
Year,
Month,
avg(Price)
FROM Oil
GROUP BY Month, Year
ORDER BY Year")
na.omit(AggregateOil)
## Year Month avg(Price)
## 2 2014 09 93.29000
## 3 2014 10 84.39696
## 4 2014 11 72.00000
## 5 2014 12 56.71261
## 6 2015 01 42.92636
## 7 2015 02 48.05500
## 8 2015 03 47.82364
## 9 2015 04 51.97773
## 10 2015 05 56.44286
## 11 2015 06 59.81955
## 12 2015 07 48.68783
## 13 2015 08 42.86762
## 14 2015 09 43.41227
## 15 2015 10 46.22364
## 16 2015 11 40.42238
## 17 2015 12 35.57174
## 18 2016 01 28.66571
## 19 2016 02 28.87905
## 20 2016 03 35.91391
## 21 2016 04 40.75524
## 22 2016 05 44.58909
## 23 2016 06 48.75727
## 24 2016 07 42.52524
## 25 2016 08 44.72435
## 26 2016 09 43.12864
## 27 2016 10 49.77524
## 28 2016 11 43.58545
## 29 2016 12 49.60818
## 30 2017 01 47.73091
## 31 2017 02 50.79500
## 32 2017 03 49.32783
## 33 2017 04 48.50750
## 34 2017 05 46.36870
## 35 2017 06 45.17773
## 36 2017 07 42.18952
## 37 2017 08 48.03696
## 38 2017 09 47.44952
## 39 2017 10 51.57773
## 40 2017 11 54.06409
## 41 2017 12 55.12524
## 42 2018 01 58.15957
## 43 2018 02 59.11800
## 44 2018 03 59.87364
## 45 2018 04 66.25381
## 46 2018 05 66.93565
## 47 2018 06 67.87333
## 48 2018 07 67.75500
## 49 2018 08 68.05565
## 50 2018 09 66.72050
## 51 2018 10 70.74870
## 52 2018 11 51.78500
## 53 2018 12 42.44810
## 54 2019 01 46.90826
## 55 2019 02 52.20700
## 56 2019 03 58.15143
## 57 2019 04 60.95955
## 58 2019 05 58.18217
## 59 2019 06 54.65750
## 60 2019 07 52.37043
## 61 2019 08 54.80591
## 62 2019 09 51.52818
AggregateOil$Year <- NULL
AggregateOil$Month <- NULL
TimeSm <- ts(AggregateOil, frequency = 12, start = c(2014,8 ))
na.omit(TimeSm)
## Jan Feb Mar Apr May Jun Jul
## 2014
## 2015 42.92636 48.05500 47.82364 51.97773 56.44286 59.81955 48.68783
## 2016 28.66571 28.87905 35.91391 40.75524 44.58909 48.75727 42.52524
## 2017 47.73091 50.79500 49.32783 48.50750 46.36870 45.17773 42.18952
## 2018 58.15957 59.11800 59.87364 66.25381 66.93565 67.87333 67.75500
## 2019 46.90826 52.20700 58.15143 60.95955 58.18217 54.65750 52.37043
## Aug Sep Oct Nov Dec
## 2014 93.29000 84.39696 72.00000 56.71261
## 2015 42.86762 43.41227 46.22364 40.42238 35.57174
## 2016 44.72435 43.12864 49.77524 43.58545 49.60818
## 2017 48.03696 47.44952 51.57773 54.06409 55.12524
## 2018 68.05565 66.72050 70.74870 51.78500 42.44810
## 2019 54.80591 51.52818
plot.ts(TimeSm, main = "Timeseries of oil Prices", col = "blue")
plot(TimeSm, main = "Timeseries of oil Prices", col = "blue")
HoltFor1 <- HoltWinters(na.omit(TimeSm))
HoltFor2 <- HoltWinters(na.omit(TimeSm), gamma = FALSE)
HoltFor3 <- HoltWinters(na.omit(TimeSm), beta= FALSE)
HoltFor4 <- HoltWinters(na.omit(TimeSm), gamma = FALSE, beta= FALSE)
plot(HoltFor1, main = "Oil holt Winter's Model", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(HoltFor2, main = "Oil holt Winter's Model (gamma = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(HoltFor3, main = "Oil holt Winter's Model(beta = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(HoltFor4, main = "Oil holt Winter's Model (gamma = FALSE, beta = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(forecast(HoltFor1, 5), main = "Oil Foecast 5 Months", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(forecast(HoltFor2, 5), main = "Oil Foecast 5 Months (gamma = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(forecast(HoltFor3, 5), main = "Oil Foecast 5 Months (beta = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
plot(forecast(HoltFor4, 5), main = "Oil Foecast 5 Months (gamma = FALSE, beta = FALSE)", xlab = "Years", ylab= "Price", sub = "Source: https://fred.stlouisfed.org/series/DCOILWTICO")
