人気ブログランキング |

タグ:エクセル ( 50 ) タグの人気記事

個人用マクロブックの使い方

TH69です。

今回はエクセルのマクロネタですので、興味のない方は読み飛ばして下さい。

新しいPCでは「個人用マクロブック」という便利なエクセルの機能をまだ使っていないので、いい機会と思いその使い方を記事にしてみました。「個人用マクロブック」は汎用のマクロなどをいつでも使えるように登録できる機能で、使い方によっては邪魔臭い作業を大幅に短縮することができます。

エクセルのリボンに「開発」が表示されていない場合は、ファイル>オプション>リボンのユーザー設定を開いて「開発」にチェックを入れて表示させて下さい。
e0382354_13303787.gif

では、「個人用マクロブック」を実際に使ってみましょう。

今回は行列番号の表示と非表示の切り替えをやってみたいと思います。

何でもいいのでエクセルのブックを開き「開発」タブを表示すると、そこに「マクロの記録」がありますのでクリックします。
e0382354_13303795.gif


すると「マクロの記録」ウインドウが開きますので、「マクロの保存先」の右端にある▼をクリックし、「個人用マクロブック」を選択し「OK」ボタンを押します。
e0382354_13303830.gif


「OK」ボタンを押すとマクロの記録が始まりますので、ファイル>オプション>詳細設定>次のシートで作業するときの表示設定にある「行列番号を表示する」のチェックを外し「OK」ボタンをクリックします。
e0382354_13483639.gif


元の画面に戻ったら開発タブの「記録終了」をクリックしてマクロの記録を終了させます。行列番号は非表示になっていますね。
e0382354_08513947.gif


そして同じ開発タブの「Visual Basic」をクリックします。
e0382354_08514003.gif


すると「Visual Basic for Applications」というウインドウが開きます。よく「VBA」とか言ってるあれですね。この画面はVisual Basic Editor(VBE)というもので、VBAのコードを編集するためのエディタとなります。
e0382354_13571376.gif


その左画面にある「VBAProject(PERSONAL.XLSB)」のツリーを開き、標準モジュールにある「Module1」を開きます。すると何かプログラムのコードのようなものが表示されます。これが今マクロ記録した「行列番号非表示」のVBAコードとなります。
(この「PERSONAL.XLSB」が個人用マクロブックの本体で、通常は非表示のブックです。)

先頭がシングルコーテーションの行はコメントなので、結局VBAコードとしては「ActiveWindow.DisplayHeadings = False」だけのようです。簡単ですね。英語が苦手な私でも何となくわかります。「アクティブウインドウの行列番号表示→しない」みたいです。
e0382354_14015666.gif


それでは試しに「= False」の部分をクリアし、「=」を入れてみましょう。すると下の画像のようにリストが表示されますので、Trueを選択してダブルクリック(もしくはTabキー)で確定させます。「True」なので、多分行列番号が表示されるはずですね。
e0382354_14221972.gif


VBE画面のツールバーに右三角の実行ボタンがありますので押してみましょう。その時どのマクロを実行するのかVBEは判断できませんので、カーソルを実行したいマクロの「Sub~End Sub」の間に置くようにしてくださいね。
e0382354_14255390.gif



思い通りに行列番号が表示されました。簡単ですね(笑)。
でも、これはこれで邪魔臭いですね。行列番号の表示非表示を切り替えるためにVBEを操作するなんて考えられません(汗)。では、「ActiveWindow.DisplayHeadings = True」と「ActiveWindow.DisplayHeadings = False」のマクロを作って利用しましょうか。(私も昔はそのようにしていました)でも「True」と「False」を切り替えるだけなので、何とか一つのコードに収められそうです。
e0382354_08514087.gif


ここからはVBAのプログラミングになりますね。まずマクロ一覧に表示されるマクロ名である「Macro1」(数字部分は変わる場合があります)の部分を、分かりやすい「行列番号切替」などに変更します。その時に「半角スペース」や「()」などは変更しないように注意してくださいね。
e0382354_14342362.gif



次に「ActiveWindow.DisplayHeadings = True」の頭に「if」と入力します。「if」の後ろには半角スペースを入れます。そして半角スペースを空けて「then」と入力し確定します。勘のいい方ならわかると思いますが動きは数式内のIF関数と同じで、「If~Then〇」で「もし~だったら〇を実行」となります。VBAでは「IF文」と言います。

このコードは「アクティブウインドウの行列番号が表示だったら〇を実行」となりますね。
e0382354_14390895.gif


「アクティブウインドウの行列番号が表示だったら」どうしたいかというと非表示にしたいわけですから、
「ActiveWindow.DisplayHeadings」と入力して(コピペでいいです)「=」を入れ、出てきたリストから「False」を選択して確定します。
e0382354_14480710.gif



