jupyter/股票管理.ipynb

股票管理

股票信息导入

In [ ]:

import baostock as bs
import pandas as pd

#### 登陆系统 ####
lg = bs.login()
# 显示登陆返回信息
print('login respond error_code:'+lg.error_code)
print('login respond  error_msg:'+lg.error_msg)

#### 获取证券信息 ####
rs = bs.query_all_stock(day="2020-10-20")
print('query_all_stock respond error_code:'+rs.error_code)
print('query_all_stock respond  error_msg:'+rs.error_msg)

#### 打印结果集 ####
data_list = []
while (rs.error_code == '0') & rs.next():
    # 获取一条记录，将记录合并在一起
    data_list.append(rs.get_row_data())
#results = pd.DataFrame(data_list, columns=rs.fields)

#### 结果集输出到csv文件 ####   
#result.to_csv("all_stock.csv", encoding="utf-8", index=False)
for result in data_list:
    print(result)

#### 登出系统 ####
bs.logout()

In [ ]:

import baostock as bs
import pandas as pd
import pymysql

# 登陆系统
lg = bs.login()
# 显示登陆返回信息
print('login respond error_code:'+lg.error_code)
print('login respond  error_msg:'+lg.error_msg)

# 获取证券基本资料
rs = bs.query_stock_basic(code="")
# rs = bs.query_stock_basic(code_name="浦发银行")  # 支持模糊查询
print('query_stock_basic respond error_code:'+rs.error_code)
print('query_stock_basic respond  error_msg:'+rs.error_msg)

# 打印结果集
data_list = []
while (rs.error_code == '0') & rs.next():
    # 获取一条记录，将记录合并在一起
    data_list.append(rs.get_row_data())
#result = pd.DataFrame(data_list, columns=rs.fields)
# 结果集输出到csv文件
#result.to_csv("D:/stock_basic.csv", encoding="gbk", index=False)

db = pymysql.connect("localhost","root","songyi","stock" )
cursor = db.cursor()

sql = "insert into stock_info (code,name,ipoDate,outDate,type,status) values(%s,%s,%s,%s,%s,%s)"
try:
    cursor.executemany(sql,tuple(data_list))
    db.commit()
    print("ok!")
except:
    # 如果发生错误则回滚
    print("error!")
    db.rollback()   
db.close()
# 登出系统
bs.logout()

每日持有标的信息导入

In [ ]:

import csv
import pymysql

def read_data(filename):
    detail = {}
    with open(filename) as f:
        reader = csv.reader(f)
        #header_row =next(reader)
        for row in reader:
            detail.setdefault(row[0],[])
            detail[row[0]].append(row[1])
    return detail
m_xx = []
m_stock = {}
db = pymysql.connect("localhost","root","songyi","stock" )
cursor = db.cursor()
sql = 'select code,name from stock_info where type="1"'
cursor.execute(sql)
results = cursor.fetchall()
for result in results:
    m_stock[result[0]] = result[1]
#print(m_stock)
filename = '每日标的信息.csv'
detail = read_data(filename)
for k,v in detail.items():
    m_rq = '2020-' + k[0:2] + '-' + k[2:]
    i = 1
    for m_dm in v:
        if m_dm[0:1] == '6':
            m_dm = 'sh.' + m_dm
        else:
            m_dm = 'sz.' + m_dm
        print('\t'+m_dm + '\t'+m_stock[m_dm])
        m_xx.append((m_rq,m_dm,i))
        i += 1
choice = input('以上为本日数据，是否导入？（y/n）')
if choice.upper() == "Y":
    sql = "insert into daily_item (rq,code,ord) values(%s,%s,%s)"
    try:
        cursor.executemany(sql,m_xx)
        db.commit()
        print("ok!")
    except:
    # 如果发生错误则回滚
        print("error!")
        db.rollback()   
db.close()

每日调仓信息导入

In [ ]:

import csv
import pymysql

def read_data(filename):
    detail = {}
    
    with open(filename) as f:
        reader = csv.reader(f)
#        header_row =next(reader)
        for row in reader:
            detail1 = {}
            detail.setdefault(row[3],[])
            detail1 = {'dm':row[0],'zj':row[1],'ly':row[2],'cb':row[4]}
            detail[row[3]].append(detail1)
    return detail
