gary136

import pandas as pd
import requests
import numpy as np
from io import StringIO
import time
import datetime
import matplotlib.pyplot as plt

def y_m_generator(first_y, last_y):
    y_m = [(y,m) for y in range(first_y, last_y+1) for m in range(1, 13)]

gary136

from sqlalchemy import *
from sqlalchemy.exc import InternalError

# [your_mysql_account] / [your_mysql_password] 請換成你自己的帳密
def connect_sql(database,echo=True):
    engine = create_engine("mysql+pymysql://[your_mysql_account]:[your_mysql_password]\
    @localhost:3306/{}".format(database),echo=echo)
    return engine
# [your_database_name] 請換成你自己的資料庫名稱
engine = connect_sql([your_database_name],False)

gary136

def monthly_sales(year, month):
    
    url = url_generator(year, month)
    
    # 偽瀏覽器
    headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) \
    AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
    
    # 取得該年月的資料
    r = requests.get(url, headers=headers)

gary136

# Insert Table
from sqlalchemy.exc import IntegrityError

y_m = y_m_generator(2010, 2018)
for y,m in y_m:
    if m<10:
        p = str(y)+'0'+str(m)
    else:
        p = str(y)+str(m)
    

gary136

gary136

def industry_growth(yyyymm):
    text = '''select B.INDUSTRY_NAME_1
        ,B.INDUSTRY_NAME_2
        ,ROUND(AVG(A.MOM), 2) AS AVG_MOM
        ,ROUND(AVG(A.YOY), 2) AS AVG_YOY
        ,ROUND(AVG(A.ACC_YOY), 2) AS AVG_ACC_YOY
        ,COUNT(*) AS CNT
    from SII_REV_{} A
    left join TEJ_STOCK_DATA_BASIC B
    on A.ID = B.ID

gary136

import seaborn as sns
from matplotlib.font_manager import FontProperties
sns.set(font=['sans-serif'])
# 有關 Matplotlib / Seaborn 視覺化的中文顯示問題請參考 https://reurl.cc/15x6m
sns.set_style("whitegrid",{"font.sans-serif":['Microsoft JhengHei']})
plt.figure(figsize=(12,7))
sns.barplot(x="INDUSTRY_NAME_2", y="AVG_ACC_YOY", data=df_2018_growth.head(10))
plt.xlabel('產業名', fontsize=15)
plt.ylabel('營收年增率(%)', fontsize=15)

gary136

def sel_from_ind(ind):
    text = '''select A.*
    ,B.INDUSTRY_NAME_1
    ,B.INDUSTRY_NAME_2
    from SII_REV_201812 A
    left join TEJ_STOCK_DATA_BASIC B
    on A.ID = B.ID
    where B.INDUSTRY_NAME_2 = "{}"
    ;'''
    df = pd.read_sql_query(text.format(ind), con=engine)

gary136

def sales_aggregate(Id):
    y_m = y_m_generator(2010, 2018)
    dfs = []
    for y,m in y_m:
        p = str(y)+'0'+str(m) if m<10 else str(y)+str(m)
        (mapping_y, mapping_m) = (y, m+1) if m!=12 else (y+1, 1)
        df = pd.read_sql_query('select * from SII_REV_{} where ID = "{}"'.format(p, Id), con=engine)
        df['Y_M'] = p
        df['Sales_Public_Date'] = datetime.datetime(mapping_y,mapping_m,7,0,0,0)
        cols = df.columns.tolist()

gary136

%matplotlib inline
import matplotlib.pyplot as plt
import matplotlib
import matplotlib.dates as mdates

yoy = df['YOY']
sales_this_month = df['Sales_This_Month']
zero = pd.Series(np.zeros(len(yoy)))
zero.index = yoy.index