カテゴリー: 業務自動化プログラム

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

/ MELBO / コメントする

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

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

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

 

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

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

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

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

 

 

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

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

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

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

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

手順

１．URLリストを作成する。
　　それぞれのページをリスト（URL）に入れていきます。

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

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

 

手順１．　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

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

 

コード１

# インプットボックスで日付を指定します。・・①
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は次の通りです。

１ページ目
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

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

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

次に共通部分を確認してみます。
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を取得していきます。

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

コード２

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要素(リスト要素）を取得して変数へ代入します。

 

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

 

これによると、ページ番号のリンクはul要素内の３つの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は、「コード１」で取得した次の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に表示されている為替データを抽出してひとつのリストにまとめていきます。

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

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

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

コード３

# データをリストに入れていく
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要素は１つしかない
        ので、tables[0]になります。

⑦　これよりデータを取得していきます。
　　まず、データを格納するリスト（data_list)を作成します。
 
⑧　この表の先頭行は項目ですので、データ取得は2行目からになります。
　　その行数をカウントする変数をline_numとし、０を入れておきます。
 
⑨　取得した全行（rows)から1行づつ抽出して変数（row)に代入します。
 
⑩　そのとき、行数を１カウントします。
 
⑪　行数が２未満ならば処理せず飛ばします。（1行目の項目行を飛ばす）
 
⑫　変数rowに代入された１行分から「th」要素と「td」要素を取得して変数
　（cells)へ代入します。
 
tr要素の中には、セルに相当するth要素とtd要素が配置されています。th要素には<th> ~ </th>で見出し（ヘッダー）を囲みます。td要素には<td> ~ </td>でデータを囲みます。
 
⑬　変数cellsからデータを取得して蓄積していくためvaluesリストを作成しま
       す。
 
⑭　全行分のtd,th要素を格納した変数cellsをループ処理して、１データずつ中
　　身を取り出します。
 
⑮　要素の内容をgetText()メソッドで読み取り、valuesリストへ格納します。
 
⑯　valuesリストに格納されたデータのうち、1番目から5番目のデータを取得し
       て変数datasへ代入します。
 
⑰　変数datasへ代入されたデータをリスト（data_list）へ格納します。
        data_listには１ページ分のデータが格納されます。
 
⑱.⑲　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’), ・・・・・・・・・・・・・]

手順３．CSVファイルへ書き出す

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

 

コード４

# 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=””を加えます。
　　（これを忘れると１行おきに空行が挿入されます。）
　　書き込みモード（mode=”w”）でファイルを開くと、ファイルが存在しない
　　場合は、新しいファイルが作成されます。ファイルがすでに存在する場合
　　は、上書きされます。

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

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

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

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

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

以上で終了です。

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

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

/ MELBO / コメントする

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

そのデータを分析するには、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 → エクセル　の手順でした。

表の最終列を読み取る

/ MELBO / コメントする

 

次の表の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変数に入る
③ そのセルが空白ならばカウントを１つ減らず
④ セルが空白でなければ処理は終了して、その時点のセルの数を返す（それが空白列を除いた最大列となる）

 

それでは、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])

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

①決算書シートの行から１行づつ取り出し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行すべてが空白の行を判定する

/ MELBO / コメントする

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

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

データは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を返します。

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

/ MELBO / コメントする

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

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

通常は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変数に入る
③ そのセルが空白ならばカウントを１つ減らず
④ セルが空白でなければ処理は終了して、その時点のセルの数を返す（それが空白列を除いた最大列となる）

 

 

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

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

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

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

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

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

 

 

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

/ MELBO / コメントする

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

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

子会社の事業区分は次の通りです。
事業区分　　　チーム区分
・事業A　　・チーム１
・事業A　　・チーム２
・事業C　　・チーム１
・事業C　　・チーム２
・事業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’]

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

Pythonでパスワードを生成する

/ MELBO / コメントする
要件
文字や数値を入れた箱から、１つづつランダムに選び出し、それらを連結した文字列を作成する。

パスワードを生成する時などに使えます。

コード

import string
import secrets

def pass_gen(size=10):
chars = string.ascii_uppercase + string.ascii_lowercase + string.digits

return ”.join(secrets.choice(chars) for x in range(size))

print(pass_gen(6))

GAQK0FUENn

 

手順

１．chars変数に、パスワードで使用する文字・数字を代入します。

２．そのchars変数からひとつづつ文字・数値をランダムに抽出します。

３．抽出された文字・数値を結合してパスワードが出来上がります。

 

 

説明

１．chars変数に代入する文字・数値について

