import requests
import time
import pandas as pd
import json
import itertools
from datetime import datetime
import credsetting

print(datetime.now().strftime("%H:%M:%S"))

csv_stocklist = pd.read_csv('C:\\Users\\rleva\\Desktop\\StockList_i2.csv')

stocklist = csv_stocklist['Ticker'].values.tolist()

def get_ebitda_marketcap() :
    url = f"https://api.twelvedata.com/statistics?symbol={ticker}&apikey={credsetting.twelvedata_API_Key}"
    response = requests.get(url).json()
    symbol = response['meta']['symbol']
    ebitda = round(response['statistics']['financials']['income_statement']['ebitda'],2)
    market_cap = response['statistics']['valuations_metrics']['market_capitalization']
    return((symbol,round((ebitda/market_cap),2)))
    
new_list = []

for ticker in stocklist[:10]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[10:19]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[19:29]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[29:39]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[39:49]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[49:59]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[59:69]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[69:79]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[79:89]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[89:99]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[99:109]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[109:119]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[119:129]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[129:139]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[139:149]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[149:159]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[159:169]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[169:179]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[179:189]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[189:199]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[199:209]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[209:219]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[219:229]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[229:239]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[239:249]:
    new_list.append(get_ebitda_marketcap())

time.sleep(61)

print(datetime.now().strftime("%H:%M:%S"))

for ticker in stocklist[249:259]:
    new_list.append(get_ebitda_marketcap())

print(new_list)

final_list = dict(new_list)

print(final_list)

tier1_list = []
tier2_list = []
tier3_list = []
tier4_list = []
no_buy = []
    
for key in final_list.keys() :
    if final_list[key] > .50 :
        tier1_list.append(key)
    elif final_list[key] > .4 :
        tier2_list.append(key)
    elif final_list[key] > .3 :
        tier3_list.append(key)
    elif final_list[key] > .2 :
        tier4_list.append(key)
    else :
        no_buy.append(key)

print(final_list)
print(tier1_list)
print(tier2_list)
print(tier3_list)
print(tier4_list)
print(no_buy)

panda_list1 = pd.DataFrame(tier1_list,columns= ['Tickers'])
panda_list2 = pd.DataFrame(tier2_list,columns= ['Tickers'])
panda_list3 = pd.DataFrame(tier3_list,columns= ['Tickers'])
panda_list4 = pd.DataFrame(tier4_list,columns= ['Tickers'])
panda_list5 = pd.DataFrame(no_buy,columns= ['Tickers'])

with pd.ExcelWriter('Stock_Portfolio_Allocator_v6.xlsx') as writer:
    panda_list1.to_excel(writer, sheet_name='Tier 1 Stocks')
    panda_list2.to_excel(writer, sheet_name='Tier 2 Stocks')
    panda_list3.to_excel(writer, sheet_name='Tier 3 Stocks')
    panda_list4.to_excel(writer, sheet_name='Tier 4 Stocks')
    panda_list5.to_excel(writer, sheet_name='Stocks to Sell')