def driptest(numlist):
    '''
    input: a list of numbers (nums corrsp to stocks listed in tckrlist)
    output: the value of a portfolio containing stocks specified  in numlist starting on 9/19/2000 and ending on 7/15/2013
            divs reinvested at price on close of pay dates
    '''
    #TODO add semi-annual contributions, invested evenly between all stocks, every 130 days
    cashperstock = 50000/len(numlist) #start with $50,000 dollars divided by number of stocks
    holdingslist = []

    for i in numlist: #buy initial shares
        price = mf.parse_yahoo_historical(mf.fetch_historical_yahoo(tckrlist[i], (2000, 9, 19), (2000, 9, 19), dividends=False), adjusted=False)
        iprice = price[0][2]

        ishares = round(cashperstock/iprice, 2)
        holdingslist.append(ishares)

    for i in fulldatelist: #for every day in window
        hcount = 0
        p = i.split('/')
        d = int(p[1])
        yahoodate = (int(p[2]), int(p[0]), d) # a yahoo fetch compatible date
        hcount = 0
        for j in numlist: #look at every stock in the numlist
            paycount = 0
            icurrprice = 0
            d = int(p[1])
            yahoodate = (int(p[2]), int(p[0]), d)
            for k in paydatemaster[j]: #for each stock, look at payment date master list
                k = k.strip()
                if k == i: #check if current day is a dividend pay day
                    paymentmaster[j][paycount] = float(paymentmaster[j][paycount])
                    divpaymt = round(paymentmaster[j][paycount]*holdingslist[hcount], 2)

                    try:
                        currprice = mf.parse_yahoo_historical(mf.fetch_historical_yahoo(tckrlist[j], yahoodate, yahoodate, dividends=False), adjusted=False)
                        icurrprice = currprice[0][2]
                        holdingslist[hcount] += round(divpaymt/icurrprice, 3) #reinvest, using yahoo data, tckrlist[j]
                    except: #sometimes paydates are on the weekend, in which case the next available day's close price is used     
                        while icurrprice == 0:
                            d+= 1
                            try:
                                yahoodate = (int(p[2]), int(p[0]), d)
                                currprice = mf.parse_yahoo_historical(mf.fetch_historical_yahoo(tckrlist[j], yahoodate, yahoodate, dividends=False), adjusted=False)
                                icurrprice = currprice[0][2]
                                holdingslist[hcount] += round(divpaymt/icurrprice, 3)
                            except:
                                pass
                paycount += 1
            hcount += 1

    finaldate = (2013, 7, 15)
    count = 0
    value = 0
    for i in numlist:
        finalprice = mf.parse_yahoo_historical(mf.fetch_historical_yahoo(tckrlist[i], finaldate, finaldate, dividends=False), adjusted=False)
        ifinalprice = finalprice[0][2]
        value += round(ifinalprice*holdingslist[count], 2) #calculate final value
        count += 1
    return value

	def get_stock_history(self):
		"""
		Fetches data using matplotlib's yahoo API (undocumented).
		A call to fetch_historical_yahoo returns a CSV file containing tuples:
		[(date, open, close, high, low, volume),...]
		
		Parameters
		----------
		None

		Returns
		-------
		dates: array of historical dates
		prices: array of associated prices on those dates
		"""
		stock_history = finance.fetch_historical_yahoo(self.symbol, self.start, self.end)
		r = mlab.csv2rec(stock_history)
		r.sort()

		dates = r.date
		prices = r.adj_close

		# convert to epoch time for highcharts
		dates = [(int(time.mktime(time.strptime(date.strftime("%Y-%m-%d"), "%Y-%m-%d"))) - time.timezone)*1000 for date in dates]

		return dates, prices

    def getData(self):
      self.dataLoaded = 1

      fh = finance.fetch_historical_yahoo(self.name, self.startdate, self.enddate)
  
      self.data = mlab.csv2rec(fh); fh.close()
      self.data.sort()

