決算書PDFをExcelデータにするには。Googleドライブ→ドキュメント→Excelマクロ

決算書のPDFを、データにできれば、数字の分析に使えます。
Googleドライブ、ドキュメント、Excelマクロを利用した事例をまとめてみました。

image

PDFファイルをExcelに

PDFファイルは、便利な反面、編集できない、データとして使えないというデメリットもあります。
編集できないのは、メリットではあるのですが、困る場合もあるはずです。

たとえば、決算書をPDFでうけとった場合。
過去の決算書を分析するのは、税理士業務でもコンサルティングでもよくあります。
1年分の決算書を眺めていてもわかることには限りがあるので、複数年のデータを並べたいところです。
しかし、PDFしかないことも多く、PDFをデータにしなければいけません。
(決算書のデータや会計データがあればいいのですが、そうもいかないことも多いです)

PDFをみながら入力することもできますが、大変です。

AdobePDF(有料版)だと、Excelにコンバートできます。

ex-it_01

しかしながら、こんな感じになり、かえって手間です。
規則性があれば、マクロを使えますが、こんな感じだと規則性もなく、使えません。

image

テキストデータにも変換できます。
ちょっとはましのようですが、使うのは厳しいでしょう。


■スポンサードリンク

image

その他、フリーソフトやWebサービスもありますが、こんなときのおすすめは、Googleを使うことです。
(無料Webサービスとかちょっと危険ですし)

PDFをGoogleドライブ→Googleドキュメントで、テキスト化