　① string.ascii_uppercase
       大文字 ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’

    ② string.ascii_lowercase
       小文字 ‘abcdefghijklmnopqrstuvwxyz’

    ③ string.digits
       文字列 ‘0123456789’

    ほか
　記号を入れる場合は　’%&$#’　
　①と②を合わせたものは　string.ascii_letters

 

２．パスワードの文字数について

print(pass_gen(6))
pass_gen( )の中にパスワードの文字数を指定します。
ここでは６つの文字数を指定しています。
数値を指定せずに、print (pass_gen( ) ) とすると、10の文字数のパスワードが出来上がります。
これは最初に、
def pass_gen(size=10):の箇所で
デフォルト値をsize=10としているためです。

 

３．文字の抽出および結合について

secrets.choice(chars) for x in range(size)で文字を抽出しています。
これを　”.join( )　の（　）の中に入れることにより、ひとつづつ抽出された文字・数値が結合されます。

”.join( )　の　「” 」はシングルクォーテーションが２つで、文字を連結したい記号を中に入れます。
ここでは、文字を空白で連結したいので”としています。
（” はシングルクォーテーションが２つです)

もしもハイフン(‐）で結合する場合には”の間に「‐」を入れ
‘‐’.join(　　)　とします。

カンマ（,）で文字を結合する場合には、”の間に「,」を入れ
‘,’.join(　　)とします。

 

パスワードが生成されるイメージ

 

注意

secretsモジュールについて

先のコードでは、乱数を生成するのにsecretsモジュールを使用しています。
乱数を生成するにはrandomモジュールでも生成できますが、あくまでもシミュレーション向けの擬似乱数で,セキュリティや暗号学的に設計されていないようです。

セキュリティ向けの強い乱数を生成できるsecretsモジュールを用いるので、Python3.6以上を利用してください。

IDLEを利用する場合について

先のコードをIDLEのshell画面にコピペすると、次のエラーが出ます。

SyntaxError: multiple statements found while compiling a single statement

IDLEのShell画面では複数行に渡るペーストはできないようです。
複数行のペーストをする場合には、Shell画面のFile>New Fileで出てくる「エディター画面」の方を利用して下さい。

こちらはエディターで、Run>Run Moduleとしてみてください。
実行結果がShell画面の方へ出力されます。

jupyter notebookであればそのままコピペしても大丈夫です。
というより、IDLEのShell画面のみコピペ不可のようです。

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

/ MELBO / コメントする

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

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

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

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

 

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

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

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

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

230万円÷３=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の箱は何も値が入らない「０」を設定します。
for 構文を使ってひとつづつデータを取り出し、Asumに加えていきます。

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

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

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

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

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

次に、’B’チームの「800000」が「ｖ」に入り、それが「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に格納されている値の数（３）となります。

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

 

Webサイトから情報を自動で抜き出す方法

/ MELBO / コメントする

財務では、経済情報、マーケット情報、株価・為替情報など逐次整理しておく必要があります。

情報源は一般的にはWebサイトになりますが、サイトにアクセスして情報を抜き出すにも手間がかかるため、これらの作業を自動化して業務効率を図ることが必要です。

集めた情報から分析、加工、データマイニング、そしてプレゼン資料にまとめるところまですべて自動化したいのですが、まずは初歩的なところから始めたいと思います。

財務など分析系にはpythonというプログラミング言語が最適ですので、pythonを利用して業務効率を図るにはどうすればよいかを考えていきます。

今回は、手始めにヤフーファイナンスから書かれている中見出し（h2)の題名を収集する方法を試してみました。

課題
指定したwebサイトから項目を抜き出し箇条書きで表示したい

Webページから第２項目（h2)を拾う

import requests
from bs4 import BeautifulSoup

url = “https://news.finance.yahoo.co.jp/”

rp = requests.get(url)

bs = BeautifulSoup(rp.text, ‘html.parser’)

for i in bs.select(“h2”):

　print(i.getText())

 

分析したいWebページのアドレスをurl = ”           ” の中に埋め込みます。

Webページのアドレスがurl変数に代入され、requests.get()の引数に渡すことによりWebサイトのデータを取得し、変数rpに代入します。

requests.get(url)で取得したデータをテキストとしてBeautiful Soupに渡しただけで、HTMLを解析してくれます。

このコードではh2のタグを選んで全て表示させています。

第二引数の「html.parser」はHTMLでパースをするということを設定しています。
bs.select(“h2”)でh2のタグを指定して、for文を回すことでそれぞれのタイトルを抽出することが出来ました。

抽出結果

経済総合

市況・概況

日本株

外国株

産業

雑誌・コラム

【PR】Yahoo!ファイナンスからのお知らせ

不動産投資コラム（楽待）

ヘッドライン

総合アクセスランキング

マーケット情報