은행, 신용카드, POS 데이터 연결: 불러오기 및 조정

은행 명세서에 "J. Smith CR $450"이 표시되어 있습니다. 신용카드 처리 업체는 $3,200의 일괄 입금을 보여줍니다. POS 시스템에는 47개의 개별 고객 주문이 나열되어 있습니다. 어딘가에 J. Smith 거래가 있지만 어느 주문에 연결되는지 알 수 없습니다. 매일 세 개 또는 네 개의 데이터 원본을 다루는 비즈니스에서는 이 연결 문제가 수십 건의 거래로 복잡해집니다.

은행 피드, Square나 Stripe 같은 신용카드 처리 업체, POS 시스템 등 여러 원본에서 결제를 조정하는 것은 거래 설명이 맞지 않고 참조 번호가 전달되지 않을 때 탐정 작업이 됩니다. 은행 명세서는 일괄 금액을 보여주고, 처리 업체는 결제 날짜별로 거래를 그룹화하고, POS는 개별 판매 타임스탐프를 기록합니다. 한 원본은 고객 이름을 사용하고, 다른 원본은 거래 ID를 사용하고, 셋째는 송장 번호를 사용합니다.

이 가이드는 수동 셀 대비 또는 당신이 가지지 않은 IT 리소스가 필요한 맞춤 통합 프로젝트 없이 이 세 시스템 간에 거래를 불러오고, 표준화하고, 연결하는 실용적인 방법을 안내합니다.

여러 원본 간 거래 연결이 어려운 이유

각 시스템은 자체 구조로 데이터를 내보냅니다. 은행 명세서(PDF 또는 CSV 다운로드 여부)는 일반적으로 지급인 이름과 반올림된 금액을 보여줍니다. 신용카드 처리 업체는 상인 수수료가 이미 차감된 일괄 결제 파일을 거래 날짜가 아닌 입금 날짜별로 그룹화하여 내보냅니다. POS 시스템은 수수료 전에 총 판매액을 항목별로 기록합니다.

타이밍 불일치는 형식 문제를 악화시킵니다. 고객이 월요일에 지불하고, 처리 업체가 화요일에 일괄 처리하고, 은행이 수요일에 게시합니다. 한 시스템에서는 거래 날짜로, 다른 시스템에서는 입금 날짜로 조정하면 동일한 $450 결제가 한 원본에서는 누락된 것으로 나타나고 다른 원본에서는 설명되지 않은 것으로 나타납니다.

일반적인 설명으로 인해 악화됩니다. 은행 명세서에 사람 이름이나 원래 주문 번호에 대한 참조 없이 "Credit Card Deposit"만 표시되면 여러 창을 열지 않고도 POS 기록으로 추적할 수 없습니다. 하루에 50건의 거래를 처리하는 비즈니스의 경우 월 1,000회의 조회입니다.

세 원본 모두에 걸쳐 공통 필드로 시작하기

성공적인 연결에는 세 개 내보내기에 모두 나타나는 필드가 적어도 하나 필요합니다(이름이 다를 수 있음). 거래 금액과 날짜는 보편적이지만, 금액 단독으로는 같은 금액을 같은 날에 지불하는 두 고객과 거짓 일치를 만듭니다.

체인을 통해 전파되는 참조 번호를 찾으십시오. 일부 신용카드 처리 업체는 원래 거래 ID를 처리 업체 내보내기 및 은행 명세서 메모 필드에 포함합니다. 예를 들어 Square는 은행 입금에 거래 코드를 추가합니다. POS 시스템이 지급 메모에 처리 업체가 은행으로 전달하는 참조 번호를 포함할 수 있다면 그것이 당신의 연결 키입니다.

참조 번호가 전파되지 않으면 복합 키를 구성하십시오: 거래 금액, 날짜, 지급인 이름의 첫 몇 글자를 결합하십시오. J. Smith의 $450.00 거래가 2025년 3월 15일이 되면 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 Sheets에서 열 표준화

각 내보내기를 스프레드시트에서 열으십시오. 세 개의 시트가 있는 새 통합 문서를 만드십시오: 은행, 처리 업체, 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는 지급인 이름입니다. 이 수식을 세 시트 모두에 아래로 복사하십시오.

