The Euro on Forex¶

# import libraries and set display options
import pandas as pd
import pprint

pd.options.display.float_format = '{:20,.4f}'.format
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', 1000)
pd.set_option('display.colheader_justify', 'center')
pd.set_option('display.precision', 3)

# open file
x_rates = pd.read_csv("euro-daily-hist_1999_2020.csv")

# head, tail, and basic info
display(x_rates.head())
display(x_rates.tail())
display(x_rates.info())

# strip brackets and trailing space from country names
# replace remaining space with underscore
# lower case all column names
x_rates.columns = x_rates.columns.str.replace("[","").str.replace("]","")
x_rates.columns = x_rates.columns.str.rstrip()
x_rates.columns = x_rates.columns.str.replace(" ","_")
x_rates.columns = x_rates.columns.str.lower()

# rename columns
x_rates.rename(columns={"period\\unit:":"date",
                        "chinese_yuan_renminbi":"chinese_yuan",
                        "uk_pound_sterling":"uk_pound"}, inplace=True)

# convert datetime
x_rates["date"] = pd.to_datetime(x_rates["date"])

# resort and reindex
x_rates.sort_values("date", inplace=True)
x_rates.reset_index(drop=True, inplace=True)

# convert hyphens in currency columns to NaN
import numpy as np
x_rates = x_rates.replace("-", np.nan)

# convert exchange rate values to float
x_rates.iloc[:,1:] = x_rates.iloc[:,1:].astype(float)

# set style for cells with centered output
from IPython.core.display import HTML
HTML("""
<style>
.output_png {
    display: table-cell;
    text-align: center;
    vertical-align: middle;
}
</style>
""")

# x_rates head
display(x_rates.head())

# create a us dollar to euro dataframe and add 30 day rolling mean column
us_dollar = x_rates[["date","us_dollar"]].copy()
us_dollar["rolling_mean_30"] = us_dollar["us_dollar"].rolling(30).mean()

# line graph for dollar to euro exchange rate
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
%matplotlib inline
years = mdates.YearLocator() # every year
years_fmt = mdates.DateFormatter('%Y')
# ticks = [2000,2005,2010,2015,2020]
fig, ax = plt.subplots(figsize=(10,3.75))
ax.plot(us_dollar["date"], us_dollar["rolling_mean_30"])
ax.xaxis.set_major_locator(years) # format the ticks
ax.xaxis.set_major_formatter(years_fmt)
fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
ax.xaxis.set_major_locator(fmt_three_years)
# num_ticks = 5 
# plt.xticks(range(0, len(us_dollar["date"].sort_values().unique()), num_ticks),
#            us_dollar["date"].sort_values().unique()[::num_ticks], rotation = 0)
ax.set(xlabel="Year",
       ylabel="US dollar",
       title="US Dollar to Euro Exhcange Rates")
plt.show()

# create nan group and get dates
nan_dollar_group = us_dollar[us_dollar["us_dollar"].isnull()]
nan_days = nan_dollar_group["date"]

# line graph showing dollar to euro showing NaN values with and without highlight
fig, (ax1, ax2) = plt.subplots(nrows=2, ncols=1, figsize=(10,7.5))
for ax in ax1,ax2:
    ax.xaxis.set_major_locator(years) # format the x ticks
    ax.xaxis.set_major_formatter(years_fmt)
    fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
    ax.xaxis.set_major_locator(fmt_three_years)
    ax.set(yticks=[],
           xlabel="Year")
ax1.plot(us_dollar["date"], us_dollar["rolling_mean_30"])
ax1.set(title="dataset w/ NaN values")
ax2.plot(us_dollar["date"], us_dollar["rolling_mean_30"])
ax2.set(title="dataset w/ corresponding dates in light blue")
for xc in nan_days:
    plt.axvline(x=xc, color="lightcyan")
fig.suptitle("US dollar to Euro exchange rates", fontsize=20)
fig.tight_layout(pad=3.0)
plt.show()

# create a dollar to euro dataframe with NaN dates removed
US_dollar_nonull = x_rates[x_rates["us_dollar"].notna()].copy()
US_dollar_nonull["rolling_mean_30"] = US_dollar_nonull["us_dollar"].rolling(30).mean()

