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

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

PDFファイルをExcelに

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

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

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

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

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

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

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

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

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

PDFを開き、

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

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

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

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

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

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

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

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

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

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

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

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

1 設定

まず設定が必要です。

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

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

Excelマクロの概要

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

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

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

から、
・現金及び預金

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

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

Sub FS_convert() 
    
'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") 
    
     
'ーーーー繰り返しーーーー 
    Dim i As Long 
    For i = 1 To Range("a" & Rows.Count).End(xlUp).Row

        
'3 不要部分の除去 
    
        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, " ", "") 
        
        
'4 科目を抽出 
     '科目以外（数値）を判定 
        Reg_Account_Name.Pattern = "[0-9,-]" 
        Reg_Account_Name.Global = True 
           
        
        
    
    '科目以外を/に置換→左右の科目を抽出する準備 
        
        Dim Account_Name As String 
        
        Account_Name = Reg_Account_Name.Replace(Range("b" & i).value, "/") 
           
        If InStr(Account_Name, "/") > 0 Then 
        '左側の科目を抽出 
            Range("c" & i).value = Split(Account_Name, "/")(0) 
            
        '右側の科目を抽出 
            Range("e" & i).value = Replace(Split(Account_Name, Range("c" & i).value)(1), "/", "")

        End If 
        
        
        
        
'5 数値を抽出 
    
     '数値以外（科目）を判定 
    
        Reg_Account_Value.Pattern = "[^0-9,-]" 
         Reg_Account_Value.Global = True 
        
    
    '数値以外を/に置換 
        Dim Account_Value As Variant 
        
        Account_Value = Reg_Account_Value.Replace(Range("b" & i).value, "/") 
        
        If InStr(Account_Name, "/") > 0 Then 
       
        '左側の数値を抽出 
            Range("d" & i).value = Split(Account_Value, "/")(Len(Range("c" & i).value)) 
        
        '右側の数値を抽出 
            Range("f" & i).value = Replace(Split(Account_Value, Range("d" & i).value)(1), "/", "") 
        
        End If 
        
        
'6 調整 
        If Range("c" & i).value = "純資産の部合計" Then 
            Range("c" & i, "d" & i).Cut Range("e" & i) 
             
        End If 
        
        If Range("c" & i).value = "資産の部合計" Then 
            Range("f" & i).value = Range("d" & i).value 
            
        End If 
    
    Next 
'----繰り返し終わりーーー

End Sub

 

読取準備のマクロ

‘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列に入れておきます。

科目を抽出

現金及び預金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新」

とある検査キット

■昨日の娘日記

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