VLOOKUP 또는 Power Query로 병합 및 연결

조정이라는 네 번째 시트를 만드십시오. 은행 기록을 먼저 복사하십시오. 은행이 앵커이므로 은행이 보여주는 것을 설명하려고 합니다.

"Processor Match" 열을 추가하십시오. =VLOOKUP(E2,Processor!E:G,2,FALSE)를 사용하십시오. 여기서 E2는 복합 키이고 Processor!E:G는 키 열과 검색할 열(처리 업체 거래 ID 같은)입니다. VLOOKUP가 #N/A를 반환하면 해당 은행 거래에는 처리 업체 일치가 없습니다.

POS 시트에 대해 유사한 VLOOKUP이 있는 "POS Match" 열을 추가하십시오.

여러 신용카드 거래를 한 줄 항목으로 일괄 처리하는 은행의 경우 은행 기록 하나에 $3,200이 표시되고 합계가 $3,200인 처리 업체 기록이 12개 표시됩니다. 그 경우 처리 업체 시트를 관련 날짜 범위로 필터링하고 순액을 합산하고 총액이 은행 라인과 일치하는지 수동으로 확인하십시오. 통합 문서의 별도 일괄 조정 섹션에 해당 일괄 입금을 기록하십시오.

Excel의 Power Query는 당신이 편하다면 더 우아하게 처리합니다. 세 원본을 모두 쿼리로 가져오고 복합 키에서 병합한 후 일치하는 열을 확장하십시오. 일치하지 않는 행은 조인된 열에 null 값으로 나타나므로 예외를 쉽게 찾을 수 있습니다.

일반적인 연결 실패 처리

깨끗한 데이터가 있어도 일부 거래는 자동으로 연결되지 않습니다. 일반적인 원인과 해결 방법은 다음과 같습니다.

타이밍 차이

처리 업체는 3월 15일을 보여주고, 은행은 3월 17일을 보여줍니다. 연결 창을 넓히십시오: 복합 키에서 날짜를 생략하고 금액과 지급인 이름만 연결한 후 날짜가 2~3일 이내인지 수동으로 확인하십시오. 또는 3월 15, 16, 17일에 대한 키를 만들고 세 번의 VLOOKUP을 수행하십시오.

수수료 및 반올림

고객이 $100을 지불했고, 처리 업체가 $3.20의 수수료를 받았고, 은행이 $96.80을 입금했습니다. POS는 $100을 표시하고, 은행은 $96.80을 표시하고, 일치하지 않습니다. POS 시트에 "순액" 열을 만들어 일반적인 처리 업체 수수료를 빼십시오. 그런 다음 순액을 연결하십시오. 또는 POS와 처리 업체 간 총액으로 연결한 다음 처리 업체 순액을 은행으로 연결하십시오.

이름 및 설명

은행은 "Smith, John"을 말하지만 POS는 "John Smith"를 말합니다. 복합 키가 깨집니다. 이름을 정규화하십시오: 성과 이름을 나누고, 알파벳순으로 정렬하고, 다시 결합하십시오. 또는 금액과 날짜만 연결한 후 이름이 충분히 가까운지 수동으로 확인하십시오.

누락된 참조 번호

참조 번호가 전파되지 않고 같은 날에 여러 $100 지불이 다른 고객으로부터 있으면 연결이 모호해집니다. 이 경우 수동 확인으로 결정하지만 사용한 기준을 문서화하여 다음 달의 조정이 더 빠릅니다. 고객이 지급 메모의 주문 번호를 포함하도록 요청하는 것을 고려하십시오.

스크립트 또는 조정 소프트웨어로 자동화

매월 동일한 원본을 조정하면 이 단계를 수동으로 반복하는 것은 시간이 오래 걸립니다. Excel 매크로 또는 Python 스크립트는 불러오기, 키 생성, VLOOKUP 프로세스를 자동화할 수 있습니다.

pandas를 사용한 간단한 Python 스크립트는 세 개의 CSV를 읽고, 열을 표준화하고, 키를 생성하고, 키에서 병합하고, 일치 및 불일치 기록이 플래그된 출력 CSV를 작성합니다. 프로그래머가 아니면 몇 시간 동안 프리랜서를 고용하여 이를 만드십시오. 스크립트는 첫 달에 대가를 지불합니다.

