from  setting import get_mysql_conn,get_engine

import pandas as pd

import pymongo

from pyecharts import Geo,Style,Map

engine = get_engine('db_stock',local='local')

# 堆叠图

from pyecharts import Bar

df = pd.read_sql('tb_bond_jisilu',con=engine)



result ={}

for name,grades in df.groupby('评级'):

#     print(name,grades[['可转债名称','可转债价格']])

    for each in grades['可转债价格']:

        result.setdefault(name,)

        result[name].append(each)

        

        

# 确定价格的范围



value = [str(i) for i in range(85,140)]

ret = [0]*len(value)

ret1 = dict(zip(value,ret))



ret_A_add = ret1.copy()

for item in result['A+']:

    for k in ret1:

        if float(k)+0.5>item and float(k)-0.5<=item:

            ret_A_add[k]+=1

            

retAA_ = ret1.copy()

for item in result['']:

    for k in ret1:

        if float(k)+0.5>item and float(k)-0.5<=item:

            retAA_[k]+=1



retAA = ret1.copy()

for item in result['AA']:

    for k in ret1:

        if float(k)+0.5>item and float(k)-0.5<=item:

            retAA[k]+=1

            

retAA_add = ret1.copy()

for item in result['AA+']:

    for k in ret1:

        if float(k)+0.5>item and float(k)-0.5<=item:

            retAA_add[k]+=1

            

retAAA = ret1.copy()

for item in result['AAA']:

    for k in ret1:

        if float(k)+0.5>item and float(k)-0.5<=item:

            retAAA[k]+=1



bar = Bar('可转债价格分布')

bar.add('A+',value,list(ret_A_add.values()),is_stack=True,yaxis_max=11)

bar.add('',value,list(retAA_.values()),is_stack=True,yaxis_max=11)

bar.add('AA',value,list(retAA.values()),is_stack=True,yaxis_max=11)

bar.add('AA+',value,list(retAA_add.values()),is_stack=True,yaxis_max=11)

bar.add('AAA',value,list(retAAA.values()),is_stack=True,yaxis_max=11)

SELECT area `地区`,count(*) as `数目` FROM `tb_basic_info` GROUP BY area order by 数目 desc;

# A股上市公司分布：

df = pd.read_sql('tb_basic_info',con=engine)

result = df['area'].value_counts()

total = result.sum()

ration = result/total*100

# 热力图

def create_heatmap(attr,value,name,maptype):

    style = Style(title_color="#fff", title_pos="center",

              width=1200, height=600, background_color="#696969")



    # 可视化

    geo = Geo(name,**style.init_style)

    geo.add("", attr, value, visual_range=[min(value), max(value)], symbol_size=8,

            visual_text_color="#000",

            is_visualmap=True, type='effectScatter',effect_scale=7,is_random=True,is_roam=False,is_piecewise = True,visual_split_number= 10,

            )



    geo.render('{}.html'.format(name))

create_heatmap(attr,value,'公司分布','china')

array([[4.67296746e-307, 1.69121096e-306, 9.34601642e-307,

        1.33511562e-306],

       [8.34447260e-308, 6.23043768e-307, 2.22522597e-306,

        1.33511969e-306],

       [1.37962320e-306, 9.34604358e-307, 9.79101082e-307,

        1.78020576e-306],

       [1.69119873e-306, 2.22522868e-306, 1.24611809e-306,

        8.06632139e-308]])

numpy.logspace

numpy.logspace(start, stop, num=50, endpoint=True, base=10.0, dtype=None)[source]

Return numbers spaced evenly on a log scale.

In linear space, the sequence starts at base ** start (base to the power of start) and ends with base ** stop (see endpoint below).

Parameters:

start : float

base ** start is the starting value of the sequence.

stop : float

base ** stop is the final value of the sequence, unless endpoint is False. In that case, num + 1 values are spaced over the interval in log-space, of which all but the last (a sequence of length num) are returned.

num : integer, optional

Number of samples to generate. Default is 50.

endpoint : boolean, optional

If true, stop is the last sample. Otherwise, it is not included. Default is True.

base : float, optional

The base of the log space. The step size between the elements in ln(samples) / ln(base) (or log_base(samples)) is uniform. Default is 10.0.

dtype : dtype

The type of the output array. If dtype is not given, infer the data type from the other input arguments.

Returns:

samples : ndarray

num samples, equally spaced on a log scale

