Project 2 – FIN 654: foreign exchange market interactions

Part 1
- Exchange Rates data preparation and exploration
Part 2
- Exchange Rates analysis
- Notes on lead and lag
Conclusion

Part 1

Exchange Rates data preparation and exploration

In this set we will build and explore a data set using filters and if and diff statements. We will then answer some questions using plots and a pivot table report. We will then review a function to house our approach in case we would like to run some of the same analysis on other data sets.

Problem

Marketing and accounts receivables managers at our company continue to note we have a significant exposure to exchange rates. Our functional currency (what we report in financial statements) is in U.S. dollars (USD).

Our customer base is located in the United Kingdom, across the European Union, and in Japan. The exposure hits the gross revenue line of our financials.
Cash flow is further affected by the ebb and flow of accounts receivable components of working capital in producing and selling several products. When exchange rates are volatile, so is earnings, and more importantly, our cash flow.
Our company has also missed earnings forecasts for five straight quarters.

To get a handle on exchange rate exposures we download this data set and review some basic aspects of the exchange rates.

##        DATE  USD.EUR  USD.GBP USD.CNY USD.JPY
## 1 1/29/2012 0.763678 0.638932 6.29509 77.1840
## 2  2/5/2012 0.760684 0.633509 6.29429 76.3930
## 3 2/12/2012 0.757491 0.632759 6.29232 77.2049
## 4 2/19/2012 0.760889 0.634166 6.29644 78.7109
## 5 2/26/2012 0.750301 0.632641 6.29710 80.3373
## 6  3/4/2012 0.750474 0.629771 6.29873 81.1607

##           DATE  USD.EUR  USD.GBP USD.CNY USD.JPY
## 255 12/11/2016 0.938872 0.791554 6.93141 114.397
## 256 12/18/2016 0.950478 0.796572 6.93042 116.796
## 257 12/25/2016 0.958288 0.810481 6.94908 117.469
## 258   1/1/2017 0.954067 0.813594 6.94929 117.100
## 259   1/8/2017 0.951493 0.812388 6.92820 116.968
## 260  1/15/2017 0.943352 0.820854 6.91781 115.287

## 'data.frame':    260 obs. of  5 variables:
##  $ DATE   : Factor w/ 260 levels "1/1/2017","1/10/2016",..: 15 103 89 94 100 126 109 114 120 130 ...
##  $ USD.EUR: num  0.764 0.761 0.757 0.761 0.75 ...
##  $ USD.GBP: num  0.639 0.634 0.633 0.634 0.633 ...
##  $ USD.CNY: num  6.3 6.29 6.29 6.3 6.3 ...
##  $ USD.JPY: num  77.2 76.4 77.2 78.7 80.3 ...

##         DATE        USD.EUR          USD.GBP          USD.CNY     
##  1/1/2017 :  1   Min.   :0.7199   Min.   :0.5835   Min.   :6.092  
##  1/10/2016:  1   1st Qu.:0.7544   1st Qu.:0.6224   1st Qu.:6.149  
##  1/11/2015:  1   Median :0.7926   Median :0.6418   Median :6.279  
##  1/12/2014:  1   Mean   :0.8196   Mean   :0.6561   Mean   :6.310  
##  1/13/2013:  1   3rd Qu.:0.8932   3rd Qu.:0.6656   3rd Qu.:6.369  
##  1/15/2017:  1   Max.   :0.9583   Max.   :0.8209   Max.   :6.949  
##  (Other)  :254                                                    
##     USD.JPY      
##  Min.   : 76.39  
##  1st Qu.: 96.90  
##  Median :102.44  
##  Mean   :103.05  
##  3rd Qu.:117.19  
##  Max.   :124.78  
##

Questions

What is the nature of exchange rates in general and in particular for this data set? We want to reflect the ups and downs of rate movements, known to managers as currency appreciation and depreciation.

