2012年8月17日金曜日

預金通帳をバラバラにしてみる、という発想


預金通帳をバラバラにする。

はぁ?・・・・・って感じだが・・・・・。

まぁ、ちょっと思い付きレベルの話。


本題に入る前にとりあえず。

法人でも個人でも、事業上の取引の多くは銀行の預金口座を通して行われる。
当然その取引履歴は会計ソフト等への入力によって会計帳簿に記録されることになる。

古くは、預金通帳を見ながら、預金出納帳などを作成したり振替伝票を起こしたりし、それらを元に会計帳簿へ転記していた。
その後、PC・会計ソフトの普及により、通帳から直接会計ソフトへ入力するように。
で、イマドキは、ネットバンクを利用していれば、最初からCSVデータで取得できるので、少し加工して会計ソフトに取り込む、という感じだろうか。

何らかの理由でネットバンクが利用できない場合、預金通帳をデータ化するか、通帳を元に会計ソフトへ直接入力必要がある。
預金口座の内容がデータ形式(CSV等)になっていると、直接会計ソフトへ入力するのに比べ、Excel上で加工できるので非常に効率的。

例えば、通帳に記載された摘要欄の内容毎にソート(データの並び替え)をかけて、同じ内容の取引を一気に処理してしまうとか。
あるいは、通帳に記載された摘要欄の内容をキーとして、登録された勘定科目を自動的に割り当てる、なんてことも。
また、会計帳簿の摘要欄に入力する内容も、Excelの一括置換機能を使ったり、やはり登録された摘要文を自動で割り当てたりとか。

だから、ネットバンク契約がなくても、なんとかこの預金通帳の内容をデータ化したい、と思うわけである。
一度ネットバンクのデータを加工して取り込むという作業を体験してしまったら、紙の通帳から直接会計ソフトへ入力(仕訳)するという作業は二度とやりたくなくなる。
そのために通帳のOCRによるデータ化などにもチャレンジしたりしてる。



で、本題。

今回は、「預金通帳の会計処理」ではなく「預金通帳のデータ化」を外注する、と仮定した場合の話。

「できれば自分で会計処理したい。でも自分で全部入力するのメンドクサイ。でも外注すると中身を見られるのでハズカシイ。でも自分でOCRもなんかメンドクサイ。あ〜、データにさえ、データにさえなっていれば・・・」ってケースを考えてみる。

そんな時に、通帳をバラバラにするのはどうだろうか・・・・と。

もちろん、通帳そのものをシュレッダーやハサミで切るということではない。
通帳をスキャンして、そのPDFファイルをバラバラにする、という意味だ。
下記ABCDはそれぞれ別のPDFファイルとなる。


このように、通帳を4つのファイルに分割し、たとえばそれを別々の外注先へデータを依頼する、とか。

この方法だと次のようなメリットが。
  1. 通帳の中身(個別の取引金額や残高、これらの時期など)を把握されずにデータ化できる。
  2. 外注先に残るデータが万が一流出してもダメージが少なく済む。
  3. 通帳を元に会計ソフトに入力するのに比べ、外注先に簿記会計の知識・教育が不要。
  4. 外注先に会計ソフトなどを用意する必要がない。
  5. データ化の処理も、日付なら日付、残高なら残高だけをひたすら処理すればテンキーだけでOK。
  6. ページ毎の通帳よりも、属性が限られた一列のみの通帳の方が、OCRの精度が上がるかも知れない。

逆に以下のような問題もあるか。
  1. 分割データを用意するのにちょっと手間。
  2. バラバラに外注してデータ化されたものを結合するのにちょっと手間。
  3. 外注先でデータが正しく入力できたかの検証がしづらい。
  4. バラバラに分割されたファイルのファイル名にひと工夫必要。

もちろん、ある特定の外注先に4つのファイルを同時にまとめて外注したら意味がない、というのは言うまでもないが。



先ほど「自分でやりたいけど中身見られるのがハズカシイ」というケースを想定したが。
会計事務所などが顧問先から依頼を受けた記帳業務の一部再委託などに利用できないか、というのが本来の目的。

もちろん、万が一流出しても取引内容が分からないから、というだけですぐに再委託がOKになるものでもない。
が、取引内容が分からず、万が一流出してもダメージの少ないこのような方法なら、顧問先の理解・承諾は格段に得やすくなるのではないだろうか。