そして改行して「else」と入力します。数式内のIF関数の「もし~でなかったら●」の部分ですね。
e0382354_14511309.gif


この場合「アクティブウインドウの行列番号が表示でなかったら(非表示だったら)」何がしたいかというと、表示したいわけなので「ActiveWindow.DisplayHeadings = True」(コピペでいいです)と入力します。
e0382354_14543408.gif

さらに改行して「end if」と入力します。これはIF文を使う場合の決まり事で、これがないとIF文がどこで終了するのか分からないので必ず入力します。
VBAのIF文は基本中の基本なので、『If~Then〇(Else●)End If』は定型文として覚えておくと便利です。数式でIF関数が使える人なら何の問題もなく理解及び使用ができると思います。(「Else」がカッコなのは使用しない場合もあるからです。)
e0382354_14593664.gif


VBEでのVBAコード編集作業はこれで終了となります。実際に動くかどうか「実行ボタン」を何度か押して行列番号の表示と非表示が切り替わることを確認してくださいね。
お疲れさまでした。

でも、このままでは個人用マクロブックの利用はできませんので注意してください。個人用マクロブックを利用する設定を保存しないといけないためです。

ブックを閉じる時に下の画像のようなダイアログが出ると思いますが、ここで「すべて保存」をクリックすることにより個人用マクロブックが有効になります。次回エクセル起動時より非表示で「PERSONAL.XLSB」が開き、そこに登録されたマクロを使用できるようになります。
e0382354_14593648.gif


それから今回作ったマクロ「行列番号切替」を利用する場合は、開発タブのマクロをクリックします。
e0382354_14593618.gif


そこで出てきたマクロウインドウのマクロを選択し実行ボタンを押します。
e0382354_14593639.gif


これで今までの『ファイル>オプション>詳細設定>次のシートで作業するときの表示設定』からやるよりはかなり楽になりましたが、まだ邪魔臭い気がします。

次回(があればですが…)は、もっと簡単にマクロを利用できるようにする方法をアップしたいと思います。

同じやり方で汎用マクロ(設定関係が特に便利です)が色々個人用マクロブックに登録できますのでお試しください。ご質問等あればコメントなどでお願いします。

では。

【参考コード】
Sub 行列番号切替()

If ActiveWindow.DisplayHeadings = True Then
ActiveWindow.DisplayHeadings = False
Else
ActiveWindow.DisplayHeadings = True
End If

End Sub

(可読性をアップするVBAコードのインデント(字下げ)はTabキーで)

にほんブログ村 米国株
by th1252 | 2019-08-10 15:42 | Excel | Trackback | Comments(0)

6月1日から「IEX API」で最新株価が取得できないようです(涙)

TH69です。

あれあれさんに教えて頂いた「IEX API」ですが、サイトの仕様変更により6月1日から最新株価の取得ができなくなってしまいました。「Yahoo Finance(米国)」からの取得はとても遅くて「IEX API」を愛用していたため結構困っていました。

一応、分からないまま(APIについては全く分かりません)色々やっていると取得できるようにはなったので次期バージョンアップで対応予定ですが、それまでは「Yahoo Finance」から取得する設定にしてご利用願います。

 TH69の亜米利株>ティッカー一覧シート>取得先サイト>Yahoo Finance

では。



by th1252 | 2019-06-02 17:12 | TH69の亜米利株の情報 | Trackback | Comments(0)

VBAコードを自動インデントしてくれる「Smart Indenter」のインストール

TH69です。

今回はExcelVBAネタですので、興味のない方は読み飛ばして下さい。と、前回と同じ展開で…(笑)

VBAコードを自動インデントしてくれる超便利なExcelVBAアドイン「Smart Indenter」のインストールについては、自分で書こうと思いましたが以下のリンク先記事の方が詳しく分かりやすいと思います。(私もこちらの記事を参考に作業しました)



Windows10で「Smart Indenter」が使用できない原因はシステムファイルに「msaddndr.dll」がないためなので、それをインストールしてやると使えるようになるみたいです。

分からない点があったら、コメントやメールなどでご連絡お願いします。

では。



by th1252 | 2019-03-16 10:59 | Excel | Trackback | Comments(0)

「TH69の亜米利株」Ver.7.1の現況報告です

TH69です。

TH69の亜米利株」に「資産推移」シートを追加し、Ver.7.1として検証中です。

e0382354_15175962.gif

「基準日(C2セル)」と12行目以降のデータ記録欄は「保有株管理シート」更新時に自動更新及び追記します。なお、「基準日(C2セル)」は手打ち変更も可能で、最新記録日以外と過去のデータの比較も可能です。