array([1.00000000e+00, 1.77827941e+01, 3.16227766e+02, 5.62341325e+03,

       1.00000000e+05, 1.77827941e+06, 3.16227766e+07, 5.62341325e+08,

       1.00000000e+10])

numpy.asfarray(a, dtype=<class 'numpy.float64'>)



Return an array converted to a float type.



Parameters:	

a : array_like

The input array.



dtype : str or dtype object, optional

Float type code to coerce input array a. If dtype is one of the ‘int’ dtypes, it is replaced with float64.



Returns:	

out : ndarray

The input a as a float ndarray.

Examples



>>>

>>> np.asfarray([2, 3])

array([ 2.,  3.])

>>> np.asfarray([2, 3], dtype='float')

array([ 2.,  3.])

>>> np.asfarray([2, 3], dtype='int8')

array([ 2.,  3.])

all_stocks = DataAPI.SecTypeRegionRelGet(secID=u"",ticker=u"",typeID=u"",field=u"",pandas="1")

fbl = 



for code in all_stocks['secID']:

    df = DataAPI.MktEqudGet(secID=code,ticker=u"",tradeDate=u"",beginDate=u"20120101",endDate=u"",isOpen="",field=u"",pandas="1")

    df['ztj']=map(lambda x:round(x,2),df['preClosePrice']*1.1)

    df['chgPct']=df['chgPct']*100

    

    # 非一字板

    zt = df[(df['ztj']==df['highestPrice']) & (df['openPrice']!=df['highestPrice'])]

    fz= df[(df['ztj']==df['highestPrice']) & (df['openPrice']!=df['highestPrice'])&(df['closePrice']==df['highestPrice'])]

    try:

        f = len(fz)*1.00/len(zt)*100

        fbl.append((code,f))

    except Exception,e:

        print e

        print code

dx= dict(fbl)

x = np.array(dx.values())

print x.mean()

# -*- coding=utf-8 -*-

import datetime



__author__ = 'Rocky'

'''

http://30daydo.com

Contact: weigesysu@qq.com

'''

# 每天的涨跌停

import urllib2, re, time, xlrd, xlwt, sys, os

import setting

import pandas as pd

import tushare as ts

from setting import LLogger

reload(sys)

sys.setdefaultencoding('gbk')



logger = LLogger('zdt.log')

class GetZDT:

    def __init__(self,current):

        self.user_agent = "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/64.0.3282.167 Chrome/64.0.3282.167 Safari/537.36"

        # self.today = time.strftime("%Y%m%d")

        self.today=current

        self.path = os.path.join(os.path.dirname(__file__), 'data')

        self.zdt_url = 'http://home.flashdata2.jrj.com.cn/limitStatistic/ztForce/' + self.today + ".js"

        self.zrzt_url = 'http://hqdata.jrj.com.cn/zrztjrbx/limitup.js'



        self.host = "home.flashdata2.jrj.com.cn"

        self.reference = "http://stock.jrj.com.cn/tzzs/z ... ot%3B



        self.header_zdt = {"User-Agent": self.user_agent,

                           "Host": self.host,

                           "Referer": self.reference}



        self.zdt_indexx = [u'代码', u'名称', u'最新价格', u'涨跌幅', u'封成比', u'封流比', u'封单金额', u'最后一次涨停时间', u'第一次涨停时间', u'打开次数',

                           u'振幅',

                           u'涨停强度']



        self.zrzt_indexx = [u'序号', u'代码', u'名称', u'昨日涨停时间', u'最新价格', u'今日涨幅', u'最大涨幅', u'最大跌幅', u'是否连板', u'连续涨停次数',

                            u'昨日涨停强度', u'今日涨停强度', u'是否停牌', u'昨天的日期', u'昨日涨停价', u'今日开盘价格', u'今日开盘涨幅']

        self.header_zrzt = {"User-Agent": self.user_agent,

                            "Host": "hqdata.jrj.com.cn",

                            "Referer": "http://stock.jrj.com.cn/tzzs/zrztjrbx.shtml"

                            }



    def getdata(self, url, headers, retry=5):

        req = urllib2.Request(url=url, headers=headers)

        for i in range(retry):

            try:

                resp = urllib2.urlopen(req,timeout=20)

                content = resp.read()

                md_check = re.findall('summary|lasttradedate',content)

                if content and len(md_check)>0:

                    return content

                else:

                    time.sleep(60)

                    logger.log('failed to get content, retry: {}'.format(i))

                    continue

            except Exception, e:

                logger.log(e)

                time.sleep(60)

                continue

        return None



    def convert_json(self, content):

        p = re.compile(r'"Data":(.*)};', re.S)

        if len(content)<=0:

            logger.log('Content\'s length is 0')

            exit(0)

        result = p.findall(content)

        if result:

            try:

            # print result

                t1 = result[0]

                t2 = list(eval(t1))

                return t2

            except Exception,e:

                logger.log(e)

                return None

        else:

            return None





    def save_to_dataframe(self, data, indexx, choice, post_fix):

        engine = setting.get_engine('db_zdt')

        if not data:

            exit()

        data_len = len(data)

        if choice == 1:

            for i in range(data_len):

                data[choice] = data[choice].decode('gbk')



        df = pd.DataFrame(data, columns=indexx)



        filename = os.path.join(self.path, self.today + "_" + post_fix + ".xls")

        if choice == 1:

            df[u'今天的日期']=self.today

            df.to_excel(filename, encoding='gbk')

            try:

                df.to_sql(self.today + post_fix, engine, if_exists='fail')

            except Exception,e:

                logger.log(e)





    def storedata(self):

        zdt_content = self.getdata(self.zdt_url, headers=self.header_zdt)

        logger.log('zdt Content'+zdt_content)

        zdt_js = self.convert_json(zdt_content)

        self.save_to_dataframe(zdt_js, self.zdt_indexx, 1, 'zdt')

        time.sleep(5)



