## loading libraries
library(tidyverse)
library(tidyquant)
library(scales)
library(ggrepel)
library(readxl)
library(readr)
library(janitor)
library(here)
library(rnaturalearth)
library(countrycode)
library(wbstats)
library(broom)
library(maps)
library(lubridate)
options(scipen=10)final_project_dc
#| echo: false
load libraries
EBRD data
The EBRD_Ukraine_project_summaries are taken from https://www.ebrd.com/work-with-us/project-finance/project-summary-documents.html?c37=on&keywordSearch=. They include information on project_id, project_name, sector, project_type (private vs state), link to the project website, and status (concept reviewed, approved, signed, disbursed, repaying, complete, cancelled). This data is the most up to data but it does not include information on the volume of the project (only when following the link to the project website). Instead of scraping the EBRD website for this information I used a subset with data on Ukraine from a dataset with all EBRD investments between 1991 and 2021 (https://www.ebrd.com/work-with-us/project-finance/ebrd-investments.xlsx) and manually added the investment information on the 25 projects added in Ukraine after 2021 by going through the project websites.
I harmonized sector and project type categories and also harmonized project names to facilitate merging the datasets and importantly get the most complete dataset on the projects possible.
#clean EBRD project data
EBRD_Ukraine_project_summaries <- read_csv("EBRD_Ukraine_project_summaries.csv")Rows: 352 Columns: 8
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (6): Country, Title, Sector, Project Type, Project Status, Link
dbl (1): ProjectID
dttm (1): Publication Date
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.Ukraine_projects_2 <- read_excel("Ukraine_projects_2.xlsx")
# harmonizing column names and categories of project types, sectors and standardizing project names
# The EBRD has classified a loan to a bank of which the Ukrainian state holds more than 98% of shares once as a Private project and once as State project. Since it is officially still a private enterprise, I will categorize it as a Private project.
Ukraine_projects_3 <- Ukraine_projects_2 %>%
clean_names() %>%
rename(project_name = "operation_name",
project_type = "portfolio_class") %>%
mutate(project_type = ifelse(project_type == "PRIVATE", "Private", "State")) %>%
mutate(sector = str_to_lower(sector) %>%
str_replace_all('&', 'and') %>%
str_to_title() %>%
str_replace('And', 'and')) %>%
mutate(sector = ifelse(sector == "Municipal and Env Inf", "Municipal and Environmental Infrastructure", sector)) %>%
mutate(project_name = str_to_lower(project_name) %>%
str_to_title()) %>%
mutate(project_name = gsub("project", "", project_name, ignore.case = TRUE)) %>%
mutate(project_name = str_squish(project_name))
Ukraine_projects_4 <- Ukraine_projects_3 %>%
mutate(sector = ifelse(project_name == "Balkan Gastransit", "Natural Resources", sector),
sector = ifelse(project_name == "Balkan Gas Transit Ii", "Natural Resources", sector),
project_name = ifelse(project_name == "Black Sea Shipping Management Co. Ltd (Bssm)", "Black Sea Shipping Management Co. Ltd", project_name),
project_name = ifelse(project_name == "Braeside (F.oisiw Cable)", "Braeside", project_name),
project_name = ifelse(project_name == "Doep (Amended)", "Doep", project_name),
project_name = ifelse(project_name == "Ffh Ukraine Holdings (F. Universalna)", "Ffh Ukraine Holdings", project_name),
project_name = ifelse(project_name == "First Lease Llc (Ald Automotive Ukraine)", "First Lease Llc", project_name),
project_name = ifelse(project_name == "Naftogaz Eurobond (F. Condor Bond)", "Naftogaz Eurobond", project_name) ,
project_name = ifelse(project_name == "Slobozhanska Budivelna Keramika (Sbk)", "Slobozhanska Budivelna Keramika", project_name) ,
project_name = ifelse(project_name == "Ukraine Second Kiev-Chop Road Rehabilitation\"", "Ukraine Second \"Kiev-Chop Road Rehabilitation\"", project_name),
project_name = ifelse(project_name == "Ukraine Subordinated Credit Facility To Jscb Otp Bank, Ukraine", "Ukraine Subordinated Credit Facility To Jscb Otp Bank", project_name),
project_name = ifelse(project_name == "Ukraine Third Kiev-Chop M06 Road Rehabilitation\"", "Ukraine Third \"Kiev-Chop M06 Road Rehabilitation\"", project_name),
project_name = ifelse(project_name == "Ukreximbank Senior Loan", "Ukrexim Bank Subordinated Loan", project_name)) %>%
select(!"country")
write.csv("Ukraine_projects_4", "./processed_01/Ukraine_projects_4.csv")
EBRD_Ukraine_project_summaries_2 <- EBRD_Ukraine_project_summaries %>%
clean_names() %>%
rename(project_name = "title") %>%
mutate(sector = str_to_lower(sector) %>%
str_replace_all('&', 'and') %>%
str_to_title() %>%
str_replace('And', 'and')) %>%
mutate(sector = ifelse(sector == "Non-Depository Credit (Non Bank)", "Financial Institutions", sector),
sector = ifelse(sector == "Power and Energy", "Energy", sector),
sector = ifelse(sector == "Information and Communication Technologies", "Telecommunications, Media and Technology", sector),
project_type = ifelse(project_type == "Public", "State", project_type)) %>%
mutate(project_name = str_to_lower(project_name) %>%
str_to_title()) %>%
mutate(project_name = gsub("project", "", project_name, ignore.case = TRUE)) %>%
mutate(project_name = str_squish(project_name))
EBRD_Ukraine_project_summaries_3 <- EBRD_Ukraine_project_summaries_2 %>%
mutate(sector = ifelse(project_name == "Fif - Dcfta - Otp Leasing Sme Facility #3", "Leasing Finance", sector),
sector = ifelse(project_name == "Fif - Eap Smec - Otp Leasing Sme Loan", "Leasing Finance", sector),
project_type = ifelse(project_name == "Fif - Eap Smec - Ukrgasbank Sme Loan", "Private", project_type),
project_name = ifelse(project_name == "Horizon Capital Growth Fund Ii L.p.", "Horizon Capital Growth Fund Ii", project_name),
project_name = ifelse(project_name == "Nibulon Working Capital Syndicated Facility", "Nibulon Working Capital Facility", project_name),
project_name = ifelse(project_name == "Ukraine Second Kiev-Chop Road Rehabilitation\"", "Ukraine Second \"Kiev-Chop Road Rehabilitation\"", project_name),
project_name = ifelse(project_name == "Ukraine Third Kiev-Chop M06 Road Rehabilitation\"", "Ukraine Third \"Kiev-Chop M06 Road Rehabilitation\"", project_name))
write.csv("EBRD_Ukraine_project_summaries_3", "./processed_01/EBRD_Ukraine_project_summaries_3.csv")because all other data is in USD I will now convert the Euro amounts into USD using yearly average exchange rates from the OECD (Units of Euros per USD) https://stats.oecd.org/index.aspx?queryid=169#
exchange_rates <- read_excel("exchange_rates.xlsx", skip = 3)New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`exchange_rates_2 <- exchange_rates %>%
slice(2) %>%
select("1990":"2022") %>%
pivot_longer(
cols = "1990":"2022",
names_to = "year",
values_to = "EUR_per_USD"
) %>%
mutate(year = as.numeric(year))
write_csv(exchange_rates_2, "./processed_01/Ukraine_projects_4.csv")In the next step, I merge the two datasets and clean the resulting dataset. I also created a new variable where I assign the status “Signed” to all the projects from the investment list that were not matched to a status from the project summaries dataset. I know that these projects have passed at least the status of “signed” and have not been cancelled, I don’t know if they have been completed. The project_status_full variable should thus be seen as an “at least” status. It would also be helpful to have a date for every project that indicates roughly when the project starts. The investment list provided the dates when the project was signed, while the project summary list provides dates of publication. Following the project stages at the EBRD it is reasonable to assume that a project is at least publicly disclosed when disbursal starts.
# merge
Ukraine_all_projects <- merge(EBRD_Ukraine_project_summaries_3, Ukraine_projects_4, by = c("project_name", "sector", "project_type"), all = TRUE)
# clean the merged set, (removing doubles after checking it is the same project), row numbers change with each pipe!
Ukraine_all_projects_2 <- Ukraine_all_projects %>%
filter(row_number() != 36) %>% # removing a double project_id (Balkan Gastransit, publication_date == "2001-08-03 11:54:59")
filter (row_number() != 117) %>% # another double, ID: 52695, Dff - Nyva Extension
filter(row_number() != 180) %>% # another double, ID: 50907, Fif - Procredit Bank Ukraine Sme Lcy Loan
filter(row_number() != 322) %>% # another double, ID: 3541, North-Eastern Ukraine Post Privatisation Fund
filter(row_number() != 408) %>% # another double, ID: 37598, Rivne Kyiv High Voltage Line
filter(row_number() != 492) %>% # another double, ID: 3663, Ukraine Energy Service Company - Ukresco
filter(row_number() != 533) # another double, ID: 44697, Ukrelevatortrans (Uet)
Ukraine_all_projects_3 <- Ukraine_all_projects_2 %>%
mutate(country = ifelse(is.na(country), "Ukraine", country),
project_status = ifelse(project_status == "Completed", "Complete", project_status),
project_status = ifelse(project_status == "Board Approved", "Approved", project_status),
project_status = ifelse(project_status == "Passed Concept Review, Pending Final Review", "Concept Reviewed", project_status),
project_status_full = ifelse(is.na(project_status), "Signed", project_status)) %>%
separate(publication_date, into = c("pub_date", "time"), sep = " ") %>%
mutate(pub_date = as.POSIXct(pub_date)) %>%
select(!"time")
# summary statistics to see whether I have forgotten something important
Ukraine_all_projects_3 %>%
count(project_status) %>%
mutate(prop = n/sum(n)*100) project_status n prop
1 Approved 15 2.3510972
2 Cancelled 39 6.1128527
3 Complete 189 29.6238245
4 Concept Reviewed 2 0.3134796
5 Disbursing 30 4.7021944
6 Repaying 37 5.7993730
7 Signed 37 5.7993730
8 <NA> 289 45.2978056Ukraine_all_projects_3 %>%
count(project_status_full) %>%
mutate(prop = n/sum(n)*100) project_status_full n prop
1 Approved 15 2.3510972
2 Cancelled 39 6.1128527
3 Complete 189 29.6238245
4 Concept Reviewed 2 0.3134796
5 Disbursing 30 4.7021944
6 Repaying 37 5.7993730
7 Signed 326 51.0971787# understanding POSIXct time format
Ukraine_all_projects_3 %>%
filter(project_name == "Ad-Zarya") %>%
summarize(diff = pub_date - original_signing_date) diff
1 -93.79167 days# 1997-11-03 - 1998-02-05
# negative result means pub_date happened BEFORE original signing date
Ukraine_all_projects_3 %>%
filter(! (is.na(pub_date) | is.na(original_signing_date))) %>%
summarize(mean_time = mean(pub_date - original_signing_date),
max_time = max(pub_date - original_signing_date),
min_time = min(pub_date - original_signing_date)) mean_time max_time min_time
1 -2238.367 hours 71501 hours -34963 hoursUkraine_all_projects_3 %>%
filter(! (is.na(pub_date) | is.na(original_signing_date))) %>%
filter (original_signing_date < pub_date) project_name
1 Air Liquide Ukraine
2 Arricano Prospect
3 Astarta Wc Support Loan
4 Balkan Gastransit
5 Braeside
6 Dff - Dniprovska Agri
7 Ffh Ukraine Holdings
8 Fif - Procredit Bank Ukraine Sme Lcy Loan
9 G4g: Rbi Ukraine
10 Grcf - Lviv Solid Waste
11 Horizon Capital Growth Fund Ii
12 Ikea Ukraine
13 Kiev International Bank
14 Naftogaz Eurobond
15 Ojsc Slavutich Brewery
16 Rf - Uksatse Liquidity Facility
17 Ternopil District Heating Modernisation
18 Ukrrichflot
19 Ukrsibbank (F. Izia)
20 Uptf - Zhytomyr Trolleybus
21 Us/Ebrd Sme - Procredit Bank Ukraine
22 Us/Ebrd Sme - Procredit Bank Ukraine
23 Vcip Ii - Scalarr
sector project_type pub_date
1 Manufacturing and Services Private 2011-06-20
2 Property and Tourism Private 2014-12-23
3 Agribusiness Private 2020-07-09
4 Natural Resources Private 2001-08-03
5 Telecommunications, Media and Technology Private 2007-08-14
6 Agribusiness Private 2018-03-22
7 Insurance, Pension, Mutual Funds Private 2019-11-15
8 Financial Institutions Private 2020-08-27
9 Financial Institutions Private 2021-12-24
10 Municipal and Environmental Infrastructure State 2020-10-23
11 Equity Funds Private 2021-06-11
12 Property and Tourism Private 2007-09-21
13 Financial Institutions Private 1999-03-18
14 Natural Resources Private 2019-07-31
15 Agribusiness Private 2001-11-22
16 Transport State 2020-07-10
17 Municipal and Environmental Infrastructure State 2013-01-23
18 Transport Private 2000-08-04
19 Financial Institutions Private 2011-12-22
20 Municipal and Environmental Infrastructure State 2020-04-28
21 Financial Institutions Private 2004-07-28
22 Financial Institutions Private 2004-07-28
23 Telecommunications, Media and Technology Private 2021-04-15
project_id country project_status
1 40936 Ukraine Signed
2 46819 Ukraine Complete
3 50180 Ukraine Cancelled
4 5836 Ukraine Complete
5 38267 Ukraine Disbursing
6 48474 Ukraine Complete
7 41514 Ukraine Signed
8 50907 Ukraine Cancelled
9 51350 Ukraine Signed
10 49437 Ukraine Repaying
11 52761 Ukraine Signed
12 35688 Ukraine Complete
13 6252 Ukraine Complete
14 50809 Ukraine Signed
15 21682 Ukraine Complete
16 52251 Ukraine Signed
17 42524 Ukraine Repaying
18 2224 Ukraine Complete
19 42245 Ukraine Complete
20 48237 Ukraine Repaying
21 33532 Ukraine Complete
22 33532 Ukraine Complete
23 51608 Ukraine Signed
link
1 //www.ebrd.com/work-with-us/projects/psd/air-liquide-ukraine.html
2 //www.ebrd.com/work-with-us/projects/psd/psd-arricano-prospect.html
3 https://www.ebrd.com/work-with-us/projects/psd/astarta-wc-support-loan.html
4 //www.ebrd.com/work-with-us/projects/psd/5836.html
5 //www.ebrd.com/work-with-us/projects/psd/braeside-.html
6 https://www.ebrd.com/work-with-us/projects/psd/dff-dniprovska-agri.html
7 //www.ebrd.com/work-with-us/projects/psd/ojsc-insurance-company-universalna.html
8 //www.ebrd.com/work-with-us/projects/psd/50907.html
9 //www.ebrd.com/work-with-us/projects/psd/51350.html
10 https://www.ebrd.com/work-with-us/projects/psd/49437.html
11 //www.ebrd.com/work-with-us/projects/psd/52761.html
12 //www.ebrd.com/work-with-us/projects/psd/ikea-ukraine.html
13 //www.ebrd.com/work-with-us/projects/psd/kiev-international-bank.html
14 //www.ebrd.com/work-with-us/projects/psd/50809.html
15 //www.ebrd.com/work-with-us/projects/psd/ojsc-slavutich-brewery.html
16 https://www.ebrd.com/work-with-us/projects/psd/52251.html
17 //www.ebrd.com/work-with-us/projects/psd/ternopil-district-heating-modernisation.html
18 //www.ebrd.com/work-with-us/projects/psd/ukrrichflot.html
19 //www.ebrd.com/work-with-us/projects/psd/ukrsibbank-(f.-izia).html
20 https://www.ebrd.com/work-with-us/projects/psd/48237.html
21 //www.ebrd.com/work-with-us/projects/psd/usebrd-sme-procredit-bank-ukraine.html
22 //www.ebrd.com/work-with-us/projects/psd/usebrd-sme-procredit-bank-ukraine.html
23 //www.ebrd.com/work-with-us/projects/psd/51608.html
direct_regional original_signing_date ebrd_finance ebrd_finance_debt
1 Direct 2011-06-17 80983982 80983982
2 Direct 2014-12-23 16785935 16785935
3 Direct 2018-08-08 23853697 23853697
4 Direct 1999-12-21 35338811 35338811
5 Direct 2007-08-10 22198176 0
6 Direct 2017-07-13 4417351 4417351
7 Direct 2011-09-19 8160029 0
8 Direct 2019-10-08 22388431 22388431
9 Direct 2021-12-16 74382411 0
10 Direct 2018-06-01 20000000 20000000
11 Direct 2021-04-23 8366043 0
12 Direct 2007-09-21 13252054 13252054
13 Direct 1995-08-18 1750000 0
14 Direct 2019-07-12 120000000 120000000
15 Direct 2001-11-21 15018995 15018995
16 Direct 2020-07-10 20000000 20000000
17 Direct 2012-09-28 7000000 7000000
18 Direct 1995-09-29 7127838 7127838
19 Direct 2011-04-21 76496464 0
20 Direct 2018-11-29 9000000 9000000
21 Direct 2003-11-14 4399368 3533881
22 Direct 2000-10-02 9951134 7951232
23 Direct 2020-11-16 2524823 0
ebrd_finance_equity ebrd_finance_guarantee project_status_full
1 0.0 0 Signed
2 0.0 0 Complete
3 0.0 0 Cancelled
4 0.0 0 Complete
5 22198176.0 0 Disbursing
6 0.0 0 Complete
7 8160028.9 0 Signed
8 0.0 0 Cancelled
9 0.0 74382411 Signed
10 0.0 0 Repaying
11 8366043.2 0 Signed
12 0.0 0 Complete
13 1750000.0 0 Complete
14 0.0 0 Signed
15 0.0 0 Complete
16 0.0 0 Signed
17 0.0 0 Repaying
18 0.0 0 Complete
19 76496463.8 0 Complete
20 0.0 0 Repaying
21 865487.2 0 Complete
22 1999901.3 0 Complete
23 2524822.6 0 Signed# in only 23 cases (of the 248 that have both dates), the date of signature was before the date of public disclosure
# apparently, most projects are publicly disclosed before they get signed, on average they get disclosed about three months before they get signed. The signature is closer to the disbursal and when the project actually affects the country. The date of public disclosure is probably closer to the initiative to set up a project. I will create two variables, one favoring the signature date (start_date) and one favoring the disclosure date (initiative_date) where both dates are available.
Ukraine_all_projects_4 <- Ukraine_all_projects_3 %>%
mutate(initiative_date = case_when(
is.na(pub_date) ~ original_signing_date,
TRUE ~ pub_date)) %>%
mutate(start_date = case_when(
is.na(original_signing_date) ~ pub_date,
TRUE ~ original_signing_date
))
Ukraine_all_projects_5 <- Ukraine_all_projects_4 %>%
mutate(year = year(start_date),
ebrd_finance = ifelse(is.na(ebrd_finance), 0, ebrd_finance),
ebrd_finance_debt = ifelse(is.na(ebrd_finance), 0, ebrd_finance_debt),
ebrd_finance_equity = ifelse(is.na(ebrd_finance), 0, ebrd_finance_equity),
ebrd_finance_guarantee = ifelse(is.na(ebrd_finance), 0, ebrd_finance_guarantee))
Ukraine_all_projects_6 <- Ukraine_all_projects_5 %>%
left_join(exchange_rates_2, by = "year") %>%
mutate(
ebrd_finance_USD = ebrd_finance/EUR_per_USD,
ebrd_finance_debt_USD = ebrd_finance_debt/EUR_per_USD,
ebrd_finance_equity_USD = ebrd_finance_equity/EUR_per_USD,
ebrd_finance_guarantee_USD = ebrd_finance_guarantee/EUR_per_USD)
write.csv("Ukraine_all_projects_4", "./processed_01/Ukraine_all_projects_4.csv")
write.csv("Ukraine_all_projects_6", "./processed_01/Ukraine_all_projects_6.csv")EBRD all projects all countries
EBRD_all_countries_PSD : project summaries (without investment volume) EBRD_overview: overview over cumulated investments - in mln reported euro EBRD_all_countries_investments (then EBRD_ac_inv): investments in all countries (volumes) - Net Cumulative Business Investment - 2021, in mln reported euro
For comparison, I will also look at all projects in all countries from the EBRD that are listed on the project summaries list (covering all projects in all countries but without investment volume).
EBRD_all_countries_PSD <- read_csv("EBRD_all_countries_PSD.csv")Rows: 4423 Columns: 8
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (7): Publication Date, Country, Title, Sector, Project Type, Project Sta...
dbl (1): ProjectID
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.EBRD_overview <- read_excel("ebrd-investments.xlsx", sheet = "Summary", skip = 5)
EBRD_all_countries_investments <- read_excel("ebrd-investments.xlsx", sheet = "List", skip = 5)
EBRD_ac_inv <- EBRD_all_countries_investments %>%
clean_names() %>%
rename(project_name = "operation_name",
project_type = "portfolio_class") %>%
mutate(project_type = ifelse(project_type == "PRIVATE", "Private", "State")) %>%
mutate(sector = str_to_lower(sector) %>%
str_replace_all('&', 'and') %>%
str_to_title() %>%
str_replace('And', 'and')) %>%
mutate(sector = ifelse(sector == "Municipal and Env Inf", "Municipal and Environmental Infrastructure", sector)) %>%
mutate(country = str_to_lower(country) %>%
str_to_title()) %>%
mutate(project_name = str_to_lower(project_name) %>%
str_to_title()) %>%
mutate(project_name = gsub("project", "", project_name, ignore.case = TRUE)) %>%
mutate(project_name = str_squish(project_name))
EBRD_ac_inv_wo_U <- EBRD_ac_inv %>%
filter(! country == "Ukraine") %>%
mutate(year = year(original_signing_date))
EBRD_ac_inv_wo_U_2 <- EBRD_ac_inv_wo_U %>%
left_join(exchange_rates_2, by = "year") %>%
mutate(
ebrd_finance_USD = ebrd_finance/EUR_per_USD,
ebrd_finance_debt_USD = ebrd_finance_debt/EUR_per_USD,
ebrd_finance_equity_USD = ebrd_finance_equity/EUR_per_USD,
ebrd_finance_guarantee_USD = ebrd_finance_guarantee/EUR_per_USD)
write_csv(EBRD_ac_inv_wo_U_2, "./EBRD_ac_inv_wo_U_2.csv")
EBRD_overview_sector <- EBRD_overview %>%
slice(1:14) %>%
clean_names() %>%
mutate(sector = str_to_lower(sector) %>%
str_replace_all('&', 'and') %>%
str_to_title() %>%
str_replace('And', 'and') %>%
str_squish()) %>%
mutate(sector = ifelse(sector == "Municipal and Env Inf", "Municipal and Environmental Infrastructure", sector),
sector = ifelse(is.na(sector), "Total", sector),
row_number = row_number()) %>%
mutate(sector_group = case_when(
row_number <= 5 ~ "Financial Institutions",
row_number <= 10 ~ "Industry, Commerce & Agribusiness",
row_number <= 13 ~ "Sustainable Infrastructure",
row_number == 14 ~ "Total")) %>%
select(!"row_number")
write_csv(EBRD_overview_sector, "./processed_01/EBRD_overview_sector.csv")
EBRD_overview_region <- EBRD_overview %>%
slice(17:55) %>%
rename(country = "Sector",
country_group = "Sector Group") %>%
clean_names() %>%
mutate(country = str_to_lower(country) %>%
str_replace_all('&', 'and') %>%
str_to_title() %>%
str_replace('And', 'and') %>%
str_squish()) %>%
mutate(row_number = row_number()) %>%
mutate(country_group = case_when(
row_number <= 6 ~ "Central Asia",
row_number <= 15 ~ "Central Europe and Baltics",
row_number <= 17 ~ "Cyprus and Greece",
row_number <= 23 ~ "Eastern Europe and Caucasus",
row_number == 24 ~ "Russia",
row_number <= 32 ~ "South-Eastern Europe",
row_number <= 37 ~ "Southern and Eastern Mediterranean",
row_number == 38 ~ "Turkey",
row_number == 39 ~ "Total")) %>%
select(!"row_number")
write_csv(EBRD_overview_sector, "./processed_01/EBRD_overview_region.csv")National Bank of Ukraine: (important: information in current USD)
all data from: https://bank.gov.ua/en/statistic/sector-external#metod_bob
Data on Foreign Investment from BoP (BOP_y) in mln USD - assets vs liabilities: https://www.oecd.org/daf/inv/FDI-statistics-asset-liability-vs-directional-presentation.pdf - current USD according to monthly average rate https://bank.gov.ua/admin_uploads/article/BoP_methodology_en.pdf
Data on Gross External Debt (ZB_y)
# Financial Account data from 2000 to 2022
BOP_y <- read_excel("BOP_y_en.xlsx", sheet = "1.4", skip = 5)New names:
• `Description` -> `Description...1`
• `Description` -> `Description...2`fin_acc <- BOP_y %>% # selecting only the Financial Account
slice(275:421) %>%
select("Description...1", "2000":"2022") %>%
rename(fin_acc_item = "Description...1") %>%
rename(y2000 = '2000') %>%
mutate(y2000 = as.numeric("y2000"))Warning: There was 1 warning in `mutate()`.
ℹ In argument: `y2000 = as.numeric("y2000")`.
Caused by warning:
! NAs durch Umwandlung erzeugt#realized on the way that it made more sense to rename everything
fin_acc_subset_1 <- fin_acc %>%
slice(c(1:2, 11:12, 17, 24, 36:37, 39, 42 , 45, 48, 51, 54:56, 79, 84:86, 89, 92:93, 114, 133)) %>%
mutate(spec_item = case_when(
row_number() == 2 ~ "1_Direct Investment Total",
row_number() == 3 ~ "1.1_DI_Net incurrence of liabilities",
row_number() == 4 ~ "1.2_DI_Equity and investment fund shares",
row_number() == 5 ~ "1.3_DI_Debt instruments",
row_number() == 6 ~ "2_Portfolio Investment Total",
row_number() == 7 ~ "2.2_PI_Net incurrence of liabilities",
row_number() == 8 ~ "2.3_PI_Equity and investment fund shares",
row_number() == 9 ~ "2.4_PI_Debt Securities Total",
row_number() == 10 ~ "2.4.1_PI_DS_Deposit-taking corporations, except central bank",
row_number() == 11 ~ "2.4.2_PI_DS_General government",
row_number() == 12 ~ "2.4.3_PI_DS_Other sectors",
row_number() == 13 ~ "3_Financial derivatives net",
row_number() == 14 ~ "4_Other investment Total",
row_number() == 15 ~ "4.1_OI_Net acquisition of financial assets",
row_number() == 16 ~ "4.2_OI_Net incurrence of liabilities",
row_number() == 17 ~ "4.3_OI_Loans",
row_number() == 18 ~ "4.3.1_Net incurrence of liabilities",
row_number() == 19 ~ "4.3.1.1_Central bank",
row_number() == 20 ~ "4.3.1.1.1_Credit and loans with the IMF",
row_number() == 21 ~ "4.3.1.2_Deposit-taking corporations, except the central bank",
row_number() == 22 ~ "4.3.1.3_General government",
row_number() == 23 ~ "4.3.1.3.1_Credit and loans with the IMF",
row_number() == 24 ~ "4.4_OI_Other accounts receivable payable",
row_number() == 25 ~ "4.5_OI_Special drawing rights Net incurrence of liabilities",
TRUE ~ fin_acc_item
))
fin_acc_subset_2 <- fin_acc_subset_1 %>%
select(spec_item, everything())
# Gross External Debt data from 2003 to 2022
ZB_y <- read_excel("ZB_y_en.xlsx", sheet = "1.1")Warning: Expecting numeric in B6 / R6C2: got a dateWarning: Expecting numeric in C6 / R6C3: got a dateWarning: Expecting numeric in D6 / R6C4: got a dateWarning: Expecting numeric in E6 / R6C5: got a dateWarning: Expecting numeric in F6 / R6C6: got a dateWarning: Expecting numeric in G6 / R6C7: got a dateWarning: Expecting numeric in H6 / R6C8: got a dateWarning: Expecting numeric in I6 / R6C9: got a dateWarning: Expecting numeric in J6 / R6C10: got a dateWarning: Expecting numeric in K6 / R6C11: got a dateWarning: Expecting numeric in L6 / R6C12: got a dateWarning: Expecting numeric in M6 / R6C13: got a dateWarning: Expecting numeric in N6 / R6C14: got a dateWarning: Expecting numeric in O6 / R6C15: got a dateWarning: Expecting numeric in P6 / R6C16: got a dateWarning: Expecting numeric in R6 / R6C18: got a dateWarning: Expecting numeric in S6 / R6C19: got a dateWarning: Expecting numeric in T6 / R6C20: got a dateWarning: Expecting numeric in U6 / R6C21: got a dateNew names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`colnames(ZB_y) <- c("debt_item", "y2003", "y2004","y2005","y2006","y2007","y2008","y2009","y2010","y2011","y2012","y2013","y2014","y2015","y2016", "y2017", "y2018", "y2019", "y2020", "y2021", "y2022")
ZB_y_1 <- ZB_y[-c(1:4),]
ZB_y_1 <- ZB_y_1[-c((nrow(ZB_y_1)-3):nrow(ZB_y_1)),]
ZB_y_2 <- ZB_y_1 %>%
slice(c(1, 6, 10, 16, 20, 31, 40, 46, 48))
GED_item <- c("GED_1_General Government", "GED_1.1_GG_SDR", "GED_2_Central Bank", "GED_2.1_CB_SDR", "GED_3_Deposit-Taking Corporations, except the Central Bank", "GED_4_Other sectors", "GED_5_Direct investment: intercompany lending", "GED_Total", "GED_arrears_other_secor_non_guaranteed")
ZB_y_2$GED_item <- GED_item
ZB_y_3 <- ZB_y_2 %>% select("GED_item", "y2003":"y2022")
#saving progress
write_csv(fin_acc_subset_2, "./processed_01/fin_acc_subset_2.csv")
write_csv(ZB_y_3, "./processed_01/ZB_y_3.csv")World Bank Data (information in USD)
GDP
GDP_data <- read_csv("GDP_data.csv")Rows: 7 Columns: 36
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (4): Country Name, Country Code, Series Name, Series Code
dbl (32): 1990 [YR1990], 1991 [YR1991], 1992 [YR1992], 1993 [YR1993], 1994 [...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.GDP_data_1 <- GDP_data %>%
slice(1:2)
GDP_data_2 <- GDP_data_1 %>%
pivot_longer(
cols = "1990 [YR1990]":"2021 [YR2021]",
names_to = "year",
values_to = "year_value"
) %>%
select(! "Series Code") %>%
pivot_wider(
names_from = "Series Name",
values_from = "year_value"
) %>%
separate(year, into = c("year", "year_2"), sep = " ") %>%
select(! c("year_2", "Country Code"))
GDP_data_3 <- GDP_data_2 %>%
mutate(GDP_mln_USD = `GDP (current US$)`/1000000)
write_csv(GDP_data_3, "./processed_01/GDP_data_3.csv")unused code
Ukraine_all_projects %>% group_by(project_id) %>% filter(row_number() == 1)
dist_matrix <- adist(Ukraine_all_projects$project_name)
Set a threshold for the maximum distance between similar project names
max_dist <- 6
Identify pairs of project names that have a low Levenshtein distance
matches <- which(dist_matrix <= max_dist, arr.ind = TRUE)
Extract the matching project names
matching_projects <- unique(Ukraine_all_projects$project_name[matches])
Ukraine_projects_3 %>% distinct(sector) EBRD_Ukraine_project_summaries_2 %>% distinct(sector)
Sources: https://oblrada.dp.gov.ua/en/investors/foreign-direct-investment-in-ukraine-war-and-peace/
https://tradingeconomics.com/ukraine/foreign-direct-investment
https://www.ceicdata.com/en/indicator/ukraine/foreign-direct-investment
https://data.worldbank.org/indicator/BN.KLT.PTXL.CD?locations=UA
https://data.worldbank.org/indicator/DT.DOD.DIMF.CD?locations=UA
https://www.macrotrends.net/countries/UKR/ukraine/external-debt-stock#:~:text=Short%2Dterm%20debt%20includes%20all,a%202.52%25%20increase%20from%202020.
exchange rate data https://stats.oecd.org/index.aspx?queryid=169#