検索日付の「直近更新日」は「基準日」の前日、あとは文字通りの日付、「年初来」はいわゆる「YTD(Year to date)」となります。空白のA8セルは手打ち検索欄で、自分の好きな日付を入力できます。

検索日の値の取得には「Vlookup」関数を利用していますが、このシートの表は確実に昇順固定ですので第4引数(検索の型)をTRUE(近似値検索)としています。よって検索日付と一致する日付が見つからない場合は、近似値である「日付データの直近の日付」が検索されて値が表示されます。

転職したりして忙しく時間もありませんが、こんな感じで進めていますので何かありましたらコメントなどでご連絡願います。

では。(=^_^=)



by th1252 | 2019-01-20 15:54 | TH69の亜米利株の情報 | Trackback | Comments(2)

VBAコードを自動インデントしてくれる「Smart Indenter」のダウンロード

TH69です。

今回はExcelVBAネタですので、興味のない方は読み飛ばして下さい。

最近、東芝ダイナブックのノートPC(Win7)の動作不安定さからDELLのノートPC(Win10)に換えたのですが、VBAコードを自動インデントしてくれる超便利なExcelVBAアドイン「Smart Indenter」 のインストールがまだだったのでやろうとしたところ…

無い~!(汗)

いつもDLしたソフトのファイルをバックアップ保存しているフォルダに、目的のファイル「IndenterVBA.exe」が見つかりませんでした。こういうありがたい貴重なソフトは必ずバックアップしているのに、なぜかこのファイルは保存されていませんでした。

仕方ないので本家サイトからDLしようとしたところ…

無い~!(汗)

本家サイトの「Smart Indenter」のリンクがリンク切れになっていました(涙)。

それで色々試行錯誤していたところ、グーグル検索で直接目的ファイルの「IndenterVBA.exe」を検索したらダウンロードできるサイトが見つかりましたので紹介しておきます。


1)Auto-download.comのページ「IndenterVBA.exe」を開き、「Download IndenterVBA.exe (server 1)」をクリックします。

e0382354_12281817.gif


2)開いたページの「Get link」ボタンをクリックします。

e0382354_12281836.gif


3)「input Captcha」の入力欄に、表示されているCaptcha(この場合は74899)を入力し、「Download」ボタンをクリックします。

e0382354_12281995.gif


4)すると「IndenterVBA.exe を開く」ダイアログボックスが開きますので、「ファイルを保存」ボタンをクリックしファイルを保存します。

e0382354_12281954.gif


5)「IndenterVBA.exe」がDLできたら、保存先の「IndenterVBA.exe」を実行すると「Smart Indenter」がインストールされます。

でもインストールはちょっと待ってください!!

お使いのPCがWin7までならOKですが、Win10の場合は「Smart Indenter」が使用できないようです。Win10で「Smart Indenter」を使えるようにする方法は次の記事で紹介します。

ホンマ、ネットはありがたいモンですね。

では。(=^_^=)



by th1252 | 2019-01-20 12:49 | Excel | Trackback | Comments(0)

「TH69の亜米利株」Ver6.2bがベクターで公開されましたが…(汗)

TH69です。

TH69の亜米利株」Ver.6.2bがベクターで公開されましたが、若干の不具合のためVer.6.2cとして再度公開申請することにしました。

自宅のPC(Excel2013)では気付かなかったのですが、会社のPC(Excel2003)で開いてみると???という感じでした。

e0382354_08422704.gif

上の画像のように「損益管理」シートの「選択ティッカー更新」ボタンのテキストが、まるで上詰めみたいになっていました(涙)。原因はテキストの最後に改行が入っていたためですが、2013では中央揃えのように見えるため気付きませんでした。

Ver.6.2bは使って頂いても問題ありませんし見た目だけの問題ですが、何となく気分が悪いのでVer6.2cとすることにしました。公開はもう少し先になると思います。

では。(=^_^=)



by th1252 | 2018-07-26 07:37 | TH69の亜米利株の情報 | Trackback | Comments(3)

「TH69の亜米利株」Ver6.2までの主な変更内容

TH69です。

TH69の亜米利株」バージョンアップの動作検証をサリーサさんにお願いしていたのですが、「問題となる箇所はありませんでした」という嬉しい報告を頂きました。不具合やバグがかなり発生することを予想していたので逆にちょっと拍子抜けですが(笑)、若干の不具合修正をしたVer.6.2をベクターに公開申請する方向で進めていくつもりです。

○Ver6.2までの主な変更内容
e0382354_17114062.gif

【Ver.6.0】
いつかは作りたいと思っていた「配当管理」シートを追加しました。内容的には私が見れたら面白そうと思った項目を全て詰め込みました。
e0382354_17113981.gif
シートの項目や集計内容などは前回お知らせしたものと同じです。