m_xx = []
m_add = []
m_sub = []
db = pymysql.connect("localhost","root","songyi","stock" )
cursor = db.cursor()
filename = '调仓明细.csv'
detail = read_data(filename)
#print(detail)
for rq in sorted(detail.keys()):
    m_rq = '2020-' + rq[0:2] + '-' + rq[2:]
    for xx_move in detail[rq]:
        m_dm = xx_move['dm']
        if m_dm[0:1] == '6':
            m_dm = 'sh.' + m_dm
        else:
            m_dm = 'sz.' + m_dm
        m_xx.append((m_rq,m_dm,int(xx_move['zj']),xx_move['ly']))
        if xx_move['zj'] == '1':
            m_add.append((m_dm,xx_move['ly'],float(xx_move['cb'])))
        else:
            m_sub.append((m_dm))
#print(m_xx)
sql = "insert into change_item (rq,code,pos,reason) values(%s,%s,%s,%s)"
try:
    cursor.executemany(sql,m_xx)
    if len(m_add) > 0:
        sql_add = "insert into stock_item (code,reason,cost) values(%s,%s,%s)"
        cursor.executemany(sql_add,m_add)
    if len(m_sub) > 0:
        sql_sub = "update stock_item set status=0 where code=%s"
        cursor.executemany(sql_sub,m_sub)    
    db.commit()
    print("导入成功!")
except:
    # 如果发生错误则回滚
    print("error!")
    db.rollback()   

db.close()

持仓股票信息导入

In [ ]:

import pymysql
db = pymysql.connect("localhost","root","songyi","stock" )
cursor = db.cursor()
sql = 'SELECT a.unit,a.CODE,b.cost FROM daily_item AS a,daily_cost as b WHERE a.rq=(select max(rq) FROM daily_item) AND a.code=b.code'
cursor.execute(sql)
results = cursor.fetchall()
sql = "insert into stock_item(item,code,cost) values(%s,%s,%s)"
try:
    cursor.executemany(sql,list(results))
    #db.commit()
    print("ok!")
except:
    # 如果发生错误则回滚
    print("error!")
    db.rollback()   
sql = 'SELECT a.reason,a.code from change_item AS a WHERE a.pos=1'
cursor.execute(sql)
results = cursor.fetchall()
sql = "update stock_item set reason=%s where code=%s "
try:
    cursor.executemany(sql,list(results))
    #db.commit()
    print("ok!")
except:
    # 如果发生错误则回滚
    print("error!")
    db.rollback()   
db.close()

外汇实时数据采集

In [ ]:

import requests
import time
from bs4 import BeautifulSoup
import pymongo
import re

#pattern = re.compile('(?<=\").*(?=\")')
pattern = re.compile(r'\"(.*)\"')
#myclient = pymongo.MongoClient('mongodb://localhost:27017/')
#mydb = myclient["gaokao"]
#mycol = mydb["news"]
url = 'http://hq.sinajs.cn/list=USDCAD'
strhtml = requests.get(url)
#strhtml.encoding = 'utf8'
soup = BeautifulSoup(strhtml.text,'lxml')
data = strhtml.text
#data1 = data.split("\r")
#data = soup.select('schoolList')
#for item1 in data:
#    print(item1.get_text())
if pattern.findall(data):
    for data1 in pattern.findall(data):
        data2 = data1.split(',')
        print(data2)
        print('当前买入价：',data2[1])
        print('当前卖出价：',data2[2])
        print('昨收价：',data2[3])
        print('今开价：',data2[5])
        print('最高价：',data2[6])
        print('最低价：',data2[7])
        print(float(data2[7]))

In [ ]:

In [ ]:

from email.mime.text import MIMEText
from email.header import Header
import smtplib
import requests
import time
import re

pattern = re.compile(r'\"(.*)\"')
url = 'http://hq.sinajs.cn/list=USDCAD'
strhtml = requests.get(url)
data = strhtml.text
if pattern.findall(data):
    for data1 in pattern.findall(data):
        data2 = data1.split(',')
