-
Notifications
You must be signed in to change notification settings - Fork 1
/
종목선정_재무정보.py
183 lines (137 loc) · 7.8 KB
/
종목선정_재무정보.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
import datetime, time
import talib as ta
import numpy as np
import pandas as pd
from pandas import DataFrame
import pandas.io.sql as pdsql
import mysql.connector
MySQL_POOL_SIZE = 2
데이타베이스_설정값 = {
'host': '127.0.0.1',
'user': 'user',
'password': 'password',
'database': 'database',
'raise_on_warnings': True,
}
class NumpyMySQLConverter(mysql.connector.conversion.MySQLConverter):
""" A mysql.connector Converter that handles Numpy types """
def _float32_to_mysql(self, value):
return float(value)
def _float64_to_mysql(self, value):
return float(value)
def _int32_to_mysql(self, value):
return int(value)
def _int64_to_mysql(self, value):
return int(value)
def _timestamp_to_mysql(self, value):
return value.to_datetime()
def mysqlconn():
conn = mysql.connector.connect(pool_name="stockpool", pool_size=MySQL_POOL_SIZE, **데이타베이스_설정값)
conn.set_converter_class(NumpyMySQLConverter)
return conn
# 데이타를 기간에 맞게 잘라내는 함수
def 기간(dataframe, 시작기간=None, 종료기간=None):
df = dataframe.copy()
if (시작기간 is None) and (종료기간 is None):
pass
elif (시작기간 is None) and not(종료기간 is None):
df = df[:종료기간]
elif not(시작기간 is None) and (종료기간 is None):
df = df[시작기간:]
elif not(시작기간 is None) and not(종료기간 is None):
df = df[시작기간:종료기간]
return df
################################################################################
# 시가총액 순으로 종목 선정
def 시가총액(시장범위=['KOSPI','KOSDAQ'], 시가총액범위=[500,3000]):
_시장범위 = ["'%s'" % i for i in 시장범위]
query = """
SELECT 시장구분, 종목코드, 종목명, 주식수, 감리구분, 상장일, 전일종가, 시가총액, 종목상태
FROM 종목코드_주식
WHERE (시장구분 IN (%s)) and (시가총액 between %s * (10000 * 10000) and %s * (10000 * 10000))
ORDER BY 시가총액 DESC
""" % (','.join(_시장범위), 시가총액범위[0], 시가총액범위[1])
conn = mysqlconn()
df = pdsql.read_sql_query(query, con=conn)
conn.close()
return df
################################################################################
# 그린브라트 방식으로 종목을 선정
def Greenblatt(날짜='2011-12-31', 기간구분='년간'):
result = DataFrame()
query = """
SELECT A.날짜, A.기간구분, A.종목코드, C.종목명, B.종가, A.매출액, A.영업이익, A.당기순이익, A.자산총계, A.부채총계, A.자본총계, A.자본금,
A.부채비율, A.유보율, A.영업이익률, A.순이익률, A.ROA, A.ROE, A.EPS, A.BPS, A.DPS, A.PER, 1/A.PER as RPER, A.PBR, A.발행주식수, A.배당수익률, C.종목상태
FROM 재무정보 A, (select 종목코드, 종가 from 일별주가 where 일자 = (select max(일자) from 일별주가 where 일자 <= '%s')) B, 종목코드 C
WHERE 날짜='%s' and 기간구분='%s' and A.종목코드=B.종목코드 and A.종목코드=C.종목코드
""" % (날짜, 날짜, 기간구분)
conn = mysqlconn()
df = pdsql.read_sql_query(query, con=conn)
conn.close()
df['rank1'] = df['ROA'].rank(ascending=False)
df['rank2'] = df['RPER'].rank(ascending=False)
df['ranksum'] = df['rank1'] + df['rank2']
df['rank'] = df['ranksum'].rank(ascending=True)
result = df.sort_values(['rank', 'rank1', 'rank2'], ascending=[True,True,True])
return result
################################################################################
# 드레먼 방식으로 종목을 선정 - 역발상투자
def DavidDreman(날짜='2011-12-31', 기간구분='년간'):
result = DataFrame()
query = """
SELECT A.날짜, A.기간구분, A.종목코드, C.종목명, B.종가, A.매출액, A.영업이익, A.당기순이익, A.자산총계, A.부채총계, A.자본총계, A.자본금,
A.부채비율, A.유보율, A.영업이익률, A.순이익률, A.ROA, A.ROE, A.EPS, A.BPS, A.DPS, A.PER, 1/A.PER as RPER, A.PBR, A.발행주식수, A.배당수익률, C.종목상태
FROM 재무정보 A, (select 종목코드, 종가 from 일별주가 where 일자 = (select max(일자) from 일별주가 where 일자 <= '%s')) B, 종목코드 C
WHERE 날짜='%s' and 기간구분='%s' and A.종목코드=B.종목코드 and A.종목코드=C.종목코드
""" % (날짜, 날짜, 기간구분)
conn = mysqlconn()
df = pdsql.read_sql_query(query, con=conn)
conn.close()
df['rank1'] = df['ROA'].rank(ascending=False)
df['rank2'] = df['PBR'].rank(ascending=True)
df['rank3'] = df['PER'].rank(ascending=True)
df['ranksum'] = df['rank1'] + df['rank2'] + df['rank3']
df['rank'] = df['ranksum'].rank(ascending=True)
result = df.sort_values(['rank', 'rank1', 'rank2', 'rank3'], ascending=[True,True,True,True])
return result
################################################################################
# •보통 좋은종목을 선정하는 방법
def 좋은종목(날짜='2011-12-31', 기간구분='년간'):
result = DataFrame()
query = """
SELECT A.날짜, A.기간구분, A.종목코드, C.종목명, B.종가, A.매출액, A.영업이익, A.당기순이익, A.자산총계, A.부채총계, A.자본총계, A.자본금,
A.부채비율, A.유보율, A.영업이익률, A.순이익률, A.ROA, A.ROE, A.EPS, A.BPS, A.DPS, A.PER, 1/A.PER as RPER, A.PBR, A.발행주식수, A.배당수익률, C.종목상태
FROM 재무정보 A, (select 종목코드, 종가 from 일별주가 where 일자 = (select max(일자) from 일별주가 where 일자 <= '%s')) B, 종목코드 C
WHERE 날짜='%s' and 기간구분='%s' and A.종목코드=B.종목코드 and A.종목코드=C.종목코드
""" % (날짜, 날짜, 기간구분)
conn = mysqlconn()
df = pdsql.read_sql_query(query, con=conn)
conn.close()
df['rank1'] = df['영업이익률'].rank(ascending=False)
df['rank2'] = df['ROE'].rank(ascending=False)
df['rank3'] = df['PER'].rank(ascending=True)
df['rank4'] = df['유보율'].rank(ascending=False)
df['ranksum'] = df['rank1'] + df['rank2'] + df['rank3'] + df['rank4']
df['rank'] = df['ranksum'].rank(ascending=True)
result = df.sort_values(['rank','rank1','rank3','rank4'], ascending=[True,False,True,False])
return result
################################################################################
# 영업이익 위주로 종목을 선정
def 영업이익(날짜='2011-12-31', 기간구분='년간', 정렬순서=True):
result = DataFrame()
query = """
SELECT A.날짜, A.기간구분, A.종목코드, C.종목명, B.종가, A.매출액, A.영업이익, A.당기순이익, A.자산총계, A.부채총계, A.자본총계, A.자본금,
A.부채비율, A.유보율, A.영업이익률, A.순이익률, A.ROA, A.ROE, A.EPS, A.BPS, A.DPS, A.PER, 1/A.PER as RPER, A.PBR, A.발행주식수, A.배당수익률, C.종목상태
FROM 재무정보 A, (select 종목코드, 종가 from 일별주가 where 일자 = (select max(일자) from 일별주가 where 일자 <= '%s')) B, 종목코드 C
WHERE 날짜='%s' and 기간구분='%s' and A.종목코드=B.종목코드 and A.종목코드=C.종목코드
""" % (날짜, 날짜, 기간구분)
conn = mysqlconn()
df = pdsql.read_sql_query(query, con=conn)
conn.close()
df['rank1'] = df['영업이익률'].rank(ascending=False)
df['rank2'] = df['순이익률'].rank(ascending=False)
df['rank3'] = df['유보율'].rank(ascending=False)
df['ranksum'] = df['rank1'] + df['rank2'] + df['rank3']
df['rank'] = df['ranksum'].rank(ascending=True)
result = df.sort_values(['rank', 'rank1', 'rank2', 'rank3'], ascending=[정렬순서,False,False,False])
return result