Time Series is the historical representation of data points collected at periodic intervals of time. Statistical tools like R use forecasting models to analyse historical time series data to predict future values with reasonable accuracy. In this post I will be using R time series to forecast the exchange rate of Australian dollar using daily closing rate of the dollar collected over a period of two years.

The data for this post is sourced from AdventureWorksDW which is a SQL Server database that’s available for download from CodePlex. Within AdventureWorksDW database, the table FactCurrencyRate contains daily closing rate (EndOfDayRate) of different currencies (CurrencyKey) pegged against the US dollar.

R Time Series

Data from SQL Server is first loaded into an R dataset using functions from RODBC package.

<br>
library(RODBC)<br>
aw &lt;- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=localhost;database=AdventureWorksDW2014;trusted_connection=yes;")<br>
aud &lt;- sqlQuery(aw,'select EndOfDayRate from [dbo].[FactCurrencyRate] where CurrencyKey=6')<br>
View(aud)<br>
odbcClose(aw)<br>

The above code above does these

• Loads the RODBC package using library() function
• Establishes connection to SQL Server database using odbcDriverConnect() function.
• Fetches data from FactCurrenyRate for AUD (CurrencyKey=6) into a dataset using sqlQuery() function
• Displays data using View() function (Screen Capture 2)

<br>
tsAud &lt;- ts(aud,frequency=7)<br>
plot.ts(tsAud)<br>

Removing Seasonal Variations

It is important to know the extent of exchange rate fluctuation that is attributed to specific times (or seasons) of the year. Seasonality in time series can impact the forecasted value and it is usually a good practise to eliminate such variations from time series. To eliminate seasonal variations, the time series has to be deconstructed to its components using decompose () function. The decompose() function splits the time series into $seasonal and $trend components.

<br>
tsAudComp &lt;- decompose(tsAud)<br>
dev.new()<br>
plot.ts(tsAudComp$seasonal)<br>
dev.new()<br>
plot.ts(tsAudComp$trend)<br>

Now the seasonal variation can be removed from the time series

<br>
tsAudSeasonalAdjusted &lt;- tsAud -tsAudComp$seasonal<br>
dev.new()<br>
plot.ts(tsAudSeasonalAdjusted )<br>

The resulting time series will look like Screen Capture 4.

It is worth noting that the adjusted time series does not show much deviation from before. This indicates that the seasonal variation in this instance is minimal.

Forecasting

So far the historical data has been plotted as time series and adjusted for seasonal variations. Now we move on to forecasting. The fluctuations in AUD time series plot is fairly consistent over time and therefore forecasts can be made using simple exponential smoothing. HoltWinters() function in R is used for simple exponential smoothing

<br>
audHW &lt;-HoltWinters(tsAudSeasonalAdjusted ,beta=FALSE,gamma=FALSE)<br>
plot(audHW)<br>

The parameter beta=FALSE sets the filter to exponential smoothing and the parameter gamma=FALSE indicates a non-seasonal model. The resulting plot is shown in Screen Capture 5

With the exponential smoothing done, the dataset is prepared and ready for forecast. The forecast.HoltWinters() function in forecast package does the forecasting.

<br>
audHWforecast &lt;- forecast.HoltWinters(audHW,h=30)<br>
plot.forecast(audHWforecast)<br>

The parameter h=30 indicates forecasting for the next 30 intervals which is next 30 days in this instance. The output of forecast.HoltWinters is shown below (Screen Capture 6)

Interpretation of Forecast Model

The dark grey shaded area in Screen Capture 6 represents the range of AUD exchange rate for next 30 days with 80% prediction. In this case it’s in the range of 0.525 to 0.57 USD. The lighter shade of grey represents the AUD exchange rate with 90% prediction which is in the range of 0.52 to 0.585

Validating the Forecast by Analysing Prediction Errors

Any forecast model is subject to certain level of prediction errors. So it is important to validate the forecast model by studying the distribution of forecast errors. The Holt Winters forecast model creates a dataset called $residuals which is forecast error dataset. This dataset has the sum of squared values between expected and observed value for each data point. A plot of errors should produce a normal distribution or bell curve with mean of zero. If the plot of errors does not produce a bell curve, it indicates the forecast errors are too high and the forecast model is not recommended.

The following lines of code plots a histogram and a normal distribution curve for the forecast errors

<br>
hist(audHWforecast$residuals, col="yellow")<br>
dnormResiduals &lt;- dnorm(audHWforecast$residuals,mean=mean(audHWforecast$residuals), sd=sd(audHWforecast$residuals) )<br>
dev.new()<br>
plot(audHWforecast$residuals,dnormResiduals,col="blue")<br>

The plots in Screen Capture 7 shows the forecast errors are normally distributed with a mean close to 0. This validates that the forecast model is a correct fit and the predicted values are reasonably accurate.

Download Source Code

The code used in this post is available for download from here http://1drv.ms/1D909Yt