#print(data2)
with open('price.txt','r') as fl:
    for line in fi:
        p_high = line.split(',')[0]
        p_low = line.split(',')[1]

message ='当前美元加元买入价：{}，卖出价：{}'.format(data2[1],data2[2])
msg = MIMEText(message,'plain','utf-8')
msg['Subject'] = Header("外汇价格已经到达预期价位！",'utf-8')
msg['From'] = Header('512song@sina.com')
msg['To'] = Header('491525765@qq.com','utf-8')

from_addr = '512song@sina.com' #发件邮箱
password = '409fe5d8471da663' #邮箱密码
to_addr = 'songyi@yeah.net' #收件邮箱
smtp_server = 'smtp.sina.com' #SMTP服务器，以新浪为例
try:
    server = smtplib.SMTP(smtp_server,25) #第二个参数为默认端口为25，有些邮件有特殊端口
    print('开始登录')
    server.set_debuglevel(1) 
    server.login(from_addr,password) #登录邮箱
    print('登录成功')
    print("邮件开始发送")
    server.sendmail(from_addr,to_addr,msg.as_string())  #将msg转化成string发出
    server.quit()
    print("邮件发送成功")
except smtplib.SMTPException as e:
    print("邮件发送失败",e)

In [ ]:

from email.mime.text import MIMEText
from email.header import Header
import smtplib
import requests
import time
import re

pattern = re.compile(r'\"(.*)\"')
url = 'http://hq.sinajs.cn/list=USDCAD'
strhtml = requests.get(url)
data = strhtml.text
if pattern.findall(data):
    for data1 in pattern.findall(data):
        data2 = data1.split(',')
print(data2)

message ='当前美元加元最高价：{}，最低价：{}'.formtat()
msg = MIMEText(message,'plain','utf-8')

msg['Subject'] = Header("测试smtp邮件",'utf-8')
msg['From'] = Header('512song@sina.com')
msg['To'] = Header('491525765@qq.com','utf-8')

from_addr = '512song@sina.com' #发件邮箱
password = '409fe5d8471da663' #邮箱密码
to_addr = '491525765@qq.com' #收件邮箱

smtp_server = 'smtp.sina.com' #SMTP服务器，以新浪为例
try:
    server = smtplib.SMTP(smtp_server,25) #第二个参数为默认端口为25，有些邮件有特殊端口
    print('开始登录')
    server.set_debuglevel(1) 
    server.login(from_addr,password) #登录邮箱
    print('登录成功')
    print("邮件开始发送")
    server.sendmail(from_addr,to_addr,msg.as_string())  #将msg转化成string发出
    server.quit()
    print("邮件发送成功")
except smtplib.SMTPException as e:
    print("邮件发送失败",e)

财务报表获取

In [ ]:

import akshare as ak

stock_financial_debt_ths_df = ak.stock_financial_debt_ths(symbol="000338", indicator="按年度")
print(stock_financial_debt_ths_df)

In [ ]:

import akshare as ak

stock_financial_report_sina_df = ak.stock_financial_report_sina(stock="000338", symbol="资产负债表")
print(stock_financial_report_sina_df)

In [62]:

import akshare as ak

stock_financial_abstract_ths_df = ak.stock_financial_abstract_ths(symbol="000338", indicator="按报告期")
data1 = stock_financial_abstract_ths_df
a1 = data1.loc[ data1.报告期 =='2023-12-31']
for value in a1:
    print(value)

报告期
净利润
净利润同比增长率
扣非净利润
扣非净利润同比增长率
营业总收入
营业总收入同比增长率
基本每股收益
每股净资产
每股资本公积金
每股未分配利润
每股经营现金流
销售净利率
销售毛利率
净资产收益率
净资产收益率-摊薄
营业周期
存货周转率
存货周转天数
应收账款周转天数
流动比率
速动比率
保守速动比率
产权比率
资产负债率

In [77]:

import akshare as ak

stock_financial_debt_ths_df = ak.stock_financial_debt_ths(symbol="000407", indicator="按报告期")
print(stock_financial_debt_ths_df)

            报告期 报表核心指标 *所有者权益（或股东权益）合计     *资产合计     *负债合计 *归属于母公司所有者权益合计  \