def stockpricemasterlist(tckrlist):
    stockpricedates = []
    stockpricemaster = []
    for j in tckrlist:
        temporlist = []
        temporlist2  = []
        price = mf.fetch_historical_yahoo(j, (2000, 9, 19), (2013, 7, 15), dividends=False)
        for i in price:
    
            try:
                i =i.split(',')
                i[0] = i[0].split('-')
                if i[0][1][0] == str(0):
                    
                    m = i[0][1][1]
                    
                else:
                    m = i[0][1]
                if i[0][2][0] == str(0):
                    d = i[0][2][1]
                else:
                    d = i[0][2]
                y = i[0][0]
                date = str(m)+'/'+str(d)+'/'+str(y)
                temporlist.append(date)
                temporlist2.append(i[4])
            except:
                pass
        stockpricedates.append(temporlist)
        stockpricemaster.append(temporlist2)
    return stockpricedates, stockpricemaster

def get_ticker_data_from_web_stend(ticker, startdate, enddate):
  """  return a numpy record array with fields: date, open, high, low, close, volume, adj_close) """
  
  fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
  
  r = mlab.csv2rec(fh); fh.close()
  r.sort()

  return r

def download_ticker_data(ticker):
    fullpath = data_folder + '/' + ticker
    today = enddate = datetime.date.today() + datetime.timedelta(days=1)
    startdate = enddate - datetime.timedelta(days=180)
    try:
        fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
        shutil.copy2(fh.name, fullpath)
        fh.close()
        return True, None
    except Exception, e:
        return False,str(e)

def get_ticker_data_from_web(ticker):
  """  return a numpy record array with fields: date, open, high, low, close, volume, adj_close) """
  startdate = datetime.date(1990,1,1)
  enddate   = datetime.date.today()
  
  fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
  
  r = mlab.csv2rec(fh); fh.close()
  r.sort()

  return r

def main(
    stock='aapl',
    start_date=None,
    end_date=None,
    training=None,
    time_series=None,
    animate=None,
    **kwargs
):
    training = training or 20
    end_date = end_date or datetime.today()
    start_date = start_date or end_date - timedelta(days=int((training > 1 and training) or 30))

    stock_data = mlab.csv2rec(finance.fetch_historical_yahoo(stock, start_date, end_date))
    stock_prices = tuple(reversed(stock_data.adj_close))
    dates = tuple(reversed(stock_data.date))
    training_set = stock_prices[:int(training > 1 and training or training * len(stock_data.adj_close))]

    animate = (animate in {'foreground', 'background'} and get_animated_training(dates, animate == 'background')) \
        or train

    def _time_series(window_size):
        neural_network = train_as_time_series(training_set, window_size, animate, **kwargs)
        network_samples = sliding_window(stock_prices, window_size)
        predicted_prices = list(network_samples[0][0])
        predicted_prices.extend(chain.from_iterable(neural_network.feed(inputs) for inputs, _ in network_samples))
        plot(
            stock_prices,
            predicted_prices,
            dates[len(training_set) - 1],
            x_axis=dates,
            title='Trained as Time Series',
            errors=neural_network.errors
        )

    def interpolation():
        neural_network = train_as_function_interpolation(training_set, animate, **kwargs)
        predicted_prices = list(chain.from_iterable(imap(neural_network.feed, xrange(len(stock_prices)))))
        plot(
            stock_prices,
            predicted_prices,
            dates[len(training_set) - 1],
            x_axis=dates,
            title='Trained as Function Interpolation',
            errors=neural_network.errors
        )

    if time_series:
        _time_series(time_series)
    else:
        interpolation()

def get_quote_daily_matplotlib(ticker):

    startdate = datetime.date(2006, 1, 1)
    today = enddate = datetime.date.today()
    #ticker = 'SPY'


    fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
    # a numpy record array with fields: date, open, high, low, close, volume, adj_close

    r = mlab.csv2rec(fh)
    fh.close()
    r.sort()
    return r

def mr_stock_price(ticker):
    now = datetime.datetime.now()
    earlier =  now - datetime.timedelta(days = 4)
    price = mf.fetch_historical_yahoo(ticker, (earlier.year, earlier.month, earlier.day), (now.year, now.month, now.day), dividends=False)
     
    for r in price:
        r = r.split(',')
        try:
            float(r[-1])
            mr_price= float(r[-1].strip())
            break
#            shareprices.append(float(r[-1].strip()))
        except:
            continue    
    return mr_price

