· 6 years ago · Jan 15, 2020, 01:14 PM
1import json
2import requests
3from bs4 import BeautifulSoup
4import datetime
5from selenium import webdriver
6from selenium.webdriver.firefox.options import Options
7from openpyxl import Workbook
8from openpyxl import load_workbook
9from xlsx2html import xlsx2html
10
11API_TOKEN = 'YOUY API KEY HERE'
12WTD_BASE_URL = "https://api.worldtradingdata.com/api/v1/stock"
13WTD_HISTORY_BASE_URL = "https://api.worldtradingdata.com/api/v1/history"
14
15ticker_names = [
16 'S&P 500',
17 'Nasdaq Comp',
18 'Dow Jones',
19 'Russell2000',
20 'OSEBX',
21 'OMXS30B',
22 'Euro Stoxx 50',
23 'DAX',
24 'CAC 40',
25 'FTSE 100',
26 'FTSE MIB',
27 'IBEX 35',
28 'PSI 20',
29 'Nikkei 225',
30 'KOSPI',
31 'HANG SENG',
32 'Bombay SENSEX',
33 'CSI 300',
34 'Shanghai Composite',
35 'FTSE China A50',
36 '3188 HK',
37 'HSCEI',
38 'Olje',
39 'Kobber',
40 'Aluminium',
41 'Gull',
42 'USDNOK',
43 'EURNOK',
44 'GBPNOK',
45 'EURUSD',
46 'USDJPY',
47 'iShare High Yield',
48 'US 10 Yr',
49 'Trend Global',
50 'Trend Europa',
51 'Trend USA'
52 ]
53
54symbols = {
55 'Shanghai Composite':'000001.SS',
56 'CSI 300':'000300.SS',
57 'HSCEI':'^HSCE',
58 'OSEBX':'OSEBX.OL',
59 'iShare High Yield':'HYG',
60 'US 10 Yr':'^TNX',
61 'USDNOK':'USDNOK=X',
62 'USDJPY':'USDJPY=X',
63 'EURUSD':'EURUSD=X',
64 'GBPNOK':'GBPNOK=X',
65 'EURNOK':'EURNOK=X',
66 'Gull':'GC=F',
67 'Trend Global':'TRENDEN:NO',
68 'Trend Europa':'TRENDEU:NO',
69 'Trend USA':'TRENDUS:NO',
70 'Olje':'CO1:COM',
71 'Kobber':'LMCADS03:COM',
72 'Aluminium':'LMAHDS03:COM',
73 'S&P 500':'^INX',
74 'Nasdaq Comp':'^IXIC',
75 'Dow Jones':'^DJI',
76 'Russell2000':'^RUT',
77 'OMXS30B':'^OMXS30',
78 'Euro Stoxx 50':'^SX5E',
79 'DAX':'^DAX',
80 'CAC 40':'^PX1',
81 'FTSE 100':'^UKX',
82 'FTSE MIB':'^FTSEMIB',
83 'IBEX 35':'^IB',
84 'PSI 20':'^PSI20',
85 'Nikkei 225':'^NI225',
86 'KOSPI':'KOSPI.KS',
87 'HANG SENG':'^HSI',
88 'Bombay SENSEX':'^SENSEX',
89 'FTSE China A50':'^XIN9',
90 '3188 HK':'3188.HK'
91 }
92
93wtd_list = [
94 'S&P 500',
95 'Nasdaq Comp',
96 'Dow Jones',
97 'Russell2000',
98 'OMXS30B',
99 'Euro Stoxx 50',
100 'DAX',
101 'CAC 40',
102 'FTSE 100',
103 'FTSE MIB',
104 'IBEX 35',
105 'PSI 20',
106 'Nikkei 225',
107 'KOSPI',
108 'HANG SENG',
109 'Bombay SENSEX',
110 'FTSE China A50',
111 '3188 HK'
112 ]
113
114yahoo_list = [
115 'Shanghai Composite',
116 'CSI 300',
117 'HSCEI',
118 'OSEBX',
119 'iShare High Yield',
120 'US 10 Yr',
121 'USDNOK',
122 'USDJPY',
123 'EURUSD',
124 'GBPNOK',
125 'EURNOK',
126 'Gull'
127 ]
128
129bloomberg_list = [
130 'Trend Global',
131 'Trend Europa',
132 'Trend USA',
133 'Olje',
134 'Kobber',
135 'Aluminium'
136 ]
137
138def date(day):
139 if(day == 'yesterday'):
140 return str(datetime.datetime.now()-datetime.timedelta(days=1))[:10]
141 elif(day == 'lastyear'):
142 return str(datetime.datetime.now()-datetime.timedelta(days=365))[:10]
143 elif(day == 'yearstart'):
144 return str(datetime.datetime.now())[0:4]+'-01-01'
145 elif(day == '' or day == 'today'):
146 return str(datetime.datetime.now())[:10]
147 else:
148 return 'Error!'
149
150def getDataFromYahoo(symbol):
151 try:
152 return_data = {}
153 url = requests.get("https://finance.yahoo.com/quote/{}".format(symbol))
154 soup = BeautifulSoup(url.content,features='lxml')
155 header_block = list(soup.find_all(id='quote-header-info')[0].children)
156 price_text = header_block[2].find_all('span')[0].get_text().replace(',','')
157 day_change_block = header_block[2].find_all('span')[1].get_text()
158 percent_day_change = str(float(day_change_block[day_change_block.index('(')+1:day_change_block.index(')')-1]))
159 return_data = {'price':price_text, 'change_pct':percent_day_change}
160 return return_data
161 except:
162 print('Error while getting data from yahoo!')
163 return ''
164
165def getBloombergData(ticker_name):
166 options = Options()
167 options.headless = True
168 driver = webdriver.Firefox(options=options,executable_path=r'webdriver.exe')
169 driver.get("https://www.bloomberg.com/quote/{}".format(symbols[ticker_name]))
170 raw_html = str(driver.page_source.encode("utf-8"))
171 driver.close()
172 soup = BeautifulSoup(raw_html,'lxml')
173 textBlock = soup.find_all('span',string='{}'.format(symbols[ticker_name]))
174 valueDataBlock = textBlock[0].find_parent('section').findNext('section').find_all('span')
175 currentValue = valueDataBlock[0].get_text().replace(',','')
176 changePercent = str(float(valueDataBlock[3].get_text()[:-1]))
177 try:
178 ytdValue=soup.find_all('span',string='YTD Return')[0].nextSibling.get_text()
179 ytdValue=str(float(ytdValue[:-1]))
180 except:
181 ytdValue=''
182 try:
183 oneYearReturnValue=soup.find_all('span',string='1 Year Return')[0].nextSibling.get_text()
184 oneYearReturnValue=str(float(oneYearReturnValue[:-1]))
185 except:
186 oneYearReturnValue=''
187
188 returnJSON = {symbols[ticker_name]:{date('today'):{'price':currentValue,'day_change':changePercent, 'year_change':oneYearReturnValue, 'ytd_change':ytdValue}}}
189 return returnJSON
190
191def getCurrentDataFromWTC(symbol):
192 infos = {'symbol':symbol, 'api_token':API_TOKEN}
193 api_requests = requests.get(WTD_BASE_URL,params=infos)
194 try:
195 return json.loads(api_requests.content)
196 except:
197 print("Unexpected Error #1")
198
199def findLocalHistoryData(tickerName,date):
200 try:
201 ticker_filename = 'json/data.json'
202 with open(ticker_filename,'r') as file:
203 jsonDictionary = json.load(file)
204 return jsonDictionary[symbols[tickerName]][date]['price']
205 except:
206 print('Local history missing! Continuing without it!')
207 return ''
208
209def writeLocalHistory(data):
210 with open('json/data.json','r+') as file:
211 dictionary = {}
212 try:
213 dictionary = json.load(file)
214 except:
215 pass
216 dictionary.update(data)
217 file.seek(0)
218 file.truncate(0)
219 json.dump(dictionary,file)
220
221def exportToExcel(JSONData,date,name):
222 excel_workbook = load_workbook('others/stocks_temp.xlsx')
223 excel_worksheet = excel_workbook.active
224 dataList = []
225 for ticker_name in ticker_names:
226 indData = []
227 base=JSONData[symbols[ticker_name]][date]
228
229 if(base['price'] != ''):
230 indData += [float(base['price'].replace(',',''))]
231 else:
232 indData += ['']
233
234 if(base['day_change'] != ''):
235 indData += [float(base['day_change'].replace(',',''))/100]
236 else:
237 indData = ['']
238
239 if(base['year_change'] != ''):
240 indData += [float(base['year_change'].replace(',',''))/100]
241 else:
242 indData += ['']
243
244 if(base['ytd_change'] != ''):
245 indData += [float(base['ytd_change'].replace(',',''))/100]
246 else:
247 indData += ['']
248 dataList += [indData]
249
250 #USA Tickers
251 for j in range(4):
252 for i in range(4):
253 excel_worksheet['{}{}'.format(chr(ord('B')+i), str(4+j))] = dataList[j][i]
254
255 #Europe Tickers
256 for j in range(9):
257 for i in range(4):
258 excel_worksheet['{}{}'.format(chr(ord('B')+i), str(10+j))] = dataList[4+j][i]
259
260 #Asia Tickers
261 for j in range(9):
262 for i in range(4):
263 excel_worksheet['{}{}'.format(chr(ord('B')+i), str(21+j))] = dataList[13+j][i]
264
265 #Commodities
266 for j in range(4):
267 for i in range(4):
268 excel_worksheet['{}{}'.format(chr(ord('H')+i), str(2+j))] = dataList[22+j][i]
269
270 #Currencies
271 for j in range(5):
272 for i in range(4):
273 excel_worksheet['{}{}'.format(chr(ord('H')+i), str(10+j))] = dataList[26+j][i]
274
275 #Interest & Bonds
276 for j in range(2):
277 for i in range(4):
278 excel_worksheet['{}{}'.format(chr(ord('H')+i), str(21+j))] = dataList[31+j][i]
279
280 #Funds
281 for j in range(3):
282 for i in range(4):
283 excel_worksheet['{}{}'.format(chr(ord('H')+i), str(25+j))] = dataList[33+j][i]
284
285 excel_workbook.save('{}.xlsx'.format(name))
286 print("Exported to Excel!")
287
288def exportToHTML(excel_file,export_file_name):
289 out_html = xlsx2html(excel_file)
290 out_html.seek(0)
291 with open(export_file_name,'w') as file:
292 file.write(out_html.read())
293
294def getAllValues():
295 allData = {}
296 for ticker_name in ticker_names:
297 if ticker_name in wtd_list:
298
299 #For current price and day_change
300 responseJSON = getCurrentDataFromWTC(symbols[ticker_name])
301 currentPrice = responseJSON['data'][0]['price']
302 day_change = responseJSON['data'][0]['change_pct']
303
304 #For Year Change
305 year_change = ""
306 priceLastYear = findLocalHistoryData(ticker_name,date('lastyear'))
307 try:
308 year_change = str((float(currentPrice)-float(priceLastYear))/(float(priceLastYear))*100)
309 except:
310 year_change = ''
311
312 #For YTD Change
313 ytd_change = ""
314 priceOnYearStart = findLocalHistoryData(ticker_name,date('yearstart'))
315 try:
316 ytd_change = str(((float(currentPrice)-float(priceOnYearStart))/float(priceOnYearStart))*100)
317 except:
318 ytd_change = ''
319
320 parsedJSON = {symbols[ticker_name]:{date('today'):{'price':currentPrice,'day_change':day_change, 'year_change':year_change, 'ytd_change':ytd_change}}}
321 allData.update(parsedJSON)
322
323 elif ticker_name in yahoo_list:
324 resposeDataYahoo = getDataFromYahoo(symbols[ticker_name])
325 currentPrice = resposeDataYahoo['price']
326 day_change = resposeDataYahoo['change_pct']
327
328 #For Year Change
329 year_change = ""
330 priceLastYear = findLocalHistoryData(ticker_name,date('lastyear'))
331 try:
332 year_change = str((float(currentPrice)-float(priceLastYear))/(float(priceLastYear))*100)
333 except:
334 year_change = ''
335
336 #For YTD Change
337 ytd_change = ""
338 priceOnYearStart = findLocalHistoryData(ticker_name,date('yearstart'))
339 try:
340 ytd_change = str(((float(currentPrice)-float(priceOnYearStart))/float(priceOnYearStart))*100)
341 except:
342 ytd_change = ''
343
344 parsedJSON = {symbols[ticker_name]:{date('today'):{'price':currentPrice,'day_change':day_change, 'year_change':year_change, 'ytd_change':ytd_change}}}
345 allData.update(parsedJSON)
346
347 elif ticker_name in bloomberg_list:
348 resposeDataBloomberg = getBloombergData(ticker_name)
349 currentPrice = resposeDataBloomberg[symbols[ticker_name]][date('today')]['price']
350 day_change = resposeDataBloomberg[symbols[ticker_name]][date('today')]['day_change']
351 year_change = resposeDataBloomberg[symbols[ticker_name]][date('today')]['year_change']
352 ytd_change = resposeDataBloomberg[symbols[ticker_name]][date('today')]['ytd_change']
353 allData.update(resposeDataBloomberg)
354
355
356 writeLocalHistory(allData)
357 exportToExcel(allData,date('today'),date('today'))
358 exportToHTML('{}.xlsx'.format(date('today')), '{}.html'.format(date('today')))
359 return allData
360
361getAllValues()