また、「配当履歴」シートに「配当単価」欄を追加しました。
e0382354_17114028.gif
「配当単価」は今すぐに利用するものではありませんが、将来的に利用しようと考えていますので入力しておくと何かと便利と思います。


【Ver.6.1】
サリーサさんからご要望のあった株売買時の手数料及び売却益にかかる日本の税金の記録に対応しました。
e0382354_17114012.gif
株購入時の手数料は投資額にプラス、株売却時の手数料は売却額からマイナスし、それぞれ集計や購入単価、売却単価の計算を行います。よって手数料を入力すると購入単価はプラス、売却単価はマイナスの計算値になります。また税金については日本円で入力されたものを米ドル換算し、売却額からマイナスしています。

手数料・税金とも入力は必須ではありませんが、入力することにより実際の取引における資金の流れがより忠実に記録・管理できるものと思います。

ベクターで公開されましたらここでお知らせしますので、ご利用よろしくお願いします。



by th1252 | 2018-06-30 17:52 | TH69の亜米利株の情報 | Trackback | Comments(0)

「TH69の日本株」(仮称)を製作検討中です

TH69です。

日本株の取引履歴、保有株の損益や配当等について記録管理するソフト「TH69の日本株」(仮称)を製作検討中です。投資パンダさんの影響で日本株のIPOに応募し始めたりして、もしかすると日本株を取引する可能性もあるためです。実際に取引を始めてから作り出しても間に合わないので、とりあえずちょっと改造してみようかと…。ベースがあるため、改造にはさほど時間は掛からないと思います。

では。(=^_^=)



by th1252 | 2018-06-14 07:37 | Trackback | Comments(9)

「TH69の亜米利株」で「配当管理」シートが出来上がってきました!

TH69です。

「TH69の亜米利株」で「配当管理」シートがかなり出来上がってきましたので一応報告です。

e0382354_12012939.gif

画像の「配当管理」シートの内容や動作について簡単に説明します。

1)A2セルで配当の税込と税抜を切り替えられるようにした。
2)上記A2セルを空白にするとシート内全ての計算式を空白にするようにした
3)ティッカー更新ボタンを押すと「損益管理」シートにあるティッカー全てを貼り付け後、配当の有無及び配当額でソートするようにした。
4)「対評価額配当率」は現在保有株の評価額に対する配当率
5)「対投資額配当率」はこれまでの全投資額に対する配当率
6)「当年配当」は今年1年間(1月~12月)の配当額
7)現在の月から過去12ヶ月の配当金を表示できるようにした(同月の複数配当にも対応)
8)「12ヶ月配当」は上記12ヶ月分の配当金合計額
9)「税率」は計算上の税率(ADR手数料なども含む)
10)「税額」は計算上の徴収された税金額
11)米ドルと日本円の表示に対応

投資の素人なので用語とかよく分かりませんが、「配当率」というのはその投資に対してどれくらいのリターンがあったか、という意味で使っています。基本は評価額に対する「配当率」で考えています。

またシート内の表示はありませんが、評価額や投資額は入力されたティッカーの合計額となります。保有株すべてのティッカーだと全評価額(全投資額)に対する配当率となります。配当のないティッカーを消去すると、これまでに配当のあったティッカーのみの評価額(投資額)に対する配当率となります。この辺りもどちらがいいのかよく分からないですね。

一応動いてはいますが、動作検証などまだまだこれから先は長いです(汗)。

では。(=^_^=)



by th1252 | 2018-06-10 12:40 | TH69の亜米利株の情報 | Trackback | Comments(0)

「TH69の亜米利株」指定ティッカーの配当推移グラフ作成中です

TH69です。

「TH69の亜米利株」の配当管理シートや配当推移グラフ等を試行錯誤しながら製作中ですが、指定ティッカーの配当推移グラフが結構面白く仕上がってきたので一応報告です。

e0382354_11533829.gif

ティッカー欄に解析したいティッカーを入力すると、そのティッカーの直近から○配当前(まだ決めてません)までの配当を計算・集計してグラフ化します。

項目はグラフ1で「$配当(税抜き)」「税金(ADR手数料等含む)」「株数(配当時の持ち株数)」、グラフ2で「配当単価」「増配率」にするつもりです。面倒臭いのでエラー処理も何もしていませんが、こんな事ができるというのを分かって頂ければと思います。

何かアイデアやご要望などございましたら、コメントお願いします。

では。(=^_^=)

今、配当の累計金額がないのに気付きました(汗)。どこかのグラフに入るかな?
ちょっと考えてみます。




by th1252 | 2018-05-29 12:30 | TH69の亜米利株の情報 | Trackback | Comments(0)