Python For Finance(Beginner): See Behind the FX rate

Many people have browsed FX trading sites to check FX rates. If you are buying a large amount of foreign currency whether for traveling or paying for oversea expense, you would be checking FX rate frequently (probably several times a day - like me) as a small rate difference could lead to a quite large financial impact.

So the question arise — How could we know which bank/agent offers the most competitive rate? And what more could we learn from the provided FX rates?

As an beginner in Python, I found an simple way that could use Python to:
1. Send a request to the FX trading site to get live FX rates and record them in excel
2. Compare the rates offered by multiple agents using trend plot

First, let start with a FX trading site that you like.

As a Chinese, I went with two major Chinese banks - Bank Of China and China Merchant Bank. But don't worry if you choose a different one, the logic would still apply.

Below are what I am seeing for the two Banks i choose.

Bank Of China - FX rate

China Merchant Bank - FX rate

If you were familiar with CSS/HTML (don’t panic if not), then you would notice - they are both in the form of tables!

Simply speaking , if you open the link(click me!) in Chrome, right-click and choose 'inspect'. You would see the below which shows the website code. Hovering your mouse over the FX rate section on the screen, you would have uncovered an html table!

In short, an html table element on any page could be converted to a Pandas dataframe using pd.read_html().

As one of the banks only provides real-time data, i have to make a request to the website every 15 mins and store the table data into excel. I used the below code to do the job but i am sure there are many ways to get the data as well!

import time
import requests
import xlsxwriter
import pandas as pd
from lxml import etree
from datetime import datetime


config = {
    'BankOfChina': {
        'link' : 'https://www.bankofchina.com/sourcedb/whpj/enindex_1619.html',
    },
    'CMBChina' : {
        'link': 'http://english.cmbchina.com/Rate/ForexRates.aspx',
        'xpath' : '//*[@id="rightpart"]/div[3]/div[2]/div[1]/text()',
    }
}


def requestFxTable(bankLink):
    # make a request to the website and asking for the table
    # Bank of China have different format and i have to clean it using loc
    if bankLink == config['BankOfChina']['link'] :
        df = pd.read_html(bankLink)[4].loc[4:31,:6]
        return df
    elif bankLink == config['CMBChina']['link'] :
        df = pd.read_html(bankLink)[0]
        return df
    
def enrich_time_prefix(df , bankLink):
    page = requests.get(bankLink)
    tree = etree.HTML(page.text)
    time_prefix = tree.xpath(config['CMBChina']['xpath'])
    time_prefix = time_prefix[1][:10]
    df['Time'] = time_prefix +' '+ df['Time']

def requestFxRate(bankLink):
    df = requestFxTable(bankLink)
    #change columns and rows to make the table more neat
    df.columns = df.iloc[0]
    df = df[1:]
    #add a request time to keep track 
    df['RequestTime_UK'] = datetime.now()
    if bankLink == config['CMBChina']['link'] :
        #CMB have slightly different time format thus need to add a prefix before time
        enrich_time_prefix(df, bankLink)
        return df
    return df

def dataWriter(bank):
    df_All = pd.read_excel('/Users/Ivy_li/Fx_Rate_{}.xlsx'.format(bank))
    df = requestFxRate(config[bank]['link'])
    df_All = df_All.append(df)
    df_All.to_excel('/Users/Ivy_li/Fx_Rate_{}.xlsx'.format(bank))
    
#Once you have got the data, you could store it into excel
count = 0
while count < 21600 :
    count += 1
    #request every 15 mins , you could change the frequency 
    time.sleep(60.0*15)
    #open excel and write the data
    for bank in config.keys():
        dataWriter( bank )

You could then leave the code to run for a day or two for it to collect the data.
Once the data is collected, you have a series of data to play with.

By plotting line graphs, you could check which banks offer the best rates - in my case, China Merchant Bank offered the more competitive rates at the time.

I also noticed that both banks charges relatively consistent spreads (the difference between selling and buying rate) across the days. But in comparison, Bank of China seems to be charging higher spreads than China Merchant Bank like calculate below.

Sample Code:

import pandas as pd
import plotly.graph_objs as go

xls_BOC = pd.ExcelFile('Fx_Rate_BankOfChina.xlsx')
xls_CMB = pd.ExcelFile('Fx_Rate_CMBChina.xlsx')
df_BOC = pd.read_excel(xls_BOC)
df_CMB = pd.read_excel(xls_CMB)
df_BOC = df_BOC[df_BOC['Currency Name'] == 'GBP']
df_CMB = df_CMB[df_CMB['Currency'] == 'GB Pound Sterling']

fig_FX = go.Figure()
fig_FX.add_trace(go.Line(y = df_BOC['Cash Selling Rate'], x = pd.to_datetime(df_BOC['Pub Time']), name='BOC-Selling Rate(GBP)'))
fig_FX.add_trace(go.Line(y = df_CMB['Selling rate'], x = pd.to_datetime(df_CMB['Time']), name='CMB-Selling Rate(GBP)'))
fig_FX.update_layout(
    title="Trend Graph - FX rate",
    xaxis_title="Time",
    yaxis_title="GBP Rate"
)
fig_FX.show()

fig_FX_Spreads = go.Figure()
fig_FX_Spreads.add_trace(go.Line(y = df_BOC['Cash Selling Rate']-df_BOC['Cash Buying Rate'], x = pd.to_datetime(df_BOC['Pub Time']), name='BOC-Bid Rate(GBP)'))
fig_FX_Spreads.add_trace(go.Line(y = df_CMB['Selling rate']-df_CMB['Cash bid'], x = pd.to_datetime(df_CMB['Time']), name='CMB-Bid Rate(GBP)'))
fig_FX_Spreads.update_layout(
    title="Trend Graph - FX spreads rate",
    xaxis_title="Time",
    yaxis_title="Spreads"
)
fig_FX_Spreads.show()

With the FX data at hand, there are more that we could do , for example :

1. We could verify if China Merchant Bank is offering the more competitive rates across the currencies for both selling and buying
2. there are two dips in the plotted graph, are them being driven by certain financial event?
   etc.

Since I am a beginner as well, I welcome your feedback !