fvc

import xlwings as wb
import time
from datetime import datetime, timedelta
from multiprocessing import JoinableQueue as queue
import pandas as pd
from threading import Thread
from sqlalchemy import create_engine
import asyncio
from ib_insync import *

db_queue = queue()
strat_queue = queue()
now = datetime.now()

def to_db(symb):
    global db_queue
    data = pd.DataFrame(columns=('Date', 'Close_price'))
    engine = create_engine('mysql://root:B@CKT3ST3Rfr@mew0rk@localhost:3306/stock_base', echo=True)

    while True:
        d = db_queue.get()
        data =  data.append({'Date':d[0], 'Close_price':d[1]}, ignore_index=True)
        db_queue.task_done()

        if len(data) == 2:
            data = data.set_index(pd.to_datetime(data['Date']))
            data =  data.drop(data.columns[0], axis=1)
            #print("\n\tEntering the Table into database *** for *** \n",data, "\n")

            data.to_sql(name=symb, con=engine, if_exists='append', index=False)
            data = pd.DataFrame(columns=('Date', 'Close_price'))
        time.sleep(2)

@asyncio.coroutine
def strat_MA(window,sym):
    print("started strategy thread")
    buffer = pd.DataFrame(columns=['close'])

    while len(buffer) <= window:
        n = strat_queue.get()
        buffer = buffer.append({'close': n}, ignore_index=True)


    ib = IB()
    ib.connect('127.0.0.1', 7496, clientId=5)
    contract = Stock(sym, 'AEB', 'EUR')
    ib.qualifyContracts(contract)

    while True:
        n = strat_queue.get()
        buffer = buffer.append({'close': n}, ignore_index=True)
        MA = buffer.rolling(window).mean()
        Position = ib.reqPositions()

        if buffer['close'].iloc[-1] < (.01 * MA['close'].iloc[-1]) and len(Position) == 0:
            order = MarketOrder('BUY', 10)
            ib.placeOrder(contract, order)
            print ("buy order generated")

        if buffer['close'].iloc[-1] > (.01 * MA['close'].iloc[-1]) and len(Position) > 0:
            order = MarketOrder('SELL', 10)
            ib.placeOrder(contract, order)
            print("sell order generated")

def filter_per_min(val, ts, lts):

    epoch = datetime(now.year, now.month, now.day)
    result = epoch + timedelta(days=ts)
    result = result.replace(microsecond=0,second=0)

    if result > lts:
        lts = result
        a = [result, val]
        print(result, "price:", val)
        db_queue.put(a)
        strat_queue.put(val)
    return lts


############################################################################
b = 1
while b == 1:
    sym = input('Enter the symbol: ')
    print("\nEnter 0 to confirm your symbol as: ",sym)
    b = int(input(''))
window = int(input('Enter window length for MA strategy: '))
nw = now.replace(microsecond=0,second=0)
start =  time.time()
db = Thread(name='db',target=to_db,args=(sym,))
dstrat = Thread(name='MA', target=strat_MA,args=(window, sym,))
db.start()
dstrat.start()


while True:
    try:
        wb.Book(r'C:\Users\Administrator\Desktop\Datasets\RT_feed.xlsx')
        time.sleep(1)
        a = wb.Range('B1').value
        b = wb.Range('E1').value
    except Exception:
        print("-----Waiting for RTD server response----")
        time.sleep(1)

    try:
        nw = filter_per_min(a, b, nw)
    except Exception:
        print("-----Waiting1 for RTD server response----")
        time.sleep(1)