0    2024-03-31                 32.83亿    69.36亿    36.53亿         29.06亿   
1    2023-12-31                 32.53亿    69.56亿    37.03亿         28.84亿   
2    2023-09-30                 31.75亿    66.75亿    35.00亿         28.18亿   
3    2023-06-30                 31.88亿    71.97亿    40.08亿         27.95亿   
4    2023-03-31                 31.30亿    70.27亿    38.97亿         27.52亿   
..          ...    ...             ...       ...       ...            ...   
99   1996-12-31                  1.80亿     3.06亿     1.26亿          1.65亿   
100  1996-06-30                  1.56亿     2.60亿     1.04亿          1.41亿   
101  1995-12-31               8217.50万     1.66亿  8384.17万       6716.24万   
102  1994-12-31               6585.60万     1.17亿  5120.34万       6467.31万   
103  1993-12-31               2460.94万  3472.74万  1011.79万       2460.94万   

    报表全部指标 流动资产      货币资金  交易性金融资产  ... 所有者权益（或股东权益） 实收资本（或股本）      资本公积  \
0                   6.44亿    False  ...                  8.80亿     9.54亿   
1                   7.67亿    False  ...                  8.80亿     9.54亿   
2                   6.47亿    False  ...                  8.80亿     9.53亿   
3                  10.33亿    False  ...                  8.80亿     9.53亿   
4                   7.29亿    False  ...                  8.80亿     9.53亿   
..     ...  ...       ...      ...  ...          ...       ...       ...   
99               3507.95万  700.00万  ...               7865.00万  6117.91万   
100                 False    False  ...               6050.00万  6110.36万   
101              1591.58万    False  ...               5000.00万  1287.21万   
102              2316.67万    False  ...               5000.00万  1287.21万   
103               296.69万    False  ...               1919.72万     False   

       其他综合收益     盈余公积     未分配利润 归属于母公司所有者权益合计    少数股东权益 所有者权益（或股东权益）合计  \
0    5833.01万    1.92亿     8.07亿        29.06亿     3.77亿         32.83亿   
1    5825.59万    1.92亿     7.87亿        28.84亿     3.69亿         32.53亿   
2    3869.46万    1.64亿     7.65亿        28.18亿     3.58亿         31.75亿   
3    3873.28万    1.64亿     7.44亿        27.95亿     3.93亿         31.88亿   
4    3871.64万    1.64亿     7.02亿        27.52亿     3.78亿         31.30亿   
..        ...      ...       ...           ...       ...            ...   
99      False  807.23万  1678.16万         1.65亿  1554.03万          1.80亿   
100     False  429.03万  1500.51万         1.41亿  1470.25万          1.56亿   
101     False  429.03万     False      6716.24万  1501.27万       8217.50万   
102     False  152.04万     False      6467.31万   118.29万       6585.60万   
103     False  3000.00   540.93万      2460.94万     False       2460.94万   

    负债和所有者权益（或股东权益）合计  
0              69.36亿  
1              69.56亿  
2              66.75亿  
3              71.97亿  
4              70.27亿  
..                ...  
99              3.06亿  
100             2.60亿  
101             1.66亿  
102             1.17亿  
103          3472.74万  

[104 rows x 76 columns]

In [63]:

import akshare as ak

stock_financial_cash_ths_df = ak.stock_financial_cash_ths(symbol="000063", indicator="按报告期")
print(stock_financial_cash_ths_df)

           报告期 报表核心指标 *现金及现金等价物净增加额 *经营活动产生的现金流量净额 *投资活动产生的现金流量净额  \
0   2024-03-31              -60.70亿         29.83亿       -150.61亿   
1   2023-12-31               39.41亿        174.06亿       -209.01亿   
2   2023-09-30               94.28亿         92.62亿        -43.20亿   
3   2023-06-30               78.75亿         64.26亿        -15.06亿   
4   2023-03-31               26.73亿         23.26亿        -50.84亿   
..         ...    ...           ...            ...            ...   
89  2000-12-31                3.18亿      -7071.17万         -3.18亿   
90  2000-06-30                1.73亿         -1.90亿         -2.30亿   
91  1999-12-31             4665.82万         -5.24亿         -2.35亿   
92  1998-12-31            -1388.42万          1.58亿         -1.44亿   
93  1998-06-30               -1.28亿       2923.72万      -6786.66万   

   *筹资活动产生的现金流量净额 *期末现金及现金等价物余额 报表全部指标 一、经营活动产生的现金流量 销售商品、提供劳务收到的现金  ...  \
