fea

from urllib.request import Request, urlopen
import json
from pandas.io.json import json_normalize
import pandas as pd
import xlsxwriter



Prefix = "https://api.iextrading.com/1.0/tops?symbols="
#Suffix3 = input()
Suffix1 = "IBM"
linke1 = Prefix + Suffix1



# print (linke1)
# print (linke2)

def return_valid_securities():
    """
    Return a list of valid securities
    :param securities: list of securities
    :return: list of valid securities
    """

    valid_securities = _url_to_dataframe(linke1)['symbol']


    return [x for x in securities if x in set(valid_securities)]


def _url_to_dataframe(url, nest=None):
    """
    Takes a url and returns the response in a pandas dataframe
    :param url: str url
    :param nest: column with nested data
    :return: pandas dataframe containing data from url
    """

    request = Request(url)
    response = urlopen(request)
    elevations = response.read()
    data = json.loads(elevations)

    if nest:
        data = json_normalize(data[nest])
    else:
        data = json_normalize(data)

    return pd.DataFrame(data)


#while (Suffix1 == "IBM"):

def get_latest_quote_and_trade1():
    """
    Gets latest quote and trade data
    :param securities: list of securities
    :return: pandas dataframe containing data for valid securities
    """

    df = _url_to_dataframe(linke1)
    df['lastSaleTime'] = pd.to_datetime(df['lastSaleTime'], unit='ms')
    df['lastUpdated'] = pd.to_datetime(df['lastUpdated'], unit='ms')
    df.set_index(['symbol'], inplace=True)
    writer = pd.ExcelWriter('output4.xlsx', engine='xlsxwriter')
    dataframe = pd.DataFrame({'output': [df,df,df]})
    return df.to_excel(writer, sheet_name='Stocks', header=True, engine='xlswriter',)
    writer.save()

        #return df.to_string()
        #return df.to_excel('exceloutput.xlsx', sheet_name='sheet1', index=False,)


print(get_latest_quote_and_trade1())
print(linke1)
'''
output1 = get_latest_quote_and_trade1()
dataframe = pd.DataFrame ({'output': [output1]})
writer = pd.ExcelWriter('output3.xlsx', engine='xlsxwriter')
dataframe.to_excel(writer, sheet_name='sheet2', header=True)
workbook = writer.book
worksheet = writer.sheets['sheet2']
writer.save()
'''