openpyxl

IndexTimeSeriesAnalysis.py

# -*- coding: utf-8 -*-
"""
Created on Tue Feb 05 10:02:17 2015

@author: assa
"""

import pdb
import pandas as pd
import cx_Oracle as odb

# REAL_DB_DEV
ip = '10.10.1.23'
port = '1521'
SID = 'fndb2'

tns_REALDB_DEV = odb.makedsn(ip, port, SID)
conn = odb.connect('UFNGDBA','venus2002',tns_REALDB_DEV)
curs = conn.cursor()


u_cd_lst = []

for iter in xrange(16,25):
    u_cd = 'FI00.WLT.%.3d'%iter
    u_cd_lst.append(u_cd)

simul_id_num_lst = [3,4,2,6,7,5,9,10,8]
simul_id_lst = []

for simul_num in simul_id_num_lst:
    simul_id = 'KHJ%.2d'%simul_num
    simul_id_lst.append(simul_id)



for i in xrange(len(simul_id_lst)):
    simul_id = simul_id_lst[i]
    u_cd = u_cd_lst[i]
    
    print u_cd, 
    
    sqltext = """
    SELECT A.STD_DT, 
    A.CLS_PRC / 1000 * 599 PROXY, B.CLS_PRC KOSPI 
    FROM JISUDEV.RES_STYLE_IDX A, FNS_UD B, FNC_CALENDAR C
    WHERE A.SIMUL_ID = '%s'
    AND A.U_CD = '%s'
    AND A.STD_DT = B.TRD_DT
    AND B.U_CD = 'I.001'
    AND A.STD_DT = C.TRD_DT
    AND A.STD_DT >= '20091230'
    AND C.MON_LAST_YN = 'Y'
    AND substr(A.STD_DT,5,2) = '12'
    ORDER BY A.STD_DT    
    """%(simul_id, u_cd)
    
    df_yoy = pd.read_sql(sqltext, conn)
    df_yoy['PROXY_Ret'] = df_yoy['PROXY'].pct_change()
    df_yoy['KOSPI_Ret'] = df_yoy['KOSPI'].pct_change()
    
    enddate_lst = list(df_yoy['STD_DT'])


    sqltext = """
    SELECT A.STD_DT,
    A.CLS_PRC / 1000 * 599 PROXY, B.CLS_PRC KOSPI
    FROM JISUDEV.RES_STYLE_IDX A, FNS_UD B
    WHERE A.SIMUL_ID = '%s'
    AND A.U_CD = '%s'
    AND A.STD_DT = B.TRD_DT
    AND A.STD_DT >= '20091230'
    AND B.U_CD = 'I.001'
    ORDER BY A.STD_DT
    """%(simul_id, u_cd)

    
    df = pd.read_sql(sqltext, conn)
    
    df['PROXY_Ret'] = df['PROXY'].pct_change()
    df['KOSPI_Ret'] = df['KOSPI'].pct_change()
    
    df['Exc_Ret'] = df['PROXY_Ret'] - df['KOSPI_Ret']
    
    pdb.set_trace()
    
    for j in xrange(len(df_yoy)-1):
        print df_yoy['PROXY_Ret'].iloc[j+1],
        
    
    for j in xrange(len(enddate_lst)-1):
        startdate = enddate_lst[j]
        enddate = enddate_lst[j+1]
        #print startdate, enddate
        df_year = df[(df['STD_DT'] >= startdate) & (df['STD_DT'] <= enddate)]
        print df_year['Exc_Ret'].std(),

    print 

makeexcelfile.py

# -*- coding: cp949 -*-
"""
Created on Wed Dec 24 10:48:43 2014

@author: assa
"""