We will calculate percentage changes as log returns of currency pairs. Our interest is in the ups and downs. To look at that we use if and else statements to define a new column called direction. We will build a data frame to house this initial analysis.
Using this data frame, interpret appreciation and depreciation in terms of the impact on the receipt of cash flow from customer’s accounts that are denominated in other than our USD functional currency.

##       USD.EUR    USD.GBP     USD.CNY    USD.JPY
## 2 -0.39282058 -0.8523826 -0.01270912 -1.0301113
## 3 -0.42063724 -0.1184583 -0.03130311  1.0571858
## 4  0.44758304  0.2221127  0.06545522  1.9318720
## 5 -1.40130272 -0.2407629  0.01048156  2.0452375
## 6  0.02305476 -0.4546859  0.02588158  1.0197119
## 7  1.19869144  0.7988383  0.22598055  0.6384155

##        USD.EUR    USD.GBP      USD.CNY    USD.JPY
## 255 -0.1234763 -0.4555311  0.602265010  0.9803397
## 256  1.2285861  0.6319419 -0.014283828  2.0753968
## 257  0.8183343  1.7310378  0.268885929  0.5745646
## 258 -0.4414460  0.3833571  0.003021937 -0.3146198
## 259 -0.2701570 -0.1483412 -0.303945688 -0.1127877
## 260 -0.8592840  1.0367203 -0.150079365 -1.4475722

##  num [1:259, 1:4] -0.3928 -0.4206 0.4476 -1.4013 0.0231 ...
##  - attr(*, "dimnames")=List of 2
##   ..$ : chr [1:259] "2" "3" "4" "5" ...
##   ..$ : chr [1:4] "USD.EUR" "USD.GBP" "USD.CNY" "USD.JPY"

##      USD.EUR   USD.GBP    USD.CNY   USD.JPY
## 2 0.39282058 0.8523826 0.01270912 1.0301113
## 3 0.42063724 0.1184583 0.03130311 1.0571858
## 4 0.44758304 0.2221127 0.06545522 1.9318720
## 5 1.40130272 0.2407629 0.01048156 2.0452375
## 6 0.02305476 0.4546859 0.02588158 1.0197119
## 7 1.19869144 0.7988383 0.22598055 0.6384155

##   USD.EUR USD.GBP USD.CNY USD.JPY
## 2      -1      -1      -1      -1
## 3      -1      -1      -1       1
## 4       1       1       1       1
## 5      -1      -1       1       1
## 6       1      -1       1       1
## 7       1       1       1       1

## 'data.frame':    259 obs. of  13 variables:
##  $ dates            : Date, format: "2012-02-05" "2012-02-12" ...
##  $ returns.USD.EUR  : num  -0.3928 -0.4206 0.4476 -1.4013 0.0231 ...
##  $ returns.USD.GBP  : num  -0.852 -0.118 0.222 -0.241 -0.455 ...
##  $ returns.USD.CNY  : num  -0.0127 -0.0313 0.0655 0.0105 0.0259 ...
##  $ returns.USD.JPY  : num  -1.03 1.06 1.93 2.05 1.02 ...
##  $ size.USD.EUR     : num  0.3928 0.4206 0.4476 1.4013 0.0231 ...
##  $ size.USD.GBP     : num  0.852 0.118 0.222 0.241 0.455 ...
##  $ size.USD.CNY     : num  0.0127 0.0313 0.0655 0.0105 0.0259 ...
##  $ size.USD.JPY     : num  1.03 1.06 1.93 2.05 1.02 ...
##  $ direction.USD.EUR: num  -1 -1 1 -1 1 1 1 -1 -1 1 ...
##  $ direction.USD.GBP: num  -1 -1 1 -1 -1 1 1 -1 -1 1 ...
##  $ direction.USD.CNY: num  -1 -1 1 1 1 1 1 -1 -1 -1 ...
##  $ direction.USD.JPY: num  -1 1 1 1 1 1 1 -1 -1 -1 ...

