In [1]:
import pandas as pd
import altair as alt
from vega_datasets import data
In [2]:
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))
Out[2]:
In [3]:
from IPython.display import display, HTML

CSS = """
.output {
    align-items: center;
}
"""

HTML('<style>{}</style>'.format(CSS))
Out[3]:
In [4]:
### 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
In [5]:
### 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')
In [6]:
change_rate_indices_by_country4=pd.read_csv('change_rate_indices_by_country3_vertical.csv')

The Impact of COVID-19 on Stock Market

Zheng Yao, Yuxin Liu, Jaleed Aslam, Jiale Cheng

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!

Global Level: Among continents, among countries

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:

$ \frac{Price(14. Oct.) - Price(19. Feb.)}{Price(19. Feb.)} $

In [7]:
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
Out[7]:

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.)

In [8]:
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
Out[8]:

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!

Question 1: Is there correlation between max. drop rate and final change rate?

Try to figure it out by yourself !

In [9]:
####################### 纵向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
Out[9]:

Answer: YES!

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:

  • the difference among continents
  • the difference among countries in one continent
  • the whole development of the growth line

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.

In [10]:
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)
Out[10]: