Tuesday, May 12, 2020

How Finance/Programmer Bloggers can work with each other.

Recently, a really nice blog article caught my eye. The Boy who Procrastinates has written an article where he shared some of his Python source code where he wrote about the "Sell in May" phenomenon in Singapore.

You can access his article here.

The beauty about articles like this is we can learn from his code fragments and build on them further to add more value to our readers. So I took a chunk of his code and spent an entire morning modifying it to accept a list of stock indices as input and compiled average the returns of the indices over a history of 20 years. You will find a nifty use of Python Pivot Tables in my solution.


The worst STI month I teach my students is never May but in August as it falls on a Hungry Ghost Month. This is probably the best month to get some bargain hunting done. It is just interesting to see that this is true based on 20 years of stock market history.

Surprisingly, for the Dow Jones, worst gains come September. As my code can accept any index, I interposed results on the VIX index and the largest increase in volatility happens to be August as well.

( I'm sure a reader might know why options volatility would grow in that month! Please enlighten me !)

Before these insights are compiled to future course materials, I naturally would need to put in in a nice chart but at least the data frame containing all the pertinent information can be generated by my program.

After today's exercise, I suspect that financial/programming bloggers in Singapore may be able to cooperate with each other by picking up useful fragments of each other's code. Once I finish my classes this week, I may want to explore how we can work closer together, maybe even incorporate a company together to solve financial problems from various institutions. 

For engineers who wish to improve upon my works, my source code is as follows:

from pandas_datareader import data as pdr
import pandas as pd
import yfinance as yf
import numpy as np

yf.pdr_override()

# Indicate start and end date
start = '2000-01-01'
end   = '2020-01-01'
months = ["January","February","March","April","May","June","July","August","September","October","November","December"]
stock_counter = ['^STI','^DJI','^VIX']


# Create the base dataframe called 'dummy' to contain all monthly data.
dummy = pdr.get_data_yahoo('^DJI', start, end)
dummy = dummy.groupby(pd.Grouper(freq='M')).tail(1)
dummy = dummy.drop(['High','Low','Open','Close','Volume',"Adj Close"],axis=1)

# Generate stock price data and put it in dummy dataframe
for counter in stock_counter:
  stock_data = pdr.get_data_yahoo(counter, start, end)
  monthly_stock_data = stock_data.groupby(pd.Grouper(freq='M')).tail(1)
  monthly_stock_data = monthly_stock_data.drop(['High','Low','Open','Close','Volume'],axis=1)
  dummy[counter] = monthly_stock_data["Adj Close"]

#print(dummy)

# Generate monthly returns from raw index levels
monthly_returns = round(dummy.pct_change()*100,2)
monthly_returns['Month'] = pd.DatetimeIndex(monthly_returns.index).month
#print(monthly_returns)

# Use a pivot table to average up all monthly returns
table = pd.pivot_table(monthly_returns,index=["Month"],aggfunc=np.mean)
table["Month Names"] = months
table = table.set_index('Month Names')
print(f"\n\n***** Monthly return comparisons *****")
print(f"Counters compared are :{stock_counter}")
print(f"From {start} To {end}")
print("**************************************")

print(table)



1 comment:

  1. Wow, didn't know theres a get_data_yahoo() method! I've always been downloading to csv file

    ReplyDelete