タイトル

エクセルを使ったデータ分析の手引き

UKI

はじめに

トレードに関する情報は世の中に溢れています。例えばツイッターのタイムライン（TL）を見ても、アレが効く、コレが効くという話を見掛けます。しかし、一見してチャート上では効果的に見える指標でも、実際にデータ分析やバックテストを行ってみるとそのパフォーマンスの低さに愕然とすることが多々あります。ありふれた情報の中から本当に有効である情報を取捨選択する必要があるのです。自分自身の投資スタイルを確立するためにも、データ分析を始めてみてはいかがでしょうか。

データ分析といっても、統計ソフトや機械学習ライブラリなどの専門知識は必要ありません。エクセルさえ使えればものの10分で結果を得ることができます。TLで気になる話題が流れたらまず調べる、このような癖をつけることが大切だと思います。そうやって自分自身が使えるデータ、使えないデータを層別していくことでトレーディングスキルが向上していくのでしょう。


データ分析の手順

1.分析の目的や手順を明確化する
まずは以下のことを明確にしましょう。
・どのような事象を分析したいか
・どのような結果を期待しているか
・どのようなデータが必要か
・分析の期間はどれくらいか

2.ヒストリカルデータの収集
続いて分析に使うデータを集めます。これができれば分析の9割は終わったようなものです。普段からヒストリカルデータを扱うサイトを探し、ブックマークしておくことが大切です。ヒストリカルデータが手に入らないものは、APIを叩いたりスクレイピングして取得したデータをストレージしておく必要があります。このような収集コストの高いデータほど、エッジが存在する可能性があることは言うまでもないでしょう。

3.エクセルで分析する
データが揃ってしまえばそれらを加工したり時系列で照合したりするのはさほど難しくありません。以下、3つの事例を元に詳細な手順を説明していきます。


事例1.BTCの難易度調整と値動きの関係

1.分析の背景と目的
この分析の背景は、TLにて難易度調整に関するツイートを見掛けたからです。そもそも難易度調整前後に恣意的な動きを感じていたため、分析してみることにしました。あまり昔を分析しても仕方がないため分析期間は2017年以降と定めました。


2.ヒストリカルデータの収集

＜必要なデータ＞
(1)難易度調整のヒストリカルデータ
(2)上記の期間のBTC価格

(1)難易度調整のヒストリカルデータ
この分析の最もキーとなる作業は「難易度調整のヒストリカルデータ」を探す（もしくは作る）ことです。通常であればこのプロセスにかなりの時間を要するのですが、今回は「bitcoin mining difficulty」でググるとすぐに見つかりました（↓）。

出所：BTC.com（https://btc.com/stats/diff）

このデータをエクセルに貼り付けます。単純にコピペしてみましょう。以下のように選択してコピー（ctrl+c）（↓）。

そしてエクセルに貼り付けます（ctrl+v）。簡単に貼り付きます（↓）。

ここで必要なデータは、B列のBlock TimeとD列のChangeです。この2つを残してエクセルをきれいにしておきましょう。時系列順に分析するためBlock Timeで古い順に並べ替えしておきます。また2017年より前のデータは削除しておきます（↓）。


(2)BTC価格のヒストリカルデータ
まず取得するOHLCVの時間軸を決める必要があります。難易度調整は決まった時刻ではなくバラバラになっているため、少なくとも日足よりも短い時間足が望ましいです。ここでは1時間足を使うことにします。以下、BTCの価格データ取得先を簡単にまとめました。

＜日足データの入手方法＞
(a)CoinMarketCapのデータをコピペする
(b)BlockChainからCSVをダウンロードする

上記のサイトから簡単に入手できます。

＜分足や時間足データの入手方法＞
(a)各取引所のAPIを叩いて入手する
(b)CryptowatchのAPIを叩いて入手する（直近6000データのみ）

短い足のデータはAPIを叩いて自身で入手することが確実です。一度スキルを身に着けておけばその後は快適にデータ取得できるようになります。APIからのデータ取得方法はググればすぐに出てくるため説明は割愛します。

今回は、BitMEXのXBTUSDの1時間足のOHLCVを使うことにしました。以下、APIから取得したデータです。これでデータ収集は完了です（↓）。


3.エクセルの分析
手順様々な箇所から取得したデータでもっとも難しいことは、バラバラになっているデータの時系列を揃えることです。これを失敗してしまうと分析に未来のデータを使ってしまい、恐ろしく高いパフォーマンスが得られることがあります。また、時間軸を所定のリージョンの時刻で揃えることも必要です。今回のデータは全て日本時刻で揃えています。この時系列での照合は、エクセルの関数であるVLOOKUPを使うと簡単に行うことができます。

VLOOKUP(検索値, 範囲, 列番号, 検索の型)

扱ったことがない人にとって引数の意味が分かりづらいかもしれません。ここでは実際に使ってみましょう。

まず分かりやすく、難易度調整の直近価格を検索します。以下のようにE2セルに数式を入力します（↓）。

E2セル：=VLOOKUP(A2,N:O,2,TRUE)

第一引数のA2は検索する数値です。この場合、A2セルの時刻”2017/1/11 7:40:52”を検索します。
第二引数のN:Oは検索する範囲ですが、実際に検索する範囲は指定した範囲の第1列（この場合N列）になります。この検索対象となる列（N列）は昇順でソートしておく必要があります。
第三引数は戻り値の列番号を示します。この場合、N:O列（つまり2列）のうち、2番目のO列の値を返すことになります。
第四引数は、TRUE/FALSEの指定となり、FALSE指定の場合は検索値と完全に一致するものしか返しません（検索値がない場合はエラーを返す）。TRUE指定の場合、検索値がなかった場合にはエラーでなく検索値に最も近い値が選択されます。

上記に従ってE2セルに入力したこの関数の動作を説明すると、

・A2の値（2017/1/11 7:40:52）をN列で検索します。
・完全一致する値がN列にない場合、最も近い値が選択されます。
・戻り値は検索範囲の2列目を返すため、O列の値を返します。

一点注意したいことは、上記は難易度調整時刻の直近値を返しているため、難易度調整時刻に対して古い時刻の価格であるか新しい時刻の価格であるか分かりません（誤差30分未満となります）。厳密にバックテストをする場合はこれではいけませんが、今回は単に値動きの挙動を確認したいだけであり、そこまで細かくこだわらないことにします。

さて、E2セルの値をE列全体にコピーしましょう（↓）。

次にD列（1日前価格）を検索します。以下のようにD2セルに数式を入力します。

D2セル：=VLOOKUP(A2-1,N:O,2,TRUE)

先程のE2セルと異なる点は、第一引数の値が”A2”となっているか”A2-1”となっているかだけです。エクセルの時刻表示は、整数部が日付、小数点以降が時刻を表します。したがって、A2セルの値が”2017/1/11 7:40:52”であれば、A2-1の値は”2017/1/10 7:40:52”となり、1日前の値となります。

同様にしてC2セル、F2セル、G2セルにも数式を入力します（↓）。

C2セル：=VLOOKUP(A2-7,N:O,2,TRUE)　7日前
F2セル：=VLOOKUP(A2+1,N:O,2,TRUE)　1日後
G2セル：=VLOOKUP(A2+7,N:O,2,TRUE)　7日後

さて、各セルの値をその列全体にコピーしましょう（↓）。

次に所定の期間における価格の変化率（＝リターン）を求めます。以下のように各セルに入力してください（↓）。

H2セル：=(E2-C2)/C2*100　調整前7日間のリターン
I2セル：=(E2-D2)/D2*100　調整前1日間のリターン
J2セル：=(F2-E2)/E2*100　調整後1日間のリターン
K2セル：=(G2-E2)/E2*100　調整後7日間のリターン

さて、各セルの値をその列全体にコピーしましょう（↓）。

これでデータ分析用の値が全て揃いました。あとはB列のChange（難易度調整の変化率）とH列～K列（各期間のリターン）について散布図を書きましょう（説明は割愛します）。


4.結果
結果は以下のツイートの通りです。
難易度調整の1日前から順張りの特性が確認されます（難易度が上がる＝価格も上がる）。それに対して難易度調整以降は有意とは言えないまでも反落するような傾向が見て取れます。2019/7/9の難易度調整では奇しくもその通りの挙動となりました。
※今回たまたまそうなっただけであることに留意ください。値動きの殆どは偶発性によるものです。

自分はツイートを見掛けてから分析完了まで、所要時間はたったの10分程度でした。たったこれだけのことで、その指標がリターンに対してどのような特性を持っているか定量的に把握することができます。

おまけとして今回のデータのCSVを添付しておきますので、ご自身でイジって練習して頂けると良いかと思います。


事例2と事例3はニーズがあるようでしたら追記する予定です。ここで知っていただきたいのは、データ分析とは決して難しいものではなく、データ分析するかしないかで確実にトレードスキルに差がついていく、ということです。データ分析の目的は使える指標を探すだけでなく、使えない指標を切り捨てるためのものでもあります。定量的な判断を元に一般論に惑わされることなくトレードできるようになることがデータ分析の目的なのです。


事例2.USDT発行枚数を参照したトレード

1.分析の背景と目的

2.ヒストリカルデータの収集

3.エクセルの分析手順

4.結果


事例3.暗号通貨以外の金融商品を使った分析

1.分析の背景と目的

2.ヒストリカルデータの収集

3.エクセルの分析手順

4.結果