# -*- coding: utf-8 -*-
"""data.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1AN7fxwjfSy0E33IGeL_rqV-eG_PpSDIe
"""

#!pip install yahoofinancials
#!pip install dnspython

import numpy as np 
import pandas as pd 
import matplotlib.pyplot as plt
import datetime as dt
import pymongo 
import json
from pytz import timezone
from yahoofinancials import YahooFinancials

PASSWORD = '#######'

CRYPTO_LIST = { 'bitcoin': 'BTC-USD', 'dash' : 'DASH-USD', 'ethereum' : 'ETH-USD', 'litecoin' : 'LTC-USD', 'monero' : 'XMR-USD',
                'ripple' : 'XRP-USD' }

FOREX_LIST = { 'AustralianDollar' : 'AUDUSD=X', 'Euro' : 'EURUSD=X', 'NewZealandDollar' : 'NZDUSD=X', 'BritishPound' : 'GBPUSD=X', 
              'BrazilianReal' : 'BRLUSD=X', 'CanadianDollar' : 'CADUSD=X', 'ChineseYuan' : 'CNYUSD=X', 'HongKongDollar' : 'HKDUSD=X', 
              'IndianRupee' : 'INRUSD=X', 'KoreanWon' : 'KRWUSD=X', 'MexicanPeso' : 'MXNUSD=X', 'SouthAfricanRand' : 'ZARUSD=X', 
              'SingaporeDollar' : 'SGDUSD=X', 'DanishKrone' : 'DKKUSD=X', 'JapaneseYen' : 'JPYUSD=X', 'MalaysianRinggit' : 'MYRUSD=X', 
              'NorwegianKrone' : 'NOKUSD=X', 'SwedishKrona' : 'SEKUSD=X', 'SriLankanRupee' : 'LKRUSD=X', 'SwissFranc' : 'CHFUSD=X', 
              'NewTaiwanDollar' : 'TWDUSD=X', 'ThaiBaht' : 'THBUSD=X' }

STOCK_INDICES = { 'S&P500' : '^GSPC', 'Dow30' : '^DJI', 'NASDAQ' : '^IXIC', 'Russel2000' : '^RUT', 'S&P/TSX' : '^GSPTSE', 
                  'IBOVESPA' : '^BVSP', 'IPCMEXICO' : '^MXX', 'Nikkei225' : '^N225', 'HANGSENGINDEX' : '^HSI', 
                  'SSE' : '000001.SS', 'ShenzhenComponent' : '399001.SZ', 'TSEC' : '^TWII', 'KOSPI' : '^KS11', 'STI' : '^STI', 
                  'Jakarta' : '^JKSE', 'FTSEBursaMalaysiaKLCI' : '^KLSE', 'S&P/ASX200' : '^AXJO',  'S&P/NZX50' : '^NZ50', 
                  'S&PBSE' : '^BSESN', 'FTSE100' : '^FTSE', 'DAX' : '^GDAXI', 'CAC40' : '^FCHI', 
                  'ESTX50' : '^STOXX50E', 'EURONEXT100' : '^N100', 'BEL 20' : '^BFX' }

def delete_collection(database, collection=None):
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + database + "?retryWrites=true&w=majority")
  if database == 'crypto':
    mydb = client["crypto"]
    if collection == None:
      for key, value in CRYPTO_LIST.items():
        mycol = mydb[key]
        mycol.drop()
    else:
      mycol = mydb[collection]
      mycol.drop()
  client.close()

def create_collection(database, collection, ticker, start_date, end_date):
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + database + "?retryWrites=true&w=majority")
  db = client[database]
  col = db[collection]
  yf = YahooFinancials(ticker)
  prices = yf.get_historical_price_data(start_date, end_date, 'daily')
  DF = pd.DataFrame.from_dict(prices[ticker]['prices'])
  DF = DF.drop(['date', 'volume', 'adjclose'], axis=1)
  to_add = []
  for i in range(len(DF)):
    tmp = {}
    for j in range(len(DF.columns)):
      tmp[str(DF.columns[j])] = str(DF.iloc[i][j])
    to_add.append(tmp)
  col.insert_many(to_add)
  client.close()

def build_database(start_date, end_date):
  try:
    delete_collection('crypto', collection=None)
  except:
    pass
  for key, value in CRYPTO_LIST.items():
    create_collection('crypto', key, value, str(dt.datetime.strptime(start_date, '%Y-%m-%d') + dt.timedelta(days=1))[:10], end_date)

  try:
    delete_collection('forex', collection=None)
  except:
    pass
  for key, value in FOREX_LIST.items():
    create_collection('forex', key, value, str(dt.datetime.strptime(start_date, '%Y-%m-%d') + dt.timedelta(days=1))[:10], end_date)

  try:
    delete_collection('stock', collection=None)
  except:
    pass
  for key, value in STOCK_INDICES.items():
    create_collection('stock', key, value, start_date, end_date)

def get_datelist(start_date, end_date):
  days = int((dt.datetime.strptime(end_date, '%Y-%m-%d') - dt.datetime.strptime(start_date, '%Y-%m-%d')).days)
  date_list = [str(dt.datetime.strptime(end_date, '%Y-%m-%d') - dt.timedelta(days=x))[:10] for x in range(days)]
  return date_list

def get_data(database, collection):
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + database + "?retryWrites=true&w=majority")
  db = client[database]
  col = db[collection]
  result = []
  for x in col.find():
    result.append( { 'high' : x['high'], 'low' : x['low'], 'open' : x['open'], 'close' : x['close'], 'formatted_date' : x['formatted_date']} )
  return result

def to_dataframe(dictionary):
  return pd.DataFrame.from_dict(dictionary)

