為替データを自動で取得する

当社では外国通貨の取引が多く、為替変動が収益に大きく左右します。

頻繁に為替動向をチェックしていますが、webより自動でデータを取得したいと思います。

サイトはYahoo Financeを利用します。
https://info.finance.yahoo.co.jp/history/?code=USDJPY

 

サイトの赤丸の欄に開始日と終了日を入力すると毎日の為替データが表示されます。

この日付指定をインプットボックスで行い、指定した期間の為替データを自動で取得してCSVファイルに保存するところまでやっていきます。

現在、CSVファイルが次のパスにあります。

ファイルには次のようにデータが自動で入ります。

 

 

それではさっそくはじめましょう。

さきほどのURLにアクセスして、開始日を2020年2月27日に指定し、終了日を2020年5月27日に指定すると、次のように4ページに分かれて毎日の為替データが表示されます。

これからやりたいことは、このそれぞれのページからデータを抽出してCSVファイルへ書き込みます。

指定した期間のデータが1ページで表示されていれば、ひとつのURLを分析すればよいのですが、いくつもページが分かれているためページごとにURLが異なっています。

よって、その4ページ分のURLをひとつのリストに入れて、for 文でそれぞれ抜き出しデータを抽出していきます。

手順

1.URLリストを作成する。
  それぞれのページをリスト(URL)に入れていきます。

2.データリストを作成する。
  リスト(URL)から1ページづつ抽出し、ページに表示されている為替データ
  をデータリスト(USD_price)に入れていきます。

3.CSVファイルへ書き出す。
  データが格納されたリスト(USD_price)からCSVファイル 
  (ForeignExchange_rate_USD.csv)へ書き出していきます。

 

手順1. URLリストを作成する

4ページそれぞれのURLは次のようになっています。

https://info.finance.yahoo.co.jp/history/?
code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=1 

https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=2

https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=3

https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=4

これをひとつのリストに入れていきます。

出来上がりは次のようになります。