if __name__ == '__main__':

    date_list = [datetime.datetime.strftime(i,'%Y%m%d') for i in list(pd.date_range('20170401','20171231'))]

    for today in date_list:



        if not ts.is_holiday(datetime.datetime.strptime(today,'%Y%m%d').strftime('%Y-%m-%d')):

            print today

            obj = GetZDT(today)

            obj.storedata()

        else:

            logger.log('Holiday')

# -*- coding=utf-8 -*-

__author__ = 'Rocky'

'''

http://30daydo.com

Contact: weigesysu@qq.com

'''

# 每天的涨跌停

import re

import time

import xlrd

import xlwt

import sys

import os

import setting

from setting import is_holiday, DATA_PATH

import pandas as pd

import tushare as ts

from setting import llogger

import requests

from send_mail import sender_139

import datetime

# reload(sys)

# sys.setdefaultencoding('gbk')



logger = llogger(__file__)





class GetZDT:

    def __init__(self):

        self.user_agent = "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/64.0.3282.167 Chrome/64.0.3282.167 Safari/537.36"

        self.today = time.strftime("%Y%m%d")

        self.path = DATA_PATH

        self.zdt_url = 'http://home.flashdata2.jrj.com.cn/limitStatistic/ztForce/' + \

            self.today + ".js"

        self.zrzt_url = 'http://hqdata.jrj.com.cn/zrztjrbx/limitup.js'



        self.host = "home.flashdata2.jrj.com.cn"

        self.reference = "http://stock.jrj.com.cn/tzzs/z ... ot%3B



        self.header_zdt = {"User-Agent": self.user_agent,

                           "Host": self.host,

                           "Referer": self.reference}



        self.zdt_indexx = [u'代码', u'名称', u'最新价格', u'涨跌幅', u'封成比', u'封流比', u'封单金额', u'最后一次涨停时间', u'第一次涨停时间', u'打开次数',

                           u'振幅',

                           u'涨停强度']



        self.zrzt_indexx = [u'序号', u'代码', u'名称', u'昨日涨停时间', u'最新价格', u'今日涨幅', u'最大涨幅', u'最大跌幅', u'是否连板', u'连续涨停次数',

                            u'昨日涨停强度', u'今日涨停强度', u'是否停牌', u'昨天的日期', u'昨日涨停价', u'今日开盘价格', u'今日开盘涨幅']

        self.header_zrzt = {"User-Agent": self.user_agent,

                            "Host": "hqdata.jrj.com.cn",

                            "Referer": "http://stock.jrj.com.cn/tzzs/zrztjrbx.shtml"

                            }



    def getdata(self, url, headers, retry=5):

        for i in range(retry):

            try:

                resp = requests.get(url=url, headers=headers)

                content = resp.text

                md_check = re.findall('summary|lasttradedate', content)

                if content and len(md_check) > 0:

                    return content

                else:

                    time.sleep(60)

                    logger.info('failed to get content, retry: {}'.format(i))

                    continue

            except Exception as e:

                logger.info(e)

                time.sleep(60)

                continue

        return None



    def convert_json(self, content):

        p = re.compile(r'"Data":(.*)};', re.S)

        if len(content) <= 0:

            logger.info('Content\'s length is 0')

            exit(0)

        result = p.findall(content)

        if result:

            try:

                # print(result)

                t1 = result[0]

                t2 = list(eval(t1))

                return t2

            except Exception as e:

                logger.info(e)

                return None

        else:

            return None



    # 2016-12-27 to do this

    def save_excel(self, date, data):

        # data is list type

        w = xlwt.Workbook(encoding='gbk')

        ws = w.add_sheet(date)

        excel_filename = date + ".xls"

        # sheet=open_workbook(excel_filenme)

        # table=wb.sheets()[0]

        xf = 0

        ctype = 1

        rows = len(data)

        point_x = 1

        point_y = 0

        ws.write(0, 0, u'代码')

        ws.write(0, 1, u'名称')

        ws.write(0, 2, u'最新价格')

        ws.write(0, 3, u'涨跌幅')

        ws.write(0, 4, u'封成比')

        ws.write(0, 5, u'封流比')

        ws.write(0, 6, u'封单金额')

        ws.write(0, 7, u'第一次涨停时间')

        ws.write(0, 8, u'最后一次涨停时间')

        ws.write(0, 9, u'打开次数')

        ws.write(0, 10, u'振幅')

        ws.write(0, 11, u'涨停强度')

        print("Rows:%d" % rows)

        for row in data:

            rows = len(data)

            cols = len(row)

            point_y = 0

            for col in row:

                # print(col)

                # table.put_cell(row,col,)

                # print(col)

                ws.write(point_x, point_y, col)

                # print("[%d,%d]" % (point_x, point_y))

                point_y = point_y + 1



            point_x = point_x + 1



        w.save(excel_filename)



    def save_to_dataframe(self, data, indexx, choice, post_fix):

        engine = setting.get_engine('db_zdt')

        if not data:

            exit()

        data_len = len(data)

        if choice == 1:

            for i in range(data_len):

                data[i][choice] = data[i][choice]



        df = pd.DataFrame(data, columns=indexx)



        filename = os.path.join(

            self.path, self.today + "_" + post_fix + ".xls")



        # 今日涨停

        if choice == 1:

            df['今天的日期'] = self.today

            df.to_excel(filename, encoding='gbk')

            try:

                df.to_sql(self.today + post_fix, engine, if_exists='fail')

            except Exception as e:

                logger.info(e)

        # 昨日涨停

        if choice == 2:

            df = df.set_index(u'序号')

            df[u'最大涨幅'] = df[u'最大涨幅'].map(lambda x: round(x * 100, 3))

            df[u'最大跌幅'] = df[u'最大跌幅'].map(lambda x: round(x * 100, 3))

            df[u'今日开盘涨幅'] = df[u'今日开盘涨幅'].map(lambda x: round(x * 100, 3))

            df[u'昨日涨停强度'] = df[u'昨日涨停强度'].map(lambda x: round(x, 0))

            df[u'今日涨停强度'] = df[u'今日涨停强度'].map(lambda x: round(x, 0))

            try:

                df.to_sql(self.today + post_fix, engine, if_exists='fail')

            except Exception as e:

                logger.info(e)



            avg = round(df['今日涨幅'].mean(), 2)

            current = datetime.datetime.now().strftime('%Y-%m-%d')

            title = '昨天涨停个股今天{}\n的平均涨幅{}\n'.format(current, avg)

            try:

                sender_139(title, title)

            except Exception as e:

                print(e)



    # 昨日涨停今日的状态，今日涨停



    def storedata(self):

        zdt_content = self.getdata(self.zdt_url, headers=self.header_zdt)

        logger.info('zdt Content' + zdt_content)

        zdt_js = self.convert_json(zdt_content)

        self.save_to_dataframe(zdt_js, self.zdt_indexx, 1, 'zdt')

        time.sleep(0.5)

        zrzt_content = self.getdata(self.zrzt_url, headers=self.header_zrzt)

        logger.info('zrzt Content' + zdt_content)



        zrzt_js = self.convert_json(zrzt_content)

        self.save_to_dataframe(zrzt_js, self.zrzt_indexx, 2, 'zrzt')





if __name__ == '__main__':

    # today='2018-04-16'

    # 填补以前的数据

    # x=pd.date_range('20170101','20180312')

    # date_list = [datetime.datetime.strftime(i,'%Y%m%d') for i in list(pd.date_range('20170401','20171231'))



    if is_holiday():

        logger.info('Holiday')

        exit()

    logger.info("start")

    obj = GetZDT()

    obj.storedata()

#-*-coding=utf-8

'''

可转债监控

'''

import tushare as ts

from setting import get_engine

engine = get_engine('db_bond')

import pandas as pd

import datetime

class ConvertBond():



    def __init__(self):

        self.conn=ts.get_apis()

        self.allBonds=ts.new_cbonds(pause=2)

        self.onSellBond=self.allBonds.dropna(subset=['marketprice'])

        self.today=datetime.datetime.now().strftime('%Y-%m-%d %H:%M')



    def stockPrice(self,code):

        stock_df = ts.get_realtime_quotes(code)

        price = float(stock_df['price'].values[0])

        return price



    def dataframe(self):

        price_list=[]

        for code in self.onSellBond['scode']:

            price_list.append(self.stockPrice(code))

        self.onSellBond['stock_price']=price_list

        self.onSellBond['ratio'] = (

self.onSellBond['marketprice']

/(self.onSellBond['stock_price'] / self.onSellBond['convprice'])-1)*100

        self.onSellBond['Updated']=self.today

        self.onSellBond.to_sql('tb_bond',engine,if_exists='replace')



    def closed(self):

        ts.close_apis(self.conn)



