<jupyter_start><jupyter_text>In this notebook:* Merging and sanity check on parquet slices of original DTCC data* Outlier removal* Tenor filtering (90 days max)* Tenor - Return - Volume analysis<jupyter_code># insert PATH to download folder of DTCC pickle slices
PATH = Path('../../data/pickle_data_ndf_subset_currencies').resolve()
df = pd.concat(list(map(lambda x: pd.read_pickle(x),
                               list(PATH.glob('*.pickle')))))
# the dataframe contains a subset of the original DTCC record columns which we selected to be as
# clear as possible while at the same time retaining most information
df.head(1)
n_zero_spots = df['SPOT_RATE'][df['SPOT_RATE']==0].count()
print(f'Initial length: {len(df)} \nVoid spot rates # rows: {n_zero_spots}')
# filter out zero spot rows
df = df[df['SPOT_RATE'] != 0]

n_negative_term = df['TERM_LENGTH'][df['TERM_LENGTH']<0].count()
print(f'Negative term # rows: {n_negative_term}')
# filter negative term rows
df = df[df['TERM_LENGTH']>0]

print(f'Final length: {len(df)}')<jupyter_output>Initial length: 8654042 
Void spot rates # rows: 38077
Negative term # rows: 538
Final length: 8611049<jupyter_text>Outliers<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>`NDF_RATE` has high variance. That has to be caused by outliers in the rounded amounts reported in the original dataset<jupyter_code>spot_means = df['NDF_RATE'].groupby(df['CURRENCY']).mean()
spot_means
ndf_means = df['SPOT_RATE'].groupby(df['CURRENCY']).mean()
ndf_means
spot_std = df['NDF_RATE'].groupby(df['CURRENCY']).std()
spot_std
ndf_std = df['SPOT_RATE'].groupby(df['CURRENCY']).std()
ndf_std
currs = ['CNY', 'IDR', 'INR', 'KRW', 'MYR', 'PHP', 'TWD']
zs_list = []
for i in range(len(currs)):
    zs = (df['NDF_RATE'][df['CURRENCY']==currs[i]] - ndf_means[i])/ndf_std[i]
    # keep zscores larger than 3.5 to drop
    zs = zs[abs(zs) > 3.5]
    zs_list.append(zs)
# filter original df by removing outliers
for zs in zs_list:
    df.drop(zs.index, inplace=True)
df['NDF_RATE'].groupby(df['CURRENCY']).mean() - df['SPOT_RATE'].groupby(df['CURRENCY']).mean()<jupyter_output><empty_output><jupyter_text>After removing outliers the difference in means becomes sensible. `IDR` and `KRW` both make sense given the magnitude of the average rate.<jupyter_code>df['NDF_RATE'].groupby(df['CURRENCY']).std() - df['SPOT_RATE'].groupby(df['CURRENCY']).std()
# rows of outliers removed
8611049 - len(df)<jupyter_output><empty_output><jupyter_text>Visualization sanity check The plots below show ndf and spot rates as extracted from NDF trade records. The spot rate in particularshows jumps since the trade records can have multiple entries in the same trading day, or there could be a periodof no recorded trades which results in a jump.<jupyter_code>plt.figure(figsize=(15,5))
plt.plot(np.array(df['SPOT_RATE'][df['CURRENCY']=='KRW'][:1000]))
plt.title('USDKRW Spot Rate');
plt.figure(figsize=(15,5))
plt.plot(np.array(df['NDF_RATE'][df['CURRENCY']=='KRW'][:1000]))
plt.title('USDKRW NDF Rate');<jupyter_output><empty_output><jupyter_text>`NDF_RATE` is considerably more noisy (as expected)  Returns<jupyter_code>df['LOGRET'] = np.log(df['SPOT_RATE']) - np.log(df['NDF_RATE'])
sorted_df = df.sort_values(by=['LOGRET'], kind='mergesort', ascending=False)
# top trades by logret
sorted_df.head()
sorted_df.groupby(['CURRENCY'])['LOGRET'].describe()<jupyter_output><empty_output><jupyter_text>Term Length Analysis We now turn our attention at the distribution of `TERM LENGTH` values.<jupyter_code>sorted_df['TERM_LENGTH'].describe()<jupyter_output><empty_output><jupyter_text>A good percentage of these values seems unrealistic (`TERM_LENGTH` is computed in days). Some of these values are cause by an incorrect `EFFECTIVE_DATE` cell value.<jupyter_code>sorted_df[sorted_df['TERM_LENGTH'] > 1500].head(10)
len(sorted_df)<jupyter_output><empty_output><jupyter_text>We will only take into account tenors of medium length. Therefore we drop `TERM_LENGHTs` > 90<jupyter_code>sorted_df = sorted_df[sorted_df['TERM_LENGTH'] <= 90]
sorted_df.groupby(['CURRENCY'])['TERM_LENGTH'].describe()<jupyter_output><empty_output><jupyter_text>Tenor - Return - Volume plots<jupyter_code>plt.figure(figsize = (10, 16))
sorted_df.groupby(['TERM_LENGTH'])['LOGRET'].describe()[['mean', 'std', 'min', 'max']].plot()
plt.ylabel('log-return');<jupyter_output><empty_output><jupyter_text>We can perform the same analysis but grouping by different currencies:<jupyter_code>sorted_df['CURRENCY'].unique()
fig, axs = plt.subplots(2,4, figsize=(25, 15), facecolor='w', edgecolor='k')
fig.subplots_adjust(hspace = .5, wspace = .1)

