Comparing Georgian Banks

An example of web scraping, data wrangling and data visualization with R

by JS Gourdet

Georgian Banks

1. Objectives

Quarterly, financial reports of Georgian Banks are uploaded on the website of the National Bank of Georgia.
A lot of semi-structured financial data are therefore publicly available. Let's use them to compare Georgian Banks from different points of view.

But how to exploit, transform and represent these data from the National Bank of Georgia website to a user-friendly charts ?
This is the challenge that this document is addressing using the tool R.

Disclaimer: The objective of this document is not to provide an accurate financial analysis of the Georgian Banks, but just the technical steps to show how to leverage insights from available raw data. Only if high quality data of all banks were uploaded, there could be satisfying figures.

2. Input

Data source for the analysis is the page “Financial Indices of Commercial Banks” of the National Bank of Georgia: https://www.nbg.gov.ge/index.php?m=404&lng=eng

3. Outputs: Motion charts (4D)

3.1. Evolution of Banks' Net Income, Total Assets and Personnel over time

The purpose of this chart is to show the evolution over time of the Banks' net incomes (from profit & loss statement) according to their total assets (balance sheet) and sizing them (bubbles) by the size of their personnel (using personnel expenses from P&L).

However, as figures of profit & loss statement are reset every year, it does not make sense to each quarterly reports over years, otherwise the chart would look like a sawtooth-shaped chart. So only one quarter (the 3rd as the 4th is not available) is picked for each year. Then other quarters (incl. for the missing reports) are extrapolated (of course not very accurate and it would be better to use Q4).

User guide:
1. As the variable Size on the chart cannot be set automatically (bug), set it manually to the value “StaffExpenses”
2. Press the button play (arrow) to visualize the motion chart.
3. Each bubble represent a bank, click on bubble to see the bank name and trail can be enabled (right panel)
4. You can change axis if you want but also visualize other chart using other tabs (top-right corner)

3.2. Evolution of Banks' Loans and Deposits market shares and Total Assets over time

The purpose of this chart is to show the evolution over time of the Banks' deposits market shares according to loans market shares (in % using amount of loans and deposits from Balance Sheet) sizing them (bubbles) by their Total Assets (from Balance Sheet).

However, although the pattern and trends are interesting, figures are not accurate since some quarterly reports are missing time to time leading to wrong figures as the whole market is therefore not represented (which explains sometime some strange behaviors in the motion chart).

User guide:
1. As the variable Size on the chart cannot be set automatically (bug), set it manually to the value “TotalAssets”
2. Press the button play (arrow) to visualize the motion chart.
3. Each bubble represent a bank, click on bubble to see the bank name and trail can be enabled (right panel)
4. You can change axis if you want but also visualize other chart using other tabs (top-right corner)

4. Intermediary Steps

Step 1: Downloading Banks' Quarterly Reports

Input: Webpage “Financial Indices of Commercial Banks” of the National Bank. (see link above)

Sub-steps:
1. Extracting the list of Georgian Banks's names (to use as referential for further steps).
2. Extracting all the URLs of the quarterly reports
3. Downloading all the quarterly reports

Outputs:

See source code: 01-harvestGeoBanks.R

Step 2: Transforming raw data into structured data

Input:

Sub-steps:
For each reports, extract following fields :
1. Bank name (handling exceptions of renamed banks and using a fuzzy matching as names are sometimes partial or with typos)
2. Report Date (using parsing as not all the time in same format and managing exceptions of wrong dates)
3. NetIncome and Personnel expenses from P&L
4. TotalAssets, Net Loans, Time Deposit and Demand Deposit from balance sheet

On consolidated data:
5. Calculate the total market of loans and deposits per quarter to calculate the market share for each bank per quarter

Outputs:

See source code: 02-transformGeoBanks.R

Step 3: Generating the charts embedded in a HTML file

Input:

Sub-steps:
1. Creating report (this HTML page) in Rmarkdown
2. For chart 1, filtering only Q3 banks' figures
3. Selecting, ordering, formating features for each chart
3. Building the charts with Google Chart (googleVis)
4. Displaying the charts embedded in the page.

Output:

See source code: 03-GeoBanks.Rmd

5. Lessons Learned

Main Data Quality issues