ExcelとPython(とR)を連携させる – ExcelPython編

概要

  • ExcelPythonを使うとエクセルのビルドイン関数のようにpythonの関数を呼び出すことができる
  • 当然numpyにも対応。ただしpythonのクラスには対応していない(?)
  • VBAを1行も書く必要がない。xlwingsではVBAに、呼び出したいpython関数を書かなければならなかった。
  • VBAを駆逐可能という代物

ExcelPythonのインストール

まずは下記サイトダウンロードし、インストールします。今回はv2.0.8を使用しています。

ExcelPython – Write Excel UDFs in Python!
http://ericremoreynolds.github.io/excelpython/

その後エクセルを起動させるとExcelPythonのリボンが追加されています。
150516_1

Pythonコード

ここでは前回の記事で使用した関数を流用します。
ポイントとしてはハイライトしていますが、xlpythonをロードするのと、エクセルから呼び出し可能にしたい関数の前に@xlfuncを書くことです。
あとはこれを使用したいエクセルシートと同階層に保存します。

簡単に説明するとgetDataFromQuandlは指定したティッカーのデータをQuandl経由でロードします。
garchはGARCH(1,1)のボラティリティを返します。

Excelでpythonコードをリンクさせる

といってもそこまで難しいことはなく

150516_2

  1. エクセルシートをマクロ使用可な形式(xlsm)で保存する。その際に先ほど保存した.pyファイルと同じ名前にする必要があります。
  2. ExcelPythonリボンにある「Setup ExcelPython」をクリック
  3. 「Import Python UDFs」をクリック

通常何も手を加えていないエクセルの場合以下の様なエラーが出ると思います。

150516_3

その際は
「Excelのオプション」→「セキュリティセンター」→「セキュリティセンターの設定」→「マクロの設定」→「VBAプロジェクト オブジェクト モデルへのアクセスを信頼する」のチェックをオンにします。

「Import Python UDFs」のクリックが完了すると、先ほど@xlfuncを指定した関数がエクセルから呼び出すことが可能です。

ExcelPythonを使ってみる

まずはaddNumを使ってみましょう。関数を使用しようとするとちゃんとサジェストにも表示されるようになります。
150516_4

Ctrl+Shift+Aの引数表示も対応しています。
150516_5

ということでめでたく計算することが出来ました。
150516_6

ExcelPythonを使ってみる – 返り値が行列編

どうやらExcelPythonはPandasのDataFrameには対応していないので行列に変換する必要があります。
その変換はto_records()で行っています。こうすることでPandasのindexも含めて返すことが可能になります。

先ほど定義したgetDataFromQuandlを使うと以下。1列目の日付は見やすいようにエクセル上でフォーマットを変更しています。

150516_7

ExcelPythonを使ってみる – インプットが行列編

エクセルからの入力が行列の場合、@xlargを使ってその引数のサイズを指定する必要があります。

縦ベクトルで返したいのでPython上で返すときにサイズを変換しています。

150516_8

ということでGARCHボラティリティの計算もすることができました。

感想

xlwingsとの比較という点ですと、
長所

  • マクロを書かなくて良い
  • ビルドイン関数のように呼び出すことができる

短所

  • Pandasに対応していない
  • 行列を返す場合の処理が面倒(予めエクセル上の範囲を指定する必要がある)

という感じでしょうか。

参考

ExcelPython – Write Excel UDFs in Python!
http://ericremoreynolds.github.io/excelpython/

excelpython/docs at master · ericremoreynolds/excelpython · GitHub
https://github.com/ericremoreynolds/excelpython/tree/master/docs

xlwingsを使ってExcelとPython(とR)を連携させる

概要

  • xlwingsというpythonパッケージを使うことでExcelからPython関数を呼ぶ
  • さらにRのPypeRパッケージを使うことでPythonからR関数を呼ぶ
  • Quandlから日経平均を取得し、そこからGarchモデルを計算してみる

xlwingsを用いたPythonとExcelの連携

公式HPとドキュメントが非常にわかりやすいのでそこに従うだけで連携は簡単にできます。(URLは記事一番下にあります)
xlwingsのダウンロードはpipがある環境では

