#!/usr/bin/env python3
import sys
import pandas as pd
import json
import pycurl
import certifi
import io
import odswriter
writer = pd.ExcelWriter('stock-data.ods', engine='odf')
# pandas engine odf requires odfpy
#df = pd.read_excel(sys.argv[1], sys.argv[2], engine=sys.argv[3])
df = pd.read_excel(sys.argv[1], sheet_name='Tracking', header=None, index_col=None)
print(df)
print('-----')
print(df.index)
print('-----')
print(df.columns)
print('-----')
print(df.at[8, 1])
print('-----')
# Rows 9-14, 18-64
ranges = []
ranges.extend(list(range(8, 14)))
ranges.extend(list(range(17, 63)))
baseurl='https://api.londonstockexchange.com/api/gw/lse/instruments/alldata/'
data = {}
for i in ranges:
  print(df.at[i,1])
  ticker=df.at[i,1]
  url = baseurl + ticker
  print(url)
  buffer = io.BytesIO()
  c = pycurl.Curl()
  c.setopt(c.WRITEDATA, buffer)
  c.setopt(c.CAINFO, certifi.where())
  c.setopt(c.URL, url) 
  c.perform()
  c.close()
  body = buffer.getvalue()
  # Body is a byte string.
  # We have to know the encoding in order to print it to a text file
  # such as standard output.
  #print(body.decode('iso-8859-1'))
  print(body.decode('utf8'))
  j = json.loads(body.decode('utf8'))
  print(j.keys())
  if ('message' in j.keys() and j['message'].startswith('Not found instrument with tidm')):
    pass
  else:
    print(j['tidm'])
    print(j['lastprice'])
    print(j['marketcapitalization'])
    shares = j['marketcapitalization'] / j['lastprice']
    print(shares)
    df.at[i,3] = j['lastprice']
    for k in j.keys():
      if not k in data:
        data[k] = []
      data[k].append(j[k])
print('-----')
print(data)
df2 = pd.DataFrame(data)
print(df2)
df2.to_excel(writer, engine='odf')
df2.to_csv('stock-data.csv')