Covid-19: Analysing Covid-19 and the Impact on Bursa Malaysia KLSE Index

While taking the Google Data Analytics course, I was inspired to do a case study on Covid-19 and analyse the impact on my country’s stock market.

While I was searching for a reputable website to extract the Covid-19 data, I stumbled upon Alex Freberg’s Data Analyst Portfolio Project using SQL and Tableau on Covid-19 cases.

So, I took parts of what I learnt from his Youtube tutorial and added my own analysis on the Malaysian stock market.

Objective: Analyse Covid-19 worldwide data and its impact on the KLSE Bursa Index Prices in Malaysia.

We are expecting a parallel impact of the number of cases and deaths on the stock market.

Dataset

• Covid-19 data from 1 Jan 2020 to 5 Jul 2021 from Our World in Data
• Bursa Malaysia KLSE Index Prices from 1 Jan 2020 to 2 Jul 2021 from Yahoo! Finance

Data Analysis

We split the Covid-19 data set into 2 CSV files in order to perform JOINs in SQL. In total, there are 3 CSV files.

• covid_deaths.csv — containing worldwide Covid-19 cases and death data
• covid_vaccs.csv — containing worldwide Covid-19 vaccinations data
• klse.csv — containing KLSE index prices

The 3 data sets are then imported in SQL where we will be exploring and analysing the data.

-----------------------
-- Covid-19 Analysis --
-----------------------
-- View imported tables
SELECT *
FROM dbo.covid_deaths;
SELECT *
FROM dbo.covid_vaccs;
SELECT *
FROM dbo.klse;

dbo.covid_deaths

dbo.covid_vaccs

dbo.klse

-- *********************
-- ANALYSIS BY LOCATION
-- *********************
-- 1 Worldwide > Total Cases, Total Deaths & Death Rate by Country and Date
-- Shows the likelihood of dying if you contract Covid-19
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases) * 100 AS death_rate
FROM dbo.covid_deaths
WHERE continent IS NOT NULL
ORDER BY location, date;
-- 2 Malaysia > Total Cases, Total Deaths & Death Rate by Date
SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases) * 100 AS death_rate
FROM dbo.covid_deaths
WHERE location = 'Malaysia'
ORDER BY date;

1 — Death rate represents the likelihood of dying if you contract Covid-19. 1st death in Afghanistan is on the 28th day after the 1st case is detected.

2 — Malaysia recorded its 1st Covid-19 death on 17 March which is also the 1st day of lockdown.

-- 3 Worldwide > Infection Rate per Population by Country & Date
-- Show the percentage of population contracting Covid-19 at a given date
SELECT location, date, total_cases, population, (total_cases/population) * 100 AS infection_rate
FROM dbo.covid_deaths
WHERE continent IS NOT NULL
ORDER BY location, date;

3 — Infection Rate by Country’s population at a given date

-- 4 Malaysia > Infection Rate per Population by Date
-- Show the percentage of population contracting Covid-19 in Malaysia
SELECT location, date, total_cases, population, (total_cases/population) * 100 AS infection_rate
FROM dbo.covid_deaths
WHERE location = 'Malaysia'
ORDER BY date;

4 — Shows the infection rate in Malaysia. On 14 Mar 2021, infection rate is 1 out of 100 people.

-- 5 Worldwide > Countries with Highest Infection Rate compared to Population
SELECT location, population, MAX(total_cases) AS total_cases, MAX((total_cases/population)) * 100 AS infection_rate
FROM dbo.covid_deaths
GROUP BY location, population
ORDER BY infection_rate DESC;
-- 6 Malaysia > Overall Highest Infection Rate in Malaysia
SELECT location, population, MAX(total_cases) AS total_cases, MAX((total_cases/population)) * 100 AS infection_rate
FROM dbo.covid_deaths
WHERE location = 'Malaysia'
GROUP BY location, population
--ORDER BY infection_rate DESC;

5 — Top 10 countries with highest infection rate.

6 — With population of 32 million, Malaysia’s overall infection rate is at 2.4%.

-- 7 Worldwide > Highest Death Count per Population & Death Rate
SELECT location, population, MAX(total_deaths) AS total_deaths, (MAX(total_deaths)/population) * 100 AS death_rate_by_population
FROM dbo.covid_deaths
WHERE continent IS NOT NULL
GROUP BY location, population
ORDER BY death_rate_by_population DESC;
-- 8 Malaysia > Highest Death Count by Population & Death Rate
SELECT location, population, MAX(total_deaths) AS total_deaths, (MAX(total_deaths)/population) * 100 AS death_rate_by_population
FROM dbo.covid_deaths
WHERE location = 'Malaysia'
GROUP BY location, population
--ORDER BY death_rate_by_population DESC;

7 — Top 10 countries with highest death rate.

8 — Malaysia has average death rate of 0.02%.

