253 lines
9.0 KiB
Python
253 lines
9.0 KiB
Python
|
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)
|
||
|
|
|
||
|
|
|