0          59.59亿       449.44亿                             335.98亿  ...   
1          73.72亿       510.13亿                            1282.64亿  ...   
2          42.17亿       565.00亿                             936.39亿  ...   
3          27.18亿       549.46亿                             648.24亿  ...   
4          55.17亿       497.44亿                             328.30亿  ...   
..            ...           ...    ...           ...            ...  ...   
89          7.05亿         False                              59.76亿  ...   
90          5.90亿         False                              18.19亿  ...   
91          8.07亿         False                              23.89亿  ...   
92      -2747.59万         False                              26.06亿  ...   
93      -8964.03万         False                               6.94亿  ...   

   经营性应付项目的增加        其他 间接法-经营活动产生的现金流量净额 2、不涉及现金收支的重大投资和筹资活动：  \
0       False     False             False                        
1     -11.97亿     False           174.06亿                        
2       False     False             False                        
3     -47.23亿     False            64.26亿                        
4       False     False             False                        
..        ...       ...               ...                  ...   
89     19.71亿  1179.35万         -7071.17万                        
90      2.16亿     False            -1.90亿                        
91  -2849.51万     False            -5.24亿                        
92      4.49亿     9.16万             1.58亿                        
93      1.50亿  3133.53万          2923.72万                        

   3、现金及现金等价物净变动情况：  现金的期末余额 减：现金的期初余额 加：现金等价物的期末余额 减：现金等价物的期初余额  \
0                      False     False        False        False   
1                    203.40万   160.40万      510.11亿      470.70亿   
2                      False     False        False        False   
3                    119.00万   160.40万      549.45亿      470.70亿   
4                      False     False        False        False   
..              ...      ...       ...          ...          ...   
89                     8.73亿     5.62亿      736.77万        False   
90                     7.41亿     5.68亿        False        False   
91                     5.68亿     5.21亿        False        False   
92                     5.21亿     5.35亿        False        False   
93                     4.07亿     5.35亿        False        False   

   间接法-现金及现金等价物净增加额  
0             False  
1            39.41亿  
2             False  
3            78.75亿  
4             False  
..              ...  
89            3.18亿  
90            1.73亿  
91         4665.82万  
92        -1388.42万  
93           -1.28亿  

[94 rows x 75 columns]

财务报表获取(资产负债)

In [ ]:

import openpyxl
import json
import akshare as ak

fi_xls = 'data/山东辖区境内上市公司名单.xlsx'
dict1 = {}
list1 = ['2021-12-31','2022-12-31','2023-12-31','2024-03-31']

wb = openpyxl.load_workbook(fi_xls)
sheet = wb.active
list2 = ['*资产合计','货币资金','交易性金融资产','*负债合计']
for n in range(2,sheet.max_row+1):    
    code = str(sheet.cell(n,2).value).rjust(6,"0")
    dict1.setdefault(code,{})
    name = sheet.cell(n,3).value
    address = sheet.cell(n,4).value
    phone = sheet.cell(n,5).value
    dict1[code]['name'] = name
    dict1[code]['address'] = address
    dict1[code]['phone'] = phone
for k, v in dict1.items():
    data = ak.stock_financial_debt_ths(symbol=k, indicator="按报告期")
    for item in list1:
        data1 = data.loc[ data.报告期 ==item]
        if data1.shape[0] >0:
            dict1[k].setdefault(item,{})
            for item1 in list2:
                if item1 in data1.keys():
                    for value in data1[item1]:
                        dict1[k][item][item1] = value
                else:
                    dict1[k][item][item1] = 'null'
                    
with open('data/山东辖区境内上市公司相关财务数据.json','w') as fl2:
    json.dump(dict1,fl2,ensure_ascii=False)       