def main():

    bond=ConvertBond()

    bond.dataframe()

    bond.closed()

if __name__=='__main__':

    main()

import os

import MySQLdb

MYSQL_USER = *********

MYSQL_PASSWORD = ********

MYSQL_HOST = *********

MYSQL_PORT = *****



def get_engine(db):

    engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, db))

    return engine

import tushare as ts

#获取2017年第3季度的盈利能力数据

ts.get_profit_data(2017,3)

def profit(code,start,end):

    try:

        df=ts.get_k_data(code,start=start,end=end)

    except Exception,e:

        print e

        return None

    try:

        p=(df['close'].iloc[-1]-df['close'].iloc[0])/df['close'].iloc[0]*100.00

    except Exception,e:

        print e

        return None 

    return round(p,2)

profit('300678','2016-12-31','2017-12-31')

def price_change():

    basic=ts.get_stock_basics()

    pro=



    for code in basic.index.values:

        print code

        p=profit(code,'2016-12-31','2017-12-31')

        pro.append(p)

    basic['price_change']=pro

    basic.to_csv('2017_all_price_change.xls',encoding='gbk')

    df=pd.read_csv('2017_all_price_change.xls',encoding='gbk')

    df.to_excel('2017_all_price_change.xls',encoding='gbk')

def analysis():

    df=pd.read_excel('2017_all_price_change.xls',encoding='gbk')

    df=df.sort_values(by='price_change',ascending=False)

    df.to_excel('2017-year.xls',encoding='gbk')

engine=get_engine('stock')

df.to_sql('2017years',engine)

def get_engine(db):

    engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, db))

    return engine

• 2017-year.zip

    df = pd.DataFrame({'A': [1, 2, 3, 4, 5], 'B': [10, 20, 30, 40, 50]})

    df2 = pd.DataFrame({'A': [6], 'B': [60]})

    print 'df\n', df

    print 'df2\n', df2



    df_x = [df, df2]

    result = pd.concat(df_x)

    print 'first result\n', result

result2= result.reset_index()

result2 = result.reset_index(drop=True)

result3 = result.reindex(columns=['A','C'])

    df = pd.DataFrame({'A': [1, 2, 3, 4, 5], 'B': [10, 20, 30, 40, 50]})

    df2 = pd.DataFrame({'A': [6], 'B': [60]})

    print 'df\n', df

    print 'df2\n', df2

    df_x = [df, df2]

    result = pd.concat(df_x)

    print 'first result\n', result

    basic=ts.get_stock_basics()

    

    print basic.info()

    print basic.head(10)



    basic.to_excel('basic2017.xls',encoding='gbk')

    basic.to_csv('2017_all_price_change.xls',encoding='gbk')

    df=pd.read_csv('2017_all_price_change.xls',encoding='gbk')

    df.to_excel('2017_all_price_change.xls',encoding='gbk')

	df=pd.read_excel('600050.xls',dtype={'datetime':np.datetime64})

	df=df.set_index('datetime')

	df['close'].plot(use_index=True,grid=True)

	plt.show()