銀行、クレジットカード、POSデータを照合：インポート＆照合

銀行明細書に「J. Smith CR $450」と表示されている。クレジットカード決済代行業者には$3,200のバッチ入金が表示されている。POSシステムには47件の個別顧客注文がリストされている。どの注文がJ. Smithの取引に関連しているのか、その山の中のどこかにあるはずだが、どれなのか。複数のデータソースが異なる形式で毎日数十件の取引を処理する場合、このマッチング問題は増幅される。

銀行フィード、SquareやStripeなどのクレジットカード決済代行業者、POSシステムなど複数のソースからの支払いを照合することは、取引説明が一致せず、参照番号が引き継がれない場合、探偵の仕事に変わってしまう。銀行明細書は統合されたバッチ金額を表示し、決済代行業者は決済日でグループ化された取引を表示し、POSは個別の販売タイムスタンプを記録する。1つのソースは顧客名を使用し、もう1つはトランザクションIDを使用し、3番目は請求書番号を使用する。

このガイドは、セル単位での手動比較やIT部門が対応する必要があるカスタム統合プロジェクトなしに、複数のシステム間で取引をインポート、標準化、マッチングするための実践的な方法を説明している。

複数のソース間での取引マッチングが難しい理由

各システムは独自の構造でデータをエクスポートする。銀行明細書（PDFまたはCSVダウンロード）は通常、支払者の名前と四捨五入されたドル金額を示す。クレジットカード決済代行業者は、マーチャント手数料が既に差し引かれたバッチ決済ファイルをエクスポートし、取引日ではなく入金日でグループ化する。POSシステムは、手数料が差し引かれる前に、小計の販売額と商品の詳細を記録する。

タイミングのずれが形式の問題を複雑にしている。顧客は月曜日に支払い、決済代行業者は火曜日にバッチ処理し、銀行は水曜日に転記する。一方のシステムで取引日により、他方で入金日により照合する場合、同じ$450の支払いが一方のソースでは紛失し、他方では説明のつかないものとして表示される。

一般的な説明がこれを悪化させる。銀行明細書が元の注文番号の参照なしで、人の名前または「クレジットカード入金」のみを表示する場合、金額と日付を相互参照しながら複数のウィンドウを開かずにPOSレコードにトレースバックすることはできない。1日50件の取引を処理するビジネスの場合、月間1,000件のルックアップになる。

3つのソースすべてで共通フィールドで開始する

マッチングを成功させるには、3つのエクスポートすべてに表示される少なくとも1つのフィールドが必要である（異なる名前でも構わない）。トランザクション金額と日付は普遍的だが、同じ日に異なる顧客が同じドル金額を支払う場合、金額だけでマッチングすると誤マッチが発生する。

チェーン全体で伝播する参照番号を探す。一部のクレジットカード決済代行業者は、決済代行業者のエクスポートと銀行明細書のメモフィールドの両方に元のトランザクションIDを含める。たとえば、Squareは銀行入金にトランザクションコードを追加する。POSシステムが決済代行業者が銀行に渡す支払いメモに参照番号を含めることができる場合、それがマッチングキーである。

参照番号が伝播されない場合は、複合キーを作成する：トランザクション金額、日付、支払者名の最初の数文字を組み合わせる。3月15日のJ. Smithからの$450.00の取引は450.00-2025-03-15-JSMになる。各ソースファイルでこのキーを作成し、それでマッチングする。

各システムからクリーンなデータをエクスポートする

何かをマッチングできるようにするには、使用可能な形式の生データが必要である。銀行明細書はPDF形式、クレジットカード決済代行業者は通常CSVダウンロードを提供し、POSシステムはさまざまである。

銀行明細書

銀行がオンラインバンキングを通じてCSVまたはOFXダウンロードを提供する場合は、それを使用する。既に表形式である。PDF明細書（特にスキャンまたは画像ベースのもの）の場合、抽出は複雑になる。Tabulaなどの無料ツールは、クリーンなデジタルPDFに対しては機能するが、テキストが実際には画像であるスキャンされた明細書では失敗する。OCR誤差により、$1,250.00が$1.250.00に変換されたり、借方と貸方の列が入れ替わったりする。

