# Electricity Forecasting – the Power of R in a Spreadsheet

So maybe your working within a utility and you need to make a forecast electricity demand so that you can work out the supply stack to meet that demand. Your familiar with at least one of R, Python or Matlab, but you need to take your electricity forecasts into account as part of some other work in Excel.

Electricity forecasting typically uses some sort of time series model to take into the longitudinal nature of the data and also the seasonal, trend and other components. Some great data is available for download from the National Grid.

After reading the data into R and examining the variables, it looks like it might make sense to focus on the TGSD data: “Total Gross System Demand (TGSD) – the total demand on the Transmission System; it includes station load, pump storage pumping and interconnector exports.”

Here is a time series plot of just over two weeks of data:

This time of day and day of week components are clearly evident.

Its a well known fact that temperature plays some part in electricity demand in the UK, albeit not to the same extent as it does for gas demand.

So the variables that we are going to use to try and predict electricity demand are:

– lagged electricity demand

– time of day

– day of week

– temperature

Important to note at this stage that this model is being built for illustrative purposes, its goes without saying that this could be improved upon. For example one could add a new variable to account for public holidays etc, one could also take into account other weather variables, use a seasonal normal temperature, weight the temperature used by the demand in each post code etc.

So the data is loaded into R, some tidying-up is done and its merged with the weather data. Note that the weather data has daily granularity. An ARIMA model is fitted and the excellent ‘forecast’ package is used for an initial forecast.

1 2 3 4 5 6 7 8 9 10 11 |
library(forecast) ############################################################################ # Fit the model and plot a forecast # for speed & time purposes, only the last X% of the data is used ############################################################################ range <- seq(round(nrow(model_df)-nrow(model_df)*0.3,0),nrow(model_df)) demand <- demand[range] model_df <- model_df[range,] arima_fit <- Arima(demand,order=c(1,0,0),xreg=model_df,seasonal=c(0,0,0)) fc <- forecast(arima_fit,xreg=tail(model_df,10)) plot(fc) |

You can also visit the github repo for the full code.

So, having this model is great – but how do we integrate it with Excel for example ?

The following code shows how to deploy the model the the dukeanalytics servers.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
############################################################################ # Create a simple prediction function to help with deploying the model ############################################################################ arima_predict_fn <- function(model_object,new_data){ df <- data.frame(day = factor(new_data$day,levels=c("Friday","Monday","Saturday","Sunday","Thursday","Tuesday","Wednesday")), temp=as.numeric(new_data$temp),hh=factor(new_data$settlement_period,levels=seq(1,48,by=1))); model_df <- cbind(Day=model.matrix(~as.factor(df$day)), HH=model.matrix(~as.factor(df$hh)),Temp=df$temp); model_df <- model_df[,-c(1,8)]; if (is.null(dim(model_df))) {model_df <- as.matrix(t(model_df))}; fc <- forecast(model_object,xreg=model_df)$mean; return(fc); } ############################################################################ # Deploy the model ,the special predict function needs to be used ############################################################################ library(ddeploy) ## please register here http://www.dukeanalytics.com/products/account/registration.php ## to get your own API key details and fill them in here duke_config <- list( user_name="try_it", api_key="db1542b66f16aba5768d8a19c27dec4facf9168a", endpoint="/api/v1.0" ) ## deploy the model duke_deploy(duke_config,arima_fit,predict_udf=arima_predict_fn) ## make forecasts with the deployed model fc_deployed <- duke_predict(duke_config,"arima_fit",data_for_excel_test) plot(fc_deployed) |

Now, since the model has been deployed, its possible to make predictions using new input from multiple sources. To make forecasts from Excel, firstly download and install the DukeDeploy plugin.

Once you have this installed, download the following Excel file which contains an example forecast.

Its also possible to make forecasts from within Google Sheets This requires installation of the Google Sheets DukeDeploy Add-in (available for free from the Google Add-in Store).

Happy Forecasting! Any questions or queries to team@dukeanalytics.com

## Comments