def MakeResultExcel(strfilename,strstartdate = ''):
    import time
    import pandas as pd
    import pandas.io.sql as psql
    import cx_Oracle as odb
    from openpyxl import workbook
    
    ip = '10.10.1.50'
    port = '1521'
    SID = 'fndb2'
    
    tns_REALDB = odb.makedsn(ip, port, SID)
    conn = odb.connect('UFNGDBA','venus2002',tns_REALDB)
    curs = conn.cursor()
    
    strnowdate = time.strftime('%Y%m%d',time.localtime())
    if strstartdate == '':
        strstartdate = strnowdate
        
    # need to check up strstardate how to far...
    
    sqltext = """
    SELECT A.TRD_DT_PDAY
    FROM FNC_CALENDAR A
    WHERE A.TRD_DT = '%s'
    """ %strnowdate
    
    curs.execute(sqltext)
    row = curs.fetchone()
    
    strprevdate = row[0]
    
    df_list = []
    
    
    # KOSPI new listed stock at nowdate
    sqltext = """
    SELECT B.TRD_DT ,B.GICODE, B.ITEMABBRNM, 
    DECODE(B.MKT_GB,'1','KOSPI','2','KOSDAQ') MKT_GB,
    substr(B.FGSC_CD,0,10) UCD, C.U_NM
    FROM FNS_J_MAST_HIST B,  FNS_U_MAST C
    WHERE B.TRD_DT between '%s' AND '%s'
    AND B.TRD_DT = B.REG_DT 
    AND B.MKT_GB = '1'
    AND C.U_CD = substr(B.FGSC_CD,0,10)
    AND B.sosok_gb IN ('01', '02', '06', '10', '12')    
    ORDER BY B.TRD_DT, B.GICODE""" %(strstartdate, strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'KOSPI new listed done' 
    
    # KOSDAQ new listed stock at nowdate
    sqltext = """
    SELECT B.TRD_DT ,B.GICODE, B.ITEMABBRNM, 
    DECODE(B.MKT_GB,'1','KOSPI','2','KOSDAQ') MKT_GB,
    substr(B.FGSC_CD,0,10) UCD, C.U_NM
    FROM FNS_J_MAST_HIST B,  FNS_U_MAST C
    WHERE B.TRD_DT between '%s' AND '%s'
    AND B.TRD_DT = B.REG_DT 
    AND B.MKT_GB = '2'
    AND C.U_CD = substr(B.FGSC_CD,0,10)    
    ORDER BY B.TRD_DT, B.GICODE""" %(strstartdate, strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'KOSDAQ new listed done' 
    
    # KOSPI delisted stock at nowdate
    sqltext = """
    SELECT B.TRD_DT ,B.GICODE, B.ITEMABBRNM, substr(B.FGSC_CD,0,10) UCD, C.U_NM,
    DECODE(B.MKT_GB,'1','KOSPI','2','KOSDAQ') MKT_GB
    FROM FNJ_AA4 A, FNS_J_MAST_HIST B, FNS_U_MAST C
    WHERE A.CLS_DT between '%s' and '%s'
    AND B.TRD_DT = A.CLS_DT - 1
    AND A.GICODE = B.GICODE
    AND B.MKT_GB = '1'
    AND B.sosok_gb IN ('01', '02', '05', '06', '07', '10', '12')
    AND C.U_CD = substr(B.FGSC_CD,0,10)
    ORDER BY B.TRD_DT, B.GICODE
    """ %(strstartdate, strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'KOSPI delisted done' 
    
    # KOSDAQ delisted stock at nowdate
    sqltext = """
    SELECT B.TRD_DT ,B.GICODE, B.ITEMABBRNM, substr(B.FGSC_CD,0,10) UCD, C.U_NM,
    DECODE(B.MKT_GB,'1','KOSPI','2','KOSDAQ') MKT_GB
    FROM FNJ_AA4 A, FNS_J_MAST_HIST B, FNS_U_MAST C
    WHERE A.CLS_DT between '%s' and '%s'
    AND B.TRD_DT = A.CLS_DT - 1
    AND A.GICODE = B.GICODE
    AND B.MKT_GB = '2'
    AND C.U_CD = substr(B.FGSC_CD,0,10)
    ORDER BY B.TRD_DT, B.GICODE
    """ %(strstartdate, strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'KOSDAQ delisted done' 
    
    # new listed prefered stock 
    sqltext = """
    SELECT '%s' TRD_DT, A.GICODE, B.ITEMABBRNM, substr(B.FGSC_CD,0,10) UCD, C.U_NM
    FROM 
    (
    SELECT  A.GICODE--, A.ITEMABBRNM
    FROM FNS_J_MAST_HIST A
    WHERE A.GICODE NOT LIKE 'A%%0'
    AND A.USE_YN = 'Y'
    AND A.STK_GB = '701'
    AND A.MKT_GB IN ('1', '2')
    AND A.TRD_DT = '%s'
    MINUS
    SELECT  A.GICODE--, A.ITEMABBRNM
    FROM FNS_J_MAST_HIST A
    WHERE A.GICODE NOT LIKE 'A%%0'
    AND A.USE_YN = 'Y'
    AND A.STK_GB = '701'
    AND A.MKT_GB IN ('1', '2')
    AND A.TRD_DT = '%s'
    ) A, FNS_J_MAST B, FNS_U_MAST C
    WHERE A.GICODE = B.GICODE
    AND C.U_CD = substr(B.FGSC_CD,0,10)
    """ %(strnowdate,strnowdate,strprevdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'new listed prefered stock done' 
    
    #  delisted prefered stock 
    sqltext = """
    SELECT '%s' TRD_DT, A.GICODE, B.ITEMABBRNM, substr(B.FGSC_CD,0,10) UCD, C.U_NM
    FROM 
    (
    SELECT  A.GICODE--, A.ITEMABBRNM
    FROM FNS_J_MAST_HIST A
    WHERE A.GICODE NOT LIKE 'A%%0'
    AND A.USE_YN = 'Y'
    AND A.STK_GB = '701'
    AND A.MKT_GB IN ('1', '2')
    AND A.TRD_DT = '%s'
    MINUS
    SELECT  A.GICODE--, A.ITEMABBRNM
    FROM FNS_J_MAST_HIST A
    WHERE A.GICODE NOT LIKE 'A%%0'
    AND A.USE_YN = 'Y'
    AND A.STK_GB = '701'
    AND A.MKT_GB IN ('1', '2')
    AND A.TRD_DT = '%s'
    ) A, FNS_J_MAST B, FNS_U_MAST C
    WHERE A.GICODE = B.GICODE
    AND C.U_CD = substr(B.FGSC_CD,0,10)
    """ %(strnowdate,strprevdate,strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'delisted prefered stock done' 
    
    
    #  MKF2000 in out stock 
    sqltext = """
    SELECT A.TRD_DT, A.GICODE, A.ITEMABBRNM, decode(C.IN_OUT_GB,'1','IN','2','OUT') INOUT_GB, 
    DECODE(A.MKT_GB,1,'KOSPI',2,'KOSDAQ') MKT_GB, substr(A.FGSC_CD,0,10) UCD, B.U_NM
    FROM FNS_J_MAST_HIST A, FNS_U_MAST B, FNS_U_MAP_HIST C
    WHERE A.GICODE = C.GICODE
    AND C.UPDATE_DT between '%s' and '%s'
    AND A.TRD_DT = C.UPDATE_DT
    AND substr(A.FGSC_CD,0,10) = B.U_CD
    AND C.U_CD = 'FGSC'
    AND C.IN_OUT_GB IN ('1', '2')
    ORDER BY A.TRD_DT, A.GICODE
    """ %(strstartdate, strnowdate)
    
    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    print 'MKF2000 in out stock done' 
    
    
    wb = workbook.Workbook()
    ws = wb.get_active_sheet()
    header = ['일자','종목코드','종목명','시장구분','업종코드','업종명']
    header2 = ['일자','종목코드','종목명','편입편출','시장구분','업종코드','업종명']
    sheettitlelst = ['KOSPI_신규상장','KOSDAQ_신규상장','KOSPI_상장폐지','KOSDAQ_상장폐지',
                  '우선주_신규상장','우선주_상장폐지']
    
    
    #for sheetiter in xrange(2):
    for sheetiter in xrange(len(sheettitlelst)):      
        if sheetiter != 0: ws = wb.create_sheet()
        ws.title = unicode(sheettitlelst[sheetiter], 'cp949')
        df = df_list[sheetiter]
        for i in xrange(len(df)+1):
            for j in xrange(len(header)):
                cell = ws.cell(row=i,column=j)
                if i == 0:                         
                    cell.value = unicode(header[j], 'cp949')
                elif i > 0:
                    if j == 2 or j == 5:                
                        cell.value = unicode(df.ix[i-1][j], 'cp949')
                    else:
                        cell.value = df.ix[i-1][j]
                        
    
    ws = wb.create_sheet()                    
    ws.title = unicode('MKF2000 구성종목 편입편출','cp949')
    df = df_list[-1]
    for i in xrange(len(df)+1):
            for j in xrange(len(header2)):
                cell = ws.cell(row=i,column=j)
                if i == 0:                         
                    cell.value = unicode(header2[j], 'cp949')
                elif i > 0:
                    if j == 2 or j == 6:                
                        cell.value = unicode(df.ix[i-1][j], 'cp949')
                    else:
                        cell.value = df.ix[i-1][j]
    
    
    wb.save(strfilename)
    print 'save excel file'
    
    
if __name__ == '__main__':
    import time
    
    strnowdate = time.strftime('%Y%m%d',time.localtime())
    strfilename = 'Test_%s.xlsx' %(strnowdate)
    MakeResultExcel(strfilename, '20141222')

MakeExcelFromOracle.py

# -*- coding: utf-8 -*-
"""
Created on Tue Jan 06 16:45:17 2015

@author: assa
"""

import time
import pandas as pd
import pandas.io.sql as psql
import cx_Oracle as odb
from openpyxl import workbook

# REAL_DB_DEV
ip = '10.10.1.23'
port = '1521'
SID = 'fndb2'

tns_REALDB_DEV = odb.makedsn(ip, port, SID)
conn = odb.connect('UFNGDBA','venus2002',tns_REALDB_DEV)

curs = conn.cursor()

df_list = []
u_cd_list = []

for i in xrange(15):

    u_cd = 'FI00.WLT.0%.2d'%(i+1)

    sqltext = """
    SELECT substr(A.STD_DT,0,4) || '-' || substr(A.STD_DT,5,2) || '-' || substr(A.STD_DT,7,2) TRD_DT,
    A.CLS_PRC / 1000 * 599 PROXY, B.CLS_PRC KOSPI
    FROM JISUDEV.RES_STYLE_IDX A, FNS_UD B
    WHERE A.SIMUL_ID = 'KHJ01'
    AND A.U_CD = '%s'
    AND A.STD_DT = B.TRD_DT
    AND B.U_CD = 'I.001'
    ORDER BY A.STD_DT
    """ %u_cd


    df = psql.frame_query(sqltext, conn)
    df_list.append(df)
    u_cd_list.append(u_cd)
    pass

wb = workbook.Workbook()
ws = wb.get_active_sheet()

header = ['Date','PROXY', 'KOSPI']

for u_cd_iter in xrange(len(u_cd_list)):
    if u_cd_iter != 0: ws = wb.create_sheet()
    u_cd = u_cd_list[u_cd_iter]
    print u_cd
    ws.title = u_cd_list[u_cd_iter]
    df = df_list[u_cd_iter]
    for i in xrange(len(df)+2):
        for j in xrange(len(header)):
            cell = ws.cell(row=i,column=j)
            if i == 0 and j == 0:
                cell.value = u_cd
            elif i == 1:
                cell.value = header[j]
            elif i > 1:
                cell.value = df.ix[i-2][j]

wb.save('test2.xlsx')

MakeExcelFromOracle2.py

# -*- coding: utf-8 -*-
"""
Created on Tue Jan 07 16:45:17 2015

@author: assa
"""
import pdb
import time
import pandas as pd
import pandas.io.sql as psql
import cx_Oracle as odb
from openpyxl import workbook


ip = '10.10.1.50'
port = '1521'
SID = 'fndb2'

tns_REALDB = odb.makedsn(ip, port, SID)
conn = odb.connect('UFNGDBA','venus2002',tns_REALDB)
curs = conn.cursor()

df_list = []
u_cd_list = []

sqltext = """
SELECT  A.U_CD
FROM    UFNGDBA.FNI_U_MAST A
WHERE   A.U_CD LIKE 'DNPK200.FGSC.__.__'
"""

df_ucd = psql.frame_query(sqltext, conn)
u_cd_list = list(df_ucd['U_CD'])

for u_cd_iter in u_cd_list:

    sqltext_index = """
    SELECT A.U_CD,A.STD_DT, A.CLS_PRC
    FROM UFNGDBA.FNI_fgc_Idx A
    WHERE A.U_CD = '%s'
    AND A.STD_DT BETWEEN '20141201' AND TO_CHAR(SYSDATE,'YYYYMMDD')
    ORDER BY A.U_CD, A.STD_DT
    """ %(u_cd_iter)

    df_index = psql.frame_query(sqltext_index, conn)
    df_index['Return'] = df_index['CLS_PRC'].pct_change() * 100
    df_index = df_index.fillna(0)

    df_list.append(df_index)


wb = workbook.Workbook()
ws = wb.get_active_sheet()

header = ['U_CD','Date', 'CLS_PRC', 'Return']

for u_cd_iter in xrange(len(u_cd_list)):
    if u_cd_iter != 0: ws = wb.create_sheet()
    u_cd = u_cd_list[u_cd_iter]
    print u_cd
    ws.title = u_cd_list[u_cd_iter]
    df = df_list[u_cd_iter]
    for i in xrange(len(df)+1):
        for j in xrange(len(header)):
            cell = ws.cell(row=i,column=j)
            if i == 0:
                cell.value = header[j]
            elif i > 0:
                cell.value = df.ix[i-1][j]

wb.save('test2.xlsx')

NPS_TRIndex_Check.py

# -*- coding: cp949 -*-
"""
Created on Mon Feb 02 17:24:52 2015

@author: ASSA
"""
import pdb
import time
import pandas as pd
import cx_Oracle as odb
from openpyxl import workbook


# REAL_DB
ip = '10.10.1.50'
port = '1521'
SID = 'fndb2'

tns_REALDB = odb.makedsn(ip, port, SID)
conn = odb.connect('UFNGDBA','venus2002',tns_REALDB)


sqltext = """
SELECT  DISTINCT A.U_CD
FROM    UFNGDBA.FNI_FGC_IDX A
WHERE   A.U_CD LIKE 'NPC.FDI%'
AND     A.STD_DT = '20150130' 
"""

df = pd.read_sql(sqltext,conn)
u_cd_list = list(df['U_CD'])
df_list = []
df_divid_list = []


for u_cd in list(df['U_CD']):
    u_cd_tr = 'D' + u_cd
    
    sql_text = """
    SELECT A.U_CD, A.STD_DT, A.CLS_PRC_RAW  "TR_Index", B.CLS_PRC_RAW "Index", 
    A.CLS_PRC_RAW / LAG (A.CLS_PRC_RAW,1) OVER (ORDER BY A.STD_DT) - 1 "TR Index Ret",
    B.CLS_PRC_RAW / LAG (B.CLS_PRC_RAW,1) OVER (ORDER BY A.STD_DT) - 1 "Index Ret",
    A.CLS_PRC_RAW / LAG (A.CLS_PRC_RAW,1) OVER (ORDER BY A.STD_DT) 
    - B.CLS_PRC_RAW / LAG (B.CLS_PRC_RAW,1) OVER (ORDER BY A.STD_DT) "ExcessRet" 
    FROM FNI_FGC_IDX A,  FNI_FGC_IDX B
    WHERE A.U_CD = '%s'
    AND B.U_CD = '%s'
    AND A.std_dt(+) = B.std_dt
    --AND A.std_dt BETWEEN '20141210' AND '20141215'
    --AND A.std_dt BETWEEN TO_CHAR(SYSDATE-31,'YYYYMMDD') AND  TO_CHAR(SYSDATE,'YYYYMMDD') 
    AND A.std_dt BETWEEN '20141201' AND  TO_CHAR(SYSDATE,'YYYYMMDD')
    ORDER BY A.STD_DT
    """ %(u_cd_tr,u_cd)
    
    sql_text_divid = """
    SELECT A.DISCLOS_DT, A.GICODE, A.ITEMABBRNM, A.STK_DIVID_AMT, C.U_CD
    FROM FNI_DISCLOS A, FNS_U_MAP B, 
    (
        SELECT A.GICODE, A.U_CD
        FROM FNS_U_MAP_HIST_CUST A
        WHERE A.U_CD = '%s'
        AND A.UPDATE_DT = '20141228'
    ) C
    WHERE A.INCREASE_CAP_CD = '24'
    AND A.DISCLOS_DT BETWEEN '20141201' AND '20150203'   -- = TO_CHAR(SYSDATE-1,'YYYYMMDD')
    AND A.GICODE LIKE 'A%%0'
    AND B.U_CD = 'I.101'
    AND A.GICODE = B.GICODE (+)
    AND A.GICODE = C.GICODE
    ORDER BY A.DISCLOS_DT
    """%u_cd
    
    df_index = pd.read_sql(sql_text,conn)    
    df_divid = pd.read_sql(sql_text_divid,conn)
    
    print u_cd_tr, u_cd
    df_list.append(df_index)
    df_divid_list.append(df_divid)
    
wb = workbook.Workbook()
ws = wb.get_active_sheet()

header = ['U_CD', 'Date','TR_Index', 'Index', 'TR Index Ret', 'Index Ret', 'ExcessRet']

header2 = ['DISCLOS_DT', 'GICODE', 'ITEMABBRNM', 'STK_DIVID_AMT' , 'U_CD']

for u_cd_iter in xrange(len(u_cd_list)):
    if u_cd_iter != 0: ws = wb.create_sheet()
    u_cd = u_cd_list[u_cd_iter]
    #print u_cd
    ws.title = u_cd_list[u_cd_iter]
    df = df_list[u_cd_iter]
    df_divid = df_divid_list[u_cd_iter]
    df_excess = df[abs(df['ExcessRet']) > 0.00001]
    
    for i in xrange(len(df)+1):
        for j in xrange(len(header)):
            cell = ws.cell(row=i,column=j)
            if i == 0:
                cell.value = header[j]
            elif i > 0:
                cell.value = df.ix[i-1][j]
                print df.ix[i-1][j],
        
        for j in xrange(len(header2)):
            cell = ws.cell(row=i,column=j+len(header)+2)
            if i == 0:
                cell.value = header2[j]
            elif i > 0 and i <= len(df_divid):                
                if j == 2: 
                    cell.value = unicode(df_divid.ix[i-1][j], 'cp949')
                else: 
                    cell.value = df_divid.ix[i-1][j]
                    
                print df_divid.ix[i-1][j],

        for j in xrange(len(header)):
            cell = ws.cell(row=i,column=j+len(header)+len(header2)+4)
            if i == 0:
                cell.value = header[j]
            elif i > 0 and i <= len(df_excess):                
                cell.value =  df_excess.iloc[i-1][j]
                print  df_excess.iloc[i-1][j],
    
                
        print
        

wb.save('test_nps2.xlsx')

openpyxl.py

# -*- coding: utf-8 -*-
"""
Created on Fri Dec 19 17:54:14 2014

@author: assa
"""

from openpyxl import workbook

wb = workbook.Workbook()
ws = wb.create_sheet()
ws.title = "New Title"
wb.get_sheet_names()

c = ws.cell('A4')
#c = ws.cell(row = 0, column = 0)
c.value = 1

print c.value

wb.save('test.xlsx')

openpyxl_test2.py

# -*- coding: utf-8 -*-
"""
Created on Tue Dec 23 16:28:25 2014

@author: assa
"""

import time
import win32com.client
import pandas as pd
import pandas.io.sql as psql
import cx_Oracle as odb
from openpyxl import workbook


ip = '10.10.1.50'
port = '1521'
SID = 'fndb2'

tns_REALDB = odb.makedsn(ip, port, SID)
conn = odb.connect('UFNGDBA','venus2002',tns_REALDB)
curs = conn.cursor()

strnowdate = time.strftime('%Y%m%d',time.localtime())


# KOSPI new listed stock at nowdate
sqltext = """
SELECT B.TRD_DT ,B.GICODE, B.ITEMABBRNM, substr(B.FGSC_CD,0,10) UCD, C.U_NM,
DECODE(B.MKT_GB,'1','KOSPI','2','KOSDAQ') MKT_GB
FROM FNJ_AA4 A, FNS_J_MAST_HIST B, FNS_U_MAST C
WHERE A.LIST_DT between '20141119' and '%s'
AND B.TRD_DT = A.LIST_DT
AND A.GICODE = B.GICODE
AND B.MKT_GB = '1'
AND B.sosok_gb IN ('01', '02', '05', '06', '07', '10', '12')
AND C.U_CD = substr(B.FGSC_CD,0,10)
ORDER BY B.TRD_DT, B.GICODE""" %(strnowdate)

df = psql.frame_query(sqltext, conn)

wb = workbook.Workbook()
ws = wb.get_active_sheet()
header = ['일자','종목코드','종목명','업종코드','업종명']



for i in xrange(len(df)+1):
    for j in xrange(5):
        c = ws.cell(row=i,column=j)
        if i == 0:            
            u_headeritem = unicode(header[j])
            c.value = u_headeritem
        else:
            if i%2 == 0:
                u_str = unicode(df.ix[i][j])
                c.value = u_str
            else:
                c.value = df.ix[i][j]

wb.save('test2.xlsx')