Wouldn’t that suck if you have saved money in your bank only to find later that you don’t get the whole money back, let alone no interests.

Yep, that is what’s happening with some of the central banks in the world right now.

Is there data about this?

There is an organization called Bank for International Settlements who hosts the historical interest rates data of all the central banks in the world among many other data sets and updates them periodically.

And conveniently enough, there is an R package literally called BIS, which provides a function to download that data easily in R!

Brilliant!!🔥🔥🔥

So I’ve quickly downloaded the data, cleaned up little bit, and visualized it. Here is a chart that shows the historical interest rates of 6 major countries and area over the last 58 years.

Loading...

And Japan and Switzerland are those taking money from the bank accounts with the negative interest rates in the recent years! 

Now, I want to share how you can do the same. 

Here are the 5 steps.

  1. Install BIS Package
  2. Write R Script with BIS Package to Download Data
  3. Clean Up Data to Convert Text to Date
  4. Visualize Data
  5. Categorize Numeric Values / Create Bins
  6. Handle Missing Values in Chart

1. Install BIS Package

First, you need to install the BIS R package inside Exploratory before you can use it.

Click ‘R Packages’ menu on the left hand side on the Project List page.

Under the Install tab, type ‘BIS’ and click ‘Install’ button.

Once it’s installed, now it’s time to use it!

2. Write R Script with BIS Package to Download Data

Select ‘R Script’ from Data Frame menu at the left hand side.

First, you can use ‘get_datesets’ function to list what datasets are available.

library(BIS)

datasets <- get_datasets()

Here is a list of all the data sets.

Loading...

Now, let’s say we want to get ‘Policy rates (monthly)’ data.

You can use ‘get_bis’ function like the below.

library(BIS)

datasets <- get_datasets()

get_bis(datasets$url[datasets$name == "Policy rates (monthly)"],quiet = TRUE)

The get_bis function takes the URL from the data returned by get_datasets function. You can copy and paste the URL value of the data set you are interested in or you can use the name value to filter the URL data like the above.

Once the data looks good in the preview table, click on ‘Import’ button.

You will see the data displayed in Summary view like the below.

3. Clean Up Data

This is monthly interest rates data, which means it is a time series data. But the date column is Character data type, which makes it harder to visualize the time series data.

We can fix this quickly.

Concatenate “-01” Text with str_c function

We want to use ‘ymd’ function, which converts the data from Character type to Date type. I’ll explain more details about this function in the next section.

But here is one problem.

Currently, the data contains only Year and Month part of the information, but it doesn’t have the Day. Well, this is monthly data so they didn’t have to, I guess.

But the function ‘ymd’ we’re going to use to convert from Character to Date needs that! It needs to have Year, Month, and Day, all three of them! 😱

Well, we can create one.

We can add this by adding “-01” with ‘str_c’ function from ‘stringr’ R package, which concatenates multiple text strings.

Select ‘Work with Text’ -> ‘Concatenate Text’ from the column header menu of ‘date’ column.

This will populate str_c function with date column.

Add “-01” to the function like below and run it.

str_c(date, "-01")

You will see the Year-Month-Day data as the above. This means that we can convert this Character type data to Date type with ymd function.

Convert Character to Date with ymd function

Select ‘Change Data Type’ -> ‘Convert to Date / Time’ -> ‘Year, Month, Day’ from the column header menu of date column.

This will populate ymd function with date column.

This ‘ymd’ function is part of an R package called ‘lubridate’ and it is super powerful.

The cool thing about the function is that it doesn’t matter what characters are between Year, Month, and Day values. For example, it can take any of the following data and convert them to Date type appropriately.

2018-04-02
2018/04/02
2018/4/2
Reported on 2018, April 2nd. 

What it matter is the order in which Year, Month, and Day are presented.

It just works.

What if you have your data in a different order? There are other functions like ‘mdy’, ‘dmy’, etc. Take a look at this post for more details on these functions.

Anyway, you can see the result of the Character to Date conversion in Summary view better.

Before:

After:

4. Visualize Data

Now that we have the time series data prepared, let’s visualize it.

Under Viz view, I have selected Line chart, assigned ‘date’ column to X-Axis and set the aggregation level to ‘Month’. Then, I’ve assigned ‘obs_value’ column to Y-Axis. This is the column that has the central banks’ interest rates. Finally, I’ve assigned ‘reference_area’ (Country) column to Color By.

We can see crazy interest rates for Brazil around 1990.

But this is not what we are interested in. What we want to do is to see if the banks in the world are setting the negative interest rate and which countries.

Filter Data

In this post, let’s focus on the following 5 major countries and 1 area.

  • Canada
  • Euro Area
  • Japan
  • Switzerland
  • United Kingdom
  • United States

Let’s use the Chart Level Filter to get this job done like the below.

It’s a bit hard to see the 6 lines on top of each other.

We can separate it into 6 different charts, each of which represents each country or area, by using Repeat By.

Looks Euro, Japan, and Switzerland seem to have the interest rates in negative in the recent years. But are they really? It’s kind of hard to see it.

Categorize Numerical Values

Let’s highlight those negative interest rates.

For this, we can create 3 categories based on the interest rate values as follows.

We can use ‘Create Bins’ feature to categorize the numerical values of the interest rates.

Select ‘Create Bins (Categories)’ from the column header menu of ‘obs_value’.

In the dialog, we can set the Cutting Points to 0, 1, which means it will create 3 categories each of which are separated by 0 and 1.

You can see the preview of the data transformation in a form of the histogram.

We can set Label Text to make the category names more human friendly.

If it looks ok, click Ok button to run it.

Go back to Viz view and assign this new category column to Color By.

Do you notice there is something weird going on especially with the orange lines?

Let’s take a look at the United States at the right hand side bottom.

The problem here is that in most of the times the interest rates are greater than 1, but there are some brief periods around 1960 and after 2000 where the interest rates are actually between 0 and 1.

And the nature of Line chart tries to connect the available data points. So for example, when you have data only at 2000, 2017, and 2018, then you will get a line that connects those 3 data points ignoring the years between 2000 and 2017. But sometimes, you don’t want it to connect between 2000 and 2017. And this is exactly the case this time.

We can address this problem by explicitly telling Line chart what to do with those ‘empty’ data periods.

6. Handle Missing Values in Chart

You can configure how to deal with Missing Values in the chart.

Select Missing Value Handling from the configuration menu for Y-Axis.

And select Fill with NA. This will make those ‘empty’ period explicitly NA, which makes the line NOT be drawn for those times.

Now the orange lines are not trying to connect with one another!

There is one last thing I want you to try.

Here, we are trying to high light the negative interest rates. Having the part of the line when it is negative shown as blue is not really helping to highlight that.

Well, we can change the color!

Change Color Palette

Open Color Setting dialog by clicking the config icon next to Color By and select one of the built-in Color Palettes.

You can create your own Color Palette, but this time we can go with one of the existing ones that have Red color as the first color.

And let’s move Legend position to the top from the Property dialog.

And here it is.

Loading...

We can see the interest rates being negative in Japan and Switzerl and 0 in Euro in the recent years.

And interestingly enough, in the United States and Canada, the interest rates are picking up recently.

Now, you know where you want to keep your money. (This is the rate for the central banks, not the commercial banks, but they tend to be influenced by the central banks.)