まず、PDFをGoogleドライブ(https://drive.google.com/drive/my-drive)にアップロードします。

image

PDFを開き、

image

画面上部の[アプリを開く]から[Googleドキュメント](GoogleのWord)を選びましょう。

image

そうすると、Googleドキュメントで、テキストのみが抽出できます。

image

これをExcelへ貼り付ければ、なにかしらできるはずです。

Excelマクロで、科目・金額・科目・金額データを整理

Excelへデータを貼り付けます。

現 金 及 び 預 金 357,978,121 支 払 手 形 13,856,571

といったデータになっているので、このままでは使えません。
科目 金額 科目 金額
とわけたいところです。
通常だと、[区切り位置]で区切りたいところですが、

image

スペースで区切ろうとしても、「現 金 及 び 預 金」にもスペースが入っているので、科目名がバラバラになってしまいます。

image

[フラッシュフィル](Ctrl+E)という便利な機能があり、このようにB列からE列にパターンをつくり、

image

Ctrl+Eを押すと、そのパターンで、データをつくれますが、精度はいまいちです。

image

こういったときにもマクロを使うと便利です。

次のようなデータできあがります。

image

1 設定

まず設定が必要です。

Alt+F11で開いた、VBEで、[ツール]→[参照設定]で、

image

[Microsoft VBScript Regular Expressions 5.5]を探してチェックしてEnterキーで閉じましょう。

image

Excelマクロの概要

今回のマクロは、こういったものです。
さきほどの参照設定をしたのは、正規表現という機能を使うためでした。
正規表現とは、

「現金及び預金357978121支払手形13856571」
というデータから、数値のみ、文字のみを取り出すときの指定方法を考えていただければ。

「現金及び預金357978121支払手形13856571」

から、
・現金及び預金

・支払手形
を抽出し、セルに書き込み、次に
・357978121
・13856571
という数値を取り出してセルに書き込みます。

もっとかっこいいマクロがあるでしょうが、こういった泥臭くパターンをつくって処理する方法もあるという事例を思ってみていただければ。
これでも十分効率化できますし、どの会社にも使えるマクロです。
(紙をスキャンした決算書は、Googleドキュメントでどのくらい読み取れるかによりますし、決算書PDFのつくり方によってはテキストデータがうまくとれない可能性もあります。)

読取準備のマクロ

‘1 科目の読取準備
Dim Reg_Account_Name As Object
Set Reg_Account_Name = CreateObject(“VBScript.RegExp”)

‘2 数値の読取準備
Dim Reg_Account_Value As Object
Set Reg_Account_Value = CreateObject(“VBScript.RegExp”)

ここで、読取の準備をしています。

不要部分の除去

以降は、繰り返しです。
Forで、1行目から、データの数だけ繰り返します。

ーーー繰り返しーーーー
Dim i As Long
For i = 1 To Range(“a” & Rows.Count).End(xlUp).Row

まず、不要な部分を取り除きます。

【流動資産】 468,176,053 【流動負債】 314,451,909

現 金 及 び 預 金 357,978,121 支 払 手 形 13,856,571

この【や】がのちほど邪魔になるので、Replaceで、取り除きましょう。
Repalceは、Replace(〇、△、□)で、〇の中の△を□に変えるという使い方です。

また、「,(カンマ)」と科目中の空白も邪魔なので消しましょう。

Range(“b” & i).value = Replace(Range(“a” & i).value, “【”, “”)
Range(“b” & i).value = Replace(Range(“b” & i).value, “】”, “”)
Range(“b” & i).value = Replace(Range(“b” & i).value, “,”, “”)
Range(“b” & i).value = Replace(Range(“b” & i).value, ” “, “”)

取り除いた結果は、B列に入れておきます。
image

科目を抽出

現金及び預金357978121支払手形13856571

から、科目、現金及び預金、支払手形を抽出していきます。

今回の事例、2つ、科目があるのが曲者で、うまくいかず、次のような方法でやりました。

Reg_Account_Name.Pattern = “[0-9,-]”
Reg_Account_Name.Global = True

まず、科目以外、数値を判定します。
[0-9]というのが数字です。
そのあとの ,-は、一部マイナスがあるので、同様に入れています。

Reg_Account_Name.Global = True

は、文字全体を検索するという意味です。

次に、 科目をAccount_Nameという変数に入れていきます。

Account_Name = Reg_Account_Name.Replace(Range(“b” & i).value, “/”)

で、数値を判定して、それをReplaceで、/に変えました。
結果こうなります。

現金及び預金/////////支払手形////////

左側の科目は、Splitを使って、これを/で区切って、その1つ目(0、1、2、3・・と数えるので、0と指定)を取り出します。

Range(“c” & i).value = Split(Account_Name, “/”)(0)

スペースを区切りと考えると、こうなっているので、

現金及び預金 / / / / / / / / / 支払手形 / / / / / / / /

このうち、1番目を取り出すのです。

右側は、困ったものです。何番目というのがわかりません。
そこで、Range(“c” & i).value、つまり左側の科目を使います。

Range(“e” & i).value = Replace(Split(Account_Name, Range(“c” & i).value)(1), “/”, “”)

現金及び預金/////////支払手形////////

を、左側の科目(現金及び預金)で区切り、2番目を取り出す(1と指定)

/////////支払手形////////

/を取り除く(Replace)

支払手形

という感じです。

これで右側の科目を取り出せました。

ここで、数値がない=/がないとエラーになるので、IFを入れて、Instrで、/を含んでいたら、という条件を付けます。
If InStr(Account_Name, “/”) > 0 Then

End If

科目は、C列、E列に入れます。

数値を抽出

数値も同様に、 数値以外(科目)を判定します。
[^0-9,-]   で、数値と―以外という意味です。

Reg_Account_Value.Pattern = “[^0-9,-]”
Reg_Account_Value.Global = True

左側の数値を抽出します。

数値以外を/に変えるとこうなるので、ここから数値のみを取り出すのですが、やややっかいです。

//////357978121////13856571

Splitで区切るにしても、/の位置がわかりません。
そこで、Range(“c” & i).value、つまり、左側の科目「現金及び預金」を使います。

「現金及び預金」は6文字、
//////も6文字なので、
//////357978121////13856571のうち、/で区切ったときに数値があるのは、7番目なので、6と指定すればいいわけです

Len(Range(“c” & i).valueとすれば、その6を得られます。。
(このときのために【と】を除去しました。文字数カウントがずれるので)

Range(“d” & i).value = Split(Account_Value, “/”)(Len(Range(“c” & i).value))

右側は、科目と同様に、左側の数値で区切って、Splitで2番目(1と指定)を取り出しました。
Range(“f” & i).value = Replace(Split(Account_Value, Range(“d” & i).value)(1), “/”, “”)

数値は、D列、F列に入ります。

調整

最後に調整を加えています。
純資産の部合計が単独で右側にあるとき、科目を数値も左側に来てしまいます。
(左側が空欄なので)
それを切り取って、右側に移動させる調整です。

If Range(“c” & i).value = “純資産の部合計” Then
Range(“c” & i, “d” & i).Cut Range(“e” & i)

End If

また、左と右が同じ数値だと、右側の数値が取り出せないので、

資産の部合計517342460負債及び純資産合計517342460

のところは、右側の数値の左側の数値を入れるようにしています。

If Range(“c” & i).value = “資産の部合計” Then
Range(“f” & i).value = Range(“d” & i).value

End If
特殊な事例ではあるのですが、
・PDF→Excel
・Excelマクロでの正規表現
そして、
・手打ちしたら負けの精神

の参考にしていただければ。


■編集後記

昨日は、お客様の決算を中心に。
新規のお客様の決算は、過去のデータの確認、チェックから始まります。
今回の事例も使い、PDFを5期分データにして、分析資料をつくりました。

■昨日の1日1新
※詳細は→「1日1新」

とある検査キット

■昨日の娘日記

昨日、保育園帰りに、手をつないで横断歩道を渡っていたら、信号の青が点滅。
それを見た娘が小走りしだしました。
たまたまかもしれませんが、そういうのがわかるんだと感心。


■スポンサードリンク ■井ノ上陽一のプロフィール
■スポンサードリンク



井ノ上陽一のVALU
■著書
【監修】十人十色の「ひとり税理士」という生き方
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』