上場株式の終値を自動取得するエクセルファイルを作ってみました

前回の日記で書いた、上場株式の終値を自動取得するエクセルファイルですが、最後まで作ってほしいというご意見をいただいたので、とりあえずやっつけで作成してみました。

タイトルのページで証券コードを入力すると、それに対応する株式などの３か月分の終値などの時系列データを、ヤフーファイナンス様から取得するようになっています。

また取得後、相続日や、相続日以前３か月間の毎月の平均の終値も計算し、最少額を端数切捨てして表示するようにしています。
→このエクセルファイルは、こちらのホームページからダウンロードできます

このエクセルファイルはWebクエリの機能を使って、インターネットを通じてデータを取得するので多少時間がかかるのですが、こういったブラウザ上に表示されるデータを取得する作業がある場合、特に繰り返し同じ作業を行う時には、やはり自動化した方が効率的だと思います。

昔会社員だったころ、毎日夜中にサーバ内で更新される滞留債権の明細が、イントラネットを通じて毎朝ブラウザ上で表形式で表示されるので、それをエクセルに張り付けて担当営業ごとに仕分けして整えた表を、それぞれの営業担当者に配布して督促させるという仕事を振られたことがありました。
毎日同じことを繰り返すのがあまりに不毛なので、今回のファイルのようにWebクエリを使ってデータの取得から担当別への仕分け、印刷までVBAで自動化したところ、多少時間がかかっても人間が手作業でやるよりよっぽど早いですし、パソコンに作業をさせている間自分は別のことができるので、朝一でボタンを押して自動でデータを取りに行かせた後自分は給湯室までお茶を飲みに行くなど(^^；、結構楽ができた記憶があります。

Webクエリは、表などテーブル形式になっているデータしか上手く取得できないという点もありますが、１行程度のコードでブラウザ上のデータが取得できるので、毎日ブラウザ上の表をエクセルにコピーするなどの作業に疲れている方がいらっしゃれば、是非自動化を検討してみてください。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

エクセルで時系列の株価を自動で取得する方法

少し前に事務所に来た相続管理ソフトのダイレクトメールに、「上場株式の評価に必要な株価の最終価額や月平均額を、証券コードから自動算出します」という文言があったので、エクセルでも同じことができるかなとちょっと試してみました。

相続発生日に加えて発生月とその前月、前々月の終値の平均を計算できればいいので、ヤフーファイナンスから該当する三ヶ月間の時系列での株価データを自動で引っ張ってくるよう、Webクエリの機能を使ったマクロを作ってみました。

銘柄ごとにシートを分けて、３ヶ月間の時系列データを取得するまでは上手く動きますね。

後は相続発生日と３ヶ月のそれぞれの平均値を出すようにすれば、利用できそうですね。

と作りながらも何ですが、どうせ相続の際の財産評価は一回限りなので、数銘柄だったら自分でブラウザ動かして取得したほうが早いような気がします(^^;。

ただこのWebクエリの機能を利用すると、ブラウザ上のデータをエクセルにコピーするルーチンワークがあればそういった作業は自動化できるので、上手く使うと効率化につながるかもしれません。

※今月は、相続税のシミュレーションも依頼いただきました。

作りかけの相続税シミュレーションシートも早く作らないと。遊んでる場合じゃない・・・。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

源泉徴収票を２８年版に変更しました

私の事務所のホームページで公開しているエクセルの「給与・賞与・年末調整自動計算シート」。前々から源泉徴収票を、２８年から変わったA5サイズに変更してほしいとのご要望をいただいていたのですが、確定申告も終わって時間ができたので、変更してみました。

サイズが変わっただけですぐに修正できるかと思っていたのですが、意外と面倒で手間取ってしまいました・・・。

扶養控除の対象者の名前にフリガナが必要となったり、扶養控除対象者が別欄になった分摘要の記載事項がシンプルになっていますね。

住宅ローン控除の明細は、ちょっと細かくなっています。

給与支払報告書もA5サイズで印刷できます。

ただマイナンバーは、今のところは手書き仕様です。エクセルでマイナンバーを管理する場合、何かいい方法はあるのかなあ？
まあマイナンバー空欄でも有効は有効なので(^^;、今後の課題です。

※先週からNHKの「Why!?プログラミング」という子供向け番組を見ています。
www.nhk.or.jp
私がVBAに興味を持ったきっかけは、最初に入った会社の先輩が経理の仕事をVBAで自動化して楽をしているのを見たことだったのですが、簡単なことなら文系の人間でもコンピュータを自分の思った通りに動かすことができて、仕事をちゃっちゃと終わらせることができるので、それを知ってもらうきっかけを私も作ることができればなあなんて思ってしまいます。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

進捗表を共有する場合の便利な方法は？

お世話になっている税理士さんが確定申告の進捗管理を、共有サーバに保存したエクセルファイルで行っているという話をFacebookで拝見しました。
私も１０年近く前に会計事務所に勤務していた頃には、同じ方法でボスや同僚と情報共有をしていたのですが、事務所にいないとエクセルファイルが確認できないのが難点でした。
そのころと違って現在はクラウドサービスが普及しているので、無料で外出先でも事務所でも情報共有できるような方法をちょっと検討してみました。

というわけでサンプルとして、こんな簡単な確定申告書の進捗管理表をとりあえず作成。

担当ごとの確定申告書の作成状況を、「資料の受取→決算書作成→申告書作成→申告書提出→申告書控え返却」の５段階に分けて日付を入力していくと、担当者ごとの状況が一番上に集計される形になっています。
またマクロで、日付のセルをダブルクリックすると、今日の日付が自動で入るようになっています。

そしてこのファイルをOneDriveにアップロードし、メンバー限定で共有します（リンク先は誰でも閲覧できますが、共有メンバーはもちろん制限できます）
エクセルファイルへのリンク

アップロードしたファイルは、共有メンバーがこちらのリンクをクリックして、ブラウザ上で閲覧・簡単な編集をしたり、エクセルを立ち上げてマクロを使った編集も行うことができます。

変更後保存をすると、共有メンバー全員で最新のデータを共有することができます。

こうやってスマホのブラウザで確認できるのも便利かもしれませんね。

こういった情報の共有は、チャットワークやサイボウズのようなグループウェアを使った方が簡単かもしれませんが、どうしてもエクセルで考えてしまうのがロートルになってきている証拠でしょうか(^^;。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

税理士は税理士法を知らない！？

今年は１１日で確定申告が完了したので、今週は久しぶりにのんびりとした週末。
ふと日税連のホームページをのぞいてみると、実践税理士法の坂田純一先生が講師の「職業倫理について（第一編）～税理士法上の責任について～』というビデオ研修があったので、つい視聴してしまいました。

新版　実践税理士法

２７年の３月から研修科目に「税理士法その他職業倫理に関するもの」が加わってから、税理士法についての研修はちらほらと出てきたような気がしますが、まだまだ数は少なめですよね・・・。

（研修の科目）
第４条 研修の科目は、次の各号に掲げるものとする。
（１）税理士法その他職業倫理に関するもの
（２）租税法及び会計に関するもの
（３）公益的業務に関するもの
（４）情報処理に関するもの
（５）法律、経済、経営その他税理士の業務の改善進歩及び資質の向上に役立つと認められるもの

（研修の受講義務）
第５条 税理士会員は、第２条に規定する研修を、一事業年度に 36 時間以上受けなければならない。

研修の内容は常識に照らせば子供でも分かりそうな職業倫理と、それに関連する税理士法の基礎の基礎という感じだったのですが、その中でもトホホと思ったのが、懲戒を受けた税理士が一様に「税理士法を知らなかったので、こういう行為が懲戒になるとは思わなかった」という言い訳をするという坂田先生のお話でした。

他の業界の方が聞いたら、税理士が税理士法を知らないというのは冗談のような話ですが、試験科目にもありませんし今まで倫理研修が必須科目でもなかったので、税理士法を全然知らないという税理士は結構いらっしゃるのですよね。
次の産経新聞の記事にも懲戒を受ける税理士の数は増えてきているとありますが、
www.sankei.com
通り一遍でも税理士法を知っていれば、この記事にあるようなことはなかなかできないような気がします。税理士が自分自身を守るためにも、最低限税理士法の概要ぐらいは身に着けておくべきなのでしょうね。

来月の初めには、私の所属する近畿青年税理士連盟大阪支部でも、税理士法の基本について学ぶ研修を開催します。

この研修が、税理士になったばかりの人にとって税理士法に興味をもっていただくきっかけになればなあと思います。

※土曜日は、大阪青年司法書士会の定期総会の来賓にお招きいただきました。

初めての参加でしたが、税理士と同じようなところ、少し温度差があるところが垣間見ることができた一日でした。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です

預金のデータをエクセルで変換して弥生会計に取り込む方法

１月もほぼ終わり、私の事務所もいよいよ確定申告モードに突入です。
普段はほとんど記帳代行をしていない私の事務所も、この時期は記帳作業から逃れられません。
最近は預金通帳を始め様々な会計資料が電子データで取得できるので、お客様にも極力データで送ってもらい、自動で会計ソフトに取り込むことができるようなやり方をできるところから導入しています。

ただお客様からいただいた生のデータは、往々にしてそのまま使うことができません。
たとえばいただいた預金のデータが以下のようなものであった場合・・・。

このデータは、「年月日ごとにデータが分かれている」、「入金・出金のデータが一列になっている」ので、これを弥生会計に取り込むにはちょっとした加工が必要です。

今回の場合は、年月日ごとに分かれているデータを、エクセルで利用できるシリアル値での日付データに変換し、入金・出金の別に金額を別の列に区分してみます。

具体的には、日付は「=date(B2,C2,D2)」とDate関数で結合し、入金と出金ごとに列を分けるには、「=if(A2="入金"、E2、””）」といったIf関数で、入金・出金をキーにして分けます。

そしてこのデータをこちらのホームページからダウンロードできる、「預金通帳明細→弥生会計仕訳日記帳変換シート(弥生会計14以降)」に貼りつけます。
www.kcc.zaq.ne.jp

勘定科目が「未確定勘定」になっていますが、これは摘要に対応する勘定科目をマスターに登録しておくと、自動的に勘定科目を引っ張ってきます。
登録も、ボタン一つで登録できます。

一度登録しておくと、次からは新規の摘要だけを追加するだけで済みます。

これで適用に対応する勘定科目が、自動で入力されました。

あとはボタン一つで、弥生会計に取込むデータを自動で生成します。

今回は預金データを弥生会計に取り込むデータを作成しましたが、電子データさえあれば加工次第で、そのまま会計ソフトに取り込むことができる方法は絶対にあります。

今週会った若い税理士さんにも「小林さんのホームページからダウンロードできるこのエクセル変換シートを顧問先に持って行って、顧問先でダウンロードした電子データを変換して自動で弥生会計に取り込んで、入力作業があっという間に終わります。それでいて記帳代行料は今まで通りもらっているので、すっごくいいですよ！」という感想をいただきました。
こういった感想をいただけると、Excel好き冥利につきますね(*^^*)。Excelがちょっと使えれば、会計や経理の仕事の時間が数分の一、数十分の一になることもあるので、もっとこの便利さを広めていければなと思います。

※今月から弥生さんもレシートをスキャンしてＯＣＲでデータ化し、そのまま弥生会計に取り込むサービスを始めたみたいですね。
cloud.watch.impress.co.jp
実際どこまで使えるのか？領収書を会計ソフトに入力する手間はどこまで減らせるのか？入力嫌いな人間にとっては、興味津々です。

神戸市東灘区御影の会計事務所 小林敬幸税理士事務所です