有财务指标数据库的完整脚本吗?
时间: 2024-03-26 11:41:16 浏览: 78
财务类数据库,库文件及脚本
当然可以,以下是一个简单的财务指标数据库示范脚本,用于存储和查询公司的财务指标数据:
```python
import sqlite3
import pandas as pd
import yfinance as yf
# 创建财务指标数据库和表格
conn = sqlite3.connect('financials.db')
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS financials
(id INTEGER PRIMARY KEY,
symbol TEXT,
date TEXT,
revenue REAL,
net_income REAL,
eps REAL,
roe REAL,
roa REAL)''')
# 更新财务指标数据
def update_financials(symbol, start_date, end_date):
# 从 Yahoo Finance API 获取财务数据
ticker = yf.Ticker(symbol)
financials = ticker.financials.loc[start_date:end_date]
financials.reset_index(inplace=True)
financials['symbol'] = symbol
# 插入或更新财务数据到数据库
for _, row in financials.iterrows():
c.execute("SELECT id FROM financials WHERE symbol=? AND date=?", (symbol, row['index']))
result = c.fetchone()
if result:
# 更新已有条目
c.execute("UPDATE financials SET revenue=?, net_income=?, eps=?, roe=?, roa=? WHERE id=?",
(row['Total Revenue'], row['Net Income'], row['Earnings Per Share'], row['Return on Equity'], row['Return on Assets'], result[0]))
else:
# 插入新条目
c.execute("INSERT INTO financials (symbol, date, revenue, net_income, eps, roe, roa) VALUES (?, ?, ?, ?, ?, ?, ?)",
(symbol, row['index'], row['Total Revenue'], row['Net Income'], row['Earnings Per Share'], row['Return on Equity'], row['Return on Assets']))
conn.commit()
print("Updated financials data for {}".format(symbol))
# 查询财务指标数据
def query_financials(symbol, start_date=None, end_date=None):
if start_date and end_date:
c.execute("SELECT * FROM financials WHERE symbol=? AND date BETWEEN ? AND ?", (symbol, start_date, end_date))
elif start_date:
c.execute("SELECT * FROM financials WHERE symbol=? AND date>=?", (symbol, start_date))
elif end_date:
c.execute("SELECT * FROM financials WHERE symbol=? AND date<=?", (symbol, end_date))
else:
c.execute("SELECT * FROM financials WHERE symbol=?", (symbol,))
result = c.fetchall()
if result:
df = pd.DataFrame(result, columns=['id', 'symbol', 'date', 'revenue', 'net_income', 'eps', 'roe', 'roa'])
df.set_index('date', inplace=True)
print(df)
else:
print("No data found for symbol {}".format(symbol))
# 测试更新和查询
update_financials("AAPL", "2010-01-01", "2021-12-31")
query_financials("AAPL", "2020-01-01", "2021-12-31")
```
这个脚本使用 SQLite 数据库来存储财务指标数据,并从 Yahoo Finance API 中获取数据。你可以根据需要修改表格结构和查询逻辑。
阅读全文