BankStmtConverterは、スキャンおよび撮影された明細書をAI抽出で処理し、列構造を保持し、抽出されたバランスを印刷された開始残高と終了残高に対して検証し、OCR誤差を照合に到達する前に把握する。元の銀行のレイアウトに関係なく、一貫した列ヘッダーを使用してCSVまたはExcelにエクスポートする。これは複数の銀行からのステートメントをマージする場合に重要である。

クレジットカード決済代行業者

決済代行業者のダッシュボードにログインし、取引または決済レポートをダウンロードする。照合期間に一致する日付範囲を選択する。CSVとしてエクスポートする。ほとんどの決済代行業者は、トランザクションID、総額、手数料、純額、支払者の詳細などのフィールドを含めることができる。すべてを含める。後でフィルタリングする。

POSシステム

同じ日付範囲の販売レポートをエクスポートする。クレジットカード取引のみをフィルタリングできるように支払い方法を含める（現金と小切手は銀行入金に別途マッチングされる）。注文番号、顧客名（キャプチャされている場合）、販売総額、トランザクションタイムスタンプを含める。

ExcelまたはGoogleシートで列を標準化する

各エクスポートをスプレッドシートで開く。新しいワークブックを3つのシートで作成する：銀行、決済代行業者、POS。各ソースをそのシートにコピーしてから、列ヘッダーを標準化する。

列の名前を変更してシート全体でマッチングする。銀行明細書に「Description」があり、決済代行業者に「Customer Name」がある場合、両方を「Payer」に名前変更する。銀行が「Amount」を表示し、決済代行業者が「Net Settlement」を表示する場合、マッチング対象の金額（手数料後の総額または純額）を決定し、必要に応じて計算列を作成する。

各シートに「Source」列を追加し、「Bank」、「Processor」、「POS」で埋める。マージ後にレコードを識別できるようにするためである。

一部のソースがMM/DD/YYYYを使用し、他のソースがDD/MM/YYYYを使用する場合は、=TEXT(A2,"YYYY-MM-DD")を使用して日付を一貫した形式に変換する。Excelの日付解析は曖昧な形式を誤解釈する可能性があるため、すべてISO 8601に強制する。

複合キー列を作成する：=TEXT(B2,"0.00")&"-"&TEXT(C2,"YYYY-MM-DD")&"-"&LEFT(D2,3)。ここでB2は金額、C2は日付、D2は支払者名である。この式を3つのシートすべてにコピーする。

VLOOKUPまたはPower Queryでマージしてマッチングする

「Reconciliation」という4番目のシートを作成する。銀行は明細書があなたのアンカーなので、銀行レコードを最初にコピーする。銀行が表示するものを説明しようとしている。

「Processor Match」列を追加する。=VLOOKUP(E2,Processor!E:G,2,FALSE)を使用する。E2は複合キーで、Processor!E:Gはキー列と取得したい列（決済代行業者のトランザクションIDなど）である。VLOOKUPが#N/Aを返す場合、その銀行取引には決済代行業者のマッチがない。

「POS Match」列をPOSシートに対する同様のVLOOKUPで追加する。

複数のクレジットカード取引を1行にバッチ処理する銀行の場合、1つの銀行レコードが$3,200で、複数の決済代行業者レコードが$3,200に合計される。その場合、Processorシートを関連する日付範囲にフィルタリングし、純額を合計し、合計が銀行ラインと一致することを手動で確認する。そのバッチ入金をワークブックの「Batch Reconciliation」セクションに記録する。

ExcelのPower Queryは、これに精通している場合、より優雅に処理する。3つのソースをすべてクエリとしてインポートし、複合キーにマージし、マッチした列を展開する。マッチされていない行は、結合した列にnull値が表示され、例外が簡単にわかる。

一般的なマッチング失敗を処理する

クリーンなデータでも、一部の取引は自動的にマッチングされない。通常の原因と修正方法は次のとおりである。

タイミングの違い

決済代行業者は3月15日を表示し、銀行は3月17日を表示する。マッチウィンドウを広げる：複合キーで日付を省略し、金額と支払者名のみでマッチングしてから、日付が2〜3日以内であることを手動で確認する。または3月15日、16日、17日のキーを作成し、3つのVLOOKUPを実行する。

手数料と四捨五入