def get_benchmark(tick,y1,m1,d1,y2,m2,d2):
    startdate = datetime.date(y1, m1, d1)
    enddate = datetime.date(y2, m2, d2)
    ticker= tick
    benchmark_df= read_csv(finance.fetch_historical_yahoo(ticker, startdate, enddate),index_col='Date')
    benchmark_Index=benchmark_df['Close']
    benchmark_Index=benchmark_Index.to_frame()
    benchmark_Index.columns=['values']
    benchmark_Index=benchmark_Index.reset_index()
    benchmark_Index["New Date"]=benchmark_Index["Date"].map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d').strftime('%d/%m/%y'))
    benchmark_Index["Date"]=benchmark_Index["New Date"].map(lambda x: datetime.datetime.strptime(x, '%d/%m/%y'))
    benchmark_Index=benchmark_Index.iloc[::-1]
    benchmark_Index=benchmark_Index.drop(["New Date"],1)
    benchmark_Index=benchmark_Index.set_index(["Date"])
    benchmark_Index=benchmark_Index.divide(benchmark_Index.ix[0])
    return benchmark_Index

    def _fetch_yahoo_data(self,startdate,enddate,dividends):

        ticker=self.full_ticker


        try:
            fh = finance.fetch_historical_yahoo(ticker, startdate, enddate,None,dividends)
            # From CSV to REACARRAY
            r = mlab.csv2rec(fh); fh.close()
            # Order by Desc
            r.sort()
        except:
            print "Error: %s,%s,%s" % (sys.exc_info()[0],sys.exc_info()[1],sys.exc_info()[2])
            print "Error loading yahoo data for %s %s" % (self,str(startdate))
            return None


        return r

def hist_data_collection():


    conn = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "sshravan91", db = "test1")
        

    c = conn.cursor(MySQLdb.cursors.DictCursor)
    c.execute("SELECT DISTINCT(Symbol) AS SYMBOL FROM HISTORY")            
    symbol_data = c.fetchall()

        
    for var2 in xrange(c.rowcount):            
        tempvar = symbol_data[var2]['SYMBOL']
        print tempvar
        symbolslist.append(tempvar.lower())
            
    c.close()
    del c
    conn.close()


    today = enddate = date.today()
    startdate = today - timedelta(1)
    
    conn = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "sshravan91", db = "test1")

    for j in xrange(len(symbolslist)):

        ticker = str(symbolslist[j])
        fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
        r = mlab.csv2rec(fh); fh.close()
        r.sort()
        #print r
        
        query = "INSERT INTO HIST_STOCK (_DATE, NAME, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ_CLOSE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        x = conn.cursor()
        x.execute(query, (str(r.date[0]), ticker, str(r.open[0]), str(r.high[0]), str(r.low[0]), str(r.close[0]), str(r.volume[0]), str(r.adj_close[0])))
        conn.commit()
        row = x.fetchall()
        x.close()
        del x
    
    conn.close()

def update_ticker_prices():
    for ticker, in db.session.query(Securities.ticker).all():
        data = fetch_historical_yahoo(ticker, datetime.now() - timedelta(days=7), datetime.now())
        reader = csv.DictReader(data)
        for row in reader:
            new_record = SecurityPrices()
            for key in row:
                row[key.lower().replace('adj close', 'adjusted_close')] = row.pop(key)
            for column in inspect(SecurityPrices).attrs:
                if column.key == 'id':
                    continue
                elif column.key == 'ticker':
                    setattr(new_record, column.key, ticker)
                else:
                    setattr(new_record, column.key, row[column.key])
            try:
                db.session.add(new_record)
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

 def fetchStockData(cls, beg, end, ffile, yahoo=True, ticker="AAPL"):
     """Depending on the boolean parameter yahoo, it will fetch the stock data from yahoo finance,
     or from the file, given that the file contains date columns and price columns where ticker name is the
     header for prices for that security."""
     if yahoo is True:
         p = fetch_historical_yahoo(ticker, beg, end)
         result = parse_yahoo_historical_ochl(p, False, True)
         return result
     else:
         result = os.path.exists(ffile)
         if result is False:
             raise FileNotFoundError("Error: {} file was not found.".format(ffile))
         f = pd.read_csv(ffile)
         max = len(f.columns)
         for i in range(1, max):
             cur_name = f.columns.values[i]
             if cur_name == ticker:
                 break
         prices = f.values[:, i]  # f.values and f.iloc have actual data starting from 0 index. First is row.
         max_rows = len(f.values[:, 0])
         """date, year, month, day, d, open, close, high, low, volume, adjusted_close"""
         results = []
         for j in range(0, max_rows):
             dt = datetime.date(*[int(val) for val in f.values[j, 0].split('-')])
             results.append((dt, 0, 0, 0,
                         0, 0, float(prices[j]), 0, 0, 0, float(prices[j])))
         my_type = np.dtype(
             [(str('date'), object),
             (str('year'), np.int16),
             (str('month'), np.int8),
             (str('day'), np.int8),
             (str('d'), np.float),
             (str('open'), np.float),
             (str('close'), np.float),
             (str('high'), np.float),
             (str('low'), np.float),
             (str('volume'), np.float),
             (str('aclose'), np.float)])
         d = np.array(results, dtype=my_type)
         return d.view(np.recarray)
     return result

