夢見る税理士の独立開業繁盛記

神戸市東灘区で開業している駆け出し税理士の、試行錯誤日記

事務所ホームページはこちらです→神戸市東灘区御影の会計事務所 小林敬幸税理士事務所

取引金額をエクセルに自動で見つけさせるのが山場?

前回の日記で「取引先名はWeb-APIを・・・」などと書いてしまいましたが、とりあえず「リストにある取引先名にあてはまるものがあれば、その取引先名を引っ張ってくる」という安直な機能にしてしまいました(^^;。


ついでに備考についても、「領収書」とか「請求明細書」とかリストにある名前があてはまれば、それを引っ張ってくる機能をつけてみました。

この領収書の場合、「ロボステップストア」というリストにある取引先を抽出し、「領収書」というリストにある書類名を備考に抽出します。


Amazonの領収書の場合はこんな感じ。


あとは取引金額をどうやってエクセルに自動で見つけさせるかですね。これさえクリアしたら、あとは「日付」「取引先名」「取引金額」の要件を満たすエクセルの検索リストを作って、リネームしたPDFファイルを保管するだけなので、そんなに難しくなさそう。
果たしてこの課題、クリアできるかしら・・・?


兵庫県から「兵庫県中小企業等原油価格・物価高騰対策一時支援金」の申請受付が始まったとのお知らせが。
web.pref.hyogo.lg.jp
支給要件は以下の通りのようですが、該当する方は結構多そう・・・。

主な支給要件
次の要件をすべて満たす者:詳細は募集要項のとおり

  • 次のアまたはイを満たすこと

ア 国の事業復活支援金を受給していること
(対象月:令和3年11月分から令和4年3月分までのいずれかの月)
※国の事業復活支援金受給後に、同支援金の不給付要件に該当することが判明した場合等、同支援金を受給していても、審査により本兵庫県中小企業等一時支援金が不支給となる場合があります。
イ 兵庫県の経営円滑化貸付(原油価格高騰、原材料価格高騰)を借り受けていること
※金銭消費貸借契約を締結した段階から対象とします。

  • 次の所在地・住所地が国の事業復活支援金対象月末日に兵庫県内にあること

ア 中小法人等にあっては、法人の本店の所在地
イ 個人事業主にあっては、事業主本人の住所地

  • 令和3年11月以降の燃料費、光熱水費及び原材料価格高騰の影響を受けていること
  • 事業継続に向けた取組みを行っている、又はその意思があること


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

取引先名はWeb-API機能を使うべきなのかなあ

前回の日記から続く、PDFファイルから電子取引データをある程度自動で抽出して、検索機能を付けて保存できるかトライしている件。
仕事の合間に亀のようにちびちびとやっているのでなかなか進みませんが、PDFファイルをドラッグアンドドロップすると、そのPDFファイルを表示して、日付を抜き出してくるところまで形になってきたかなあという感じです。


PDFファイルをエクセルにドラッグアンドドロップすると、右下にPDFファイルを表示して、取引日付を「年、月、日」に分けて表示するようにしてみました。

www.youtube.com


あとは取引先名と取引金額をどうやってエクセルに抜き出させるかですよね。
取引先名は、PDFファイルにインボイス番号が記載されるようになったら、リンク先の「適格請求書発行事業者公表システムWeb-API機能」を使って、かなりの精度で抜き出せるような気がするのですが。
www.invoice-kohyo.nta.go.jp
現状は当然まだインボイス番号の記載がないので、別の方法を考えないとダメですよね・・・。


もう一つの課題は取引金額。
これも引っこ抜いたPDFデータには、数字データも下記のように色々あるので、金額のうち取引金額になるものをどうやって抜き出せばいいのか・・・

まだまだ先は長い・・・。


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

取引年月日を抽出するには・・・?

前回に引き続き、PDFの請求書・領収書データから取引年月日を自動でエクセルに抽出させる件。
前回は、PDFから引っこ抜いたテキストデータから日付だけを抽出することができたので、今回はその日付の中から取引年月日に該当するものをピックアップすることを考えてみました。


PDFからテキストデータを引っこ抜く際、データの一覧と一緒にPDFのレイアウトにそったデータも次のように抽出しているので、日付セルとその周辺のセルのデータを使えばうまくいくでしょうか?

日付が含まれているセルの前後の文言。それに日付セルの上下左右のセルの文言を抽出してみると、以下の通り。

取引年月日の正解は「2022/5/19」ですが、同じセルに含まれている「注文日」に着目すれば、これをキーにうまくピックアップできるかなあ・・・。


同じようにアマゾンの領収書から引っ張ってきたデータは、次のような感じ。

正解は「2022/4/21」ですが、左のセルに「注文日」という文言があるから、これも「注文」をキーに引っ張ってこれそうな気がしますね。
もうちょっと考えてみたいと思います。


※今日はチェーンの居酒屋でランチ。

最近居酒屋にランチに行くと、お昼でも何となく店員さんや店の中の雰囲気がウキウキ華やいだ感じがするんですよね。
お客さん、だいぶん戻ってきているのかしら?


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

正規表現って難しい・・・

前回の日記の続きで、PDFから電子帳簿保存法の要件を満たす索引簿をエクセルで自動で作る件。とりあえず日付を抜き出す方法を考えてみました。


PDFから文書データを引っこ抜いてみると、元のフォーマットはバラバラなので、日付の部分も次のような感じでいろいろな形で抜き出されてきます。

発⾏⽇︓2022年05⽉30⽇
注⽂⽇︓2022年05⽉19⽇

): 2022年4月21日:¥ 1,497

