Financial Data Exploration

In [4]:
import numpy as np
import pandas as pd
import scipy as sp
from scipy.stats import mode
from sklearn import linear_model
import matplotlib
import matplotlib.pyplot as plt
from sklearn import discriminant_analysis
from sklearn.decomposition import PCA
from sklearn import preprocessing
%matplotlib inline
import datetime
  1. Imported Data from Bank of England Interactive Statistical Database
  2. Cleaned and reformatted data
  3. Visualised daily exchange rates
  4. Visualised daily exchange rate for Brexit window
  5. Visualised monthly exchange rates
  6. Visualised montly exchange rate for Brexit window
  7. Determined monthly exchange rates did not provide sufficiently granularity
  8. Resampled daily data to get weekly data
  9. Visualised weekly exchange rate for Brexit window
  10. Determined weekly data was a good balance between daily and monthly - sufficient scope for collecting news articles
  11. Highlighted key timeframes of interest to explore further after sentiment analysis

Daily Indicators:

  • XUDLERS,Spot exchange rate - Euro into Sterling
  • XUDLUSS,Spot exchange rate - USD into Sterling

Monthly Indicators:

  • IUMAAJNB,Monthly average rate of discount - 3 month Treasury bills - Sterling
  • IUMAJNB,End month level of discount rate - 3 month Treasury bills - Sterling

  • IUMAVNEA,Monthly average Sterling 1 month mean interbank lending rate

  • IUMVNEA,End month Sterling 1 month mean interbank lending rate

  • XUMAERS,Monthly average Spot exchange rate - Euro into Sterling

  • XUMAUSS,Monthly average Spot exchange rate - US into Sterling
  • XUMLERS,End month Spot exchange rate - Euro into Sterling
  • XUMLUSS,End month Spot exchange rate - US into Sterling
In [7]:
daily_data = pd.read_csv('../exchangeratedata/daily_rates.csv', skiprows=3, header=0)
monthly_data = pd.read_csv('../exchangeratedata/monthly_rates.csv', skiprows=11, header=0)
In [8]:
daily_data.head()
Out[8]:
DATE XUDLERS XUDLUSS
0 04 Jan 2000 1.5874 1.6352
1 05 Jan 2000 1.5862 1.6390
2 06 Jan 2000 1.5921 1.6478
3 07 Jan 2000 1.5909 1.6372
4 10 Jan 2000 1.5956 1.6377
In [9]:
monthly_data.head()
Out[9]:
DATE IUMAAJNB IUMAJNB IUMAVNEA IUMVNEA XUMAERS XUMAUSS XUMLERS XUMLUSS
0 31 Jan 2000 5.7218 5.7646 5.7627 5.9844 1.6201 1.6402 1.6574 1.6209
1 29 Feb 2000 5.8346 5.8426 6.0597 6.0782 1.6266 1.5998 1.6400 1.5790
2 31 Mar 2000 5.8582 5.8425 5.9733 5.9844 1.6377 1.5802 1.6662 1.5952
3 30 Apr 2000 5.9178 5.9577 5.9932 6.1251 1.6730 1.5837 1.7166 1.5638
4 31 May 2000 5.9501 5.9500 6.0686 6.0469 1.6655 1.5075 1.6120 1.4950
In [10]:
daily_data['datetime'] = pd.to_datetime(daily_data['DATE'])
monthly_data['datetime'] = pd.to_datetime(monthly_data['DATE'])

daily_data['dayofweek'] = daily_data['datetime'].apply(lambda row: row.dayofweek)
weekly_data = daily_data[daily_data['dayofweek'] == 4]
In [11]:
def plot_ex(dates, rate1, rate2, label1, label2, ylim, highlight=False):
    plt.figure(figsize=(15,10))
    plt.plot(dates, rate1, color='green', label=str(label1))
    plt.plot(dates, rate2, color='blue', label=str(label2))
    plt.legend(loc='best', fontsize=20)
    plt.xlabel('Year', fontsize=20)
    plt.ylabel('Euro and US Dollar to Pound exchange rate', fontsize=20)
    plt.rc('xtick', labelsize=15) 
    plt.rc('ytick', labelsize=15) 
    plt.grid(True)
    plt.ylim(ylim[0], ylim[1])
    if(highlight):
        plt.axvline(x=datetime.datetime(2016,1,2), color='red', linewidth=2)
        plt.axvline(x=datetime.datetime(2007,1,2), color='orange', linewidth=2)
        plt.axvline(x=datetime.datetime(2009,1,12), color='orange', linewidth=2)
    plt.show()
