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

エクセルファイルを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!ファイナンスからのお知らせ

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

ヘッドライン

総合アクセスランキング

マーケット情報