人間の目で見ると、どの部分が日付というのはすぐにわかりますが、エクセルに日付と判断させるにはどうしたらいいのか・・・?
Google先生に聞くと「正規表現」というものがあるということなので、この本で勉強してみました。


正規表現とは、書き方に揺れがあるけれど一定のルールがある表現を、そのルールに基づいて表したものということみたいです。
たとえば日付の場合、

20220609
2022/06/09 0:00
2022.6.9
2022/06/09
2022年6月9日
2022-6-9
令和4年6月9日
R4.6.9

のようにいろいろな書き方があるけれど、「西暦や和暦で始まって、日付で終わる」というパターンがあるから、人間の目で見たらすぐに日付と分かります。
それでは同じようにエクセルにも、この正規表現を使って、これらを日付と認識させることはできるのでしょうか・・・?


とりあえず以下のような文字列から、エクセルに日付だけを抜き出させることができるかやってみました。

あいうえお20220609かきくけこ
あいうえお2022/06/09 0:00かきくけこ
あいうえお2022.6.9かきくけこ
あいうえお2022/06/09かきくけこ
あいうえお2022年6月9日かきくけこ
あいうえお2022-6-9かきくけこ
あいうえお令和4年6月9日かきくけこ
あいうえおR4.6.9かきくけこ
あいうえお
かきくけこ
さしすせそ
20220609かきくけこ
2022/06/09 0:00かきくけこ
2022.6.9かきくけこ
2022/06/09かきくけこ
2022年6月9日かきくけこ
2022-6-9かきくけこ
令和4年6月9日かきくけこ
R4.6.9かきくけこ
あいうえお20220609
あいうえお2022/06/09 0:00
あいうえお2022.6.9
あいうえお2022/06/09
あいうえお2022年6月9日
あいうえお2022-6-9
あいうえお令和4年6月9日
あいうえおR4.6.9


「日付の前に文字があったりなかったり。そのあとに『20か令和かRで始まり、数字2桁で終わって』、その後にも文字があったりなかったりする」というイメージで、以下のような正規表現を上記の表現に当てはめてみます。

Regex.Pattern = "(.*?)(R\d{1}.*|令和\d{1}.*|20\d{2}.*[\-|\.|\d{2}])(.*)"


具体的なマクロはこんな感じ。

Sub 作業シートの日付データを整理()
'
' 作業シートの日付データを、正規表現を使って日付だけを抽出し、日付フォーマットに変換
'