で行うことができます。(この記事では0.3.5を使用しています。)
今使っているエクセルシートとPythonをリンクさせるには、1つモジュールをロードさせる必要があります。

エクセルのVBエディター(alt + F11)から「ファイル」→「ファイルのインポート」→「xlwings.bas」を選択します。
xlwings.basの保存場所はpython上で

とすることで確認できます。xlwings.basをインポートすることで標準モジュール内にxlwingsというモジュールが追加されます。

使ってみる

Pythonコード

コードを使う際にはQuandlのトークンを自分のものに置き換えてくださいね。
pypeRの使い方に関しては手前味噌ですがhttp://nekopuni.holy.jp/?p=1345に書いています。

VBA

VBAに関しては適当なモジュールを追加し、そこに以下のように記述しました。

Excel

適当に以下の様な感じのシートを作ります。
Generateボタンには上述したGarchマクロが割り当てられています。
このボタンをクリックするとGarchマクロからPython上のxl_getDataFromQuandl()関数が実行され、
日経平均とGarchボラティリティが返されます。
excel_ann

xlwingsはnumpyやpandasに対応しているので日付の処理などもスムーズに行なうことができそうです。
A列に表示されている日付はQuandlから返されたPandasをそのままExcelに貼り付けていますが、ちゃんとExcel形式の日付に変換されていますね。

これを使えば例えばRの機械学習モジュールなどもPython経由で呼び出すことが出来ますね。
夢が広がりングです。

参考

xlwings – Replace Excel VBA with Python!
http://xlwings.org/

xlwings – Make Excel Fly!
https://media.readthedocs.org/pdf/fzumstein/latest/fzumstein.pdf

Ctrl + Shift + Enterを使いこなす

概要

・意外と知られていない(自分の周りだけかもしれないが)エクセルの行列計算を使った計算について

使用例1: 条件にあう数値だけ合計する

140215_1

なんでも良いんですが例えば上図のようなデータがあったとして
「2000年より前のSalesを合計したい」
とする.そういう時は合計を表示したいセルに

と入力してCtrl + Shift + Enter
sum文の中のif文で,A列(年)が2000より小さかったら1,そうでなかったら0のフラグを立て,同時にそのフラグにB列のSalesをかけています.
条件付きのsumproductみたいなイメージですね.

使用例2: 条件にあう数値だけ平均する

averageにも同じ方法を用いると条件に満たない数値に0が代入されているため,求めたいものと異なる平均を計算してしまう.

同様に
「2000年より前のSalesの平均を計算したい」
とした場合は,以下のようになる.

計算するときにCtrl + Shift + Enterを押すのを忘れないように.
やっていることとしては,まずif文で2000年より前だったら1のフラグを立てます.ただしこの条件を満たさなかった場合はNAとします(0ではない).
average関数はNAを含むベクトルに対しては計算が出来ないので,iferror関数をつかってNAを空白セルへと置き換えます.
そして最後にaverage関数で囲ってあげることで平均を計算しています.

これからも快適なエクセルライフを!

Enjoy!!

隙間のあるデータで散布図を書く

概要

・データに隙間(空白)がある状態で散布図を書くと思うような図にならずハマる
・表示させたくないデータは=NA()を使って明示的に無視させる必要がある

単純にIF文を使い空白にするだけだとうまくいかない

以下のようにAとBの2つの系列があると致しまして,

01

例えば『Aが負の場合のペアのみで散布図を書きたい』とした場合,
ぱっと思いつくのは
A列の値を対象に

といった具合にA列が正だったら空白にしちゃえばよろしと思いますがこの状態で散布図をプロットさせると

Image

なぜか系列が2つに(^^;;;
データセットによっては上手く表示されるケースもあるみたいですが何とも

空白ではなく=NA()で明示的にエラーにしておく必要がある

で,どうやら散布図を書く際にサンプルを無視させるためには明示的にデータをエラーにしておく必要がありますということらしく
先ほどのエクセル関数の場合には

とする必要があるということですね.

02

10年分くらいのデイリーのデータで=NA()のやり方を知らなかったために該当する列を1つ1つ消していって処理した人なんてまあ居ないですよね普通( ;∀;)