以前、顧問先に無断で再委託、しかも中国にある会計センターへ丸投げし、問題となっているケースをネット上で見かけたが。
会計事務所の立場とすれば、事前の承諾を得るか、そもそもその再委託先と顧問先の直接契約としてもらうか、などの対応をすべきだろう。


まだまだ思い付きレベルで、難しい部分もありそうだが。
とりあえず、ファイルをバラバラにするという作業は簡単に出来る。
その方法や注意点などの解説をまた後日にでも。


P.S
通帳のスキャンならScanSnap S1100がオススメ




2012年8月16日木曜日

預金通帳のOCR読取後のExcelデータ加工

こちらの記事を始め、その後も数回にわたって解説している、預金通帳のOCR読み取り。
最終的にはその読み取ったデータを会計ソフトにインポートするのであるが。
そのままインポートできるわけではなく、ある程度Excel上で加工が必要だ。


OCRソフトで読み取ったデータをそのままExcel上に取り込むと、上記図の通り、ひとつの列に数値(金額)と文字列(摘要)が混在することになる。
預金通帳は、その限られたスペースを有効に使うため、引出欄または預入欄にその金額が印字されるだけでなく、空いたスペースに取引の内容を示す摘要が印字されるからだ。
このままでは扱いが難しいので、これを数値と文字列に分ける必要がある。

また、OCRで読み取ったデータは100%合ってるとは限らない。
基本的には数値(金額)重視でOCRをかけているのだが、それでも誤認識は避けられない。
数値が違ったまま会計ソフトにインポートするというのは致命的であり、Excel上でしっかりと合わせておかなければならない。
そのための検証・修正作業も必要になってくる。

もちろん数値以外の日付や摘要も確認・修正が必要なのだが、そちらについては今回はとりあえず説明を省略する。


◆数値と文字列を分ける

これを分けるためには、Excelの「T関数」というものを使う。

詳しい解説は上記リンク先にあるが、要は「参照セルが、文字列だったらそのまま、文字列以外(数値等)なら空白」となる関数のようだ。
文字列か否かを判定してくれるので、後は「IF関数」を組み合わせて、これらのデータをしっかりと分ける。


先ほどのT関数とIF関数を使い「参照セル(C2)が文字列以外ならその数値、そうでないならゼロ」という算式を入れ、後はその式を下にドラッグコピーすれば良い。

もちろん預入欄も同様である。

そして、摘要欄も同じような感じで抽出する。


ちなみに、「残高2」の欄は次の検証のために設けた列である。



◆読み取った数値の検証

検証作業といっても大したことをする訳ではない。
「通帳の残高欄をOCR読み取りした数値」と「引出欄・預入欄の数値から計算される残高」、これが合っているかどうかを確認するだけだ。

元の通帳がこちら。


こちらがOCR結果をExcelに貼付け加工・検証したもの。

①は、通帳の「,(カンマ)」を「9」と認識してしまった結果、「1,000円」「1,100円」がそれぞれ「19,000円」「19,100円」となってしまったケース

②は、通帳の正しい残高が「29,328円」のところ、「293,281円」と末尾に余計な「1」がついてしまったケース

③は、①と同様に「,」を「9」と認識してしまったため、「4,702円」が「49,702円」となったケース

④は、「0(ゼロ)」を「9」と認識してしまい、「20,000円」が「29,000円」となったケース

⑤は、通帳残高が「89,502円」のところ、頭の「8」が認識されず、「9,502円」となったケース


このように、残高欄の数値がOCRで誤認識されていたり、引出欄・預入欄の数値が誤認識されていれば、検証欄がゼロにならない。
これを元に、該当箇所を原資料と突合して必要な修正をし、検証欄が全てゼロとなるようにする。

上記の例では、かなり古い通帳(20年以上前の三井銀行)であり、OCRソフトの辞書もまっさらなものを使っているため認識精度がイマイチだが、ちゃんと設定すれば修正が必要な箇所はそれほど多くはならないはず。



慣れてきたら、いちいち算式を入力してコピーしなくても、マクロを使って少しは楽に出来るかも。

ただ、金融機関によって通帳のレイアウトは異なる。
残高欄が毎行記載されず、同日の取引の最終行のみ記載されるものや、通帳に「摘要欄」などがさらに別途設けられているものなど。
そのような場合には上記で紹介した算式も少し工夫が必要である。

ま、とにかく、これでようやく扱いやすいデータとなる。