import time from full_fred.fred import Fred import pymysql import requests from datetime import datetime from bs4 import BeautifulSoup from w3lib.html import remove_tags while True: fred=Fred('example_key.txt') fred.set_api_key_file('example_key.txt') DFEDTARU = fred.get_series_df('DFEDTARU') DFEDTARL = fred.get_series_df('DFEDTARL') FEDFUNDS = fred.get_series_df('FEDFUNDS') IORB = fred.get_series_df('IORB') RRPONTSYAWARD = fred.get_series_df('RRPONTSYAWARD') SOFR = fred.get_series_df('SOFR') list_date1 = DFEDTARU['date'] list_value1 = DFEDTARU['value'] list_date2 = DFEDTARL['date'] list_value2 = DFEDTARL['value'] list_date3 = FEDFUNDS['date'] list_value3 = FEDFUNDS['value'] list_date4 = IORB['date'] list_value4 = IORB['value'] list_date5 = RRPONTSYAWARD['date'] list_value5 = RRPONTSYAWARD['value'] list_date6 = SOFR['date'] list_value6 = SOFR['value'] date1 = [] value1 = [] date2 = [] value2 = [] date3 = [] value3 = [] date4 = [] value4 = [] date5 = [] value5 = [] date6 = [] value6 = [] for i in list_date1: date1 += [i] for i in list_value1: value1 += [i] for i in list_date2: date2 += [i] for i in list_value2: value2 += [i] for i in list_date3: date3 += [i] for i in list_value3: value3 += [i] for i in list_date4: date4 += [i] for i in list_value4: value4 += [i] for i in list_date5: date5 += [i] for i in list_value5: value5 += [i] for i in list_date6: date6 += [i] for i in list_value6: value6 += [i] date1 = date1[-1] value1 = value1[-1] date2 = date2[-1] value2 = value2[-1] date3 = date3[-1] value3 = value3[-1] date4 = date4[-1] value4 = value4[-1] date5 = date5[-1] value5 = value5[-1] date6 = date6[-1] value6 = value6[-1] date1 = date1.replace('-', '/') date_string = date1 format = '%Y/%m/%d' date1 = datetime.strptime(date_string, format) date2 = date2.replace('-', '/') date_string = date2 format = '%Y/%m/%d' date2 = datetime.strptime(date_string, format) date3 = date3.replace('-', '/') date_string = date3 format = '%Y/%m/%d' date3 = datetime.strptime(date_string, format) date4 = date4.replace('-', '/') date_string = date4 format = '%Y/%m/%d' date4 = datetime.strptime(date_string, format) date5 = date5.replace('-', '/') date_string = date5 format = '%Y/%m/%d' date5 = datetime.strptime(date_string, format) date6 = date6.replace('-', '/') date_string = date6 format = '%Y/%m/%d' date6 = datetime.strptime(date_string, format) db = pymysql.connect(host="127.0.0.1",user="root",password="2GS@bPYcgiMyL14A",database="Macroeconomics",port=4423) cursor = db.cursor() sql = "select date from InterestRate where name='DFEDTARU'" cursor.execute(sql) db.commit() DFEDTARU_old_time = cursor.fetchall() DFEDTARU_old_time=DFEDTARU_old_time[-1][0] if DFEDTARU_old_time != date1 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date1, 'DFEDTARU', value1) cursor.execute(sql) db.commit() sql2 = "select date from InterestRate where name='DFEDTARL'" cursor.execute(sql2) db.commit() DFEDTARL_old_time = cursor.fetchall() DFEDTARL_old_time=DFEDTARL_old_time[-1][0] if DFEDTARL_old_time != date2 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date2, 'DFEDTARL', value2) cursor.execute(sql) db.commit() sql3 = "select date from InterestRate where name='FEDFUNDS'" cursor.execute(sql3) db.commit() FEDFUNDS_old_time = cursor.fetchall() FEDFUNDS_old_time=FEDFUNDS_old_time[-1][0] if FEDFUNDS_old_time != date3 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date3, 'FEDFUNDS', value3) cursor.execute(sql) db.commit() sql4 = "select date from InterestRate where name='IORB'" cursor.execute(sql4) db.commit() IORB_old_time = cursor.fetchall() IORB_old_time=IORB_old_time[-1][0] if IORB_old_time != date4 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date4, 'IORB', value4) cursor.execute(sql) db.commit() sql5 = "select date from InterestRate where name='RRPONTSYAWARD'" cursor.execute(sql5) db.commit() RRPONTSYAWARD_old_time = cursor.fetchall() RRPONTSYAWARD_old_time=RRPONTSYAWARD_old_time[-1][0] if RRPONTSYAWARD_old_time != date5 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date5, 'RRPONTSYAWARD', value5) cursor.execute(sql) db.commit() sql6 = "select date from InterestRate where name='SOFR'" cursor.execute(sql6) db.commit() SOFR_old_time = cursor.fetchall() SOFR_old_time=SOFR_old_time[-1][0] if SOFR_old_time != date6 : sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (date6, 'SOFR', value6) cursor.execute(sql) db.commit() pagee = requests.get("https://www.frbsf.org/wp-content/uploads/sites/4/proxy-funds-rate-chart1-data.csv") pagee = pagee.text pagee = pagee.split() number = 0 for i in pagee: number += 1 if number <= 5: continue else: pagee = i.split()[-1] pagee = pagee.replace(',', ' , ') PFR_new_time = pagee[0:10] PFR_new_time = PFR_new_time.replace('-', '/') PFR_value = pagee[-8:] PFR_value = PFR_value.replace(' ', '') date_string = PFR_new_time format = '%Y/%m/%d' PFR_new_time = datetime.strptime(date_string, format) sql = "select * from InterestRate where name='PFR' and date='%s'" % (PFR_new_time) cursor.execute(sql) outcome = cursor.fetchall() if outcome == () or outcome == 0 or outcome == None: sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (PFR_new_time, 'PFR', PFR_value) cursor.execute(sql) db.commit() else: sql = "update InterestRate set _value='%s' where 'name'='PFR' and 'date' = '%s'" % (PFR_value, PFR_new_time) cursor.execute(sql) db.commit() number=0 pagee = requests.get("https://markets.newyorkfed.org/api/rp/repo/multiple/results/last/1.json") pagee = pagee.json() page=pagee['repo']['operations'][0] page2=page['details'][0] if page2.__contains__('minimumBidRate'): RR_value=page2['minimumBidRate'] RR_new_time = page['operationDate'] RR_new_time = RR_new_time.replace('-', '/') date_string = RR_new_time format = '%Y/%m/%d' RR_new_time = datetime.strptime(date_string, format) sql = "select date from InterestRate where name='RR'" cursor.execute(sql) db.commit() RR_old_time = cursor.fetchall() RR_old_time = RR_old_time[-1][0] if RR_old_time != RR_new_time: sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (RR_new_time, 'RR', RR_value) cursor.execute(sql) db.commit() page = requests.get("https://www.global-rates.com/en/interest-rates/libor/american-dollar/american-dollar.aspx") page = page.text page = BeautifulSoup(page, 'html.parser') data = page.find_all('div', class_="table-normal text-end") LIBOR_new_time = data[0] LIBOR1M_value = data[5] LIBOR3M_value = data[10] LIBOR6M_value = data[15] LIBOR_new_time = remove_tags(str(LIBOR_new_time)) LIBOR1M_value = remove_tags(str(LIBOR1M_value)) LIBOR3M_value = remove_tags(str(LIBOR3M_value)) LIBOR6M_value = remove_tags(str(LIBOR6M_value)) LIBOR_new_time = LIBOR_new_time[6:10]+'-'+LIBOR_new_time[0:5] LIBOR_new_time = LIBOR_new_time.replace("-", "/") LIBOR1M_value = LIBOR1M_value.replace(' ', '') LIBOR3M_value = LIBOR3M_value.replace(' ', '') LIBOR6M_value = LIBOR6M_value.replace(' ', '') format = '%Y/%m/%d' LIBOR_new_time = datetime.strptime(LIBOR_new_time, format) sql = "select date from InterestRate where name='LIBOR1M'" cursor.execute(sql) db.commit() LIBOR_old_time = cursor.fetchall() LIBOR_old_time = LIBOR_old_time[-1][0] if LIBOR_new_time != LIBOR_old_time: sql = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (LIBOR_new_time, 'LIBOR1M', LIBOR1M_value) sql1 = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (LIBOR_new_time, 'LIBOR3M', LIBOR3M_value) sql2 = "insert into InterestRate(date,name,_value)values('%s','%s','%s')" % (LIBOR_new_time, 'LIBOR6M', LIBOR6M_value) cursor.execute(sql) cursor.execute(sql1) cursor.execute(sql2) db.commit() db.close() time.sleep(7200)