EXIST 2019 IDM Analysis
R’sızlar
04 Eylül 2020
1 Introduction
We have 7 different datasets belonging to Intraday Market of EXIST. All of them are provided for a defined time period which is 2019.
1.1 Loading the Libraries
Throughout our analysis, we will make use of some well known R packages as tidyverse, ggplot2, lubridate, etc.
library(tidyverse)
library(lubridate)
library(readr)
library(dplyr)
library(ggplot2)
library(knitr)1.2 Loading Datasets
This analysis is made according to 7 different datasets from Energy Exchange of Turkey (EXIST / EPIAS) .
weighted_average_price <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/1idm-weighted-average-price-01012019-31122019.csv?raw=true")
matching_quantity <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/2IDMMatchingQuantity-01012019-31122019.csv?raw=true")
bid_prices <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/3IDMMinMaxBidPrices-01012019-31122019.csv?raw=true")
offer_prices <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/4IntradayMinMaxOfferPrices-01012019-31122019.csv?raw=true")
bid_offer_quantities <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/5BidOfferQuantities-01012019-31122019.csv?raw=true")
matching_price <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/6IntraDayMinMaxMatchingPrice-01012019-31122019.csv?raw=true")
trade_value <- read.csv("https://github.com/pjournal/boun01g-r-sizlar/blob/gh-pages/7IDMTradeValue-01012019-31122019.csv?raw=true")2 Data Preprocessing
To make our data ready to analyze, we will do some rearrangements on our data which will also increase its reproducibility and repeatability. These rearrangements will include:
- Joining process of all 7 data
- Omission of some columns which we will not need to use
- Filtering of data due to necessities
- Date - Time information is converted to POSIXct format
- Adding of new columns such as Hour, Day of Week and Month
alfa_data <- matching_quantity %>%
inner_join(bid_prices, by = "Contract.Name") %>%
inner_join(offer_prices, by = "Contract.Name") %>%
inner_join(bid_offer_quantities, by = "Contract.Name") %>%
inner_join(matching_price, by = "Contract.Name") %>%
inner_join(trade_value, by = "Contract.Name") %>%
select(-c("Contract.Type.y", "Contract.Type.x.x", "Contract.Type.y.y",
"Contract.Type.x.x.x", "Contract.Type.y.y.y", "Contract.Name")) %>%
filter(Contract.Type.x == "Hourly") %>%
select(-Contract.Type.x)
alfa_data %>%
glimpse()## Rows: 8,740
## Columns: 10
## $ Clearing.Quantity..MWh. <chr> "331.20", "527.70", "196.70", "414.70"...
## $ Min.Bid.Price..TL.MWh. <dbl> 5.00, 5.00, 41.00, 1.00, 1.00, 1.00, 1...
## $ Max..Bid.Price..TL.MWh. <chr> "101.38", "97.55", "81.60", "45.00", "...
## $ Min..Offer.Price..TL.MWh. <chr> "60.00", "93.82", "40.00", "4.00", "5....
## $ Max..Offer.Price..TL.MWh. <chr> "100.39", "296.29", "81.60", "45.00", ...
## $ Bid.Quantity..MWh. <chr> "392.50", "910.00", "576.70", "797.40"...
## $ Offer.Quantity..MWh. <chr> "509.40", "664.20", "349.60", "552.90"...
## $ Min..Matching.Price..TL.MWh. <dbl> 60.00, 93.82, 41.05, 4.00, 5.00, 5.00,...
## $ Max..Matching.Price..TL.MWh. <dbl> 100.39, 97.55, 81.60, 45.00, 11.52, 11...
## $ Trade.Value..TL. <chr> "33,182.96", "51,036.02", "15,652.36",...beta_data <- weighted_average_price %>%
bind_cols(alfa_data)
beta_data %>%
glimpse()## Rows: 8,740
## Columns: 13
## $ Date <chr> "01.01.2019", "01.01.2019", "01.01.201...
## $ Hour <chr> "00:00", "01:00", "02:00", "03:00", "0...
## $ WAP..TL.MWh. <dbl> 100.23, 96.85, 80.25, 38.28, 11.67, 11...
## $ Clearing.Quantity..MWh. <chr> "331.20", "527.70", "196.70", "414.70"...
## $ Min.Bid.Price..TL.MWh. <dbl> 5.00, 5.00, 41.00, 1.00, 1.00, 1.00, 1...
## $ Max..Bid.Price..TL.MWh. <chr> "101.38", "97.55", "81.60", "45.00", "...
## $ Min..Offer.Price..TL.MWh. <chr> "60.00", "93.82", "40.00", "4.00", "5....
## $ Max..Offer.Price..TL.MWh. <chr> "100.39", "296.29", "81.60", "45.00", ...
## $ Bid.Quantity..MWh. <chr> "392.50", "910.00", "576.70", "797.40"...
## $ Offer.Quantity..MWh. <chr> "509.40", "664.20", "349.60", "552.90"...
## $ Min..Matching.Price..TL.MWh. <dbl> 60.00, 93.82, 41.05, 4.00, 5.00, 5.00,...
## $ Max..Matching.Price..TL.MWh. <dbl> 100.39, 97.55, 81.60, 45.00, 11.52, 11...
## $ Trade.Value..TL. <chr> "33,182.96", "51,036.02", "15,652.36",...Sys.setlocale("LC_TIME", "C")## [1] "C"idm_data <- beta_data %>%
mutate(Date.Time = as.POSIXlt(factor(Date), format="%d.%m.%y")) %>%
mutate(Date.Time = update(Date.Time, year = 2019)) %>%
mutate(Day.Week = wday(Date.Time,label = TRUE,week_start=1), Month = month(Date.Time),Hour = hour(Date.Time)) %>%
select(-Date) %>%
relocate(Date.Time,
Hour,
Day.Week,
Month,
WAP..TL.MWh.,
Clearing.Quantity..MWh.,
Min.Bid.Price..TL.MWh.,
Max..Offer.Price..TL.MWh.,
Bid.Quantity..MWh.,
Offer.Quantity..MWh.,
Min..Matching.Price..TL.MWh.,
Max..Matching.Price..TL.MWh.,
Trade.Value..TL.)
idm_data %>% glimpse()## Rows: 8,740
## Columns: 15
## $ Date.Time <dttm> 2019-01-01, 2019-01-01, 2019-01-01, 2...
## $ Hour <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,...
## $ Day.Week <ord> Tue, Tue, Tue, Tue, Tue, Tue, Tue, Tue...
## $ Month <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,...
## $ WAP..TL.MWh. <dbl> 100.23, 96.85, 80.25, 38.28, 11.67, 11...
## $ Clearing.Quantity..MWh. <chr> "331.20", "527.70", "196.70", "414.70"...
## $ Min.Bid.Price..TL.MWh. <dbl> 5.00, 5.00, 41.00, 1.00, 1.00, 1.00, 1...
## $ Max..Offer.Price..TL.MWh. <chr> "100.39", "296.29", "81.60", "45.00", ...
## $ Bid.Quantity..MWh. <chr> "392.50", "910.00", "576.70", "797.40"...
## $ Offer.Quantity..MWh. <chr> "509.40", "664.20", "349.60", "552.90"...
## $ Min..Matching.Price..TL.MWh. <dbl> 60.00, 93.82, 41.05, 4.00, 5.00, 5.00,...
## $ Max..Matching.Price..TL.MWh. <dbl> 100.39, 97.55, 81.60, 45.00, 11.52, 11...
## $ Trade.Value..TL. <chr> "33,182.96", "51,036.02", "15,652.36",...
## $ Max..Bid.Price..TL.MWh. <chr> "101.38", "97.55", "81.60", "45.00", "...
## $ Min..Offer.Price..TL.MWh. <chr> "60.00", "93.82", "40.00", "4.00", "5....idm_data$Clearing.Quantity..MWh. <- as.numeric(idm_data$Clearing.Quantity..MWh.)
idm_data$Max..Bid.Price..TL.MWh. <- as.numeric(idm_data$Max..Bid.Price..TL.MWh.)
idm_data$Max..Offer.Price..TL.MWh. <- as.numeric(idm_data$Max..Offer.Price..TL.MWh.)
idm_data$Bid.Quantity..MWh. <- as.numeric(idm_data$Bid.Quantity..MWh.)
idm_data$Offer.Quantity..MWh. <- as.numeric(idm_data$Offer.Quantity..MWh.)
idm_data$Trade.Value..TL. <- as.numeric(gsub(",", "", idm_data$Trade.Value..TL.))
idm_data[is.na(idm_data)] <- 03 Exploratory Data Analysis
3.1 IDM Matching Quantity
In this part we made monthly comparison of IDM matching quantities for 2019.
new_dataa <- idm_data %>% select(Month,Clearing.Quantity..MWh.)
monthly_matching_quantity <- new_dataa %>% group_by(Month) %>% summarise(Clearing.Quantity..MWh.= sum(Clearing.Quantity..MWh.))
ggplot(data=monthly_matching_quantity,aes(x=as.factor(Month),y=Clearing.Quantity..MWh.,fill=as.factor(Month)))+
labs(x="Months", y="Matching Quantities in MWh", color="Months", title = "Monthly Matching Quantities") +
geom_bar(stat='identity',width=0.9,color='blue') +
guides(fill=guide_legend(title="Months"))
As seen above, the Matching Quantity has a peak in March and a minimum point in September.
3.2 IDM Daily Matching Quantities
In this part we made daily comparison of IDM matching quantities for 2019.
new_dataa2 <- idm_data %>% select(Clearing.Quantity..MWh.,Day.Week)
daily_matching_quantity <- new_dataa2 %>% group_by(Day.Week) %>% summarise(Clearing.Quantity..MWh.= sum(Clearing.Quantity..MWh.))
ggplot(data=daily_matching_quantity,aes(x=as.factor(Day.Week),y=Clearing.Quantity..MWh.,fill=as.factor(Day.Week)))+
labs(x="Days", y="Matching Quantities in MWh", color="Days", title = "Daily Matching Quantities") +
geom_bar(stat='identity',width=0.9,color='blue') +
guides(fill=guide_legend(title="Weekdays"))
According to the plot above, there is no significant differences in the Matching Quantities in the weekdays. However, a little drop can be seen in the weekend.
3.3 IDM Monthly Weighted Average Prices
In this part we made monthly comparison of IDM weighted average prices for 2019.
new_dataa3 <- idm_data %>% select(WAP..TL.MWh.,Month)
monthly_weighted_average_prices <- new_dataa3 %>% group_by(Month) %>% summarise(WAP..TL.MWh.= sum(WAP..TL.MWh.))
a <- ggplot(monthly_weighted_average_prices,aes(x=as.factor(Month),y=WAP..TL.MWh.,fill=as.factor(Month)))+
labs(x="Months", y="Prices in TL", color="Months", title = "Monthly Weighted Average Prices") +
geom_bar(stat='identity',width=0.9,color='blue') +
guides(fill=guide_legend(title="Months"))
show(a)
As seen above, in the second half of the year Monthly Weighted Average Prices went up. Besides, after first quarter, there is a significant drop in the prices.
3.4 IDM Monthly Offer Quantities
In this part we made monthly comparison of IDM monthly offer quantities for 2019.
new_dataa4 <- idm_data %>% select(Offer.Quantity..MWh.,Month)
monthly_offer_quantities <- new_dataa4 %>% group_by(Month) %>% summarise(Offer.Quantity..MWh.= sum(Offer.Quantity..MWh.))
ggplot(monthly_offer_quantities,aes(x=as.factor(Month),y=Offer.Quantity..MWh.,fill=as.factor(Month)))+
labs(x="Months", y="Offer Quantities in MWh", color="Months", title = "Monthly Offer Quantities") +
geom_bar(stat='identity',width=0.9,color='blue') +
guides(fill=guide_legend(title="Months"))
It can be seen that there is a reduction in the offer quantities in the beginning of the year till May. After that, we can see significantly higher offer quantities in the last months.
3.5 IDM Monthly Offer and Matching Quantities
In this part we made monthly comparison of IDM offer and matching quantities for 2019.
new_dataa5 <- idm_data %>% select(Month,Clearing.Quantity..MWh.,Offer.Quantity..MWh.)
monthly_offer_and_matching_quantities <- new_dataa5 %>% group_by(Month) %>% summarise(Clearing.Quantity..MWh.= sum(Clearing.Quantity..MWh.),Offer.Quantity..MWh.=sum(Offer.Quantity..MWh.))
monthly_offer_and_matching_quantities_longer <-
monthly_offer_and_matching_quantities %>%
ungroup() %>%
pivot_longer(cols=c(Clearing.Quantity..MWh., Offer.Quantity..MWh.), names_to="Types", values_to="Quantities") %>%
select(Month,Types,Quantities)
ggplot(monthly_offer_and_matching_quantities_longer, aes(x=Month, y=Quantities, fill=Types)) +
scale_fill_manual(values=c("coral3", "darkslateblue")) +
theme_minimal() +
geom_bar(stat="identity", position="dodge") +
ggtitle("Average MCP vs SMP Prices per Day of Week") +
theme(axis.text.x = element_text(angle = 45), legend.position = "top")
3.6 IDM Monthly Maximum Bid and Matching Prices
We compared IDM maximum bid, and matching prices for 2019.
new_dataa9 <- idm_data %>% select(Month,Max..Matching.Price..TL.MWh., Max..Bid.Price..TL.MWh.)
monthly_max_offer_and_matching_bid_prices <- new_dataa9 %>% group_by(Month) %>% summarise(Max..Matching.Price..TL.MWh.=sum(Max..Matching.Price..TL.MWh.), Max..Bid.Price..TL.MWh.=sum( Max..Bid.Price..TL.MWh.))
monthly_max_offer_and_matching_bid_prices_longer <-
monthly_max_offer_and_matching_bid_prices %>%
ungroup() %>%
pivot_longer(cols=c( Max..Matching.Price..TL.MWh.,Max..Bid.Price..TL.MWh.), names_to="Types", values_to="Prices") %>%
select(Month,Types,Prices)
ggplot(monthly_max_offer_and_matching_bid_prices_longer, aes(x=as.factor(Month), y=Prices, fill=Types)) +
scale_fill_manual(values=c("coral3", "darkslateblue")) +
theme_minimal() +
geom_bar(stat="identity", position="dodge") +
ggtitle("IDM Monthly Maximum Matching Prices and Bid Prices") +
theme(axis.text.x = element_text(angle = 0), legend.position = "top")+
labs(x="Months") 
According to our analysis shown above, in the second quarter there is a significant drop in both prices. Moreover, to compare prices to each other, we can see that maximum bid prices are slightly higher than maximum matching prices.
3.7 IDM Monthly Trading Volume
In this part we made monthly comparison of IDM traing volume for 2019.
new_dataa6 <- idm_data %>% select(Month,Trade.Value..TL.)
monthly_trading_volume <- new_dataa6 %>%
group_by(Month) %>%
summarise(Trade.Value..TL.= sum(Trade.Value..TL.))
ggplot(data=monthly_trading_volume,aes(x=as.factor(Month),y=Trade.Value..TL., group = 1)) +
geom_line() +
geom_point() +
labs(
title = "Monthly Trading Volume for 2019",
x = "Months",
y = "Quantities in MWh"
)