About this site

VBA / VBScript / JScript / Javascript / HTML / CSSに関連することを思いつくままに、
興味のあること、気になったこと、ちょっとした手間を省くために作ったもの、を書いていきます。
気が向いたら、で不定期更新中。

上のメニューから分野別の記事をご覧ください。

Posts on this page


Recent Posts


February 28, 2016

Excel/VBA Application.Runメソッドで使うコマンドマクロ(コントロールID)を調べる

VBAにはApplication.Runメソッドがあり、リボンで表示される任意のコマンドを簡単に実行できます。この引数にあたるユーザーインターフェースコントロールID(コマンドマクロと呼ばれていたり、MSDNの説明では引数としては単にMacroNameとされている)は以下の場所から一覧をダウンロードできます。

Office Fluent User Interface Control Identifiers
https://www.microsoft.com/en-us/search/result.aspx?q=Office+Fluent+User+Interface+Control+Identifiers&form=dlc

なぜか公式の名前(UI Control ID)とWeb上での呼称(コマンドマクロ)が一致していないのようなので探すのに手間取りましたのでメモ。

大量のIDが記載されており、リボンから選択できる任意の機能を実行できるようになっていますね。

February 17, 2016

Hugo shortcodeでCSVをtableに出力する

Hugoではmarkdownの文法をshortcodeという仕組みを使って比較的自由に拡張することができます。既定のshortcodeとしては、youtube, twitter, figureなどがあり、以下のように使用できます

https://gohugo.io/extras/shortcodes/

{{< figure src="/media/spf13.jpg" title="Steve Francia" >}}
{{< youtube w7Ft2ymGmfc >}}
{{< tweet 666616452582129664 >}}

この仕組みを使うことで、csvファイルのパスを渡し、tableタグの表に変換することができそうです。

<table>
  <tbody>
  {{ with .Get "src" }}
    {{ range $i, $r := getCSV "," . }}
    <tr>
      {{ if eq $i 0}} {{range $r}}  <th> {{ . }} </th>{{ end }}
      {{ else }} {{range $r}}  <td> {{ . }} </td>{{ end }}
      {{ end }}
    </tr>
    {{ end }}
  {{ end }}
  </tbody>
</table>
このようにlayout/shortcodesフォルダ内にshortcodeを用意しておき、markdown文書では次のように記載し、csvのパスを指定して呼び出します。

{{ <csv src="/static/csv/data.csv"> }}
テンプレートを使った方法と比べると柔軟にデータを表示できる点で使い道が広がりそうです。shortcodeはパラメータを好きな数だけ与えて、自由にhtmlでデザインしたコードを出力できるため、文書中に他のwebサービスを埋め込みたい場合にも活躍してくれそうですね。例えば、csvファイルやJSONを渡してグラフを描画するといったことも可能でしょう(この場合は、ローカルのデータをHugoにハードコードさせて、それをクライアントサイドでJavaScript等でグラフにするか、shortcodeでグラフの作成まですべてやってしまうか、手法は色々とありえます。)

February 15, 2016

Excel VBAで配列を渡してグラフを作成する

VBAの計算結果を手軽にグラフで確認するための関数。データを渡すと棒グラフを作成します。ChartTypeなどを調整すれば好みのグラフを生成する関数に応用できます。

Sub plotDataColumn(ByRef XData As Variant, ByRef YData As Variant, Optional XAxisTitle As String = "X Axis", Optional YAxisTitle As String = "Y Axis", Optional ChartTitle As String = "Chart Title", Optional GapWidth As Long = 10)
    With Charts.Add
        .ChartType = xlColumnClustered
        
        With .SeriesCollection.NewSeries
            .Values = YData
            .XValues = XData
        End With
        
        .ChartGroups(1).GapWidth = GapWidth
        
        .HasTitle = True
        .ChartTitle.Text = ChartTitle
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XAxisTitle
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YAxisTitle
    End With
End Sub