Blackline 또는 ReconArt 같은 전용 조정 소프트웨어는 엔터프라이즈 규모로 이를 자동화하지만 큰 재무 팀을 위해 가격이 정해져 있습니다. 소규모 비즈니스의 경우 잘 구성된 Excel 템플릿이나 경량 스크립트로 충분합니다.

특히 불러오기 단계 - OCR 오류 또는 수동 재입력 없이 은행 명세서를 CSV로 가져오기 - BankStmtConverter는 PDF 명세서(스캔되거나 사진 촬영된 포함)를 검증된 잔액이 있는 Excel 또는 CSV로 변환하고 QuickBooks 또는 Xero로 직접 가져오기 위한 QBO 및 OFX 같은 회계 준비 형식으로 변환합니다. 은행별 템플릿 없이 모든 은행 레이아웃을 처리하며 여러 명세서를 한 번에 일괄 변환합니다. 데스크톱 버전은 업로드가 우려 사항인 경우 명세서가 기계를 떠나지 않도록 명세서를 로컬로 처리합니다.

옵션을 평가하기 위해 비교 테이블 사용

다양한 연결 워크플로우는 다양한 비즈니스 규모와 기술 편안함 수준에 맞습니다. 다음은 일반적인 방법을 나란히 비교한 것입니다.

반복 가능한 월간 프로세스 만들기

연결 방법이 작동하면 체크리스트로 문서화하여 당신이나 팀 원이 매달 단계를 다시 발명하지 않고 반복할 수 있습니다.

