エクセルファイルを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 → エクセル の手順でした。

CSSはどこに入力すればよいのか

見出しを飾りたい場合、検索するといろんな例が出ています。

コードも表示されていて、「コピペして使ってください」などと大変ありがたいブログもあります。

ワードプレスでブログを作成している方に、
利用したい見出しのコードをコピーして利用する方法を説明します。

(コピー可の表示がない場合は安易にコピーして使用してはいけません)

CSSにコードをコピーする手順

よく使われている見出しですが、次のように飾りたいと思います。

検索すると

「次のようにコードをコピペして使ってください」とあり、b1という名前でコードが示されています。

b1 {
padding: 0.25em 0.5em;/*上下 左右の余白*/
color: #494949;/*文字色*/
background: transparent;/*背景透明に*/
border-left: solid 5px #7db4e6;/*左線*/
}

① まず、コードをコピーして下さい。

② ダッシュボードの「外観」ー「カスタマイズ」をクリック。

③ 「追加CSS」をクリック。

④ コピーしたCSSコードを張り付けます。

⑤ 投稿画面に戻って「テキスト」の画面で次のように入力してください。

<b1>  提出先の確認  </b1>

”b1 ”はさきほどコピーしたCSSの名前です。
”提出先の確認” は見出しの文字です。

見出しの文字を大きくしたい場合は、次のように文字を大きくするコードを<b1>  </b1>で挟めばOKです。

<b1><strong><span style=”font-size: 18pt;”>提出先の確認</span></strong></b1>

これで終了です。

確定給付年金から確定拠出年金への移行注意点

皆さんの会社の退職金制度はどのようになっていますでしょうか?

当社では、最近確定給付年金から確定拠出年金へ移行しました。

簡単に済むはずだったのですが、最後の最後で大もめになり、大変なことに。。

これから確定拠出年金へ移行される担当者の手助けになればと思い記述しておきます。

確定給付企業年金とは

確定給付企業年金とは、文字通り「給付」が「確定」している「企業年金」です。
従業員が受け取る「給付額」があらかじめ約束されていて、
会社が運用の責任を負い、運用結果が悪ければ、企業が不足分を穴埋めします。DBとも呼ばれます。

確定拠出年金(企業型)とは

会社が「拠出」する掛金が「確定」している「企業年金」制度です。

加入者(従業員)が運用の責任を負い、運用結果によって給付額(年金資産)が変動します。DCとも呼ばれます。

2012年1月からマッチング拠出制度が開始され、会社掛金に加えて、企業型DC加入者が自ら掛金を拠出することもできるようになりました。

確定給付年金(DB)から確定拠出年金(DC)への移行は、従業員からすればこれまで決まった退職金額が受け取れていたのが、自分で運用することになり、運用次第ではこれまでの退職金を下回ることにもなりかねない大きな問題です。

想定利回りがもめる原因に

想定利回りとは
退職金制度を、確定給付年金制度(DB)から確定拠出年金制度(DC)に移行する場合、DCにおいて60歳で給付できる額が旧制度であるDBと同水準の給付額となるように資産形成するために必要となる運用利回りのことをいいます。

要するに、60歳で旧制度と同じだけ退職金をもらうには、何パーセントで運用する必要があるかということです。

高い率であれば、会社が負担する掛金は少なくなりますが、個人の運用ノルマは大きくなります。

低い率にすれば、社員(運用者)は運用ノルマが軽くなるので楽になりますが、会社としては毎月の掛金が高くなりますので経営を圧迫します。

DC法では想定利回りは規約で規定すべき事項とはされていないため、会社で決めればよいのですが、まず従業員ともめる要素でもあります。

23歳で入社をして、60歳で2千万円の退職金ももらうとします。

毎月の拠出額が変わらないとした場合、毎月いくらの拠出金額になるでしょうか?

(*拠出額は、昇進などにより変わるように設定されることが多いですが、ここでは入社から退社まで一定額とします)

想定利回りを2%の場合と4%の場合で比べてみます。

拠出額を計算する

将来の目標額(将来価値)のために、いくらづつ積み立てればよいかはエクセル関数のPMT関数を使用します。

参照
PMT関数を使って目標貯蓄額のため今からいくらづつ積み立てればよいかを計算する

60歳で2千万円の退職金を支給するには、毎月いくらずつ拠出すればよいかを計算します。

PMT関数の指定項目を次のように指定します。

利率:2%(想定利回り)
拠出額が月額で計算のため,利率も月利で指定 。0.02/12

期間:23歳から60歳までの月数。444ヶ月

現在価値:現在保有している金額。これから拠出するので現在は0

将来価値:60歳で支払う退職金額を指定。ここでは2千万円

エクセルのセルに、次のように入力して下さい
=PMT( 0.02/12, 444, 0, 20000000, 1 )

それぞれの意味は次の通りです
=PMT(利率,期間,現在価値,将来価値,支払期日)