['https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=1', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=2', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=3', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=4']

このurlを見てみると日付が指定されていることに気づきます。

https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d

この部分に自由に数字をインプットできるようにすればよく、次のようにインプットボックスで開始日と終了日を指定できるようにします。

 

コード1

# インプットボックスで日付を指定します。・・①
symd = input(“スタート日を入力してください(例:2020/1/1)>”)
eymd = input(“最終日を入力してください(例:2020/12/31)>”)

# 日付を分割して変数へ入れます。・・②
SYMD = symd.split(“/”)
sy, sm, sd = SYMD

EYMD = eymd.split(“/”)・・③
ey, em, ed = EYMD

# urlを作成します。・・④
url = “https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy={}&sm={}&sd={}&ey={}&em={}&ed={}&tm=d”.format(sy,sm,sd,ey,em,ed)

 

 

ここで、コードの意味を確認しておきます。

yahoo Financeのページで、2020年2月27日から2020年5月27日のデータを取得して見ると4 ページに分かれて表示されます。

それぞれのurlは次の通りです。

1ページ目
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=1

2ページ目
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=2

3ページ目
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=3

4ページ目
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=4

それぞれのページを見比べてみると最後の” &p=〇 ” の部分だけ違っていて〇にはページ数が入っています。

次に共通部分を確認してみます。
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d

日付の指定を2020年2月27日から2020年5月27日までしましたので、このurlから次のように推測されます。

sy=開始年
sm=開始月
sd=開始日

ey=終了年
em=終了月
ed=終了日

よって、それぞれの年、月、日にインプットボックスから指定した値が入るように設定します。

① 次のようにデータを取得する期間の開始日と終了日を、インプットボックス
  から取得して変数へ代入します。

  symd = input(“スタート日を入力してください(例:2020/1/1)>”)
  eymd = input(“最終日を入力してください(例:2020/12/31)>”)

次に、変数へ入った年月日を年、月、日に分割してそれぞれ変数へ代入します。

② 開始日を変数へ代入します。
  SYMD = symd.split(“/”)
  sy, sm, sd = SYMD

  symdの変数には「2020/2/27」が代入されています。
  split(“/”)で  [‘2020’, ‘2’, ’27’]  と年、月、日が区分されSYMD変数へ代入され
  ます。

  sy,sm,sd=SYMDにより
  syには’ 2020′ が入り、sm には’ 2 ‘ が入り、sd には’ 27 ‘が入ります。

③ 次に、終了日を変数へ代入します。
  EYMD = eymd.split(“/”)
  ey, em, ed = EYMD

  eymdの変数には「2020/5/27」が代入されています。
  split(“/”)で  [‘2020’, ‘5’, ’27’]  と年、月、日が区分されEYMD変数へ代入され
  ます。

  次に、ey,em,ed=EYMDにより
  eyには’ 2020′ が入り、em には’ 5 ‘ が入り、ed には’ 27 ‘が入ります。

④ 年、月、日が変数に入りましたので、これら変数をurlにフォーマットして
  urlを作成します。

url = “https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy={}&sm={}&sd={}&ey={}&em={}&ed={}&tm=d”.format(sy,sm,sd,ey,em,ed)

 

print(url)で次のように確認ができます。

https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d

これで日付を指定したURLが取得できました。

これをコードに指定して、requestsでダウンロードしBeautifulSoupで解析してそれぞれのページのURLを取得していきます。

それでは、次のコードを書いていきます。

コード2

r = requests.get(url) #①
soup = BeautifulSoup(r.content, “html.parser”) #②
ul_elems = soup.select(“ul.ymuiPagingBottom”) #③
a_elems = ul_elems[0].select(“a”) #④

URL = [] #⑤
URL.append(url) #⑥

for elem in a_elems: #⑦
    if elem.getText() != “次へ”: #⑧
        href = elem.get(“href”) #⑨
        URL.append(href) #⑩

print(URL)

['https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=2', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=3', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=4']

 

コードの意味を説明していきます。

①  webサーバーからHTMLファイルをダウンロードします。

② BeautifulSoup()のかっこ内に「HTMLファイルの内容」と「パーサー
  を指定し、その結果をsoup変数に代入しておきます。
      HTMLファイルの内容」は、requestsモジュールでダウンロードした結果か 
     ら.contentで取得できます。

③  ul要素(リスト要素)を取得して変数へ代入します。

 

ページの「1」のところで右クリックをして「検証」をクリックすると次のようにコードが表示されます。

 

これによると、ページ番号のリンクはul要素内の3つのa要素に相当することが分かります。そこで、まずul要素をclass属性の値(ymuiPagingBottom)で特定し、そこからa要素(リンク要素)を取得します。

select()メソッドによる要素の検索では、id属性とclass属性を検索条件に付加することができます。以下のように、「id属性の値」は「#」の後に、「class属性の値」は「.」の後に付け加えます。

<ul class=”ymuiPagingBottom clearFix”>
となっていますので、 
soup.select(“ul.ymuiPagingBottom“)
と指定します。

④ ul要素(リスト要素)からa要素(リンク要素)を取得して、変数a_elemsへ代入します。

⑤ すべてのページのURLを格納するリスト(URL)を作ります。

⑥ ul要素を見ると、1ページ目のURLがありませんので、1ページ目のURLをリストに入れておきます。
1ページ目のURLは、「コード1」で取得した次のURLになります。

url = “https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy={}&sm={}&sd={}&ey={}&em={}&ed={}&tm=d”.format(sy,sm,sd,ey,em,ed)

⑦ ④で取得したa要素が格納されているa_elemsからひとつづつa要素を抽出し
        て変数elemへ代入します。

⑧ 変数elemに代入されたa要素を読み取っていきます。
       要素の内容を読み取るには、取得した要素からgetText()を呼び出します。

⑨ a要素からhref属性の値を読み取り、変数hrefへ代入します。
  (href属性はリンク先の場所を示します。)
  このとき「次へ」のa要素は除外します。

⑩ 変数hrefへ代入されたURLをリスト(URL)へ格納します。

  次に⑦へ戻って次のa要素からhref属性を読み取りURLリストへ格納してい
       きます。

      すべてのa要素からhref属性を読み取りリストに格納されると、URLリストは
  次のようになります。

['https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=2', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=3', 'https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=2020&sm=2&sd=27&ey=2020&em=5&ed=27&tm=d&p=4']

これで、すべてのページのURLがひとつのリストにまとまりました。

次から、このそれぞれのURLに表示されている為替データを抽出してひとつのリストにまとめていきます。

手順2 データリストを作成する。

リスト(URL)から1ページづつ抽出し、ページに表示されている為替データをデータリスト(USD_price)に入れていきます。

まずはコードから見ていきます。

コード3

# データをリストに入れていく
USD_price = []  #  ①
for url in URL:  # ②

    # HTMLファイルのダウンロード
    r = requests.get(url)   # ③

    # Beautiful Soup
    soup = BeautifulSoup(r.content, “html.parser”) # ④

    # table要素の取得
    tables = soup.select(“table.boardFin”) # ⑤

    # tr要素の取得
    rows = tables[0].select(“tr”) # ⑥

    # 各行のデータを抽出
    data_list = []      # ⑦
    line_num = 0        # ⑧

    for row in rows:        # ⑨
        line_num = line_num + 1     # ⑩

        # 先頭行を飛ばす
        if line_num < 2:   # ⑪
            continue

        cells = row.select(“th,td”)  # ⑫
        values = []         # ⑬

        for cell in cells:     # ⑭
            values.append(cell.getText()) # ⑮

        # 対象データ
        datas = (values[0], values[1], values[2], values[3], values[4])  # ⑯
        data_list.append(datas)   # ⑰

    # 確認
    for data in data_list:    # ⑱
        USD_price.append(data) # ⑲

print(USD_price)


[('2020年5月27日', '107.520000', '107.940000', '107.350000', '107.710000'), ('2020年5月26日', '107.690000', '107.920000', '107.390000', '107.520000'), ('2020年5月25日', '107.570000', '107.770000', '107.540000', '107.690000'), ('2020年5月22日', '107.590000', '107.760000', '107.300000', '107.610000'), ('2020年5月21日', '107.480000', '107.840000', '107.460000', '107.610000'), ('2020年5月20日', '107.690000', '107.980000', '107.320000', '107.510000'), ('2020年5月19日', '107.310000', '108.080000', '107.280000', '107.680000'), ・・・・・・・・・・・・・]

では、ひとつづつ説明していきます。

① これから取得する為替データを格納するリストを作成します。

② 変数URLに入ったURLリストからひとつづつURLを抽出して変数urlへ代入
       します。

③ webサーバーからHTMLファイルをダウンロードして、変数rへ代入しま
  す。

④ 変数rへ代入されたurlをBeautifulSoupで読み込み、「HTMLファイルの内
        容
」と「パーサー」を指定し、その結果をsoup変数に代入します。

⑤ テーブル要素を取得して変数へ代入します。

    webサイトの表の中で右クリックして「検証」をクリックします。

コードの次の箇所にカーソルを合わせると表が選択されることから、次のコードが表の位置を示していることがわかります。

<table width=”100%” border=”0″ cellspacing=”0″ cellpadding=”0″ class=”boardFin yjSt marB6″><tbody><tr>

table要素を取得して変数(tables)へ代入します。

table要素はいくつもあるので、特定するためにclass属性の「boardFin」で取得します。

⑥ 取得したtable要素から、「tr」要素を取得します。
  tr要素は表の「行」の要素です。
  すなわち、指定したtable要素の中から行を取得することになります。
  このホームページにはclass属性が「boardFin」のtable要素は1つしかない
        ので、tables[0]になります。

⑦ これよりデータを取得していきます。
  まず、データを格納するリスト(data_list)を作成します。
 
⑧ この表の先頭行は項目ですので、データ取得は2行目からになります。
  その行数をカウントする変数をline_numとし、0を入れておきます。
 
⑨ 取得した全行(rows)から1行づつ抽出して変数(row)に代入します。
 
⑩ そのとき、行数を1カウントします。
 
⑪ 行数が2未満ならば処理せず飛ばします。(1行目の項目行を飛ばす)
 
⑫ 変数rowに代入された1行分から「th」要素と「td」要素を取得して変数
 (cells)へ代入します。
 
tr要素の中には、セルに相当するth要素とtd要素が配置されています。th要素には<th> ~ </th>で見出し(ヘッダー)を囲みます。td要素には<td> ~ </td>でデータを囲みます。
 
⑬ 変数cellsからデータを取得して蓄積していくためvaluesリストを作成しま
       す。
 
⑭ 全行分のtd,th要素を格納した変数cellsをループ処理して、1データずつ
  身を取り出します。
 
⑮ 要素の内容をgetText()メソッドで読み取り、valuesリストへ格納します。
 
⑯ valuesリストに格納されたデータのうち、1番目から5番目のデータを取得し
       て変数datasへ代入します。
 
⑰ 変数datasへ代入されたデータをリスト(data_list)へ格納します。
        data_listには1ページ分のデータが格納されます。
 
⑱.⑲ 1ページ分のデータがdata_listへ格納されるので、そのデータを
         USD_priceリストへ放り込みます。
 
1ページ分のデータがUSD_listへ格納されたら②へ戻り次のページの処理をします。そしてすべてのページのデータがUSD_priceリストへ格納されたら終了です。
 
USD_priceは次のようになります。
  
[(‘2020年5月27日’, ‘107.520000’, ‘107.940000’, ‘107.350000’, ‘107.710000’), (‘2020年5月26日’, ‘107.690000’, ‘107.920000’, ‘107.390000’, ‘107.520000’), (‘2020年5月25日’, ‘107.570000’, ‘107.770000’, ‘107.540000’, ‘107.690000’), (‘2020年5月22日’, ‘107.590000’, ‘107.760000’, ‘107.300000’, ‘107.610000’), (‘2020年5月21日’, ‘107.480000’, ‘107.840000’, ‘107.460000’, ‘107.610000’), (‘2020年5月20日’, ‘107.690000’, ‘107.980000’, ‘107.320000’, ‘107.510000’), (‘2020年5月19日’, ‘107.310000’, ‘108.080000’, ‘107.280000’, ‘107.680000’), ・・・・・・・・・・・・・]

手順3.CSVファイルへ書き出す

データが格納されたリスト(USD_price)からCSVファイル 
  (ForeignExchange_rate_USD.csv)へ書き出していきます。

 

コード4

# CSVファイルへ保存する
from pathlib import Path
import csv

# CSVファイルに保存
dld_dir = Path(“./RPA”)  # ①
file_path = dld_dir / “ForeignExchange_rate_USD.csv”  # ②

with file_path.open(mode=”w”, encoding=”cp932″, newline=””) as f: # ③
    writer = csv.writer(f)    # ④

    # ヘッダー
    writer.writerow([“日付”, “始値”, “高値”, “安値”, “終値”]) # ⑤

    # データ書き込み
    for data in USD_price: # ⑥
        writer.writerow(data) # ⑦

① CSVファイルが保存されているディレクトリ(RPA)をdld_dir変数へ代入し
  ます。

② そのディレクトリ変数を利用して、CSVファイルのパスをfile_path変数へ
  代入します。

③ CSVファイルを開きます。
  mode=”w”でファイルを開きます。
  open()のかっこ内にnewline=””を加えます。
  (これを忘れると1行おきに空行が挿入されます。)
  書き込みモード(mode=”w”)でファイルを開くと、ファイルが存在しない
  場合は、新しいファイルが作成されます。ファイルがすでに存在する場合
  は、上書きされます。

  mode=”w”を指定した場合はファイルの中身はになります。
  mode=”w”モードを指定した場合は、ファイルを開いただけで一度空の状態
  になるので注意が必要です。
  (mode=”a”を指定した場合は書き込み内容が追記されます。)

 「cp932」はMicrosoftがShift_JISを独自に拡張した文字コードです。
  日本語版のWindows環境ではShiftJISにしておいた方が無難ですので
 「cp932」を使用します。

④ CSVファイルに書き込みます。
  書き込む時はCSV用のライター(writerを作成します。
  csv.writer()のかっこ内にファイルを開いた時の変数fを指定すると作成でき
  ます。

⑤ ヘッダーを書き込みます。

⑥⑦ データが格納されているUSD_priceリストからデータを抽出して、
            writerowで1行づつ書き込んで行きます。
  

以上で終了です。

為替データを取得してファイルに書き込んだだけでは自動化の意味はあまりありませんが、このデータを分析・加工し、またグラフにしてパワーポイントに表示するところまで自動で仕上げれば、報告用の資料を作成する時間はだいぶ縮小されます。

エクセルファイルをpandasで利用してまたエクセルに戻すには

財務においてデータはエクセルを利用することが一般的かと思います。

そのデータを分析するには、pandasを利用すると楽になります。
それには次の手順が基本となります。

1. エクセルファイルをpandasで読み込む
2.pandasで表を加工、分析する
3.pandasで出来上がった表、データをエクセルに戻す(書き込む)

次の表のうち、科目名称(B列)、通貨コード(D列)、円換算額(F列)の列のみを抽出して別シートに「外貨換算_2」の名前で書き込んでいきます。

では、コードを書いていきます。

import os
os.chdir(“C:/Users/ユーザー名/Documents/Python”)

import openpyxl
import pandas as pd

fx_sheet=pd.read_excel(“./RPA/RPA_エクスポージャー分析.xlsx”,sheet_name=”外貨換算”) # ①

fx_sheet_2=fx_sheet[[“科目名称”,”通貨コード”,”円換算額”]] # ②

file_name = “./RPA/RPA_エクスポージャー分析.xlsx” # ③

with pd.ExcelWriter(file_name,engine=”openpyxl”, mode=”a”) as ew:
fx_sheet_2.to_excel(ew, sheet_name=”円換算額_2″) # ④

①エクセルファイルをpandasで読み込みます。

② pandasで読み込んだデータを加工します。ここでは、表示したい科目を選んでいます。

③  エクセルのファイル名を変数へ代入しておきます。(④で使います)

④ pandasので加工したデータをエクセルシートに書き出します。
sheet_name= “円換算額_2” と書き出すシート名を指定します。

次のように、新しいシートに指定した名前でデータが書き込まれます。

エクセル → pandas → エクセル の手順でした。

表の最終列を読み取る

 

次の表のE列に日付を書き込みたいと思います。

表の最終列(D列)を取得して、書き込み列を指定します。

表の最終列を取得する場合、表より離れた場所に入力や書式設定がされているとその場所を最終列とみなしてしまいます。
そこで、表の最終列(ここではD列)を確実に最終の列と判断するために次の関数を利用します。

def remove_right_none(lst): # 関数名
    count = len(lst) # ① 
    for item in reversed(lst): # ②
        if item.value is None: # ③
            count = count – 1 # ③
        else:   # ④
            break # ④
    return count # ④

① 引数に指定されたlstの長さをカウントする
② 最後のセルからひとつづつitem変数に入る
③ そのセルが空白ならばカウントを1つ減らず
④ セルが空白でなければ処理は終了して、その時点のセルの数を返す(それが空白列を除いた最大列となる)

 

それでは、E2へ日付を書き込んでいきます。

# ワークシートを変数へ代入します。
wb = openpyxl.load_workbook(“./RPA/RPA_月次決算.xlsx”)
ws_3 = wb[“損益計算書(累計)”]

# 「損益計算書(累計)」シートの最大列を取得します。
data_column_num_3 = max([remove_right_none(row) for row in ws_3.rows])

このコードの意味を説明すると次のようになります。

①決算書シートの行から1行づつ取り出しrow変数へ代入します。
② そのrow(1行分)が関数の引数となりremove_right_none(lst)関数で処理され列数が返ります。
③ すべての行につき関数で処理された後、1番大きい列数が最大列としてdata_column_num_3へ代入されます。

行ごとに最終列がことなる可能性があるため、すべての行につき最終列を取得した後に、その中でも一番大きな列番号をmax( ) で取得して表の最終列とします。

# 書き込む列番号を取得します。
cl_col_3 = data_column_num_3 + 1

# 日付を書き込む
ws_3.cell(row=2, column=cl_col_3).value = “2020/05/31″

 

以上で、損益計算書(累計)シートのE2へ ”2020/05/31″ と書き込まれました。

1行すべてが空白の行を判定する

エクセルシートに合計残高試算表があります。
このデータを読み込んでリストにしたいと思います。

合計残高試算表の最終行は次のようになっています。

データは238行目で終了しており、242行目は貸借が一致するか確認の計算式が入力されています。

表から離れた場所にデータや書式設定があるとそこまで行を読み込んでしまいます。

よって、行のすべてが空白(239行目)の場合にはその前の行(ここでは238行)が表の最終行とするようにしたいときこの関数を利用します。

次のようにis_empty( )の名前で関数を作成します。

def is_empty(cell):
return cell.value is None or not str(cell.value).strip()

これは、セルに値がない場合はTrueを返します。
また、セルに半角スペースがある場合、空とみなされなため、スペースをstrip()で除いた後、strではない(つまり空)ときTrueを返します。

表の最大列を取得する関数を作成する

毎月の試算表が列ごとに記されており、新しい月の試算表は表の最大(右)列のひとつ右の列に書き込まれていきます。

では表の最大列はどのように取得したらよいのでしょうか?

通常はmax_columnで取得できますが、表からはずれて右側に文字または書式が設定されている場合、その列を最大列と認識してしまいます。

すると書き込みはその右側の列に書き込まれてしまいます。

上の図では、表の最後は先月(4月)の書き込みがされているD列なので、当月(5月)分としてはE列に書き込みたいところです。
しかし、G4にグレー色の書式が設定されているため、G列を表の最後の列と認識してしまい、その右の列、つまりH列に5月分が書き込まれてしまっています。

表からはずれて文字入力や書式が設定されていても、表の最終列を認識するためそれに対応する関数を作成することにします。

最大列を取得する関数を作る

書式が設定されていてもデータがなければ空列とみなすように作成します。

def remove_right_none(lst): # 関数名
    count = len(lst) # ①
    for item in reversed(lst): # ②
        if item.value is None: # ③
            count = count – 1 # ③
        else:   # ④
            break # ④
    return count # ④

① 引数に指定されたlstの長さをカウントする
② 最後のセルからひとつづつitem変数に入る
③ そのセルが空白ならばカウントを1つ減らず
④ セルが空白でなければ処理は終了して、その時点のセルの数を返す(それが空白列を除いた最大列となる)

 

 

最大列を取得する関数ができたところで、さっそく使用してみます。

作成した remove_right_none( ) 関数を次のように利用します。

# ワークシートの取得
ws_2 = wb[“合計残高試算表_2”]

# 各行の最大列うち、最も大きい列を取得
data_column_num_2 = max([remove_right_none(row) for row in ws_2.rows])  

1.シートの行から1行づつ取り出しrow変数へ代入する。
2.そのrow(1行分)が関数の引数となりremove_right_none( )関数で処理され列数が返る。
3.すべての行につき関数で処理された後、max( )によりその中から1番大きい列数が最大列としてdata_column_num_2へ代入される。

これで表の最大列(上記図のD列)が取得されました。

 

 

集合関数を利用して事業区分を整える

子会社を吸収した関係で事業区分の再編を行っています。

親会社の事業区分は次の通りです。
事業区分   チーム区分
・事業C  ・チーム1
・事業C  ・チーム2
・事業D
・事業E

子会社の事業区分は次の通りです。
事業区分   チーム区分
・事業A  ・チーム1
・事業A  ・チーム2
・事業C  ・チーム1
・事業C  ・チーム2
・事業D
・事業G

親会社と子会社が合併した関係で事業区分につき整理したいと思います。
次の①、②、③の目的で整理してみます。

①和集合
親会社と子会社の事業区分をまとめます。
親会社と子会社では、同じ事業を行っていたものもあり、またC事業のようにチームは分かれますが同じ事業というものもありますので、事業区分がダブることなく親会社と子会社を合わせてどんな事業があるのかを把握します。

②積集合
親会社と子会社で同じ事業は何があるのかを整理します。

③差集合
子会社の行っていた事業のうち、親会社で行っていない事業は何があるのかを整理します。

それぞれの事業区分を抽出したら次のようなデータができました。
親会社(parent)=C ,E, D, C
子会社(sub)=G, D, C, C, A, A

アルファベット順になっていないため見にくいですが、気にすることなく次のコードを書いて整理します。
(pythonのnumpyモジュールを使用します)

import numpy as np

parent = [‘C’, ‘E’,’D’,’C’]
sub = [‘G’,’D’,’C’,’C’, ‘A’,’A’]

# ① 親会社(paretnt)と子会社(sub)の和集合を出力
print(np.union1d(parent,sub))

# ② 親会社(paretnt)と子会社(sub)の積集合を出力
print(np.intersect1d(parent, sub))

# ③子会社(sub)から親会社(parent)を引いた差集合を出力
print(np.setdiff1d(sub, parent))

すると次のように出力されました。

['A' 'C' 'D' 'E' 'G']
['C' 'D']
['A' 'G']



結果は次の通りとなります。

① 親会社と子会社の事業をまとめる(和集合)
[‘A’ ‘C’ ‘D’ ‘E’ ‘G’]

抽出したデータはアルファベット順になっていませんでしたが、結果はアルファベット順に返りました。
また、チームが複数あるC事業はひとつにまとめられています。

②親会社が行う事業と子会社が行う事業で同じもの(積集合)
[‘C’ ‘D’]

③子会社が行う事業で親会社が行っていなかった事業(差集合)
[‘A’ ‘G’]

以上統合における事業整理でした。

数値の処理方法 – 桁区切り / format関数 / python

桁の大きな数字を記載する時には、3桁ごとにカンマで区切るのがマナーです。

いくら数字に慣れている人でも、「100000000」をすぐに1億だと判断できる人はいないと思います。

この3桁区切りは、特に英語では理解が簡単です。

英語では、1(one) ,  10(ten) ,  100(hundred)  の後は次のように3桁ごとに単位が用意されています。

1,000=thousand
1,000,000=million
1,000,000,000=billion
1,000,000,000,000=trillion

このように区切りがあることにより、大きな数字でも簡単に読むことができます。

5,000は、5と1,000(thousand)だから「five thousand」
50,000は、50と1,000(thousand)だから「fifty thousand」
500,000は、500と1,000(thousand)だから「five hundred thousand」

5,000,000 は、5と1,000,000(million)だから「five million」
50,000,000は、50と1,000,000(million)だから「fifty million」
500,000,000は、500と1,000,000(million)だから「five hundred million」

というように、以下billion, trillion も
50,000,000,000は、50と1,000,000,000(billion)だから「fifty billion」
50,000,000,000,000は、50と1,000,000,000,000(trillion)だから「fifty trillion」

とすぐに読むことができます。

というわけで、pythonで数字に桁区切りする方法を記述します。

桁区切りなし

ave_v=766666
print(ave_v)
>>> 766666

「ave_v」は変数で、数値が代入されています。

 

桁区切りあり

ave_v=766666
print( ” {:,} ” . format(ave_v) )
>>> 766,666

桁区切りをつけるには、
 ” {:,} ” . format(     ) 
とします。

format(   ) に数値または数値が代入されている変数を入れます。

それをprint(   )  で囲むと
>>> 766,666 と表示されます。
 

文字を付加する

ave_v=766666
print(“売上合計は”+”{:,}”.format(ave_v) + “円です”)
>>> 売上合計は766,666円です

文字を不可して表示させるには、
文字を「 ”   ”  」で囲んで、「+」でつなげます。

“売上合計は”  + ” {:,} ” . format(     ) + ” 円です”   
とします。

format(   ) に数値または数値が代入されている変数を入れます。

それをprint(   )  で囲むと
>>> 売上合計は766,666 円です
と表示されます。
 

桁区切りと小数点以下を表示する

ave_v=766666.66666
print(“売上合計は”+”{:,.2f}”.format(ave_v) + “円です”)
>>> 売上合計は766,666.67円です

 

小数点がある場合、
たとえば小数第2位まで表示させる場合は、波カッコの中を {:,.2f} とします。

小数第3位まで表示させる場合は、{:,.3f} とします。

{ コロン(:)  カンマ(,) ピリオド(.)  小数点桁数 f  } となります。


 

小数点を正しく十進法で表示する方法 – decimal / python

小数点以下を四捨五入する場合、次のように思わぬ答えが返る場合があります。


ave_v = round (1.25,1)
print(ave_v)
>>>1.2  ← 1.3のはずが・・

ave_v = round (1.35,1)
print(ave_v)
>>>1.4 ← 合ってる

 

ave_v = round (1.45,1)
print(ave_v)
>>>1.4 ← 1.5 のはずが・・

 

私たちが使い慣れているのは10進数での数値表現ですが、コンピュータは2進数で数値を表現します。
10進数と2進数の間の変換には誤差が伴います。

 

ひとつの誤差は小さいものでも、計算の過程で蓄積されていくと、最後には大きな差になる可能性もあります。

また、if により条件分岐させる場合、
AとBが一致するはずが、不一致とされて異なる処理になってしまう可能性もあります。

A = 0.1
B = 0.2
C = 0.3

if A+B==C:
    print(‘OK’)
else:
    print(‘NO’)
>>> NO  ← OK のはずなのに・・

 

これは「A+B」で返る値が微小に異なるからです。

A = 0.1
B = 0.2
print(A+B)
>>> 0.30000000000000004

 

財務においては、数値は正確に算出しなければなりません。
Pythonでは浮動小数点数の値を正確に計算することができるdecimalモジュールが用意されていますので、こちらを利用する必要があります。

 

 

十進法で正確に計算する
from decimal import Decimal

A = Decimal(“0.1”)
B = Decimal(“0.2”)

print(A + B)

>>> 0.3

 

解説

import 文によりDecimal モジュールをインポートします。

「import  decimal 」
とだけで記述してもよいのですが、その場合には

A= decimal. Decimal (“0.1”)
のように、「decimal. Decimal」と記述する必要があります。

 

Decimal ( ) には数値が入りますが、「”  ” 」または「’   ‘ 」を付けて文字列とする必要があります。
Decimal(“0.1”)の代わりに、Decimal( str(0.1) ) としてもOKです。

 

文字列としなかった場合、エラーが出てくれれば間違いに気づきますが、
次のようにエラーが出ずに誤差が出てしまうので注意が必要です。

A = Decimal(0.1)
B = Decimal(0.2)
print(A + B)
>>> 0.3000000000000000166533453694


 

任意の桁で四捨五入する

from decimal import Decimal, ROUND_HALF_UP

A=Decimal(“1.456”).quantize(Decimal(“0”),rounding=ROUND_HALF_UP)
print(A)
>>>1

B=Decimal(“1.456”).quantize(Decimal(“0.1”),rounding=ROUND_HALF_UP)
print(B)
>>>1.5


C=Decimal(“1.456”).quantize(Decimal(“0.01”),rounding=ROUND_HALF_UP)
print(C)
>>>1.46



 

解説

四捨五入する場合には、
decimalモジュールの「quantizeメソッド」を使用することで、小数点を指定した桁数で丸めることができます。

 

from decimal import Decimal, ROUND_HALF_UP
モジュールをインポートします。

 

Decimal(“  ”).
対象となる値を入力します。

 

quantize(Decimal(“ ”)
整数で丸める場合には、quantize(Decimal(“0”)とします。
小数点第1位まで求める場合には、quantize(Decimal(“0.1”)とします。
小数点第2位まで求める場合には、quantize(Decimal(“0.01”)とします。

 

rounding=ROUND_HALF_UP
引数roundingにROUND_HALF_UPを指定することで繰り上げによる四捨五入を行うことができます。

 

小数点を処理する方法 – round / python

python における小数点の処理の仕方について見ていきます。

前回はA事業に所属するチームの平均売上高を算出しました。
参照  →  売上データを集計する方法 – 辞書型 / for 構文

何のことわりもなく、四捨五入をして少数第1位まで表示させましたが、業績評価においては「四捨五入・切り捨て・切上げ」のルール通りに報告する必要があります。

前回において、平均点の計算式は
ave_v  = round (Asum/len(salesA),1) となっておりましたが、
 Asum / len (salesA) の答えを、「766666.66666」に読み替えて説明していきます。

小数点以下の処理方法

#四捨五入をする場合・・①

ave_v = round ( 766666.66666)
print(ave_v)
>>> 766667

 

#小数点以下を切り捨てる場合・・②

import math

ave_v= math.floor(766666.66666)
print(ave_v)
>>> 766666

 

#小数点以下を切り上げる場合・・③

import math

ave_v= math.ceil(766666.66666)
print(ave_v)
>>> 766667

 

① 小数点以下を四捨五入をする

小数点以下を四捨五入するには「round ( ) 」を利用します。

 

カッコ( ) の中に入れる値を「引数」といいます。
引数を複数入力できる場合は、左から第1数、第2引数と数えます。

 

round( ) の()の中の第1引数は対象となる値ですが、第2引数は丸めて表示する位を指定します。

 

第2引数を指定しない場合は小数点第1位を四捨五入して整数になります。

ave_v = round ( 766666.66666)
print(ave_v)
>>> 766667

round( 対象値 , 丸めて表示する位)

 

第2引数に「1」を指定した場合には、四捨五入をして少数点第1位まで表示します。
(小数点以下第2位を四捨五入して第1位まで表示する)

ave_v = round ( 766666.66666,1)
print(ave_v)
>>> 766666.7

 

第2引数に「-1」を指定した場合には、四捨五入をして整数1の位が「0」になります。
(整数の1の位を四捨五入する)

ave_v = round ( 766666.66666,-1)
print(ave_v)
>>> 766670.0

 

 

②小数点以下を切り捨てる

Pythonで切り捨てにするには、Python付属のmathモジュールの「floor」を使います。
「import math」と記述して、mathモジュールをインポートします。

import math
ave_v= math.floor(766666.66666)
print(ave_v)
>>> 766666

 

 

③小数点以下を切り上げる

Pythonで切り上げにするには、Python付属のmathモジュールの「ceil」を使います。
「import math」と記述して、mathモジュールをインポートします。
(すでにインポートされている場合は不要)

import math

ave_v= math.ceil(766666.66666)
print(ave_v)
>>> 766667

 

round( )で「5」を丸める場合は注意!

#期待値1.3  ×
ave_v = round (1.25,1)
print(ave_v)
>>>1.2

#期待値1.4  〇
ave_v = round (1.35,1)
print(ave_v)
>>>1.4

 

#期待値1.5 ×
ave_v = round (1.45,1)
print(ave_v)
>>>1.4

 

#期待値1.6  〇
ave_v = round (1.55,1)
print(ave_v)
>>>1.6

 

#期待値1.7   ×
ave_v = round (1.65,1)
print(ave_v)
>>>1.6

 

#期待値1.8  〇
ave_v = round (1.75,1)
print(ave_v)
>>>1.8

 

#期待値1.9   〇
ave_v = round (1.85,1)
print(ave_v)
>>>1.9

 

#期待値2.0   ×
ave_v = round (1.95,1)
print(ave_v)
>>>1.9

 

round( ) で「5」を丸める場合は注意が必要です。
上記のように例をあげてみました。

# 期待する値  〇(表示の値が同じ場合)  ×(表示の値が異なる場合)

#期待値1.3  ×
ave_v = round (1.25,1)
print(ave_v)
>>>1.2

「1.25」の値について、少数点2位を四捨五入して小数点1位で表示させています。
期待する値は「1.3」になるはずですが、表示される値は「1.2」となります。

原因は ,次の公式ページにもあるように、小数を浮動小数点数で正確に表せないことが原因 のようです。
組み込み関数round()

そこで、小数点数を正しく十進数で計算するにはdecimalモジュールを使用する必要があります。

売上データを集計する方法 – 辞書型 / for 構文

財務部では毎月業績を算出し、分析をして経営会議へ報告する必要があります。データの取得から分析、ひいては報告資料まで自動化したいものです。

pythonは分析系に強いプログラミング言語ですので、財務に携わる方はぜひ習得しておきましょう。

まずは簡単なところから始めるにあたり、チームの売上高を合計し、平均を算出するところからやってみたいと思います。

要件
チームの平均売上高を求めたい

 

次のような設定を想定します。

事業Aに、チームが3チーム(A,B,C) 所属しています。
それぞれのチームの月間売上高は次の通りでした。

事業名:A_bu
所属チーム 売上高 
Aチーム   60万円
Bチーム   80万円
Cチーム   90万円
合計     230万円

この3チームの平均を求めていきます。

230万円÷3=76.6万円になれば正解ですね。

要件回答
売上合計: 2300000
平均点: 766666.7

 

チーム売上の平均を求める

#A事業の業績データを辞書型で集計する・・①
salesA={‘A’:600000,’B’:800000,’C’:900000}
#合計を求める
Asum = 0
for v in salesA.values():
    Asum += v
print(‘売上合計:’,Asum)


”’チーム当たりの平均売上高を計算・・②
小数点以下1位になるように四捨五入”’

ave_v=round(Asum/len(salesA),1)
print(‘平均点:’,ave_v)

 

 

①A事業の業績データを辞書型で集計する

salesA={‘A’:600000,’B’:800000,’C’:900000}

変数「salesA」={ キー(チーム名):値(売上高)}
として辞書型でセッティングします。

#合計を求める
Asum = 0
for v in salesA.values():
Asum += v

平均を求めるには、まず合計を求める必要があります。
「Asum」という箱を設けて、ここに各チームの売上高を放り込んでいきます。

最初はAsumの箱は何も値が入らない「0」を設定します。
for 構文を使ってひとつづつデータを取り出し、Asumに加えていきます。

for構文は次のようになります。

「salesA」は①で設定した辞書型の変数で、チームと売上高が格納されています。
「values()」でそのデータから「値」を取り出します。ここでの値は「売上高」ですので、各チームの売上高がfor の後の「v」へ順番に入っていきます。

辞書型データは {‘A’:600000,’B’:800000,’C’:900000} となっていますので、
まずは’A’チームの「600000」が「v」に入ります。

そして、その「v」の値が
Asum+=v でAsumの箱に放り込まれます。
「Asum+=v 」は 「Asum  =  Asum + v  」と同じ意味です。

これでAsumの箱は「600000」が入ることになります。

次に、’B’チームの「800000」が「v」に入り、それが「Asum」に追加されます。そして’C’チームの売上高がAsumへ加算された時点でfor ループは終了します。

print(Asum)で合計を表示させると
>>>  2300000
となっていれば正解です。

 

②チーム当たりの平均売上高を計算

ave_v=round(Asum/len(salesA),1)
print(‘平均点:’,ave_v)

 

最初の行で平均を算出して、その値を「ave_v」変数へ代入しています。
Asum  /  len(salesA)  は 
合計   /     チーム数    を意味しています。

Asum は ①より求めた売上高の合計(2300000)が格納されています。len(salesA) は  ①の冒頭で設定したsalesAに格納されている値の数(3)となります。

print(ave_v)で
>>> 766666.7
と表示されれば正解です。

 

辞書型からデータを抽出する基本的な使い方- for 構文 / python

pytonにおいて、データを1つの変数に格納する方法はコレクションとよばれ、大きく分けて4つの方法があります。

1.リスト型
2.ディクショナリ型
3.タプル型
4.集合型

詳しくはこちらを参照→ 複数データを1つの変数で管理する / コレクション

 

辞書型では、「キー」と「値」をセットで格納することができ、「キー」を指定して「値」を呼び出すことができます。

今回は、辞書型のコレクションから、キーと値を同時に取り出して、セットで画面表示する方法について記述しておきます。

 

要件
事業Aで扱う商品を販売価格とセットで表示させたい

 

当社のA事業で扱う商品と販売単価の一覧は次の通りです。

商品名  販売単価
a                   1,000
b                   3,000
c                   5,000
d                   7,000
g                   9,000

商品データが辞書型で変数に代入されています。
Aitem={ ‘a’:1000,’b’:3000, ‘c’:5000, ‘d’:7000, ‘g’:9000}

 

要件回答
aの販売単価は1000円です。
bの販売単価は3000円です。
cの販売単価は5000円です。
dの販売単価は7000円です。
gの販売単価は9000円です。

 

コード(D-1)

#辞書型のデータ(商品名と販売単価)を変数に代入・・①
Aitem={
    ‘a’:1000,
    ‘b’:3000,
    ‘c’:5000,
    ‘d’:7000,
    ‘g’:9000
}

#辞書型のデータ一からキーと値をセットで取得する・・②
for name,price in Aitem.items():

#画面に出力・・③
    sp=”{0}の販売価格は{1}円です。”.format(name,price)
    print(sp)

>>>
aの販売価格は1000円です。
bの販売価格は3000円です。
cの販売価格は5000円です。
dの販売価格は7000円です。
gの販売価格は9000円です。

 

 

 

①辞書型のデータ(商品名と販売価格)を変数に代入する

変数名を「Aitem」とし、次のようにセットします。
辞書型は波カッコ{ } で括ります。

Aitem={ ‘a’:1000,’b’:3000, ‘c’:5000, ‘d’:7000, ‘g’:9000}

変数名 =  {商品名1:販売価格1, 商品名2:販売価格2・・・}

 

②辞書型のデータからキーと値をセットで取得する

for name,price in Aitem.items():

構文は、
for 「キー変数」, 「値変数」 in  「辞書型変数」. items( ) :
items( ) は、キーと値を利用する関数です。

 

Aitem に格納されているキー(商品名)と値(販売単価)が順番にfor 以下の「キー変数 name」と「値変数 price」の箇所に入っていきます。

まず最初の処理「’a:1000’」から処理が始まります。
「a」が「name」に入り、「1000」が「price」に入ります。

 

③画面に出力する

  sp=”{0}の販売価格は{1}円です。”.format(name,price)

 

まず、画面表示として表示したい文字列を for 構文の変数で設定した「name」と「price」をブランクにして作ります。

” { } の販売単価は { } 円です。”

と、値を埋め込みたい場所に{ } を書きます。

最初の{ } に「0 」と番号を付け、次の{ } には「1」と番号を付けていきます。

 

format( name, price )のうち、「name」が {0} に入り、priceが{ 1 } に入ります。

今、「name」には「a 」が入っており、「price」には「1000」が入っているので、{0} には「a」が入り、{1}には「1000」が入ることになります。

よって、
“{0}の販売価格は{1}円です。” →  ”{a}の販売価格は{1000}円です。”
となります。

spはその文字列が入る変数で、print(sp)で表示させます。

 


f-stringという機能を使用すると、簡潔に書くこともできます。

sp= “{0}の販売価格は{1}円です。”.format(name,price)
print(sp)

の代わりに

print ( f  ” 商品 {name} の販売価格は {price} 円 です”  )

と記述します。

文字列の直前に「f」を付けると、数値が格納された変数名を{ } の中に直接入れることができますので、こちらの方がわかりやすいかもしれません。


 

一つ目の処理が終了すると、二つ目の処理に移りますので、

Aitem に格納されている2つ目の商品と販売価格のセット「b: 3000」の処理が始まります。

同じような処理を繰り返し、最後の商品まで終了したらプログラムは終了します。

 

分析に役立つ集合演算の作り方 / python

当社は、A事業とB事業に分かれており、A事業で扱う商品とB事業で扱う商品が区分されています。

次のような質問に答えるためどのようにデータを抽出するかを記述しておきます。

①A事業とB事業を合わせた全体の商品一覧

②A事業のうちB事業で扱っていない商品一覧

③B事業のうちA事業で扱っていない商品一覧

④A事業とB事業の両方で扱っている商品一覧

⑤A事業とB事業でだぶっていない商品一覧

 

コードの説明に入る前に集合演算についておさらいをしてみます。
2つのセットの共通点や相違点を探すには、「集合演算」の考え方を利用します。
学生の頃ベン図を学んだかと思いますが、関係性をベン図で描いてみます。

 

a商品~g商品まで7つの商品があります。
A事業とB事業でそれぞれ次のように商品を扱っています。

A事業:a,b,c,d,g
B事業:b,c,d,e,f

 

 

和集合
二つの集合に対して、少なくともどちらかに入っているものを集めた集合を和集合といいます。

差集合
ある集合の要素から別の集合の要素を取り除いたものを差集合といいます。

積集合
二つの集合に対して、両方とも入っているものを集めた集合を積集合といいます。

対称差
二つの集合に対して、どちらか一方だけを満たす要素を集めた集合を対称差といいます。

課題の①~⑤のうち、それぞれがどの集合に相当するのかを当てはめてみます。

①A事業とB事業を合わせた全体の商品一覧・・和集合

②A事業のうちB事業で扱っていない商品一覧・・差集合

③B事業のうちA事業で扱っていない商品一覧・・差集合

④A事業とB事業の両方で扱っている商品一覧・・積集合

⑤A事業とB事業でだぶっていない商品一覧・・対称差

 

それでは、イメージ図ができたところでコードの記述をしていきます。

 

集合演算の作り方

#商品を変数にセットする
itemA={‘a’,’b’,’c’,’d’,’g’}
itemB={‘b’,’c’,’d’,’e’,’f’}

#A事業とB事業を合わせた全体の商品一覧を作る・・①
itemA|itemB
>>> {‘a’, ‘b’, ‘c’, ‘d’, ‘e’, ‘f’, ‘g’}

#A事業のうちB事業で扱っていない商品一覧を作る・・②
itemA-itemB
>>> {‘a’, ‘g’}

#B事業のうちA事業で扱っていない商品一覧を作る・・差集合・・③
itemB-itemA
>>> {‘e’, ‘f’}

#A事業とB事業の両方で扱っている商品一覧を作る・・④
itemA&itemB
>>> {‘b’, ‘c’, ‘d’}

#A事業とB事業でだぶっていない商品一覧を作る・・⑤
itemA^itemB
>>> {‘a’, ‘e’, ‘f’, ‘g’}


 

 

集合と演算子の関係は次の通りです。

課題番号 集合の種類 コード記述の演算子
和集合 「|」
②③ 差集合 「− 」
積集合 「&」
対称差 「^」