'変数宣言
Dim i As Long
Dim Lastrow As Long
Dim Regex As Object
Dim Matches As Variant


'正規表現の設定
Set Regex = CreateObject("VBScript.RegExp")

Regex.Pattern = "(.*?)(R\d{1}.*|令和\d{1}.*|20\d{2}.*[\-|\.|\d{2}])(.*)"




'「作業シート」のA列のデータ数を取得
With Sheets("作業シート")
Lastrow = .Cells(1, 1).End(xlDown).Row

'「作業シート」のA列のデータを、正規表現に該当するかどうか、マッチ実施し、結果をB~E列に表示
For i = 1 To Lastrow
Set Matches = Regex.Execute(.Range("A" & i))
If Matches.Count > 0 Then
.Range("B" & i).Value = Matches(0).Value
.Range("C" & i).Value = Matches(0).submatches(0)
.Range("D" & i).Value = Matches(0).submatches(1)

.Range("E" & i).Value = Matches(0).submatches(2)
End If

Next


End With

End Sub

このマクロで上記の文字列に正規表現のパターンを当てはめてみると、日付を含むデータだけを抽出して、そこからさらに日付だけを抽出してくれます。

あとはこのD列の文字列を日付形式に加工すれば、使えるようになるかなあ。

でもこの正規表現、「こう書いたらこう動く」というのがいまいちよく理解できないんですよねえ。難しい・・・。
今まで使ったことがなかったので、今後もあまり使わないような気もするけど、もうちょっと勉強しないとダメかなあ。


ちなみに前回の領収書にあてはめると、以下の四件を抽出します。

次はこの中から、どうやって取引日を判定させるかですよね。正解の取引日は「2022年5月19日」だけど、これをエクセルにどうやって判定させるか・・・。
前後左右にある文言などから、条件にはまるものを選ぶのがいいのかしら?


※今月の近畿税理士会の会報誌では、電子帳簿保存法への対応方法として、取引先に「取引年月日・企業名・取引金額をPDFデータ名に付してもらう」方法が紹介されていました。

お願いして対応してもらえる取引先がどれぐらいあるのか・・・?これも難しそうですよね・・・。


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

電子取引データの保存はどうするのが効率的かな?

先日支部の役員会に出席した際、同じ支部の税理士さんから「小林さんのところは電子帳簿保存法への対応って、どうやってますか?」と聞かれることがありました。
「電子取引データの保存の部分が課題ですけど、これはという方法で対応できていないですよね~。本会の情報システム部でいい研修をしてくれたらいいのですけど・・・」などと言っていたのですが、令和6年1月の義務化が迫っているのに何も出来ていない状況は我ながら褒められた状況でもないので、どうするのがいいんだろうと時々考えながら答えが見つけられずにいます。


この電子帳簿保存法、保存対象となるデータを以下の3つに区分して、それぞれのルールが定められています。

  1. 「電子帳簿等保存」(会計ソフトなどで作った帳簿をデータで保存)
  2. 「スキャナ保存」(紙で受け取った書類をスキャンして、データで保存)
  3. 「電子取引」(データで受け取った取引データを、データで保存)

続きを読む

小規模な会社の間接費は仕掛品に配賦しなくていい?

最近エクセルで簡易な個別原価計算の管理シートを作成しているので、個別原価計算の参考書などをめくって勉強しています。

勉強しながら少し悩むのは、間接費の配賦をどの程度やるべきなのかなあということ。
個々の案件に直課できる原価はこのシートで管理できるようになっていますが、間接費についてはこのシートでは管理できないので、配賦するためには別の方法で管理する必要があります。
間接費の配賦は工数管理など行って、案件ごとに配賦することが利益管理の面でも税務的にも大事ですが、そもそも一部屋で数人とかでやっているような会社の場合、工数管理はおろか原価と販管費も混然一体になってしまっているようなこともままあるような気がしますし、そういう場合はどうやって間接費を完成原価や仕掛品に配賦するのがいいのでしょうか?

続きを読む