def div(stock_symbol):
    today = date.today()
    start = (today.year - year_to_cal, today.month, today.day)
    a = fetch_historical_yahoo(stock_symbol, start, today, cachename=None,dividends=True)
    dd = pd.DataFrame(list(a))
    
    re1='.*?'	# Non-greedy match on filler
    re2='([+-]?\\d*\\.\\d+)(?![-+0-9\\.])'	# Float 1
    rg = re.compile(re1+re2,re.IGNORECASE|re.DOTALL)
    sum = 0
    
    #print dd[0][2]
    for i in xrange(dd.shape[0]):    
        m = rg.search(dd[0][i])
        if m:
            if m.group(1) == 'None':
                pass
            else:
                sum += float(m.group(1))
        #print "("+float1+")"+"\n"
    
    return sum/year_to_cal

def hist_data_collection(ticker_up):
    ticker=ticker_up.lower()
    today = enddate = date.today()
    startdate = datetime.date(2000,1,1)
    
    conn = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "sshravan91", db = "test1")

    fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
    r = mlab.csv2rec(fh); fh.close()
    r.sort()
    
    
    for j in xrange(len(r)):

        query = "INSERT INTO HIST_STOCK (_DATE, NAME, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ_CLOSE) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        x = conn.cursor()
        x.execute(query, (str(r.date[j]), ticker.upper(), str(r.open[j]), str(r.high[j]), str(r.low[j]), str(r.close[j]), str(r.volume[j]), str(r.adj_close[j])))
        conn.commit()
        row = x.fetchall()
        x.close()
        del x
    
    conn.close()

    def getDataFromYahoo(self, symbol, startDate):      

        hist = fi.fetch_historical_yahoo(symbol, 
                                         startDate, 
                                         datetime.today())
        
        newEntries = []                                 # new container

        for i in hist:                                  # split each line 
            newEntries.append(i[:-1].replace("-","/").split(','))

        try:

            col = newEntries.pop(0)

            for i in newEntries:                        # convert i[0] in datetime
                i[0] = datetime.strptime(i[0], '%Y/%m/%d')

            data = pd.DataFrame(newEntries, columns = col)

        except ValueError as e:
            logging.error('error in data set')
        return data

def ticker_data(ticker):
    print ticker
    startdate = datetime.date(2012,10,28)
    today = enddate = datetime.date.today()
    pricesd = np.zeros(1000)
    datesd = np.zeros(1000)
    volumed = np.zeros(1000)

    try:
        fh = finance.fetch_historical_yahoo(ticker, startdate, enddate)
        # a numpy record array with fields: date, open, high, low, close, volume, adj_close)
        r = mlab.csv2rec(fh); fh.close()
        r.sort()
        prices = r.adj_close
        dates = r.date
        volume = r.volume
    except:
        prices = pricesd
        dates = datesd
        volume = volumed
        print "Unexpected error:"
#        raise
        
    return prices, dates, np.mean(volume), volume

import csv
import codecs
from datetime import date
import sqlite3
import numpy as np
import matplotlib.finance as finance
import matplotlib.mlab as mlab
import sys


server = 'HKI.db'

startdate = date(2016,6,1)
enddate = date(2016,6,30)

fh = finance.fetch_historical_yahoo('^HSCE', startdate, enddate)#, cachename=ticker + '.csv'
# a numpy record array with fields: date, open, high, low, close, volume, adj_close)

r = mlab.csv2rec(fh); fh.close()
r.sort()
r=r[r.volume>0]



conn = sqlite3.connect('HKI.db')
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS HKITRI (
    secid text NOT NULL,
    tradedate text,
    closeprice real,