すると答えは
-30401
となります(マイナスがついているのは、支払うからです)

毎月30,401円の拠出額が必要となります。

 

想定利回りを4%で計算してみます。

エクセルのセルに、次のように入力して下さい
=PMT( 0.04/12, 444, 0, 20000000, 1 )

すると答えは
-19646
となります。

月19,646円の拠出額が必要となります。

 

社員からすれば、想定利回りが2%であれば、毎月30,401円の拠出金額がもらえますが、想定利回り4%であれば毎月19,646円しかもらえません。
当然想定利回りは2%を要求しますね。

反対に会社は費用は少ない方がよいですから想定利回りは4%としたいところです。

 

ドルコスト平均法

想定利回りは4%で決まりました。
従業員からは毎年4%で運用するのは不可能だとの意見も出ました。

ここで、社員に理解して頂きたいのは「毎年4%で運用する必要はない」ということです。

ここで肝になるのが、「ドルコスト平均法」という考え方です。
ドルコスト平均法とは、投資において、定期的に一定金額分を買っていく方法です。

毎回購入額が決まっているため、高値のときは少ししか買わず、安値のときに多く買うことができます。

確定拠出年金は、会社が拠出する金額は毎月一定額であり、全額が投資に回りますので、ドルコスト平均法の投資となります。
(元本確保型といってリスクがない商品も含め)

確定拠出年金(企業型)は、会社が揃えた投資信託商品の中からリスク許容度に応じて商品を選択していきます。

たとえば日経平均株価に連動する商品で運用している場合、株が下がればその分多く購入することになります。

たとえば、拠出額1万円で、1単位1万円の商品を購入しているとします。
株価が下がり、1単位9千円となった場合、同じ拠出額1万円で1.11単位を購入することができます。

その後、株価が上昇して9千円から1万円に戻ったとします。
すると手持ちの評価額は1,11×10,000=11,100円となります。

株価が1単位1万円から下落せずに、そのまま1万円であった場合には手持ちの評価額は1×10,000=10,000円です。

よって、株価が下落した時に購入したため1,100円評価額が上がることになります。(あくまでも、株価が戻ることが前提としてですが)

ここで次の表をご覧ください。

投資パターンがA~Eまで5つのパターンがあります。
20年につき、毎年10,000円づつ投資をしていくとします。

どのパターンも、1年目の株価は10,000円であり、20年目の株価は21,068円で終了するとします。

このうち、最終回(20年目)において、どれが一番収益が高いと思いますか?

Aパターンは、毎年4%づつ一定で上昇していくパターンですが、この中で何番目の収益になるかわかりますでしょうか?

20年目の収益を次のように計算します。

20年目の収益額=20年目の投資評価額-20年間の投資拠出額累計

*20年目の投資評価額=20年間の累積購入数×20年目の株価
*20年間の投資拠出額累計=10,000円×20回=200,000円

すると答えは次のようになります。

A:97,781円
B:250,922円
C:198,893円
D:112,769円
E:94,259円

一定の率で上昇していくAは4番目となり収益パターンとしては優れていないことがわかります。

株価は、上昇と下降を繰り返していきます。

運用成績としては、運用利回りが同率で上昇していくよりも、ある程度資産が蓄積された後にどれだけ上昇の波に乗るかがポイントとなります。

要するに、拠出金額の累計が少ないうちは、いくら株価が上昇しても(下降しても)あまり影響はありません。

しかし、ある程度拠出額が累積してきた場合には、少しの変動が大きな影響を持つことになります。

株価は上下に変動するので、どうせ下落するなら手持ちが少ないうちに、上昇するなら手持ちが多いときに・・ということですね。

だから、金額が多い年長者はなるべくリスクのない商品を選択すべきであり、手持ちの金額が少ない若年層は積極的に運用していくことが基本となります。

年金資産配分方法で大もめに

最後に、年金資産の配分です。
ここで、思わぬ落とし穴があり社員と大もめになりました。

確定給付年金で財務部の投資成果もあり、年金資産は退職金要支給額を上回る金額が残りました。

退職金要支給額とは、今社員全員が自己都合で退職した場合の退職金総額をいいます。

それを年金資産が上回っているのですから、年金資産から全員に退職金を支払っても、なお余りがあることを意味します。

よって、社員には確定給付年金(DB)から確定拠出年金(DC)に移行の際には、現在の退職金(DB)に余剰金を上乗せしてDC口座へ入金することを約束しました。

しかし、実際に年金資産が各人のDC口座へ入金する金額が確定した一覧を見てぞっとしました。

なんと、若年層のDC金額はDB金額を下回っており、老年層のDC金額はDB金額をはるかに超えて入金されてしまいました。

では、多く入金された老年層から少なく入金された若年層に資金を回せばよいと思いますが、これはDC法に則り行っているためできないとのことです。

DC法では、次のように計算されます。