# line graph showing 30 day rolling mean with and without NaN rows
fig, (ax1, ax2) = plt.subplots(nrows=2, ncols=1, figsize=(10,7.5))
for ax in ax1,ax2:
    ax.xaxis.set_major_locator(years) # format the x ticks
    ax.xaxis.set_major_formatter(years_fmt)
    fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
    ax.xaxis.set_major_locator(fmt_three_years)
    ax.set(yticks=[],
           xlabel="Year")
ax1.plot(us_dollar["date"], us_dollar["rolling_mean_30"])
ax1.set(title="NaN rows included")
ax2.plot(US_dollar_nonull["date"], US_dollar_nonull["rolling_mean_30"])
ax2.set(title="NaN rows removed")
fig.suptitle("US dollar to Euro exchange rates", fontsize=20)
fig.tight_layout(pad=3.0)
plt.show()

# is there a way to apply either function to all the columns in x_rates?
# one drawback could be having 42 data frames
    
# calling xchange function on four highly traded currencies
us_dollar = xchange("us_dollar")
japanese_yen = xchange("japanese_yen")
uk_pound = xchange("uk_pound")
australian_dollar = xchange("australian_dollar")

# calling xchange function on four least traded currencies
israeli_shekel = xchange("israeli_shekel")
philippine_peso = xchange("philippine_peso")
malaysian_ringgit = xchange("malaysian_ringgit")
romanian_leu = xchange("romanian_leu")

# it would be cool to convert or use xchange_list for this but I can't get the string off
c_list = [us_dollar, japanese_yen, uk_pound, australian_dollar,
          israeli_shekel, philippine_peso, malaysian_ringgit, romanian_leu]

# line graph showing Euro to each high frequency trading pairs
fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(nrows=2, ncols=2, figsize=(12,8))
fig.tight_layout(pad=3)
for ax in ax1, ax2, ax3, ax4:
    ax.xaxis.set_major_locator(years) # format the x ticks
    ax.xaxis.set_major_formatter(years_fmt)
    fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
    ax.xaxis.set_major_locator(fmt_three_years)
    ax.set(yticks=[],
           xlabel="Year")
for ax, currency in zip((ax1, ax2, ax3, ax4), c_list[:5]):
    ax.plot(currency["date"], currency["rolling_mean_30"])
#   ax.set_ylabel(str(currency)) # why not, i can get it to change to a string "currency", but not a variable
ax1.set(ylabel="US dollar")
ax2.set(ylabel="Japanese yen")
ax3.set(ylabel="UK pound")
ax4.set(ylabel="Australian dollar")
fig.suptitle("Euro to High Frequency Pairs", fontsize=20)
plt.show()

# line graph showing Euro to each low frequency trading pairs
fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(nrows=2, ncols=2, figsize=(12,8))
fig.tight_layout(pad=3)
for ax in ax1, ax2, ax3, ax4:
    ax.xaxis.set_major_locator(years) # format the x ticks
    ax.xaxis.set_major_formatter(years_fmt)
    fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
    ax.xaxis.set_major_locator(fmt_three_years)
    ax.set(yticks=[],
           xlabel="Year")
for ax, currency in zip((ax1, ax2, ax3, ax4), c_list[4:]):
    ax.plot(currency["date"], currency["rolling_mean_30"])
#   ax.set_ylabel(str(currency)) # why not, i can get it to change to a string "currency", but not a variable
ax1.set(ylabel="Israeli shekel")
ax2.set(ylabel="Philippine peso")
ax3.set(ylabel="Malaysian ringgit")
ax4.set(ylabel="Romanian leu")
fig.suptitle("Euro to Low Frequency Pairs", fontsize=20)
plt.show()

x_values1 = us_dollar["date"]
y_values1 = us_dollar["rolling_mean_30"]

x_values2 = japanese_yen["date"]
y_values2 = japanese_yen["rolling_mean_30"]

x_values3 = uk_pound["date"]
y_values3 = uk_pound["rolling_mean_30"]

# x_values4 = uk_pound["date"]
# y_values4 = uk_pound["rolling_mean_30"]

