import pandas as pd
import altair as alt
from vega_datasets import data
from IPython.display import display
from IPython.display import HTML
import IPython.core.display as di
di.display_html('<script>jQuery(function() {if (jQuery("body.notebook_app").length == 0) { jQuery(".input_area").toggle(); jQuery(".prompt").toggle();}});</script>', raw=True)
CSS = """#notebook div.output_subarea {max-width:100%;}""" #changes output_subarea width to 100% (from 100% - 14ex)
HTML('<style>{}</style>'.format(CSS))
from IPython.display import display, HTML
CSS = """
.output {
align-items: center;
}
"""
HTML('<style>{}</style>'.format(CSS))
### because the country name of some indices are incorrecct. They show the stock exchange name. So we modified it manually.
world_index_hist = pd.read_csv('World Index History.csv')
world_index_info = pd.read_csv('World Index Info.csv')
world_index_hist_vertical = pd.read_csv('World Index History Vertical with change rate.csv')
manual_index_hist_vertical = pd.read_csv('Manual World Index History Vertical with change rate.csv')
world_index_hist_vertical = pd.concat([world_index_hist_vertical, manual_index_hist_vertical]).reset_index()
####################### 横向
# 具体处理过程见 Data Manual
manual_index_hist = pd.read_csv('Manual World Index History.csv')
manual_index_hist['WICAN.FGI'] = manual_index_hist['WICAN.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['WIIDN.FGI'] = manual_index_hist['WIIDN.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['WIBRA.FGI'] = manual_index_hist['WIBRA.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['^DJEG20E '] = manual_index_hist['^DJEG20E '].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['^OMXI10'] = manual_index_hist['^OMXI10'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['CRE.VI'] = manual_index_hist['CRE.VI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['XU100.IS'] = manual_index_hist['XU100.IS'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['^OMXC25 '] = manual_index_hist['^OMXC25 '].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['PSI20.LS'] = manual_index_hist['PSI20.LS'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['FVTT.FGI'] = manual_index_hist['FVTT.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['WIPER.FGI'] = manual_index_hist['WIPER.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['WIPHL.FGI'] = manual_index_hist['WIPHL.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist['WICHL.FGI'] = manual_index_hist['WICHL.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
world_index_hist = world_index_hist.merge(manual_index_hist, how='left', on='Date')
price_index_1902 = world_index_hist[35:36] # price on 19 Feb.
price_index_1410 = world_index_hist[205:206] # price on 12 Oct.
price_index = pd.concat([price_index_1902, price_index_1410]).dropna(axis = 1)
price_index = price_index.drop('Date', axis=1)
price_index.reset_index(drop=True, inplace=True)
change_rate_indices = price_index.astype(float).pct_change()
change_rate_indices = change_rate_indices.loc[1].to_frame().reset_index().rename(columns = {'index': 'Name', 1 : 'change_rate'})
indices_df = change_rate_indices.merge(world_index_info, on='Name')
indices_df['change_rate_percentage'] =indices_df['change_rate'].apply(lambda x: format(x, '.2%'))
indices_df['Country'] = indices_df['Country'].str.upper()
indices_df = indices_df.drop('Unnamed: 0', axis=1)
# indices_df
code_lookup = pd.read_csv("country_lookup.csv")
indices_df = indices_df.merge(code_lookup, left_on='Country', right_on='Alpha-2 code')
indices_df = indices_df.rename(columns = {'Country_y': 'location', 'Numeric code': 'id'}).drop('Country_x', axis=1)
# indices_df
covid_data = pd.read_csv("https://covid.ourworldindata.org/data/owid-covid-data.csv")
case_data = covid_data.loc[:, ['location', 'total_cases_per_million', 'continent']]
case_data = case_data.groupby('location').last().reset_index()
indices_df = indices_df.merge(case_data, on='location', how='left')
# indices_df
### group by country
change_rate_indices_by_country = indices_df.groupby('location').first().reset_index()
change_rate_indices_by_country['location'] = change_rate_indices_by_country['location']
change_rate_indices_by_country['change_rate_nom'] =change_rate_indices_by_country['change_rate'].apply(lambda x: abs(x))
# change_rate_indices_by_country
### Hong Kong is a part of China, so the COVID-19 data considered these two as an ensemble. Hong Kong will take the data of China
change_rate_indices_by_country.loc[13, 'total_cases_per_million'] = change_rate_indices_by_country.loc[6, 'total_cases_per_million']
change_rate_indices_by_country = change_rate_indices_by_country.dropna(subset = ['total_cases_per_million'])
### group by continent
change_rate_indices_by_continent = indices_df.groupby('continent').mean().reset_index()
change_rate_indices_by_continent['change_rate_percentage'] =change_rate_indices_by_continent['change_rate'].apply(lambda x: format(x, '.2%'))
# change_rate_indices_by_continent
### because the country name of some indices are incorrecct. They show the stock exchange name. So we modified it manually.
world_index_hist3 = pd.read_csv('World Index History.csv')
world_index_info3 = pd.read_csv('World Index Info.csv')
world_index_hist_vertical3 = pd.read_csv('World Index History Vertical with change rate.csv')
manual_index_hist_vertical3 = pd.read_csv('Manual World Index History Vertical with change rate.csv')
world_index_hist_vertical3 = pd.concat([world_index_hist_vertical3, manual_index_hist_vertical3]).reset_index()
####################### 横向
# 具体处理过程见 Data Manual
manual_index_hist3 = pd.read_csv('Manual World Index History.csv')
manual_index_hist3['WICAN.FGI'] = manual_index_hist3['WICAN.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['WIIDN.FGI'] = manual_index_hist3['WIIDN.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['WIBRA.FGI'] = manual_index_hist3['WIBRA.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['^DJEG20E '] = manual_index_hist3['^DJEG20E '].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['^OMXI10'] = manual_index_hist3['^OMXI10'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['CRE.VI'] = manual_index_hist3['CRE.VI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['XU100.IS'] = manual_index_hist3['XU100.IS'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['^OMXC25 '] = manual_index_hist3['^OMXC25 '].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['PSI20.LS'] = manual_index_hist3['PSI20.LS'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['FVTT.FGI'] = manual_index_hist3['FVTT.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['WIPER.FGI'] = manual_index_hist3['WIPER.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['WIPHL.FGI'] = manual_index_hist3['WIPHL.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
manual_index_hist3['WICHL.FGI'] = manual_index_hist3['WICHL.FGI'].apply(
lambda x: float(x.split()[0].replace(',', ''))
if(',' in str(x)) else float(x))
world_index_hist3 = world_index_hist3.merge(manual_index_hist3, how='left', on='Date')
price_index_1902 = world_index_hist3[35:36] # price on 19 Feb.
price_index_1903 = world_index_hist3[56:57] # price on 19 Mar.
price_index3 = pd.concat([price_index_1902, price_index_1903]).dropna(axis = 1)
price_index3 = price_index3.drop('Date', axis=1)
price_index3.reset_index(drop=True, inplace=True)
change_rate_indices3 = price_index3.astype(float).pct_change()
change_rate_indices3 = change_rate_indices3.loc[1].to_frame().reset_index().rename(columns = {'index': 'Name', 1 : 'drop_rate'})
indices_df3 = change_rate_indices3.merge(world_index_info3, on='Name')
indices_df3 = indices_df3.merge(change_rate_indices, on='Name')
indices_df3['drop_rate_percentage'] =indices_df3['drop_rate'].apply(lambda x: format(x, '.2%'))
indices_df3['change_rate_percentage'] =indices_df3['change_rate'].apply(lambda x: format(x, '.2%'))
indices_df3['Country'] = indices_df3['Country'].str.upper()
indices_df3 = indices_df3.drop('Unnamed: 0', axis=1)
# indices_df
code_lookup = pd.read_csv("country_lookup.csv")
indices_df3 = indices_df3.merge(code_lookup, left_on='Country', right_on='Alpha-2 code')
indices_df3 = indices_df3.rename(columns = {'Country_y': 'location', 'Numeric code': 'id'}).drop('Country_x', axis=1)
# indices_df
covid_data = pd.read_csv("https://covid.ourworldindata.org/data/owid-covid-data.csv")
case_data = covid_data.loc[:, ['location', 'total_cases_per_million', 'continent']]
case_data3 = case_data.groupby('location').last().reset_index()
indices_df3 = indices_df3.merge(case_data, on='location', how='left')
# indices_df
### group by country
change_rate_indices_by_country3 = indices_df3.groupby('location').first().reset_index()
change_rate_indices_by_country3['location'] = change_rate_indices_by_country3['location']
# change_rate_indices_by_country
### Hong Kong is a part of China, so the COVID-19 data considered these two as an ensemble. Hong Kong will take the data of China
change_rate_indices_by_country3.loc[13, 'total_cases_per_million'] = change_rate_indices_by_country3.loc[6, 'total_cases_per_million']
change_rate_indices_by_country3 = change_rate_indices_by_country3.dropna(subset = ['total_cases_per_million'])
change_rate_indices_by_country3['drop_rate_nom'] =change_rate_indices_by_country3['drop_rate'].apply(lambda x: abs(x))
change_rate_indices_by_country3['change_rate_nom'] =change_rate_indices_by_country3['change_rate'].apply(lambda x: abs(x))
### group by continent
# change_rate_indices_by_continent2 = indices_df2.groupby('continent').mean().reset_index()
# change_rate_indices_by_continent2['change_rate_percentage'] =change_rate_indices_by_continent2['change_rate'].apply(lambda x: format(x, '.2%'))
# change_rate_indices_by_continent
change_rate_indices_by_country3.to_csv('change_rate_indices_by_country3.csv')
change_rate_indices_by_country4=pd.read_csv('change_rate_indices_by_country3_vertical.csv')
2020 continues to surprise. A pandemic, the shutdown of the global economy, the deepest recession since the 1930s, a global equity market collapse and now, record highs for the U.S. equity market. We’re still in the early recovery phase of the cycle following the COVID-19 recession. This implies an extended period of low-inflation, low-interest-rate growth—an environment that usually favors equities over bonds. In this project, we will answer the following questions
What does global stock market really look like?
Why are there significant differences of stock development among countries and companies in COVID-19?
How to make investment during corona?
If you are motivated to invest in the stock market but don't know the direction, or you are just curious about the recent stock market situation. Here is the right place for you. Come on! Let's play around and figure it out together! The tip for interactive part are italic. Don't miss the chance!
For an overview worldwide, we gathered the price history of 62 general equity market indices from 43 countries. The chosen indices are recognized as an efficient measurement of the real equity market and be able to help investors establish an overview of national market performance. You may find the price change rate of the national stock index in the map below.
Don't forget to move your mouse over the map to explore more!
Orange = EARNING
Grey = LOSING
SIZE = Change rate to the price on 19 Feb., when COVID-19 was announced as a global pandemic and led to the equity market collapse. We calculated it with this fomular:
countries = alt.topo_feature(data.world_110m.url, 'countries')
map1 = alt.Chart(countries).mark_geoshape(
stroke='white',
fill='#e5e4db'
)
symbols = alt.Chart(change_rate_indices_by_country).mark_circle().encode(
longitude='Longitude (average)',
latitude='Latitude (average)',
size=alt.Size('change_rate_nom:Q'),
tooltip=['location', 'change_rate_percentage'],
color=alt.condition(
alt.datum.change_rate > 0,
alt.value("orange"), # The positive color
alt.value("grey") # The negative color
)
)
map_world = alt.layer(
map1, symbols
).project(
type='mercator',
scale=145,
center=[25,15]
).properties(
title = 'Overview of Stock Markets Development Worldwide',
width = 830,
height = 450
)
map_world
From this chart we can see, only a few contries has recovered from the Covid-19 Crash and most of winners come from Asia. But don't be sad! Have you seen the big orange dots in US, Argentina, New Zealand and Iceland? Some of them are out of surprise, aren't they? Especially the performance of Argentina has benefited its investors with 23% return rate. What a breaking news!
Besides being shoked by these new discoveries, have you ever raised a question: Why do some countries recover so fast? Maybe they dropped less at the very beginning? The crash map below show the maximal drop rate since 19 Feb.
Don't forget to move your mouse!
Size & Color = Maximal drop rate (19 Feb.-19 Mar.)
countries = alt.topo_feature(data.world_110m.url, 'countries')
map3 = alt.Chart(countries).mark_geoshape(
stroke='white',
fill='#e5e4db'
)
symbols3 = alt.Chart(change_rate_indices_by_country3).mark_circle().encode(
longitude='Longitude (average)',
latitude='Latitude (average)',
size=alt.Size('drop_rate_nom:Q',),
tooltip=['location', 'drop_rate_percentage'],
color='drop_rate_nom:Q')
map_world3 = alt.layer(map3, symbols3).project(
type='mercator',
scale=145,
center=[25,15]
).properties(
title = 'Overview of Corona Crash Worldwide',
width = 830,
height = 450
)
map_world3
You may have found, that all countries reacted with a great drop to WHO's announcement. Comlombia's equity market value was even cut off by 60%. And China, which was firstly and seriously hurt by Corona only dropped by 10%. What a difference!
Try to figure it out by yourself !
####################### 纵向1
world_index_hist_vertical_merged = world_index_hist_vertical.merge(change_rate_indices_by_country, on = 'Name')
world_index_hist_vertical_merged = world_index_hist_vertical_merged.loc[:,
['Date',
'Price',
'Name',
'continent',
'total_cases_per_million',
'location',
'Daily Change Rate',
'change_rate_percentage',
'change_rate']]
world_index_hist_vertical_merged['Daily Change Rate_round'] = world_index_hist_vertical_merged['Daily Change Rate'].apply(lambda x: format(x, '.2'))
world_index_hist_vertical_merged['Date'] = pd.to_datetime(world_index_hist_vertical_merged['Date'], format = '%Y-%m-%d')
####################### 纵向2
world_index_hist_vertical_merged3 = world_index_hist_vertical3.merge(change_rate_indices_by_country3, on = 'Name')
world_index_hist_vertical_merged3 = world_index_hist_vertical_merged3.loc[:,
['Date',
'Price',
'Name',
'continent',
'total_cases_per_million',
'location',
'Daily Change Rate',
'change_rate_percentage',
'change_rate']]
world_index_hist_vertical_merged3['Daily Change Rate_round'] = world_index_hist_vertical_merged3['Daily Change Rate'].apply(lambda x: format(x, '.2'))
world_index_hist_vertical_merged3['Date'] = pd.to_datetime(world_index_hist_vertical_merged3['Date'], format = '%Y-%m-%d')
double_bar=alt.Chart(change_rate_indices_by_country4).mark_bar(opacity=0.7).encode(
alt.X('rate_number:Q', title=None),
y=alt.Y('location', title=None,
sort = alt.SortField(field = 'drop_rate_nom', order ='ascending')
),
color="rate_art",
tooltip=['location', 'drop_rate_percentage', 'change_rate_percentage']
).properties(
title = 'Dropped Deeper, Recovers More Slowly?',
width = 400,
height = 600,
)
double_bar
This chart is sorted by descending drop rate. For most of countries, dropped more means recovers more slowly. One outlier is Argentina. It sits 5th from the bottom, dropped most also recovered most. Also for many asian countries, e.g. South Korea, Japan..., they recovers fast even though they were more seriously attacked. Here, we created a wonderful interactive panel for you to discovery:
Click the continent bar to see the difference among countries of this continent. You can also switch to One-Country-View by clicking the country name in the lower bar chart. To the right are the stock growth line of the selected stock markets. Don't forget to hover the mouse to see the date. To clearify the comparison, the price on 19 Feb. was set as benchmark price, and the growth rate of others day was calculated accordingly.
alt.data_transformers.disable_max_rows()
selector = alt.selection_single(empty='all', fields=['continent'])
selector2 = alt.selection_single(empty='all', fields=['location'])
bar_country = alt.Chart(change_rate_indices_by_country).mark_bar().encode(
alt.X('change_rate:Q',
title=None),
# 'Change Rate (19 Feb.- 14 Oct.)'),
alt.Y('location:O',
sort = alt.SortField(field = 'change_rate', order ='descending'),
title=None),
color=alt.Color('total_cases_per_million:Q',scale=alt.Scale(type='sqrt'), legend=None),
tooltip = ['location', 'change_rate_percentage']
).properties(
title = 'Change Rate in Countries',
width = 200,
height = 200,
).transform_filter(
selector
).add_selection(
selector2
)
bar_continent = alt.Chart(change_rate_indices_by_continent).mark_bar().encode(
alt.X('change_rate:Q',
title=None),
alt.Y('continent:O',
sort = alt.SortField(field = 'change_rate', order ='descending'),
title=None),
color=alt.Color(
'total_cases_per_million:Q',
scale=alt.Scale(type='sqrt'),
legend=None),
tooltip = ['continent', 'change_rate_percentage']
).properties(
title = 'Change Rate in Continents',
width = 200,
height = 200,
).add_selection(
selector
)
###########################################
nearest2 = alt.selection(type='single', nearest=True, on='mouseover',
fields=['Date'], empty='none')
# The basic line
line = alt.Chart(world_index_hist_vertical_merged).mark_line(interpolate='basis').encode(
alt.X('Date', title=None),
alt.Y('Daily Change Rate', title='Growth Rate'),
color=alt.Color('location', legend=alt.Legend(columns=2))
).properties(
width=450, height=200
).transform_filter(
selector
)
line2 = alt.Chart(world_index_hist_vertical_merged).mark_line(interpolate='basis').encode(
alt.X('Date', title=None),
alt.Y('Daily Change Rate', title='Growth Rate'),
color=alt.Color('location', legend=alt.Legend(columns=2)),
tooltip = 'Date'
).transform_filter(
selector2
)
selectors2 = alt.Chart(world_index_hist_vertical_merged).mark_point().encode(
x='Date',
opacity=alt.value(0),
).add_selection(
nearest2
)
points2 = line2.mark_point().encode(
opacity=alt.condition(nearest2, alt.value(1), alt.value(0))
)
text2 = line2.mark_text(align='left', dx=10, dy=-20).encode(
text=alt.condition(nearest2, 'Date', alt.value(' '))
)
rules2 = alt.Chart(world_index_hist_vertical_merged).mark_rule(color='gray').encode(
x='Date',
).transform_filter(
nearest2
)
hist2 = alt.layer(
line2, selectors2, points2, rules2, text2
).properties(
width=450, height=200
)
(bar_continent | line) & (bar_country | hist2)
After looking at the development of general equity market indices from 43 countries, you may find some lines similar. Some lay on the ground while some have a sharp decline right after a rapid rebound. Economists classify these recessions patterns into 4 types:
world_index_hist_de = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['Germany'])]
world_index_hist_ar = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['Argentina'])]
world_index_hist_cn = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['China'])]
world_index_hist_sg = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['Singapore'])]
world_index_hist_us = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['United States'])]
world_index_hist_uk = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['United Kingdom'])]
world_index_hist_sw = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['Switzerland'])]
world_index_hist_be = world_index_hist_vertical_merged[world_index_hist_vertical_merged['location'].isin(['Belgium'])]
ar = alt.Chart(world_index_hist_ar).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.55,0.35]), title=None),
).properties(
width=150,
height=100,
title ='Argentina'
)
cn = alt.Chart(world_index_hist_cn).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.55,0.35]), title=None),
).properties(
width=150,
height=100,
title ='China'
)
#######
de = alt.Chart(world_index_hist_de).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
).properties(
width=150,
height=100,
title ='Germany'
)
us = alt.Chart(world_index_hist_us).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
).properties(
width=150,
height=100,
title ='United States'
)
######
sw = alt.Chart(world_index_hist_sw).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
# color='location',
).properties(
width=150,
height=100,
title ='Switzerland'
)
be = alt.Chart(world_index_hist_be).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
# color='location',
).properties(
width=150,
height=100,
title ='Belgium'
)
#########
sg = alt.Chart(world_index_hist_sg).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
# color='location',
).properties(
width=150,
height=100,
title ='Singapore'
)
uk = alt.Chart(world_index_hist_uk).mark_line().encode(
x=alt.X('Date', title=None),
y=alt.Y('Daily Change Rate', scale=alt.Scale(domain=[-0.5,0.15]), title=None),
# color='location',
).properties(
width=150,
height=100,
title ='United Kingdom'
)
w_shape = (cn&ar).properties(title='W-Shaped')
u_shape = (be&sw).properties(title='U-Shaped')
v_shape = (us&de).properties(title='V-Shaped')
l_shape = (sg&uk).properties(title='L-Shaped')
w_shape|u_shape|v_shape|l_shape
The behavior of a country's equity market as a whole follows the four patterns above. Some economist created a term, K-shaped, to describe the market after COVID-19 Crash. K-shaped Recession is one where recession is quick like a V-shaped recession for the socially advantaged, but slow and L-shaped for the socially disadvantaged. Actually, besides the field of industial, to which one company belongs, many other factors could also result in distinct recovery pattern. So from here on, we are going to explore the reasons behind the different market performance in these 3 aspects:
The answer will come out later.
In this part, we considered 6 possible influencing factors for a countries's stock market during COVID-19. As the current situation shows, stringent policies from government do have reduced the infection rate, but they dragged down the economy at the same time. The lack of medical resources has led to a higher infection rates and increasing number of mortality. GDP indicates the past situation of one country's economy. Would these national indicators make an influence on equity market's rebounce?
A country consist of millions of citizens. Aging demographics of one country may make the situation in Corona time harder. The human development index reflects the educated level, life expectancy and the income level of one country's citizens. Will these factors directly or indirectly affect the stock market?
To find out the correlation, we used the price history of 62 general equity market indices from 43 contries and the change rate to 19 Feb. 2020 for this study (calculation formular was shwon in 'Global Level' part). The information of 6 infuencing factors are listed here ⇒ owid-covid. After getting all the data we need, we drawed a set of scatter plots to let the data tell stories.
import pandas as pd
import altair as alt
import numpy as np
world_index_his_1 = pd.read_csv('Data For Project/World Index History.csv',parse_dates=['Date']).rename(columns={'Date':'date'})
world_index_his_2 = pd.read_csv('Data For Project/Manual World Index History.csv',parse_dates=['Date']).drop(columns=['Unnamed: 0']).rename(columns={'Date':'date'}).sort_index(ascending=False)
world_index_info = pd.read_csv('Data For Project/World Index Info.csv').drop(columns='Unnamed: 0').set_index('Name').sort_index()
covid = pd.read_csv('https://covid.ourworldindata.org/data/owid-covid-data.csv',parse_dates=['date'])
start_date = pd.to_datetime('2020.2.19')
end_date = pd.to_datetime('2020.10.15')
countries=[
'China','China','China','Vietnam','Russia','Colombia','Czech Republic','Spain','Greece',
'Italy','Malaysia','Pakistan','Qatar','Thailand','Australia','Australia','Belgium','India',
'United Kindom','Brazil','United States','Kuwait','France','United Kiongdom','Germany',
'United States','Canada','Hong Kong','United States','Indonesia','South Africa','Malaysia',
'Korea','Argentina','Mexico','France','Japan','Morocco','United States','New Zealand',
'Sweden','United States','Singapore','Switzerland','Israel','Taiwan','United States','United States'
]
factors = ['stringency_index','hospital_beds_per_thousand','human_development_index','gdp_per_capita',
'median_age','positive_rate']
def clipe_date(df,start_date,end_date):
data = df[df['date']<=end_date]
return data[data['date']>=start_date]
def get_ChangeRate(df):
df = clipe_date(df,start_date,end_date)
df = df.set_index('date')
df = df.apply(pd.to_numeric,errors='coerce')
growth= df.apply(lambda x: x/x[0]-1)
growth = growth[-1:]
growth = growth.T
growth.columns=['change rate']
return growth
growth1 = get_ChangeRate(world_index_his_1)
growth2 = get_ChangeRate(world_index_his_2)
growth = pd.concat([growth1,growth2]).sort_index()
growth = growth.dropna(axis=0,how='all').T
growth.columns=countries
cov_factors = pd.DataFrame()
dic={}
for country in enumerate(countries):
dic['stringency_index']=covid[covid.location==country[1]].stringency_index.mean()
dic['hospital_beds_per_thousand']=covid[covid.location==country[1]].hospital_beds_per_thousand.mean()
dic['human_development_index']=covid[covid.location==country[1]].human_development_index.mean()
dic['gdp_per_capita']=covid[covid.location==country[1]].gdp_per_capita.mean()
dic['median_age']=covid[covid.location==country[1]].median_age.mean()
dic['positive_rate']=covid[covid.location==country[1]].positive_rate.mean()
dic=pd.DataFrame(dic,index=[0])
cov_factors = cov_factors.append(dic)
cov_factors.index=countries
multiples_1 = pd.concat([cov_factors,growth.T],axis=1)
multiples_1['country']=multiples_1.index
zoom in to see the details near zero point.
def plot_scatter_chart(source,factor):
selection = alt.selection_multi(fields=['country'], bind='legend')
Map=alt.Chart(multiples_1).mark_circle().encode(
x=factor,
y=alt.Y('change rate',title='Change rate'),
color=alt.Color('country',legend=alt.Legend(columns=2)),
tooltip = ['country','change rate'],
opacity=alt.condition(selection, alt.value(1), alt.value(0.2))
).add_selection(selection).properties(width=200,height=150).interactive()
line = alt.Chart(pd.DataFrame({'y': [0]})).mark_rule().encode(y='y')
return Map+line
maps = []
for factor in enumerate(factors):
maps.append(plot_scatter_chart(multiples_1,factor[1]))
plot = None
for row in np.array_split(np.array(maps),2):
row_plot = row[0]
for col in row[1:]:
row_plot |= col
if plot:
plot &= row_plot
else:
plot = row_plot
plot
It seems likes strict policies dragged back the economy but rebounced the equity market. Timely and efficient policy boost investors' confidence. In the chart of 'GDP Per Capita', you may have noticed 2 outliers China and Argentina. They have relative low GDP Per Capita, but rebounds as TOP 3 in the whole world! But this question is further beyond our knowledge, hope economists will answer it for us. XD
Until now, we have found out 3 criteria to select the investment country. But we are still confused about the criteria for choosing good companies there. Maybe we should start the company research? Oh, Wait! Before getting deeper into it, we still have one much easier index to explore. That is industry sectors.
As you may have noticed from the scatter plots above, the U.S. stock market is always an outlier. It keeps earning money while most of the others are losing a lot.
Especially when the U.S. starts to lose control of Corona virus. Let's work together to reveal the secrets behind the mirage!
In this part, we used the S&P 500 Index for 11 sectors to explore the U.S. equity market. As a comparison we introduced the MSCI Europe Index, which is published with the aim of bringing greater transparency to financial markets. You may ask, why did we choose these two samples, but not China, not South Africa or so. The reason lies in the facts: Europe and the U.S. have lots of things in common, including economic system, the severity of the epidemic, country developed level etc. By choosing them, we can preclude interference by other influencing factors.
A new question for this part!
import os
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
# Get the data
data = pd.read_csv('usa11sector.csv')
data = data.rename(columns = {'Price ': 'Price'})
# data['Price'] = pd.to_numeric(data['Price'], errors='1coerce') #### convert string column to numeric
arr_date = data['Date'].unique()
list_dates_price = []
for i in range(len(arr_date)):
# i = 0
date = arr_date[i]
price = data[data['Date'] == date]
price = price[['Sector', 'Price']].sort_values(by = ['Sector'])
list_dates_price.append(price)
width = 0.35
plt.figure(figsize=[20, 5])
# for i in range(len(list_dates_price)):
df1 = list_dates_price[0]
x = df1['Sector']
y1 = tuple(df1['Price'])
N = len(df1)
ind = np.arange(N)
df2 = list_dates_price[1]
# x2= df2['Sector']
y2 = tuple(df2['Price'])
plt.bar(ind, y1, width, label = arr_date[0])
plt.bar(ind + width, y2, width, label = arr_date[1])
plt.xlabel('Sector of USA', fontsize=20)
plt.ylabel('Price', fontsize=20)
plt.title('Price Comparison across Sectors', fontsize = 30)
plt.xticks(ind - width / 4, tuple(x))
plt.tick_params(labelsize=14, rotation=20)
plt.legend(arr_date, loc='upper left')
plt.show()
import os
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
# Get the data
data = pd.read_csv('eu11sector.csv')
data = data.rename(columns = {'Price ': 'Price'})
# data['Price'] = pd.to_numeric(data['Price'], errors='1coerce') #### convert string column to numeric
arr_date = data['Date'].unique()
list_dates_price = []
for i in range(len(arr_date)):
# i = 0
date = arr_date[i]
price = data[data['Date'] == date]
price = price[['Sector', 'Price']].sort_values(by = ['Sector'])
list_dates_price.append(price)
width = 0.35
plt.figure(figsize=[20, 5])
# for i in range(len(list_dates_price)):
df1 = list_dates_price[0]
x = df1['Sector']
y1 = tuple(df1['Price'])
N = len(df1)
ind = np.arange(N)
df2 = list_dates_price[1]
# x2= df2['Sector']
y2 = tuple(df2['Price'])
plt.bar(ind, y1, width, label = arr_date[0])
plt.bar(ind + width, y2, width, label = arr_date[1])
plt.xlabel('Sector of Europe', fontsize=20)
plt.ylabel('Price', fontsize=20)
# plt.title('Price Comparison across Sectors', fontsize = 30)
plt.xticks(ind - width / 4, tuple(x))
plt.tick_params(labelsize=14, rotation=20)
plt.legend(arr_date, loc='upper left')
plt.show()
As bar charts show, all industry sectors of European stock market are losing money. Maybe you had foreseen this result before, as almost all european stock markets were beaten to the ground and are suffering a long tough. Bad news here!
But the situation in the U.S. is quite different. First of all, several sectors (Consumer Discretionary, Health Care, IT) are growing, which has a large market share. IT sector, in particular, is not only the most price-representative sector, but also the one with the highest growth (almost 15% in 8 months!). However, the performance of other sectors like Energy and Financials are not unsatisfactory. The decline in these sectors is considerably, but due to the small market share, they have very limited impact on the general stock market index, which is always regarded as one of the most important stock market indicators.
Small tips: The unsatisfactory performance of Energy and Financials sectors does not mean they won't rebounce strongly later. Quite the opposit, the stocks with a lower price have much more space to grow. But wait, not all companies worth an investment! Even some counpanies from American IT sector might be a dangerous bomb. A new question raise up, here we go!
To answeranswer this question, we analysed 500 american companies from S&P 500 and 300 Chinese companies from the CSI 300 Index. And we discovered the correlation of a company's stock price change rate and these 4 key financial and operating information:
zoom in to see the details.
S_P_info = pd.read_csv('Data For Project/S_P500 Company Info.csv')
S_P_his = pd.read_csv('Data For Project/S_P500 Company History.csv',parse_dates=['Date'])
DAX_info = pd.read_csv('Data For Project/DAX30 Company Info.csv')
DAX_his = pd.read_csv('Data For Project/DAX30 Company History.csv',parse_dates=['Date'])
CSI_info = pd.read_csv('Data For Project/CSI300 Company Info.csv')
CSI_his = pd.read_csv('Data For Project/CSI300 Company History.csv',parse_dates=['Date'])
def get_growth(df):
df=df.rename(columns={'Date':'date'})
df = clipe_date(df,start_date,end_date)
df = df.set_index('date')
growth= df.apply(lambda x: x/x[0]-1)
growth = growth[-1:]
growth = growth.T
growth.columns=['change rate']
growth.sort_index(inplace=True)
return growth
S_P_growth = get_growth(S_P_his)
DAX_growth = get_growth(DAX_his)
CSI_growth= get_growth(CSI_his)
def sort_merge_info(df,df2):
df=df.set_index('Name')
df.sort_index(inplace=True)
return df.join(df2)
S_P_info=sort_merge_info(S_P_info,S_P_growth)
DAX_info=sort_merge_info(DAX_info,DAX_growth)
CSI_info=sort_merge_info(CSI_info,CSI_growth)
S_P_info.head(2)
factors_country = ['FullTimeEmployees','EnterpriseValue','EarningsQuarterlyGrowth','ProfitMargins']
def plot_Scatter_countries(source,factor):
Map=alt.Chart(source).mark_circle().encode(
x=factor,
y=alt.Y('change rate',title='change rate'),
color=alt.Color('LongName',title='company',legend=alt.Legend(columns=2)),
tooltip = ['LongName','change rate']
).properties(
width=200,
height=150
).interactive()
line=line = alt.Chart(pd.DataFrame({'y': [0]})).mark_rule().encode(y='y')
return Map+line
maps = []
for factor in enumerate(factors_country):
maps.append(plot_Scatter_countries(S_P_info,factor[1]))
maps.append(plot_Scatter_countries(CSI_info,factor[1]))
# maps.append(plot_Scatter_countries(DAX_info,factor[1]))
plot = None
for row in np.array_split(np.array(maps),4):
row_plot = row[0]
for col in row[1:]:
row_plot |= col
if plot:
plot &= row_plot
else:
plot = row_plot
plot
The scatter chart don't disappoint us: horizontally, the charts made by 500 U.S. companies forms a triangle with a corner pointing to the right. It is almost equally divided above and below the zero-line, which means profitable and unprofitable companies are half-half. Let's have a quick glance at the second column where 300 Chinese companies are plotted. All points form a L-shape, with the vast majority above the zero-line. A Completely different distribution, isn't it?
* | United States | China | |
---|---|---|---|
Shape | Triangle | L-Shape | |
Distribution | Half-Half | Most above |
This tells that the growth of Chinese stock market is not only driven by large companies, but by companies of different size, profit margins. They contribute their growth a lot to the national overall index.
Vertically, the chart of Enterprice Value caught our attention. While the vast majority of company stocks are having an uncertain future in terms of gaining or losing, several large companies have grown a lot. Most of them are IT Gaints: Apple, Microsoft, Amazon, Facebook, etc. As COVID-19 will compare us for years, we have no reason to doubt that the stock price of these IT Gaints will go up further.
Wait, there's one more thing to discuss! In the chart of Full Time Employees we can see a great difference from China and the States. The big Chinese companies with more full time employees are mostly under the zero-line, while big companies in the States are gaining! It maybe because China has more Labor-intensive industries. The points shown in this chart to be having most employees are mostly Labor-intensive (Chemicals manufacturing, auto) and socially disadvantaged companies (Insurance). Besides them, many banks from Financials Sector are also included.
OMG, investment is so complicated. But we had made it easier with data visualisation! Now we can answer the last question with confidence!
After analysing the market performance in 3 aspects, we can drawn some surprising conclusions:
The uncertainty during COVID-19 pandemic has made investors hesitant. The turmoil in the stock market will eventually pass. What we need to overcome is just the panic when we face the unknown.
Here we provid you with a list of TOP 15 profitable companies and unprofitable companies. You may find your favorite egg here. Come! Find it!
#Import libraries
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import altair as alt
#The data of top companies profit of years 2019 and 2020 of the world by Fortune was collected from the source given below.
#https://www.someka.net/excel-template/fortune-global-500-list/
df_2019 = pd.read_csv('global-2019.csv')
dfff1 = df_2019.set_index('Company Name')
df_2020 = pd.read_csv('global-2020.csv')
dfff = df_2020.set_index('Company Name')
# We were interested in getting to know the profit figures of companies of USA, Germany and China in the year 2020 and wanted
# to compare with with year 2019.
# companies from year 2019 and 2020 were chosen randomly from USA, Germany and China as profit gainer.
df_19 =dfff1.loc[['BASF','Deutsche Telekom','Walmart','Berkshire Hathaway','Amazon.com','UnitedHealth Group', 'McKesson',
'AmerisourceBergen','Sinopec Group','Verizon Communications','Ping An Insurance',
'Alibaba Group Holding','Pfizer','Merck','AIA Group'],:]
df_19.reset_index()
df_20 =dfff.loc[['BASF','Deutsche Telekom','Walmart','Berkshire Hathaway','Amazon.com','UnitedHealth Group', 'McKesson',
'AmerisourceBergen','Sinopec Group','Verizon Communications','Ping An Insurance',
'Alibaba Group Holding','Pfizer','Merck','AIA Group'],:]
# Plotting br chart of companies profit (2019)
y19=alt.Chart(df_19.reset_index()).mark_bar().encode(
y='Profits-2019(mUSD):Q',
x='Company Name',
color ='Company Name',tooltip=['Company Name','Profits-2019(mUSD):Q']
).properties(width=300, height= 400 ,title='Bar chart of companies profit (2019)' ).interactive()
# Plotting br chart of companies profit (2020)
y20 =alt.Chart(df_20.reset_index()).mark_bar().encode(
y='Profits-2020 (mUSD):Q',
x='Company Name',
color ='Company Name',tooltip=['Company Name', 'Profits-2020 (mUSD):Q']
).properties(width=300, height= 400 ,title='Bar chart of companies profit (2020)' ).interactive()
y19 | y20
# companies from year 2019 and 2020 were chosen randomly from USA, Germany and China as a profit losser.
df_19_Loser=dfff1.loc[['Daimler', 'BMW Group','Siemens','Bosch Group','Lufthansa Group','SAP','Exxon Mobil',
'AT&T','SAIC Motor','Fannie Mae','Chevron','General Motors','Wells Fargo','PepsiCo'],:]
df_20_Loser =dfff.loc[['Daimler', 'BMW Group','Siemens','Bosch Group','Lufthansa Group','SAP','Exxon Mobil',
'AT&T','SAIC Motor','Fannie Mae','Chevron','General Motors','Wells Fargo','PepsiCo'],:]
#Plotting bar chart
y19_loser=alt.Chart(df_19_Loser.reset_index()).mark_bar().encode(
y='Profits-2019(mUSD):Q',
x='Company Name',
color ='Company Name',tooltip=['Company Name','Profits-2019(mUSD):Q']
).properties( width=300, height= 400 ,title='Bar chart of companies profit (2019)').interactive()
#Plotting bar chart
y20_Loser =alt.Chart(df_20_Loser.reset_index()).mark_bar().encode(
y='Profits-2020 (mUSD):Q',
x='Company Name',
color ='Company Name',tooltip=['Company Name', 'Profits-2020 (mUSD):Q']
).properties( width=300, height= 400 ,title='Bar chart of companies profit (2020)' ).interactive()
y19_loser | y20_Loser