Excel VBAで仕組債の期待リターンを計算する

仕組債は、通常の証券への投資と異なり、リターンを複数のルールの組み合わせで決定しているため、その分布も独特なものとなります。原資産や参照する資産のリターンは、左右対称の分布が期待される場合であっても、仕組債のリターンは非対称な複雑な形を取ります。

ここでは、マネックス証券の提供する日経平均連動型仕組債を参考に仕組債の理論価格をモンテカルロシミュレーションにより計算しました。

まず、対象とする証券は以下のものとしました。
  • 原則、3か月に1回利払いがある
  • 原則、2年後に償還される
  • 原則、額面で償還される
  • 但し、3か月に1回の早期償還判定日に参照株価が早期償還判定水準以上であれば、その時点で額面が償還され以降の利払いはない
  • 但し、参照株価がノックイン判定水準を一度でも下回った場合は、参照株価に連動した額面が償還される
また、計算の前提として以下を想定しました。
  • 日経平均株価の年率期待リターンは5%
  • 日経平均株価の年率期待ボラティリティは30%
  • 次月の日経平均株価は前月の株価を平均とする正規分布となる
  • 株価の計算間隔は月次
  • サブモデルの試行回数は1000回
  • モデル全体の試行回数は1000回
  • 割引率(あるいは投資家の期待収益率)は5%
  • 仕組債のクーポンレートは年率4.5%
  • 早期償還判定水準は105%
  • ノックイン判定水準は75%
計算された仕組債の割引現在価値は非対称な分布となります。大半の場合では早期償還される一方で、ノックイン判定水準である75%付近を中心とした釣鐘状分布を取り、参照株価の動き次第では投資額の50%を失う場合もありうる、という結果になりました。


割引現在価値の平均値は93となり、期待リターン5%の投資家にとっては、この仕組債はNPVマイナスの損な投資であるといえるでしょう(93の価値のものに100の値段がついている)。

もっとも、参照株価の動きにどのような前提を置くのかでリターンは異なってくるため、この証券が一方的に販売側に有利に設計されている、とまではいえません。また、この計算で置いた前提はかなり疑問のあるもので、例えば、株価の分布の前提は正しくは、「月次リターンがファットテール分布となる」とすべきでしょうし、分布のパラメータも正しく見積もる必要があるはずです。さらに試行回数も結果の収束を確認して最適化すべきでしょうし、計算間隔も月次でいいのか、は疑問点です。

とはいえ、モンテカルロシミュレーションを用いることで、直感的には理解しにくい仕組債の期待リターンを可視化することができ、そもそもどのような性質の商品なのかが分かりやすくなることは間違いないですね。

計算に用いたコード:
Sub derivative()
    Dim Price As Double, AnnualVolatility As Double, AnnualReturn As Double
    Dim MonthlyVolatility As Double, MonthlyReturn As Double
    Dim CouponRate As Double, QuarterlyCouponRate As Double
    Dim KnockOut As Double, KnockIn As Double

    Dim Iteration As Integer
    
    Dim DiscountRate As Double
    
    Dim ExpectedValue(0 To 1000) As Double
    
    Dim PriceArray(0 To 24) As Double
    Dim PayoffArray(0 To 24) As Double

    Price = 100
    AnnualVolatility = 0.3
    AnnualReturn = 0.05
    
    CouponRate = 0.045
    
    KnockOut = 1.05
    KnockIn = 0.75
    
    MonthlyVolatility = AnnualVolatility / Sqr(12)
    MonthlyReturn = (1 + AnnualReturn) ^ (1 / 12) - 1
    
    QuarterlyCouponRate = CouponRate / 4
    
    Iteration = 10000
    
    DiscountRate = 0.05
    
    Math.Randomize
    
    For j = 0 To 1000
        For i = 0 To 24
            PayoffArray(i) = 0
        Next
        
        PriceArray(0) = Price
        For i = 1 To 24
            ' creat new price
            PriceArray(i) = WorksheetFunction.Norm_Inv(Math.Rnd(), PriceArray(i - 1) * (1 + MonthlyReturn), PriceArray(i - 1) * MonthlyVolatility)
            
            ' on the end of quarter
            If i Mod 3 = 0 Then
                ' pay off interest
                PayoffArray(i) = PayoffArray(i) + Price * QuarterlyCouponRate
                
                ' If price is more than knock out level
                If (PriceArray(i) > Price * KnockOut) And (i <> 24) Then
                    ' Payoff principle and exit loop
                    PayoffArray(i) = PayoffArray(i) + Price
                    Exit For
                    
                ElseIf i = 24 Then
                    If WorksheetFunction.Min(PriceArray) < Price * KnockIn Then
                        PayoffArray(i) = PayoffArray(i) + PriceArray(i)
                    Else
                        PayoffArray(i) = PayoffArray(i) + Price
                    End If
                End If
            End If
        Next
        
        ExpectedValue(j) = (WorksheetFunction.NPV((1 + DiscountRate) ^ (1 / 12) - 1, PayoffArray)) * ((1 + DiscountRate) ^ (1 / 12))
    Next
    
    Range("a1:a1001") = WorksheetFunction.Transpose(ExpectedValue)
End Sub

Excel VBAでモンテカルロシミュレーションを行い円周率を計算する

VBAで乱数を生成し、半径の中に存在するケースの数を数え上げることで円周率を求めるコード。
Sub pi()
    Dim xi As Double, yi As Double
    Dim radius As Double
    radius = 10
    
    Dim Iteration As Integer
    Iteration = 10000
    
    Dim CountInRadius As Integer
    CountInRadius = 0
    
    For i = 1 To Iteration
        xi = Math.Rnd * radius
        yi = Math.Rnd * radius
        
        If Math.Sqr(xi ^ 2 + yi ^ 2) < radius Then
            CountInRadius = CountInRadius + 1
        End If
    Next
    
    MsgBox "pi = " & CountInRadius / Iteration * 4
End Sub

Excel VBAで数列を生成する

VBAでデータをプロットする際に、軸の情報として使用するために適当な数列を生成するユーザー定義関数を作成しました。

createArray: 数列の生成
repeatArray: 与えられた数列を繰り返した数列を生成
combineArray: 与えられた数列を繋げた数列を生成

createArray(1,5,TRUE,,,) = {1,2,3,4,5}
createArray(1,5,,TRUE,,3) = {1,2.5,5}


Function createArray(beginValue As Variant, Optional endValue As Variant, Optional IntervalMode As Boolean = True, Optional LengthMode As Boolean = False, Optional Param As Variant)
    Dim tempArray As Variant
    Dim Interval As Variant, Length As Variant
    
    If IsMissing(endValue) Then endValue = beginValue
    
    If IntervalMode Then
        If IsMissing(Param) Then Param = 1
        Interval = Param
        Length = WorksheetFunction.Floor((endValue - beginValue) / Interval, 1) + 1
    End If
    
    If LengthMode Then
        If IsMissing(Param) Then Param = WorksheetFunction.Floor(endValue - beginValue, 1)
        Length = Param
        Interval = (endValue - beginValue) / (Length - 1)
    End If
        
    ReDim tempArray(0 To Length - 1)
    
    For i = 0 To Length - 1
        tempArray(i) = beginValue + Interval * i
    Next

    createArray = tempArray
End Function

Function repeatArray(origArray As Variant, Iteration As Integer)
    Dim tempArray As Variant
    Dim Length As Long
    
    Length = UBound(origArray) - LBound(origArray) + 1
    If Iteration < 1 Then Iteration = 1
    ReDim tempArray(0 To Length * Iteration - 1)
    
    For i = 0 To Length * Iteration - 1
        tempArray(i) = origArray(i Mod Length)
    Next
    
    repeatArray = tempArray
End Function

Function combineArray(Array1 As Variant, Array2 As Variant)
    Dim tempArray As Variant
    Dim Length1 As Long, Length2 As Long
    
    Length1 = UBound(Array1) - LBound(Array1)
    Length2 = UBound(Array2) - LBound(Array2)
    ReDim tempArray(0 To Length1 + Length2 - 1)
    
    For i = 0 To Length1 - 1
        tempArray(i) = Array1(i)
    Next
    
    For i = Length1 To Length1 + Length2 - 1
        tempArray(i) = Array2(i - Length1)
    Next
    
    combineArray = tempArray
End Function

February 1, 2016

Excelのグラフに表示するデータやタイトルを動的に変更したい

エクセルのグラフは、通常、セル範囲を指定してその範囲のデータを描画させるわけですが、ユーザーの操作に対応して変更可能なグラフを作成することもできます。VBAを使用する必要はなく、ワークシート関数だけで作成できてしまいます。

まず、下準備としてデータ範囲の名前を設定しましょう。プロットしたいデータの名前がA~Dまであるとして、その名前のリストを用意し、そのセル範囲の名前を、例えばlist_seriesとします。

次に、プロットしたいデータを選択させる仕組みを作ります。フォーム部品を使っても良いのですが、最もお手軽な方法は入力規則を使う方法でしょう。

そして、選択されたデータを参照するdynamic rangeを作成します。ここでは、MATCH関数でlist_seriesの何番目か、を取り出し、OFFSET関数を使って対応するデータ範囲を取り出し、graph_dataと名付けました。

なお、このdynamic rangeを以下のようにひと工夫すると入力されたデータちょうどの大きさのセル範囲を指定できます(但し、データは空白セルなく、ずっと詰まって並んでいる、こと)。

ここで、適当なグラフを作成し、動的に変更したいデータ系列を選択し、以下のようにワークブック参照を付けた状態でデータ範囲をdynamic_range.xlsx!graph_dataと設定します。
(なお、理由は不明ですが、セル範囲に定義された名前のみ、をgraph_dataのように指定することは出来ません。参照された名前は存在しない、といったエラーとなります。ワークシートあるいはワークブック参照を付けて入力する必要があります。)

この状態で、先ほど設定したデータ選択セルを変更すると、動的にグラフのデータが変更されます。

さらに、グラフのタイトルボックスを選択し、数式バーにセルを指定すると、セルの内容がグラフタイトルに表示されます。
(なお、理由は不明ですが、タイトルバーにはセル参照しか指定できず、式やワークシート関数、定義された名前を使うことは出来ません。使いたい場合は、一度適当なセルで計算を行い、そのセルを参照するという美しくない解決法になるようです。)

発展形としては、折れ線グラフではグラフの横軸方向についても動的にしてやり、適当な範囲を選んでプロットする、といったことをしてやれば、時系列のデータを表示するには便利になるでしょう。ついでに、フォーム部品のスクロールバーを用いれば、かなり自由にデータをプロットできる動的なグラフになります。

参考ファイル(dynamic_chart.xlsx)

コマンドプロンプトでロケールに左右されずにファイル名に日時を設定したい

バッチファイルを配布するときに厄介なのが、%DATE%や%TIME%といった変数はロケールによって出力が変わってしまう点です。色々と解決法は議論されているようなのですが、以下の解決法は別のファイルを必要とせず、バッチファイルの一部を変更するだけで実装可能で最も優れた解決案のようです。

XP以降(?)ではWSHスクリプトをバッチファイルに混在して記載できるため、JScript側で日時を取得して処理し、それをシェルスクリプトに返してやることで、ロケールに依存しない日時の文字列を得ることができるようです。

DATE/TIMEコマンドやDATE/TIME環境変数の形式は地域に依存します。

「地域と言語のオプション」の影響を受けないようにするには、DATE/TIMEコマンドやDATE/TIME環境変数を使わず、WSHスクリプトを使うのが一番です。

@if(0)==(0) ECHO OFF
FOR /F %%0 IN ('CScript.exe //NoLogo //E:JScript "%~f0"') DO SET yyyymmddhhmmss=%%0
ECHO %yyyymmddhhmmss%
GOTO :EOF
@end
var d=new Date();
WScript.Echo(d.getFullYear()*10000000000+(d.getMonth()+1)*100000000+d.getDate()*1000000+d.getHours()*10000+d.getMinutes()*100+d.getSeconds());
http://scripting.cocolog-nifty.com/blog/2007/01/datetimedatetim_061c.html
では、この仕組みがどのように機能するか、というところなのですが、とても面白くて、こちらの記事に解説されています。上手い仕組みがあるものですね。
BATとWSHのコードを1ファイルに混在させるためのshebang記法(複雑なバッチを1ファイルで実現)
http://computer-technology.hateblo.jp/entry/20131025/p1

Hugoで使えるMarkdownの文法について

Hugoの公式ページを読んでいて少し混乱したのでメモとして残します。
公式ページいわく、

The source nitro.md file is converted to HTML by the excellent Blackfriday Markdown processor, which supports extended features found in the popular GitHub Flavored Markdown.
https://gohugo.io/content/example/

とのことで、GitHubで使えるのと同じMarkdownが使えんだーと思ったのですが、それは間違いで、正しくは、「GitHubと似たような機能を使えるBlackfridayエンジンを使っている」ということ。

なので、以下のページにてBlackfridayの記法を調べ、それに沿って記事を記載する必要があるようです。例えば、テーブルの記法が微妙に違いますね。

https://github.com/russross/blackfriday#extensions

January 27, 2016

Hugoが使える場合、使えない場合、使わなくて良い場合

Hugoはstaticなwebsite generatorです。ローカルマシン上に用意したファイルを、指定した規則に基づいてhtmlに変換して出力するソフトウエアです。サイト構築を行ってみて思う所を簡単にメモします。

更新頻度
リアルタイムで更新したい、日に何度も更新がある、アップロード頻度を上げることは好ましくない、という場合にはHugoは適切な手段とは言えないでしょう。コンテンツの一部にスクリプトを使って外部サービスからデータを呼び出すことで動的なページを作成することは可能ですが、それならばもともと動的なシステムを使えば良いように思われます。

サイトの規模
サイトの規模があまりに大きくなってくると、ローカルでのコンテンツのレンダリング時間が長くなり
ます。また差分の規模も大きくなればアップロードする時間が長くなるため、動的にコンテンツを生成するほうがストレスがないでしょう。

コンテンツの複雑さ
仮にコンテンツがブログのように時系列の1カテゴリのデータのみで構成されている場合は、既存のブログサービスに勝るものはないように思います。一方で、サイトの部分部分で異なった機能を持たせたい場合、例えば、サイトの一部はブログだが、他の部分は静的なページで、さらに他の部分にはメインのブログとは異なるが時系列の情報がある場合(例えば、テーマAのブログとテーマBの進捗報告が同じサイトに共存している場合)、既存のブログサービスやCMSで扱うのは困難になってきます。もちろん、WordpressやMoveble Typeではかなり柔軟にウェブページを作成できるのですが、作成者にしかわからないような絡み合った設定になってしまう点で課題が大きいように思います。それなら、希望の構造に合わせて柔軟にコーディングできるHugoのようなジェネレータに軍配が上がるでしょう(大規模なシステムなら、自前でphpを書いていけば良いのですが。)

セキュリティ
動的な製作環境の問題点の一つが、セキュリティアップデートと永久に付き合わないといけない点です。サーバー上にプログラムを配置する以上、常にセキュリティリスクにさらされているといえます。一方で、静的なサイトはサーバーそれ自体のセキュリティは依然として残るものの(例えば、ファイルそのものが書き換えられる)、プログラムに起因するリスクは根本的に取り除くことができ、管理の手間を大幅に減らすことができます。