def preprocessed_data(database, collection):
  date_list = get_datelist('2015-09-30', '2020-12-03')
  df = to_dataframe(get_data(database, collection))
  missing_dates = []
  for i in range(len(date_list)):
    if date_list[i] not in df['formatted_date'].to_list():
      missing_dates.append(date_list[i]) 
  for i in range(len(date_list)):
    try:
      next_day = str(dt.datetime.strptime(df[['formatted_date']].iloc[i][0], '%Y-%m-%d') + dt.timedelta(days=1))[:10]
      if next_day in missing_dates:
        row = pd.DataFrame({ "high" : df[['high']].iloc[i][0], "low" : df[['low']].iloc[i][0], "open" : df[['open']].iloc[i][0], 
                            "close" : df[['close']].iloc[i][0], "formatted_date" : next_day }, index=[i+1])
        df = pd.concat([df.iloc[:i+1], row, df.iloc[i+1:]]).reset_index(drop=True)
      if df[['formatted_date']].iloc[i][0] in df['formatted_date'].to_list()[:i]:
        df = df.drop([i])
      if df[['open']].iloc[i][0] == 'nan':
        df.at[i, 'open'] = df[['open']].iloc[i-1][0]
      if df[['close']].iloc[i][0] == 'nan':
        df.at[i, 'close'] = df[['close']].iloc[i-1][0]
      if df[['high']].iloc[i][0] == 'nan':
        df.at[i, 'high'] = df[['high']].iloc[i-1][0]
      if df[['low']].iloc[i][0] == 'nan':
        df.at[i, 'low'] = df[['low']].iloc[i-1][0]
    except IndexError:
      pass
  convert_dict = { 'high' : float, 'low' : float, 'open' : float, 'close' : float, 'formatted_date' : str }
  df = df.astype(convert_dict)
  return df

def build_nn_database():
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + 'nn_data' + "?retryWrites=true&w=majority")
  db = client['nn_data']
  col = db['nn_data']
  result = {}
  for key, value in CRYPTO_LIST.items():
    result[key] = np.array(preprocessed_data('crypto', key)['close'].to_list(), dtype=np.float32)
  for key, value in FOREX_LIST.items():
    result[key] = np.array(preprocessed_data('forex', key)['close'].to_list(), dtype=np.float32)
  for key, value in STOCK_INDICES.items():
    result[key] = np.array(preprocessed_data('stock', key)['close'].to_list(), dtype=np.float32)

  DF = pd.DataFrame.from_dict(result)
  to_add = []
  for i in range(len(DF)):
    tmp = {}
    for j in range(len(DF.columns)):
      tmp[str(DF.columns[j])] = str(DF.iloc[i][j])
    to_add.append(tmp)
  col.insert_many(to_add)
  client.close()

nn_data_columns = ['bitcoin', 'dash', 'ethereum', 'litecoin', 'monero', 'ripple', 'AustralianDollar', 'Euro', 'NewZealandDollar', 
                   'BritishPound', 'BrazilianReal', 'CanadianDollar', 'ChineseYuan', 'HongKongDollar', 'IndianRupee', 'KoreanWon', 
                   'MexicanPeso', 'SouthAfricanRand', 'SingaporeDollar', 'DanishKrone', 'JapaneseYen', 'MalaysianRinggit', 
                   'NorwegianKrone', 'SwedishKrona', 'SriLankanRupee', 'SwissFranc', 'NewTaiwanDollar', 'ThaiBaht', 'S&P500', 
                   'Dow30', 'NASDAQ', 'Russel2000', 'S&P/TSX', 'IBOVESPA', 'IPCMEXICO', 'Nikkei225', 'HANGSENGINDEX', 
                   'SSE', 'ShenzhenComponent', 'TSEC', 'KOSPI', 'STI', 'Jakarta', 'FTSEBursaMalaysiaKLCI', 'S&P/ASX200', 'S&P/NZX50', 
                   'S&PBSE', 'FTSE100', 'DAX', 'CAC40', 'ESTX50', 'EURONEXT100', 'BEL 20']

def get_nn_data():
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + 'nn_data' + "?retryWrites=true&w=majority")
  db = client['nn_data']
  col = db['nn_data']
  result = []
  for x in col.find():
    result.append(x)
  df = pd.DataFrame(result)
  df = df.drop(['_id'], axis=1)  	
  return df

def get_new_data(database, collection):
  if database == 'stock':
    start_date = str((dt.datetime.now(timezone('EST')) - dt.timedelta(days=1)))[:10]
    end_date = str((dt.datetime.now(timezone('EST')) - dt.timedelta(days=0)))[:10]
    ticker = STOCK_INDICES[collection]
  elif database == 'forex' or database == 'crypto':
    start_date = str((dt.datetime.now(timezone('EST')) - dt.timedelta(days=1)))[:10]
    end_date = start_date
    if database == 'forex':
      ticker = FOREX_LIST[collection]
    elif database == 'crypto':
      ticker = CRYPTO_LIST[collection]
  yf = YahooFinancials(ticker)
  prices = yf.get_historical_price_data(start_date, end_date, 'daily')[ticker]['prices'][0]
  result = { 'high' : prices['high'], 'low' : prices['low'], 'open' : prices['open'], 'close' : prices['close'], 'formatted_date' : prices['formatted_date'] }
  return result

def update_database(database, collection):
  client = pymongo.MongoClient("mongodb+srv://kmhatre:" + PASSWORD + "@crypto.j5hw0.mongodb.net/" + database + "?retryWrites=true&w=majority")
  db = client[database]
  col = db[collection]
  new_data = get_new_data(database, collection)
  col.insert_one(new_data)
  client.close()