财务报表获取(利润)

In [ ]:

filename = 'data/山东辖区境内上市公司相关财务数据.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = ['2021-12-31','2022-12-31','2023-12-31','2024-03-31']
list2 = ['营业总收入','净利润']
for k, v in dict1.items():
    data = ak.stock_financial_abstract_ths(symbol=k, indicator="按报告期")
    for item in list1:
        data1 = data.loc[ data.报告期 ==item]
        if data1.shape[0] >0:
            dict1[k].setdefault(item,{})
            for item1 in list2:
                if item1 in data1.keys():
                    for value in data1[item1]:
                        dict1[k][item][item1] = value
                else:
                    dict1[k][item][item1] = 'null'
                    
with open('data/山东辖区境内上市公司相关财务数据1.json','w') as fl2:
    json.dump(dict1,fl2,ensure_ascii=False)       

财务报表获取(现金流量)

In [64]:

filename = 'data/山东辖区境内上市公司相关财务数据1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
list1 = ['2021-12-31','2022-12-31','2023-12-31','2024-03-31']
list2 = ['*经营活动产生的现金流量净额']
for k, v in dict1.items():
    data = ak.stock_financial_cash_ths(symbol=k, indicator="按报告期")
    for item in list1:
        data1 = data.loc[ data.报告期 ==item]
        if data1.shape[0] >0:
            dict1[k].setdefault(item,{})
            for item1 in list2:
                if item1 in data1.keys():
                    for value in data1[item1]:
                        dict1[k][item][item1] = value
                else:
                    dict1[k][item][item1] = 'null'
                    
with open('data/山东辖区境内上市公司相关财务数据1.json','w') as fl2:
    json.dump(dict1,fl2,ensure_ascii=False)      

导出数据至excel

In [75]:

filename = 'data/山东辖区境内上市公司相关财务数据1.json'
with open(filename,'r') as fl:
    dict1 = json.load(fl)
filename = 'data/山东辖区境内上市公司相关财务数据1.xlsx'
wb = openpyxl.Workbook()
sheet = wb.active
list1 = ['2021-12-31','2022-12-31','2023-12-31','2024-03-31']
i = 0
for k, v in dict1.items():
    
    sheet.merge_cells(f'A{i*4+1}:A{i*4+4}')
    sheet.merge_cells(f'B{i*4+1}:B{i*4+4}')
    sheet.merge_cells(f'C{i*4+1}:C{i*4+4}')
    sheet[f'A{i*4+1}'] = i+1
    sheet[f'B{i*4+1}'] = k
    sheet[f'C{i*4+1}'] = v['name']
    n = 1
    for item in list1:
        
        if item in v.keys():
            sheet[f'D{i*4+n}'] = item
            sheet[f'E{i*4+n}'] = v[item]['*资产合计']
            sheet[f'F{i*4+n}'] = v[item]['货币资金']
            sheet[f'G{i*4+n}'] = v[item]['交易性金融资产']
            sheet[f'H{i*4+n}'] = v[item]['*负债合计']
            sheet[f'I{i*4+n}'] = v[item]['营业总收入']
            sheet[f'J{i*4+n}'] = v[item]['净利润']
            sheet[f'K{i*4+n}'] = v[item]['*经营活动产生的现金流量净额']
        else:
            sheet[f'D{i*4+n}'] = item
            sheet[f'E{i*4+n}'] = ''
            sheet[f'F{i*4+n}'] = ''
            sheet[f'G{i*4+n}'] = ''
            sheet[f'H{i*4+n}'] = ''
            sheet[f'I{i*4+n}'] = ''
            sheet[f'J{i*4+n}'] = ''
            sheet[f'K{i*4+n}'] = ''
        n+=1
    i+=1
wb.save(filename)    
       

In [ ]:

pygal图表

In [ ]:

import pygal
bar_chart = pygal.Bar(height=300)
bar_chart.add('Fibonacci', [0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55])
bar_chart.add('Padovan', [1, 1, 1, 2, 2, 3, 4, 5, 7, 9, 12])
svg = bar_chart.render()

In [ ]:

from IPython.display import SVG
SVG(svg)

In [ ]: