outlook

outlook.py

# -*- coding: utf-8 -*-
"""
Created on Fri Dec 19 18:22:02 2014

@author: assa
"""

import win32com.client
olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject = "I AM SUBJECT!!"
newMail.Body = u'     TRD_DT   GICODE ITEMABBRNM  MKT_GB\n0  20141223  A160550          \ub274  KOSDAQ'
newMail.To = "hyojkim@fnguide.com"
#newMail.CC = "moreaddresses here"
#newMail.BCC = "address"
#attachment1 = "Path to attachment no. 1"
#attachment2 = "Path to attachment no. 2"
#newMail.Attachments.Add(attachment1)
#newMail.Attachments.Add(attachment2)
#newMail.display()
newMail.Send()

outlook_newlisted.py

# -*- coding: utf-8 -*-
"""
Created on Fri Dec 23 18:22:02 2014

@author: assa
"""

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


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())

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]


# 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 = '%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)
strKOSPINewListedStock = df.to_string()


# KOSDAQ 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 = '%s'
AND B.TRD_DT = A.LIST_DT
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""" %(strnowdate)

df = psql.frame_query(sqltext, conn)
strKOSDAQNewListedStock = df.to_string()


# 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 = '%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
""" %(strnowdate)

df = psql.frame_query(sqltext, conn)
strKOSPIDeListedStock = df.to_string()

# 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 = '%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
""" %(strnowdate)

df = psql.frame_query(sqltext, conn)
strKOSDAQDeListedStock = df.to_string()

# 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)
strNewListedPreferStock = df.to_string()

#  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)
strDeListedPreferStock = df.to_string()


#  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 = '%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
""" %(strnowdate)

df = psql.frame_query(sqltext, conn)
strMKF2000InOutStock = df.to_string()


olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject = u'KOSPI,KOSDQ, 우선주 종목 신규상장, 상장폐지, MKF2000 지수 편입편출'
newMail.Body = ''.join('%s \n' * 7) %(strKOSPINewListedStock,                                       
                                      strKOSDAQNewListedStock,                                      
                                      strKOSPIDeListedStock,
                                      strKOSDAQDeListedStock,
                                      strNewListedPreferStock,
                                      strDeListedPreferStock,
                                      strMKF2000InOutStock)                 
                
newMail.To = "hyojkim@fnguide.com"
#newMail.CC = "moreaddresses here"
#newMail.BCC = "address"
#attachment1 = "Path to attachment no. 1"
#attachment2 = "Path to attachment no. 2"
#newMail.Attachments.Add(attachment1)
#newMail.Attachments.Add(attachment2)
#newMail.display()
newMail.Send()

outlook_send.py

# -*- coding: utf-8 -*-
"""
Created on Wed Dec 24 10:51:43 2014

@author: assa
"""

import os
import time
import valueassetmng_excel
import win32com.client


strnowdate = time.strftime('%Y%m%d',time.localtime())
strfilename = 'Result_%s.xlsx' %(strnowdate)
valueassetmng_excel.MakeResultExcel(strfilename,'20141222')
filepath = os.getcwd()
filepath = filepath + '\\' + strfilename


olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject = u'KOSPI,KOSDQ, 우선주 종목 신규상장, 상장폐지, MKF2000 지수 편입편출'
newMail.Body = ''
#newMail.To = "hyojkim@fnguide.com"
newMail.To = "baberuth@truefriend.com; unsil_kim@truefriend.com"
#newMail.CC = "moreaddresses here"
#newMail.BCC = "address"
#attachment1 = "Path to attachment no. 1"
#attachment1 = r"C:\Python\openpyxl\test2.xlsx"
#attachment2 = "Path to attachment no. 2"
#olByValue = 0
newMail.Attachments.Add(Source=filepath)
#newMail.Attachments.Add(attachment2)
#newMail.display()
newMail.Send()

print 'Send email'