-- *********************
-- ANALYSIS BY CONTINENT
-- *********************
-- 9 Worldwide > Infection Rate & Death Rate by Continent
SELECT d.location, d.population, MAX(total_cases) AS total_cases, 
 MAX(total_deaths) AS total_deaths,
 (MAX(total_cases)/d.population) * 100 AS infection_rate, 
 (MAX(total_deaths)/MAX(total_cases)) * 100 AS death_perc
FROM dbo.covid_deaths AS d
JOIN dbo.covid_vaccs AS v
 ON d.date = v.date
WHERE d.continent IS NULL
 AND d.location != 'World'
 AND d.location != 'International'
 AND d.location != 'European Union'
GROUP BY d.continent, d.location, d.population
ORDER BY (MAX(total_cases)/d.population) * 100 DESC;

9 — America has the highest infection and death percentage followed by Europe.

-- ***********************
-- ANALYSIS BY VACCINATION
-- ***********************
-- 10 Worldwide > Rolling Vaccinations by Country & Date
SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, 
 SUM(CONVERT(INT,v.new_vaccinations)) OVER (PARTITION BY d.location
  ORDER BY d.location, d.date) 
  AS rolling_vaccinations
-- Partition by location & date to ensure that once the rolling sum of new vaccinations for a location stops, the rolling sum starts for the next location
FROM dbo.covid_deaths AS d
JOIN dbo.covid_vaccs AS v
 ON d.location = v.location 
AND d.date = v.date
WHERE d.continent IS NOT NULL
ORDER BY d.location, d.date;

10 — Vaccinations in Japan begins in mid-Feb 2021.

-- 11 Malaysia > Rolling Vaccinations by Date
SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, 
 SUM(CONVERT(INT,v.new_vaccinations)) OVER (PARTITION BY d.location
  ORDER BY d.location, d.date) AS rolling_vaccinations
FROM dbo.covid_deaths AS d
JOIN dbo.covid_vaccs AS v
 ON d.location = v.location 
 AND d.date = v.date
WHERE d.location = 'Malaysia'
ORDER BY d.location, d.date;

11 — Same goes to Malaysia’s vaccination exercise.

-- Use CTE
-- 12 Malaysia > Rolling Vaccinations & Percentage of Vaccinated Population
WITH vaccination_per_population (continent, location, date, population, new_vaccinations, rolling_vaccinations) 
AS 
(
SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, 
 SUM(CONVERT(INT,v.new_vaccinations)) OVER (PARTITION BY d.location
  ORDER BY d.location, d.date) AS rolling_vaccinations
FROM dbo.covid_deaths AS d
JOIN dbo.covid_vaccs AS v
 ON d.location = v.location 
 AND d.date = v.date
WHERE d.continent IS NOT NULL AND d.location = 'Malaysia'
)
SELECT *, (rolling_vaccinations/population) * 100 AS vaccinated_per_population
FROM vaccination_per_population;

12 — By 3 Jul 2021, vaccination rate is at 28%.

-- Create TEMP TABLE
DROP TABLE IF EXISTS perc_population_vaccinated
CREATE TABLE perc_population_vaccinated
 (
 continent NVARCHAR(255),
 location NVARCHAR(255),
 date DATETIME,
 population NUMERIC,
 new_vaccinations NUMERIC,
 rolling_vaccinations NUMERIC
 )
-- Insert into TEMP TABLE
INSERT INTO perc_population_vaccinated
SELECT d.continent, d.location, d.date, d.population, v.new_vaccinations, 
 SUM(CONVERT(INT,v.new_vaccinations)) OVER (PARTITION BY d.location
  ORDER BY d.location, d.date) AS rolling_vaccinations
FROM dbo.covid_deaths AS d
JOIN dbo.covid_vaccs AS v
 ON d.location = v.location 
 AND d.date = v.date
WHERE d.continent IS NOT NULL
SELECT *, (rolling_vaccinations/population) * 100 AS vaccinated_per_population
FROM perc_population_vaccinated
WHERE location = 'Malaysia';

To give a brief timeline of Malaysia’s Covid-19 lockdown to understand the significance of the stock market crash.

We consider MCO, or also known as Movement Control Order to be the strictest lock phase in Malaysia where only essential services are allowed to operate with majority of people working from home and only 1 person per household can go out for groceries.

-- ************************************************
-- ANALYSIS OF IMPACT ON MALAYSIA BURSA INDEX PRICE
-- ************************************************
-- 13 Malaysia > Infection Rate & Death Rate vs KLSE Index Price by Date during MCO 1.0
SELECT d.date, location, new_cases, total_cases, new_deaths, total_deaths, (total_cases/population) * 100 AS infection_rate, 
 (total_deaths/population) * 100 AS death_perc, 
 k.adj_close
