def main(stockid, sdate, edate, stick):
# Make DB access to retrive data
# Table StockExchang has following colums in roder:
# 2@margin_buy, 3@margin_sell, 4@margin_remain,
# 5@short_sale_buy, 6@short_sale_sell, 7@short_sale_remain
# 8@TotalVolume, 9@ChargeOff, 10@Category, 11@Date
#
db = add_db_record.ConnectDB("localhost", "stock", "ftdi1234")
cursor = db.cursor()
cursor.execute( \
"SELECT * FROM `MarginTrading` WHERE CoId = (SELECT CoId from Company WHERE StockID = %s) \
AND Date BETWEEN %s AND %s ORDER BY Date ASC" , (stockid, sdate, edate,))
data = cursor.fetchall()
if (cursor.rowcount <= 0):
print("StockExchange_plot: Error: No DATA " + str(cursor.rowcount))
return (-1)
print(data)
df = pd.DataFrame(index=pd.date_range(sdate, edate))
#df['Date'] = [mdates.date2num(parser.parse(str(t))) for t in df['Date']]
df['Date'] = df.index.tolist()
df['Date'] = pd.to_datetime(df['Date'])
df["Date"] = df["Date"].apply(mdates.date2num)
df['Margin Buy'] = [0] * df.shape[0]
df['Margin Sell'] = [0] * df.shape[0]
df['Margin Balance'] = [0] * df.shape[0]
df['Stock Short Buy'] = [0] * df.shape[0]
df['Stock Short Sell'] = [0] * df.shape[0]
df['Stock Short Balance'] = [0] * df.shape[0]
# Date is the index, using date to copy values
for row in data:
df.loc[row[11], 'Margin Buy'] = row[2]
df.loc[row[11], 'Margin Sell'] = row[3]
df.loc[row[11], 'Margin Balance'] = row[4]
df.loc[row[11], 'Stock Short Buy'] = row[5]
df.loc[row[11], 'Stock Short Sell'] = row[6]
df.loc[row[11], 'Stock Short Balance'] = row[7]
plot_df = df[[
'Date', 'Margin Buy', 'Margin Sell', 'Margin Balance',
'Stock Short Buy', 'Stock Short Sell', 'Stock Short Balance'
]].copy()
# 券資比 = (融券餘額 / 融資餘額) * 100%
plot_df['Balance Ratio'] = (plot_df['Stock Short Balance'] /
plot_df['Margin Balance']) * 100
# Fill 0 instead of NaN
plot_df['Balance Ratio'].fillna(value=0, inplace=True)
#print(df.groupby(df.index.strftime("%m%d")).sum())
# Group by Year and Month
# print(df.groupby(df.index.strftime("%y%m")).sum())
# Group by the numeric day of the year
# print(df.groupby(df.index.strftime("%j")).mean())
# print(df.groupby(df.index.strftime("%m%d")).mean())
cursor.execute("SELECT CompanyName FROM Company WHERE StockID = %s",
(stockid, ))
row = cursor.fetchall()
if (cursor.rowcount <= 0):
print("StockExchange_plot: Error: Cannot find Company Name " +
str(cursor.rowcount))
figTitle = u'股票交易資訊' + " STOCK: [" + stockid + "]"
else:
figTitle = u'股票交易資訊' + " " + row[0][0] + ": [" + stockid + "]"
# Construct all the plots
ax = []
fig, ax = plt.subplots(4,
1,
sharex=True,
gridspec_kw={'height_ratios': [1, 1, 1, 1]},
figsize=(12, 10))
#fig, (ax1, ax2, ax3, ax4, ax5, ax6) = plt.subplots(6, 1, sharex=True, gridspec_kw={'height_ratios': [1,1,1,1,1,1]}, figsize = (12,10))
fig.autofmt_xdate(rotation=60)
# Tick on mondays every week, if monthly, then use "mdates.MonthLocator()"
ax[0].xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=MO))
ax[0].xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax[0].xaxis.set_minor_locator(mdates.DayLocator())
for ap in ax:
ap.set_ylabel('Volume', size=10)
ap.xaxis.grid(False)
# Setup font manager to display Chinese characters
fontP = FontProperties(
fname="/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc")
ax[0].plot(plot_df["Date"],
plot_df["Margin Buy"],
color='b',
label="Margin Buy(" + u'融資買入' + ")")
ax[0].plot(plot_df["Date"],
plot_df["Margin Sell"],
color='m',
label="Margin Sell(" + u'融資賣出)' + ")")
ax[2].plot(plot_df["Date"],
plot_df["Margin Balance"],
color='r',
label="Margin Balance(" + u'融資餘額)' + ")")
ax[1].plot(plot_df["Date"],
plot_df["Stock Short Buy"],
color='k',
label="Stock Short Buy(" + u'融卷買入)' + ")")
ax[1].plot(plot_df["Date"],
plot_df["Stock Short Sell"],
color='y',
label="Stock Short Sell(" + u'融卷賣出)' + ")")
ax[2].plot(plot_df["Date"],
plot_df["Stock Short Balance"],
color='g',
label="Stock Short Balance(" + u'融卷餘額)' + ")")
ax[3].plot(plot_df["Date"],
plot_df["Balance Ratio"],
color='b',
label="Stock Short Balance(" + u'券資比)' + ")")
# Show legend, only be done after above "label" is configured
for ap in ax:
ap.legend(loc='best',
fancybox=True,
framealpha=1,
shadow=True,
borderpad=1,
prop=fontP)
fig.suptitle(figTitle, fontproperties=fontP)
plt.axis('tight')
plt.show()
def auto_query_cnt(path):
isFolderChecked = False
isDBChecked = False
# search for the last saved file
fileList = glob.glob(os.path.join(path, "*.csv"))
if (len(fileList) > 0):
print("auto_query_cnt: Directory is NOT empty")
isFolderChecked = True
latest_file = max(fileList, key=os.path.getctime)
print(latest_file)
# Extract file name from full path string
file_name = os.path.basename(latest_file)
fname, fext = os.path.splitext(file_name)
# Gets the date from the file and start from the next till current date.
# The file's name is YYYYMMDD_[File Name].csv
fdate = fname.split("_")[0]
startDate = date(int(fdate[:4]), int(fdate[4:6]), int(fdate[6:8]) + 1)
endDate = datetime.datetime.today().date()
do_Crawl(startDate, endDate, path)
else:
print("auto_query_cnt: Directory is empty::" + path)
print("auto_query_cnt: Start checking latest record in DB")
# This is to check if the table name exists before connecting to the DB
fn = hasFunction(path)
if (fn == -1):
print(
"auto_query result (ERROR): No matching function for the table"
)
else:
try:
add_db_record.ConnectDB("localhost", "stock", "ftdi1234")
table_date = add_db_record.getLatestDate(table_dict[fn])
print("auto_query_cnt: found table dates are::")
print(table_date)
# Check if record exists
if (table_date is None):
print("auto_query result (ERROR): no records found")
else:
isDBChecked = True
# The date stored in the DB has the format of YYYY-MM-DD
tdate = table_date.split("-")
startDate = date(int(tdate[0]), int(tdate[1]),
int(tdate[2]) + 1)
endDate = datetime.datetime.today().date()
do_Crawl(startDate, endDate, path)
except Exception as e:
print("auto_query result (ERROR): Exception")
print(e)
print("auto_query_cnt: Check if to run init process")
# If folder is empty and DB is also empty, then run the init
# process, that is date range is 5 years before current executing date
# till now
if ((isFolderChecked == False) and (isDBChecked == False)):
start_date = date(datetime.datetime.today().year - 5, 1, 1)
end_date = datetime.datetime.today().date()
print("auto_query_cnt: Start running init process")
do_Crawl(start_date, end_date, path)
else:
print("auto_query result: Not running init process")
def main(stockid, sdate, edate, opt):
# Make DB access to retrive data
# Table StockExchang has following colums in roder:
# 0@ExcId, 1@CoId, 2@ExchangVolume, 3@StartPrice
# 4@HighPrice, 5@LowPrice, 6@EndPrice, 7@Category, 8@Date
db = add_db_record.ConnectDB("localhost", "stock", "ftdi1234")
cursor = db.cursor()
cursor.execute( \
"SELECT * FROM `StockExchange` WHERE CoId = (SELECT CoId from Company WHERE StockID = %s) \
AND Date BETWEEN %s AND %s ORDER BY Date ASC", (stockid, sdate, edate,))
data = cursor.fetchall()
if(cursor.rowcount <= 0):
print("StockExchange_plot: Error: No DATA " + str(cursor.rowcount))
return(-1)
# d = pandas.date_range(start='1/1/1980', end='11/1/1990', freq='MS')
# freq: [M] month end frequency, [MS] month start frequency
# [A, Y] year end frequency, [AS, YS] year start frequency
ohlc = pd.DataFrame(index=pd.date_range(sdate, edate))
#ohlc['Date'] = [mdates.date2num(parser.parse(str(t))) for t in ohlc['Date']]
ohlc['Date'] = ohlc.index.tolist()
ohlc['Date'] = pd.to_datetime(ohlc['Date'])
ohlc["Date"] = ohlc["Date"].apply(mdates.date2num)
ohlc['Open Price'] = [0] * ohlc.shape[0]
ohlc['Close Price'] = [0] * ohlc.shape[0]
ohlc['High Price'] = [0] * ohlc.shape[0]
ohlc['Low Price'] = [0] * ohlc.shape[0]
ohlc['Volume'] = [0] * ohlc.shape[0]
# Date is the index, using date to copy values
for row in data:
ohlc.loc[row[8], 'Open Price'] = row[3]
ohlc.loc[row[8], 'Close Price'] = row[6]
ohlc.loc[row[8], 'High Price'] = row[4]
ohlc.loc[row[8], 'Low Price'] = row[5]
ohlc.loc[row[8], 'Volume'] = row[2]
print(ohlc)
df = ohlc[['Date', 'Open Price', 'High Price', 'Low Price','Close Price']].copy()
df_bar = ohlc[['Volume']].copy()
#print(df.groupby(df.index.strftime("%m%d")).sum())
# Group by Year and Month
# print(df.groupby(df.index.strftime("%y%m")).sum())
# Group by the numeric day of the year
# print(df.groupby(df.index.strftime("%j")).mean())
# print(df.groupby(df.index.strftime("%m%d")).mean())
#f1, ax = plt.subplots(figsize = (10,5))
#fig = plt.figure()
#ax = fig.add_subplot(1,1,1)
fig, (ax1, ax2) = plt.subplots(2, 1, sharex=True, gridspec_kw={'height_ratios': [3,1]})
fig.autofmt_xdate(rotation=60)
ax1.set_ylabel('Price', size=10)
# Tick on mondays every week, if monthly, then use "mdates.MonthLocator()"
ax1.xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=MO))
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax1.xaxis.set_minor_locator(mdates.DayLocator())
cursor.execute("SELECT CompanyName FROM Company WHERE StockID = %s", (stockid,))
row = cursor.fetchall()
if(cursor.rowcount <= 0):
print("StockExchange_plot: Error: Cannot find Company Name " + str(cursor.rowcount))
figTitle = u'股票交易資訊' + " STOCK: [" + stockid + "]"
else:
figTitle = u'股票交易資訊' + " " + row[0][0] + ": [" + stockid + "]"
fontP = FontProperties(fname="/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc")
fig.suptitle(figTitle, fontproperties=fontP)
#print(df.values)
candlestick_ohlc(ax1, df.values, width=.6, colorup='red', colordown='green')
# Followiong is to draw using DataFrame
#candlestick_ohlc(ax, list(zip(ohlc["Date"].tolist(), ohlc["Open Price"].tolist(), ohlc["High Price"].tolist(),
# ohlc["Low Price"].tolist(), ohlc["Close Price"].tolist())),width=.6, colorup='green', colordown='red' )
# Create SMA: Simple Moving Average
#df["SMA200"] = df["Close Price"].rolling(20).mean()
#print(df["SMA200"])
#ax1.plot(ohlc["Date"], df["SMA200"], color = 'blue', label = 'SMA200')
# Plot volume
# Set M/K for the volume
ax2.set_ylabel('Volume', size=10)
volume = df_bar['Volume']
volume_scale = None
scaled_volume = volume
if volume.max() > 1000000:
volume_scale = 'M'
scaled_volume = volume/1000000
elif volume.max() > 1000:
volume_scale = 'K'
scaled_volume = volume/1000
# Set color according to candlestick: colorup='red', colordown='green'
color_data = []
for vopen, vclose in zip(df["Open Price"], df["Close Price"]):
if(vopen > vclose):
color_data.append('g')
else:
color_data.append('r')
# do plot
ax2.bar(df['Date'], scaled_volume, color=color_data, width=.6, align='center')
if volume_scale:
volume_title = 'Volume (%s)' % volume_scale
#ax2.set_title("Volume")
ax2.xaxis.grid(False)
plt.show()
def main(stockid, sdate, edate, opt):
global cursor
foreign_buy = []
foreign_sell = []
trust_buy = []
trust_sell = []
dealer_buy = []
dealer_sell = []
idate = []
try:
db = add_db_record.ConnectDB("localhost", "stock", "ftdi1234")
cursor = db.cursor()
# DB Columns are:
# 0@FexId (table's unique id), 1@CoId (company id) 2@Foreign_Investor_buy,
# 3@Foreign_Investor_sell, 4@Investment_Trust_buy, 5@Investment_Trust_sell,
# 6@Dealer_buy, 7@Dealer_sell, 8@Total, 9@Category, 10@Date
cursor.execute( \
"SELECT * FROM FoundationExchange WHERE CoId = (SELECT CoId from Company WHERE StockID = %s) AND \
Date BETWEEN %s AND %s ORDER BY Date ASC", (stockid, sdate, edate,))
data = cursor.fetchall()
if(cursor.rowcount <= 0):
print("StockExchange_plot: Error: No DATA " + str(cursor.rowcount))
return(-1)
for row in data:
print(row)
foreign_buy.append(row[2])
foreign_sell.append(row[3])
trust_buy.append(row[4])
trust_sell.append(row[5])
dealer_buy.append(row[6])
dealer_sell.append(row[7])
idate.append(parser.parse(row[10]))
dates = [mdates.date2num(t) for t in idate]
cursor.execute("SELECT CompanyName FROM Company WHERE StockID = %s", (stockid,))
row = cursor.fetchall()
if(cursor.rowcount <= 0):
print("StockExchange_plot: Error: Cannot find Company Name " + str(cursor.rowcount))
figTitle = u'股票交易資訊' + " STOCK: [" + stockid + "]"
else:
figTitle = u'股票交易資訊' + " " + row[0][0] + ": [" + stockid + "]"
#fig, ax1 = plt.subplots(1, 1, sharex=True, gridspec_kw={'height_ratios': [1]}, figsize = (12,8))
#fig, ax1 = plt.subplots(1, 1, sharex=True, figsize = (12,8))
fig, (ax1, ax2, ax3) = plt.subplots(3, 1, sharex=True, gridspec_kw={'height_ratios': [1,1,1]}, figsize = (12,10))
fontP = FontProperties(fname="/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc")
fig.suptitle(figTitle, fontproperties=fontP)
date_format = '%Y-%m-%d'
start = datetime.datetime.strptime(sdate, date_format)
end = datetime.datetime.strptime(edate, date_format)
tdelta = end - start
print(tdelta.days)
# Configure x-ticks according to number of days
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax2.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
ax3.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
if(tdelta.days > 547):
ax1.xaxis.set_major_locator(mdates.MonthLocator())
ax1.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=3))
ax2.xaxis.set_major_locator(mdates.MonthLocator())
ax2.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=3))
ax3.xaxis.set_major_locator(mdates.MonthLocator())
ax3.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=3))
elif(182 < tdelta.days < 547):
ax1.xaxis.set_major_locator(mdates.MonthLocator())
ax1.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=1))
ax2.xaxis.set_major_locator(mdates.MonthLocator())
ax2.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=1))
ax3.xaxis.set_major_locator(mdates.MonthLocator())
ax3.xaxis.set_minor_locator(MonthLocator(bymonthday=1, interval=1))
else:
ax1.xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=MO))
ax1.xaxis.set_minor_locator(mdates.DayLocator())
ax2.xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=MO))
ax2.xaxis.set_minor_locator(mdates.DayLocator())
ax3.xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=MO))
ax3.xaxis.set_minor_locator(mdates.DayLocator())
# Plot on left Y axis
#ax1.set_ylabel("Number of Shares")
#ax2.set_ylabel("Number of Shares")
#ax3.set_ylabel("Number of Shares")
volume_dict = {
'foreign_buy': foreign_buy,
'foreign_sell': foreign_sell,
'trust_buy': trust_buy,
'trust_sell': trust_sell,
'dealer_buy': dealer_buy,
'dealer_sell': dealer_sell
}
volume_scale_dict = {
'foreign_buy': None,
'foreign_sell': None,
'trust_buy': None,
'trust_sell': None,
'dealer_buy': None,
'dealer_sell': None
}
#for k in volume_dict:
# if(max(volume_dict[k]) > 1000000):
# volume_scale_dict[k] = 'M'
# volume_dict[k][:] = [x/1000000 for x in volume_dict[k]]
# elif(max(volume_dict[k]) > 1000):
# volume_scale_dict[k] = 'K'
# volume_dict[k][:] = [x/1000 for x in volume_dict[k]]
foreign_buy_scale = None
#if(max(foreign_buy) > 1000000):
# foreign_buy_scale = 'M'
# foreign_buy[:] = [x/1000000 for x in foreign_buy]
#elif(max(foreign_buy) > 1000):
# foreign_buy_scale = 'K'
# foreign_buy[:] = [x/1000 for x in foreign_buy]
ax1_volume_title = 'Number of Shares (%s)' % volume_scale_dict['foreign_buy']
ax1.set_ylabel(ax1_volume_title)
ax2.set_ylabel('Number of Shares (%s)' % volume_scale_dict['trust_buy'])
ax3.set_ylabel('Number of Shares (%s)' % volume_scale_dict['dealer_buy'])
# trying to smooth out the curve here, but not much differences
#y_sm = np.array(foreign_buy)
#x_sm = np.array(dates)
#x_smooth = np.linspace(x_sm.min(), x_sm.max(), 800)
#y_smooth = spline(dates, foreign_buy, x_smooth)
#ax1.plot(x_smooth, y_smooth, 'red', linewidth=1)
#ax1.bar(dates, foreign_buy, 0.5, label="Foreign Buy", color='r')
#ax1.bar(dates, foreign_sell, 0.5, label="Foreign Sell", color='b')
# using rolloing to se the trends
#rforeign_buy = pd.DataFrame({'Foreigh Buy':foreign_buy})
#rforeign_buy = rforeign_buy.rolling(8).mean()
ax1.plot_date(dates, foreign_buy, '-', label="Foreign Buy", color='r')
ax1.plot_date(dates, foreign_sell, '-', label="Foreign Sell", color='b')
#ax1.plot_date(dates, rforeign_buy, '-', label="Foreign Sell", color='b')
ax2.plot_date(dates, trust_buy, '-', label="Investor Buy", color='y')
ax2.plot_date(dates, trust_sell, '-', label="Investor Sell", color='m')
ax3.plot_date(dates, dealer_buy, '-', label="Dealer Buy", color='g')
ax3.plot_date(dates, dealer_sell, '-', label="Dealer Sell", color='k')
#Format the x-axis for dates (label formatting, rotation)
fig.autofmt_xdate(rotation=60)
#fig.tight_layout()
# Show grids and legends
ax1.grid(True)
ax1.legend(loc='best', framealpha=0.5)
ax2.grid(True)
ax2.legend(loc='best', framealpha=0.5)
ax3.grid(True)
ax3.legend(loc='best', framealpha=0.5)
plt.show()
#plt.savefig("/var/www/script/figure.png")
#res = mpld3.save_html(fig)
#mpld3.display(fig)
#jf = open("/var/www/script/foundation.js", "w")
#res = mpld3.fig_to_html(fig, "fig.hmtl")
#print(res)
#jf.write(res)
#jf.close()
#mpld3.show()
#return(res)
except Exception as e:
print(e)
raise Exception
def intial_db():
init_db_utf8.InitDB("localhost", "root", "1234")
add_db_record.ConnectDB("localhost", "stock", "1234")