似非FPが投資のことをまったりのんびり考えるブログ

資格だけの名ばかり似非FPが株式投資をしながら考えたことを書いて整理して自分が投資家として成長できることを目指します

エクセルで投資データベースの実演をしてみる

スポンサーリンク

 気が向いたのでエクセルで投資データベースの実演するというエントリー

エクセルで投資管理したいという方は参考になるかもしれない。

ちなみにこの規模のデータベース(90万行200M超のファイルサイズ)から以下の通りにやると笑えるほどすごく重たいもの(PCパワーによるけど多分実用に耐えない)になりますのでそこは留意くださいませ。

実際にデータあるなら任意の銘柄ごとのシートを10個位貼り付けた方がたぶんらく。

あくまでこのような感じに出来るのよっていう実演するというエントリーです。

 

また以下ぐらいはクリアしている方向けの記事です。

・エクセルの基本的な操作はできるし基本的な関数も理解してる。

絶対参照を理解してる。

VLOOKUPを理解してる。

・VLOOKUP関数で参照値に&が使える事を知っている。

それをふまえてどうぞ。

 ここで購入したデータを使ってやってみます。

gumroad.com

 

2016年度のデータの中に、データを網羅したエクセルファイルがあったりします。

開きます。

f:id:kamomenotoushi:20170605212513j:plain

これを扱いやすいように編集します。(編集する前にコピーして名前変えておくのがおすすめ)

 

VLOOKで参照列を指定しやすいようにA列及び1行に新規列と行を挿入し、1行には通し番号を通します。(VLOOKで列番号から参照するため)

またA2には「コード日付」とか入れときます。

f:id:kamomenotoushi:20170605212913j:plain 

 

その上でA列のコード日付データをユニークにするための仕込みをします。

E列の日付は時間も入っているので少々扱いづらいのでこれは日付にした方がいいのでそのような処理をする関数を入れます。

AK3からAN3に・・

AK3:「=YEAR(F3)」

AL3:「=MONTH(F3)」

AM3:「=DAY(F3)」

AN3:「=DATE(AK3,AL3,AM3)」

AN列には書式で日付にしてもいいかもしれません。

参考URL

エクセル YEAR, MONTH, DAY 関数:年月日を取得する

エクセル DATE 関数:日付を計算した結果を取得する

 

f:id:kamomenotoushi:20170605213043j:plain

ここまでしたら

A3を「=B3&AN3」とします。

参考URL

「&」でつなぎ1つのセルに表示--Excel・エクセル

f:id:kamomenotoushi:20170605213104j:plain

ここまで来たらA3及びAK3~AN3を表の下まで式をコピーします。

ちなみに選択して右下にカーソルを合わせるときにできる「+」をダブルクリックすると表の終わりまでコピーされます。

f:id:kamomenotoushi:20170605213238j:plain

これで、A列はコードと日付からできたユニークな(他に同じ値を持つものがいない)データベースになります。

 

そしてデータベースからの表示表を作ります。

新規シートを追加し例えばですが以下のような表を作ります。

f:id:kamomenotoushi:20170605213417j:plain

この場合は式などを以下のようにしています。

(以下の”2016”は今回使用してるデータファイルのデフォルトシート名です)

B1:「参照したい銘柄コード」

D1:「=VLOOKUP(B1,'2016'!B:C,2,FALSE)」

E1:「=VLOOKUP(B1,'2016'!B:D,3,FALSE)」

F1:「=VLOOKUP(B1,'2016'!B:E,4,FALSE)」

B3~E3:「データベースで参照したい項目についている1行目の番号」

(株価は7となってるので7と入れれば株価が参照されるし、14と入れれば出来高になる)

B4:「=HLOOKUP(B3,'2016'!$1:$2,2,FALSE)」(B3~E3で指定した項目を表示させる関数)

C4~E4:B4からのコピー

A5から下:参照したい日付

B5:「=VLOOKUP($B$1&$A5,'2016'!$A:$AJ,Sheet1!B$3,FALSE)」

(B1で指定した銘柄コード(絶対参照)と日付と、3行で指定した項目から参照してくる式。コピーに対応するため絶対参照を使ってます。)

C5~E5:B5のコピー

B5~E5の下:B5~E5のコピー

参考URL

ExcelのVLOOKUP関数の使い方

ExcelのHLOOKUP関数の使い方

 

これで、B1にて銘柄コード、B3~E3にて項目を番号にて指定するとデータベースから日付に対応したデータを参照する表ができました。

これで銘柄コードや番号を変えるとデータを参照できるデータベースが一応完成です。

f:id:kamomenotoushi:20170605213710j:plain

このとおりにやるとくそ重いけど。

元データベースの規模を抑えたりすると結構使えるかもしれません。 

やっぱり任意の銘柄シート貼り付けるほうがラクだしそもそも実際に購入すると銘柄ごとのデータあるし

 

元データの入手経路もあるけどかなり応用効くんじゃないかなあって思います。

例えば株探の業績データと時系列株価貼り付けてデータベースを作りコードを入れると時系列業績と株価を参照できるものとか。

複数のコードを入れて例えば三井物産住友商事の株価出来高推移を比較確認するものとか。 

 

よろしければ参考にしてくださいませ。

 

 

記事を気に入ってくださった方は↓をクリックしてくれると嬉しいです。
  にほんブログ村 株ブログ 株 中長期投資へ