FROM dbo.covid_deaths AS d
LEFT JOIN dbo.klse AS k
 ON d.date = k.Date
WHERE location = 'Malaysia' 
 AND d.date BETWEEN '2020-03-17' AND '2020-05-03'
ORDER BY d.date ASC;

13 —During MCO 1.0 in mid-Mar to May 2020, Malaysia index prices (“adj_close”) were badly impacted and was at its lowest since the 9/11 with infection rate at 1.7%.

-- 14 Malaysia > Infection Rate & Death Rate vs KLSE Index Price by Date during MCO 2.0
SELECT d.date, location, new_cases, total_cases, new_deaths, total_deaths, (total_cases/population) * 100 AS infection_rate,  
 (total_deaths/population) * 100 AS death_perc, 
 k.adj_close
FROM dbo.covid_deaths AS d
LEFT JOIN dbo.klse AS k
 ON d.date = k.Date
WHERE location = 'Malaysia' 
 AND d.date BETWEEN '2021-01-13' AND '2021-04-03'
ORDER BY d.date ASC;

14 — MCO 2.0 in mid-Jan to early Apr 2021.

-- 15 Malaysia > Infection Rate & Death Rate vs KLSE Index Price by Date during MCO 3.0
SELECT d.date, location, new_cases, total_cases, new_deaths, total_deaths, (total_cases/population) * 100 AS infection_rate, 
 (total_deaths/population) * 100 AS death_perc, 
 k.adj_close
FROM dbo.covid_deaths AS d
LEFT JOIN dbo.klse AS k
 ON d.date = k.Date
WHERE location = 'Malaysia' 
 AND d.date BETWEEN '2021-05-07' AND '2021-05-31';

15 — MCO 3.0 in May 2021. Prices are slowly dropping with the rise of daily new cases and deaths.

-- 16 Malaysia > Infection Rate & Death Rate vs KLSE Index Price by Date in early Jul 2021
SELECT d.date, location, new_cases, total_cases, new_deaths, total_deaths, (total_cases/population) * 100 AS infection_rate, 
 (total_deaths/population) * 100 AS death_perc, 
 k.adj_close
FROM dbo.covid_deaths AS d
LEFT JOIN dbo.klse AS k
 ON d.date = k.Date
WHERE location = 'Malaysia' 
ORDER BY d.date DESC;

16 — As of 4 Jul 2021, our daily cases are at a range of 5–6k with no signs of dropping with prices slipping down edging to 1500.

-- 17 Malaysia > Vaccination Rate by Date
SELECT v.date, location, new_vaccinations, total_vaccinations, (total_vaccinations/population) * 100 AS vaccination_rate, k.adj_close
FROM dbo.covid_vaccs AS v
LEFT JOIN dbo.klse AS k
 ON v.date = k.Date
WHERE location = 'Malaysia' 
 AND (total_vaccinations/population) * 100 > 1
ORDER BY v.date DESC;

17 — Vaccination rate is steadily increasing now at its 28%. Meaning 28 out of every 100 people is vaccinated.

Data Visualisations

Then, I performed data visualisations for Malaysia using Tableau. You may view the original visualisation here.

Based on the statistics,

▲ Out of 100 people, 2 people will get infected and 1 of them will die from Covid-19.

▲ As of 22 Jun 2021, the vaccination rate is at 15%, a considerably slow effort considering that the vaccination exercise starts in early March. On average, the rate is at 5% each month.

▲ Highest average new cases and deaths in June 2021 at 6,203 cases and 80 deaths.

▲ Forecast shows that if infection rate does not drop in the coming days, new daily cases and deaths will average from 5,600 increasing to 6,400 cases with 52 deaths in the next 3 months.

Update 1 Jul: For the past week, the cases have been fluctuating in the 5,000–6,000 range and hitting 6,200 on 30 Jun.

▲

▲ The impact of Covid-19 is significant on the KLSE Index Price.

MCO timeline for reference:
— MCO 1.0 — 18 Mar 2020–3 May 2020
— MCO 2.0 — 13 Jan 2021–31 May 2021
— MCO 3.0 — 1 Jun 2021–28 Jun 2021

▲ The index is worst hit on 18 Mar 2020 which is 1st day of MCO 1.0 with drop in 165 index points. It bounced in June 2020 when average new cases is at 27 cases.
▲ In early Oct 2020, cases are seen on a rise as there is a widespread of cases due to elections in Sabah, hence bringing down the index price again with a fall of 56 points.
▲ In Nov and Dec 2020, the Malaysian government made initiatives in loosing the SOP whilst allowing dine-ins and lifted interstate travel ban (which has largely contributed to the 3rd wave in 2021). The index points rise with overall positive outlook and reinstatement of economy.

▲Minimal impact on index prices in 2021 as people could be indifferent to the rising cases or influenced by the overall global economic recovery.