Using Python to access the Bank of England's Statistical Interactive Database (IADB)
In this we will retrieve data from the Bank of England's Statistical Interactive Database (IADB) using Python 3. Please take a look at the following sites to get a feel for what kind of data is available:
As an , we will take a look at the fixed mortgage interest rates in the UK since the year 2000. The full description of the series is:
Monthly interest rate of UK monetary financial institutions (excl. Central Bank) sterling 2, 3, 5 and 10 year (75% LTV) fixed rate mortgage to households (in percent) not seasonally adjusted.
We will use Python to (re)construct the following example URL and retrieve the data. If you click on this link, it will download the data:
In the above URL, we used CSV as the data format. It is possible to download data in other formats, like XML, HTML Excel. In this tutorial we will work with Pandas, so we prefer to retrieve the data in CSV format. Please note that the API takes the following parameters:
| Parameter name | Required format | Example |
|---|---|---|
| DD/MON/YYYY (mandatory parameter) | 01/Feb/2006 | |
| DD/MON/YYYY or "now" (mandatory parameter) | 01/Oct/2007 | |
| SeriesCodes | list of full series codes (mandatory parameter) | LPMAUZI |
| "Y" (mandatory parameter) | Y | |
| CSVF | "TT", "TN", "CT" or "CN" (mandatory parameter) | TN |
| VPD | "Y" or "N" (optional parameter) | Y |
NB:
- The "SeriesCodes" parameter supports up to 300 comma separated codes in a single request.
- The "CSVF" format parameter options are "TT" (Tabular with titles), "TN" (Tabular no titles), "CT" (Columnar with- titles) or "CN" (Columnar no titles).
- The "VPD=Y" parameter need only be included if provisional data is desired.
As always, let's start with importing some packages we will use for this exercise.
import pandas as pd # 0.23.0
import requests # 2.19.1
import io
First, we define the URL endpoint:
url_endpoint = 'http://www.bankofengland.co.uk/boeapps/iadb/fromshowcolumns.asp?csv.x=yes'
Secondly, we define the parameters of our request; the header will further avoid potential HTTP 500 errors:
payload = {
'Datefrom' : '01/Jan/2000',
'Dateto' : '01/Oct/2018',
'SeriesCodes': 'IUMBV34,IUMBV37,IUMBV42,IUMBV45',
'CSVF' : 'TN',
'UsingCodes' : 'Y',
'VPD' : 'Y',
'VFD' : 'N'
}
headers = {
'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) '
'AppleWebKit/537.36 (KHTML, like Gecko) '
'Chrome/54.0.2840.90 '
'Safari/537.36'
}
We can use the requests package to construct the URL with the dictionary of parameters:
response = requests.get(url_endpoint, params=payload, headers=headers)
# Check if the response was successful, it should return '200'
print(response.status_code)
The URL that was constructed by the requests package is:
print(response.url)
We can use Pandas' read_csv function to load the data. The function takes a file (or an URL) as an argument. We already downloaded the data (as bytes), so we can pass it with 'io.BytesIO'.
df = pd.read_csv(io.BytesIO(response.content))
# Print the first 5 rows to the screen for inspection
df.head()
This already looks pretty good. But, notice the value of '0' at '29 Feb 2000' for the 10-year mortgage rate ('IUMBV45'), which looks suspicious. Please inspect the series df['IUMBV45'] and you'll see there are missing values between index 116 to 174, represented by '..' (two dots). Strangely, at index 157, there is a value. If you are working in Jupyter Notebooks and cannot print the entire series to your screen, you can use this little hack (converting the series to string)
print(df['IUMBV45'].to_string())
Can you see the series? Let's continue the inspection.
# Check the DataFrame's info
df.info()
Checking the DataFrame's information, it seems there are three things that need to be done:
- Convert the 'DATE' column to '' format (now it is 'object)
- Set 'DATE' to be the index (now it is a 'RangeIndex')
- Convert the 10-year mortgage rate 'IUMBV45' to be numeric
# Step 1: Convert 'DATE' to datetime format
df['DATE'] = pd.to_datetime(df['DATE'])
# Step 2: Set 'DATE' to be the index
df = df.set_index('DATE')
# Check the new index
df.index
Series 'IUMBV45' is of type "object" (and not "float64") because of the missing values are represented by '..' (two dots). We can convert it to float64 and will empty all the cells with dots by setting the parameter errors='coerce' in Pandas 'to_numeric' function.
# Step 3: Convert the 10 year mortgage rate 'IUMBV45' to numeric
df['IUMBV45'] = pd.to_numeric(df['IUMBV45'], errors='coerce')
# Check the DataFrame's info again
df.info()
This looks better, the dates are the index now and there are 167 values left for 'IUMBV45'. Remember the value of '0' at '29 Feb 2000'? Let's see if there are any other dates with a value of '0'.
df.loc[df['IUMBV45'] == 0]
It happens only once. For sake of simplicity of this tutorial, let's not worry too much about it and just get rid of this one occurrence. (We did report this anomaly to the Bank of England, so perhaps it is not there anymore when you run the code).
# Remove the value by setting it to None
df.loc['2000-02-29','IUMBV45'] = None
# Finally, check the statistics of the table
df.describe()
Excellent, the data does seem to make sense. Longer mortgages normally have higher rates than shorter ones. The mean interest rates from 2, 3, 5 and 10 mortgages (left to right respectively) are indeed increasing, which we expected. To conclude, let's visualize this data.
# IPython's magic to plot inline
%matplotlib inline
df.plot()
you enjoyed this tutorial!
About the author:
Joris H., Python & open source enthusiast. Entrepreneur @ PythonSherpa - https://www.pythonsherpa.com