[R-SIG-Finance] Handling half hourly data from electricity markets

[Gabor Grothendieck]

On Wed, Jun 22, 2011 at 2:08 AM, Adrian Ladaniwskyj
<Adrian.Ladaniwskyj at hydro.com.au> wrote:
>
>
> Hi all;
>
> I am attempting to conduct analysis on half hourly Australian NEM
> electricity market wholesale price data.
>
> I need a way to parse a half hourly dataset, and generate Yearly,
> Quarterly, and Monthly peak/offpeak average prices, in line with the
> form of quoted pricing for typical OTC and futures market energy swap
> products.
>
> Peak is defined between 7am and 10pm market time, offpeak 10pm-7am.
>
> (For example , to get the average peak price for jan, 2011, I need to be
> able to select ONLY price data from jan 2011, that occurred between 7am
> and 10pm, sum the prices, then divide them by the number of instances
> for the desired result)
>
> I cannot seem to find an adequate coded example of how to achieve this
> as yet.
>
> An example of the data I am working with is set out below:
>
> Iteration             Time $/MWh $/MWh $/MWh
> 2          1 2011-01-01 00:30 22.00 16.37 23.64
> 3          1 2011-01-01 01:00 17.69 16.53 15.49
> 4          1 2011-01-01 01:30 16.49 15.00 15.09
> 5          1 2011-01-01 02:00 12.17 11.73 10.96
> 6          1 2011-01-01 02:30  7.38  7.20  6.73
> 7          1 2011-01-01 03:00  6.10  6.24  5.36
> 8          1 2011-01-01 03:30  5.80  6.24  4.91
> 9          1 2011-01-01 04:00  4.98  5.40  4.10
> 10         1 2011-01-01 04:30  5.72  6.24  4.72
>
> If anyone can assist me in this, it would be greatly appreciated.
>

Set up a peak column (TRUE for peak, FALSE for offpeak) and then
aggregate by peak and month, quarter or year.  To get the month and
quarter use as.yearmon and as.yearqtr from zoo.

 Lines <- "Iteration             Time $/MWh $/MWh $/MWh
2          1 2011-01-01 03:30 22.00 16.37 23.64
3          1 2011-01-01 04:00 17.69 16.53 15.49
4          1 2011-01-01 11:30 16.49 15.00 15.09
5          1 2012-01-01 12:00 12.17 11.73 10.96
6          1 2012-01-01 12:30  7.38  7.20  6.73
7          1 2012-01-01 13:00  6.10  6.24  5.36
8          1 2012-01-01 13:30  5.80  6.24  4.91
9          1 2012-04-01 14:00  4.98  5.40  4.10
10         1 2012-04-01 14:30  5.72  6.24  4.72"

library(zoo)
# DF <- read.table("myfile", ...whatever...)
DF <- read.table(textConnection(Lines), skip = 1, as.is = TRUE,
	col.names = c("", "", "Date", "Time", "X1", "X2", "X3"))[-(1:2)]

DF <- transform(DF, peak = Time >= "07:00" & Time <= "22:00")
aggregate(cbind(X1, X2, X3) ~ peak + as.yearmon(Date), DF, mean)
aggregate(cbind(X1, X2, X3) ~ peak + as.yearqtr(Date), DF, mean)
aggregate(cbind(X1, X2, X3) ~ peak + years(Date), DF, mean)

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com