{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "### Table of Conetents\n", "- [Example Starts Here](#example_starts_here)\n", "- [init_db()](#init_db)\n", "- [insert_hist_data(), query_hist_data()](#insert_query_hist_data)\n", "- [download_insert_hist_data()](#download_insert_hist_data)\n", "- [get_hist_data()](#get_hist_data)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true }, "outputs": [], "source": [ "# Prepare environment\n", "import os, sys\n", "sys.path.insert(0, os.path.abspath('..'))\n", "from IPython.core.interactiveshell import InteractiveShell\n", "InteractiveShell.ast_node_interactivity = \"all\"\n", "from io import StringIO\n", "import pandas as pd\n", "import warnings\n", "warnings.filterwarnings(\"ignore\")" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "gs1h_csv = StringIO(\"\"\"\n", "Symbol,DataType,BarSize,TickerTime,opening,high,low,closing,volume,barcount,average\n", "GS,TRADES,1h,2017-09-05 12:00:00+00:00,224.5,224.5,223.98,223.98,23,18,224.302\n", "GS,TRADES,1h,2017-09-05 13:00:00+00:00,224.25,224.25,220.01,220.39,6431,3782,221.423\n", "GS,TRADES,1h,2017-09-05 14:00:00+00:00,220.39,220.7,217.3,218.12,11332,5881,218.82\n", "GS,TRADES,1h,2017-09-05 15:00:00+00:00,218.09,219.64,218.07,219.45,6457,3843,218.795\n", "GS,TRADES,1h,2017-09-05 16:00:00+00:00,219.45,219.46,218.11,218.67,4940,3550,218.633\n", "GS,TRADES,1h,2017-09-05 17:00:00+00:00,218.72,219.19,218.13,218.73,3228,2527,218.657\n", "GS,TRADES,1h,2017-09-05 18:00:00+00:00,218.72,218.86,217.62,217.67,4939,3219,218.285\n", "GS,TRADES,1h,2017-09-05 19:00:00+00:00,217.68,218.3,217.46,217.85,8173,5594,217.747\n", "GS,TRADES,1h,2017-09-05 20:00:00+00:00,217.79,218.85,217.78,217.92,2445,10,217.781\n", "GS,TRADES,1h,2017-09-05 21:00:00+00:00,218.0,218.11,217.91,218.11,8,5,218.022\n", "GS,TRADES,1h,2017-09-05 22:00:00+00:00,218.11,218.17,217.95,217.95,15,11,218.12\n", "GS,TRADES,1h,2017-09-05 23:00:00+00:00,218.15,218.15,217.93,217.93,2,2,218.04\n", "GS,TRADES,1h,2017-09-06 12:00:00+00:00,218.97,219.29,218.97,219.2,28,11,219.159\n", "GS,TRADES,1h,2017-09-06 13:00:00+00:00,219.1,220.78,218.67,219.58,4729,2596,219.796\n", "GS,TRADES,1h,2017-09-06 14:00:00+00:00,219.61,221.02,219.54,220.01,4451,2722,220.49\n", "GS,TRADES,1h,2017-09-06 15:00:00+00:00,219.98,220.2,217.73,218.2,4222,2500,219.02\n", "GS,TRADES,1h,2017-09-06 16:00:00+00:00,218.2,219.83,217.61,219.8,2680,1809,218.335\n", "GS,TRADES,1h,2017-09-06 17:00:00+00:00,219.77,220.5,219.41,219.57,2470,1492,219.954\n", "GS,TRADES,1h,2017-09-06 18:00:00+00:00,219.61,219.8,218.9,219.33,2127,1447,219.428\n", "GS,TRADES,1h,2017-09-06 19:00:00+00:00,219.33,219.7,218.85,219.05,5587,3451,219.363\n", "GS,TRADES,1h,2017-09-06 20:00:00+00:00,218.83,219.09,218.83,218.99,3634,6,218.83\n", "GS,TRADES,1h,2017-09-06 21:00:00+00:00,218.98,218.98,218.98,218.98,2,1,218.98\n", "GS,TRADES,1h,2017-09-06 22:00:00+00:00,218.7,218.7,218.7,218.7,1,1,218.7\n", "GS,TRADES,1h,2017-09-06 23:00:00+00:00,218.69,218.7,218.69,218.7,8,2,218.696\n", "GS,TRADES,1h,2017-09-07 11:00:00+00:00,219.0,219.0,219.0,219.0,1,1,219.0\n", "GS,TRADES,1h,2017-09-07 12:00:00+00:00,219.21,219.4,218.5,218.5,31,16,219.015\n", "GS,TRADES,1h,2017-09-07 13:00:00+00:00,218.57,218.83,216.07,216.31,3338,1726,217.503\n", "GS,TRADES,1h,2017-09-07 14:00:00+00:00,216.35,216.35,214.64,215.77,7048,4299,215.392\n", "GS,TRADES,1h,2017-09-07 15:00:00+00:00,215.74,216.41,214.96,215.28,4571,3190,215.666\n", "GS,TRADES,1h,2017-09-07 16:00:00+00:00,215.24,216.28,215.06,216.07,2191,1541,215.518\n", "GS,TRADES,1h,2017-09-07 17:00:00+00:00,216.04,216.41,215.26,215.6,2058,1495,215.708\n", "GS,TRADES,1h,2017-09-07 18:00:00+00:00,215.58,215.74,215.25,215.37,2206,1509,215.428\n", "GS,TRADES,1h,2017-09-07 19:00:00+00:00,215.4,215.94,214.95,215.83,6582,4149,215.313\n", "GS,TRADES,1h,2017-09-07 20:00:00+00:00,215.84,216.88,215.8,216.02,1869,9,215.846\n", "GS,TRADES,1h,2017-09-07 21:00:00+00:00,215.98,215.98,215.9,215.9,9,6,215.927\n", "GS,TRADES,1h,2017-09-07 22:00:00+00:00,215.9,215.99,215.9,215.99,14,3,215.909\n", "GS,TRADES,1h,2017-09-07 23:00:00+00:00,216.09,216.09,216.09,216.09,1,1,216.09\n", "GS,TRADES,1h,2017-09-08 11:00:00+00:00,215.44,215.44,215.44,215.44,1,1,215.44\n", "GS,TRADES,1h,2017-09-08 12:00:00+00:00,214.9,215.5,214.8,215.5,22,8,215.05\n", "GS,TRADES,1h,2017-09-08 13:00:00+00:00,215.5,218.76,215.1,218.67,5788,3250,217.577\n", "GS,TRADES,1h,2017-09-08 14:00:00+00:00,218.68,219.28,217.78,218.04,4696,3283,218.707\n", "GS,TRADES,1h,2017-09-08 15:00:00+00:00,218.06,218.39,216.82,216.89,2574,1880,217.345\n", "GS,TRADES,1h,2017-09-08 16:00:00+00:00,216.92,217.3,216.67,217.14,2049,1433,217.015\n", "GS,TRADES,1h,2017-09-08 17:00:00+00:00,217.12,217.17,216.15,216.76,2254,1565,216.591\n", "GS,TRADES,1h,2017-09-08 18:00:00+00:00,216.76,217.35,216.61,217.01,1921,1373,217.117\n", "GS,TRADES,1h,2017-09-08 19:00:00+00:00,217.01,217.34,216.69,217.24,3980,2789,217.075\n", "GS,TRADES,1h,2017-09-08 20:00:00+00:00,217.21,217.21,216.71,216.71,2222,6,217.209\n", "GS,TRADES,1h,2017-09-08 21:00:00+00:00,217.0,217.0,217.0,217.0,2,1,217.0\n", "GS,TRADES,1h,2017-09-08 22:00:00+00:00,216.46,216.8,216.46,216.8,3,2,216.698\n", "GS,TRADES,1h,2017-09-08 23:00:00+00:00,216.8,216.8,216.8,216.8,0,0,216.8\n", "\"\"\")\n", "\n", "gs1h = pd.read_csv(gs1h_csv)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "\n", "## Example starts here\n", "------" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import pytz\n", "import asyncio\n", "import aiomysql.sa as aiosa\n", "from sqlalchemy import create_engine, MetaData\n", "from ibstract import IB\n", "from ibstract import MarketDataBlock\n", "from ibstract import HistDataReq\n", "from ibstract import init_db, insert_hist_data, query_hist_data\n", "from ibstract import download_insert_hist_data\n", "from ibstract import get_hist_data\n", "from ibstract.utils import dtest" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "\n", "### `init_db()` : Initialize MySQL database by creating one table for each security type." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": true }, "outputs": [], "source": [ "db_info = {\n", " 'host': '127.0.0.1',\n", " 'user': 'root',\n", " 'password': 'ibstract',\n", " 'db': 'ibstract_test',\n", "}\n", "init_db(db_info)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tables in Ibstract MySQL database:\n" ] }, { "data": { "text/plain": [ "['Bond',\n", " 'CFD',\n", " 'Commodity',\n", " 'Forex',\n", " 'Future',\n", " 'FuturesOption',\n", " 'Index',\n", " 'MutualFund',\n", " 'Option',\n", " 'Stock',\n", " 'Warrant']" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" }, { "name": "stdout", "output_type": "stream", "text": [ "Columns in Stock table:\n" ] }, { "data": { "text/plain": [ "[Column('Symbol', VARCHAR(length=20), table=, primary_key=True, nullable=False),\n", " Column('DataType', VARCHAR(length=20), table=, primary_key=True, nullable=False),\n", " Column('BarSize', VARCHAR(length=10), table=, primary_key=True, nullable=False),\n", " Column('TickerTime', DATETIME(), table=, primary_key=True, nullable=False),\n", " Column('opening', FLOAT(), table=),\n", " Column('high', FLOAT(), table=),\n", " Column('low', FLOAT(), table=),\n", " Column('closing', FLOAT(), table=),\n", " Column('volume', INTEGER(display_width=10, unsigned=True), table=),\n", " Column('barcount', INTEGER(display_width=10, unsigned=True), table=),\n", " Column('average', FLOAT(), table=)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "engine = create_engine(\n", " \"mysql+pymysql://{}:{}@{}/{}\".format(\n", " db_info['user'], db_info['password'], db_info['host'], db_info['db']),\n", " echo=False)\n", "meta = MetaData()\n", "meta.reflect(bind=engine)\n", "print(\"Tables in Ibstract MySQL database:\")\n", "list(meta.tables.keys())\n", "print(\"Columns in Stock table:\")\n", "meta.tables['Stock'].columns.values()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "### Coroutines `insert_hist_data()`, `query_hist_data()` : Insert / Read MarketDataBlock() to/from MySQL database" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1h2017-09-05 08:00:00-04:00224.50224.50223.98223.982318224.302
2017-09-05 09:00:00-04:00224.25224.25220.01220.3964313782221.423
2017-09-05 10:00:00-04:00220.39220.70217.30218.12113325881218.820
2017-09-05 11:00:00-04:00218.09219.64218.07219.4564573843218.795
2017-09-05 12:00:00-04:00219.45219.46218.11218.6749403550218.633
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-05 08:00:00-04:00 224.50 224.50 223.98 \n", " 2017-09-05 09:00:00-04:00 224.25 224.25 220.01 \n", " 2017-09-05 10:00:00-04:00 220.39 220.70 217.30 \n", " 2017-09-05 11:00:00-04:00 218.09 219.64 218.07 \n", " 2017-09-05 12:00:00-04:00 219.45 219.46 218.11 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-05 08:00:00-04:00 223.98 23 18 \n", " 2017-09-05 09:00:00-04:00 220.39 6431 3782 \n", " 2017-09-05 10:00:00-04:00 218.12 11332 5881 \n", " 2017-09-05 11:00:00-04:00 219.45 6457 3843 \n", " 2017-09-05 12:00:00-04:00 218.67 4940 3550 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-05 08:00:00-04:00 224.302 \n", " 2017-09-05 09:00:00-04:00 221.423 \n", " 2017-09-05 10:00:00-04:00 218.820 \n", " 2017-09-05 11:00:00-04:00 218.795 \n", " 2017-09-05 12:00:00-04:00 218.633 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# The MarketDataBlock() to be inserted.\n", "blk_gs1h = MarketDataBlock(gs1h)\n", "blk_gs1h.tz_convert('US/Eastern')\n", "blk_gs1h.df.head()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1h2017-09-06 10:00:00-04:00219.61221.02219.54220.0144512722220.490
2017-09-06 11:00:00-04:00219.98220.20217.73218.2042222500219.020
2017-09-06 12:00:00-04:00218.20219.83217.61219.8026801809218.335
2017-09-06 13:00:00-04:00219.77220.50219.41219.5724701492219.954
2017-09-06 14:00:00-04:00219.61219.80218.90219.3321271447219.428
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-06 10:00:00-04:00 219.61 221.02 219.54 \n", " 2017-09-06 11:00:00-04:00 219.98 220.20 217.73 \n", " 2017-09-06 12:00:00-04:00 218.20 219.83 217.61 \n", " 2017-09-06 13:00:00-04:00 219.77 220.50 219.41 \n", " 2017-09-06 14:00:00-04:00 219.61 219.80 218.90 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-06 10:00:00-04:00 220.01 4451 2722 \n", " 2017-09-06 11:00:00-04:00 218.20 4222 2500 \n", " 2017-09-06 12:00:00-04:00 219.80 2680 1809 \n", " 2017-09-06 13:00:00-04:00 219.57 2470 1492 \n", " 2017-09-06 14:00:00-04:00 219.33 2127 1447 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1h 2017-09-06 10:00:00-04:00 220.490 \n", " 2017-09-06 11:00:00-04:00 219.020 \n", " 2017-09-06 12:00:00-04:00 218.335 \n", " 2017-09-06 13:00:00-04:00 219.954 \n", " 2017-09-06 14:00:00-04:00 219.428 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "async def run(loop, blk):\n", " engine = await aiosa.create_engine(\n", " user=db_info['user'], db=db_info['db'],\n", " host=db_info['host'], password=db_info['password'],\n", " loop=loop, echo=False)\n", " await insert_hist_data(engine, 'Stock', blk)\n", " data = await query_hist_data(engine, 'Stock', 'GS', 'TRADES', '1h', \n", " dtest(2017, 9, 6, 10), dtest(2017, 9, 6, 14),)\n", " engine.close()\n", " await engine.wait_closed()\n", " return data\n", "\n", "loop = asyncio.get_event_loop()\n", "blk_readback = loop.run_until_complete(run(loop, blk_gs1h))\n", "blk_readback.df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "### Coroutine `download_insert_hist_data()` : Download historical data and insert part of them to MySQL database" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1d2017-08-30 00:00:00-04:00220.25224.22220.09222.421858012085222.7730
2017-08-31 00:00:00-04:00223.25224.49222.58223.741549110053223.7635
2017-09-01 00:00:00-04:00224.55227.56223.53225.881694011739226.3505
2017-09-05 00:00:00-04:00223.85224.00217.30217.784549928392218.9010
2017-09-06 00:00:00-04:00218.98221.02217.61218.832615815960219.5335
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-30 00:00:00-04:00 220.25 224.22 220.09 \n", " 2017-08-31 00:00:00-04:00 223.25 224.49 222.58 \n", " 2017-09-01 00:00:00-04:00 224.55 227.56 223.53 \n", " 2017-09-05 00:00:00-04:00 223.85 224.00 217.30 \n", " 2017-09-06 00:00:00-04:00 218.98 221.02 217.61 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-30 00:00:00-04:00 222.42 18580 12085 \n", " 2017-08-31 00:00:00-04:00 223.74 15491 10053 \n", " 2017-09-01 00:00:00-04:00 225.88 16940 11739 \n", " 2017-09-05 00:00:00-04:00 217.78 45499 28392 \n", " 2017-09-06 00:00:00-04:00 218.83 26158 15960 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-30 00:00:00-04:00 222.7730 \n", " 2017-08-31 00:00:00-04:00 223.7635 \n", " 2017-09-01 00:00:00-04:00 226.3505 \n", " 2017-09-05 00:00:00-04:00 218.9010 \n", " 2017-09-06 00:00:00-04:00 219.5335 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1d2017-08-31 00:00:00-04:00223.25224.49222.58223.741549110053223.764
2017-09-01 00:00:00-04:00224.55227.56223.53225.881694011739226.350
2017-09-05 00:00:00-04:00223.85224.00217.30217.784549928392218.901
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.25 224.49 222.58 \n", " 2017-09-01 00:00:00-04:00 224.55 227.56 223.53 \n", " 2017-09-05 00:00:00-04:00 223.85 224.00 217.30 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.74 15491 10053 \n", " 2017-09-01 00:00:00-04:00 225.88 16940 11739 \n", " 2017-09-05 00:00:00-04:00 217.78 45499 28392 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.764 \n", " 2017-09-01 00:00:00-04:00 226.350 \n", " 2017-09-05 00:00:00-04:00 218.901 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Clear Stock table\n", "engine.execute(meta.tables['Stock'].delete())\n", "\n", "# A user coroutine to download historical data and insert to MySQL database\n", "async def run(loop, req, broker, insert_limit):\n", " engine = await aiosa.create_engine(\n", " user=db_info['user'], db=db_info['db'],\n", " host=db_info['host'], password=db_info['password'],\n", " loop=loop, echo=False)\n", " blk_download = await download_insert_hist_data(\n", " req, broker, engine, insert_limit)\n", " blk_readback = await query_hist_data(\n", " engine, req.SecType, req.Symbol, req.DataType, req.BarSize,\n", " start=dtest(2017, 8, 1), end=dtest(2017, 10, 1))\n", " engine.close()\n", " await engine.wait_closed()\n", " return blk_download, blk_readback\n", "\n", "# Arguments\n", "req = HistDataReq('Stock', 'GS', '1d', '5d', dtest(2017, 9, 7))\n", "broker = IB('127.0.0.1', 4002)\n", "insert_limit = (dtest(2017, 8, 31), dtest(2017, 9, 5)) # Only insert partial data between (SQL inclusive) 8/31 and 9/5\n", "\n", "# Run loop\n", "loop = asyncio.get_event_loop()\n", "blk_download, blk_readback = loop.run_until_complete(\n", " run(loop, req, broker, insert_limit))\n", "blk_download.df\n", "blk_readback.df" ] }, { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "\n", "### Coroutine `get_hist_data()` : Try to query data from local MySQL database. If any part is not found, download and insert back to MySQL all missing parts concurrently and asynchronously.\n", "\n", "\n", "#### `get_hist_data()` executes in these steps:\n", "1. Try to query historical data from local MySQL for the input user request.\n", "2. Determine missing data parts and corresponding start-end date/time gaps. Create multiple HistDataReq() requests for these gaps.\n", "3. Concurrently download these requests from broker API.\n", "4. Concurrently combine downloaded data pieces with the data from local MySQL data block.\n", "5. Return the combined data per the input user request." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1d2017-08-31 00:00:00-04:00223.25224.49222.58223.741549110053223.764
2017-09-01 00:00:00-04:00224.55227.56223.53225.881694011739226.350
2017-09-05 00:00:00-04:00223.85224.00217.30217.784549928392218.901
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.25 224.49 222.58 \n", " 2017-09-01 00:00:00-04:00 224.55 227.56 223.53 \n", " 2017-09-05 00:00:00-04:00 223.85 224.00 217.30 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.74 15491 10053 \n", " 2017-09-01 00:00:00-04:00 225.88 16940 11739 \n", " 2017-09-05 00:00:00-04:00 217.78 45499 28392 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-31 00:00:00-04:00 223.764 \n", " 2017-09-01 00:00:00-04:00 226.350 \n", " 2017-09-05 00:00:00-04:00 218.901 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Use data above\n", "blk_gs_3days = blk_readback\n", "\n", "# Clear Stock table\n", "engine.execute(meta.tables['Stock'].delete())\n", "\n", "# Populate database with some data\n", "async def populate_db(blk_db):\n", " engine = await aiosa.create_engine(\n", " user=db_info['user'], db=db_info['db'],\n", " host=db_info['host'], password=db_info['password'],\n", " loop=loop, echo=False)\n", " await insert_hist_data(engine, 'Stock', blk_db)\n", " data_exist_in_db = await query_hist_data(engine, 'Stock', 'GS', 'TRADES', '1d',\n", " dtest(2017,1,1),dtest(2017,12,31))\n", " engine.close()\n", " await engine.wait_closed()\n", " return data_exist_in_db\n", "\n", "# Insert and query local MySQL database\n", "loop = asyncio.get_event_loop()\n", "blk_db = loop.run_until_complete(populate_db(blk_gs_3days))\n", "blk_db.df" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
openinghighlowclosingvolumebarcountaverage
SymbolDataTypeBarSizeTickerTime
GSTRADES1d2017-08-29 00:00:00-04:00217.27220.14215.75219.961879512617218.7545
2017-08-30 00:00:00-04:00220.25224.22220.09222.421858012085222.7730
2017-08-31 00:00:00-04:00223.25224.49222.58223.741549110053223.7635
2017-09-01 00:00:00-04:00224.55227.56223.53225.881694011739226.3505
2017-09-05 00:00:00-04:00223.85224.00217.30217.784549928392218.9010
2017-09-06 00:00:00-04:00218.98221.02217.61218.832615815960219.5335
2017-09-07 00:00:00-04:00218.73218.81214.64215.842796317892215.7020
2017-09-08 00:00:00-04:00215.51219.28215.40217.212325015562217.5120
\n", "
" ], "text/plain": [ " opening high low \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-29 00:00:00-04:00 217.27 220.14 215.75 \n", " 2017-08-30 00:00:00-04:00 220.25 224.22 220.09 \n", " 2017-08-31 00:00:00-04:00 223.25 224.49 222.58 \n", " 2017-09-01 00:00:00-04:00 224.55 227.56 223.53 \n", " 2017-09-05 00:00:00-04:00 223.85 224.00 217.30 \n", " 2017-09-06 00:00:00-04:00 218.98 221.02 217.61 \n", " 2017-09-07 00:00:00-04:00 218.73 218.81 214.64 \n", " 2017-09-08 00:00:00-04:00 215.51 219.28 215.40 \n", "\n", " closing volume barcount \\\n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-29 00:00:00-04:00 219.96 18795 12617 \n", " 2017-08-30 00:00:00-04:00 222.42 18580 12085 \n", " 2017-08-31 00:00:00-04:00 223.74 15491 10053 \n", " 2017-09-01 00:00:00-04:00 225.88 16940 11739 \n", " 2017-09-05 00:00:00-04:00 217.78 45499 28392 \n", " 2017-09-06 00:00:00-04:00 218.83 26158 15960 \n", " 2017-09-07 00:00:00-04:00 215.84 27963 17892 \n", " 2017-09-08 00:00:00-04:00 217.21 23250 15562 \n", "\n", " average \n", "Symbol DataType BarSize TickerTime \n", "GS TRADES 1d 2017-08-29 00:00:00-04:00 218.7545 \n", " 2017-08-30 00:00:00-04:00 222.7730 \n", " 2017-08-31 00:00:00-04:00 223.7635 \n", " 2017-09-01 00:00:00-04:00 226.3505 \n", " 2017-09-05 00:00:00-04:00 218.9010 \n", " 2017-09-06 00:00:00-04:00 219.5335 \n", " 2017-09-07 00:00:00-04:00 215.7020 \n", " 2017-09-08 00:00:00-04:00 217.5120 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# A user coroutine to get wider range of historical data than those existing in MySQL.\n", "# Data existing in MySQL will not be downloaded, but combined with downloaded data.\n", "async def run(req, broker, loop):\n", " blk_ret = await get_hist_data(req, broker, mysql={**db_info, 'loop': loop})\n", " return blk_ret\n", "\n", "# Request daily data of 8 days, from 8/29 - 9/8.\n", "# Data from 8/31 - 9/5 exist in local database and will not be downloaded.\n", "req = HistDataReq('Stock', 'GS', '1d', '8d', dtest(2017, 9, 9))\n", "broker = IB('127.0.0.1', 4002)\n", "\n", "loop = asyncio.get_event_loop()\n", "blk_ret = loop.run_until_complete(run(req, broker, loop))\n", "blk_ret.df" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.1" } }, "nbformat": 4, "nbformat_minor": 2 }