def save_sp500_to_sql(data):
"""
Insert the S&P 500 symbols into the MySQL database
"""
db_config = load_config('MySQL_finclab')
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
cols = "ticker, instrument, name, sector, currency, created_date, last_updated_date"
args = "%s, " * len(cols.split(','))
args = args[:-2]
query = """
INSERT INTO symbol ({cols}) VALUES ({args})
""".format(cols=cols, args=args)
# Using the MySQL connection, carry out an INSERT INTO for every symbol
cursor.executemany(query, data)
# print("Last updated row is:", cursor.lastrowid)
conn.commit()
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def download_one_futures_from_quandlAPI(
contract, data_folder='~/Work/FinanceData/Futures/'):
"""
Download an individual futures contract from Quandl and then store it to disk in the dir directory using Quandl HTTP .csv API.
An auth_token is required, which is obtained from the Quandl upon sign-up.
"""
# Load configuration - API key
apiConfig = load_config('Quandl')
# Construct the API call
api_call = "http://www.quandl.com/api/v1/datasets/"
api_call += "OFDP/FUTURE_{}.csv".format(contract)
params = "?auth_token={}&sort_order=asc".format(apiConfig["api_key"])
full_url = "".join([api_call, params])
# Download the data from Quandl
data = requests.get(full_url).text
# Store the data to disk
f = open("{folder}/{file}.csv".format(folder=data_folder, file=contract),
'w')
f.write(data)
f.close()
def obtain_tickers():
"""
Obtains a list of the ticker symbols in the database.
return:
A list of tuples - [(id, ticker)]
"""
db_config = load_config('MySQL_finclab')
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
query = """
SELECT id, ticker FROM symbol
"""
cursor.execute(query)
data = cursor.fetchall()
return [(d[0], d[1]) for d in data]
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def obtain_tickers():
"""
Obtains a list of the ticker symbols in the database.
return:
A list of tuples - [(id, ticker)]
"""
db_config = load_config('MySQL_finclab')
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
query = """
SELECT id, ticker FROM symbol
"""
cursor.execute(query)
data = cursor.fetchall()
return [(d[0], d[1]) for d in data]
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def save_sp500_to_sql(data):
"""
Insert the S&P 500 symbols into the MySQL database
"""
db_config = load_config('MySQL_finclab')
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
cols = "ticker, instrument, name, sector, currency, created_date, last_updated_date"
args = "%s, " * len(cols.split(','))
args = args[:-2]
query = """
INSERT INTO symbol ({cols}) VALUES ({args})
""".format(cols=cols, args=args)
# Using the MySQL connection, carry out an INSERT INTO for every symbol
cursor.executemany(query, data)
# print("Last updated row is:", cursor.lastrowid)
conn.commit()
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def download_one_futures_from_quandl(contract, data_folder='~/Work/FinanceData/Futures/'):
"""
Download an individual futures contract from Quandl and then store it to disk in the dir directory. An auth_token is required, which is obtained from the Quandl upon sign-up.
The quandl library is required:
pip install quandl
"""
# Load configuration - API key
api_config = load_config('Quandl')
# Initial download using Quandl library
df = Quandl.get("OFDP/FUTURE_{}".format(api_config['api_key']),
returns="pandas")
# Store the data to disk
df.to_csv("{folder}/{file}.csv".format(folder=data_folder, file=contract), 'w')
def download_one_futures_from_quandl(contract,
data_folder='~/Work/FinanceData/Futures/'
):
"""
Download an individual futures contract from Quandl and then store it to disk in the dir directory. An auth_token is required, which is obtained from the Quandl upon sign-up.
The quandl library is required:
pip install quandl
"""
# Load configuration - API key
api_config = load_config('Quandl')
# Initial download using Quandl library
df = Quandl.get("OFDP/FUTURE_{}".format(api_config['api_key']),
returns="pandas")
# Store the data to disk
df.to_csv("{folder}/{file}.csv".format(folder=data_folder, file=contract),
'w')
def insert_data_to_db(vendorID, symbolID, data):
"""
Takes a list of tuples of daily data and adds it to the MySQL database. Appends the vendor ID and symbol ID to the data.
parameters:
vendorID: data vendor
symbolID: stock ticker
data: List of tuples of the OHLC data plus adj_close and volume
"""
# MySQL config
db_config = load_config("MySQL_finclab")
# Create the time now
now = dt.datetime.utcnow()
# Amend the data to include the vendor ID and symbol ID
daily_data = [(vendorID, symbolID, d[0], now, now, d[1], d[2], d[3], d[4],
d[5], d[6]) for d in data]
cols = "data_vendor_id, symbol_id, price_date, created_date, last_updated_date, open_price, high_price, low_price, close_price, volume, adj_close_price"
args = "%s, " * len(cols.split(','))
args = args[:-2]
query = """
INSERT INTO daily_price ({cols}) VALUES ({args})
""".format(cols=cols, args=args)
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, daily_data)
conn.commit()
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def insert_data_to_db(vendorID, symbolID, data):
"""
Takes a list of tuples of daily data and adds it to the MySQL database. Appends the vendor ID and symbol ID to the data.
parameters:
vendorID: data vendor
symbolID: stock ticker
data: List of tuples of the OHLC data plus adj_close and volume
"""
# MySQL config
db_config = load_config("MySQL_finclab")
# Create the time now
now = dt.datetime.utcnow()
# Amend the data to include the vendor ID and symbol ID
daily_data = [(vendorID, symbolID, d[0], now, now, d[1], d[2], d[3], d[4], d[5], d[6]) for d in data]
cols = "data_vendor_id, symbol_id, price_date, created_date, last_updated_date, open_price, high_price, low_price, close_price, volume, adj_close_price"
args = "%s, " * len(cols.split(','))
args = args[:-2]
query = """
INSERT INTO daily_price ({cols}) VALUES ({args})
""".format(cols=cols, args=args)
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, daily_data)
conn.commit()
except mdb.Error as e:
print("Error:", e)
finally:
cursor.close()
conn.close()
def download_one_futures_from_quandlAPI(contract, data_folder='~/Work/FinanceData/Futures/'):
"""
Download an individual futures contract from Quandl and then store it to disk in the dir directory using Quandl HTTP .csv API.
An auth_token is required, which is obtained from the Quandl upon sign-up.
"""
# Load configuration - API key
apiConfig = load_config('Quandl')
# Construct the API call
api_call = "http://www.quandl.com/api/v1/datasets/"
api_call += "OFDP/FUTURE_{}.csv".format(contract)
params = "?auth_token={}&sort_order=asc".format(apiConfig["api_key"])
full_url = "".join([api_call, params])
# Download the data from Quandl
data = requests.get(full_url).text
# Store the data to disk
f = open("{folder}/{file}.csv".format(folder=data_folder, file=contract), 'w')
f.write(data)
f.close()
# coding:utf-8
# 在这个文件里调用test_net.py中的test_net
import sys
import os
sys.path.append(os.getcwd())
import pprint
from network.test_network import get_test_network
from lib.load_config import load_config
from ctpn.test_net import TestClass
import tensorflow as tf
if __name__ == "__main__":
# 加载配置文件
cfg = load_config()
# pprint.pprint(cfg)
with tf.Graph().as_default() as g:
# 获取测试网络， 一个空网络
network = get_test_network(cfg)
# saver = tf.train.Saver()
# 获取测试类实例，这时候也还没有把参数填写进去
testclass = TestClass(cfg, network)
# 开始测试
testclass.test_net(g)
""" Module: To get data from MySQL database.
Peter Lee
Last update: 2016-Jan-12
A sample program to obtain the Open-High-Low-Close (OHLC) dat for the Google stock over a certain time period from the securities master database.
"""
import pandas as pd
import MySQLdb as mdb
from lib.load_config import load_config
if __name__ == '__main__':
db_config = load_config("MySQL_finclab")
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
# Select all of the historical Google adjusted close data
query = """
SELECT dp.price_date, dp.adj_close_price
FROM symbol AS sym
INNER JOIN daily_price AS dp
ON dp.symbol_id = sym.id
ORDER BY dp.price_date ASC;
"""
df = pd.read_sql_query(query, con=conn, index_col='price_date')
print(df.tail())
""" Module: To get data from MySQL database.
Peter Lee
Last update: 2016-Jan-12
A sample program to obtain the Open-High-Low-Close (OHLC) dat for the Google stock over a certain time period from the securities master database.
"""
import pandas as pd
import MySQLdb as mdb
from lib.load_config import load_config
if __name__ == '__main__':
db_config = load_config("MySQL_finclab")
try:
conn = mdb.Connection(**db_config)
cursor = conn.cursor()
# Select all of the historical Google adjusted close data
query = """
SELECT dp.price_date, dp.adj_close_price
FROM symbol AS sym
INNER JOIN daily_price AS dp
ON dp.symbol_id = sym.id
ORDER BY dp.price_date ASC;
"""
df = pd.read_sql_query(query, con=conn, index_col='price_date')
print(df.tail())
