In [1]:

from IPython.core.display import display, HTML
display(HTML("<style>.container { width:80% !important; }</style>"))

In [2]:

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

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

display(x_rates.head(2))
display(x_rates.tail(2))

/var/folders/bz/5cj1tzbj5xn319rpqrw6gpmh0000gn/T/ipykernel_9819/1974911599.py:17: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.
  x_rates.columns = x_rates.columns.str.replace("[","").str.replace("]","")

	date	australian_dollar	bulgarian_lev	brazilian_real	canadian_dollar	swiss_franc	chinese_yuan	cypriot_pound	czech_koruna	danish_krone	estonian_kroon	uk_pound	greek_drachma	hong_kong_dollar	croatian_kuna	hungarian_forint	indonesian_rupiah	israeli_shekel	indian_rupee	iceland_krona	japanese_yen	korean_won	lithuanian_litas	latvian_lats	maltese_lira	mexican_peso	malaysian_ringgit	norwegian_krone	new_zealand_dollar	philippine_peso	polish_zloty	romanian_leu	russian_rouble	swedish_krona	singapore_dollar	slovenian_tolar	slovak_koruna	thai_baht	turkish_lira	us_dollar	south_african_rand
0	1999-01-04	1.9100	NaN	NaN	1.8004	1.6168	NaN	0.5823	35.1070	7.4501	15.6466	0.7111	327.1500	9.1332	NaN	251.4800	9,433.6100	NaN	NaN	81.4800	133.7300	1,398.5900	4.7170	0.6668	0.4432	11.6446	4.4798	8.8550	2.2229	45.5100	4.0712	1.3111	25.2875	9.4696	1.9554	189.0450	42.9910	42.6799	0.3723	1.1789	6.9358
1	1999-01-05	1.8944	NaN	NaN	1.7965	1.6123	NaN	0.5823	34.9170	7.4495	15.6466	0.7122	324.7000	9.1341	NaN	250.8000	9,314.5100	NaN	NaN	81.5300	130.9600	1,373.0100	4.7174	0.6657	0.4432	11.5960	4.4805	8.7745	2.2011	44.7450	4.0245	1.3168	26.5876	9.4025	1.9655	188.7750	42.8480	42.5048	0.3728	1.1790	6.7975

	date	australian_dollar	bulgarian_lev	brazilian_real	canadian_dollar	swiss_franc	chinese_yuan	cypriot_pound	czech_koruna	danish_krone	estonian_kroon	uk_pound	greek_drachma	hong_kong_dollar	croatian_kuna	hungarian_forint	indonesian_rupiah	israeli_shekel	indian_rupee	iceland_krona	japanese_yen	korean_won	lithuanian_litas	latvian_lats	maltese_lira	mexican_peso	malaysian_ringgit	norwegian_krone	new_zealand_dollar	philippine_peso	polish_zloty	romanian_leu	russian_rouble	swedish_krona	singapore_dollar	slovenian_tolar	slovak_koruna	thai_baht	turkish_lira	us_dollar	south_african_rand
5697	2021-01-07	1.5836	1.9558	6.5172	1.5601	1.0833	7.9392	NaN	26.1470	7.4392	NaN	0.9019	NaN	9.5176	7.5660	357.7900	17,259.9900	3.9027	90.0455	155.3000	127.1300	1,342.2900	NaN	NaN	NaN	24.2552	4.9570	10.3435	1.6907	59.0430	4.4998	4.8712	91.2000	10.0575	1.6253	NaN	NaN	36.8590	8.9987	1.2276	18.7919
5698	2021-01-08	1.5758	1.9558	6.5748	1.5543	1.0827	7.9184	NaN	26.1630	7.4369	NaN	0.9013	NaN	9.4982	7.5690	359.6200	17,247.3300	3.8981	89.7975	155.5000	127.2600	1,337.9000	NaN	NaN	NaN	24.4718	4.9359	10.2863	1.6883	58.9470	4.5113	4.8708	90.8000	10.0510	1.6228	NaN	NaN	36.8480	9.0146	1.2250	18.7212

In [3]:

# miniaturize x_rates
x_rates = x_rates.iloc[:,:6]

# array with names of currencies
currency_names = x_rates.columns.values
currency_names = currency_names[1:]

# create a list & dictionary of data frames for each currency with log rate of the exchange rate, 30 day rolling mean, and year
col_names = [] # list with names of currencies
df_list = [] # list of 40 dataframes
df_dict = {} # dictionary with 40 currency:currency_dataframe pairs
for currency in x_rates.columns[1:]:
    df_name = currency
    col_names.append(df_name)
    df = x_rates[["date", currency]].copy()
    df = df[df[currency].notna()]
    df["log_rate"] = np.log(df.iloc[:,1]/df.iloc[:,1].shift()) # getting the log of the exchange rate # double check this is the correct way to get log
    df["rolling_mean_30"] = df[currency].rolling(30).mean()
    df["year"] = df["date"].dt.year
    df_dict[currency] = df
    df_list.append(df)

In [4]:

# function to create df with year and annual volatility for every row # i think i could use aggregation here but don't know how
def volatizer(currency):
    annual_df_list = [currency[currency['year'] == y] for y in currency['year'].unique()] # list of annual dfs
    c_name = currency.columns[1]
    row_dict = {}
    for frame in annual_df_list:
        year_name = frame.iat[0,4] # first cell of the "year" column, becomes the "year" key for row_dict
        annual_volatility = frame["log_rate"].std()*253**.5 # volatility measured by standard deviation * 253 trading days per year raised to the 0.5 power
        row_dict[year_name] = annual_volatility
    df = pd.DataFrame.from_dict(row_dict, orient="index", columns=[c_name+"_annual_vol"]) # indexing on year, not sure if this is cool
    return df

# apply volatizer to each currency df
for key in df_dict:
    df_dict[key] = volatizer(df_dict[key])

display(df_dict["bulgarian_lev"])

	bulgarian_lev_annual_vol
2000	0.0098
2001	0.0058
2002	0.0075
2003	0.0047
2004	0.0051
2005	0.0097
2006	0.0004
2007	0.0001
2008	0.0000
2009	0.0000
2010	0.0001
2011	0.0000
2012	0.0000
2013	0.0001
2014	0.0000
2015	0.0001
2016	0.0000
2017	0.0000
2018	0.0000
2019	0.0000
2020	0.0000
2021	0.0000