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 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 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())