①加入者等に最低積立基準額(MFR)までを分配(法令要件)
②上記①の分配後の残余資産があれば、加入者等の要支給額-MFRを上限に当該比(要支給額比)で分配
③上記②の分配後の残余資産に対して、加入者等の要支給額で分配

まず、①で各人に最低積立基準額を分配します。

年金担当者はここを注意して下さい。

最低積立基準額とは、DBの退職金要支給額ではないということです。
これは、DC法のルールに従って計算されます。

実際にこの金額がわかるのは、年金資産を分配するときに年金数理計算というものをして、はじめてわかります。
(コンサルティング料を支払って計算してもらいます)

当社の若年層のDB退職金は、若年層に手厚くなっていたようで最低積立基準額を大きく上回っていました。
よって、最低積立基準額はDBの退職金を大きく下回ることになってしまいました。

これに②の余剰金の分配が加算されます。

しかし、この配分方法も、退職金要支給額割合で按分されるので、当然金額の大きい年配者に多く配分されることになります。

そして③でさらにそれでも余剰金がある場合には取り決めた方法で分配されます。この③に限っては会社との取り決めになります。
当社は、取り決めにより退職金要支給額割合で分配することを決めましたので、ここでも金額の多い年配者に多く分配されることになりました。

よって、若年層と老年層のDC分配額が大きく差が出てしまい、若年層は旧退職金制度における退職金額にも届かず、老年層ははるかに超えた金額となってしまいました。

老年層からは喜びの声がありましたが、担当者としては、その喜びの声は若年層からの不満を消せるはずもありません。

そこで再度監事会社に相談したところ、DC規約を変更して、金額がマイナスになった人にだけ拠出金に上乗せして拠出額を決められることがわかりました。

これは、4年均等または8年均等のどちらかということらしいので、4年均等で拠出額に上乗せして支払うことに決定しました。

たとえば、DBと比較してDC入金額が40万円少ない者には、毎年10万円づつ4年間にわたり拠出金額に上乗せして支払っていきます。

それでようやく不満の声は収まりました。

これから確定給付年金から確定拠出年金に移行される方は、想定利回りと最低積立基準額には十分注意をして下さいね。

スムーズに移行できることをお祈りします。

 

アフィリエイト広告を横並びにするには

アフィリエイト登録をして広告をサイトに表示させるには、

<a href=”http://×××××”><img src=”http://×××××”  border=”0″ /></a>

のようなコードを「テキスト」画面の表示させてたい位置に貼り付けます。

 

注意:コードを貼り付けるのは「ビジュアル」画面ではなく「テキスト」画面ですのでお間違えなく。

「ビジュアル」画面にコードを貼り付けても、ただコードが表示されるだけで広告バナーは表示されません。

 

画面には次のように表示されます。

 

この広告の右側が空いてしまっていますので、そこに別のアフィリエイト広告を貼り付けたいと思います。

2つのアフィリエイト広告を次のように続けて貼り付けてみます。

<a href=”http://×××××”><img src=”http://×××××”  border=”0″ /></a>

<a href=”http://×××××”><img src=”http://×××××”  border=”0″ /></a>

 

すると次のように縦に並んでしまいます。

 

これを横並びにするには次のようにします。

<table>
<tr>
<td> 広告1のコード </td>
<td>   広告2のコード </td>
<tr>
</table>

 

横並びになりました!

 

画像を横ならびにする

画像を横並びにしてかっこよくしたい。
でも、カスタマイズするほど詳しくないし・・

あるんですね、やっぱり!
画像を横並びにするプラグイン。shortcodes by Angie Makesと言うんです。

こんな感じにしたいと思います。

 

ワードプレスのダッシュボードから
「プラグイン」「新規追加」をクリックして下さい。

プラグイン追加画面が現れますので、
右上の検索で「shortcodes by Angie Makes」と入力して下さい。

shortcodes by Angie Makesプラグインが現れますので「今すぐインストール」をクリック

 

「今すぐインストール」が「有効化」に表示が変わりますので、ボタンをクリック

 

これでプラグインがインストールされました。

「投稿」「新規追加」画面で、のボタンを探して
右の▼をクリック。

 

すると、プルダウンメニューが現れます。

今回は2枚の画像を横並びにしたいので、
「Columns」「1/2+1/2」を選択して下さい。

 

 

すると、次のような文字が現れます。

 

その中に「Sample Content」という文字があるので、それをドラッグして、
「メディアを追加」をクリック。

 

「メディアライブラリ」の中から張り付けたい画像を選ぶ。

 

すると、次のようにさきほどの「Sample Content」という文字のところに
画像が張り付けられます。

もう1つ「Sample Content」という文字がありますので、
そこをドラックして、「メディアを追加」をクリック。

 

「メディアライブラリ」から画像を選択する

 

次のように画像が縦に並んで表示されます。

 

これで完成!

プレビューは次のように横になっていますので、大丈夫です。