axs = axs.ravel()

for idx, curr in enumerate(sorted_df['CURRENCY'].unique()):
    advantage = sorted_df[sorted_df['CURRENCY']==curr].groupby(['TERM_LENGTH'])['LOGRET'].describe()[['mean']]
    axs[idx].plot(sorted_df[sorted_df['CURRENCY']==curr].groupby(['TERM_LENGTH'])['LOGRET'].describe()[['mean', 'std', 'min', 'max']])
    axs[idx].bar(np.linspace(0, 90, 90), np.array(advantage).flatten(), alpha=0.3, color='black')
    axs[idx].set_ylabel('log-return')
    
    axs[idx].set_xlabel('tenor (days)')
    axs[idx].set_title(f'Currency: {curr}')
    axs[idx].legend(['mean', 'std', 'min', 'max', 'advantage'])<jupyter_output><empty_output><jupyter_text>Volumes and Tenors<jupyter_code>temp = sorted_df.groupby(['EFFECTIVE_DATE'])['USD_AMOUNT'].sum()
plt.figure(figsize=(10,5))
temp.plot(color='grey', alpha=0.6)
plt.title('DTCC total volume grouped by day')
plt.ylabel('volume');
# USD amounts grouped by date for each currency
tempvol = sorted_df.pivot_table(index='EFFECTIVE_DATE', columns='CURRENCY', values='USD_AMOUNT', aggfunc='sum').fillna(0)
tempvol.plot(subplots=True, figsize=(16,10), layout=(4,2), alpha=0.7);
# amounts scaled by tenor
sorted_df.loc[:, 'SCALED_VOLUME'] = (sorted_df['USD_AMOUNT'] * sorted_df['TERM_LENGTH'])/sorted_df['USD_AMOUNT']
temprvol = sorted_df.pivot_table(index='EFFECTIVE_DATE', columns='CURRENCY', values='SCALED_VOLUME', aggfunc='sum').fillna(0)
temprvol.plot(subplots=True, figsize=(16,10), layout=(4,2), alpha=0.7);
temp = sorted_df.pivot_table(index='EFFECTIVE_DATE', columns='TERM_LENGTH', values='USD_AMOUNT', aggfunc='sum').fillna(0)

# grouping by month and tenor ranges
temp = temp.groupby(pd.Grouper(freq='M')).sum()

low = [i for i in range(1, 21)]
med = [i for i in range(21, 41)]
high = [i for i in range(41, 61)]
long = [i for i in range(61, 91)]

low = temp[low].sum(axis=1)
med = temp[med].sum(axis=1)
high = temp[high].sum(axis=1)
long = temp[long].sum(axis=1)

o = [low, med, high, long]
bottom = 0
plt.figure(figsize=(10,5))
for i in range(4):
    plt.plot(o[i], alpha=0.8)
    bottom += o[i]
    #plt.title(f'{str(o[i])}')
plt.legend(['low: 1<x<20', 'med:20<x<40', 'long:40<x<60', 'vlong:60<x<90'])
plt.title('Volumes by tenor')
plt.ylabel('volume (all currencies)');<jupyter_output><empty_output><jupyter_text>Save<jupyter_code>import os
PATH = os.path.realpath('../../data')
if not os.path.exists(f'{PATH}/final'):
    os.mkdir(f'{PATH}/final')
PATH = os.path.realpath('../../data/final')

PATH = os.path.realpath('../../data/final')
sorted_df.to_pickle(f'{PATH}/dtcc')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup