ヨコワケさんによる株価分析用Excel管理表の作成講座のまとめです。株価データ・投資指標データ等をExcelシートに取り込んで活用する方法をExcel初心者の方にも分かりやすく説明されています。初級編では最新データをExcelシートに取り込み、データ蓄積のベースとなる管理表を作成します。
こもりばんさん(@moriban)の新サイト 株プラス(https://t.co/QymeSsrNps)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
がOPENをされました。
それに合わせてヨコワケシャンシャンが皆様にできること
それは全体管理表作成講座です。
当日データのみで作成可能な全銘柄管理表の作成手順を数日にわたりモーメントで作成していきます。
ヨコワケシャンシャンが見せて差し上げましょう
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
無限の「株プラス」宇宙を!https://t.co/QymeSsrNps
今ちょっと準備中w
自分で自分を煽らねば前に進めぬ。
それでも、これができるのは私のみ♪
私がやらずに誰がやる。
もう一度やりきって見せましょう。
乞うご期待。
エクセル苦手な人もぜひチャレンジしてみてくださいねー♪
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
めっちゃ自信あります。
全員で完成させましょうね♪
手取り足取りお伝えするから、エクセル苦手な人でも必ずできますよ♪
エクセル自体必要か不要かって問題はあるかもしれないけどw
81 管理表(当日データVer)の完成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
だいたいこんな感じでしょうか?
管理表をきれいに見せるには、ツールバーからの「条件付き書式設定」と
右クリックからの「セルの書式設定」によるセンスが必要となります。
色々試行錯誤してご自身なりにアレンジなさってみてください。
完成はこんな感じ♪ pic.twitter.com/NHYJrXQogu
最新の環境では
「SMB共有フォルダ接続手順」を参考にPCをKABU+会員制サービスの共有フォルダに接続して下さい。(要会員登録)
同手順の「STEP 2 ネットワークドライブの割り当て」まで行い、Zドライブの中に「kabu.plus」フォルダが表示されることを確認します。
14 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
ここからは、マウントした共有フォルダを使ってエクセルに各種データを接続していきます。
ここで接続しておくと、今後ワンクリックで最新データに更新が可能となります。
ここからは EXCEL2016を前提に説明していきます。
15 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
ZドライブにWebdavがマウントされているのを確認して新しいエクセルファイルを開いて下さい。
エクセルの上のツールバー「データ」-「新しいクエリ」-「ファイルから」-「CSVから」を選択してください。
ファルダを選択できるので、「CSVEX-WebDAV」を選択してください。 pic.twitter.com/3rJumgbUUK
最新の環境・Excelでは(読み替え用)
画面上部のツールバーから「データ」タブを開いて「データの取得」「ファイルから」「テキストまたは CSV から」をクリックします。
「データの取り込み」画面が表示されるので、画面左側の項目から共有フォルダ「share (\\smb1.csvex.com) (Z:)」を選択します。
(以降の手順でも同様に読み替えて下さい。)
16 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
「CSVEX-WebDAV」をダブルクリック、「https://t.co/4lmcx1i8XK」を選択
「CSV」-「japan-all-stock-data」-「dairy」
日付の入っていないエクセル「japan-all-stock-data」をインポート
エクセル内に出たものの「編集」をクリック pic.twitter.com/yx2lIdPfOW
最新の環境・Excelでは(読み替え用)
共有フォルダ配下のフォルダから「kabu.plus」→「csv」→「japan-all-stock-data」→「daily」フォルダを開き、「japan-all-stock-data.csv」ファイルを選択して「インポート」ボタンをクリックします。
確認画面が開いたら「データの変換」ボタンをクリックします。
編集画面(Power Query エディター)が表示されます。
1行目が列名の行として認識されていない場合は、画面上部のツールバーから「1行目をヘッダーとして使用」をクリックします。
(以降の手順でも同様に読み替えて下さい。)
17 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
下記の項目名について全て以下の処理を行います。
項目名「時価総額」を左クリックで選択
右クリックし、「型の変更」-「10進数」をクリック
「現在のものを置換」を選択
項目名の「ABC」となっているのが「1.2」となれば成功。 pic.twitter.com/HCXB8S3WTz
18 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 25, 2018
下記の項目について全て先ほどと同じ処理を行ってください。
時価総額、発行済み株式数、配当利回り、1株配当、PER,PBR,EPS,BPS、最低購入価格、単元株、年初来高値、年初来安値、全て「ABC」が「1.2」に置き換え完了したら、ツールバー左上の「閉じて読み込む」をクリック pic.twitter.com/YKf3s4ikSl
補足
Ctrlキーを押しながら列名を左クリックすれば、複数の列を選択できます。この状態で列名を右クリックすれば、複数の列を一括して「型の変更」ができます。
19 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
先ほどの「閉じて読み込む」をクリックするとエクセルの元の画面に戻ります。
その際、現在のシートの名前を「基礎データ1」としてしてください。
今後データの参照にこの名称を使いますので必ず「基礎データ1」と記載してください。
今後の手順でも同じです。 pic.twitter.com/8EMkRFapWX
20 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
上のツールバーで「データ」-「新しいクエリ」-「ファイルから」-「CSVから」をクリック
「CSVEX」ー「https://t.co/DSEdknlb3u」ー「CSV」-「Japan-all-stock-financial-results」
「monthly」-日付のないエクセルファイル「「Japan-all-stock-financial-results」を選択 pic.twitter.com/N1PZNsp8l2
21 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
出てきた画面で右下の「編集」をクリック
17と同じく「ABC]とあるのを「1.2」に置き換えます。
置き換える項目は、下記の通り
売上高、営業利益、経常利益、当期利益、総資産、自己資本、資本金、有利子負債、自己資本比率、ROE,ROA,発行済み株式数
「閉じて読み込む」クリック pic.twitter.com/DPQ1apti0s
22 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 2, 2018
クリック後、Sheet2の名前「基礎データ2」としてください。
以降同じ繰り返しです。
次は、「Japan-all-stock-infomation」の「Shareholding-ratio」の日付のないファイルを選択
「ABC」から「%」に置き換える項目
投資信託持ち分
Sheet名 基礎データ3 pic.twitter.com/E1ZwNZuZ5O
最新の環境では
「基礎データ3」(shareholding-ratio)の配信が終了しているため、「基礎データ3」というシート名を付けて空のシートを作成します。
23 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
同じ手順で、次は「japan-all-stock-margin-transactions」ー「weekly」
ー日付のないエクセルファイルを選択ー編集をクリック
「ABC」から「1.2」に置き換える項目
信用買い、信用買い残高、信用売り、信用売り残高、賃借倍率
閉じて読み込む
Sheet名 基礎データ4 pic.twitter.com/0qMQZozda4
24 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
同じ手順で、一つフォルダを飛ばして「japan-all-stock-prices-2」を選択
ー「daily」ー日付のないエクセルファイルを選択
置換項目
株価、始値、高値、安値、VWAP,出来高、出来高率、売買代金、時価総額
値幅下限、値幅上限、年初来高値、乖離率、年初来安値、乖離
基礎データ5 pic.twitter.com/2oeDagVHUS
2020年12月末で各前場データの配信が終了したので、以降でてくる各データの「dairy_ms」フォルダは存在しません
— ヨコワケ🐼🎋シャンシャン✨黄金は諦めそうなその先に。 (@ShiningBlue_SKY) January 5, 2021
しかしエクセルの構成上Sheetのみは「基礎データ6」として作成しておいてください。中身はデータがないため白紙の状態となります
基礎データ6前場、10、12前場、14前場が対象となります。
最新の環境では
旧フォーマットの前場データの配信が終了しているため、代わりに「株価四本値データ(日通し・前場・後場)」を使用します。
共有フォルダ配下のフォルダから「kabu.plus」→「csv」→「tosho-stock-ohlc」→「daily」フォルダを開き、「tosho-stock-ohlc.csv」ファイルを選択して「インポート」ボタンをクリックします。
確認画面が開いたら「データの変換」ボタンをクリックします。
編集画面(Power Query エディター)で「始値」の列名を左クリックした後にShiftキーを押しながら「後場終値」の列名を左クリックして2つの列の間に含まれる列を全て選択し、右クリックして「10進数」に「型の変更」を行います。「閉じて読み込む」をクリックします。
シート名を「基礎データ6」に変更します。
26 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
同じ手順で「jsf-balance-data」を選択
日付のないエクセルファイル「jsf-balance-data」を選択
置換項目なし -閉じて読み込む
Sheet名 基礎データ7 pic.twitter.com/d00z7uSNvY
27 クエリの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
同じ手順で「jsf-gyakuhibu-data」を選択
日付のないエクセルファイル「jsf-gyakuhibu-data」
置換項目
品貸料率、品貸日、前日料率、
閉じて読み込む
Sheet名 基礎データ8 pic.twitter.com/kasiwAK4yY
31 クエリの完成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 26, 2018
これで下ごしらえは完了です。
今までの作業で、エクセルシートと共有フォルダの全データが接続されました。
今後は、ツールバー「データ」ー「すべて更新」をクリックするだけで、全ての基礎データがワンクリックで当日データに更新されます。
データの更新は毎日16時頃です。 pic.twitter.com/hSsO16FwbP
最新の環境では
KABU+のデータ更新時刻は東証の取引時間延長に伴って16:30頃に変更になっています。(参考 : ディレクトリ構成 説明資料)
32 管理表の作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
Sheet1 名を 管理表 としてください。
「基礎データ1」 の項目Aで左クリックしA列全体を選択、右クリック(コピー)
「管理表」 A1セル で右クリック(貼り付け)
投資信託、インフラファンド、リートも取り込もうと思いましたが、データの参照位置が異なるため諦めます。 pic.twitter.com/jRTkD8xclY
33 項目名の作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
項目名を入れていきます。今後の関数にも対応させる必要があるため同じように入力をお願いします。
全部の表が完成したら並び替えは可能です。不要な項目の非表示も可能です。
現在A1セルに SCとはいっていると思います。
その1つ右、B1セルから右に一つずつ入れていきます。
34 項目名の作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
B1 名称
C1 市場
D1 業種
E1 関連
F1 株価
G1 前日比
H1 前日比(%)
I1 前日終値
J1 始値
K1 高値
L1 安値
M1 前場終値
N1 VWAP
O1 出来高
P1 出来高率
Q1 売買代金
R1 時価総額(億円)
S1 発行済株式数
T1 年初来高値
U1 年初来高値乖離率 pic.twitter.com/pk4N3nQ2Ce
35 項目名
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
V1 年初来安値
W1 年初来安値乖離率
X1 信用買い残
Y1 信用売り残
Z1 貸借倍率
AA1 決算期
AB1 決算発表日
AC1 売上高(百万円)
AD1 営業利益(百万円)
AE1 経常利益(百万円)
AF1 当期利益(百万円)
AG1 総資産(百万円)
AH1 自己資本(百万円)
AI1 資本金(百万円 pic.twitter.com/tKgqGwfLNp
36 項目名
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AJ1 有利子負債
AK1 自己資本比率
AL1 ROE
AM1 ROA
AN1 配当利回り
AO1 1株配当
AP1 PER(予想)
AQ1 PBR(実績)
AR1 EPS(予想)
AS1 BPS(実績)
1行目を全て選択 (1番左の1で左クリック)、ツールバーで
(折り返して全体を表示する)をクリック pic.twitter.com/EzswPpnHT4
37 関数の入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
これから記載する各セルの関数をコピーして、セルに貼り付けるか、同じように入力してください。
最初だけ入力してしまえば、あと変更するところは1か所か、4か所だけですので、見た目より簡単な作業です。
次の投稿から記載します。
38 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
B2セル =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,2,TRUE),NA())
C2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,3,TRUE),NA()) pic.twitter.com/S6gPgfkV2l
39 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
D2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,4,TRUE),NA())
E2 空欄
F2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,6,TRUE),NA()
40 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
G2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,7,TRUE),NA())
H2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,8,TRUE),NA())
41 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
I2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,9,TRUE),NA())
J2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,10,TRUE),NA())
42 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
K2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,11,TRUE),NA())
L2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,12,TRUE),NA())
43 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
M2 =IF(INDEX(基礎データ6前場!$A:$A,MATCH($A2,基礎データ6前場!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ6前場!$1:$1048576,6,TRUE),NA())
N2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,13,TRUE),NA())
最新の環境では
旧フォーマットの前場データの配信が終了しているため、M2セルでは代わりに「株価四本値データ(日通し・前場・後場)」を参照します。
M2 =IF(INDEX(基礎データ6!$A:$A,MATCH($A2,基礎データ6!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ6!$1:$1048576,13,TRUE),NA())
44 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
O2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,14,TRUE),NA())
P2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,15,TRUE),NA()) pic.twitter.com/FBVPlTYYOW
45 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
Q2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,16,TRUE),NA())
R2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,17,TRUE),NA())/100 pic.twitter.com/RDOUgVVnfc
46 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
S2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,6,TRUE),NA())
T2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,21,TRUE),NA())
47 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
U2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,22,TRUE),NA())
V2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,24,TRUE),NA())
48 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
W2 =IF(INDEX(基礎データ5!$A:$A,MATCH($A2,基礎データ5!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ5!$1:$1048576,25,TRUE),NA())
X2 =IF(INDEX(基礎データ4!$A:$A,MATCH($A2,基礎データ4!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ4!$1:$1048576,2,TRUE),NA())
49 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
Y2 =IF(INDEX(基礎データ4!$A:$A,MATCH($A2,基礎データ4!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ4!$1:$1048576,4,TRUE),NA())
Z2 =IF(INDEX(基礎データ4!$A:$A,MATCH($A2,基礎データ4!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ4!$1:$1048576,6,TRUE),NA())
50 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AA2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,3,TRUE),NA())
AB2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,4,TRUE),NA())
51 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AC2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,5,TRUE),NA())
AD2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,6,TRUE),NA())
52 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AE2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,7,TRUE),NA())
AF2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,8,TRUE),NA())
53 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AG2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,9,TRUE),NA())
AH2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,10,TRUE),NA())
54 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AI2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,11,TRUE),NA())
AJ2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,12,TRUE),NA())
55 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
AK2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,13,TRUE),NA())
AL2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,14,TRUE),NA())
56 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
AM2 =IF(INDEX(基礎データ2!$A:$A,MATCH($A2,基礎データ2!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ2!$1:$1048576,15,TRUE),NA())
AN2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,7,TRUE),NA())
57 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AO2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,8,TRUE),NA())
AP2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,9,TRUE),NA())
58 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AQ2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,10,TRUE),NA())
AR2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,11,TRUE),NA())
59 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
AS2 =IF(INDEX(基礎データ1!$A:$A,MATCH($A2,基礎データ1!$A:$A,1),1)=$A2,VLOOKUP($A2,基礎データ1!$1:$1048576,12,TRUE),NA())
ここまで入力出来たら、B2から左クリックしながらAS2まで選択
緑枠の右下の ポツを 左クリックしながらA列の数字のある一番下までドラック pic.twitter.com/6O3e3sc4YQ
60 関数入力
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) February 27, 2018
2月27日現在 一番下の行は 3698行目だと思います。
うまくドラックするとこんな感じになって、一気にデータがすべて入ります。
ここまで来たらほぼ完成したようなものですね。
ここのデータは、ツールバーの「データ」-「すべて更新」を押すだけで
毎日最新データに置き換わります。 pic.twitter.com/bhwotO92Mc
さぁ、始めるよ。
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
今日完成させるよ♪
本当、毎日TL埋めてごめんね、もう終わるから。
とっととVALUに引っ込むから今日だけ許してね♪
61 テーブルの作成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
「管理表」A1セルで左クリックしながら、AS列の数字が入っている一番下のセルまで選択(文字の入っているセルを全部選択するイメージ)
選択出来たら上のツールバーの「ホーム」-「テーブルとして書式設定」で好きなデザインを設定してください。
一気に表っぽくなったでしょ♪ pic.twitter.com/bADwrmlMsj
補足
A1セルをクリックした後、ShiftキーとCtrlキーを同時に押しながら「→」「↓」キーを押せば、表全体を素早く選択できます。
62 強調表示(株価プラス赤色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
文字に強調表示をつけていきます
G列のGで左クリックしてG列全てを選択
ツールバー「ホーム」-「条件付き書式」ー「セルの強調表示ルール」-「指定の値より大きい」を選択
数字の部分を0とする。
右側のBOXは「ユーザー設定の書式」を選択
色「赤色」を選択し、OK pic.twitter.com/d4IVBRc5mt
63 強調表示(株価マイナス青色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
Gで左クリック、G列全てをもう一度選択
ツールバー「ホーム」-「条件付き書式設定」-「セルの強調表示ルール」-「指定の値より小さい」を選択
数字の部分を0とする。
「ユーザーの書式設定」
色「青色」を選択し、OK pic.twitter.com/uOAJXfG4ox
64 強調表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
G1セルで左クリック
「条件付き書式設定」-「ルールのクリア」ー「選択したセルからルールをクリア」を選択
これで、G1セルの文字の色が元に戻ります。 pic.twitter.com/1EkEJXZC9u
65 強調表示(株価%プラスを赤色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
Hで左クリック、H列全て選択
ツールバー「ホーム」-「条件付き書式設定」-「セルの強調表示ルール」-「指定の値より大きい」を選択
数字の部分を0とする。
「ユーザーの書式設定」
色「赤色」を選択し、OK
66 強調表示(株価%マイナスを青色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
Hで左クリック、H列全て選択
ツールバー「ホーム」-「条件付き書式設定」-「セルの強調表示ルール」-「指定の値より小さい」を選択
数字の部分を0とする。
「ユーザーの書式設定」
色「青色」を選択し、OK
67 強調表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
H1セルで左クリック
「条件付き書式設定」-「ルールのクリア」ー「選択したセルからルールをクリア」を選択
これで、H1セルの文字の色が元に戻ります。
ここまでの完成図です。 pic.twitter.com/MIrZ6uZP8M
68 表示の変更(%表示)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
前日比%(H列)の数字を%表示に変更します
H1セルでの算式の一番後ろに「/100」をつけてください
H1セルの算式を添付のように変更すれば、その下は全て自動で書き換えてくれます
Hで右クリックしてH列全体を選択ー「セルの書式設定」-「パーセンテージ」-桁数を「2」 pic.twitter.com/TLXBqhuHtk
69 表示の変更(%表示)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
出来高率%(P列)の数字を%表示に変更
P1セルでの算式の一番後ろに「/100」をつけてください
P1セルの算式を添付のように変更すれば、その下は全て自動で書き換えてくれます
Pで右クリックしてP列全体を選択ー「セルの書式設定」-「パーセンテージ」-桁数を「2」 pic.twitter.com/GTgVYefEIm
70 強調表示(出来高率10%以上を赤色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
P列全体を選択「ホーム」-「条件付き書式設定」-「指定の範囲内」
数字を「10%」と「19.99%」 「ユーザー設定の書式」
色を「赤色」 OK pic.twitter.com/KIl1FFR8qt
71 強調表示(20%以上を太字赤色)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
もう一度P列全体を選択「ホーム」-「条件付き書式設定」-「指定の値より大きい」
数字を「20%」 「ユーザー設定の書式」
スタイルを「太字」
色を「赤色」を選択
その後、P1セルを選択し「ルールのクリア」-「選択したセルからルールをクリア」 pic.twitter.com/07o3B2e8d7
72 強調表示(出来高率にデータバーを設定)
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
Pで左クリック、P列全体を選択
「ホーム」-「条件付き書式設定」-「データバー」
好きなデザインを選択
P1セルのちょぼを左クリックして、「降順」で並び替えると
出来高率順にデータバーがきれいに並びます。 pic.twitter.com/4sJlswfVQB
73 %表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
U2セルとW2セルの算式に先ほどと同じように末尾に「/100」をつけてください
U列全体を選択し「セルの書式設定」-「パーセンテージ」-「2桁」
W列全体を選択し、「セルの書式設定」-「パーセンテージ」-「2桁」
72の手順と同様にデータバーを設定したり、文字の色を設定してみたり♪ pic.twitter.com/3MAKiuFWuv
74 単位表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
Zを右クリック「セルの書式設定」ー「ユーザー定義」ー「0.00」を選択し種類の下の四角の中「0.00」となっているのを、「0.00倍」と記載 pic.twitter.com/YXQGHkcMD0
75 売上高~有利子負債までを億単位に変更
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
AC2~AJ2までの各セルの算式の末尾に「/100」を付記
AC1~AJ1までの各セルの項目名(百万円)を(億円)に変更
これは好みの問題w
AK2の算式の末尾に「/100」を付記
AKを右クリック「セルの書式設定」-「パーセンテージ」-「2桁」を選択 pic.twitter.com/U7bDJJIbUr
76 強調表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
お好きな項目に先ほどからの「条件付き書式」で
「指定の値より大きい数字」で数字に赤色とか、太字赤色とか、青色とか設定したり
「指定の範囲内」で数字に色をつけたり
同じセル内でも複数の条件を重ねて設定が可能です。
あとは「データバー」をつけてみたり。
カスタマイズ自由自在 pic.twitter.com/I6tvY15vDO
77 項目の並び替え
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
最初に設定した通りの項目の並びになっているので、お好きに並び変えてください
方法は以下
例えば、M列VWAPをO列出来高の左に持ってくる場合
M で右クリック「切り取り」
O で右クリック「切り取ったセルの挿入」で位置を変更可能です
移動後も各セルの関数に影響はありません pic.twitter.com/Mu42YVIHyN
76 項目の追加
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
新たに項目を作る場合
例えば、M列前場終値の左に「前場と終値」の株価騰落を入れる場合
Nで右クリック「挿入」
N1項目名を「前場~終値」
N2 =(F2-M2) 下の行には自動で入ると思います。
77 さらにその右 O列で右クリック「挿入」
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
O1項目名を「前場~終値(%)」
O2 =(F2-M2)/M2
Oで右クリック「セルの書式設定」-「パーセンテージ」-「2桁」
65,66の要領で、「条件付き書式」でプラス赤色、マイナス青色を設定
これでオリジナルの数値「前場終値と大引け終値の騰落率」が表示。 pic.twitter.com/FoZk3VL0gj
78 表示の変更
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
S列「売買代金(千円)」を「売買代金(億円)」に変更
S2セルの算式の末尾に「/100000」を付記 pic.twitter.com/WrK9QfGntU
79 いらない項目の非表示
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
いらない項目を非表示にする方法
I列(前日終値)、J列(始値)、K列(高値)、L列(安値)を非表示とする場合
I で左クリックしながらLまで選択
右クリックで「非表示」
非表示になっているけど、数字は生きています。
再度右クリックで範囲を選択して「再表示」で復活 pic.twitter.com/YtcGIUj9L8
81 管理表(当日データVer)の完成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
だいたいこんな感じでしょうか?
管理表をきれいに見せるには、ツールバーからの「条件付き書式設定」と
右クリックからの「セルの書式設定」によるセンスが必要となります。
色々試行錯誤してご自身なりにアレンジなさってみてください。
完成はこんな感じ♪ pic.twitter.com/NHYJrXQogu
80 新規上場IPOする場合の追加方法
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
「管理表」にコードを付した日よりあとに上場した銘柄がある場合、下記処理をして追加することになります
A列一番下で、新しく上場した銘柄コードを入力し、ダブルクリック
それだけですべてのデータは反映されます
管理表を作った日以後のIPOの際に必要な処理です pic.twitter.com/aryQ8la19x
82 管理表の使い方
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
E列「関連」としたところには、セクターを入れると管理しやすくなります。銘柄ごとに「ゲーセク」とか「BIO」とか、「監視」とか。
入れておくと、添付のようにソートをかけられるので
セクター別の業績、セクター別の値動きを見るのに便利です
「関連」項目は複数あるといいかも pic.twitter.com/tOTKAaciV3
83 管理表の使い方
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
ソートをする場合は、1行目(項目名の行)のセルの右下のポッチを
クリック
今日の「前日比(%)」でソートしたい場合
H1のポッチで左クリック
任意の数字のみを抽出したいなら、下のBOXのチェック
上昇率順に見たいなら上の「降順」
下落率順を見たいなら上の「昇順」をクリック pic.twitter.com/BOhYiSawK6
84 管理表の使い方
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
一定の数字以上のみを抽出したい場合
「出来高率」で30%以上のみを抽出したい場合
R1のポッチを右クリック「数値フィルタ」-「指定の値より大きい」
「30%」と入力すると出来高率30%以上の銘柄のみが抽出。
解除する場合は、同じポッチで「出来高率からフィルタをクリア」を選択 pic.twitter.com/5cE29xluWw
85 管理表の使い方
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
先ほどまでの並び替えや数値フィルタは複数項目に同時に使うことができるので、業績データで「利益が出ていて、自己資本比率が何%以上でROEが何%以上の会社」というようなスクリーニングも可能となります。
機能しているソートには、ポッチの横に▼マークがでます。 pic.twitter.com/mKHRHeKIgx
86 画面の固定
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
項目がたくさんあって、左に行くと会社名が見えないので、
ツールバー「表示」-「ウィンドウ枠の固定」ー「ウィンドウ枠の固定」を選択すると、上の数行と左の数列が固定されますので、スクロールしても
銘柄コード、社名、項目名などがずっと表示されたまま閲覧可能となります。
87 ズーム機能
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
右下のバーを左クリックで動かすと、エクセルの画面を大きくしたり、小さくしたりすることができます
表示する項目が多い場合などは、マイナスに合わせると画面が縮小されます
ツイッターに添付する際、どのような映り方になるかなど検証してみてください。
ちょい左が使いやすいです pic.twitter.com/VDfB3HOfue
88 行の幅の拡大
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
行が狭く感じるようなら、左上のポッチを左クリックで全体を選択
どこか一つ、行(数字の方)を左クリックしながら広げると全体が同じ広さに広がります。
列(アルファベットの方)も同じです。 pic.twitter.com/y6KDSsntcx
89 ツイッターモーメント完成
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
いかがでしたか?kabu+サイトオープン記念、ヨコワケシャンシャンのエクセル管理表作成講座♪
エクセル宇宙を存分に感じていただけたでしょうか?
今後ともkabu+(https://t.co/QymeSsrNps)をご愛顧くださいますよう、心よりお願い申し上げます。
91 完成のご挨拶
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) March 1, 2018
私はこの説明書を作る時間が大好きです。
私の説明書に沿ってエクセル管理表にチャレンジしてくださる方が完成したらきっと喜んでもらえると確信があるからです。
圧倒的自信をもってお届けいたしました(´っ・ω・)っ
お付き合いくださいまして本当にありがとうございました♪
90 エクセル管理表の続き
— ヨコワケ🕊💕シャンシャン(May the PKTN be with you) (@yokowakefes) June 9, 2018
ここまで当日データのみを利用した全体管理表の作成を行いました。
続きは、VALU(https://t.co/Ax9u84Mfwr)又はnote(https://t.co/LM1L954EjQ)で手順を公開しております。
内容はどちらも同じです。
時系列データを用いた全体管理表、個別銘柄管理表の作成を行っています。