【AIの活用】Pythonを使ってエクセルでグラフ作成

1. X軸左目盛りが棒グラフに対応し、右目盛りが折れ線グラフに対応していた。左右逆になっている。
2. 棒グラフ2項目に色の指定を日本語でしたが、それぞれ入れ替わっていた。
3. グラフの挿入が最初は上手く行かなかった。

import pandas as pd
from fredapi import Fred
import os

# FRED APIキー設定
fred = Fred(api_key=’API KEY入力‘)

# H.8レポート関連シリーズID
series_ids = {
“Loans and Leases”: “LOANS”, # 銀行貸出残高総額
“Loans_Commercial”: “BUSLOANS”, # 商業・産業向け貸出
“Loans_RealEstate”: “REALLN”, # 不動産貸出
“Loans_Consumer”: “CONSUMER”, # 消費者貸出
“Deposits_Total”: “DPSACBM027NBOG”,# 預金総額
“Borrowings_Total”: “H8B3094NCBAM” # 銀行借入総額
}

# データ取得
start_date = ‘2005-01-01’
data_frames = []

for name, sid in series_ids.items():
series = fred.get_series(sid, observation_start=start_date)
if name == “Borrowings_Total”:
series = series / 1000 # million → billion
df_temp = pd.DataFrame(series)
df_temp.columns = [name]
data_frames.append(df_temp)

# 結合
df_all = pd.concat(data_frames, axis=1)
df_all.index.name = ‘Date’

# 保存フォルダ
save_folder = “D:/FRED_Data”
os.makedirs(save_folder, exist_ok=True)

# 元データ保存
excel_file = os.path.join(save_folder, “US_Bank_Assets_Liabilities_2005_latest.xlsx”)
df_all.to_excel(excel_file)

# データ再読込
df = pd.read_excel(excel_file, index_col=0)
df.index = pd.to_datetime(df.index, errors=’coerce’).strftime(‘%Y-%m’)


# 数値整形
for col in df.columns:
df[col] = df[col].apply(lambda x: int(x) if pd.notnull(x) else None)

# 書式・グラフ付き保存
formatted_file = os.path.join(save_folder, “US_Bank_Assets_Liabilities_2005_latest_formatted.xlsx”)
with pd.ExcelWriter(formatted_file, engine=’xlsxwriter’) as writer:
df.to_excel(writer, sheet_name=’Data’)
workbook = writer.book
worksheet = writer.sheets[‘Data’]

# 日付を ‘YYYY-MM’ 形式に変換
df.index = pd.to_datetime(df.index, errors=’coerce’).strftime(‘%Y-%m’)

# 小数点以下切り捨て（整数化）
for col in df.columns:
df[col] = df[col].apply(lambda x: int(x) if pd.notnull(x) else None)

# セル幅調整
worksheet.set_column(‘A:A’, 15)
worksheet.set_column(‘B:G’, 20)

# フォント書式
cell_format = workbook.add_format({‘font_name’: ‘Meiryo’, ‘font_size’: 12, ‘num_format’: ‘#,##0’})
bold_format = workbook.add_format({‘bold’: True, ‘font_name’: ‘Meiryo’, ‘font_size’: 13})
worksheet.set_row(0, 22, bold_format)
for row in range(1, len(df)+1):
worksheet.set_row(row, 18, cell_format)


import pandas as pd
import matplotlib.pyplot as plt


# データ読込
df = pd.read_excel(“D:/FRED_Data/US_Bank_Assets_Liabilities_2005_latest.xlsx”, index_col=0)
df.index = pd.to_datetime(df.index, errors=’coerce’)
df = df.dropna()


# 日付を YYYY-MM に変換
df.index = pd.to_datetime(df.index, errors=’coerce’).strftime(‘%Y-%m’)

# 数値を小数点以下切り捨て（整数化）
for col in df.columns:
df[col] = df[col].apply(lambda x: int(x) if pd.notnull(x) else None)

# グラフ用に再度日付型に変換（X軸用）
df_plot = df.copy()
df_plot.index = pd.to_datetime(df_plot.index, format=’%Y-%m’)

# X軸ラベル（半年ごと）
xticks = pd.date_range(df_plot.index.min(), df_plot.index.max(), freq=’6MS’)
xtick_labels = [f”{d.year}-{d.month:02d}” for d in xticks]

# グラフ作成
fig, ax1 = plt.subplots(figsize=(14, 7))
ax1.set_title(“米国内全商業銀行の貸出残高と預金残高(単位:billion)”, fontname=”Meiryo”, fontsize=16)
ax1.set_xlabel(“date”, fontname=”Meiryo”, fontsize=13)
ax1.set_ylabel(“貸出残高（billion）”, fontname=”Meiryo”, fontsize=13)
ax1.set_ylim(0, 14000)
ax1.set_xticks(xticks)
ax1.set_xticklabels(xtick_labels, rotation=45, fontname=”Meiryo”, fontsize=10)

for col, color in zip(
[“Loans and Leases”, “Loans_Commercial”, “Loans_RealEstate”, “Loans_Consumer”],
[“#333333”, “#666666”, “#999999”, “#BBBBBB”]
):
ax1.plot(df_plot.index, df_plot[col], label=col, linewidth=0.8, color=color)

# 棒グラフ（右軸）
ax2 = ax1.twinx()
ax2.set_ylabel(“預金・借入残高（billion）”, fontname=”Meiryo”, fontsize=13)
ax2.set_ylim(0, 20000)
width = 20
ax2.bar(df_plot.index, df_plot[“Deposits_Total”], width=width, color=”#87CEFA”, alpha=0.7, label=”Deposits_Total”)
ax2.bar(df_plot.index, df_plot[“Borrowings_Total”], width=width, color=”#FF4500″, alpha=0.7, label=”Borrowings_Total”)

# 凡例
lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc=”upper left”, fontsize=11, frameon=True)

plt.tight_layout()

# グラフ画像保存
graph_img = “D:/FRED_Data/bank_graph.png”
plt.savefig(graph_img)
plt.close()

# Excelにデータ＋画像を一括保存
formatted_file = “D:/FRED_Data/US_Bank_Assets_Liabilities_2005_latest_formatted.xlsx”
with pd.ExcelWriter(formatted_file, engine=’xlsxwriter’) as writer:
df.to_excel(writer, sheet_name=’Data’)
workbook = writer.book
worksheet = writer.sheets[‘Data’]

# セル幅・フォント設定
worksheet.set_column(‘A:A’, 15)
worksheet.set_column(‘B:G’, 20)
cell_format = workbook.add_format({‘font_name’: ‘Meiryo’, ‘font_size’: 12, ‘num_format’: ‘#,##0’})
bold_format = workbook.add_format({‘bold’: True, ‘font_name’: ‘Meiryo’, ‘font_size’: 13})
worksheet.set_row(0, 22, bold_format)
for row in range(1, len(df)+1):
worksheet.set_row(row, 18, cell_format)

# グラフ画像挿入
worksheet.insert_image(‘J2’, graph_img)


print(f”✅ 書式・複合グラフ付きで保存しました：{formatted_file}”)