顧客が$100を支払い、決済代行業者は$3.20の手数料を取り、銀行は$96.80を入金した。POSは$100を表示し、銀行は$96.80を表示し、一致していない。POSシートに「Net Amount」列を作成して、典型的な決済代行業者の手数料を差し引いてからマッチングする。または、POSと決済代行業者で総額でマッチングし、決済代行業者の純額を銀行でマッチングする。

名前と説明

銀行は「Smith, John」と言うが、POSは「John Smith」と言う。複合キーが壊れる。名前を正規化する：姓と名を分割し、アルファベット順にして、再度結合する。または金額と日付のみでマッチングしてから、名前が十分に近いことを手動で確認する。

参照番号の欠落

参照番号が伝播せず、同じ日に異なる顧客からの複数の$100の支払いがある場合、マッチングは曖昧になる。それらのケースは手動確認に頼るが、次の月の照合が速くなるように使用した基準を文書化する。顧客に対し、今後の支払いメモで注文番号を含めるよう要求することを検討する。

スクリプトまたは照合ソフトウェアで自動化する

毎月同じソースを照合する場合、これらの手順を手動で繰り返すことは時間がかかる。Excelマクロまたはpythonスクリプトは、インポート、キー生成、VLOOKUPプロセスを自動化できる。

pandasを使用した単純なpythonスクリプトは、3つのCSVを読み込み、列を標準化し、キーを生成し、キーでマージし、マッチした記録とマッチされていない記録にフラグを付けた出力CSVを書き込む。プログラマーでない場合は、フリーランサーを数時間雇用してビルドする。スクリプトは最初の月で費用対効果がある。

BlacklineやReconArtなどの専用照合ソフトウェアはこれをエンタープライズ規模で自動化するが、大規模な財務チーム向けの価格設定である。小規模ビジネスの場合、よく構築されたExcelテンプレートまたは軽量スクリプトは通常十分である。

特にインポートステップ（OCRエラーや手動の再入力なしでCSVへの銀行明細書の取得）については、BankStmtConverterはPDF明細書（スキャンまたは撮影されたものを含む）をExcelまたはCSVに変換し、検証されたバランスと会計対応形式（QuickBooksまたはXeroへの直接インポート用のQBOおよびOFX）で変換する。元の銀行のレイアウトに関係なく、銀行ごとのテンプレートなしで任意の銀行のレイアウトを処理し、複数の明細書を一度にバッチ変換する。アップロードが懸念事項の場合、デスクトップバージョンはステートメントをローカルで処理するため、ローカルコンピュータから離れない。

オプションを評価するための比較表を使用する

異なるマッチングワークフローは、異なるビジネスサイズと技術的なレベルに適している。一般的なアプローチの並列比較である。

反復可能な月次プロセスを構築する

マッチング方法が機能したら、チェックリストとして記録し、誰もが各月に手順を再発明することなく繰り返すことができるようにする。

1. 銀行明細書（PDFまたはCSV）を日付範囲でエクスポートする。PDFの場合は、合計を検証するツールを使用してExcelに変換する。
2. 同じ範囲（入金日ベース）のクレジットカード決済代行業者決済レポートをダウンロードする。
3. 同じ範囲（トランザクション日ベース）のPOS販売レポートをエクスポートする。
4. 作成した照合ワークブックテンプレートを開き、3つのソースシートにデータを貼り付ける。
5. 複合キーフォーミュラを実行してすべてのシートにマッチキーを入力する。
6. VLOOKUPを実行するか、Power Query mergeをリフレッシュしてマッチ列を入力する。
7. マッチされていないレコードをフィルタリングする（VLOOKUPが#N/Aを返すとき）。
8. マッチされていないレコードを手動で調査する：タイミングの違い、名前の変動、または手数料調整をチェックする。
9. 手動マッチを理由とともに注記列に文書化する。
10. マッチして未マッチの合計を合計する。銀行明細書の終了残高と等しいことを確認する。

毎月同じ日（例：前月の照合のための5日）にこのプロセスを実行する。一貫性により誤差が減り、異常を発見しやすくなる。

手動マッチングがまだ正しい呼び出しである場合

非常に少数のトランザクション（週5件のクレジットカード支払いなど）の場合、自動マッチング設定は無意味である。3つのソースを並べて開き、日付と金額でソートし、10分で視覚的にマッチングする。