## An 'xts' object on 2012-02-05/2017-01-15 containing:
##   Data: num [1:259, 1:12] -0.3928 -0.4206 0.4476 -1.4013 0.0231 ...
##  - attr(*, "dimnames")=List of 2
##   ..$ : NULL
##   ..$ : chr [1:12] "USD.EUR" "USD.GBP" "USD.CNY" "USD.JPY" ...
##   Indexed by objects of class: [Date] TZ: UTC
##   xts Attributes:  
##  NULL

## 'zooreg' series from 2012-02-05 to 2017-01-15
##   Data: num [1:259, 1:12] -0.3928 -0.4206 0.4476 -1.4013 0.0231 ...
##  - attr(*, "dimnames")=List of 2
##   ..$ : NULL
##   ..$ : chr [1:12] "USD.EUR" "USD.GBP" "USD.CNY" "USD.JPY" ...
##   Index:  Date[1:259], format: "2012-02-05" "2012-02-12" "2012-02-19" "2012-02-26" "2012-03-04" ...
##   Frequency: 0.142857142857143

##                USD.EUR    USD.GBP     USD.CNY    USD.JPY    USD.EUR
## 2012-02-05 -0.39282058 -0.8523826 -0.01270912 -1.0301113 0.39282058
## 2012-02-12 -0.42063724 -0.1184583 -0.03130311  1.0571858 0.42063724
## 2012-02-19  0.44758304  0.2221127  0.06545522  1.9318720 0.44758304
## 2012-02-26 -1.40130272 -0.2407629  0.01048156  2.0452375 1.40130272
## 2012-03-04  0.02305476 -0.4546859  0.02588158  1.0197119 0.02305476
## 2012-03-11  1.19869144  0.7988383  0.22598055  0.6384155 1.19869144
##              USD.GBP    USD.CNY   USD.JPY USD.EUR USD.GBP USD.CNY USD.JPY
## 2012-02-05 0.8523826 0.01270912 1.0301113      -1      -1      -1      -1
## 2012-02-12 0.1184583 0.03130311 1.0571858      -1      -1      -1       1
## 2012-02-19 0.2221127 0.06545522 1.9318720       1       1       1       1
## 2012-02-26 0.2407629 0.01048156 2.0452375      -1      -1       1       1
## 2012-03-04 0.4546859 0.02588158 1.0197119       1      -1       1       1
## 2012-03-11 0.7988383 0.22598055 0.6384155       1       1       1       1

We can plot with the ggplot2 package. In the ggplot statements we use aes, “aesthetics”, to pick x (horizontal) and y (vertical) axes. Use group =1 to ensure that all data is plotted. The added (+) geom_line is the geometrical method that builds the line plot.

Let’s dig deeper and compute mean, standard deviation, etc. Load the data_moments() function. Run the function using the exrates data and write a knitr::kable() report.

	mean	median	std_dev	IQR	skewness	kurtosis
USD.EUR	0.7185	0.5895	0.5499	0.7506	1.3773	6.3808
USD.GBP	0.6884	0.5601	0.6565	0.6588	4.0555	34.3779
USD.CNY	0.1700	0.1118	0.2233	0.1536	4.9157	41.4959
USD.JPY	0.8310	0.6358	0.7371	0.8352	1.6373	6.3185

## [1] 0.154916

Part 2

Exchange Rates analysis

We will use the data from the first part to investigate the interactions of the distribution of exchange rates.

Problem

We want to characterize the distribution of up and down movements visually. Also we would like to repeat the analysis periodically for inclusion in management reports.

Questions

How can we show the shape of our exposure to euros, especially given our tolerance for risk? Suppose corporate policy set tolerance at 95%. Let’s use the exrates.df data frame with ggplot2 and the cumulative relative frequency function stat_ecdf.

USD/EUR

USD/GBP

USD/CNY

USD/JPY

What is the history of correlations in the exchange rate markets? If this is a “history,” then we have to manage the risk that conducting business in one country will definitely affect business in another. Further that bad things will be followed by more bad things more often than good things. We will create a rolling correlation function, corr_rolling, and embed this function into the rollapply() function.

One more experiment, rolling correlations and volatilities using these functions:

How related are correlations and volatilities? Put another way, do we have to be concerned that inter-market transactions (e.g., customers and vendors transacting in more than one currency) can affect transactions in a single market? Let’s model the the exrate data to understand how correlations and volatilities depend upon one another.

## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.05
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -4.21770   0.22271  -18.93819   0.00000
## log(JPY.vol)  -5.54144   1.79239   -3.09165   0.00235
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.1
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -3.96984   0.13209  -30.05500   0.00000
## log(JPY.vol)  -4.05415   1.22338   -3.31389   0.00114
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.15
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -3.65544   0.13523  -27.03041   0.00000
## log(JPY.vol)  -5.04776   1.36883   -3.68765   0.00031
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.2
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -3.41946   0.12025  -28.43728   0.00000
## log(JPY.vol)  -3.90841   1.21236   -3.22381   0.00154
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.25
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -3.24424   0.13564  -23.91780   0.00000
## log(JPY.vol)  -2.70119   1.30126   -2.07582   0.03952
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.3
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -3.00646   0.13015  -23.09982   0.00000
## log(JPY.vol)  -1.16454   1.12589   -1.03433   0.30255
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.35
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.89360   0.09249  -31.28507   0.00000
## log(JPY.vol)  -0.68706   0.98762   -0.69568   0.48765
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.4
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.85405   0.05601  -50.95603   0.00000
## log(JPY.vol)  -0.52545   0.86223   -0.60941   0.54313
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.45
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.78600   0.09578  -29.08817   0.00000
## log(JPY.vol)  -0.88196   1.38968   -0.63465   0.52657
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.5
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.72762   0.10988  -24.82313   0.00000
## log(JPY.vol)  -0.67233   1.67010   -0.40257   0.68781
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.55
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.53524   0.12622  -20.08584   0.00000
## log(JPY.vol)  -1.64576   1.79471   -0.91701   0.36053
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.6
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.43784   0.13953  -17.47199   0.00000
## log(JPY.vol)  -1.85451   1.74646   -1.06187   0.28991
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.65
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.19872   0.14077  -15.61949   0.00000
## log(JPY.vol)  -2.84742   1.67263   -1.70236   0.09064
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.7
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -2.14073   0.12266  -17.45243   0.00000
## log(JPY.vol)  -2.65416   1.24561   -2.13081   0.03464
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.75
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -1.97726   0.12736  -15.52483   0.00000
## log(JPY.vol)  -1.89488   0.91647   -2.06759   0.04030
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.8
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -1.75658   0.14222  -12.35092   0.00000
## log(JPY.vol)  -0.67468   0.91983   -0.73348   0.46434
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.85
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -1.61049   0.07507  -21.45314   0.00000
## log(JPY.vol)   0.06474   0.48783    0.13271   0.89459
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.9
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -1.57462   0.03237  -48.65158   0.00000
## log(JPY.vol)   0.06146   0.15491    0.39676   0.69208
## 
## Call: rq(formula = log(CNY.JPY) ~ log(JPY.vol), tau = taus, data = r_corr_vol)
## 
## tau: [1] 0.95
## 
## Coefficients:
##              Value     Std. Error t value   Pr(>|t|) 
## (Intercept)   -1.45763   0.07136  -20.42651   0.00000
## log(JPY.vol)   0.45428   0.49763    0.91289   0.36268

Here is the quantile regression part of the package.

We set taus as the quantiles of interest.
We run the quantile regression using the quantreg package and a call to the rq function.
We can overlay the quantile regression results onto the standard linear model regression.
We can sensitize our analysis with the range of upper and lower bounds on the parameter estimates of the relationship between correlation and volatility.
The log()-log() transformation allows us to interpret the regression coefficients as elasticities, which vary with the quantile. The larger the elasticity, especially if the absolute value is greater than one, the more risk dependence one market has on the other.
The risk relationships can also be viewed year by year. Here we see very different patterns
\(y = a + bx + e\) is interpreted as systematic movements in \(y = a + bx\), while unsystematic movements are simply \(e\).