fig = plt.figure()
ax = fig.add_subplot(111, label = "1")
ax2 = fig.add_subplot(111, label = "2", frame_on = False)
ax3 = fig.add_subplot(111, label = "3", frame_on = False)
for ax in ax, ax2, ax3:
    ax.xaxis.set_major_locator(years) # format the x ticks
    ax.xaxis.set_major_formatter(years_fmt)
    fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
    ax.xaxis.set_major_locator(fmt_three_years)
    ax.set(yticks=[],
           xlabel="Year")
#     ax.set_xlim(right=2009)

ax.plot(x_values1, y_values1, color = "blue")

ax.set_yticks([])
# ax.set_xlabel("x label 1", color = "C0")
# ax.set_ylabel("y label 1", color = "C0")
# ax.tick_params(axis = 'x', colors = "C0")
# ax.tick_params(axis = 'y', colors = "C0")

ax2.plot(x_values2, y_values2, color = "green")

ax2.set_yticks([])
# ax2.xaxis.tick_top()
# ax2.yaxis.tick_right()
# ax2.set_xlabel('x label 2', color = "C1")
# ax2.set_ylabel('y label 2', color = "C1")
# ax2.xaxis.set_label_position('top')
# ax2.yaxis.set_label_position('right')
# ax2.tick_params(axis = 'x', colors = "C1")
# ax2.tick_params(axis = 'y', colors = "C1")

ax3.plot(x_values3, y_values3, color = "yellow")
ax3.set_yticks([])

plt.show()

# [us_dollar, japanese_yen, uk_pound, australian_dollar,
#  israeli_shekel, philippine_peso, malaysian_ringgit, romanian_leu]

for country in c_list:
    plt.plot(country["date"],country["rolling_mean_30"])

# plt.legend()
plt.show()

first_decade = x_rates[x_rates["date"].dt.year < 2009]
display(first_decade.head())
display(first_decade.tail())

# line graph showing Euro to yen and aus dollar
fig, ax = plt.subplots(figsize=(9,5.5))
ax.plot(japanese_yen["date"], japanese_yen["rolling_mean_30"], color="green")
ax.set_ylabel('Japanese yen', color="green")
ax.set(yticks=[])
ax2 = ax.twinx()
ax2.plot(australian_dollar["date"], australian_dollar["rolling_mean_30"], color="blue")
ax2.set_ylabel('Australian dollar', color="blue")
ax2.set(yticks=[])
ax.set(xlabel='Year')
ax.xaxis.set_major_locator(years) # format the x ticks
ax.xaxis.set_major_formatter(years_fmt)
fmt_three_years = mdates.MonthLocator(interval = 36) # Major ticks every 3 years
ax.xaxis.set_major_locator(fmt_three_years)
fig.suptitle("Euro to Two Traded Currencies", fontsize=20)
plt.show()

fig, ax = plt.subplots()

ax.plot(new_frame["date"],new_frame["chinese_yuan_rolling_30"])
plt.show()

late_2000s = x_rates[x_rates["date"].dt.year.between(2009,2013)]
late_2000s.head()

# function to get log of daily rate
def logarizer(dataset):
    dataset["log_rate"] = np.log(dataset.iloc[:,1]/dataset.iloc[:,1].shift()) # getting the log of the exchange rate
    return dataset["log_rate"]

# function to get overall volatility
volatility_dict = {}
def volatizer(dataset):
    column_headers = list(dataset.columns.values)
    currency_name = column_headers[1]
    volatility = dataset["log_rate"].std()*253**.5 # volatility measured by standard deviation * 253 trading days per year
    volatility = round(volatility, 4)*100
    volatility_dict[currency_name] = volatility

# calling logarizer on all eight currencies
for currency in c_list:
    logarizer(currency)
    
# calling volatizer on all eight currencies
for currency in c_list:
    volatizer(currency)

# line graph showing log exchange rates of each currency
plt.figure(figsize=(10,14))
for i, currency in zip(range(1,9), c_list):
    plt.subplot(4,2,i)
    currency["log_rate"].plot.hist(bins=50)
#     plt.title(currency) # will get ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
plt.show()

# plt.title("Romanian volatility: " + str(romanian_leu["volatility"]) + "%")

volatility_dict