データソースが頻繁に変更される場合（プロセッサを切り替える、新しいPOSターミナルを追加する）も手動マッチングが機能する。毎回自動化を再構築することは、手動マッチングより手間がかかることが多い。その場合、複合キーフォーミュラの準備ができた単純なテンプレートを保持し、適応させるだけである。

しかし、月間50件以上の取引を超えるか、2番目の銀行口座を追加した場合、手動マッチングに費やされた時間は、再利用可能なプロセスを構築するための時間を超える。良いテンプレートをビルドするのに1時間かかり、その後毎月5時間を節約できる。

マッチされていないトランザクション

マッチング後、残りもの：決済代行業者またはPOSレコードのない銀行入金、または銀行入金のないPOS販売。これらはタイミングの違い（トランザクションはまだ決済されていない）、手数料または調整（決済代行業者がPOSレコードのない月額手数料を請求した）、またはデータエクスポートエラー（トランザクションが日付範囲フィルタの外に落ちた）のいずれかである。

タイミングの違いについては、日付範囲を数日拡大し、マッチを再実行する。クレジットカード決済は2～3営業日のラグがある可能性がある。

手数料については、「Processor Monthly Fee」の定常POSエントリを作成し、毎月手動でマッチングする。POSが返品を記録しない場合は、決済代行業者に表示されるが、POS返金または手数料に対しても同じである。

データエラーについては、ソースシステムに戻り、トランザクションが存在することを確認する。必要に応じて日付範囲を拡大してもう一度エクスポートする。トランザクションが本当にソースシステムから消えた場合、それは赤旗である。ベンダーサポートに連絡する。

マッチされていないすべてのアイテムを理由コード（Timing、Fee、Error）と解決日とともに文書化する。アイテムが複数の照合サイクルで未マッチのままの場合はエスカレートする。

それをすべて総勘定元帳に結び付ける

取引マッチングは照合の半分に過ぎない。銀行の$3,200が47の販売から$180の決済代行業者手数料を差し引いたPOSと等しいことを知ったら、総勘定元帳が同じ金額を反映していることを確認する必要がある。

GLエントリを信用状取消アカウントと銀行アカウント用にエクスポートする。GL借方と貸方をマッチ済み合計と比較する。POSが販売で$3,200を記録したが、GLが$3,000を表示する場合、調査する転記エラーがある。

多くの会計システムは、OFXまたはCSVフィードを通じて銀行データを直接インポートする。QuickBooksまたはXeroを使用する場合、マッチされた銀行データをQBOまたはOFX形式に変換してインポートし、Excelではなくシステム内で取引をマッチングする。BankStmtConverterはこれらの形式に直接エクスポートするため、CSVの再インポートステップを排除する。

繰延収益または前払いの場合は、複数のソース間でのマッチングがさらに重要である。各支払いがどの特定の顧客注文に結びつくかを知る必要があり、単に金額のバランスだけではない。マッチされたデータのPOS注文番号または顧客名を使用して、支払いを正しい収益認識スケジュールに割り当てる。

今後のマッチング問題をソースで防止する

最高の照合は、探偵作業を必要としない照合である。チーム間で協力してデータキャプチャを上流で改善する。

クレジットカード注文を処理する人に、クレジットカードターミナルのメモフィールドにPOS注文番号を含めるよう依頼する。多くのターミナルでは、決済代行業者エクスポートに伝播し、場合によっては銀行明細書にも伝播する参照行を追加できる。

オンライン請求書を使用する場合は、支払いリクエストに請求書番号を含める。StripeとSquareはどちらも、トランザクションで移動するカスタムメタデータフィールドを許可している。

電話または郵便注文の場合は、カードに表示される顧客の名前を正確に尋ねるようチームをトレーニングし、その方法でPOSに記録する。一貫した命名により、マッチ失敗の半分が排除される。

より頻繁に照合する。月次照合は30日間のトランザクションをマッチングすることを意味し、タイミング不一致の機会が増える。週次照合はバッチサイズを削減し、問題を分離しやすくする。

目標は、95%のトランザクションが自動的にマッチングし、奇数の例外のみが手動で注意する必要がある状態に到達することである。その状態は、クリーンなエクスポート、標準化された列、良い複合キーで達成でき、高価なソフトウェアなしでも達成可能である。