Animation

Attempt interpretations to help managers understand the way market interactions affect accounts receivables.

Notes on lead and lag

In the ccf() function we get results that produce positive and negative lags. A positive lag looks back and a negative lag (a lead) looks forward in the history of a time series. Leading and lagging two different serries, then computing the moments and corelations show a definite asymmetry.

Suppose we lead the USD.EUR return by 5 days and lag the USD.GBP by 5 days. We will compare the correlation in this case with the opposite: lead the USD.GBP return by 5 days and lag the USD.EUR by 5 days. We will use the dplyr package to help us.

	mean	median	std_dev	IQR	skewness	kurtosis
ahead_x	0.0872	0.1078	0.905	1.1820	0.1671	3.6297
behind_y	0.0945	-0.0130	0.951	1.1134	1.7055	13.2014

	mean	median	std_dev	IQR	skewness	kurtosis
ahead_y	0.1072	-0.0047	0.9593	1.1139	1.6409	12.7057
behind_x	0.0673	0.1043	0.8953	1.1624	0.1285	3.5876

## [1] 0.0003739413

## [1] -0.004339494

Leading x, lagging y will produce a negative correlation. The opposite produces an even smaller and positive correlation. Differences in means, etc. are not huge between the two cases, but when combined produce the correlational differences.

Conclusion

Skills and Tools

The analysis is carried out using R programming language with the following packages

zoo(): For transforming data into a format that is easy to work with a time series data.
ggplot2(): for data visualazation
magick(): for animation of graphics
xts(): for creating a time series objects
moments(): To calculate summary statistics inclusing kurtosis, skewness and IQR
acf(): for computing autocorrelatioin
pacf(): partial autocorelation
matrixStats(): for statistics computatiuon of a matrix object
Plotly(): for data visualization

Data Insights

The datasets used for this experiment has 260 observation with five variables.The following are the valiables in the dataframe.

USD:EUR
USD:GBP
USD:CNY
USD:JPY

The tolerable rate for USD:EUR is 1.47%, tolerable rate for USD:GBP is 1.49%, the tolerable rate for USD:CNY is 0.49% and the tolerable rate for USD/JPY is 1.99%.

The CNY is less volatile with low tolerable rate compared to JPY with the higher toleravble rate of 1.99% with high volatility.

The CNY and GBP is more skewed to the right compared to JPY and EUR. Negative skew means there are more observations less than the median.

Business Remarks

Policy advise: establish a decision process for approvals when the exchange rates of currency is higher than the tolerable rate. If we are buying, we would experience strong swings in cost and input product utilization at the procurement end of the chain.

The violatility of the currency is very high in European Countries, United Kindom and Japan compayed to China and since most of the company transaction take place in European Countries, United Kindom and Japan, and when USD rise the value of EUR, GBP and JPY fall but less affected by CYN. based on these, the following starategy can be adopted:

There are persistancy in the currency interaction and this also give some certainty that when the currency fall, there are high significance it will go back up. This interaction will enable the manager to make a well informed decision in financial forcasting.
The company can take the advantage of the currency interaction between USD, EUR and JPY to make financial forecast in helpinhg the company retained more values of money in USD.
When the value of GBP is high when dealing with UK customers, the money can be temporary kept in the UK banks and change to USD when the value of the currency drop since the reporting currency for abbual report and financial statement is in USD and there is high persistency between this two currency.
When the value of EUR is high when dealing with Europeans customers, the money can be temporary kept in the EU banks and change to USD when the value of the currency drop since the reporting currency for annual report and financial statement is in USD and there is high persistency between this two currency.
When the value of JPY is high when dealing with Japan customers, the money can be temporary kept in the Japaneese banks and change to USD when the value of the currency drop since the reporting currency for abbual report and financial statement is in USD and there is high persistency between this two currency.
When the value of GBP is droping or falling, the company can buy more of the currency or retained there money in that currency and sell it when the trend go back up and there is high persistency between this two currency.