1. 날짜 범위에 대한 은행 명세서를 내보내십시오(PDF 또는 CSV). PDF인 경우 총액을 검증하는 도구를 사용하여 Excel로 변환하십시오.
2. 동일한 범위에 대한 신용카드 처리 업체 결제 보고서를 다운로드하십시오(입금 날짜별).
3. 동일한 범위에 대한 POS 판매 보고서를 내보내십시오(거래 날짜별).
4. 조정 통합 문서 템플릿을 열고 데이터를 세 원본 시트에 붙여넣으십시오.
5. 복합 키 수식을 실행하여 모든 시트의 연결 키를 채우십시오.
6. VLOOKUP을 실행하거나 Power Query 병합을 새로고침하여 연결 열을 채우십시오.
7. 일치하지 않는 기록을 필터링하십시오(VLOOKUP가 #N/A를 반환한 경우).
8. 일치하지 않는 기록을 수동으로 조사하십시오: 타이밍 차이, 이름 변형 또는 수수료 조정을 확인하십시오.
9. 모든 수동 일치를 설명 이유가 있는 메모 열에 문서화하십시오.
10. 일치 및 불일치 총액을 합산하십시오; 은행 명세서 마감 잔액과 같은지 확인하십시오.

매달 같은 날(예: 전월 5일 조정)에 이 프로세스를 실행하십시오. 일관성은 오류를 줄이고 이상을 발견하기 쉽게 만듭니다.

수동 연결이 여전히 올바른 선택일 때

매우 작은 거래량의 경우 - 일주일에 신용카드 결제 5건 정도 - 자동화된 연결을 설정하는 것은 과한 일입니다. 세 원본을 나란히 열고 날짜와 금액별로 정렬하고 10분 안에 시각적으로 연결하십시오.

수동 연결은 데이터 원본이 자주 변경되는 경우(처리 업체 전환, 새로운 POS 터미널 추가)에도 작동합니다. 매번 자동화를 다시 만드는 것이 더 번거로울 수 있습니다. 이 경우 복합 키 수식이 준비된 간단한 템플릿을 유지하십시오.

하지만 월 50건 거래를 넘기거나 두 번째 은행 계좌를 추가하면 수동 연결에 소비되는 시간이 재사용 가능한 프로세스를 만드는 시간을 초과합니다. 좋은 템플릿을 구성하는 한 시간은 이후 월 5시간을 절약합니다.

일치하지 않는 거래는 어떻게 하나

연결 후 남은 항목이 있을 것입니다: 처리 업체 또는 POS 기록이 없는 은행 입금, 또는 은행 입금이 없는 POS 판매. 이는 타이밍 차이(거래가 아직 결제되지 않음), 수수료 또는 조정(처리 업체가 POS 기록이 없는 월간 수수료를 부과함), 또는 데이터 내보내기 오류(거래가 날짜 범위 필터를 벗어남)입니다.

타이밍 차이의 경우 날짜 범위를 몇 일로 넓히고 연결을 다시 실행하십시오. 신용카드 결제는 2~3영업일이 지연될 수 있습니다.

수수료의 경우 "Processor Monthly Fee"에 대한 상설 POS 항목을 만들고 매달 수동으로 연결하십시오. POS가 반품을 기록하지 않는 경우 처리 업체에 나타나는 환불 또는 지불 거절도 동일합니다.

데이터 오류의 경우 원본 시스템으로 돌아가 거래가 존재하는지 확인하십시오. 필요하면 더 넓은 날짜 범위로 다시 내보내십시오. 거래가 원본 시스템에서 진정으로 사라진다면 그것은 빨간 깃발입니다. 공급업체 지원에 문의하십시오.

모든 일치하지 않는 항목을 사유 코드(Timing, Fee, Error) 및 해결 날짜와 함께 문서화하십시오. 항목이 한 조정 주기 이상 일치하지 않은 상태로 남으면 상향 보고하십시오.

모두 일반 원장에 다시 연결하기

거래 연결은 조정의 절반일 뿐입니다. $3,200이 은행에 동일하고 47건 판매가 POS와 같고 $180 처리 업체 수수료를 차감한다는 것을 알게 되면 일반 원장이 동일한 금액을 반영하는지 확인해야 합니다.

신용카드 클리어링 계정 및 은행 계정에 대한 GL 항목을 내보내십시오. GL 차변 및 대변을 일치된 총액과 비교하십시오. POS가 $3,200의 판매를 기록했지만 GL이 $3,000을 표시하면 조사할 기입 오류가 있습니다.

많은 회계 시스템은 OFX 또는 CSV 피드를 통해 직접 은행 데이터를 가져옵니다. QuickBooks 또는 Xero를 사용하는 경우 일치된 은행 데이터를 QBO 또는 OFX 형식으로 변환하고 이를 가져온 다음 Excel이 아닌 시스템에서 거래를 연결하십시오. BankStmtConverter는 이러한 형식으로 직접 내보내므로 CSV 재가져오기 단계를 없애줍니다.

이연 수익 또는 선결제의 경우 원본 간 연결은 금액이 균형을 이루는 것보다 각 지급이 올바른 수익 인식 일정에 할당되도록 하기 위해 특정 고객 주문과 연결되는 것을 알아야 하기 때문에 더욱 중요합니다. 일치된 데이터의 POS 주문 번호 또는 고객 이름을 사용하십시오.

향후 연결 문제 원본에서 방지

최고의 조정은 탐정 작업이 필요 없는 것입니다. 상류 데이터 캡처를 개선하기 위해 팀과 협력하십시오.

신용카드 주문을 처리하는 사람에게 신용카드 터미널 메모 필드에 POS 주문 번호를 포함하도록 요청하십시오. 많은 터미널에서 처리 업체 내보내기에 전파되고 때로는 은행 명세서에도 나타나는 참조 라인을 추가할 수 있습니다.

온라인 송장을 사용하는 경우 지급 요청에 송장 번호를 포함하십시오. Stripe와 Square 모두 거래와 함께 이동하는 맞춤 메타데이터 필드를 허용합니다.

전화 또는 우편 주문의 경우 고객의 이름을 카드에 나타나는 대로 정확히 요청하도록 팀을 교육하고 POS에서 정확히 그렇게 기록하십시오. 일관된 이름 지정은 일치 실패의 절반을 제거합니다.

더 자주 조정하십시오. 월간 조정은 30일간의 거래와 더 많은 타이밍 불일치의 기회를 의미합니다. 주간 조정은 배치 크기를 줄이고 문제를 격리하기 쉽게 만듭니다.

목표는 95%의 거래가 자동으로 일치하고 홀수 예외만 수동 주의가 필요한 상태에 도달하는 것입니다. 이 상태는 깨끗한 내보내기, 표준화된 열, 좋은 복합 키가 있으면 비싼 소프트웨어가 없어도 달성할 수 있습니다.