Colaboratoryを使用して、投資環境スコアをGoogleスプレッドシートに記入する

import sys
import time

import pandas as pd
import datetime as dt
import pandas_datareader.data as web
import matplotlib.pyplot as plt
import re

# 認証のためのコード
from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)


open = dt.date(1900,1,1)
stop = dt.date.today()

try:

  # 実行時間を測るStart
  start = time.time()
  print ("開始時間:{0}".format(time.strftime('%Y/%m/%d %H:%M:%S')) )

  #アルファベットを数値に変換
  def toAlpha(num):
    if num<=26:
        return chr(64+num)
    elif num%26==0:
        return toAlpha(num//26-1)+chr(90)
    else:
        return toAlpha(num//26)+chr(64+num%26)

  #月末データを取得する
  def endmonth(df):
      dt = df.index
      dtm = pd.date_range(dt.min(), dt.max(), freq='BM') # 範囲内の月末最終営業日のみ
      dtm = dtm.astype(str) # 文字列に
      return df[df.index.isin(dtm)]

  def WriteSheets(df,start_cell,filename,sheetname,Clearflg):
    ## スプレッドシートを開く（名前から）
    ss = gc.open(filename)
    # シートを特定する（シート名で特定）
    st = ss.worksheet(sheetname)

    col_lastnum = len(df.columns) # DataFrameの列数
    row_lastnum = len(df.index)   # DataFrameの行数

    start_cell_col = re.sub(r'[\d]', '', start_cell)
    start_cell_row = int(re.sub(r'[\D]', '', start_cell))

    # アルファベットから数字を返すラムダ式(A列～Z列まで)
    # 例：A→1、Z→26
    alpha2num = lambda c: ord(c) - ord('A') + 1

    # 展開を開始するセルからA1セルの差分
    row_diff = start_cell_row-1
    col_diff = alpha2num(start_cell_col)-alpha2num('A')

    cell_list = st.range(start_cell+':'+toAlpha(col_lastnum+1+col_diff)+str(row_lastnum+1+row_diff))

    for cell in cell_list:
        if cell.col == 1+col_diff and cell.row == 1+row_diff:
            val = ''
        elif cell.col == 1+col_diff:
            val = df.index[cell.row-(2+row_diff)]
        elif cell.row == 1+row_diff:
            val = df.columns[cell.col-(2+col_diff)]
        else:
            val = df.iloc[cell.row-(2+row_diff)][cell.col-(2+col_diff)]

        cell.value = str(val)

    if Clearflg == 1:
        st.clear()

    st.update_cells(cell_list)

  #米国金利データ
  df = web.DataReader(["DGS3MO","DGS6MO","DGS1","DGS2","DGS5","DGS10","DGS20","DGS30"], 'fred', open, stop)
  #欠損値を前のデータで保管
  df = df.fillna(method = 'ffill')
  #スプレッドに記述
  WriteSheets(df,'A1','金利 取得用','米国金利',1)
  #月末のみ
  dtm = endmonth(df)
  WriteSheets(dtm,'K1','金利 取得用','米国金利',0)

  #FFレート
  df = web.DataReader(["FEDFUNDS","DFEDTARU", "DFEDTARL"], 'fred', open, stop)
  #欠損値を前のデータで保管
  df = df.fillna(method = 'ffill')
  #スプレッドに記述
  WriteSheets(df,'A1','金利 取得用','FFレート',1)
  #月末のみ
  dtm = endmonth(df)
  WriteSheets(dtm,'F1','金利 取得用','FFレート',0)

  #社債
  df = web.DataReader(["DBAA","BAA10Y"], 'fred', open, stop)
  #欠損値を前のデータで保管
  df = df.fillna(method = 'ffill')
  #スプレッドに記述
  WriteSheets(df,'A1','金利 取得用','社債',1)
  # 月末のみ
  dtm = endmonth(df)
  WriteSheets(dtm,'E1','金利 取得用','社債',0)

  #米ドル指数
  df = web.DataReader(["TWEXMMTH","TWEXBGSMTH","RTWEXAFEGS","RTWEXBGS"], 'fred', open, stop)
  #欠損値を前のデータで保管
  df = df.fillna(method = 'ffill')
  #スプレッドに記述
  WriteSheets(df,'A1','金利 取得用','米ドル指数',1)


except Exception:

  e = sys.exc_info()[1]
  print("エラー",e.args)

finally:

  # 実行時間を測るEnd
  print ("終了時間:{0}".format(time.strftime('%Y/%m/%d %H:%M:%S')) )
  elapsed_time = time.time() - start
  print ("処理時間:{0}".format(elapsed_time) + "[sec]")