In [12]:
# daily rates
plot_ex(daily_data['datetime'], daily_data['XUDLERS'], daily_data['XUDLUSS'], 'EUR/GBP', 'USD/GBP',(1.0, 2.2), True)
In [13]:
# comparison of monthly average with end of month
plot_ex(monthly_data['datetime'], monthly_data['XUMAUSS'], monthly_data['XUMLUSS'], 'Monthly average - USD/GBP', 'End month- USD/GBP',(1.0, 2.2), False)
In [14]:
# monthly end month 
plot_ex(monthly_data['datetime'], monthly_data['XUMLERS'], monthly_data['XUMLUSS'], 'EUR/GBP', 'USD/GBP', (1.0, 2.2), True)
In [15]:
# weekly rates
plot_ex(weekly_data['datetime'], weekly_data['XUDLERS'], weekly_data['XUDLUSS'], 'EUR/GBP', 'USD/GBP',(1.0, 2.2), True)

Brexit window

In [16]:
# comparison of weekly average vs end month 
brexit = monthly_data['datetime'].apply(lambda row: row.year > 2015 or (row.month == 12 and row.year == 2015))
brexit_times = monthly_data['datetime'][brexit]
plot_ex(brexit_times, monthly_data['XUMAUSS'][brexit], monthly_data['XUMLUSS'][brexit], 'Monthly average EUR/GBP', 'End month USD/GBP', (1.1, 1.5), False)
In [17]:
# brexit daily rates
brexit = daily_data['datetime'].apply(lambda row: row.year > 2015)
brexit_times = daily_data['datetime'][brexit]
plot_ex(brexit_times, daily_data['XUDLERS'][brexit], daily_data['XUDLUSS'][brexit], 'EUR/GBP', 'USD/GBP', (1.1, 1.5), False)
In [18]:
# brexit monthly rates
brexit = monthly_data['datetime'].apply(lambda row: row.year > 2015 or (row.month == 12 and row.year == 2015))
brexit_times = monthly_data['datetime'][brexit]
plot_ex(brexit_times, monthly_data['XUMLERS'][brexit], monthly_data['XUMLUSS'][brexit], 'EUR/GBP', 'USD/GBP',(1.1, 1.5), False)
In [19]:
# brexit weekly rates
brexit = weekly_data['datetime'].apply(lambda row: row.year > 2015)
brexit_times = weekly_data['datetime'][brexit]
plot_ex(brexit_times, weekly_data['XUDLERS'][brexit], weekly_data['XUDLUSS'][brexit], 'EUR/GBP', 'USD/GBP', (1.1, 1.5), False)

Financial Crisis Window:

In [20]:
# financial daily data
crisis = daily_data['datetime'].apply(lambda row: (row.year > 2007 and row.month > 2) and (row.year < 2010))
crisis_times = daily_data['datetime'][crisis]      
plot_ex(crisis_times, daily_data['XUDLERS'][crisis], daily_data['XUDLUSS'][crisis], 'EUR/GBP', 'USD/GBP', (1.0, 2.1), False)
In [21]:
# financial weekly data
crisis = weekly_data['datetime'].apply(lambda row: (row.year > 2007 and row.month > 2) and (row.year < 2010))
crisis_times = weekly_data['datetime'][crisis]      
plot_ex(crisis_times, weekly_data['XUDLERS'][crisis], weekly_data['XUDLUSS'][crisis], 'EUR/GBP', 'USD/GBP', (1.0, 2.1), False)
In [22]:
# financial monthly data
crisis = monthly_data['datetime'].apply(lambda row: (row.year > 2007 and row.month > 2) and (row.year < 2010))
crisis_times = monthly_data['datetime'][crisis]      
plot_ex(crisis_times, monthly_data['XUMLERS'][crisis], monthly_data['XUMLUSS'][crisis], 'EUR/GBP', 'USD/GBP', (1.0, 2.1), False)