FIN463 GWU Stocks & Currency Markets Calculations Analysis Excel Workbook do the question 1 & 2 on the file in the below and I will provide you the data fo

FIN463 GWU Stocks & Currency Markets Calculations Analysis Excel Workbook do the question 1 & 2 on the file in the below and I will provide you the data for number 1, and part of the data for number 2. Please read carefully FIN 463
International Finance
Professor Robert B.H. Hauswald
Kogod School of Business, AU
Bloomberg Assignment
The BBG assignment is an individual assignment but you can prepare the analysis together with
your group members, share data and information, discuss findings, etc. However, the final product,
i.e., its presentation, the interpretations, and the discussion of the analysis, need to be a personal
product.
The requisite data is daily and should go back five years, i.e., it should run from March or
October five years ago (T − 5) to April or November of this year (T ) depending on the semester
(spring or fall). All the calculations can be carried out either in Bloomberg or by downloading
the data and using Excel depending on your preferences. As usual, submission will be both in
electronic format and hardcopy.
Please choose any two of the four questions below. Note that questions 2 and 3 are less data and
computationally intensive than questions 1 or 4. You can earn additional credit by submitting
more than just two questions. Good luck!
1. FX Trading. Liquidity is an issue very much on the minds of traders and market participants 50
ever since the 2007/2008 financial crisis broke. Bid-ask spreads are a good indication of market
depth. Being in charge of ultra-high frequency (algorithm) trading at a major hedge fund
you wonder about depth in major FX markets.
To this end you will have to download the daily (banking day) interbank bid and asked FX
rates for the last 10 years from Bloomberg.
(a) Find the mean and median daily percentage bid-ask spreads and fill in the tables below:
Mean µ (%)
Spot
1M Forward
3M Forward
6M Forward
JPY/EUR
USD/EUR
EUR/GBP
CHF/EUR
Median
Spot
1M Forward
3M Forward
6M Forward
JPY/EUR
USD/EUR
EUR/GBP
CHF/EUR
(b) Compute the volatility (standard deviation of the daily currency rates of return), build
a third table, and examine the correlation between volatility and bid-ask spreads.
Volatility σ (%)
Spot
1M Forward
3M Forward
6M Forward
Correlation ρ (µ, σ)
Spot
1M Forward
3M Forward
6M Forward
JPY/EUR
JPY/EUR
USD/EUR
USD/EUR
EUR/GBP
EUR/GBP
CHF/EUR
CHF/EUR
(c) Interpret your findings noting that tighter spreads typically mean higher liquidity. Other
factor influencing spreads are risk (volatility) and volumes, which you can download from
http://www.bis.org/publ/rpfx16.htm. The higher the volatility, the more risk the trader
(market maker) faces and the more reluctant she becomes offering a quote. Work out
what this mechanism implies for FX rate volatility and its correlation with spreads.
What do you observe?
2. Stock and Currency Markets. Examine the comovement of major stock markets and 50
currency pairs. You focus on the 10 most important stock exchanges by market capitalization
and liquidity (turnover): NYSE, NASDAQ, London Stock Exchange, Tokyo Stock Exchange,
Toronto Stock Exchange, Shanghai Stock Exchange, Shenzhen Stock Exchange, Hong Kong
Stock Exchange, Euronext, and Deutsche Bõrse.
(a) Compute the daily (banking day) return correlations of the various stock exchange composite indices and present your results in a diagonal 10×10 matrix.
(b) Compute the daily (banking day) correlations between the major currency pairs (EUR/USD,
USD/JPY, GBP/USD, USD/CHF, USD/CAD, USD/CNY) and the S&P Global 1200,
S&P 500 (NYSE and NASDAQ), CSI 300 (Shanghai and Shenzhen), TOPIX, FTSE 100,
Euronext 100, and the DAX. Present your results in 6 (FX pairs) x 7 (stock indices)
matrix.
(c) Discuss your finding and offer explanations for the observed phenomena.
3. International Portfolio Diversification. Another problem that you encounter in your 50
new position is linked to outdated perceptions of diversification. From your predecessor, a
somewhat old-fashioned portfolio manager who did not believe in either statistics or diversification, you inherited a curious international portfolio consisting of only four, equally weighted
subportfolios of stock indices, i.e., each making up 25% of the overall portfolio:
Market
S&P 500, US
FTSE 100, UK
TOPIX, Japan
Euronext, EU
Return Forecast µi (%)
(a) Complete the above table.
2
Risk Estimate σi (%)
(b) Given the previously computed correlations, calculate the expected risk (standard deviation) and return (mean) of the portfolio.
(c) In light of your analysis of the comovement of stock returns and FX returns across
markets what do you expect for the future of your portfolio? Under what scenario
would you expect correlations to rise so dramatically across markets?
4. Carry Trade. Since the financial crisis broke about 10 years ago your hedge fund has had a 50
number of very important carry trades (uncovered interest arb). Typically, you would fund
yourself in the JPY at LIBOR and then roll 3M or 6M investments in USD or EUR investing
the funds at LIBID. Assuming a start date in March 2007 download the requisite LIBOR and
LIBID data together with the JPY/USD bid-ask FX data which is missing from Bloomberg
given that you should already have the necessary 10Y of JPY/EUR bid-ask spread. In the
analysis, you carefully distinguish between borrowing and lending rates and bid-ask currency
quotes and use the necessary formulae taking into account those spreads, i.e., transaction
costs.
(a) Looking back, you wonder how profitable the two carry trades were. Did the maturity
horizon matter? Provide details on your strategy and instruments to implement the
carry trades, how you roll it (over), and what the gains and losses would have been.
(b) Tabulate a distribution of gains and losses for the 3M and 6M maturities. Do they look
anything like a normal distribution?
(c) Evaluate the risks. You are particularly concerned about the American financial crisis
and the European (Greek) debt crisis. Focus on profitability in JPY/USD carry trades
from 2008 to 2010 and JPY/EUR ones from 2010 to 2012.
3
JPY/EUR Spot
Date
Bid Price Ask Price Spread
2019-11-22 119.86 119.86
2019-11-21 120.08 120.23
2019-11-20 120.18 120.34
2019-11-19 120.22 120.28
2019-11-18 120.32 120.38
2019-11-15 120.14 120.28
2019-11-14 119.45 119.52
2019-11-13 119.76 119.79
2019-11-12 119.95 120.09
2019-11-11 120.26 120.35
2019-11-08 120.13 120.64
2019-11-07 120.72 120.78
2019-11-06 120.56 120.63
2019-11-05 120.85 120.93
2019-11-04 120.82 120.83
2019-11-01
120.8 120.81
2019-10-31 120.48
120.5
2019-10-30 121.36 121.38
2019-10-29 120.99
121
2019-10-28 120.93 120.94
2019-10-25 120.39 120.44
2019-10-24 120.61 120.62
2019-10-23 120.97 120.98
2019-10-22 120.69
120.7
2019-10-21
121.1 121.11
2019-10-18 121.02 121.12
2019-10-17 120.89
120.9
2019-10-16 120.42 120.43
2019-10-15
120.1 120.11
2019-10-14 119.53 119.54
2019-10-11 119.66 119.77
2019-10-10 118.82 118.85
0.00
0.15
0.16
0.06
0.06
0.14
0.07
0.03
0.14
0.09
0.51
0.06
0.07
0.08
0.01
0.01
0.02
0.02
0.01
0.01
0.05
0.01
0.01
0.01
0.01
0.10
0.01
0.01
0.01
0.01
0.11
0.03
Date
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
Euro Forward 1M
Bid Price Ask PriceSpread
119.756 119.762 0.0056
120.104 120.261 0.1566
120.206 120.373 0.1663
120.248 120.313 0.0653
120.346 120.412 0.0658
120.166 120.312 0.1459
119.477 119.553 0.0759
119.791 119.826 0.0354
119.981 120.126 0.1441
120.287 120.385 0.0978
120.156 120.672 0.5158
120.749 120.815 0.0663
120.589 120.666 0.0764
120.879 120.965 0.0857
120.842 120.859 0.0168
120.827 120.843 0.0164
120.506 120.532
0.026
121.38 121.408 0.0278
121.009 121.024 0.0154
120.953 120.969 0.0157
120.415 120.472
0.057
120.635 120.651 0.0159
120.995 121.012 0.0176
120.709 120.724 0.0151
121.12 121.136 0.0157
121.039 121.146 0.1069
120.91 120.925
0.015
120.445 120.462 0.0174
120.125 120.142 0.0171
119.549 119.567 0.0182
119.677 119.795 0.1184
118.84 118.875 0.0348
Date
2019-11-22
2019-11-21
2019-11-20
2019-11-19
2019-11-18
2019-11-15
2019-11-14
2019-11-13
2019-11-12
2019-11-11
2019-11-08
2019-11-07
2019-11-06
2019-11-05
2019-11-04
2019-11-01
2019-10-31
2019-10-30
2019-10-29
2019-10-28
2019-10-25
2019-10-24
2019-10-23
2019-10-22
2019-10-21
2019-10-18
2019-10-17
2019-10-16
2019-10-15
2019-10-14
2019-10-11
2019-10-10
Euro Forward 3M
Bid Price Ask Price
119.725 119.7013
120.105 120.2622
120.207 120.371
120.238 120.3086
120.336 120.4047
120.153 120.3044
119.458 119.5415
119.773 119.8141
119.967 120.1126
120.276 120.3803
120.141 120.6685
120.742 120.816
120.582 120.6699
120.878 120.9701
120.832 120.8598
120.823 120.8503
120.493 120.5298
121.367 121.4128
121.012 121.0377
120.964 120.984
120.427 120.4945
120.647 120.6709
121.005 121.0304
120.713 120.7348
121.12 121.1455
121.041 121.1546
120.905 120.9231
120.449 120.4745
120.134 120.1562
119.551 119.5859
119.67 119.8009
118.842 118.8798
2019-10-09
2019-10-08
2019-10-07
2019-10-04
2019-10-03
2019-10-02
2019-10-01
2019-09-30
2019-09-27
2019-09-26
2019-09-25
2019-09-24
2019-09-23
2019-09-20
2019-09-19
2019-09-18
2019-09-17
2019-09-16
2019-09-13
2019-09-12
2019-09-11
2019-09-10
2019-09-09
2019-09-06
2019-09-05
2019-09-04
2019-09-03
2019-09-02
2019-08-30
2019-08-29
2019-08-28
2019-08-27
2019-08-26
117.92
117.32
117.66
117.38
117.22
117.45
117.79
117.79
118.1
117.78
117.91
117.99
118.22
118.48
119.28
119.61
119.71
118.96
119.68
119.6
118.71
118.75
118.47
117.88
118
117.39
116.25
116.53
116.8
117.78
117.56
117.29
117.81
117.93
117.33
117.67
117.39
117.23
117.47
117.8
117.8
118.11
117.79
117.95
118
118.23
118.58
119.29
119.62
119.72
118.97
119.76
119.61
118.72
118.76
118.48
117.9
118.01
117.4
116.26
116.54
116.85
117.79
117.57
117.3
117.86
0.01
0.01
0.01
0.01
0.01
0.02
0.01
0.01
0.01
0.01
0.04
0.01
0.01
0.10
0.01
0.01
0.01
0.01
0.08
0.01
0.01
0.01
0.01
0.02
0.01
0.01
0.01
0.01
0.05
0.01
0.01
0.01
0.05
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
117.94
117.341
117.683
117.401
117.245
117.476
117.818
117.827
118.126
117.792
117.927
117.996
118.225
118.483
119.259
119.604
119.712
118.966
119.683
119.602
118.722
118.758
118.476
117.886
118.006
117.396
116.262
116.543
116.812
117.79
117.583
117.314
117.834
117.956
117.359
117.698
117.416
117.262
117.502
117.834
117.842
118.144
117.811
117.978
118.015
118.241
118.587
119.275
119.625
119.731
118.982
119.767
119.617
118.735
118.772
118.491
117.911
118.021
117.411
116.277
116.559
116.867
117.805
117.599
117.33
117.893
0.0159
0.0177
0.0149
0.0147
0.0168
0.0262
0.0156
0.0156
0.0184
0.019
0.0501
0.0186
0.0157
0.1045
0.016
0.021
0.0189
0.0157
0.084
0.0149
0.0129
0.0137
0.0144
0.025
0.0151
0.015
0.0149
0.0159
0.0549
0.0153
0.016
0.0158
0.0588
2019-10-09
2019-10-08
2019-10-07
2019-10-04
2019-10-03
2019-10-02
2019-10-01
2019-09-30
2019-09-27
2019-09-26
2019-09-25
2019-09-24
2019-09-23
2019-09-20
2019-09-19
2019-09-18
2019-09-17
2019-09-16
2019-09-13
2019-09-12
2019-09-11
2019-09-10
2019-09-09
2019-09-06
2019-09-05
2019-09-04
2019-09-03
2019-09-02
2019-08-30
2019-08-29
2019-08-28
2019-08-27
2019-08-26
117.933
117.328
117.673
117.391
117.239
117.465
117.817
117.83
118.098
117.845
117.975
118.053
118.277
118.51
119.297
119.652
119.751
119.02
119.736
119.654
118.781
118.81
118.536
117.946
118.066
117.455
116.323
116.611
116.872
117.847
117.62
117.349
117.873
117.9566
117.3576
117.693
117.4135
117.2683
117.5065
117.8381
117.8585
118.1191
117.865
118.026
118.0709
118.2941
118.6233
119.3156
119.6706
119.7688
119.0361
119.8221
119.6707
118.7972
118.8261
118.5518
117.9718
118.0825
117.4709
116.3397
116.6268
116.9287
117.8629
117.6361
117.3657
117.9293
2019-08-23
2019-08-22
2019-08-21
2019-08-20
2019-08-19
2019-08-16
2019-08-15
2019-08-14
2019-08-13
2019-08-12
2019-08-09
2019-08-08
2019-08-07
2019-08-06
2019-08-05
2019-08-02
2019-08-01
2019-07-31
2019-07-30
2019-07-29
2019-07-26
2019-07-25
2019-07-24
2019-07-23
2019-07-22
2019-07-19
2019-07-18
2019-07-17
2019-07-16
2019-07-15
2019-07-12
2019-07-11
2019-07-10
117.43
117.93
118.17
117.93
118.13
117.96
117.87
117.97
119.24
118.07
118.36
118.59
119
119.21
118.69
118.36
118.99
120.47
121.14
121.21
120.92
121.1
120.51
120.68
120.91
120.86
120.99
121.16
121.34
121.48
121.62
122.11
122.03
117.54
117.96
118.19
117.94
118.14
117.97
117.88
117.98
119.25
118.08
118.39
118.6
119.03
119.26
118.71
118.42
119.01
120.48
121.15
121.22
120.97
121.11
120.52
120.69
120.92
120.87
121
121.17
121.35
121.5
121.63
122.12
122.04
0.11
0.03
0.02
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.03
0.01
0.03
0.05
0.02
0.06
0.02
0.01
0.01
0.01
0.05
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.02
0.01
0.01
0.01
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
117.453
117.952
118.188
117.949
118.15
117.975
117.882
117.984
119.258
118.083
118.371
118.603
119.016
119.229
118.708
118.377
119.008
120.495
121.162
121.231
120.945
121.122
120.537
120.707
120.937
120.88
121.008
121.182
121.361
121.496
121.636
122.127
122.045
117.568
117.989
118.214
117.965
118.165
117.991
117.897
117.998
119.272
118.099
118.407
118.617
119.052
119.284
118.734
118.442
119.037
120.515
121.179
121.247
121
121.138
120.553
120.722
120.953
120.896
121.024
121.197
121.375
121.521
121.65
122.142
122.061
0.1149
0.0364
0.0253
0.0151
0.0151
0.0159
0.0147
0.0147
0.0142
0.0155
0.0357
0.0143
0.0354
0.0552
0.0252
0.0652
0.0295
0.0202
0.0161
0.0155
0.0554
0.0155
0.0158
0.0146
0.016
0.0165
0.0163
0.0155
0.0144
0.0251
0.0137
0.0151
0.0159
2019-08-23
2019-08-22
2019-08-21
2019-08-20
2019-08-19
2019-08-16
2019-08-15
2019-08-14
2019-08-13
2019-08-12
2019-08-09
2019-08-08
2019-08-07
2019-08-06
2019-08-05
2019-08-02
2019-08-01
2019-07-31
2019-07-30
2019-07-29
2019-07-26
2019-07-25
2019-07-24
2019-07-23
2019-07-22
2019-07-19
2019-07-18
2019-07-17
2019-07-16
2019-07-15
2019-07-12
2019-07-11
2019-07-10
117.491
117.993
118.228
117.988
118.189
118.015
117.931
118.018
119.295
118.119
118.407
118.643
119.06
119.268
118.75
118.413
119.04
120.533
121.2
121.275
120.989
121.161
120.578
120.741
120.967
120.908
121.017
121.213
121.387
121.522
121.661
122.158
122.075
117.6083
118.0287
118.2539
118.0043
118.2045
118.0387
117.9479
118.0337
119.3122
118.1352
118.4463
118.6596
119.0968
119.3231
118.7743
118.4842
119.0652
120.5494
121.2147
121.2902
121.0429
121.1753
120.5947
120.7559
120.9821
120.9251
121.035
121.2278
121.403
121.5469
121.6777
122.1734
122.0918
2019-07-09
2019-07-08
2019-07-05
2019-07-04
2019-07-03
2019-07-02
2019-07-01
2019-06-28
2019-06-27
2019-06-26
2019-06-25
2019-06-24
2019-06-21
2019-06-20
2019-06-19
2019-06-18
2019-06-17
2019-06-14
2019-06-13
2019-06-12
2019-06-11
2019-06-10
2019-06-07
2019-06-06
2019-06-05
2019-06-04
2019-06-03
2019-05-31
2019-05-30
2019-05-29
2019-05-28
2019-05-27
2019-05-24
122
121.92
121.73
121.67
121.6
121.76
122.37
122.62
122.55
122.54
121.82
122.29
121.98
121.15
121.35
121.39
121.77
121.66
122.22
122.47
122.91
122.67
122.61
122.22
121.7
121.67
121.48
120.94
121.99
121.98
122.06
122.56
122.45
122.01
121.93
121.78
121.68
121.61
121.77
122.38
122.7
122.56
122.55
121.86
122.32
121.99
121.18
121.37
121.41
121.78
121.72
122.23
122.48
122.92
122.68
122.62
122.23
121.71
121.69
121.5
120.97
122
121.99
122.07
122.57
122.46
0.01
0.01
0.05
0.01
0.01
0.01
0.01
0.08
0.01
0.01
0.04
0.03
0.01
0.03
0.02
0.02
0.01
0.06
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.02
0.02
0.03
0.01
0.01
0.01
0.01
0.01
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
122.02
121.945
121.748
121.687
121.621
121.781
122.394
122.639
122.566
122.552
121.831
122.299
121.986
121.15
121.353
121.395
121.776
121.664
122.225
122.479
122.918
122.678
122.617
122.225
121.707
121.677
121.485
120.952
122
122.002
122.081
122.579
122.471
122.036
121.959
121.806
121.707
121.637
121.798
122.409
122.724
122.582
122.567
121.878
122.333
122.001
121.185
121.38
121.419
121.79
121.729
122.241
122.494
122.934
122.693
122.631
122.242
121.724
121.704
121.512
120.986
122.015
122.015
122.096
122.596
122.485
0.0156
0.0144
0.0577
0.0196
0.0158
0.0169
0.0154
0.0852
0.0157
0.0144
0.0476
0.0339
0.015
0.0351
0.0271
0.0244
0.0142
0.0654
0.0153
0.0153
0.0151
0.0151
0.0137
0.0167
0.0165
0.0266
0.0271
0.0344
0.0146
0.0137
0.0143
0.0166
0.0143
2019-07-09
2019-07-08
2019-07-05
2019-07-04
2019-07-03
2019-07-02
2019-07-01
2019-06-28
2019-06-27
2019-06-26
2019-06-25
2019-06-24
2019-06-21
2019-06-20
2019-06-19
2019-06-18
2019-06-17
2019-06-14
2019-06-13
2019-06-12
2019-06-11
2019-06-10
2019-06-07
2019-06-06
2019-06-05
2019-06-04
2019-06-03
2019-05-31
2019-05-30
2019-05-29
2019-05-28
2019-05-27
2019-05-24
122.048
121.974
121.793
121.715
121.656
121.813
122.43
122.674
122.604
122.606
121.879
122.348
122.035
121.197
121.393
121.441
121.821
121.706
122.267
122.514
122.956
122.721
122.657
122.27
121.756
121.718
121.532
120.996
122.046
122.037
122.116
122.611
122.5
122.0635
121.9889
121.8518
121.7321
121.6722
121.8283
122.4441
122.759
122.6194
122.6218
121.9291
122.3835
122.0504
121.2313
121.4197
121.4661
121.8349
121.7759
122.2816
122.5301
122.9755
122.7356
122.6738
122.2859
121.77
121.743
121.5563
121.0304
122.0608
122.0514
122.1301
122.6258
122.5149
2019-05-23
2019-05-22
2019-05-21
2019-05-20
2019-05-17
2019-05-16
2019-05-15
2019-05-14
2019-05-13
2019-05-10
2019-05-09
2019-05-08
2019-05-07
2019-05-06
2019-05-03
2019-05-02
2019-05-01
2019-04-30
2019-04-29
2019-04-26
2019-04-25
2019-04-24
2019-04-23
2019-04-22
2019-04-19
2019-04-18
2019-04-17
2019-04-16
2019-04-15
2019-04-12
2019-04-11
2019-04-10
2019-04-09
122.55
123.05
123.32
122.88
122.79
122.75
122.75
122.81
122.67
123.49
123.07
123.22
123.38
124.06
124.44
124.6
124.75
125
124.95
124.43
124.35
125.13
125.57
126
125.76
125.74
126.56
126.34
126.64
126.54
125.65
125.14
125.16
122.57
123.06
123.33
122.89
122.8
122.76
122.76
122.82
122.68
123.56
123.1
123.23
123.39
124.07
124.46
124.61
124.76
125.04
124.96
124.49
124.36
125.14
125.58
126.01
125.88
125.75
126.57
126.38
126.65
126.6
125.66
125.17
125.18
0.02
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.01
0.07
0.03
0.01
0.01
0.01
0.02
0.01
0.01
0.04
0.01
0.06
0.01
0.01
0.01
0.01
0.12
0.01
0.01
0.04
0.01
0.06
0.01
0.03
0.02
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
########
122.569
123.072
123.346
122.903
122.812
122.773
122.774
122.833
122.693
123.511
123.092
123.242
123.401
124.078
124.462
124.624
124.777
125.027
124.972
124.447
124.369
125.152
125.59
126.016
125.778
125.762
126.582
126.367
126.659
126.558
125.67
125.162
125.18
122.595
123.087
123.36
122.919
122.826
122.787
122.788
122.848
122.708
123.585
123.125
123.257
123.415
124.094
124.487
124.64
124.793
125.071
124.989
124.511
124.384
125.167
125.605
126.034
125.906
125.772
126.596
126.41
126.674
126.622
125.684
125.196
125.205
0.0255
0.0149
0.0145
0.0155
0.0143
0.0147
0.0141
0.0152
0.0152
0.0744
0.0335
0.0149
0.0147
0.0162
0.0247
0.0156
0.016
0.0439
0.0169
0.0633
0.0148
0.015
0.0151
0.0183
0.1282
0.0106
0.0132
0.0434
0.0146
0.0644
0.0144
0.0342
0.0245
2019-05-23
2019-05-22
2019-05-21
2019-05-20
2019-05-17
2019-05-16
2019-05-15
2019-05-14
2019-05-13
2019-05-10
2019-05-09
2019-05-08
2019-05-07
2019-05-06
2019-05-03
2019-05-02
2019-05-01
2019-04-30
2019-04-29
2019…
Purchase answer to see full
attachment