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>
""")

# 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="Date",
       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="Date")
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=18)
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="Date")
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=18)
fig.tight_layout(pad=3.0)
plt.show()
# plt.figure(figsize=(10,3.75))
# plt.subplot(1,2,1)
# plt.plot(us_dollar["date"], us_dollar["rolling_mean_30"])
# plt.yticks([])
# plt.subplot(1,2,2)
# plt.plot(US_dollar_nonull["date"], US_dollar_nonull["rolling_mean_30"])
# plt.yticks([])
# plt.suptitle("US dollar to Euro exchange rates, with NaN dates and corresponding dates removed")
# plt.tight_layout()
# plt.show()

# building the rolling 30 mean into the data frame
mean_rates = x_rates.rolling(30).mean()
mean_rates = mean_rates.add_suffix("_rolling_30")
new_frame = pd.concat([x_rates, mean_rates], axis=1)
new_frame = new_frame[[item for items in zip(x_rates.columns, mean_rates.columns) for item in items]]
display(new_frame.head())
# x_rates = new_frame
display(x_rates.head())
# # how do I get the currency columns before the rolling mean column

# function to build data frame for Euro to other half of trading pair
# this one uses new_frame, which has the 30 day rolling mean incorporated, and then tries to remove the NaN dates
framer_list = []
# US_dollar_nonull = x_rates[x_rates["us_dollar"].notna()].copy()
def framer(col):
    framer_list.append(col)
    col_names = ["date", (str(col)), (str(col)+"_rolling_30")]
    df = pd.DataFrame(columns=col_names)
    df.iloc[:,0] = new_frame["date"]
    df.iloc[:,1] = new_frame[col]
    df.iloc[:,2] = new_frame[(str(col)+"_rolling_30")]
    df = df[df.iloc[:,2].notna()]
    return df

australian_dollar = framer("australian_dollar")
# print(australian_dollar)
plt.plot(australian_dollar["date"], australian_dollar["australian_dollar_rolling_30"])

# function to build data frame for Euro to other half of trading pair
xchange_list = []
def xchange(column):
    xchange_list.append(column)
    dataset = x_rates[["date",column]].copy()
    dataset = dataset[dataset[column].notna()]
    dataset["rolling_mean_30"] = dataset[column].rolling(30).mean()
    return dataset

canadian_dollar = xchange("canadian_dollar")
display(canadian_dollar.head())
print(xchange_list)

# list of currency names
currency_names = list(x_rates.iloc[:,1:])
# display(currency_names)
print(currency_names)
print(currency_names[1])

currency_names[1] = xchange(currency_names[1])
display(currency_names[1])