lizixuan
/
coinbus-data
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
coinbus-data/coinbus/Macroeconomic_FARBODI.py

294 lines
21 KiB
Python
Raw Permalink Normal View History

代码优化
2025-09-12 08:51:00 +00:00
import time
import pymysql
import requests
from bs4 import BeautifulSoup
from w3lib.html import remove_tags
import datetime
while True:
try:
# now_time = datetime.datetime.now()
# next_time = now_time + datetime.timedelta(days=+1)
# next_year = next_time.date().year
# next_month = next_time.date().month
# next_day = next_time.date().day
# next_time = datetime.datetime.strptime(str(next_year) + "-" + str(next_month) + "-" + str(next_day) + " 20:45:01","%Y-%m-%d %H:%M:%S")
# timer_start_time = (next_time - now_time).total_seconds()
db = pymysql.connect(host="127.0.0.1",user="root",password="2GS@bPYcgiMyL14A",database="Macroeconomics",port=4423)
cursor = db.cursor()
page = requests.get("https://www.federalreserve.gov/releases/h41/current/default.htm")
page=page.text
page = BeautifulSoup(page, 'html.parser')
date = page.find_all('div', class_="dates")
# 获取数据
date = remove_tags(str(date))
# 删除多余字符
date = date.replace("[", "")
date = date.replace("]", "")
date = date.replace("Release Date:", "")
date = date.replace(",","")
date = date.replace(" ","")
date = date.strip()
date = date.lstrip()
date1 = date[-4:]
date2 = date[-6:-4]
# 转换时间
date = date.replace("January", "/01/")
date = date.replace("February", "/02/")
date = date.replace("March", "/03/")
date = date.replace("April", "/04/")
date = date.replace("May", "/05/")
date = date.replace("June", "/06/")
date = date.replace("July", "/07/")
date = date.replace("August", "/08/")
date = date.replace("September", "/09/")
date = date.replace("October", "/10/")
date = date.replace("November", "/11/")
date = date.replace("December", "/12/")
date = date1+date[0:4]+date2
date_string = date
format = '%Y/%m/%d'
from datetime import datetime
date = datetime.strptime(date_string, format)
sql = "select time from CHAFRNFRAA order by id desc limit 1"
cursor.execute(sql)
db.commit()
old_time = cursor.fetchall()
ole_time=old_time[0][0]
except:
time.sleep(30)
continue
# 判断时间
if date != ole_time:
page = page.find_all('span',style="font-family:'Courier New'; font-weight:bold")
page = remove_tags(str(page))
page = page.replace(",", "")
page = page.replace("[", "")
page = page.replace("]", "")
page=page.split()
symbol= ''
list=[]
# 数据拼接
for i in page:
if i =='-':
symbol= '-'
continue
if i =='+':
continue
if i =='(0)':
continue
if i =='...':
i='NULL'
value= symbol + i
symbol= ''
list+=[value]
sql = "insert into FARBODI(name,time,THIS_AVG_VALUE,CHANGE_LASTWEEK,CHANGE_LASTYEAR,THIS_VALUE)values(%s,%s,%s,%s,%s,%s)"
data_list=[('Reserve Bank credit',date,list[0],list[1],list[2],list[3]),
('Securities held outright', date, list[4], list[5], list[6], list[7]),
('U.S. Treasury securities', date, list[8], list[9], list[10], list[11]),
('Bills', date, list[12], list[13], list[14], list[15]),
('Notes and bonds, nominal', date, list[16], list[17], list[18], list[19]),
('Notes and bonds, inflation-indexed', date, list[20], list[21], list[22], list[23]),
('Inflation compensation', date, list[24], list[25], list[26], list[27]),
('Federal agency debt securities', date, list[28], list[29], list[30], list[31]),
('Mortgage-backed securities', date, list[32], list[33], list[34], list[35]),
('Uposho', date, list[36], list[37], list[38], list[39]),
('Udosho', date, list[40], list[41], list[42], list[43]),
('Repurchase agreements', date, list[44], list[45], list[46], list[47]),
('Foreign official', date, list[48], list[49], list[50], list[51]),
('Others', date, list[52], list[53], list[54], list[55]),
('Loans', date, list[56], list[57], list[58], list[59]),
('Primary credit', date, list[60], list[61], list[62], list[63]),
('Secondary credit', date, list[64], list[65], list[66], list[67]),
('Seasonal credit', date, list[68], list[69], list[70], list[71]),
('PPPLF', date, list[72], list[73], list[74], list[75]),
('Bank Term Funding Program', date, list[76], list[77], list[78], list[79]),
('Other credit extensions', date, list[80], list[81], list[82], list[83]),
('NphoMFLLC(MSLP)', date, list[84], list[85], list[86], list[87]),
('Net portfolio holdings of MLF LLC', date, list[88], list[89], list[90], list[91]),
('Net portfolio holdings of TALF II LLC',date, list[92], list[93], list[94], list[95]),
('Float',date, list[96], list[97], list[98], list[99]),
('Central bank liquidity swaps',date, list[100], list[101], list[102], list[103]),
('Other Federal Reserve assets',date, list[104], list[105], list[106], list[107]),
('Foreign currency denominated assets',date, list[108], list[109], list[110], list[111]),
('Gold stock',date, list[112], list[113], list[114], list[115]),
('Special drawing rights certificate account',date, list[116], list[117], list[118], list[119]),
('Treasury currency outstanding',date, list[120], list[121], list[122], list[123]),
('Total factors supplying reserve funds',date, list[124], list[125], list[126], list[127])]
cursor.executemany(sql,data_list)
sql2 = "insert into FARBODIC(name,time,THIS_AVG_VALUE,CHANGE_LASTWEEK,CHANGE_LASTYEAR,THIS_VALUE)values(%s,%s,%s,%s,%s,%s)"
data_list2=[('Currency in circulation',date, list[128], list[129], list[130], list[131]),
('Reverse repurchase agreements',date, list[132], list[133], list[134], list[135]),
('Foreign official and international accounts',date, list[136], list[137], list[138], list[139]),
('Others',date, list[140], list[141], list[142], list[143]),
('Treasury cash holdings',date, list[144], list[145], list[146], list[147]),
('DwFRBotrb',date, list[148], list[149], list[150], list[151]),
('Tdhbdi',date, list[152], list[153], list[154], list[155]),
('U.S. Treasury, General Account',date, list[156], list[157], list[158], list[159]),
('Foreign official',date, list[160], list[161], list[162], list[163]),
('Other',date, list[164], list[165], list[166], list[167]),
('Treasury contributions to credit facilities',date, list[168], list[169], list[170], list[171]),
('Other liabilities and capital',date, list[172], list[173], list[174], list[175]),
('Tfotrbarf',date, list[176], list[177], list[178], list[179]),
('RbwFRB',date, list[180], list[181], list[182], list[183])]
cursor.executemany(sql2,data_list2)
sql3 = "insert into MI(name,time,THIS_AVG_VALUE,CHANGE_LASTWEEK,CHANGE_LASTYEAR,THIS_VALUE)values(%s,%s,%s,%s,%s,%s)"
data_list3=[('Shicffoaia',date, list[184], list[185], list[186], list[187]),
('Marketable U.S. Treasury securities',date, list[188], list[189], list[190], list[191]),
('Fadambs',date, list[192], list[193], list[194], list[195]),
('Other securities',date, list[196], list[197], list[198], list[199]),
('Securities lent to dealers',date, list[200], list[201], list[202], list[203]),
('Overnight facility',date, list[204], list[205], list[206], list[207]),
('U.S. Treasury securities',date, list[208], list[209], list[210], list[211]),
('Federal agency debt securities',date,list[212], list[213], list[214], list[215])]
cursor.executemany(sql3,data_list3)
sql4 = "insert into MDOSLASOAAL(name,time,D15,D16_D90,D91_Y1,Y1_Y5,Y5_Y10,Y10_,TOTAL)values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
data_list4=[('Loans', date, list[216], list[217], list[218], list[219], list[220], list[221], list[222]),
('USTsH', date, list[223], list[224], list[225], list[226], list[227], list[228], list[229]),
('USTsWc', date, list[230], list[231], list[232], list[233], list[234], list[235], list[236]),
('FadsH', date, list[237], list[238], list[239], list[240], list[241], list[242], list[243]),
('FadsWc', date, list[244], list[245], list[246], list[247], list[248], list[249], list[250]),
('MbsH', date, list[251], list[252], list[253], list[254], list[255], list[256], list[257]),
('MbsWc', date, list[258], list[259], list[260], list[261], list[262], list[263], list[264]),
('LphbMFLLC(MSLP)', date, list[265], list[266], list[267], list[268], list[269], list[270], list[271]),
('Repurchase agreements', date, list[272], list[273], list[274], list[275], list[276], list[277], list[278]),
('Central bank liquidity swaps', date, list[279], list[280], list[281], list[282], list[283], list[284], list[285]),
('Reverse repurchase agreements', date, list[286], list[287], list[288], list[289], list[290], list[291], list[292]),
('Term deposits', date, list[293], list[294], list[295], list[296], list[297], list[298], list[299])]
cursor.executemany(sql4,data_list4)
sql5 = "insert into SIOMS(name,time,value)values(%s,%s,%s)"
data_list5 = [('Mortgage-backed securities held outright', date, list[300]),
('Residential mortgage-backed securities', date, list[301]),
('Commercial mortgage-backed securities', date, list[302]),
('Commitments to buy mortgage-backed securities', date, list[303]),
('Commitments to sell mortgage-backed securities', date, list[304]),
('Cash and cash equivalents', date, list[305])]
cursor.executemany(sql5, data_list5)
sql6 = "insert into IOPAOCFL(name,time,OPAPTLLC,UPFAPT,FAAOA,TOTAL)values(%s,%s,%s,%s,%s,%s)"
data_list6 =[('MS Facilities LLC (Main Street Lending Program)',date, list[306], list[307], list[308], list[309])]
cursor.executemany(sql6, data_list6)
sql7 = "insert into CSOCOAFRB(name,time,EFC,THIS_VALUE,CHANGE_LASTWEEK,CHANGE_LASTYEAR)values(%s,%s,%s,%s,%s,%s)"
data_list7 = [('Gold certificate account', date, 'NULL', list[310], list[311], list[312]),
('Special drawing rights certificate account', date, 'NULL', list[313], list[314], list[315]),
('Coin', date, 'NULL', list[316], list[317], list[318]),
('Supadraal', date, 'NULL', list[319], list[320], list[321]),
('Securities held outright', date, 'NULL', list[322], list[323], list[324]),
('U.S. Treasury securities', date, 'NULL', list[325], list[326], list[327]),
('Bills', date, 'NULL', list[328], list[329], list[330]),
('Notes and bonds, nominal', date, 'NULL', list[331], list[332], list[333]),
('Notes and bonds, inflation-indexed', date, 'NULL', list[334], list[335], list[336]),
('Inflation compensation', date, 'NULL', list[337], list[338], list[339]),
('Federal agency debt securities', date, 'NULL', list[340], list[341], list[342]),
('Mortgage-backed securities', date, 'NULL', list[343], list[344], list[345]),
('Uposho', date, 'NULL', list[346], list[347], list[348]),
('Udosho', date, 'NULL', list[349], list[350], list[351]),
('Repurchase agreements', date, 'NULL', list[352], list[353], list[354]),
('Loans', date, 'NULL', list[355], list[356], list[357]),
('NphoMFLLC(MSLP)', date, 'NULL', list[358], list[359], list[360]),
('NphoMLFLLC', date, 'NULL', list[361], list[362], list[363]),
('Net portfolio holdings of TALF II LLC', date, 'NULL', list[364], list[365], list[366]),
('Items in process of collection', date, 'NULL', list[367], list[368], list[369]),
('Bank premises', date, 'NULL', list[370], list[371], list[372]),
('Central bank liquidity swaps', date, 'NULL', list[373], list[374], list[375]),
('Foreign currency denominated assets', date, 'NULL', list[376], list[377], list[378]),
('Other assets', date, 'NULL', list[379], list[380], list[381]),
('Total assets', date, 'NULL', list[382], list[383], list[384])]
cursor.executemany(sql7, data_list7)
sql8 = "insert into CSOCOAFRBC(name,time,EFC,THIS_VALUE,CHANGE_LASTWEEK,CHANGE_LASTYEAR)values(%s,%s,%s,%s,%s,%s)"
data_list8 = [('FRnnoFBh', date, 'NULL', list[385], list[386], list[387]),
('Reverse repurchase agreements', date, 'NULL', list[388], list[389], list[390]),
('Deposits', date, 'NULL', list[391], list[392], list[393]),
('Term deposits held by depository institutions', date, 'NULL', list[394], list[395], list[396]),
('Other deposits held by depository institutions', date, 'NULL', list[397], list[398], list[399]),
('U.S. Treasury, General Account', date, 'NULL', list[400], list[401], list[402]),
('Foreign official', date, 'NULL', list[403], list[404], list[405]),
('Other', date, 'NULL', list[406], list[407], list[408]),
('Deferred availability cash items', date, 'NULL', list[409], list[410], list[411]),
('Treasury contributions to credit facilities', date, 'NULL', list[412], list[413], list[414]),
('Other liabilities and accrued dividends', date, 'NULL', list[415], list[416], list[417]),
('Total liabilities', date, 'NULL', list[418], list[419], list[420]),
('Capital paid in', date, 'NULL', list[421], list[422], list[423]),
('Surplus', date, 'NULL', list[424], list[425], list[426]),
('Other capital accounts', date, 'NULL', list[427], list[428], list[429]),
('Total capital', date, 'NULL', list[430], list[431], list[432])]
cursor.executemany(sql8, data_list8)
sql9 = "insert into SOCOEFRB(name,time,TOTAL,Boston,NewYork,Philadelphia,Cleveland,Richmond,Atlanta,Chicago,St_Louis,Minneapolis,Kansas_City,Dallas,San_Francisco)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
data_list9=[('Gcasdrc', date, list[433], list[434], list[435], list[436], list[437], list[438], list[439], list[440], list[441], list[442], list[443], list[444], list[445]),
('Coin', date, list[446], list[447], list[448], list[449], list[450], list[451], list[452], list[453], list[454], list[455], list[456], list[457], list[458]),
('Supadraal', date, list[459], list[460], list[461], list[462], list[463], list[464], list[465], list[466], list[467], list[468], list[469], list[470], list[471]),
('NphoMFLLC(MSLP)', date, list[472], list[473], list[474], list[475], list[476], list[477], list[478], list[479], list[480], list[481], list[482], list[483], list[484]),
('Central bank liquidity swaps', date, list[485], list[486], list[487], list[488], list[489], list[490], list[491], list[492], list[493], list[494], list[495], list[496], list[497]),
('Foreign currency denominated assets', date, list[498], list[499], list[500], list[501], list[502], list[503], list[504], list[505], list[506], list[507], list[508], list[509], list[510]),
('Other assets', date, list[511], list[512], list[513], list[514], list[515], list[516], list[517], list[518], list[519], list[520], list[521], list[522], list[523]),
('Interdistrict settlement account', date, list[524], list[525], list[526], list[527], list[528], list[529], list[530], list[531], list[532], list[533], list[534], list[535], list[536]),
('Total assets', date, list[537], list[538], list[539], list[540], list[541], list[542], list[543], list[544], list[545], list[546], list[547], list[548], list[549])]
cursor.executemany(sql9, data_list9)
sql10 = "insert into SOCOEFRBC(name,time,TOTAL,Boston,NewYork,Philadelphia,Cleveland,Richmond,Atlanta,Chicago,St_Louis,Minneapolis,Kansas_City,Dallas,San_Francisco)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
data_list10 = [('Federal Reserve notes, net', date, list[550], list[551], list[552], list[553], list[554], list[555], list[556], list[557], list[558], list[559], list[560], list[561], list[562]),
('Reverse repurchase agreements', date, list[563], list[564], list[565], list[566], list[567], list[568], list[569], list[570], list[571], list[572], list[573], list[574], list[575]),
('Deposits', date, list[576], list[577], list[578], list[579], list[580], list[581], list[582], list[583], list[584], list[585], list[586], list[587], list[588]),
('Depository institutions', date, list[589], list[590], list[591], list[592], list[593], list[594], list[595], list[596], list[597], list[598], list[599], list[600], list[601]),
('U.S. Treasury, General Account', date, list[602], list[603], list[604], list[605], list[606], list[607], list[608], list[609], list[610], list[611], list[612], list[613], list[614]),
('Foreign official', date, list[615], list[616], list[617], list[618], list[619], list[620], list[621], list[622], list[623], list[624], list[625], list[626], list[627]),
('Other', date, list[628], list[629], list[630], list[631], list[632], list[633], list[634], list[635], list[636], list[637], list[638], list[639], list[640]),
('Earnings remittances due to the U.S. Treasury', date, list[641], list[642], list[643], list[644], list[645], list[646], list[647], list[648], list[649], list[650], list[651], list[652], list[653]),
('Treasury contributions to credit facilities', date, list[654], list[655], list[656], list[657], list[658], list[659], list[660], list[661], list[662], list[663], list[664], list[665], list[666]),
('Other liabilities and accrued dividends', date, list[667], list[668], list[669], list[670], list[671], list[672], list[673], list[674], list[675], list[676], list[677], list[678], list[679]),
('Total liabilities', date, list[680], list[681], list[682], list[683], list[684], list[685], list[686], list[687], list[688], list[689], list[690], list[691], list[692]),
('Capital paid in', date, list[693], list[694], list[695], list[696], list[697], list[698], list[699], list[700], list[701], list[702], list[703], list[704], list[705]),
('Surplus', date, list[706], list[707], list[708], list[709], list[710], list[711], list[712], list[713], list[714], list[715], list[716], list[717], list[718]),
('Other capital', date, list[719], list[720], list[721], list[722], list[723], list[724], list[725], list[726], list[727], list[728], list[729], list[730], list[731]),
('Total liabilities and capital', date, list[732], list[733], list[734], list[735], list[736], list[737], list[738], list[739], list[740], list[741], list[742], list[743], list[744])]
cursor.executemany(sql10, data_list10)
sql11 = "insert into CHAFRNFRAA(name,time,value)values(%s,%s,%s)"
data_list11 = [('Federal Reserve notes outstanding', date, list[745]),
('LNhbFBnstc', date, list[746]),
('Federal Reserve notes to be collateralized', date, list[747]),
('Collateral held against Federal Reserve notes', date, list[748]),
('Gold certificate account', date, list[749]),
('Special drawing rights certificate account', date, list[750]),
('UTadambsp', date, list[751]),
('Other assets pledged', date, list[752]),
('TUTadambs', date, list[753]),
('LFvosurra', date, list[754]),
('UTadambsetbp', date, list[755])]
cursor.executemany(sql11, data_list11)
db.commit()
else:
time.sleep(21600)
# time.sleep(timer_start_time)