外部库管理
安装库
pip安装
brew install ta-lib # 必须用brew安装ta-lib运行库
pip install wheel
pip install matplotlib
pip install numpy
pip install pandas
pip install pymysql
pip install TA-Lib
conda安装
不用brew安装ta-lib运行库。
conda install -c conda-forge TA-Lib
导入库
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import pymysql as pms
import talib as tl
Numpy
import numpy as np
Pandas使用
Series
series = pd.Series([85, 95, 90, 96])
print(series)
0 85
1 95
2 90
3 96
dtype: int64
print("index: {} : {}".format(type(series.index), series.index))
print("values: {} : {}".format(type(series.values), series.values))
print("dtype: {}: {}".format(type(series.dtype), series.dtype))
index: <class 'pandas.core.indexes.range.RangeIndex'> : RangeIndex(start=0, stop=4, step=1)
values: <class 'numpy.ndarray'> : [85 95 90 96]
dtype: <class 'numpy.dtype'>: int64
print("name: {}".format(series.name))
print("index.name: {}".format(series.index.name))
name: None
index.name: None
运算符
print(series.values)
print((series + 4).values)
print((series * 2).values)
[85 95 90 96]
[ 89 99 94 100]
[170 190 180 192]
append()
s1 = pd.Series([1, 2, 3, 4])
s2 = pd.Series([7, 6, 5])
s3 = s1.append(s2)
print(s1)
print(s2)
print(s3)
0 1
1 2
2 3
3 4
dtype: int64
0 7
1 6
2 5
dtype: int64
0 1
1 2
2 3
3 4
0 7
1 6
2 5
dtype: int64
count():求元素个数
print(s1.count())
4
describe()
print(s1.describe())
count 4.000000
mean 2.500000
std 1.290994
min 1.000000
25% 1.750000
50% 2.500000
75% 3.250000
max 4.000000
dtype: float64
mean():取平均值
print(s1.mean())
2.5
min():最小值
print(s1.min())
1
max():最大值
print(s1.max())
4
sum():求和
print(s1.sum())
10
var():求均方差
print(s1.var())
1.6666666666666667
std():标准差
print(s1.std())
1.2909944487358056
reset_index()
s4 = s3.reset_index()
print(s4)
print(type(s4))
index 0
0 0 1
1 1 2
2 2 3
3 3 4
4 0 7
5 1 6
6 2 5
<class 'pandas.core.frame.DataFrame'>
reindex()
s3.reindex()
0 1
1 2
2 3
3 4
0 7
1 6
2 5
dtype: int64
sort_index()
s3.sort_index()
0 1
0 7
1 2
1 6
2 3
2 5
3 4
dtype: int64
sort_values()
s3.sort_values()
0 1
1 2
2 3
3 4
2 5
1 6
0 7
dtype: int64
Dataframe
import pandas as pd
df = pd.read_csv("./data/AAPL.csv", index_col = 'Date') # index_col = False means no index column.
df
Open | High | Low | Close | Adj Close | Volume | |
---|---|---|---|---|---|---|
Date | ||||||
1999-12-31 | 0.901228 | 0.918527 | 0.888393 | 0.917969 | 0.791887 | 163811200 |
2000-01-03 | 0.936384 | 1.004464 | 0.907924 | 0.999442 | 0.862169 | 535796800 |
2000-01-04 | 0.966518 | 0.987723 | 0.903460 | 0.915179 | 0.789480 | 512377600 |
2000-01-05 | 0.926339 | 0.987165 | 0.919643 | 0.928571 | 0.801032 | 778321600 |
2000-01-06 | 0.947545 | 0.955357 | 0.848214 | 0.848214 | 0.731712 | 767972800 |
... | ... | ... | ... | ... | ... | ... |
2021-01-22 | 136.279999 | 139.850006 | 135.020004 | 139.070007 | 139.070007 | 113907200 |
2021-01-25 | 143.070007 | 145.089996 | 136.539993 | 142.919998 | 142.919998 | 157611700 |
2021-01-26 | 143.600006 | 144.300003 | 141.369995 | 143.160004 | 143.160004 | 98390600 |
2021-01-27 | 143.429993 | 144.300003 | 140.410004 | 142.059998 | 142.059998 | 140843800 |
2021-01-28 | 139.520004 | 141.990005 | 136.699997 | 137.089996 | 137.089996 | 141876400 |
5303 rows × 6 columns
TA-Lib使用
import talib as tl
dir(tl)
['ACOS',
'AD',
'ADD',
'ADOSC',
'ADX',
'ADXR',
'APO',
'AROON',
'AROONOSC',
'ASIN',
'ATAN',
'ATR',
'AVGPRICE',
'BBANDS',
'BETA',
'BOP',
'CCI',
'CDL2CROWS',
'CDL3BLACKCROWS',
'CDL3INSIDE',
'CDL3LINESTRIKE',
'CDL3OUTSIDE',
'CDL3STARSINSOUTH',
'CDL3WHITESOLDIERS',
'CDLABANDONEDBABY',
'CDLADVANCEBLOCK',
'CDLBELTHOLD',
'CDLBREAKAWAY',
'CDLCLOSINGMARUBOZU',
'CDLCONCEALBABYSWALL',
'CDLCOUNTERATTACK',
'CDLDARKCLOUDCOVER',
'CDLDOJI',
'CDLDOJISTAR',
'CDLDRAGONFLYDOJI',
'CDLENGULFING',
'CDLEVENINGDOJISTAR',
'CDLEVENINGSTAR',
'CDLGAPSIDESIDEWHITE',
'CDLGRAVESTONEDOJI',
'CDLHAMMER',
'CDLHANGINGMAN',
'CDLHARAMI',
'CDLHARAMICROSS',
'CDLHIGHWAVE',
'CDLHIKKAKE',
'CDLHIKKAKEMOD',
'CDLHOMINGPIGEON',
'CDLIDENTICAL3CROWS',
'CDLINNECK',
'CDLINVERTEDHAMMER',
'CDLKICKING',
'CDLKICKINGBYLENGTH',
'CDLLADDERBOTTOM',
'CDLLONGLEGGEDDOJI',
'CDLLONGLINE',
'CDLMARUBOZU',
'CDLMATCHINGLOW',
'CDLMATHOLD',
'CDLMORNINGDOJISTAR',
'CDLMORNINGSTAR',
'CDLONNECK',
'CDLPIERCING',
'CDLRICKSHAWMAN',
'CDLRISEFALL3METHODS',
'CDLSEPARATINGLINES',
'CDLSHOOTINGSTAR',
'CDLSHORTLINE',
'CDLSPINNINGTOP',
'CDLSTALLEDPATTERN',
'CDLSTICKSANDWICH',
'CDLTAKURI',
'CDLTASUKIGAP',
'CDLTHRUSTING',
'CDLTRISTAR',
'CDLUNIQUE3RIVER',
'CDLUPSIDEGAP2CROWS',
'CDLXSIDEGAP3METHODS',
'CEIL',
'CMO',
'CORREL',
'COS',
'COSH',
'DEMA',
'DIV',
'DX',
'EMA',
'EXP',
'FLOOR',
'HT_DCPERIOD',
'HT_DCPHASE',
'HT_PHASOR',
'HT_SINE',
'HT_TRENDLINE',
'HT_TRENDMODE',
'KAMA',
'LINEARREG',
'LINEARREG_ANGLE',
'LINEARREG_INTERCEPT',
'LINEARREG_SLOPE',
'LN',
'LOG10',
'MA',
'MACD',
'MACDEXT',
'MACDFIX',
'MAMA',
'MAVP',
'MAX',
'MAXINDEX',
'MA_Type',
'MEDPRICE',
'MFI',
'MIDPOINT',
'MIDPRICE',
'MIN',
'MININDEX',
'MINMAX',
'MINMAXINDEX',
'MINUS_DI',
'MINUS_DM',
'MOM',
'MULT',
'NATR',
'OBV',
'PLUS_DI',
'PLUS_DM',
'PPO',
'ROC',
'ROCP',
'ROCR',
'ROCR100',
'RSI',
'SAR',
'SAREXT',
'SIN',
'SINH',
'SMA',
'SQRT',
'STDDEV',
'STOCH',
'STOCHF',
'STOCHRSI',
'SUB',
'SUM',
'T3',
'TAN',
'TANH',
'TEMA',
'TRANGE',
'TRIMA',
'TRIX',
'TSF',
'TYPPRICE',
'ULTOSC',
'VAR',
'WCLPRICE',
'WILLR',
'WMA',
'__TA_FUNCTION_NAMES__',
'__all__',
'__builtins__',
'__cached__',
'__doc__',
'__file__',
'__function_groups__',
'__loader__',
'__name__',
'__package__',
'__path__',
'__spec__',
'__ta_version__',
'__version__',
'_pandas_wrapper',
'_pd_Series',
'_ta_initialize',
'_ta_lib',
'_ta_shutdown',
'atexit',
'chain',
'func',
'func_name',
'get_compatibility',
'get_function_groups',
'get_functions',
'get_unstable_period',
'set_compatibility',
'set_unstable_period',
'stream',
'stream_ACOS',
'stream_AD',
'stream_ADD',
'stream_ADOSC',
'stream_ADX',
'stream_ADXR',
'stream_APO',
'stream_AROON',
'stream_AROONOSC',
'stream_ASIN',
'stream_ATAN',
'stream_ATR',
'stream_AVGPRICE',
'stream_BBANDS',
'stream_BETA',
'stream_BOP',
'stream_CCI',
'stream_CDL2CROWS',
'stream_CDL3BLACKCROWS',
'stream_CDL3INSIDE',
'stream_CDL3LINESTRIKE',
'stream_CDL3OUTSIDE',
'stream_CDL3STARSINSOUTH',
'stream_CDL3WHITESOLDIERS',
'stream_CDLABANDONEDBABY',
'stream_CDLADVANCEBLOCK',
'stream_CDLBELTHOLD',
'stream_CDLBREAKAWAY',
'stream_CDLCLOSINGMARUBOZU',
'stream_CDLCONCEALBABYSWALL',
'stream_CDLCOUNTERATTACK',
'stream_CDLDARKCLOUDCOVER',
'stream_CDLDOJI',
'stream_CDLDOJISTAR',
'stream_CDLDRAGONFLYDOJI',
'stream_CDLENGULFING',
'stream_CDLEVENINGDOJISTAR',
'stream_CDLEVENINGSTAR',
'stream_CDLGAPSIDESIDEWHITE',
'stream_CDLGRAVESTONEDOJI',
'stream_CDLHAMMER',
'stream_CDLHANGINGMAN',
'stream_CDLHARAMI',
'stream_CDLHARAMICROSS',
'stream_CDLHIGHWAVE',
'stream_CDLHIKKAKE',
'stream_CDLHIKKAKEMOD',
'stream_CDLHOMINGPIGEON',
'stream_CDLIDENTICAL3CROWS',
'stream_CDLINNECK',
'stream_CDLINVERTEDHAMMER',
'stream_CDLKICKING',
'stream_CDLKICKINGBYLENGTH',
'stream_CDLLADDERBOTTOM',
'stream_CDLLONGLEGGEDDOJI',
'stream_CDLLONGLINE',
'stream_CDLMARUBOZU',
'stream_CDLMATCHINGLOW',
'stream_CDLMATHOLD',
'stream_CDLMORNINGDOJISTAR',
'stream_CDLMORNINGSTAR',
'stream_CDLONNECK',
'stream_CDLPIERCING',
'stream_CDLRICKSHAWMAN',
'stream_CDLRISEFALL3METHODS',
'stream_CDLSEPARATINGLINES',
'stream_CDLSHOOTINGSTAR',
'stream_CDLSHORTLINE',
'stream_CDLSPINNINGTOP',
'stream_CDLSTALLEDPATTERN',
'stream_CDLSTICKSANDWICH',
'stream_CDLTAKURI',
'stream_CDLTASUKIGAP',
'stream_CDLTHRUSTING',
'stream_CDLTRISTAR',
'stream_CDLUNIQUE3RIVER',
'stream_CDLUPSIDEGAP2CROWS',
'stream_CDLXSIDEGAP3METHODS',
'stream_CEIL',
'stream_CMO',
'stream_CORREL',
'stream_COS',
'stream_COSH',
'stream_DEMA',
'stream_DIV',
'stream_DX',
'stream_EMA',
'stream_EXP',
'stream_FLOOR',
'stream_HT_DCPERIOD',
'stream_HT_DCPHASE',
'stream_HT_PHASOR',
'stream_HT_SINE',
'stream_HT_TRENDLINE',
'stream_HT_TRENDMODE',
'stream_KAMA',
'stream_LINEARREG',
'stream_LINEARREG_ANGLE',
'stream_LINEARREG_INTERCEPT',
'stream_LINEARREG_SLOPE',
'stream_LN',
'stream_LOG10',
'stream_MA',
'stream_MACD',
'stream_MACDEXT',
'stream_MACDFIX',
'stream_MAMA',
'stream_MAVP',
'stream_MAX',
'stream_MAXINDEX',
'stream_MEDPRICE',
'stream_MFI',
'stream_MIDPOINT',
'stream_MIDPRICE',
'stream_MIN',
'stream_MININDEX',
'stream_MINMAX',
'stream_MINMAXINDEX',
'stream_MINUS_DI',
'stream_MINUS_DM',
'stream_MOM',
'stream_MULT',
'stream_NATR',
'stream_OBV',
'stream_PLUS_DI',
'stream_PLUS_DM',
'stream_PPO',
'stream_ROC',
'stream_ROCP',
'stream_ROCR',
'stream_ROCR100',
'stream_RSI',
'stream_SAR',
'stream_SAREXT',
'stream_SIN',
'stream_SINH',
'stream_SMA',
'stream_SQRT',
'stream_STDDEV',
'stream_STOCH',
'stream_STOCHF',
'stream_STOCHRSI',
'stream_SUB',
'stream_SUM',
'stream_T3',
'stream_TAN',
'stream_TANH',
'stream_TEMA',
'stream_TRANGE',
'stream_TRIMA',
'stream_TRIX',
'stream_TSF',
'stream_TYPPRICE',
'stream_ULTOSC',
'stream_VAR',
'stream_WCLPRICE',
'stream_WILLR',
'stream_WMA',
'stream_func_name',
'stream_func_names',
'wrapped_func',
'wraps']
yfinance下载股票信息
import yfinance as yf
download()
yf.download("AAPL", start = '2000-01-01', end = '2021-01-31', progress = False)
Open | High | Low | Close | Adj Close | Volume | |
---|---|---|---|---|---|---|
Date | ||||||
1999-12-31 | 0.901228 | 0.918527 | 0.888393 | 0.917969 | 0.791887 | 163811200 |
2000-01-03 | 0.936384 | 1.004464 | 0.907924 | 0.999442 | 0.862169 | 535796800 |
2000-01-04 | 0.966518 | 0.987723 | 0.903460 | 0.915179 | 0.789480 | 512377600 |
2000-01-05 | 0.926339 | 0.987165 | 0.919643 | 0.928571 | 0.801032 | 778321600 |
2000-01-06 | 0.947545 | 0.955357 | 0.848214 | 0.848214 | 0.731712 | 767972800 |
... | ... | ... | ... | ... | ... | ... |
2021-01-22 | 136.279999 | 139.850006 | 135.020004 | 139.070007 | 139.070007 | 113907200 |
2021-01-25 | 143.070007 | 145.089996 | 136.539993 | 142.919998 | 142.919998 | 157611700 |
2021-01-26 | 143.600006 | 144.300003 | 141.369995 | 143.160004 | 143.160004 | 98390600 |
2021-01-27 | 143.429993 | 144.300003 | 140.410004 | 142.059998 | 142.059998 | 140843800 |
2021-01-28 | 139.520004 | 141.990005 | 136.699997 | 137.089996 | 137.089996 | 141876400 |
5303 rows × 6 columns
import yfinance as yf
import pandas as pd
ticker = "AAPL"
csv = "./data/{}.csv".format(ticker)
df = yf.download(ticker, start = '2000-01-01', end = '2021-01-31', progress = False)
df.to_csv(csv, index = True)
print("{} written.".format(csv))
./data/AAPL.csv written.
读写MySQL数据库
导入外部库
import pymysql
创建数据库连接
con = pymysql.connect("localhost","root","admin")
cursor = con.cursor()
查看数据库
def show_databases(cursor):
sql = "SHOW DATABASES;"
cursor.execute(sql)
print(cursor.fetchall())
show_databases(cursor)
(('futures',), ('information_schema',), ('mysql',), ('performance_schema',), ('stock',), ('sys',), ('TA_TEST',))
查看数据表
def show_tables(cursor, database):
sql = "SHOW TABLES FROM {};".format(database)
cursor.execute(sql)
print(cursor.fetchall())
show_tables(cursor, "mysql")
(('columns_priv',), ('component',), ('db',), ('default_roles',), ('engine_cost',), ('func',), ('general_log',), ('global_grants',), ('gtid_executed',), ('help_category',), ('help_keyword',), ('help_relation',), ('help_topic',), ('innodb_index_stats',), ('innodb_table_stats',), ('password_history',), ('plugin',), ('procs_priv',), ('proxies_priv',), ('replication_asynchronous_connection_failover',), ('role_edges',), ('server_cost',), ('servers',), ('slave_master_info',), ('slave_relay_log_info',), ('slave_worker_info',), ('slow_log',), ('tables_priv',), ('time_zone',), ('time_zone_leap_second',), ('time_zone_name',), ('time_zone_transition',), ('time_zone_transition_type',), ('user',))
新建数据库
def create_database(cursor, database):
sql = "CREATE DATABASE {};".format(database)
try:
cursor.execute(sql)
except pymysql.Error as err:
print(err)
create_database(cursor, "TA_TEST") # use underline('_') rather than dash('-')
show_databases(cursor)
(1007, "Can't create database 'TA_TEST'; database exists")
(('futures',), ('information_schema',), ('mysql',), ('performance_schema',), ('stock',), ('sys',), ('TA_TEST',))
删除数据库
def drop_database(cursor, database):
sql = "DROP DATABASE {};".format(database)
try:
cursor.execute(sql)
except pymysql.Error as err:
print(err)
drop_database(cursor, "TA_TEST") # use underline('_') rather than dash('-')
show_databases(cursor)
(('futures',), ('information_schema',), ('mysql',), ('performance_schema',), ('stock',), ('sys',))
新建数据表
def create_table(cursor, database, table, table_columns, primary_key):
try:
sql_statement = "use " + database
cursor.execute(sql_statement)
sql_statement = "CREATE TABLE IF NOT EXISTS {} (\n".format(table)
for key, value in table_columns.items():
sql_statement += " {} {}, \n".format(key, value)
sql_statement += " PRIMARY KEY ({})) ENGINE=innodb DEFAULT CHARSET=UTF8MB4;".format(primary_key)
print(sql_statement)
cursor.execute(sql_statement)
except pymysql.Error as err:
print(err)
table_columns = {'DateTime': 'datetime',
'Open': 'DECIMAL(10,4)',
'High': 'DECIMAL(10,4)',
'Low': 'DECIMAL(10,4)',
'Close': 'DECIMAL(10,4)',
'TradeVolume': 'BIGINT',
'HoldVolume': 'BIGINT',
'Average': 'DECIMAL(10,4)',
}
primary_key = 'DateTime'
create_database(cursor, "TA_TEST")
create_table(cursor, "TA_TEST", "Future", table_columns, primary_key)
show_tables(cursor, "TA_TEST")
CREATE TABLE IF NOT EXISTS Future (
DateTime datetime,
Open DECIMAL(10,4),
High DECIMAL(10,4),
Low DECIMAL(10,4),
Close DECIMAL(10,4),
TradeVolume BIGINT,
HoldVolume BIGINT,
Average DECIMAL(10,4),
PRIMARY KEY (DateTime)) ENGINE=innodb DEFAULT CHARSET=UTF8MB4;
(('Future',),)