Pandas Equity Market

convert_daily_to_monthly.py

################################################################################################
#	name:	convert_daily_to_monthly.py
#	desc:	takes inout as daily prices and convert into monthly data
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
import numpy as np

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Getting month number
df['Month_Number'] = df['Date'].dt.month
# Getting year. month is common across years (as if you dont know :) )to we need to create unique index by using year and month
df['Year'] = df['Date'].dt.year

# Grouping based on required values
df2 = df.groupby(['Year','Month_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})
# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})
df2.to_csv('Monthly_OHLC.csv')
print('*** Program ended ***')

convert_daily_to_weekly.py

################################################################################################
#	name:	convert_daily_to_weekly.py
#	desc:	takes inout as daily prices and convert into weekly data
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
import numpy as np

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Getting week number
df['Week_Number'] = df['Date'].dt.week
# Getting year. Weeknum is common across years to we need to create unique index by using year and weeknum
df['Year'] = df['Date'].dt.year

# Grouping based on required values
df2 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})
# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})
df2.to_csv('Weekly_OHLC.csv')
print('*** Program ended ***')

pandas_create_columns.py

################################################################################################
#	name:	create_column_from_other_columns.py
#	desc:	Create new columns in pandas DataFrame
#	date:	2018-06-16
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
import numpy as np
# import pandas_datareader as datareader
import matplotlib.pyplot as plt
import datetime
from matplotlib.finance import candlestick_ohlc
# from mpl_finance import candlestick_ohlc
import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
# print(df.dtypes)
# print(df.head())

#### Normail column creation
df['range'] = df['High Price'] - df['Low Price']
df['Average'] = (df['Close Price'] + df['Open Price'])/2

#### Conditional Value
df['GT1400'] = np.where( (df['Close Price']> 1400), 1, 0)
df['DailyTrend'] = np.where( (df['Close Price']>= df['Open Price']), 'Positive', 'Negative')
df['Calculated Column'] = np.where( (df['Close Price']>= df['Open Price']), df['Close Price']- df['Open Price'], (df['Close Price'] + df['Open Price'])/2)

df.to_csv('hdfc_with_calculated_columns.csv')
print('*** Program ended ***')

simple2.py

################################################################################################
#	name:	timeseries_OHLC.py
#	desc:	creates OHLC graph
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
# import pandas_datareader as datareader
import matplotlib.pyplot as plt
import datetime
from matplotlib.finance import candlestick_ohlc
# from mpl_finance import candlestick_ohlc
import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
df["Date"] = df["Date"].apply(mdates.date2num)

# Creating required data in new DataFrame OHLC
ohlc= df[['Date', 'Open Price', 'High Price', 'Low Price','Close Price']].copy()
# In case you want to check for shorter timespan
# ohlc =ohlc.tail(60)

f1, ax = plt.subplots(figsize = (10,5))

# plot the candlesticks
candlestick_ohlc(ax, ohlc.values, width=.6, colorup='green', colordown='red')
ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))
# Saving image
plt.savefig('OHLC HDFC.png')

# In case you dont want to save image but just displya it
#plt.show()

print('*** Program ended ***')

simple3.py

################################################################################################
#	name:	timeseries_OHLC_with_SMA.py
#	desc:	creates OHLC graph with overlay of simple moving averages
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
# import pandas_datareader as datareader
import matplotlib.pyplot as plt
import datetime
from matplotlib.finance import candlestick_ohlc
# from mpl_finance import candlestick_ohlc
import matplotlib.dates as mdates

print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
df["Date"] = df["Date"].apply(mdates.date2num)

# Creating required data in new DataFrame OHLC
ohlc= df[['Date', 'Open Price', 'High Price', 'Low Price','Close Price']].copy()
# In case you want to check for shorter timespan
# ohlc =ohlc.tail(60)
ohlc['SMA50'] = ohlc["Close Price"].rolling(50).mean()


f1, ax = plt.subplots(figsize = (10,5))

# plot the candlesticks
candlestick_ohlc(ax, ohlc.values, width=.6, colorup='green', colordown='red')
ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))

# Creating SMA columns
ohlc['SMA5'] = ohlc["Close Price"].rolling(5).mean()
ohlc['SMA10'] = ohlc["Close Price"].rolling(10).mean()
ohlc['SMA20'] = ohlc["Close Price"].rolling(20).mean()
ohlc['SMA50'] = ohlc["Close Price"].rolling(50).mean()
ohlc['SMA100'] = ohlc["Close Price"].rolling(100).mean()
ohlc['SMA200'] = ohlc["Close Price"].rolling(200).mean()

#Plotting SMA columns
# ax.plot(ohlc['Date'], ohlc['SMA5'], color = 'blue', label = 'SMA5')
# ax.plot(ohlc['Date'], ohlc['SMA10'], color = 'blue', label = 'SMA10')
# ax.plot(ohlc['Date'], ohlc['SMA20'], color = 'blue', label = 'SMA20')
ax.plot(ohlc['Date'], ohlc['SMA50'], color = 'green', label = 'SMA50')
# ax.plot(ohlc.index, df['SMA100'], color = 'blue', label = 'SMA100')
ax.plot(ohlc['Date'], ohlc['SMA200'], color = 'blue', label = 'SMA200')

# Saving image
plt.savefig('OHLC with SMA HDFC.png')

# In case you dont want to save image but just displya it

# ohlc.to_csv('ohlc.csv')
# plt.show()
print('*** Program ended ***')

timeseries_simple.py

################################################################################################
#	name:	timeseries_simple.py
#	desc:	Plots line chart using 'Close Price' 
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt


print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])

# Using matplotlib to add required columns
plt.plot(df['Date'], df['Close Price'])

# Adding labels
plt.xlabel('Date')  
plt.ylabel('Close Price')  
plt.title('Simple time series plot for HDFC') 

# Saving image
plt.savefig('Simple time series plot for HDFC.png')

# In case you dont want to save image but just displya it
#plt.show()

print('*** Program ended ***')

timeseries_simple_with_pointer.py

################################################################################################
#	name:	timeseries_simple_with_pointer.py
#	desc:	Plots buy and sell signal on line chart
#	date:	2018-06-15
#	Author:	conquistadorjd
################################################################################################
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt


print('*** Program Started ***')

df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')

# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']

# Converting date to pandas datetime format
df['Date'] 	= pd.to_datetime(df['Date'])
df['SMA5'] 	= df["Close Price"].rolling(20).mean()
df['SMA20'] = df["Close Price"].rolling(100).mean()

#Using matplotlib to add required columns
plt.plot(df['Date'], df['Close Price'],linewidth=0.5,color='black')
plt.plot(df['Date'], df['SMA5'],linewidth=0.5,color='blue')
plt.plot(df['Date'], df['SMA20'],linewidth=0.5,color='c')

df['SMA5']		=df['SMA5'].fillna(0)
df['SMA20']		=df['SMA20'].fillna(0)

#Identifying the buy/sell zone
df['Buy'] = np.where( (df['SMA5']> df['SMA20']), 1, 0)
df['Sell'] = np.where( (df['SMA5']< df['SMA20']), 1, 0)

##identify buy sell signal
df['Buy_ind'] = np.where( (df['Buy'] > df['Buy'].shift(1)),1,0)
df['Sell_ind'] = np.where( (df['Sell'] > df['Sell'].shift(1)),1,0)
# print(df.dtypes)
# print(df.head(20))

## plotting the buy and sellsignals on graph
plt.scatter(df.loc[df['Buy_ind'] ==1 , 'Date'].values,df.loc[df['Buy_ind'] ==1, 'Close Price'].values, label='skitscat', color='green', s=25, marker="^")
plt.scatter(df.loc[df['Sell_ind'] ==1 , 'Date'].values,df.loc[df['Sell_ind'] ==1, 'Close Price'].values, label='skitscat', color='red', s=25, marker="v")

## Adding labels
plt.xlabel('Date')  
plt.ylabel('Close Price')  
plt.title('HDFC stock price with buy and sell signal') 

# Saving image
plt.savefig('HDFC with SMA 20-100 Buy sell.png')

# In case you dont want to save image but just displya it
# plt.show()
# df.to_csv('temp_hdfc.csv')
print('*** Program ended ***')

@helxsz in case it's of interest: https://raw.githubusercontent.com/conquistadorjd/python-03-matplotlib/master/15-06-2016-TO-14-06-2018HDFCBANKALLN.csv