Computer aided reconfiliation method and apparatus

• US 5,134,564 A
• Filed: 10/19/1989
• Issued: 07/28/1992
• Est. Priority Date: 10/19/1989
• Status: Expired due to Term

• Current Assignee: Intuit, Inc.
• Original Assignee: Intuit, Inc.
• Inventors: Dunn, Eric C. W., Proulx, Thomas A.
• Primary Examiner(s): Smith, Jerry
• Assistant Examiner(s): Cass, Russell E.
• Application Number:
  US07/424,006
• Time in Patent Office: 1,013 Days
• Field of Search: 364/406, 364/401, 364/408, 382/7, 382/3, 382/4, 382/5, 902/4, 902/40
• US Class Current: 705/33
• CPC Class Codes: G06Q 40/02 Banking, e.g. interest calculation or account maintenance credit or loans G06Q40/03
• CPC Class Codes: G06Q 40/128 Check-book balancing, updating or printing arrangements

Abstract

A method of reconciling a first list (a bank statement) formed of a first number of first records and a second list (bank customer'"'"'s list of records) formed of a second number of second records where the records affect the account balance for the bank statement. For each unmatched record in the first list, a corresponding record from the second list is selected based upon a match value. Whenever the match value exceeds a threshold value, the corresponding records from the first and second lists are paired and thereafter, are removed from further reconciliation processing. The highest match value resulting from comparing record elements and other attributes of records from the first and second lists is determined as a probable match for reconciliation.

Claims

1. A method of reconciling in a computer a first list formed of a first number of first records in the computer with a second list formed of a second number of second records in the computer where, except for errors or omissions, each of said first records is identical to a corresponding one of said second records, the method comprising:

for each one of said first records, comparing said one of said first records with one or more of said second records to determine match scores, one match score for each compared one of said second records, each match score designating the probability said one of said first records corresponds to the compared one of said second records,

for each one of said first records having a match score exceeding a predetermined pairing threshold, pairing said one of said first records with the compared one of said second records whereby said one of said first records and the compared one of said second records are reconciled,

for each one of said first records not having a match score exceeding said predetermined pairing threshold,

matching said one of said first records with a group of second records where said group of second records is determined based upon the magnitudes of corresponding match scores for said second records,

accepting or rejecting the pairing of said one of said first records with one of said second records in said group whereby, upon accepting, said one of said first records and the compared one said second records are reconciled.

2. The method of reconciling the first list and the second list of claim 1 wherein said accepting step is performed under user control.

3. The method of reconciling the first list and the second list of claim 1 further including, for one or more of said first records not paired with a second record, adding under user control a new second record to said second list corresponding to one of said first records whereby said one of said first records and the compared one of said second records are reconciled.

4. The method of reconciling the first list and the second list of claim 1 wherein said matching step is performed by determining said group to include only ones of said second records having match scores between said pairing threshold and an exclusion threshold.

5. The method of reconciling the first list and the second list of claim 1 wherein said matching step is performed by determining said group to include only a limited number of ones of said second records and to include ones of said second records having match scores closest to said pairing threshold.

6. The method of reconciling the first list and the second list of claim 1 wherein,

said first list L1 has N first records designated R1(1), R1(2), . . . , R1(r1), . . . , R1(N);

where each one of said records R1(r1) is formed of P elements E1(1), E1(2), E1(3), . . . , E1(e), . . . , E1(P),

said second list L2 has M second records designated R2(1), R2(2), . . . , R2(r2), . . . , R2(M);

where each one of said records R2(r2) is formed of P elements E2(1), E2(2), E2(3), . . . , E2(e), . . . , E2(P),

said comparing step is performed for each first record R1(r1) by Q element comparing steps C(q), for q from 1,2, . . . , Q, whereby for each first record R1(r1) each of the first elements E1(e), for e from 1, 2, . . . , U, is compared with each of the second elements E2(e), for e from 1, 2, . . . , U, respectively, for the compared one of said second records R2(r2).

7. The method of reconciling the first list and the second list of claim 6 wherein the results of each element comparing step C(q) is a probability value P(1), for q from 1, 2, . . . , Q, and where said match score is the SUM P(q) for q from 1, 2, . . . , Q.

8. The method of reconciling the first list and the second list of claim 6 wherein said first list is a bank statement for a checking account, said second list is a customer'"'"'s account information, and said elements include a date element, a check element and an amount element.

9. The method of reconciling the first list and the second list of claim 8 wherein said element comparing includes comparing to determine if the amount in the amount element of a first record R1(r1) is within an amount range of an amount in a corresponding amount element of a second record R2(r2).

10. The method of reconciling the first list and the second list of claim 9 wherein said element comparing for each first record R1(r1) with a second record R2(r2) includes,

setting a probability value P_date as a function of whether the date in the date element of said first record is within a date range of a date in a corresponding date element of said second record,

setting a probability value P_amount as a function of whether the amount in the amount element of said first record is within a amount rage of a amount in a corresponding amount element of said second record,

setting a probability value P_number as a function of whether the number in the number element of said first record is the same as the number in a corresponding number element of said second record,

forming the match score MS equal to the sum of P_date, P_amount, and P_number.

11. The method of reconciling the first list and the second list of claim 9 wherein said element comparing for each first record R1(r1) with a second record R2(r2) includes,

setting a Date Score, P_date (d1,d2,c1), probability value as a function of comparing the dates in the date elements d1 and d2 of R1(r1) and R2(r2), respectively, as a function of the number element, c1, of record R1(r1),

setting an Amount Score, P_amount (a1,a2), probability value as a function of comparing the amounts in the amount elements a1 and a2 of records R1(r1) and R2(r2), respectively,

setting a Number Score, P_number (c1,c2), probability value as a function of comparing the numbers in the number elements c1 and c2 of the records R1(r1) and R2(r2), respectively,

forming the match score MS(r1,r2) as follows:

if P_date (d1,d2,c1)=0, MS(r1,r2)=0.

if P_date (d1,d2)=0, MS(r1,r2)=(P_date (d1,d2,c1)+P_amount (a1,a2)+P_number (c1,c2))/3.

12. The method of reconciling the first list and the second list of claim 11 wherein the Date Score, P_date (d1,d2,c1), is determined as one of the date score probability values sd_i where i has one of the values 1, 2, . . . , 6 and where dd equals (d1-d2) as follows:

##STR7##

13. The method of reconciling the first list and the second list of claim 11 wherein the Amount Score, P_amount (a1,a2), is determined as one of the amount score probability values sa_i where i has one of the values 1, 2, . . . , 26, where g1 equals the sign of a1, where g2 equals the sign of a2, where l1 equals the number of digits in a1, where l2 equals the number of digits in a2, where D equals the number of digits differing in a1 and a2, and where T equals the sum of the arithmetic digit-wise difference between a1 and a2 as follows:

space="preserve" listing-type="tabular">______________________________________ g1 = g2 g1 ≠

g2 ______________________________________ ##STR8## sa.sub.1 sa.sub.3 sa.sub.5 sa.sub.7 sa.sub.9 sa.sub.2 sa.sub.4 sa.sub.6 sa.sub.8 sa.sub.10 ##STR9## sa.sub. 11 sa.sub.13 sa.sub.15 sa.sub.17 sa.sub.12 sa.sub.14 sa.sub.16 sa.sub.18 ##STR10## sa.sub.19 sa.sub.21 sa.sub.23 sa.sub.25 sa.sub.20 sa.sub.22 sa.sub.24 sa.sub.26. ______________________________________

14. The method of reconciling the first list and the second list of claim 11 wherein the Number Score, P_number (c1, c2), is determined as one of the number score probability values sn_i, where i has one of the values 1, 2, . . . , 6, as follows:

##STR11##

15. A method of reconciling in a computer a first list formed of a first number of first records in the computer with a second list formed of a second number of second records in the computer where,

said first list L1 has N first records designated R1(1), R1(2), . . . , R1(r1), . . . , R1(N);

where each one of said records R1(r1) is formed of P elements E1(1), E1(2), E1(3), . . . , E1(e), . . . , E1(P),

said second list L2 has M second records designated R2(1), R2(2), . . . , R2(r2), . . . , R2(M);

where each one of said records R2(r2) is formed of P elements E2(1), E2(2), E2(3), . . . , E2(e), . . . , E2(P),

for each one of said first records, comparing said one of said first records with one or more of said second records to determine match scores, one match score for each compared one of said second records, each match score designating the probability said one of said first records corresponds to the compared one of said second records, and wherein said first list is a statement for a first listing of an account, said second list is a second listing of said account, and said elements include a date element and an amount element,

said comparing step is performed for each first record R1(r1) by Q element comparing steps C(q), for q from 1, 2, . . . Q, whereby for each first record R1(r1) each of the first elements E1(e), for e from 1, 2, . . . , U, is compared with each of the second elements E2(e), for e from 1, 2, . . . , U, respectively, for the compared one of said second records R2(r2),

for each one of said first records having a match score exceeding a predetermined pairing threshold, pairing said one of said first records with the compared one of said second records whereby said one of said first records and the compared one of said second records are reconciled.

for each one of said first records not having a match score exceeding said predetermined pairing threshold,

matching said one of said first records with a group of second records where said group of second records is determined based upon the magnitudes of corresponding match scores for said second records,

accepting or rejecting the pairing of said one of said first records with one of said second records in said group whereby, upon accepting, said one of said first records and the compared one said second records are reconciled.

16. The method of reconciling the first list and the second list of claim 15 wherein said element comparing includes string match comparing to determine if an element of a first record R1(r1) is the same or as similar to a corresponding element of a second record R2(r2).

17. The method of reconciling the first list and the second list of claim 16 wherein said string match comparing includes comparing to detect added characters.

18. The method of reconciling the first list and the second list of claim 16 wherein said string match comparing includes comparing to detect missing characters.

19. The method of reconciling the first list and the second list of claim 16 wherein said string match comparing includes comparing to detect transposition of characters.

20. The method of reconciling the first list and the second list of claim 15 wherein said element comparing includes comparing to determine if the date in the date element of a first record R1(r1) is within a date range of date in a corresponding date element of a second record R2(r2).

21. A method of reconciling in a computer a first list formed of a first number of first records in the computer with a second list formed of a second number of second records in the computer where, except for errors or omissions, each of said first records is identical to a corresponding one of said second records, the method comprising,

for each one of said first records, comparing said one of said first records with one or more of said second records to determine match scores, one match score for each compared one of said second records, each match score designating the probability said one of said first records corresponds to the compared one of said second records,

for each one of said first records having a match score exceeding a predetermined pairing threshold, pairing said one of said first records with the compared one of said second records whereby said one of said first records and the compared one of said second records are reconciled,

for each one of said first records having a match score between said predetermined pairing threshold and an exclusion threshold,

matching said one of said first records with a group of second records where said group of second records is determined based upon the magnitudes of corresponding match scores for said second records,

displaying to a user on a computer terminal said group of second records in an order based upon the magnitudes of corresponding match scores for said second records whereby the user is presented with records for pairing in the order of most likely correspondence of records,

accepting or rejecting the pairing of said one of said first records with one of said second records in said group under user control whereby, upon accepting, said one of said first records and the compared one said second records are reconciled.

22. The method of reconciling the first list and the second list of claim 21 further including, for one or more of said first records not paired with a second record, adding under user control a new second record to said second list corresponding to one of said first records whereby said one of said first records and the compared one of said second records are reconciled.

23. The method of reconciling the first list and the second list of claim 21 further including generating a report summarizing the reconciliation results including a listing of unpaired records and balances of the first list and the second list.

24. The method of reconciling the first list and the second list of claim 21 further including the step of updating the second list to correct errors discovered as a result of reconciliation.

25. The method of reconciling the first list and the second list of claim 21 further including the step of updating the second list to add records as a result of reconciliation.

26. The method of reconciling the first list and the second list of claim 21 further including the step of updating the second list to indicate records in the second list paired with records in the first list as a result of reconciliation.